A frequent requirement in reporting is to compare daily numbers with running monthly or yearly aggregates. These numbers are often referred to as MTD (month-to-date) and YTD (year-to-date) respectively. As always, there are more than one correct paths to get to the right values.
The technique explained in this recipe is one of them. It involves creating a reference table with two columns: the first contains the date we are referring to (let's call it the current date) and the second column holds every date that is needed to obtain the running total referred to the current date, for example all the days from the beginning of the month in case of the MTD.
Looking at the image, if I want to calculate a month-to-date metric for July 3, 2005, I need to sum the three days: first, second, and third of July. The same applies to the YTD with (easy-to-guess) a lot more rows.
Think about it, in dimensional modeling words, as a helper table for a many-to-many relationship between DimDate
and itself.
We already used transformations in Chapter 4, Objects – Facts and Metrics, to do a "this month versus previous month" analysis (a one-to-one relationship); in this recipe we will learn how to use them in a many-to-many case.
Some preparation is needed before going further; the helper tables. Open the sqlcmd
command interface, change to the AdventureWorksDW2008R2
database and issue the following instruction:
with calendar as ( select cast('2005-07-01' as datetime) DateValue1 union all select DateValue1 + 1 from calendar where DateValue1 + 1 < '2010-11-30' ) ,monthtodate as ( select cast('2005-07-01' as datetime) DateValue2 union all select DateValue2 + 1 from monthtodate where DateValue2 + 1 < '2010-11-30' ) select cast(convert(varchar, DateValue1, 112) as integer) DateKey, cast(convert(varchar, DateValue2, 112) as integer) MTD_DateKey into DimDate_MTD from calendar, monthtodate where DateValue1 >= DateValue2 and DateValue2 >= DateAdd(mm, DateDiff(mm,0,DateValue1),0) order by DateValue1, DateValue2 OPTION (MAXRECURSION 0)
The full statement is available in the companion code file.
go
We are using a feature present in Microsoft SQL Server and other RDBMS called common table expression (CTE) with a recursive query.
A CTE is a temporary named result set that you can reference within a SELECT
sentence; a kind of fake table that only exists during the execution of the statement.
In SQL Server a CTE can reference itself recursively, so what we do here is generate two lists of dates from a lower limit (2005-07-01) to an upper limit (2010-11-30) and join them to produce a final result set into a new table named DimDate_MTD
. The limits are the minimum and maximum dates available in the DimDate
table.
And, before you ask: yes, I Googled it. But the copy-and-paste that followed was very difficult.
Let's begin with the month-to-date:
Month to Date
. And update schema.52 Internet Sales Month to Date
.Transformations can be applied on-the-fly to any metric by performing steps 14 and 15; the resulting column is a new metric that is only available in the current report (derived metric).
Looking at the SQL view:
The first SELECT
statement calculates the first column (the real metric) grouping on OrderDateKey
.
The second SELECT
statement joins the fact table to the DimDate_MTD
table on:
(a11.OrderDateKey = a12.MTD_DateKey)
And then groups on DateKey
. Since there are many MTD_DateKey
for each OrderDateKey
, the numbers in the second column contain an aggregate of all the calendar days from the beginning of the month until the OrderDateKey
.
The magic of the transformation is that, once it is built and proved correct, we can apply it on other metrics as well, with no additional effort other than right-clicking several times.
If you want to re-use the same transformed metric in more reports, you can embed the transformation directly into a metric definition as we learned in Chapter 4, Objects – Facts and Metrics:
Sum SalesAmount from FactInternetSales (MTD)
.Using the code in the companion file create first the helper table DimDate_YTD
then the year-to-date transformation.
Create a transformation metric based on Sum SalesAmount from FactInternetSales
and the year-to-date transformation.
Create a new report like the one in the image:
Note that the MTD Metric restarts on February 1, while the YTD does not.