Best Way for BI dash board drill down

What is the best way of MDX querying for each drill down of BI dash board c开发者_开发问答hart? as a example if you have four drill level every drill down we should execute four MDX query or execute only one query in the initial time, and keep all data of four drill levels in object collection. If you can please explain with a example.

This depends a lot on what tool you are using to display the BI Dashboard. Is it SSRS, PerformancePoint, something else?

1. Pull all the data in the initial MDX query, configure the Dashboard software to display the top level of detail and provide user with options for drilldown. As users drilldown, unhide the next level of detail. This option only requires 1 roundtrip to the database. So intially loading the dashboard may be a bit slower, but drilldown experience will be very fast (since the data has already be retrieved).

2. Pull just the top level of detail in the initial MDX query, configure the Dashboard software to display results and provide users with options for drilldown. As users drilldown, Dashboard software will send another MDX query to retrieve the next level of detail from your data source. This option will require multiple roundtrips to the database...one for the intial top-level of detail when the user first loads the dashboard, and another for each time the user drills down.

Either option will work but you'll need to make the call on which option best suits your needs after weighing the pros and cons...

• how fast is the network between your dashboard and the datasource?
• how much concurrency can your data-source handle?
• how "big" is the query to pull everything?
• how important is speed to your users?

be sure and test each if you are unsure.