Data Mart Reports in Microstrategy

Creating Data Mart Reports in Microstrategy  

When there is requirement to store all the report results to a database table you can use the interesting feature in Microstratgey called Data Mart Reports.

To create a data mart table, you first create a data mart report that defines the columns of the data mart table. You then create the data mart table and populate it with data.
The steps below walk you through the process of creating a data mart report and then executing the report to create a data mart table. The steps also include an example for most steps, based on Tutorial sample data in the MicroStrategy Tutorial project.

Follow the simple steps below to create a datamart report:

1

In MicroStrategy Developer, create a new report or select an existing report to use as the data mart table. The report should contain the attributes, metrics, and other objects that you want to use as columns in the data mart table and which will populate the data mart table when the data mart report is executed.

Your report cannot be used as a data mart if it contains any of the following:

—

View filters

—

Report Objects that are not included in the template

—

Derived metrics

•

For this example, use the sample Tutorial project to create a new report with Customer Region as the attribute and Revenue as the metric.

2

From the File menu, select Save, and select an appropriate folder in which to save the report.

•

For this example, save the report with the name My_Report, in a folder of your choice.

3

To use the report as a data mart report, from the Data menu, select Configure Data Mart. The Report Data Mart Setup dialog box opens, as shown below:

4

On the General tab, from the Data mart database instance drop-down list, select a database instance for the data mart table that will be created. The data mart table will be stored in this space.

•

For this example, choose Tutorial Data.

5

In the Table name field, type a table name that you want to associate with the database instance that you specified. This table name must be compliant with the naming conventions supported by your database.

The table name that you enter in this tab is not validated by the system. Ensure that the table name follows the naming convention rules for your database platform. If you do not use a valid table name, an error message is displayed.

•

For this example, name the table AGG_REG_REV.

6

To use a placeholder in the table name, select the This table name contains placeholders check box.

Placeholders allow you to modify table names dynamically according to your needs. The available placeholders for data mart table names are listed in the following table:

Placeholder	Replacement Options
!u	User name
!d	Date on which the table was created
!o	Report name
!j	Job ID
!r	Report GUID
!t	Timestamp
!p	Project Name
!z	Project GUID
!s	User session GUID

If you use a placeholder character other than those listed in the table, the placeholder is deleted from the table name.

For this example, disable the This table name contains placeholders check box.

7

Select whether to Create a new table or Append to existing table, described below:

•

Create a new table: Select this option to replace the existing table each time the data mart report is run. The SQL statements drop and create the table each time the report is run.

•

Append to existing table: Select this option to add the data mart report results to an existing table.

•

For this example, select Create a new table.

8

If you need to specify governors, table creation settings, and custom SQL for table creation, see Specifying governors, table creation options, and custom SQL statements

9

Click OK.

Create the data mart table

10

Execute the data mart report. MicroStrategy creates the data mart table in the database you selected.

When the data mart table is created, the system displays a message that includes the data mart table name and a notification that the data mart table creation was successful, as shown in the example message below: