SAP Datasphere: How to define Offsets for Restriction Variables in Analytic Models

[Tano_B]

The definition of offsets for time characteristics like posting date, calendar month, fiscal period, etc. is a very common requirement when building queries for reporting. This blog post will show you a practical example how to implement this requirement in an analytic model instead of handling it in a view.

In this example, we have a fact view with the time characteristic "Year". This time characteristic is associated to the standard "Year" dimension of SAP:

The definition of offsets happens in another view, called "DeriveOffset". It constist of the key column "Year" of the standard time dimension and two calculated columns, "NextYear" and "PreviousYear". Furthermore it has an input parameter for the "CurrentYear", which filters the key column "Year". As a result, the view always returns one row with the "CurrentYear" and its previous and next value:

This view can be used as "Lookup Entity" in analytic models for restriction variables. When creating a restriction variable for restricted measures you can use the "DeriveOffset" view and choose the respective "Result Column". In this example it would be the "NextYear" column:

The "Lookup Entity Parameter Mapping" let's you decide, how the input parameter of the "Lookup Entity" should be filled. Here we choose "Map to" -> "New Variable":

Datasphere automatically creates a standard variable with the input parameter's name (here: "CurrentYear") of type manual input:

This standard variable can be reused for further restriction variables. In this example, further restriction variables are created with the same "Lookup Entity" but with different "Result Column", mapped to the same standard variable "CurrentYear":

The three restriction variables can be used to create restricted measures, in this example for the measure "GrossAmount":

As a result - when opening the preview - there is only one prompt for the current year, but the defined restricted measures are with the respective offset:

 

This basic example could further be enhanced by defining a "Dynamic Default" value for the standard variable, or it could also be of the type "Derive Value" filled with values of a central view/table. The DeriveOffset view could be enhanced with more offset columns or a further cascading derivation logic could be implemented. In general, I think the derivation features have great potential when designing analytic models and the ability to use it for restriction variables closes gaps compared to BW queries. From architecture perspective, it should be considered to design derivation views in a centralized way, especially for common requirements like offsets for time characteristics.