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

Analyzing data

As you can imagine, the amount of data contained in a modern business is
enormous. If the data were very small, you could simply use Microsoft Excel
and perform all of the ad-hoc analysis you need with a Pivot Table. However,
when the rows of data reach into the billions, Excel is not capable of handling
the analysis on its own. For these massive databases, a concept called OnLine
Analytical Process (OLAP) is required. Microsoft’s implementation of OLAP is
called SQL Server Analysis Services (SSAS), which I cover in detail in Chapter 8.
If you’ve used Excel Pivot Tables before, think of OLAP as essentially a massive
Pivot Table with hundreds of possible pivot points and billions of rows
of data. A Pivot Table allows you to re-order and sum your data based on different
criteria. For example, you may want to see your sales broken down by
region, product, and sales rep one minute and then quickly re-order the groupings
to include product category, state, and store.
In Excel 2010 there is a new featured called PowerPivot that brings OLAP to
your desktop. PowerPivot allows you to pull in millions of rows of data and
work with it just like you would a smaller set of data. After you get your Excel
sheet how you want it, you can upload it to a SharePoint 2010 site and share
it with the rest of your organization.
With PowerPivot you are building your own Cubes right on your desktop using
Excel. If you use PowerPivot, you can brag to your friends and family that you
are an OLAP developer. Just don’t tell them you are simply using Excel and
Microsoft did some magic under the covers.
When you need a predefined and structured Cube that is already built for
you, then you turn to your IT department.