Time Dependency for Dimensions and Texts in Analytic Model
Figure 1: Time Dependency for Dimensions and Texts in Analytic Model (Source: SAP)
The SAP Datasphere Analytic Model Series is intended to provide you with useful guidance on how to utilize the new Analytic Model to leverage the potential of your data landscape. The Analytic Model allows for rich analytical modelling in a targeted modelling environment and will be THE go-to analytic consumption entity for SAP Datasphere.
This article is the 7th of the blog post series and introduces Time Dependency for Dimensions and Texts in Analytic Model. The following blogs have been published so far:
- #1: Introducing the Analytic Model in SAP Datasphere
- #2: Data Model Introduction
- #3: Motivation & Comparison with the Analytical Dataset
- #4: Calculated and Restricted Measures
- #5: Exception Aggregation
- #6: Using Variables in Analytic Model
Stay tuned for more blogs around the Analytic Model in the coming weeks.
Time-Dependency enablement for dimension or text in SAP Datasphere is available since Q4.2022, it was firstly supported in Analytical Dataset. The Analytic Model supports it as well and adds the possibility to collect the reference date via a model parameter.
Master data often changes over time. This is known as “time-dependency”. Let us take “Employee” as an example, an employee could change the marital status, the record in the system will be like this:
Figure 2: Sample data of Employee (Source: Own Image)
With this feature enabled, the report could display appropriate marital status for any given time period.
How to Apply
Modelers can specify the periods of validity of each record of your dimension or text entity. The following illustrations are based on below simple data model:
Figure 3: Simple data model for illustration (Source: Own Image)
Dimension: Employee_TimDep, is associated with Analytical Dataset: V_Sales_TD, which is the fact source of Analytic Model AM_TimeDep.
Three steps are required to implement the time-dependency feature in Analytic Model.
- Step 1: Specify Time-Dependent Semantic Types in your Dimension or Text Entity
- Step 2: Associate the dimension with Analytical Dataset and create Analytic Model
- Step 3(Optional): Add a variable to allow SAP Analytics Cloud users to enter a date of their choice and show dimension members based on that key date
Step 1: Specify Time-Dependent Semantic Types in your Dimension or Text Entity
In above example of dimension: Employee_TimDep, make the changes accordingly as below:
Figure 4: Time-Dependent setting on Dimension (Source: Own Image)
By default, the “Business Date – From” and “Business Date – To” are treated inclusively, however you could change the behavior, please refer to SAP Help: Enable Time-Dependency for a Dimension or Text Entity.
Step 2: Associate the dimension with Analytical Dataset and create Analytic Model
Any Analytical Dataset pointing to a time-dependent dimension via an association automatically benefits from its time-dependent data. The dimension members and their names are displayed based on the current date by default. Technically, this means that reference date is pushed down in the WHERE condition when the dimension members or text members are selected during query execution.
For example, let’s create an association on the Analytical Dataset: V_Sales_TD.
Figure 5: Association between Dimension and Analytical Dataset (Source: Own Image)
As the reference date (aka key date) is not specified, the system uses the current date by default. Click on the button “Create Analytic Model” on the panel of Analytical Dataset: V_Sales_TD.
Figure 6: Create Analytic Model from Analytical Dataset (Source: Own Image)
After deployment of Analytic Model: AM_TimeDep, perform the data preview to verify the data. The current date is April 24th in 2023, the retrieved “Marital_Status” is “Married”.
Figure 7: Data Preview of Analytic Model (Source: Own Image)
Step 3 (Optional): Add a variable to allow SAP Analytics Cloud users to enter a date of their choice and show dimension members based on that key date
With Analytic Model, there is a new variable type called “Reference Date Variable”, which provides the flexibility of choosing a key date on your own choice.
Figure 8: Create Reference Date Variable on Analytic Model (Source: Own Image)
Figure 9: Prompt for Reference Date Variable (Source: Own Image)
The test data of Employee is as below:
Figure 10: Test data for Employee (Source: Own Image)
Enter “2020-04-15” as key date, the marital status shows “Single”. Enter “2020-04-16”, it shows “Married”.
Figure 11: Analytic Model Data Preview with prompt (Source: Own Image)
So far reference date variable on Analytic Model does not support scripting on default value (e.g., set to current date), SAP Analytics Cloud does offer scripting of prompts though that we can leverage to our benefit.
With the New Optimized Story Experience in SAP Analytics Cloud that unifies the story and analytic application, you could write scripting in story, e.g., on event “onInitialization” during page initial loading, set prompt of key date to current date or a calculated date (like beginning of this week).
Figure 12: Scripting default value of prompt in SAP Analytics Cloud (Source: Own Image)
This blog gave a brief introduction and detailed steps to implement time-dependency of dimensions and texts with the new Analytic Model in SAP Datasphere. The new reference date variable on Analytic Model makes it even more convenient for modelers.
Thanks for reading! I hope you find this post helpful. For any questions or feedback just leave a comment below this post.
Many thanks to Jan Fetzer for the collaboration on this blog post!
- Introducing the Analytic Model in SAP Datasphere (Blog)
- Analytic Model Documentation in SAP Help Portal
- New openSAP Course opening on May 24, 2023: Introduction to SAP Datasphere
Find more information and related blog posts on the topic page for SAP Datasphere.
If you have questions about SAP Analytics Cloud you can submit them in the Q&A area for SAP Datasphere in the SAP Community.