Sample business scenarios - Microstrategy

Possible data marting applications include

  • Application subsetting, which uses MicroStrategy scheduling capabilities to create and periodically refreshlookup and fact tables as subsets of a central data warehouse. For this type of data mart application, projects are built using MicroStrategy Architect.
  • Warehouse building, through which you can generate warehouse tables and modify existing schemas to enhance query performance. With this type of application you can, for example,
    1. build a report template layout to create a report that yields an aggregated result
    2. create a data mart report using the template you have created
    3. add the report to a schedule to generate a data mart table
    4. add the generated table to the metadata
    5. use the schedule created above to refresh the table at either time- or event-driven intervals
  • Third party tool integration, which uses ROLAP and MicroStrategy Desktop functionality to extract data from a massive warehouse and build a result table suitable for loading onto third party tools. This capability allows you to create a single table to feed a mass-mailing software tool or a similar closed-loop application, for example.

The primary purpose of data marting is the creation of relational tables that can be used and updated in the same manner as those in a project schema. Data mart tables are created from the information in the columns and rows of reports that are selected or created for this purpose.

To create a relational table for data marting

  1. Either create a new report or select an existing one to use for table creation.
  2. Using the Report Editor, designate the report as a data mart report.
  3. Set the relevant properties, such as table creation location, table name, VLDB properties, governing parameters, and so on, for the report.
  4. Execute the report.
  5. Resolve any prompts included in the report.

MicroStrategy then creates the data mart table in the database you have selected. When table creation is complete, the system returns a message that includes the table name and notification that table creation was successful.

For table and metric alias naming, ensure that the table and metric alias names follow the naming convention rules for your particular database platform. You will receive an error if you do not use a valid table or metric alias name.

The output of data mart reports: relational tables

When the contents of a report have been used as input for data marting purposes, the result is a table located in a relational database. Data mart tables possess all the characteristics of other data warehouse tables, and have, therefore, the same requirements for creation, including

  • A name—This can be any name you want to assign to the table. When the system notifies you that table creation was successful, it includes the table name in the return message. You can select whether the data mart table uses a placeholder as part of the table name. Placeholders allow you to modify table names dynamically, according to need or convenience. Placeholders available for naming data mart tables are as shown as below.
  • The output of data mart reports: relational tables

    Any character other than D, U, or O, invalidates a placeholder and causes it to be deleted from the data mart table. Placeholder characters are case-sensitive; they should be entered in all-uppercase.

  • A location—A data mart table can be located in any relational database. You specify the table location by selecting the database instance in which you want the table to be created. It can be created in the same database as the warehouse or in a different platform altogether. These conditions apply to your selection of data mart table location:

    If the data mart database is in the same physical location as that of the warehouse, the results do not need to be brought to the server for placement.

    If the data mart database is in a different physical location from that of the warehouse, the results set needs to be brought to the server for insertion into the new platform.

  • VLDB properties—These govern creation parameters for the CREATE TABLE statement. The syntax of these statements follows. The pre-SQL statement set is shown with the VLDB properties in italics.

  • Column name aliases—Attribute columns can have user-defined names in a data mart table. Attribute column aliases are specified through the Attribute Editor.
  • Governing parameters—These specify maximum values for execution time, and for the number of rows to be sent to the Analytical Engine. The maximum row setting applies only to a data mart report that calls the Analytical Engine, such as using the function runningMax in a metric definition.

The settings at the data mart report level always override the settings at project level. By default, the Maximum execution time is set to ‘0’, which means that the project level setting will be used. The project level setting is available in the Governing category of the Project Configuration Editor. For example, if the maximum execution time at data mart level is set to '0', and the setting at the project level is set to 10 seconds, the report will timeout after 10 seconds. But, if you set the maximum execution time as 5 seconds, and the project level setting is specified as 10 seconds, the data mart report level overrides the project level settings, and the report will timeout after 5 seconds.

Pre- and post-SQL statements allow the application of user-defined SQL before and after the creation of data mart tables. Possible uses include GRANT privileges and index creation.

Pre- and post-table creation settings apply to data mart tables only; they do not affect settings generated to process a report.

Custom groups in data mart tables

Since custom groups do not exist as columns in the warehouse, when a report includes a custom group the resulting data mart table contains data that does not directly map to corresponding columns in a warehouse table. For this reason, data derived from custom group columns is handled differently.

This figure shows how columns appear in data mart tables created from reports that include custom groups.

Custom groups in data mart tables

In a table structured as shown:

  • The Element ID column contains the IDs of the custom group elements as generated by the engine.
  • The Element Name column contains the descriptions of custom group elements.

The figure that follows shows part of a sample table that includes custom group data.

part of a sample table that includes custom group data

In this example,

A points to the element-ID column as it appears in the data mart table. Element IDs (1, 2, 3,...) are extracted from the corresponding custom group elements in the report.

B points to the element names as they appear in the data mart table. These names are extracted from the corresponding custom group element names in the report.

Similarly, when a report includes columns that reflect consolidations, the SQL Engine provides element ID valuesfor the data mart table. In such cases, the table structure looks like the following.

table structure looks like

In a table structured as shown:

  • The Element_ID column contains values provided by the engine.
  • The Element_Name column contains the names of consolidation elements.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Microstrategy Topics