Configuring Data Source Time Settings for Time Intelligence

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.

Analysis Services Data Sources

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 1^st, we choose an instance of July 1^st in the member selector. Note that this could be titled as July 1^st 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 1^st.

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.

TI Considerations for AS Data Sources

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 1^st, but only has data recorded for Jan 1^st 2002 and on, we should backpopulate that fiscal time dimension to July 1^st 2001. This is to ensure all TI functions for that initial fiscal year work fully and completely.

Tabular Data Sources

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 1^st is the start of our year.

We are now ready to use the full set of TI formulas, functions, filters, and links.

TI Considerations for Tabular Data Sources

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 25^th, 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 3^rd, 2008.

Kevin Fan
Program Manager
Microsoft SharePoint BI