Grouping at a different level (level metrics)
In every report we did until now, the numbers were always grouped by the attributes in the grid (Report Level). This means that a GROUP BY clause was added at the end of every SELECT including all ID forms of those attributes. In other words, the granularity of the report is always the smallest possible with the columns in the grid. Take a report with year, month, and date: the granularity will be at date level. This is the default behavior of metrics, and it is somehow limiting: what if I want to see in the same grid the monthly sales and the yearly sales? Here comes the level metric that allows us to decide the level of aggregation of a number. Sounds interesting, and surely it takes a little getting used to, but they give a great flexibility and together with nested metrics allow the developer to satisfy many reporting needs.
Note
Before we go on, a slight detour with dates. We have different dates in the fact tables: OrderDate, ShipDate, and DueDate; but we only have a single dimension table DimDate. This poses a classic riddle to the BI developer: how to handle dimension roles. It is not in the scope of this book the discussion about the best modeling solution, you may find a lot of literature and opinions about this. Nevertheless, with MicroStrategy tools, the general wisdom is to duplicate dimension tables with aliases and treat them as separate attributes. For this recipe anyway, we will only focus on OrderDate and forget for a while about the other dates.
Getting ready
We need to specify the FK pointing from the fact table to the dimension table. The FK in this case is the column OrderDateKey. Since the column name is different from the corresponding PK, we need to set it explicitly in the Attribute Editor.
How to do it...
- Double-click on the Date attribute to edit.
- With the ID form selected, click on Modify.
- In the Modify Attribute Form window, below the Expressions list click on New, and in the Create New Form Expression dialog select FactResellerSales from the Source table combobox.
- Drag the column OrderDateKey to the Form expression text area, set Mapping method to Manual and click on OK.
- Now with the OrderDateKey expression selected on the left, click on FactResellerSales in the Source table list and click on OK.
- Click on Save and Close, update the schema, and create a report with the Sum SalesAmount from FactResellerSales metric and the Year attribute. Note down the numbers.
- Now click on View | Design View to modify this report and include the Month attribute.
- See that the numbers are now grouped by Year and Month:
- If we want to have on the same grid another column with the yearly figure, we need to create a level metric. Save this report as
11 SalesAmount by Month. - Go to the Metrics folder, right-click on Sum SalesAmount from FactResellerSales metric and select Edit from the menu.
- In the Metric Editor, look at the top-right pane and click on Level (Dimensionality) = ReportLevel.
- See that the lower-right part displays a Level (Dimensionality) grid with one Report Level row.
- From the left list of attributes, drag Year onto the second row of this grid. The upper-right part now says Level (Dimensionality) = ReportLevel, Year.
- Click on File | Save As and give this metric a new name:
Sum SalesAmount from FactResellerSales (Year Level). Close the editor window. - Go back to My Reports and right-click on 11 SalesAmount by Month, then click on Edit.
- In the Report Editor, go to Public Objects | Metrics and add the newly created metric to the report by right-clicking on it and choosing Add to Columns.
- Open the View menu and select Grid View. See the second metric now displays the total aggregated by year.
- Save the report as
12 SalesAmount by Month and Year.
How it works...
There are two metrics on this report; they both aggregate the SalesAmount column from the FactResellerSales table using the Sum function. The first one (standard metric) is grouping on the smallest level of detail on the grid (Report Level), in this case Month: you can see in the SQL view there is a first SELECT statement with a GROUP BY a12.CalendarYearMonth.
The second one (level metric) aggregates at the year level because we set the Year attribute in the dimensionality. You can see in the SQL view there is a second SELECT statement with a GROUP BY a12.CalendarYear clause.
There is also another noteworthy component in this report: temporary tables. As the SQL statements begin to complicate, MicroStrategy does an extensive use of temporary tables. You may want to discuss this with your DBA, because you will need CREATE TABLE rights on the data warehouse. Temporary tables are deleted after the successful execution of the reports, see the DROP TABLE command towards the end of the SQL view.
There's more...
We can create level metrics with any attribute we want, for example, a metric at the Product Category level or at the City level.
As with other metrics we can nest level metrics:
- Create a new blank metric.
- In the Metric Editor, go to Public Objects | Metrics and from the list double-click on Sum SalesAmount from FactResellerSales. Now click on the division (
/) operator button or type/in the Definition text area. - Double-click on Sum SalesAmount from FactResellerSales (Year Level). The definition should look like this:
([Sum SalesAmount from FactResellerSales] / [Sum SalesAmount from FactResellerSales (Year Level)]) - Click on Tools | Formatting | Values… to bring up the Format Cells dialog box.
- Select Percent from the Category list and click on OK. Click on Save and Close and name it
Percentage of the Year SalesAmount from FactResellerSales. - Add this new metric to the 12 SalesAmount by Month and Year report, we now have the total monthly amount, the total yearly amount, and the percentage that every month represents in the year.
Exercise 20
Create the report 13 Internet SalesAmount by Country and State:
Only the first lines of result data are displayed in the image.