Foreword
Data is often 'sliced and diced' by many ways ('dimensions') so that better business insights can be gained from them. One of the most common ways is "Time". Year-To-Date (YTD), Month-To-Date (MTD), Quarter-To-Date (QTD), etc. are very common calculations in any BI solution. This post explains the basic steps required for establishing "Time Intelligence" in SQL Server 2012 Tabular Model.
Steps
1. Create a separate Date / Calendar table (with continuos dates)
2. Set "Mark As Date Table" for this Calendar table
3. Establish "Active" relationship(s) with other dimensions in the Model
1. Create a separate "Date" / "Calendar" table (with continuos dates)
To make Time Intelligence work perfectly, it is required that a separate Date / Calendar table be created. This Calendar table must contain all the dates (without any gaps) that are required for calculations. For example, let us suppose that you wish to analyze "Sales" data (in Adventureworks2012 sample database) between the year 2005 and 2008. Then, the Calendar table that you create MUST CONTAIN all the dates ranging from 01-January-2005 to 31-December-2008.
You can create a Calendar table by either writing a SQL query or by copying a list of dates (manually created in a spreadsheet like MS Excel) and pasting them into your Tabular Model. Your Calendar table should contain all the important Date Fractions like Date, Month, Year, Quarter, etc. which may be employed in your Time Intelligence calculations. The 'Date' field in your Calendar table must be of type - Date (without the time part). The division between Date and Time in the transaction table should be done before importing data into the Tabular model.
Set appropriate properties for the various fields in the Calendar table. For example, in the image shown above, the 'Month' field has been sorted by 'MonthNumber' field so that it overrides the default sorting behaviour (alphabetical). This is done to ensure that any client reporting tool (for example, MS Excel) that consumes this tabular model will display data from "January" through "December" (and not alphabetically, in which case "April" would be displayed first). For simiar reason, the 'DayOfWeek' field has been sorted by 'DayOfWeekNumber' field.
2. Set "Mark As Date Table" for this Calendar table
As shown below, set "Mark As Date Table" setting for this Calendar table. Choose the appropriate 'Date' field that is to be used in Time Intelligence calculations.
3. Establish "Active" relationship(s) with other dimensions in the Model
Suppose that you wish to implement Time Intelligence calculations for "Sales" data in Adventureworks2012 sample database. For the purpose of illustration, I have related 'ModifiedDate' field of "SalesOrderDetail" table to the 'Date' field of my "Dates" Calendar table. Make sure you mark this relationhip as 'Active'. Time Intelligence does not work 'naturally' for In-Active Relationships (although, there is a workaround by explicitly specifying the relationship to use in your DAX calculations with the help of UseRelationship() function and Calculate() function).
That's it! You are now all set to implement Time Intelligence calculations in your Model.
Create a sample measure (for example, "Net Sales") and use "Analyze in Excel" feature to see the results in Excel's Pivot Table.
Happy "BI"-ing !
Regards,
Dharmesh
