INF: How To Perform Time Series Calculations (304118)

MORE INFORMATION

The logical parent dimension for a calculated member is Measures because it generally has the fewest members and is the dimension that is least likely to change. However, in situations where multiple time series calculations are required for each measure, creating a calculated measure for each time series and each measure increases the complexity of the cube, the chances of error, development costs, testing costs, and maintenance costs. One solution involves adding another dimension to the cube, with the sole purpose of providing the desired time series calculations.

For the purposes of illustration, you have been given the task of altering the Sales cube of the FoodMart 2000 sample. The change specification is that users must be able to display sums for all measures with the following time series:

• Year To Date Sum
• Quarter To Date Sum
• Month To Date Sum
• Moving Twelve Month Sum

To implement the desired changes, with minimal changes, use the following steps:

1. In Microsoft Access, open the FoodMart 2000.mdb file, and then create a new table named "Periods" with a single numeric column named "Period". Set the Period column as a primary key.
2. Run the following update query:

   INSERT INTO PERIODS (PERIOD) VALUES (1);
   					

3. Modify the structure of the Sales_Fact_1997, Sales_Fact_1998, and Sales_Fact_Dec_1998 tables, and add a numeric column named "Period" to each of these tables.
4. Run the following update queries:

   UPDATE SALES_FACT_1997 SET PERIOD=1
   					

   UPDATE SALES_FACT_1998 SET PERIOD=1
   					

   UPDATE SALES_FACT_DEC_1998 SET PERIOD=1
   					

5. Close the FoodMart 2000 database file.
6. Start Analysis Manager, select the FoodMart 2000 database, and then refresh the data source.
7. Create a new dimension, named "Periods", by using the Periods table as the data source. Save, and then process the new dimension.
8. Open the Sales cube for editing, and then add the Period dimension.
9. Create a new calculated member with the following properties:
   • Member Name = "Year To Date Sum"
   • Parent Dimension = "Periods"
   • Value = SUM(YTD(),[PERIODS].&[1])
10. Create a second calculated member with the following properties:
    • Member Name = "Quarter To Date Sum"
    • Parent Dimension = "Periods"
    • Value = SUM(QTD(),[PERIODS].&[1])
11. Create a third calculated member with the following properties:
    • Member Name = "Month To Date Sum"
    • Parent Dimension = "Periods"
    • Value = SUM(MTD(),[PERIODS].&[1])
12. Create a fourth calculated member with the following properties:
    • Member Name = "Moving Twelve Month Sum"
    • Parent Dimension = "Periods"
    • Value = SUM(LastPeriods(12),[PERIODS].&[1])
13. Save, and then process the cube.
14. Click the Data tab of the Cube Editor, and then traverse the time dimension under each of the following:
    • Periods = 1
    • Periods = Year To Date Sum
    • Periods = Quarter To Date Sum
    • Periods = Month To Date Sum
    • Periods = Moving Twelve Month Sum
    Note the differences in the data that display when different members of the Periods dimension are selected.