Another approach to Time Intelligence

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 :

— 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.


6 thoughts on “Another approach to Time Intelligence

  1. I simply want to say I’m newbie to weblog and absolutely savored you’re web blog. Likely I’m want to bookmark your blog . You actually come with exceptional posts. Thanks a lot for sharing with us your webpage.

  2. I¡¦ve been exploring for a little for any high-quality articles or blog posts in this kind of house . Exploring in Yahoo I finally stumbled upon this website. Studying this information So i am satisfied to convey that I’ve a very good uncanny feeling I found out just what I needed. I such a lot no doubt will make certain to don¡¦t omit this website and give it a look regularly.

  3. Hello.This article was really fascinating, especially because I was browsing for thoughts on this matter last Sunday.

  4. Faytech specializes in the design, development, manufacturing and marketing of Capacitive touch screen, Resistive touch screen, Industrial touch screen, IP65 touch screen, touchscreen monitors and integrated touchscreen PCs. Contact us at, 121 Varick Street,3rd Floor,New York, NY 10013,+1 646 205 3214

  5. whoah this blog is magnificent i really like studying your posts. Stay up the great work! You realize, lots of people are looking round for this information, you can help them greatly.

  6. MetroClick specializes in building completely interactive products like Photo Booth for rental or sale, Touch Screen Kiosks and Digital Signage, and experiences. With our own hardware production facility and in-house software development teams, we are able to achieve the highest level of customization and versatility for Photo Booths, Touch Screen Kiosks and Digital Signage. MetroClick, 121 Varick St, #301, New York, NY 10013, +1 646-843-0888

Leave a Reply

Your email address will not be published. Required fields are marked *