I am sure most of you are aware that in SAP Datasphere you have the ability to create a time-dependent dimension now by configuring the "From" and "To" or the "at" date values.
You can find more details here: SAP Help.
But... what if we don't just have a time dependent dimension or a time dependent text, but instead we have a time dependent dimension and a time dependent text and a time dependent attribute.
Some of you now might say.. "well - that's unrealistic" - so - welcome to the complexity of HR data.
So lets take an example:
So lets assume we have data that looks similar like this:
Our Employees:
Our Values for Attribute 01
Our Values for Attribute 02
Before we now go into SAP Datasphere and model this, lets clarify a few items upfront:
In this approach what we will do is :
... and then we bring all these together and here is another important rule to keep in mind.
When setting up Associations in SAP Datasphere the most you can point to your Attribute is 1 (one) Text Association and 1 (one) other Association, so in other case with 2 dimensions for the Attributes we basically need to set it up like a snowflake.
First we define 3 Dimensions with the individual time dependency.
Employee
Attribute 01
Attribute 02
These are all modeled as Dimension objects so that we can configure the Semantic Type.
Now we have 3 Dimensions, so all that is left to do is to now "chain them" in form of a snowflake using the Associations.
So we add the Dimension for Attribute 01 as an Association to the Employee dimension
...and we add the dimension for Attribute 02 as an Association to the Dimension object from Attribute 01.
Now all we need to do is to use our FACT and add the Association to the Employee Dimension and then create the Analytical Model.
In the Analytical Model we can then see our 3 set of Dimensions.
Because we created this using three separate dimension objects, the individual items are not grouped under on "header" in the list of dimensions, but instead - for those with BW background - its more like the good old Navigational Attributes.
and - showing data for August 02, 2023 - we have 1 dimension with 2 Attributes and each with its own time dependency.
and each time dependency is resolved correctly.
Hope this helps a few folks out. I will show next week how this also can be used to create time dependent hierarchies.
You can find more details here: SAP Help.
But... what if we don't just have a time dependent dimension or a time dependent text, but instead we have a time dependent dimension and a time dependent text and a time dependent attribute.
Some of you now might say.. "well - that's unrealistic" - so - welcome to the complexity of HR data.
So lets take an example:
- First we have a time dependent dimension "Employee" and our "Employee" has a start and end date.
- Second we have a time dependent text. Let's take a simple example and our employee gets married and changes the Last Name.
- Third - we use the example from above - the Marital Status changes as an Attribute to our Employee and there are many more examples for the time dependent Attribute, such as Pay Grade, Pay Level, Job Level, ....
So lets assume we have data that looks similar like this:
Our Employees:
| EMPLOYEE ID | EMPLOYEE NAME | START | END |
| 100 | EMPLOYEE 100 A | 01-Jan-23 | 30-Jun-23 |
| 100 | EMPLOYEE 100 B | 01-Jul-23 | 31-Dec-99 |
| 200 | EMPLOYEE 200 A | 01-Jan-23 | 30-Jun-23 |
| 200 | EMPLOYEE 200 B | 01-Jul-23 | 31-Dec-99 |
| 300 | EMPLOYEE 300 A | 01-Jan-23 | 30-Apr-23 |
| 300 | EMPLOYEE 300 B | 01-May-23 | 31-Dec-99 |
| 400 | EMPLOYEE 400 A | 01-Jan-22 | 30-Jun-23 |
| 400 | EMPLOYEE 400 B | 01-Jul-23 | 01-Aug-23 |
| 500 | EMPLOYEE 500 A | 01-Jan-22 | 30-Jun-23 |
| 500 | EMPLOYEE 500 B | 01-Jul-23 | 01-Aug-23 |
Our Values for Attribute 01
| EMPLOYEE ID | ATTRIBUTE01 | START | END |
| 100 | ATTRIBUTE01 100 A | 01-Jan-22 | 30-Jun-23 |
| 100 | ATTRIBUTE01 100 B | 01-Jul-23 | 01-Aug-23 |
| 200 | ATTRIBUTE01 200 A | 01-Jan-23 | 30-Apr-23 |
| 200 | ATTRIBUTE01 200 B | 01-May-23 | 31-Dec-99 |
| 300 | ATTRIBUTE01 300 A | 01-Jan-22 | 31-Dec-22 |
| 300 | ATTRIBUTE01 300 B | 01-Jan-23 | 30-Apr-23 |
| 400 | ATTRIBUTE01 400 A | 01-Jan-22 | 30-Jun-23 |
| 400 | ATTRIBUTE01 400 B | 01-Jul-23 | 01-Aug-23 |
| 500 | ATTRIBUTE01 500 A | 01-Jan-23 | 30-Apr-23 |
| 500 | ATTRIBUTE01 500 B | 01-May-23 | 31-Dec-99 |
Our Values for Attribute 02
| EMPLOYEE ID | ATTRIBUTE02 | START | END |
| 100 | ATTRIBUTE02 100 A | 01-Jan-22 | 31-Dec-22 |
| 100 | ATTRIBUTE02 100 B | 01-Jan-23 | 30-Apr-23 |
| 200 | ATTRIBUTE02 200 A | 01-Jan-22 | 31-Dec-22 |
| 200 | ATTRIBUTE02 200 B | 01-Jan-23 | 30-Apr-23 |
| 300 | ATTRIBUTE02 300 A | 01-Jan-23 | 31-Mar-23 |
| 300 | ATTRIBUTE02 300 B | 01-Apr-23 | 31-Dec-23 |
| 400 | ATTRIBUTE02 400 A | 01-Jan-23 | 31-Mar-23 |
| 400 | ATTRIBUTE02 400 B | 01-Apr-23 | 31-Dec-23 |
| 500 | ATTRIBUTE02 500 A | 01-Jan-23 | 31-Mar-23 |
| 500 | ATTRIBUTE02 500 B | 01-Apr-23 | 31-Dec-23 |
Before we now go into SAP Datasphere and model this, lets clarify a few items upfront:
- In SAP Datasphere the only object types that allow you to setup the time dependency are the Semantic Type "Text" and the Semantic Type "Dimension".
- We need in this example 3 (three) independent time dependencies, which means that a single dimension object won't work as that would provide us only with one set of time dependencies.
In this approach what we will do is :
- Create a Dimension which will leverage the source table for the Employees, which will take care of the name changes.
- We create a second Dimension for Attribute 01.
- We create a third Dimension for Attribute 02.
... and then we bring all these together and here is another important rule to keep in mind.
When setting up Associations in SAP Datasphere the most you can point to your Attribute is 1 (one) Text Association and 1 (one) other Association, so in other case with 2 dimensions for the Attributes we basically need to set it up like a snowflake.
First we define 3 Dimensions with the individual time dependency.
Employee
Attribute 01
Attribute 02
These are all modeled as Dimension objects so that we can configure the Semantic Type.
Now we have 3 Dimensions, so all that is left to do is to now "chain them" in form of a snowflake using the Associations.
So we add the Dimension for Attribute 01 as an Association to the Employee dimension
...and we add the dimension for Attribute 02 as an Association to the Dimension object from Attribute 01.
Now all we need to do is to use our FACT and add the Association to the Employee Dimension and then create the Analytical Model.
In the Analytical Model we can then see our 3 set of Dimensions.
Because we created this using three separate dimension objects, the individual items are not grouped under on "header" in the list of dimensions, but instead - for those with BW background - its more like the good old Navigational Attributes.
and - showing data for August 02, 2023 - we have 1 dimension with 2 Attributes and each with its own time dependency.
and each time dependency is resolved correctly.
Hope this helps a few folks out. I will show next week how this also can be used to create time dependent hierarchies.
- SAP Managed Tags:
9 Comments
