Figure 1: Using Variables 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 rich analytical modelling in a targeted modelling environment and will be THE go-to analytic consumption entity for SAP Datasphere.
This article is the sixth in the blog post series and describes what variables are in Analytic Models and which variable types exist.
We plan to release a new blog every Monday for the coming weeks. These blogs have been released so far:
Stay tuned for more blogs around the Analytic Model in the coming weeks
Variables in Analytic Model
Variables are mutable values that are captured by a user and used for calculations and filtering within the Analytic Model. When adding a variable, the user is prompted to enter a value for the variable in the data preview in SAP Datasphere or when consuming the Analytic Model in a story in SAP Analytics Cloud. Created variables must be used within the Analytic Model, otherwise an error message will be displayed.
There are different variable types that can be used in the Analytical Model. These are presented in the following chapters.
Source variables are used in fact sources to map values to the input parameters of the underlying view. The fact source uses the source variable in calculated columns, filter expressions or SQL coding. You can define to expose the input parameter of the fact source or to set a static value for the input parameter. If the input parameter is exposed, the SAP Analytics Cloud user will be prompted to enter a value for the source variable. A default value can be set for the source variable. See the following blog for more information.
In the following example (Figure 2) we create a view that has an input parameter IP_Controlling_Area which is used in a filter expression (step 1). We want to import this view as a fact source for a new analytic model. During the import we map the input parameter to a new source variable, which we also name IP_Controlling_Area (Step 2). In Step 3, when opening an SAP Analytics Cloud Story with the Analytic Model, the user is prompted to enter a value for the source variable. Currently there is no value help available for the source variable.
Figure 2: Source Variable Example (Source: Own Image)
Restricted Measure Variable
Restricted measures build on existing measures, but run flexible filters on them (e.g. “Revenue of France” with country = ‘France’, see here). Restricted measure variables are dimension-specific and allow to define dynamic filter expressions for restricted measures (e.g. “Revenue of selected country” with country = <user input>). It can be applied only in restricted measures, and they do not filter the entire data. A restricted measure variable can be a single value, multiple values, an interval or a range of values. A default value can be set and the restricted measure variable can be marked as mandatory. In the SAP Analytics Cloud, a value help can be displayed for this variable.
In the following example (Figure 3), we want to create a restricted measure Value_for_Product in an Analytic Model that restricts the key figure Price to a value for the dimension Product. We want to create a restricted measure variable WHICH_PRODUCT that dynamically filters this measure to the Product ID. To do this, in step 1, we define a restricted measure variable that references the Product ID dimension. In step 2, a restricted measure is created and referenced to the variable in the expression. In step 3, this variable can be used in SAP Analytics Cloud Stories. In the input prompt, a value help is displayed with the values of the Product ID.
Figure 3: Restricted Measure Variable Example (Source: Own Image)
The filter variable is a dimension-specific variable that is used to filter the dataset. In Analytical Datasets this variable is already known as "Story Filter". A filter variable can be a single value, multiple values, an interval or a range of values. A default value can be set and the filter variable can be marked as mandatory. In the SAP Analytics Cloud, a Value Help can be displayed for this variable.
In the following example (Figure 4) we want to define a filter on the Controlling Area dimension. For this purpose, in the first step a filter variable is created as a single value on the dimension Controlling Area, which is not mandatory. The name for the business name of the dimension also automatically becomes the name of the filter variable. An input prompt for the variable is opened in the SAP Analytics Cloud Story. For this purpose, a Value Help is displayed with the possible values for the Controlling Area dimension (step 2).
Figure 4: Filter Variable Example (Source: Own Image)
Reference Date Variable
The reference date variable can be used to define a date that can be utilized as a reference date for time-dependency of dimensions and texts (See more here). When a user selects a date for the reference date variable in an SAP Analytics Cloud Story, all dimensions members are filtered and displayed based on this date. The reference date variable is always a single value, which must be mandatory populated. A default value can be set. In SAP Analytics Cloud, a date picker is displayed for this variable to select a date.
In the following example (figure 5), a time dependency is implemented for a dimension of an Analytic Model. A reference date variable Key Date is created, which allows the user to select a reference date in an SAP Analytics Cloud Story. By default, February 28, 2023 is used. A prompt with a date picker opens in the story. The user will see the data of the time-dependent dimension based on the selected date.
Figure 5: Reference Date Variable Example (Source: Own Image)
This blog introduced how you can create variables in an Analytic Model in SAP Datasphere and then utilize them within the data preview and SAP Analytics Cloud Stories. Variables are an exciting extension that expand the possibilities in data modeling.
Thanks for reading! I hope you find this post helpful. For any questions or feedback just leave a comment below this post. Feel free to also check out the other blogposts in the series. I would like to thank my colleague jan.fetzer for collaborating on the blogpost.