In Developer, you create a data mart report by converting an existing report or by creating a new report.
Sample SQL
Same Data Warehouse Different Data Warehouse drop table Same_ Datamart _Instance
create table Same_ Datamart _Instance (Month_Id INTEGER, Month_Desc
join LU_MONTH a12 on (a11.Month_Id = a12.Month_Id)
on (a11.Month_Id = a12.Month_Id) group by a11.Month_Id
drop table Different_ Datamart _Instance
create table Different_ Datamart _Instance (Month_Id INTEGER, Month_Desc VARCHAR(100), DOLLARSALES FLOAT)
insert into Different_ Datamart _Instance values 201201, 'Jan 2012', 8817)
To create a data mart:
1 In the Report Editor, on the Data menu, select Configure Data Mart.
The report template must contain an attribute, a metric, or some other object for this option to be enabled.2 In the Report Data Mart Setup window, on the General tab, in the Data mart database instance drop-down list, select the database instance in which you want to create the data mart table.
3 In the Table name box, type the name of the data mart table you want to create.
The table name you type is not validated by the system at this point.By default, the This table name contains placeholders check box is selected.
The selection of this check box enables you to specify whether the data mart table uses placeholders to name the table. Placeholder names enable you to modify table names dynamically.
The following table lists placeholders available for naming data mart tables .
Data Mart Placeholders
Placeholder Replacement Option
!U user name
!D date on which table was created
!O report name
??? temporary table name
!!! all column names
!a attribute column names
!j job ID
4 Select one of the following options:
• Create a new table
• Append to existing table—This option enables you to add the data mart report results to an existing table. The name specified in the Table name box must be the name of the existing table you want to append.
5 On the Advanced tab, specify data mart governors and table creation properties.
6 On the SQL Statements tab, specify SQL statements that can be inserted before and after the table is created or before data is inserted in the table.
For information on the data mart governors, table creation properties, and SQL statements refer to the Advanced Reporting Guide product manual.7 Click OK to close the Report Data Mart Setup window.
You may see a warning that data mart tables created in common table spaces may overwrite someone else’s data mart table. If you want to proceed, click OK.8 Save the report.
9 Execute the data mart report to create the data mart table.
10 Update project schema.
For example, using the Forecasting Project you can create a data mart report that contains the Region and Year attributes and the Forecast Units Sold metric, as shown in the image below:
Data Mart Report Definition
You can then convert this report into a data mart. The following image shows the Report Data Mart Setup Window with data mart report configured as REGION_YEAR_FORECAST_UNIT_SALES table in the Forecast Data database instance:
Report Data Mart Setup Window
The following image shows a message displayed by the data mart report when it is executed:
Data Mart Execution Complete Message
Data Mart Options
A data mart table has the same structure as any other data warehouse table. By default, it contains columns corresponding to all attribute forms and metric columns present on the report template.
You can control the structure of a data mart table in the following ways:
• You can control what attribute columns are included in the data mart table.
• You can determine the names for the columns that contain the metric calculations.
Attribute Columns
A data mart table contains an attribute ID column for each attribute selected in the data mart report. Additionally, depending on the default display for each attribute in the data mart report, the data mart table can also include attribute description columns.
Generally, you would remove any non-ID form descriptions from the data mart report display to avoid storing duplicate attributedescriptions in the data mart table.
Consider the data mart report from the previous example that has the Region and Year attributes and the Forecast Units Sold metric on the template.
Assuming that the default display for the Region attribute is ID and
description, and for Year is ID, when the data mart report is executed, the data mart table contains the following columns:
• REGION_ID
• REGION_NAME
• YEAR_ID
• WJXBFS1
If you do not want to include attribute description columns in your data mart table to improve query performance, you must modify the attribute display and forms available in report objects for each attribute in the data mart report.
To modify the attribute display in a data mart report:
1 In the Report Editor, on the Data menu, select Attribute Display.
2 In the Attribute Display window, in the Attribute drop-down list, select the attribute whose display you want to modify.
3 Under Select one of the display options below, click Use the following attribute forms.
4 In the Available forms list, select the ID form.
5 Click the upper > button to move the ID form to the Displayed forms list.
6 In the Displayed forms list, select all non-ID forms.
7 Click the upper < button to remove the non-ID forms from the report display.
8 In the Report objects forms list, select all non-ID forms.
9 Click the lower < button to remove the non-ID forms from the report objects.
10 Click OK.
The following image shows the Attribute Display window with the Region attribute configured to use only the ID form:
Attribute Display Options
Using the previous example, after you change the display for the Region attribute from description to ID only, and then execute the data mart report, the data mart table contains the following columns:
• REGION_ID
• YEAR_ID
• WJXBFS1
Metric Column Alias
A data mart table contains a column that corresponds to each metric selected in the data mart report. These columns, created from metric calculations, become the fact columns.
By default, the alias generated for a fact column in a report SQL is
WJXBFS<n>, where n is a number. The first metric alias MicroStrategy Engine creates for a report is WJXBFS1, the next WJXBFS2, and so forth.
If you want to use a different name, you can create a column alias for the fact column that contains the metric calculation.
You specify the column alias in the Metric Editor of the metric on which the column is based.
To name a fact column in a data mart table:
1 In the Metric Editor, on the Tools menu, point to Advanced Settings and select Metric Column Options.
2 In the Metric Column Alias Options window, in the Column Name used in table SQL creation box, type a name for the metric column.
3 In the Data type drop-down list, select the data type and, if appropriate, define other relevant parameter setting(s).
4 Click OK to close the Metric Column Alias Options window.
5 Save and close the metric.
6 Update project schema.
The following image shows a custom Total_Unit_Sales column alias for the Forecast Units Sold metric:
Metric Column Alias Options Window
Using the same example, after you change the column alias for the Forecast Revenue metric, and then execute the data mart report, the data mart table contains the following columns:
• REGION_ID
• YEAR_ID
• Total_Unit_Sales