This Blog is a 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.
2. Enter the name and description of the key date derivation type and then click on the Finish button.
3. Select the Variable time reference from the drop-down.
4. Cal month Time characteristic info object needs to be selected from the Browse button.
There are three derivation types available First Day of the Period, Last Day of the Period and Period Offset by Number of Days.
5. When clicked on the save button, the Key date derivation type gets created under the info area 0BW4_GLOBAL.
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: –
Cost Transaction ADSO(ZCOSTADSO) Sample Data: –
A Composite Provider is created by doing the inner join of the above two mentioned objects.
The time-dependent characteristic automatically gets mapped in temporal join operands.
The next step is to set the Key Date.
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.
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.
Below shows the data of how the hierarchy is maintained.
Created an ADSO with the above-mentioned Cost Center Hierarchy Info object.
Loaded the ADSO with the data shown below.
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.
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.
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.
This is how the key date derivation type can be used in hierarchies in queries.
Stay tuned for more development nuggets for BW4/HANA.