You should complete the following exercises using the Forecasting Project, which is found in the MicroStrategy Analytics Modules project source. The logical data model and schema for this project are included with the exercises.
You need to review this information before beginning the exercises.
You will also use the Forecasting Project to complete other exercises throughout this course.The Forecasting Project is based on the following logical data model:
Forecasting Project Logical Data Model
The attributes in the Time hierarchy are the following:
Attributes in the Time Hierarchy
The attributes in the Geography hierarchy are the following:
Attributes in the Geography Hierarchy
The schema for this project consists of the following lookup tables:
Lookup Tables
The schema for this project consists of the following fact tables:
Fact Tables
Create an Aggregate Table Using Data Mart
In this exercise, you will first create a REGION_FORECAST_SALES aggregate table using the data mart feature in the Forecasting Project. You will then bring this table to the project and test how the Engine selects the fact table based on the logical table size.
Before you create a data mart, you will first modify the Forecast Revenue metric to use a custom Forecast_Revenue column alias. You will then create a data mart report with Region ID and Quarter ID attribute forms and the Forecast Revenue and Forecast Units Sold metrics on the template. Name the data mart table REGION_FORECAST_SALES. Save the data mart report as Regional Forecast Revenue in the Public Objects\Reports folder.
Next, you will add the table to the Forecasting Project. Then, you will create a new fact expression for the Forecast Revenue fact that uses the
Forecast_Revenue column in the REGION_FORECAST_SALES table. You will also create and run a Data Mart Test report with the Region attribute and the Forecast Revenue metric on the template to confirm that the Forecast Revenue fact uses the REGION_FORECAST_SALES table.
Finally, you will change the logical table size for the
REGION_FORECAST_SALES table to 30 and run the Data Mart Test report to view the impact of your change on the report SQL.
You can use the detailed instructions if you want help.
Detailed Instructions
Modify metric alias
1 In Developer, log in to the MicroStrategy Analytics Modules project source as Administrator and leave the password blank.
2 Open the Forecasting Project.
3 In the Public Objects folder, in the Metrics folder, edit the Forecast Revenue metric.
4 In the Metric Editor, on the Tools menu, point to Advanced Settings and select Metric Column Options.
6 Click OK to close the Metric Column Alias Options window.
7 Save and close the metric.
8 If you did not edit Forecast Units Sold during the lesson with your instructor, repeat steps 3 to 7 for the Forecast Units Sold metric so the Column Name used in table SQL creation box is Total_Units_Sales.
Create the Data Mart report
9 In the Public Objects folder, in the Reports folder, create the following report:
You can access the Region attribute from the Geography hierarchy.You can access the Quarter attribute from the Time hierarchy. The Forecast Revenue and Forecast Units Sold metrics are located in the Metrics folder.
Configure attribute display options
10 In the Report Editor, on the Data menu, select Attribute Display.
11 In the Attribute Display window, in the Attribute drop-down list, ensure that Region is selected.
12 Under Select one of the display options below, click Use the following attribute forms.
13 In the Available forms list, select the ID form.
14 Click the upper > button to move the ID form to the Displayed forms list.
18 Click the lower < button to remove the DESC form from the Report objects forms list.
19 In the Attribute Display window, in the Attribute drop-down list, select Quarter.
20 Under Select one of the display options below, click Use the following attribute forms.
21 In the Available forms list, select the ID form.
22 Click the upper > button to move the ID form to the Displayed forms list.
23 In the Displayed forms list, select the DESC form.
24 Click the upper < button to remove the DESC form from the Displayed forms list.
25 In the Report objects forms list, select the DESC form.
26 Click the lower < button to remove the DESC form from the Report objects forms list.
27 Click OK.
Configure the data mart
28 In the Report Editor, on the Data menu, select Configure Data Mart.
29 In the Report Data Mart Setup window, on the General tab, in the Data mart database instance drop-down list, ensure the Forecast Data database instance is selected.
30 In the Table name box, type REGION_FORECAST_SALES.
31 Click OK.
32 In the Report Data Mart Setup window, click OK.
33 Save the report in the Public Objects\Reports folder as Regional Forecast Revenue
34 Run the report.
After the report executes, you see a message that the result data has been stored in the REGION_FORECAST_SALES table, as shown below:
35 Close the report.
Incorporate the data mart table into the project
36 In Developer, on the Schema menu, select Architect.
37 In the Read Only window, select Edit: This will lock all schema objects in this project from other users.
38 Open the Architect graphical interface, click Project Tables View tab.
39 Disable automatic metric creation.
Automatic metric creation can be changed by clicking Architect button, selecting Settings, clicking the Metric Creation tab, and clearing Sum check box.40 In the Warehouse Tables pane, in the Forecast Data database instance, right-click the REGION_FORECAST_SALES table and select Add Table to Project.
41 In the Results Preview window, in the Fact tab, clear the Forecast Revenue
44 Right-click the Forecast Revenue fact and select Edit.
45 In the Fact Editor, create a new fact expression that uses the
Forecast_Revenue column in the REGION_FORECAST_SALES table as a source table.
46 Under Mapping method, ensure Automatic is selected.
47 Click OK.
48 Click OK to close the Fact Editor.
Update the Forecast Units Sold fact
49 In the Project Tables View tab, find the FORECAST_SALES table and select the Forecast Units Sold fact.
50 Right-click the Forecast Units Sold fact and select Edit.
51 In the Fact Editor, create a new fact expression that uses the
Total_Unit_Sales column in the REGION_FORECAST_SALES table as a source table.
52 Under Mapping method, ensure Automatic is selected.
53 Click OK.
54 Click OK to close the Fact Editor.
Verify the attribute source tables
55 In the REGION_FORECAST_SALES table, right-click the Quarter attribute ID form and select Edit.
56 In the Modify Form Expression window, click OK.
57 In the Modify Attribute Form window, ensure the
REGION_FORECAST_SALES table is selected as source tables.
58 Click OK.
59 Repeat steps 55 to 58 for Region attribute.
Update the project schema
Ensure that the Recalculate table logical sizes check box is selected in the Schema Update window.Test the new fact mapping
61 In Developer, in the Public Objects\Reports folder, create the following report:
You can access the Region attribute from the Geography hierarchy.The Forecast Revenue metric is located in the Metrics folder.
62 Run the report. The result set should resemble like the following:
63 View the report in SQL View. The SQL should look like the following:
64 Save the report in the Public Objects/Reports folder as Data Mart Test and close the report.
Change the logical table size for the aggregate table
65 Go to Architect graphical interface.
66 In Architect graphical interface, on the Design tab, click Edit logical size of tables button as shown below:
Notice that the REGION_FORECAST_SALES table’s logical size is 10. The logical size for the FORECAST_SALES table is 20. Therefore, when you execute any report that aggregates the forecast revenue data to the region or quarter level, the Engine chooses the REGION_FORECAST_SALES table due to its lower logical table size.
67 In the Logical Size Editor, for the REGION_FORECAST_SALES table, in the Size value box, type 30.
68 For the REGION_FORECAST_SALES table, select the Size locked check box.
When you select this check box, the logical size for the table will not be recalculated when you update the schema.69 Click OK.
70 Save and close Architect.
Update the project schema.
71 Update the project schema.
Ensure that the Recalculate table logical sizes check box is selected in the Schema Update window.Test the change of the logical table size on the report SQL
72 In Developer, in the Reports folder, right-click the Data Mart Test report and select View SQL. The SQL should look like the following:
Notice that this time, the Engine picked the base FORECAST_SALES table over the aggregate REGION_FORECAST_SALES table because it has a smaller logical table size.
73 Close the report without saving it.