MicroStrategy ONE

About data mart reports

A data mart is a data repository where you store the results of a report as a relational table in a data warehouse. After creating a data mart, you can use it as a source table in your projects, and execute reports against it. You can use data marts for the following applications:

  • Create aggregate fact tables to improve the performance of reports that have summarized data.

  • Create tables for use by other applications, such as Microsoft Access.

  • Create tables for off-line analysis using direct SQL queries.

Before you can create a data mart table, you need to create a data mart report, as described below.

Prerequisites

  • You must have Developer Designer (or broader) privileges to access the Report Data Mart Setup dialog box.

  • Before you can create a data mart table, you need to create a data mart report.

To create a data mart report

  1. Open the Report Editor. (How?)

  2. Create and save the report to be used as the data mart report. (How?)

    This link offers generic instructions on creating a report. It does not describe a data mart report specifically, since you must design a report to provide the information required for your data mart table.

  3. Identify the report as a data mart report by following these steps:

    • From the Data menu, select Data Mart, then select Configure Data Mart. The Report Data Mart Setup dialog box opens.

      If the report contains any of the items listed below, a message opens instead. You can select whether to automatically correct the problems or return to the Report Editor.

      • View filter

      • Report Objects, including attribute forms, that are not displayed on the template

      • Derived metrics

    • If it is not already selected, choose the General tab.

    • Use the Data Mart Database Instance drop-down list to select a database instance for the data mart table. The data mart table will be stored in this space.

    • In the entry window next to Table Name, enter the name of the data mart table to be associated with the database instance you have specified.

      The table name may contain the following placeholders, which are filled at run time:

      Placeholder

      Replacement

      !u

      User name

      !d

      Table creation date

      !o

      Report name

      !t

      Timestamp

      !p

      Project name

      !j

      Job ID

      !r

      Report GUID

      !z

      Project GUID

      !s

      User session GUID

    • Select whether to Create a new table or Append to an existing table.

    • Select the This table contains placeholders check box if applicable.

  4. Set the execution time limits for creating the data mart table (optional). By default, the Maximum execution time is set to zero, which defaults to the setting at the project level, and the Maximum number of rows to 65,536.

    See Setting execution time limits for a data mart report.

  5. Set the table creation properties for the data mart report, which govern the creation parameters for the CREATE TABLE statement.

    These optional parameters include the Table Descriptor and Table Prefix, among others. See Setting table creation properties for a data mart report.

  6. Enter custom SQL statements for pre-creation or post-creation tasks (optional). See Entering custom SQL statements for a data mart table.

    You can only see this option if you have the Use SQL Statements tab in Datamart/Bulk Export editors privilege in Developer Designer privileges.

  7. Click OK. Your changes are saved and you are returned to the Report Editor.

  8. Execute the report to create the data mart table. (For steps to run a report, see Running a report.)

Related Topics