Improving the Performance of Data Mart Reports in MicroStrategy

Jay KakkadJuly 6, 2016

Improving the Performance of Data Mart Reports In MicroStrategy

If you work regularly with data marts in MicroStrategy, you know how frustrating it can be when it takes hours to execute a report. Fortunately, there are settings that can be tweaked to improve the speed and performance of these reports. By taking a few simple steps, you can reduce the time it takes to load hundreds of millions of rows of data from hours—to just a few minutes.

 

Why use data marts?

A data mart is a mini data warehouse, or a subset of data derived from a primary data warehouse, which is typically stored in the same database server as the warehouse. Each data mart table is associated with one report via the MicroStrategy Desktop interface.

 

Data marts have a variety of business uses. Because each data mart table is associated with one report via the MicroStrategy Desktop interface, data marts can be tapped to create tables in a database that can be used for running “what-if” scenarios. They are also used to build tables for third party tools, and for building a smaller, portable database for Online Analytical Processing (OLAP) analysis.

 

Tips for improving performance

While a data mart can be stored in the warehouse or in an alternate database server, it’s best to use the existing warehouse database in order to improve execution time and performance.

 

While creating DSN for pointing target schema, used for storing data mart tables, it’s very important to set your maximum response buffer size to a higher value in your DSN.

 

To do this, go to ODBC Data Source Admin and click on the “configure” option against the newly created DSN. Then: Click on options; then click on “advanced” in this window; and finally change the value for maximum response buffer size from the default (65536) to 1048575. Once that’s done, be sure to save before you close the window.

 

Next, while creating a database instance on top of your data mart DSN, be sure to configure data mart optimization. Do this by: right clicking on the project and selecting project configuration; then select the “database instances” section. Next, select the newly created data mart database instance and click on “modify.” This will cause a new window to pop up. In this new window, go to the “advanced” tab.

 

In the data mart optimization section, select the checkbox: “This database instance is located in the same warehouse as,” then select the warehouse database instance (used by other MicroStrategy reports to fetch data from DW).

 

In order to improve the performance of your datamart reports, it’s important to set Modify Table Creation VLDB properly at the database instance level so that it applies to all data mart reports in that project. The default value is: explicit table. You need to uncheck the “use default inherited value” checkbox and select “implicit table.”

 

Once you’ve taken these steps, go through the following checklist to ensure the optimal performance of your data mart report:

 

  • Check if the dataset is a graphical report or grid. If a graph, then convert it to grid.
  • Add all report objects to the template. Nothing should be missed.
  • Move all the attributes to rows and ensure all forms are selected for display.
  • Ensure that view filters and other physical filters are removed from the report (so that complete data is fetched).
  • Remove derived metrics as well, since these are not supported by datamart reports.
  • Ensure that metric and attribute joins are exactly similar in the original report and its corresponding data mart report.
  • Ensure that VLDB properties applied in the original report are also applied in its corresponding data mart report.
  • Check the format of data mart SQL. It should generate “create table as (select * from)” structure (refer Modify Table Creation VLDB property for details)
  • Lastly, confirm that the original report SQL and its data mart’s SQL is similar. Ignore the filters (if any) in original SQL while comparing. The count of rows should also be same.

By taking time on the front end to ensure that all your settings are optimal, you can dramatically reduce the time it takes to execute your data mart reports.

 

If you run data mart reports and are searching for more efficient ways to execute said reports, contact us. We are happy to demo this utility or to discuss developing another solution for your requirements.