Setting the time properties in our Data Sources lays the groundwork for Time Intelligence. Essentially, this is where we align our data sources’ understanding of time, and our understanding of time. In this post, we will discuss how to set these time properties for Analysis Services data sources as well as tabular data sources, while sprinkling best practice tips along the way.
After creating our AS Data Source, we navigate to the Time tab where we are presented with the following page:
1) Time Dimension
This is where we select the time dimension that we want to base all our TI formulas and functions off of – perhaps a fiscal time dimension or a traditional calendar time dimension.
2) Reference Member
Here we indicate when our time dimension’s year starts by choosing an instance of the year’s start date. For example, if our time dimension uses fiscal years that start on July 1st, we choose an instance of July 1st in the member selector. Note that this could be titled as July 1st in the cube, or it might be represented as FY2010 H1 Q1 M1 D1, or perhaps some other format.
If we were using a traditional calendar year time dimension, we would select an instance of January 1st.
3) Hierarchy Level
This is where we enter the granularity of the reference member – whether the member represents a day, month, semester, etc. In the above case, we would select “Day” from the drop down menu.
4) Reference Date
We enter the date reflected by the reference member in the Reference Date section. This is to map the cube’s time dimension to a traditional calendar. In our example, we would enter 7/1/2001.
5) Time Member Associations
Lastly, we map our time dimension hierarchies (listed under the “Member Level” column of the Time Member Associations section) to defined TI hierarchies (listed under the “Time Aggregation” column). Save the data source, and we’re ready to create TI formulas, filters, and links.
As a best practice, we should always have the time dimensions start at the beginning of their respective year – even if that means having empty members for the first half of the year. For example, if we have a fiscal time dimension whose year starts on July 1st, but only has data recorded for Jan 1st 2002 and on, we should backpopulate that fiscal time dimension to July 1st 2001. This is to ensure all TI functions for that initial fiscal year work fully and completely.
Tabular data sources, which include Excel Services, Excel Workbooks, SharePoint Lists, and SQL Server Tables, have a different set up page for their time properties compared to AS data sources. When we navigate to the time tab, we are presented with this view:
The Time Dimensions section lists the time columns that PPS has auto-detected in our tabular data source. After selecting the time dimension to base our time off of, we select the period levels that we would like to use for TI. Note that while AS data sources restricted us to time period levels inbuilt the cube’s time dimension, TI for tabular data sources allows us to choose from, and thus use functions relating to, the gamut of levels.
Finally, we select the start month of our year in the Fiscal Calendar subsection. Note that this automatically assumes the first of the chosen month is the start date for the year – if I choose July, this means that July 1st is the start of our year.
We are now ready to use the full set of TI formulas, functions, filters, and links.
If the time dimension we want to use is not listed in the Time Dimensions section, we navigate to the view tab to preview our tabular data source. Select the date column in the preview, and mark it as a “TimeDimension” column type in the Details pane.
Semesters, quarters, and weeks are relative based on the start month, while years, months, days, hours, minutes, and seconds are absolute and based off of the traditional calendar. With regards to the year nomenclature for the relative time periods, TI treats dates before the start month as occurring in the previous year, and dates occurring after the start month as occurring in the current year.
For example, if the date is June 25th, 2008 and the start month is July, the ‘year’ is 2008, the quarter is Q1 2007, the semester is H1 2007, the week is W1 2007, the month is July 2008, and the day is July 3rd, 2008.
Kevin Fan Program Manager Microsoft SharePoint BI