[MSTG] Creating A Data Mart Report

 

Creating a Data Mart Report

Data Mart report is a feature in MicroStrategy which allows a report to insert the results set into a table which can be created  on any relational database. MicroStrategy objects can be built on the table (created by data mart report) for reporting needs.

Create a new report or modify an existing report to configure a data mart report.

Step 1 : Open the report editor and click on "Configure Data Mart" [Data > Configure Data Mart]

Step 2 : The "Report Data Mart Setup" wizard opens. Select the database instance you wanted to create the table in and enter a preferred name for the table.

Table name can contact place holders. "!D" will display the date when the table was created. Below are the four place holder that can be used.

!U - Username
!D - Date
!O - Report name

Place holders are case sensitive and must be entered in capital letters.

Step 3 : Check the box which says "The table name contains place holders" (if there are any place holders in the table name).

The option "Create a new table" should be selected if it is required to drop and re-create the table every time the data mart report is executed.

The option "Append to existing table" should be selected if it requires to append the data to the table which is initially created (at the first run).

Step 4 : The "Advanced" tab has options for governing settings. The "SQL Statements" tab will provide an option to enter pre and post SQLs.

Note : If the metric names are required as column names of the datamart table. 

VLDB properties are to be modified in the Project Configuration. [Project Configuration > Database Instances > VLDB Properties > Metrics]

Step 5 : Click OK and save the data mart report.

Step 6 : Right Click on the data mart report and click on "Schedule Delivery To"

Step 7 : The wizard will open and preferences for schedule, group/users, etc. can be set as shown below.

In versions prior to 9.x, this is slightly different. Scheduling will be available under properties (Right click and select Properties).

While creating a Data Mart report -

There should not be any derived metrics and view filters in the report.
No objects in the report object pane which are not in report display.
All forms of an attributes should be available for display in the report.

Note :
Location of the data mart report could be any relation database.
Governing setting in the data mart report over rides the project default settings.
VLDB Properties can be modified to change the CREATE TABLE syntax.