There has been a lot of talk about DS apps being slow. To determine whether the bottleneck is on the backend or the front-end is simple but to determine what particular element in the application is causing the delay in the backend requires a little digging. Here we will check out the process of looking under the hood and finding out.
The first step would be to use the Profiling option when running the DS app by using the document link. This can be obtained by going into the Launchpad and right-clicking the DS app and selecting the Document link. You will append the profiling option (&PROFILING=X in the URL). When executing the application using this link, it will pop-up a window when the DS app opens up;
Let’s sum up the DS and data source execution time (as given above) in excel and visualize it;
Now we can clearly see close to 90% of the DS startup time is spent on fetching the data from BW in this example.
Let’s gather details from the backend (SAP BW) and see what’s going on. Login to BW and jump over to RSTT and activate the backend tracing.
When you land in RSTT, click on the User Activation button.
After you click on User Activation button – now click on Trace Tool button on the left;
Now you start up a new session of the DS application from the Launchpad – You may have some popup messages in DS;
Close the popup shown above – so you could see as shown below.
Now go back to RSTT and click on Traces. Here you find the Trace ID listed which contains your tracing details for your DS application’s interaction with the BW. Here click on the Display button.
After you click on the Display button, you see the list of all BICS function calls from DS along with their runtime (in milliseconds).
Now click on the icon on the left of the Sequence Column to highlight all the rows then right-click on the icon to Copy Text. You might get a popup message saying Not Alla Data Has Been Copied but that’s ok as you will find out what’s not copied when you paste this in Excel
Now open up Excel and paste the copied data starting from cell B1 and then type Startup in cell A1. As you notice only 68 rows were copied. You go back to RSTT and copy and paste the missing rows below the last row in excel.
Now you go back to DS app and perform another action such as filtering on a specific dimension
After the DS has filtered the data, go back to RSTT and refresh the trace by clicking on the refresh icon and you will see more rows appear in your trace after row 69. Copy the rows below row 69 and copy and paste in excel
Now paste these rows in excel below the last row and mark the cell 70 to identify what action was performed like filtering on Grocery in Column A. Now you have 82 rows
Now I perform another DS action like expanding my object hierarchy down to level 6.
I now go back to RSTT and refresh the trace and copy all rows below 82 and paste them in excel.
Now I collapse the object hierarchy in my DS app
I go back to RSTT and copy the new rows
And paste them in excel below the last row and label it “Collapse” in Column A.
I can perform any action in DS and grab the new BICS calls from RSTT and paste them in Excel accordingly. Once I am done I go to Excel workbook where I pasted all the rows from RSTT and create a new sheet and insert a pivot table and select the rows and columns I want to analyze. Before you do this first fill in the labels in column A for all the rows like this;
I would then have proper column labels and add a new calculated column to convert the runtime column which is in milliseconds to seconds by adding a new column (column G) in the sheet, like this;
Then you create the pivot table in a new sheet that would be based on this data like this;
This table gives me total runtime in secs for each of the steps (Startup, Grocery, Expand, Collapse) as well as the breakdown of each BICS call.
I can also create another pivot table to look at
Or better yet…moving Action to columns and ProgramModule to rows.
And if I collect the Design Studio stats using the profiling option, I can plug in those numbers next to the BW stats and get a nice chart, you can visualize (and show to your colleagues in BW) where the lag is;
It’s time to call the BW guys and tell them to tune the queries. For an in-depth look at DS tracing, checkout Karol’s blog on the same topic;
I know there are other easier ways this could have been accomplished but I tried to keep it simple so even a novice can follow the steps.