Another approach to Time Intelligence

Best Parctices for Time Scale solution
This is an original method that I use when I build SSAS Cubes and it is time to share it with you

Time Intelligence is a common issue for every OLAP structure because Time as dimension apperars in every OLAP project, in every Cube you build, despite business model or type. To handle Time Intelligence good in calculations, aggregations and optimization, you need to use Timescale as well. With Timescale I mean: MonthToDate (MTD), YearToDate (YTD), LastYear(LY) etc..., all these very important to everyday use of Business Intelligence solutions.

Now I will take to technical steps to implement this genius way of handling with Timescale and Time calculations.
First you create a Table for Timescale in the source (in you DB, DWH or Data Mart), with 3-4 columns and 3-4 records for example. Here is a sample for that:

Based on this table you create a Dimension Timescale where Columns are Attributes and Records are Members of that Dimension.
After that, I go to DSV of our Cube, on every Fact Table that needs Timescale (usually all need Timescale) I add Named Calculation FK_Timescale with value 'PE', as in the image above:

I create a relationship FK_Timescale of the Fact Table to Timescale table in Data Source View (DSV) and after I build the Cube I do the same in Dimension Usage, where I create Regular relation between Fact Table and Timescale Dimension as shown above:

I create 2 name sets for MTD and YTD right after Calculate; and the MDX for that is shown above :

CALCULATE;
-- Period to date
-- Month to Date
[Timescale].[Timescale].[MTD] = Sum(MTD([Time Dim].[Hierarchy].CurrentMember),[Timescale].[Timescale].[PE]);
-- Year to Date
[Timescale].[Timescale].[YTD] = Sum(YTD([Time Dim].[Hierarchy].CurrentMember),[Timescale].[Timescale].[PE]);

Now, you have ready implemmented Timescale in the Cube for all your Measures, so you do not need to calculate Timescales for each Measure. Instead of having MTD(YTD) Revenue, you just use Revenue measure and change Timescale from PE to MTD(YTD). Test this with Excel, through Data Connection to OLAP Cube and enjoy possibilities. This way is proven more dynamic, flexible and optimized for query performance.

I would be very pleased and that will help me keeping posting good things about BI, if you find time from your busy schedule to suggest, critic or to share with love this blog or this particular content.

Regards,
Besim