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’”):
Great blog! Do you expect that Design Studio will behave similar when using SAP HANA views as data sources. In general it seems that adding more data sources introduces an exponential amount of negative side effects, especially if you take the seqential execution of data sources into consideration.
I hope more information will be shared how Design Studio handles certain stuff at run-time!
With kind regards,
Martijn van Foeken | Intenzz
on HANA there is no concept of sharing variables between multiple views. Each view has its own variables, even if they share the same name and type. So the concept of variable merging on HANA is less useful than on BW data sources. Therefore on HANA the final implementation will probably end up not invalidating “other” data sources when variable values are set.
The only useful scenario for merging variables in HANA data sources is when the same view is inserted multiple times into the same Design Studio application. In that case the variables of the data sources based on the same view will share the variable values.
I'm actually facing the scenario you are describing in a current project. Due to the fact that a data source can only have one initial view I need to add SAP HANA views multiple times as a data source if I need a different initial view for multiple crosstabs and / or charts. Being able to recalculate the 'cube' based on an initial view per crosstab or chart would be a great improvement! More similar as the 'relational micro cube' that is part of a Web Intelligence document.
Regarding the suggestion to 'Use “On Variable Initialization” to set variable values at application startup' we are facing another challenge. All our SAP HANA views are executed with a IP_USER_ID. We need this input parameter to filter the data in the views based on authorization tables stored in SAP HANA (business requirement). The user id of the user executing the Design Studio application on BIP is captured when loading the first data sources. Then during On Startup it's used to fill the IP for other data sources as well. During the 'On Variable Initialization' the user id is not available yet.
Any other tips or tricks to speed-up startup and execution times?
With kind regards,
Martijn van Foeken | Intenzz
I’m not sure if this works in your scenario, but one possibility to use context data (like a user ID) in an application is to pass it into the application via URL parameter and global script variable. The data itself is retrieved in another web application that in turn starts the “real” app.
To define a global variable that can be used as an URL Parameter, click on “Global script Variables” setting of the application’s properties.
After that you can use this global script variable to set the variable value of the data sources:
The problem is that Design Studio apps are currently lacking an easy way of redirecting to a new app. Therefore some other technology that determines the user id (e.g. an HANA XS Engine “.xsjs” app, or any BIP based web technology) might be a good choice for that “pre-app” that finally does the redirect.
I just Join the discusion with a question regarding the concept of sharing variables between multiple views on HANA. And would like to know How to set the variables in such a case where we have 2 different views with the same Parameters?
Doing something like
indeed returns an error.
Is there a way to specified which Parameter belong to which view? like teh hypothetic:
the current version of Design Studio will simply apply the specified value of „APPLICATION.setVariableValue“ to all views the have a variable with the same name. If this value is technically valid for all of these views, then there should be no error.
However in current versions of Design Studio there is no way to set different values for different views (as in your example “4710” for one and “4711” for the other).
Future versions of Design Studio will be enhanced to support this. There are two alternatives (which might be provided both):
1) Setting different values via the data source API:
2) Setting different values via fully qualified name of the variable:
Is this <DS>.setVariableValue() available in Design Studio 1.4?
We really need it for some performance improvements as our query's use variables with user exists we cannot make use of SetFilter().
Thanks for replying.
the feature didn't make it into 1.4.
However we are just in the development phase for 1.5 and this has been one of the first things that has been implemented.
Thanks for your reply. Too bad not already available in 1.4!
Are you able to bring this feature with the first-next Servicepack in 1.4?
Unfortunately it is not planned to bring this feature into a 1.4 SP.
thanks for the useful architecture insight.
Unfortunately, there are issues with this approach:
a) Authorization relevant filters must be set in the global filters using variables. In a solution with many datasources this will create the performance issues you mentioned already.
b) Avoiding the performance using by loading datasources in background is not really feasible for dashboards with many datasources / tables / charts. The reason is that it requires a lot of scripting to get this right, it is very error prone and currently the table and chart component are even displaying wrong data (i.e. dummy data) in the runtime when the datasource is not initialised. There should be a simple option to load the datasource only when it is needed, which should be determined by the Design Studio engine depending on component visibility.
It would be great if this could be considered from an architecture point of view to increase user acceptance of the tool by improving both performance and designer usability.
you're absolutely right with both comments.
We'll try to improve on the background processing stuff. Considering the visibility (as you mentioned) might be a good approach. Thanks for the suggestion.
I have installed Design studio version 1.3 (Version 126.96.36.199405058. I am trying to set variable values based on a dropdown selection of Fiscal period for specific data sources defined within Design studio.
I am not able to find the functions SetVariablevalue as part of the script editor.
the functions "setVariableValue" and "setVariableValueExt" belong to the APPLICATION object.
Once you type APPLICATION into the script editor and then the "." (dot) a list of available functions pops up:
You can then choose the function you need (e.g. setVariableValue)-
I want the same variable to pull different Fiscal Period for 2 datasources based on different dropdown selection. With Application I can only set only one value.
Do you think SAP will provide DS specific SetVariableValue in the future support packs are versions?
currently this feature does not have a high priority and it will not be in the next version (1.4) of Design Studio. However subsequent versions of DS might include this feature.
Thanks for confirmation martin.
In DS 1.5 you can set different values to the same variable for different datasources provided you need to set "Merge Prompts" to false in the Application Properties.
Thanks for sharing this information.
In your example, COST_CENTRE variable is coming four times in the prompt dialog. My question is whether all the "COST_CENTRE" variables are same which are coming from different data sources(i.e all the variables are sharing same technical name) or each variable is having different technical name.
I am also facing the same scenario. In my case, Region variable is repeated twice in prompt dialog. I tried to apply variable container concept. Since the region variable's technical name is different for both, variable container concept is not working and We couldnt use same variables in all the queries, since each variable is coming from(created for) different infoproviders.
with BW data sources the variable container is activated automatically. As a Design Studio application developer you currently do not have control over the activation. The activation and merging is based on the same technical variable. So either the variables are the same in two (or more) data sources, then merging is performed. If they are not the same, then there is no way of merging them.
The screenshot that I was providing with the four COST_CENTER was just for illustrational purposes to show how things would look like without variable merging. This is currently not possible when using BW data sources.
So when you are really having different variables in your scenario there is no way to merge them. However I don’t understand why the different infoproviders use a different variable for the same semantic thing. This is a very useful feature of BW to share the same variable between multiple infoproviders. In that case you need to adapt the infoproviders on BW. Design Studio cannot help in the case.
Thanks for your reply. Now all my doubts regarding variable container is cleared.
As you said, we can create same variable for different infoproviders. In my case, I am not able to create same variable for two different infoproviders . One infoprovider is loaded with ECC master data and another one is loaded with transaction data and both the infoproviders are composte providers. Could you please suggest why I am facing this issue ?
unfortunately I have only limited expertise in this area. However I think it might be related to “Reference Characteristic” of variables. More on this can be found here (see item “4”):
Does this Variable Container only function for input-ready variables (with the default Variable Is Ready for Input option selected) ?
The variable container also works for variables that are not input-ready.
But what is the use-case where you need this?
I'm joining the party a bit later than others - sorry! This is such a great post, exactly what I need for my Dashboard. I have a question relating to the specific merging of variables from different datasources.
I have 1 datasource (A) that is repeated 4 times, for different views, and this has one one prompt in BEx: Select time period/year (multi selection).
I had to build 4 other queries (B-E) to mimic a cascading filter type of situation since this wasn't possible without SDK. These queries will have the same time prompt as above but I don't want them to merge with Query A. How do I in such that I only merge the other 4 prompts into one like you suggested in your article?
In other words, the user will be able to see only 2 prompts when the window pops up.
Thank you so much,
in the 1.5 version of Design Studio you would do these three steps:
1) Set the application mode to “Unmerged Variables”.
This would show the prompt for all five queries (A-E) by default
2) Then you would select the prompts that you would want to see. In that case you would choose A and B. This would result in the two prompts that you would want to get.
3) Finally you copy the value from prompt B to C through E via some lines of script:
var valueOfB = DS_B.getVariableValueExt(“VAR_NAME”);
Note that 1.5 will be available in Q2 this year.
Thanks for your reply. We're still working on 1.3 so it might be a while before I get this functionality! I'll work around it for now.
Hello Martin Kolb ,
Can you please tell, how we can handle with invalidate data.
Actually i have one prompt (same name) for every 7 BEx data source. when i am loading data the application all cross tab is getting invalidated apart from chart. Can i have some event where i can write any code to validate this. or some other method to get it corrected.
I have kept Merge Prompt= False
and setting Data source via
DS_1.setVariableValueExt("FISCAL_PERIOD", "003.2015"); in On Variable Initialization.
Design Studio version 1.5
Saurav Kumar Suman
thanks for using Design Studio 1.5 🙂
I think I didn’t understand your scenario good enough to give a thorough answer. I’ll try to answer based on what I understood.
Setting “Merge Prompts” to “false” disables the variable container in this application. So setting variable values in this mode only affects the data source you set the variable. In your example “DS_1.setVariableValueExt(...)” invalidates only DS_1, und therefore only affects components where DS_1 is bound to.
Also with “Merge Prompts” set to “false” there is of course a prompt screen appearing if there are unset mandatory variables or if the application has forces the prompt screen to appear.
Calling “setVariableValue” during “On Variable Initialization” does not invalidate anything because at that point in time the data sources have not yet been initialized at all. At that point in time “setVariableValue” only presets a variable value and that newly set value will also appear as value in the prompts screen.
Hope that helps.
I have 6 BEx data source, all have one mandatory prompt same name in all FISCAL_YEAR. I am setting all data source prompts individually in On Variable Initialization event and “Merge Prompts” set to “false”, and prompt screen is set to false.
Then i bind this with 4 Crosstab and 1 line & 1 bar chart. I am wondering that when reports getting loaded cross tab data is not shown on report even on click on chart it is not showing, after minimizing and maximize of web page data getting displayed. I tried loading all data using button press then it is OK. I want all data to be displayed hen i load the report.
Saurav Kumar Suman
that sounds strange. What do you mean when you say that the data is not shown. Is there an error message? Could you put a screenshot of the started application to your next reply?
Exactly data not showing, after Minimize & Maximize i.e browser window change data is showing immediately, so its not going back to BW.
After browser minimize & maximize .
Saurav Kumar Suman
Thanks for providing the pictures.
This is a bug that is in Design Studio 1.5 SP0. It is fixed in SP1 which is not yet available.
Shipment dates of Design Studio versions (SP's an mahor versions) can be found in SAP note 1760372:
This problem happens when the crosstab has a dynamic size depending on the browser window's size. As a temporary workaround you might consider giving these crosstabs fixed widths/heights until you get an updated version of Design Studio.
Hi & Good Day Martin,
Wondering if you could help me out in a scenario that i am facing.
I am building a dashboard, reading from multiple views from HANA. In each view I have included an input parameter which is passed to design studio.
In Design studio, a value from a dropdown list will pass the value that the user picks to the input parameter.
Now the thing is, because we have input parameters for all the views in HANA, the first time that we load the dashboard, we have to initialize all the views/datasource to the default value of the input parameter first then only load the first page of the dashboard.
With this, it takes awhile to load the first initial view of the dashboard, is there any other ideas/suggestions on how to work with HANA input parameters in design studio?
I think I didn’t completely understand your scenario, but let me give an answer from what I think I understood.
First of all there is no need to initialize all of data sources (HANA views) at startup. For many data sources it makes sense to put the “Load In Script” property to the value “true”. This enables loading these data sources at any specific point in time by using the “loadDataSource” function.
For some data sources it makes sense to use them directly at application startup. If you want the user to type in a value for their variables (input parameters) at startup, you can set the Design Studio application’s property “Force Prompts on Startup” to “true”. This shows a dialog where the user can enter values to be used at initialization time of the data sources.
Additionally you can use the event “On Variable Initialization” of the Design Studio application. There you can use “setVariableValue” or “setVariableValueExt” to provide values to be used at initialization time of the data sources. This scenario is typically used when “fixed” / “hard coded” default values shall be used in the Design Studio application that differ from the defaults provided in HANA Studio. Another use case for “On Variable Initialization” is the usage of values provided at startup of the application via URL parameters.
Hope that helps.
Thanks for your advice.
However i have already did like what you mentioned but my dashboard is running slow.
Not sure if you are the right person to ask or if this is the right place, wondering if you could kindly help to see my java statistics?
I did the profiling on my dashboard and manage to get the statistics, i noticed that this part always takes most of the time.
Create system service BICS_PROVIDER_QUERY_VIEW.
Do you have any idea how can i performance tune this part and where is this coming from?
Your help is much appreciated.
this might be related to a complex „Initial View“ of the data source.
Could you try (temporarily) to delete the initial view of the data source “FIN_DS_1” by choosing “Reset Initial View” from its context menu.
Of course the application will then will not show the desired data any more. But if it’s faster after resetting the initial view then it’s worth thinking about providing a variant/copy of the view HANA view directly in HANA and do the customizations that are done in the initial view directly in HANA studio.
Thanks for sharing.
I have a question.
In 1.5, if we set 2 data source to run parallel and, after they have been initialized, click a button to
In this scenario, they still have to run sequentially right?
the answer to your question (“they still have to run sequentially right?”) is „half yes“ 😉
To explain what actually happens, it helps to look at the blog Design Studio: Parallel Processing and Scripting.
The first thing to consider is that script lines are always executed sequentially. BUT the blog also mentions that there is a phase that participates in parallel processing, which is called “Submitting variables”. First, this phase does some final checking of variables values (cross-check between several different variables, for example), then it actually transfers the variable values to the “backend” (BW, HANA, ...). What happens in that phase can be actually quite time consuming, yet the good news is: This phase performs these steps for each group in parallel.
If you take a look inside a “profiling trace” you might want to search for the words “Check Values”. There you will find entries that show what is going on in your specific case: An example of such entries are:
37/1 ms: Execute Processing Groups asynchronously (Purpose: Check Values)
Processing of Group G1. Runtime: 35 ms, Java: 0 ms, Remote: 35 ms
35 ms: RFC BICS_PROV_VAR_SET_VARIABLES (10000)
34 ms: BICS_PROV_VAR_SET_VARIABLES: (-2)
Processing of Group G2. Runtime: 32 ms, Java: 2 ms, Remote: 30 ms
30 ms: RFC BICS_PROV_VAR_SET_VARIABLES (10000)
30 ms: BICS_PROV_VAR_SET_VARIABLES: (-2)
I was wondering if you have any suggestion when we use universe.
In my case, I have a universe with an relational connection, connected to R/3.
I've posted this thread:
Thanks in advance!
can you Vote for my request on influence.sap.com:
That request covers the prompt topic and gives further suggestions.