Datasphere - Populating Dynamic Default Values in Analytical Model Variables

[Vajrakanth_Siddu]

Populating dynamic default values in report variables without user entry is a powerful feature in reporting tools that allows the default value for a variable to be determined at runtime based on certain conditions or logic, rather than being fixed or manually specified by the report designer.

In SAP BW/4HANA, dynamic default values can be set using customer exit variables. The I_STEP variable helps define which stage of processing the exit is at, such as initialisation, selection, or validation. Typically, when I_STEP = 1, the exit is in the initialisation stage, where you can dynamically set default values based on custom ABAP code, but how do we achieve the same results in SAP Datasphere? 

In this blog, I will walk through the process of populating dynamic default values in SAP Datasphere Analytical Model variables without requiring user entry, using a combination of built-in functionality and SQL Script.

In this example, I will explain the process of setting the previous fiscal year period by default in the FISCPER analytical model standard variable.

Step-by-Step Guide: Assigning the Previous Fiscal Year Period to the FISCPER Variable dynamically.

Step 1: Create a table function with the required logic to define the dynamic variable value. This table function will be triggered in real-time during the execution of the analytical model in Datasphere.

Sample SQL Script:

Step 2:  Create a fiscal year period Variable in Analytical Model and select the Dynamic Default in Value Definition section. Note: I have used standard variable in this example and it is mapped to an input parameter so the default value can be passed to input parameter of downstream models. You can also user filter variables at analytical model level if you don't need input parameter at downstream models.

Step 3 :  Select your table function : Default Value Table in Lookup Entity Field and select FISCPER in Result Column Field.

Step 4:  Deploy the Analytical Model and preview the data.  Here you can see previous fiscal period 2024010 as a default value which is generated by the table function.

Step 5:  Create a SAC Story based on above analytical model, system will populate the FISCPER variable with the default value 2024010 which is generated by the table function.

This functionality can be implemeneted in below variables:

1. Standard variable
2. Filter Variable
3. Restricted Measure Variable 
4. Reference Date Variable.

It's a simple functionality but a powerful feature in Datasphere.