This Blog is part of the SAP BW/4HANA Development blog series and will focus on how to create a key date derivation type, how to use it in a Composite Provider Temporal Joins and how to use in Hierarchies present in a Query. 

What is a Key Date?

BW system needs to know what date to use when searching for the relevant attribute information. This is where the Key Date comes in. BW uses the Key Date as a special parameter to read the relevant attribute records that correspond to the characteristic. 

 

How to create Key Date Derivation Type?

1. BW Modelling tools editor will help us to create a Key data derivation type. The editor can be called from the ‘context menu’ of any Info Area. The key date derivation type is then assigned to central Info Area 0BW4_GLOBAL.
sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

2. Enter the name and description of the key date derivation type and then click on the Finish button.
sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

3. Select the Variable time reference from the drop-down.sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

4. Cal month Time characteristic info object needs to be selected from the Browse button.sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

There are three derivation types available First Day of the Period, Last Day of the Period and Period Offset by Number of Days.sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

5. When clicked on the save button, the Key date derivation type gets created under the info area 0BW4_GLOBAL.
sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

How to use Key Date Derivation Type in Temporal Joins of Composite Provider?

Consider an example, Profit center is a Time-dependent attribute of Cost Center and there is a Cost transaction ADSO which has cost center field.

 

In the Composite Provider, we will be doing the Temporal Join of the cost center info object and cost transaction ADSO and also we will be setting the key date using the key date derivation type.

 

Cost Center (ZCOSTCNTR) Sample Data: –

costcntr

profitcntrusercompcodecoareadatefromdateto

11200

123112110000101

20081231

11200456112120090101

99991231

11300123212110000101

99991231

11400123312120110101

20110202

 

Cost Transaction ADSO(ZCOSTADSO) Sample Data: –

Cid

oidordrdtfisprfisvarntclyrclmnthcstcntrcmpcode

cost

10011

1201202006232020006K4202020200611200124501
100121202200812312008012K420082008121120012

3422

10013

1203201607052016007K4201620160711300124231

10014

1204200812312008012K4200820081211300123203

10015

1205200405242004005K4200420040511200121282

10016

1206201304252013004K4201320130411200126536

10017

1207201912182019012K420192019121130012

2891

100181208201112312011001K420112011011140012

1233

100191209201712312017012K420172017121140012

6752

100201210201012312010012K420102010121140012

8921

 

A Composite Provider is created by doing the inner join of the above two mentioned objects.sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

The time-dependent characteristic automatically gets mapped in temporal join operands.sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

The next step is to set the Key Date.sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

The Key Date can be set using different options as shown in below screenshot. The “ZKEYDATE” Key Date Derivation type is selected.

Then Activate the Composite Provider and view the data.

 

As we have created the key date derivation type with Cal month time characteristic and derivation type as the first day of the period, it will check the first day of the Cal month comes under which valid from and valid to and accordingly populate the profit center column.sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

 

For Example,

Row 1:- The cost center = 11200, the first day of the Cal month is 01.05.2004 which comes under the valid from – 01.01.1000 and valid to 31.12.2008 so the value 123 is populated for the profit center.

This is how the key date derivation type can be used in a temporal join in Composite Provider.

 

How to use Key Date Derivation Type in Hierarchies present in a Query?

A Time-dependent hierarchy has been created on the Cost Center Info object.

sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

Below shows the data of how the hierarchy is maintained.
sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

Created an ADSO with the above-mentioned Cost Center Hierarchy Info object.
sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

Loaded the ADSO with the data shown below.sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

Consumed the ADSO in a Query. The key date using the key date derivation type can be set in the (properties -> Hierarchy) section of an info object.sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

Also, a variable(ZCALMON1) has been created on Cal month as we are using ZKEYDATE derivation type which is built on 0calmonth. So, the hierarchy structure will be displayed based on the Cal month selected by the user.sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

For Example: – User has selected Cal month = 04.2012.  The first date of the Cal month is 01.04.2012. This date will be used to validate which cost center is eligible to come under which hierarchy node. Consider parent node Marketing, 11400 cost center has valid from 01.01.2011 – 31.12.2012, so this cost center will be present in the hierarchy structure.11100 cost center has valid from 01.01.1000 – 31.12.2008 and as 01.04.2012 does not fall under this range,  this cost center will not be present in the hierarchy structure under Marketing parent node.

Similarly, the other cost centres are validated and then populated. Because of this,  different Cal months will have a different hierarchy structure in output.
sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

sap-bw-4hana-version-2-0-use-key-date-derivation-type

 

This is how the key date derivation type can be used in hierarchies in queries.

Stay tuned for more development nuggets for BW4/HANA.

Subscribe to our Newsletter

5920 Windhaven Pkwy
Plano TX 75093.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com