How to prevent Microstrategy from pulling all data

Question

We are building a dashboard with many reports. The relationship between tables is defined in microstrategy. We found that Microstrategy is not using different SQL for different reports. It is pulling all the data from Database(which is 46 million) and then applying post processing on those data to generate individual reports. This is taking lot of time and it is not using the query engine of the database. How can we configure microstrategy so that it generates different query for different reports and collect only the required data for a particular report and NOT all data.

One way to do that is to use fre form SQL. But we want to have the capability for drag and drop kind of reports. How can we achieve this? We are using Microstrategy 10.1

Answer 1

From your description it sounds like Microstrategy is first pulling all data (46 million records) from the DB using its SQL Engine and then applying filtering after this.

If your reports have been created in Microstrategy developer (or web) using attribute filters then each report should correctly execute sql that has explicit where conditions that translate to those attribute filters. e.g. if you have a report with an attribute titled 'Fruit' and you want to only display apples, then you would have an attribute filter on that report that only displays results where 'Fruit' = 'Apple'. This would translate to a where condition in the SQL engine when the report is executed. However, if you are applying a view filter to the report, then the SQL engine will first obtain everything and then filter the entire dataset in the analytical engine, which would be slow especially if there are multiple reports running on the dashboard.

It's important to know how you are bringing the dataset into the dashboard - is it using a cube as a dataset, or a report, or? There are a few ways of achieving the performance you are looking for, here are a couple:

• Option 1: Develop each report in Microstrategy developer using attribute filters as desired. This would require that you have all your attribute relationships defined correctly.
• Option 2: Have all your 46 million records pulled into a cube. Use the cube as the dataset for the dashboard and then use view filters however you want on the various reports you want to place on the report.
• Option 1 + 2: You can combine both of the above options if you wish. Store entire dataset in cube, define several reports (normal reports, not cube reports) that can dynamically source from cube, using filters as required, and then add these reports into your dashboard.

These are the things I would do as first steps:

1. Check your attributes and attribute relationships are defined and work
2. Create a test report and try to filter based on one of these attributes
3. Try to create a few reports, each with different filter conditions based on one of the attributes
4. Put these reports into the dashboard and see whether each one generates different SQL statements.

Answer 2

This sounds like you have either:

1. built the reports using view filters (which apply filtering post query execution) rather than applying filter in the generated SQL, or
2. you don't have attribute relationships defined, such that the system doesn't think the filters you've defined aren't relevant to the fact tables containing the data.

Answer 3

Are you using cubes? I am assuming that what you mean by executing the query once.

You need to replace the the individual reports with new report- regular report- not the ones made out of cubes. Thats the only way