Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Another approach to Time Intelligence

  • 25 November 2013
  • Author: Besim Ismaili
  • Number of views: 14801

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 use. 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 represent Attributes and Records are Members of that Dimension. 
After that, in the 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 : 

-- 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 for your recomandations and critics.

Categories: Analysis Services
Rate this article:

Please login or register to post comments.