Does my original query execute even when not used in design studio charts?

[sumitdubey]

Dear Gurus,

We have got a huge query with almost 250 measures and 30 dimensions.

Our understanding is that if we bring this query in design studio and create multiple small initial views of the same query and use them to map with our layout then it would improve the performance.

But the loading time of the dashboard is still the same as using the original (huge) structure of the query and there is no improvement in our performance.

Should we split the original query and have small subset of the queries or should we still continue with the initial views?

Also, can we use initial views of the query for parallel processing if it is the same query?

Regards,

Sumit

[former_member193885 · Accepted solution]

When Design Studio request the data it will be based on what is set in the initial view of the datasource, if the datasource is defined to have only 2 dimensions and 10 measures it will only request for those. But if the 10 measures requested requires other measures for doing internal calculations or if there is Exception Aggregation defined which require reading the data at the most detail level it will have to get all the required measures and dimensions to get the required output.

So splitting the query alone wont help in your case. Try to run the query with the datasource required for design studio from RSRT2 and see whether the performance is comparable, make sure the dimension used in the datasource is same as the dimensions in the RSRT2 output. Also when you have multiple datasources from a Design Studio standpoint whether it is the same query or not does not matter, each of them are requested seperately from the backend unless you have Parallel processing ON.

See note below for more trouble shooting.

https://launchpad.support.sap.com/#/notes/0001931691

[former_member197738]

Hi Sumit,

To start with what version BO and DS business is running on?

What is your observation with performance statistics with current intake of 250M to 30D with in the query ? (Still in my my opinion its massive and will take performance hit)

Idea of one using single query is great but one major factor is the data. Do you know the row or amount of data this query will be pulling-in?

Regards,

Fahad

[sumitdubey]

Thanks Fahad,

We are using DS 1.6 SP4.

At the moment we are fectching almost 100 records but all the measures are Calculated Key figures.

Regards,

Sumit

[MustafaBensan]

Hi Sumit,

When you say "Our understanding is that if we bring this query in design studio and create multiple small initial views of the same query and use them to map with our layout then it would improve the performance.", this will not necessarily be the case. In fact I think it could even make performance worse depending on the structure of the different initial views.

To provide a better understanding can you provide the screenshots of each of the smaller Initial Views?

In answer to "Also, can we use initial views of the query for parallel processing if it is the same query?", as long as you assign different processing groups to the data sources I think you should be able to execute them in parallel. Have you tried this?

Regards,

Mustafa.

[sumitdubey]

Thanks a lot Mustafa,

You have answered to most of my queries. Apologies, but I couldn't share the screenshots of the initial views as they are from the client system.

I am in process of defining the parallel processing jobs. But could you please confirm, that if I define parallel processing on 4 initial views for example, based on one original query, then would that improve the performance. My understanding is,that even if we define parallel processing on 4 initial views based on one master query, then it will hit the same master query in parallel and there will not be any significant improvement.

Please correct me if this is wrong.

Thanks

Sumit

[former_member197738]

Hi Sumit,

I agree with @Mustafa copy/paste same master query and crafting layout from it would make it worse.

Your understanding is right. You wont gain much out of that.

Are you populating all data at once in your application in form of Chart, Scorecard or Crosstab visualization? If not then have you tried breaking the query into 4-5 potential views and then firing them up with script as per user navigation? using technique like background-processing, parallel processing or re-calling the query using script (on the fly dynamic query)?

Regards,

Fahad

[MustafaBensan]

Hi Sumit,

Deepu has provided some good insights in his answer above. Regarding your question about the effectiveness of parallel processing for your particular case, I think it is best to simply try it and then proceed accordingly based on the results.

Regards,

Mustafa.