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.
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.
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.
11 SalesAmount by Month
.Sum SalesAmount from FactResellerSales (Year Level)
. Close the editor window.12 SalesAmount by Month and Year
.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.
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:
/
) operator button or type /
in the Definition text area.([Sum SalesAmount from FactResellerSales] / [Sum SalesAmount from FactResellerSales (Year Level)])
Percentage of the Year SalesAmount from FactResellerSales
.