After you create the data mart report and execute it, the data mart table (with report result set) is created in the data warehouse. This table is like any other physical data warehouse table.
To use a data mart table as a source table in the project in which the data mart was created, you must first add the table to the project, then update the
appropriate fact, and finally update the project schema.
Adding the Data Mart table to the Project:
1 In Developer, on the Schema menu, select Architect.
2 On the Warehouse Tables pane, expand the Forecast Data. You can now see the data mart table.
3 Right-click the data mart table and select Add Table to Project.
4 The Results Preview window shows attributes and facts that will be created.
In the Results Preview window, in the Fact tab, clear the check box for the facts.
There might be scenarios where you want to keep the facts. If you want to create facts, ensure you select the appropriate fact check box in the Fact tab of the Results Preview window.5 Click OK.
6 On the toolbar, click Save and Close.
The following image shows the REGION_YEAR_FORECAST_UNIT_SALES table added to the project:
REGION_YEAR_FORECAST_UNIT_SALES Added to the Project
To update the fact expression:
1 In Architect graphical interface, edit the fact that is used in the metric of the data mart report.
The relationship of the metric to the fact on which it is based is referred to as a child dependency. In Developer, you can useMicroStrategy Object Manager to quickly locate child dependencies for the metric.
2 In the Project Tables view tab, locate the table that contains the fact and right-click to edit the fact on which the data mart metric is based.
3 In the Fact Editor, create a new fact expression that uses the data mart table as a source table.
If the fact column in the data mart table is named the same as the underlying fact, you may select the Automatic mapping method for the new fact expression. By default, when you create a data mart table, the fact has a unique name that is different from the other facts in the project.4 Click OK.
The following image shows the Forecast Units Sold fact mapped to the data mart aggregate table:
Mapping a Fact to a Data Mart Table
Updating the Project Schema
After you add the data mart table to a project and update the appropriate fact object, you must also update the schema logical information in the metadata.
Lesson Summary
In this lesson, you learned:
• The database instance you select during the project creation process becomes this project’s primary database instance.
• You can associate any number of secondary database instances with a single project. You use secondary database instances to create data marts,
Freeform SQL, Query Builder, and MDX reports. You associate secondary database instances with a project using the Project Configuration Editor.
• The Warehouse Tables pane in Architect graphical interface enables you to maintain the integrity of the logical tables with the data warehouse
structure by providing a variety of options that apply to the project tables on an individual basis.
• Base fact tables are tables that store a fact or set of facts at the lowest possible level of detail. Aggregate fact tables are tables that store a fact or set of facts at a higher, or summarized, level of detail.
• To use aggregate tables in the project, you first add the table to the project using the Warehouse Tables pane in Architect graphical interface. If necessary, you also map the existing attributes and facts to the aggregate table.
• MicroStrategy Architect assigns a logical table size to every table in a project when you initially add them to the project and stores these size assignments in the metadata. It assigns sizes based on the columns in the tables and the attributes to which those columns correspond.
• Logical table size is the sum of the weight for each attribute contained in the table. Attribute weight is defined as the position of an attribute in its
hierarchy divided by the number of attributes in the hierarchy multiplied by a factor of 10.
• You can change the logical table size either in the Logical Size Editor or from the Properties pane in Architect graphical interface.
• A data mart is a relational table containing a report result set. A data mart consists of two objects: the data mart report and the data mart table.
• Data mart reports are created in the Report Editor of a new or existing report. After you execute the data mart report, the data mart table is created in the chosen warehouse.
• You can choose to create only attribute ID columns in the data mart table by removing all non-ID forms from the attribute display and report objects in the data mart report.
• For the columns in the data mart table that contain metric calculations, you can use the existing metric name as the column name or you can create a new column alias.
• To use a data mart table in a project, you must add it to the project using the Warehouse Tables pane in Architect graphical interface, update the
corresponding fact, and then update the project schema.