Design Studio: Performance Implications of Variables
The content of this blog refers to variables of data sources in BW systems. In Design Studio data sources can refer to BW systems (Queries), HANA systems (Views) or DSL universes. All of them support the concept of variables. However the content below describes the behavior of BW data sources only.
Design Studio supports the concept of variables within data sources. Variables can be set via script or interactively in the UI of the application.
Setting a variable value via script can be done via the “setVariableValue” API:
To give the application user the possibility to enter variable values the “openPromptDialog” API can be used:
This API shows a UI that allows entering values of all variables inside the application.
Where is the beast?
There is one thing that you might have noticed from the stuff in the overview. Variables “belong” to the data source. However the API to set variable values is part of the APPLICATION object. The reason for this is a concept called the “Variable Container” (VC). The VC provides some very useful services for application developers. The VC combines all variables of all data sources in the application. In addition the VC performs “variable merging”. This means that if multiple data sources provide the “same” variable then the VC treats them as one variable.
In an application there are 4 data sources using a “COST_CENTER” variable. If an application wants to show data from a cost center, then (without a VC) the application code would need to set variable values for all data sources (just for illustration purpose – this API does not exist) :
Using a VC with variable merging shrinks this to a single call:
In the UI the situation would be even worse (i.e. more explicit to the end user) without a VC. In our example the “openPromptDialog” would display the “COST_CENTER” four times:
In such a UI the user must enter the cost center four times, all with the same value. Such a behavior is not desired. Therefore the “variable merging” merges all 4 “COST_CENTER” variables into one:
What does the beast do?
The variable container does really cool things. So what’s the deal?
Like all things in life this comes with a price. The variable container has a significant effect on the application’s performance.
Whenever the variable container changes, ALL data sources inside the variable container are invalidated.
There are three aspects that application developers need to know:
1) All data sources of an application that have at least one variable join the VC. These data sources share the ”invalidation life cycle” with each other (see 2. and 3. below). Only these data sources that have no variable at all are not inside.
2) The VC changes whenever a value of a variable changes. I.e. a call to “setVariableValue” invalidates all queries in the VC – also the queries which do not reference the variable that was changed.
3) The VC changes whenever a new data source joins the VC. A data source (with variables) joins the VC when it is loaded via “loadDataSource()” or re-initialized via “assignDataSource()”. As mentioned above this invalidates all data sources that are already part of the VC.
What else should I know?
A special context to consider when using variables is the application startup. The common place to put code that runs at startup is the “On Startup” event of the “APPLICATION” object.
During the “On Startup” event “normal” data sources (those which have “Load In Script” set to “false”) are already initialized. E.g. you can get values from a data source using “getData()” within the “On Startup” event.
This implies that data sources with variables have been initialized using the default values of these variable.
Calling “setVariableValue” during “On Startup” will trigger a re-initialization of all data sources in the VC. This additional re-initialization is a performance penalty that should be avoided. This can be done by moving code that initializes variables into the “On Variable Initialization” event:
The availability of the “On Variable Initialization” event allows initialization of variables at startup while preserving the concept that data sources are “ready to use” in the “On Startup” event.
What should I do to improve performance?
1) Try to use/find/create queries without variables
As mentioned above a query that has at least one variable will trigger that the data source joins the VC – even if the variable is not used in the application. If you have the appropriate permissions you could create another version of such a query without a variable. Alternatively you could remove the variable from the query if the variable is not used within other tools.
2) Beware of “setVariableValue/Ext” – Use “setFilter” even if a corresponding variable is available
In other BW related tools variables were a primary mechanism for filtering data. Modern script based tools (like Design Studio) provide a more lightweight mechanism by offering script APIs like “setFilter”.
Even if a variable is available for filtering then the “setFilter” API should be used instead. As stated before the “setVariableValue” always affects all data sources in the variable container (even if they are not related to the variable used in the “setVariableValue” call).
The “setFilter”-API affects only the data source it is called on. There are no side effects to other data sources.
However variables are also used for other purposes than filtering. In such cases the usage of variables is still necessary.
3) Use “On Variable Initialization” to set variable values at application startup
At the beginning of “On Startup” all data sources (except those with “Load In Script” = “true”) are already initialized. Calling “setVariableValue” during “On Startup” will trigger re-initialization of all data sources that are in the variable container.
To avoid this double initialization “setVariableValue” calls should be moved from “On Startup” to “On Variable Initialization”
4) Have a close look at “Load In Script” data sources joining the variable container
In most cases a late initialization of a data source is a good thing. It helps to increase the startup performance. However when several data sources are already in the variable container, the initialization of an additional data source with variables (using “loadDataSource()”) invalidates all other data sources in the VC.
5) Consider setting “Merge Prompts” to “false”
There is an option to disable the VC in an application. In the application properties you will find a property called “Merge Prompts”. By default this property is “true”, which means that the VC is active. It might be appropriate to set this property to “false”. This disables the VC and the variable merging. Even if the variable merging is semantically required you can still set “Merge Prompts” to “false” (and gain performance improvements) and emulate the “Merge Prompts” behavior by following the instructions of this blog (see chapter “How Do I Emulate ‘Merged Prompts’”):