Month-to-date and year-to-date transformations

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.

Month-to-date and year-to-date transformations

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.

Getting ready

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.

Execute it with the command:

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.

How to do it...

Let's begin with the month-to-date:

  1. Open Schema | Warehouse Catalog… menu and move the table DimDate_MTD from the left to the right of the shopping cart, then click on Save and Close.
  2. Go to Schema Objects | Attributes folder and double-click on Date.
  3. In the Attribute Editor, with the ID form selected, click on Modify.
  4. In the Modify Attribute Form window, check the DimDate_MTD table in the Source tables: list and confirm with OK.
  5. Click on Save and Close, and update schema.
  6. Move to the Schema Objects | Transformations folder, right-click on an empty area, and select New | Transformation.
  7. We're in the Select a Member Attribute dialog, pick Date and click on the Open button.
  8. In the Define a new member attribute expression window, open the Table: drop-down list and select DimDate_MTD.
  9. Click on MTD_DateKey in the Available columns list and drag it to the Enter expression here text area. Confirm with on OK.
  10. In the lower part of the Transformation Editor there are two radio buttons under Transformation mapping type, click on the second one, labeled Many to many.
  11. Click on Save and Close, name it Month to Date. And update schema.
  12. Go to My Reports folder, and create a blank report.
  13. Add the Date attribute to rows and Sum SalesAmount from FactInternetSales to columns. Run the report in Grid View.
  14. Right-click on the Sum SalesAmount from FactInternetSales header. From the context menu, select Insert | Transformations | Month to Date | Normal.
    How to do it...
  15. Answer Yes to the message box saying that the report will be re-executed.
  16. Look at the result set; now you have a new column with the running total for each month.
  17. Save and close the report, name it as 52 Internet Sales Month to Date.

How it works...

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.

There's more...

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:

  1. Go to Public Objects | Metrics folder.
  2. Edit the Sum SalesAmount from FactInternetSales metric.
  3. Save a copy of this metric as Sum SalesAmount from FactInternetSales (MTD).
  4. On the upper-right pane of the Metric Editor, click on the line that says Transformation = (nothing).
  5. From the Object Browser pane on the left, drag the Month to Date transformation onto the list on the right.
    There's more...
  6. Click on Save and Close, go to My Reports folder and add the newly created metric to the report number 52.
  7. Now you can remove the derived metric in the second column. Right-click on its header, select Remove from Report and answer Yes to the re-execute warning.
  8. Save and close the report.

Exercise 32

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:

Exercise 32

Note that the MTD Metric restarts on February 1, while the YTD does not.

Note

You can watch screencasts of these operations at:

See also

  • The Creating a cube from an existing report recipe in Chapter 12, In-Memory Cubes and Visual Insight
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset