Time Intelligence Calculation YTD & YOY (Datawarehouse vs MDX vs DAX)

In my last BI workshop we had the discussion whether or not it is worth the effort to create a BI-semantic Model on top of the Datawarehouse.

I would like to share the answer:

In order to achieve the following Report:


from your Datawarehouse without a BI-Semantic Model you will Need the following T-SQL Code:

Create View SalesPerMonth
Select Top 1000 DATEPART(YEAR,Datekey) as 'Year',DATEPART(QUARTER,Datekey) as 'Month',SUM(SoldUnits) as 'UnitSales' from FactSales group by DATEPART(YEAR,Datekey),DATEPART(QUARTER,Datekey)

Select s1.UnitSales as 'This Year same Quater Sales', s2.UnitSales as 'Last Year same Quater Sales',
  s1.UnitSales*1.0/s2.UnitSales as
  'PercentChange' from SalesPerMonth s1 join SalesPerMonth s2 on s1.[Year] -1 = s2.[Year] and s1.[Month] = s2.[Month]

ROW) AS YearToDate
FROM SalesPerMonth
ORDER BY [Year],[Month]

Hint: with SQL 2012 it is much easier using windowing functions!

However if you provide a Multidimensional Cube on top of your Datawarehouse it will be much more flexible to use the builtin Time Intelligence functions:


/*Year to Date*/

Create Member
  CurrentCube.[Calendar].[Hierarchy Calendar Calculations 3].[Year to Date]
  As "NA";  

    [Calendar].[Hierarchy Calendar Calculations 3].[Year to Date],
    [Calendar].[Calendar Year].[Calendar Year].Members,
    [Calendar].[Date Key].Members

             { [Calendar].[Hierarchy Calendar Calculations 3].[Current Calendar] }
                            [Calendar].[Hierarchy].[Calendar Year],

The DAX implementation is the easiest! Here is the link http://blogs.msdn.com/b/analysisservices/archive/2010/04/12/time-intelligence-functions-in-dax.aspx

Bottom Line: If you design your Datawarehouse according to Kimball`s Design Patterns the cube and especially the tabular model is not too much effort and will payoff quickly! When your Reports require KPIs(including Time Intelligence YoY,YTD,…) you should go for the additional OLAP or Tabular Model. Last but not least the security concept inside a BI-Semantic Model provides much more granular control compared to users accessing the Datawarehouse directly.

Does that mean that you have to use the cube only? –> No!

You can still use your DWH directly as the source for data propagation (ETL Subsystem according to Kimball) for example to shape data for data mining using views or Integration Services, or to render and distribute static reports with Reporting Services.  So go for a BI-semantic Model. The question of Multidimensional OLAP vs Tabular model will be discussed in my next Workshop (Modul 3 of Business Intelligence Academy –> http://www.microsoft.at/admp/B23AC328-E316-47CE-A357-B53E7626B5C6/BI_Academy_Module3_Datasheet.pdf )

And yes you can also use the OLAP / Tabular Model as the Data Source for Reporting Services.

Comments (0)

Skip to main content