Multiple SQL statement for each measure - Does it impact Query performance of Webi report?

[Former Member]

Hi Everybody,

My question is: when we enable "Multiple SQL statement for each measure" in Information Design Tool for .unx file then does it impact query performance of Webi report? We are having multiple context and after enabling it, measure objects from separate contexts are possible to be used together in a single report.

Appreciate your valueable suggestion.

Regards.

Anshuman Kumar

[mhmohammed · Accepted solution]

Hey Anshuman,

As pointed, you're bringing measures from multiple context, there are 2 ways to do it.

1. Create 2 Data Providers (queries), 1 for data from each context
2. Enable Multiple SQL Statement for each measure

In both the above cases, it will run 2 different SQL statements and bring the data in the report, there wont be much difference in the Query performance.

Issues with 1st scenario:

I have seen some performance issues in the report when you bring in lot of data from the multiple data providers and merge in the report.

M Mohammed

[Former Member · Accepted solution]

Hi Mahboob,

Thanks for your reply. Yes you are right, option-1 is other way of doing it. But in my case universe is for adhoc reporting so option-1 is ruled out. I couldn't find any document which mentions about the performance impact when we enable this option, so started out this discussion to get views from other users.

Regards,

Anshuman Kumar

[Former Member · Accepted solution]

Hi Anshuman,

Yes this option can degrade your query performance.

Think of a scenario, if there are two tables one is dimension and another is fact.

In fact table you have two measure columns, which you want to use in your report.

Do you think there should be two sql's to be fired to database to bring this data. I believe no, this can be taken in single pass. Here this option is just a bottleneck.

This option is good only when the measures are coming from different tables and are creating kind of scenarios.

Thanks

Gaurav