Transformations are schema objects, and a very powerful tool that MicroStrategy offers to modify the results of a metric, think about the classic "this month versus previous month" report. But it's not only that. Normally, when the SQL statement is generated, the fact table is joined to the dimension tables using the ID forms of the attributes on the grid.
The transformation allows altering the join path between fact and dimension by introducing an extra step. This extra step could be a helper table, or could be a calculation. Think about the SalesAmount in the FactResellerSales table: when we join the fact to the DimDate table on the OrderDateKey, we get the amount of sales for that particular day. By introducing a helper table we can alter the date so that we get the same number but with a different date, for example one month earlier.
We will see an example in a moment.
We need to create a helper table to calculate the previous month for every month in the DimDate table. Bring up the sqlcmd window and type this command (find it in the companion code file):
select distinct CalendarYearMonth, CONVERT(VARCHAR(7), DATEADD(month, -1, FullDAteAlternateKey), 111) PreviousCalendarYearMonth into DimDateMonthTransform from DimDate go
This statement creates a new table called DimDateMonthTransform with two columns: CalendarYearMonth and PreviousCalendarYearMonth.
Now we add the new table to the project schema and create the transformation:
- Go to the Schema | Warehouse Catalog menu.
- Select DimDateMonthTransform on the left and add it to the Tables being used in the project list, and then click on Save and Close.
- Go to Schema Objects | Attributes, double-click on Month. Answer yes if prompted to edit.
- In the Attribute Editor, with the ID form selected click on Modify.
- In the Modify Attribute Form window check the DimDateMonthTransform table in the Source tables list.
- Click on OK and then on OK again on the warning that appears. Then click on Save and Close.
- Go to Schema Objects | Transformations, on the right pane right-click and from the context menu select New | Transformation.
- When the Select a Member Attribute dialog appears click on Month and hit the Open button.
- Now you're looking at the Define a new member attribute expression window. Select DimDateMonthTransform from the Table drop-down list, and drag the PreviousCalendarYearMonth column from the Available columns to Member Attribute expression, click on OK.
- Click on Save and Close and name it
Previous Month. Update the schema. - Now go to Public Objects | Metrics and double-click on Sum SalesAmount from FactResellerSales to open it.
- In the upper-right panel there is a line that says Transformation = (nothing), click on it and the lower pane will show a grid named Transformation.
- From the left list drag the Previous Month transformation onto the first line of the grid, see that the line above now says Transformation = [Previous Month].
- From the File | Save As menu give it another name like:
Sum SalesAmount from FactResellerSales (Previous Month)and click on Save. - Close the editor window.
- In My Personal Objects | My Reports, create a blank new one.
- Put the Month attribute and the two metrics Sum SalesAmount from FactResellerSales and Sum SalesAmount from FactResellerSales (Previous month) on the Report View: Local Template section.
- Open View | Grid View to run the report and see the result. August 2005 has 1,538,408 and 489,329 in the Previous Month column.
- In order to see July 2005 you need to set the metrics as outer joined, from the Data | Report Data Options | Metric Join Type menu (see the previous recipe).
- Once you have the metrics OUTER joined, you can see that the SalesAmount for July 2005 was in fact 489,329. Save the report as
16 FactResellerSales SalesAmount Previous Month.
Congratulations, you have completed this chapter!
If you look at the SQL view, you will see that the first column is selected joining FactResellerSales to DimDate directly on (a11.OrderDateKey = a12.DateKey) and grouping on CalendarYearMonth. While the second metric is selected joining FactResellerSales on (a11.OrderDateKey = a12.DateKey) to DimDate first and next to DimDateMonthTransform on (a12.CalendarYearMonth = a13.PreviousCalendarYearMonth); the number is then aggregated grouping on CalendarYearMonth that comes from the helper table, returning in fact the following CalendarYearMonth.
When the two datasets are merged on the grid based on the value of the month, the first column shows the current month, while the second column is shifted one row forward.
The interesting thing is that you can cascade transformations. If you add an extra Previous month transformation to the metric, you get the result of two months ago, and so on. Isn't this exciting? (Not really…)
Creating a Previous Year transformation with the Year attribute it is even easier. Since year is just a number we don't need a helper table, we simply specify that the transformation is (Year -1).
Try it yourself:
- Create a new transformation based on the Year attribute.
- In the Member attribute expression area, type
CalendarYear – 1. - Save the transformation as
Last Yearand create a new SalesAmount from FactResellerSales (Previous Year) metric with this transformation, using the original SalesAmount from FactResellerSales. - In a new report, put the Year attribute and the two metrics: SalesAmount from FactResellerSales and SalesAmount from FactResellerSales (Last Year).
- The Going deeper into data with drill down recipe in Chapter 5, Data display and manipulation – Reports