A data mart is a relational table containing results of a report. You create the data mart report in Developer and save the data mart table in a warehouse of your choice. After you create a data mart table, you can add it to a project and use it as a source table.
Common applications for data marts include:
• Creating aggregate fact tables
• Creating tables for very large result sets and then using other applications such as Microsoft Excel or Microsoft Access to access the data
• Creating tables for off-line analysis
In this lesson, you will use data marts to create aggregate fact tables.
You can use data marts in other usage scenarios. Combining data marts with MicroStrategy data mining features or with Freeform SQL reports are two such scenarios.For example, consider the following scenario:
Base Fact Table
In this example, forecasting data is stored at the employee and date level in the FORECAST_SALES base fact table. However, you want to report on the
Forecast Unit Sold at the Region level. This requires three joins from the fact table to the LU_REGION lookup table. In addition, the FORECAST_SALES table may have millions of rows. This query may be very costly, especially if users request it often.
What if you could create an aggregate table that limits the number of joins and the number of rows in the fact table? You can achieve this by creating a data mart table. You can then bring this table into your project, map the Forecast Unit Sales and metric to it, and have your region-level reports automatically use it, as shown below:
Aggregate Fact Table Created as Data Mart
Data Mart Objects
Creating data marts involves creating two objects:
• Data mart report—This is a metadata object that you create in the Report Editor. When executed, the data mart report creates the data mart table in the warehouse of your choice. The data mart report contains attributes, metrics, and other application objects that translate into columns in the data mart table.
• Data mart table—This is the relational table created after the execution of a data mart report.
Data Mart Database Instances
When you create a data mart report, you must specify a database instance in which to create the data mart table.
You create a data mart in a database instance in one of the following ways:
• Option 1—Use the project’s primary database instance.
• Option 2—Use a secondary project database instance that exists in the same warehouse as the primary project database instance.
• Option 3—Use a different database instance than the project, and one that is in a different warehouse than the primary project database instance.
The following figure illustrates each of these data mart database instance options:
Data Mart Database Instance Options
If you use the primary project database instance, then you do not need to take any additional steps to create a data mart. You simply select the primary data mart database instance as a target when you create the data mart report.
If you plan to use a secondary project database instance, then you must create that database instance before creating the data mart. You then associate this database instance to the project in the Project Configuration Editor.
For instructions on how to associate a secondary database instance to a project, see “To associate a secondary database instance with a project:”starting on page 30.
Data Mart Optimization
When you associate a secondary database instance to a project in the Project Configuration Editor, a message window displays prompting you to configure data mart optimization:
Data Mart Optimization Warning Message
This message does not display if you have enabled data martoptimization for the data mart database instance before you associated this database instance to a project.
When you click Yes, the Database Instances editor for the data mart database instance opens with the Advanced tab automatically selected.
Data mart optimization occurs when you create a data mart in the primary project database instance or in a database instance that points to the same data warehouse as the primary project database instance.To optimize a database instance:
1 In the Database Instances editor, on the Advanced tab, under Data mart optimization, select the This database instance is located in the same warehouse as check box.
If the data mart database instance does not reside in the same warehouse as the project database instance, do not select this check box.2 In the list of database instances, select the primary project database instance.
3 Click OK.
The following image shows the data mart optimization option for the Forecast Data database instance residing in the same warehouse as the Tutorial Data:
Data Mart Optimization Option
Why Optimize?
The following table displays sample SQL generated when creating a data mart using a database instance in the same data warehouse as the primary project database instance, and when using a different data warehouse:
When you create the data mart in the same data warehouse, MicroStrategy Intelligence Server creates the data mart table in the project warehouse and then inserts the result data rows directly into the table.
When you create the data mart in a different data warehouse, MicroStrategy Intelligence Server extracts the results from the project data warehouse with a SELECT statement and brings the result set into the Intelligence Server
machine’s memory. It then creates the data mart table in the different data warehouse and inserts the results.