SAP Datasphere Analytic Model Series – Using Variables in Analytic Model

[tim_huse]

Figure 1: Using Variables in Analytic Model (Source: SAP)

Introduction

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:

1. Introducing the Analytic Model


2. Example Data Model used along the entire blog series


3. Motivation & Comparison with the Analytical Dataset


4. Calculated & Restricted Measures


5. Exception Aggregation


6. Using Variables in Analytic Model (this blog)


7. Time-Dependency


8. Data Preview


9. User Experience & Navigation Paradigm


10. Multi-Fact Support in Analytic Models

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 Variable

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)

 

Filter Variable

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)

 

Conclusion

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.

Best wishes,

Tim

 

Further Links

• Introducing the Analytic Model in SAP Datasphere (Blog)


• A deeper look into how SAP Datasphere Enables a Business Data Fabric


• Analytic Model Documentation in SAP Help Portal

Find more information and related blog posts on the topic page for SAP Datasphere.

[jensrm]

Hi tim.huse ,

thank you for this very nice overview and in particular also stating very clearly the possibilities one has with each of the type of variables.

I would still really appreciate a proper variable (with a predefined range set of possible values, but which can be given by the developer). This variable should than be used in "case when" statements inside of the AM, to e.g. select, which column is used as the basis for a calculation. Of course, one can use a work-around by using a source variable which is then inserted into a column, but that is very badly maintainable and has bad influence on the reusability of the source fact model.

Thank you

Jens

[jan_fetzer]

jens_rottmann_matthes , we are working on a concept on allowing subsequent calculations & derivations based on the value(s) that the user entered.
Please note that you can script on the SAC side to set defaults for prompts - this is not what you asked for, but is an additional level of dynamics added. Cheers, Jan

[stefan_merz]

Hi tim.huse

thanks for the interesting blog. I tried an analytical model with a filter variable. I used an intervall. The velaue help shows all values of for example the characteristic customer. I wanted to select a specific number intervall. But the variable only allows to type in values, which are available in the data.

For example I wanted to select all customers between 1000 and 1999, but the customer 1999 is not existent. I wasn't allowed to type in the number 1999. Therefore I need to know the exact last number. Of course I cam´n look in the value help for the last number. But if I have 5000 customers, this allways takes some time.

Is there the possibility to type in values into the variable, which are not existent in the data?

Thank you Stefan

[cjfaurie_1616]

Good evening / morning tim.huse

We are a bit lost when it comes to finding a replacement in SAP Datasphere for BEx query variables:

(1) 'Formula Variable Customer Exits' and

(2) 'Formula variable Replacement Path'?

Is there a replacement for this and how do we handle this going forward?

Your guidance will be appreciated.

Thanks

Kind regards

Neels Faurie

[davide_ranieri]

Hi tim.huse,

thank you for sharing the blog. We are facing an issue using variables in Analytic Model (both filter and restricted measure variables).
We would like to define a specific order in the preview prompt, but it seems that the tool does not keep neither the alphabetical order of the technical name / business name nor the order of creation of the variables.
Is there the possibility to set an order for the variables?

[stefan_merz]

Hi tim.huse

I created a single variable for restricted key figures based on field posting. My goal is to select everything with posting date >= variable - 29 days and posting date <= variable

posting date

Validation of restricted key figure delivers no problem, but implementing the isn't possible. How is the correct statement?

Kind regards Stefan

[jan_fetzer]

Hi cjaurie , we are working on a concept on allowing subsequent calculations & derivations based on the value(s) that the user entered. We try to align w concepts of BW in this regard, but need to see how much of that is possible and in what timeframe. This is currently being elaborated.

Cheers, Jan