Welcome to EMC Consulting Blogs Sign in | Join | Help

David Francis Blog

PerformancePoint Time Intelligence - BI for the masses Part 1

Microsoft have often mentioned their desire in the past for 'BI for Masses', giving the power of business intelligence to everyone who might need data to make a decision rather than just a few power analysts. For most users and lets face it lots of analysts too, their exposure to BI actually comes in the form of data presented to them in Excel and it is Excel style formulas that they are most comfortable with.

I think this was probably in mind when Microsoft designed their syntax for creating and using Time Intelligence filters within Microsoft PerformancePoint Dashboard Designer.

Before I go on, its probably worth a quick note on Time Intelligence Filters and what they are used for.

Time Intelligence filters within PerformancePoint provide users with a way of specifying dates using everyday terms like Today, Yesterday, Last 6 months etc rather than having to select from a calendar control or pick different measures/metrics.

The Time Intelligence filter can then be linked to your dashboard content to drive your scorecards, grids and charts etc (more on that later).

So how to do you create one?

  • Start up PerformancePoint Dashboard Designer and create a new OLAP data source (if you don't have one already).
  • Go to the Time tab and map your Time dimension to the Time Intelligence level settings.

The clever bit here is you are mapping PerformancePoint's Time Intelligence to the Time dimension that you have created and it will now automatically keep track of the current day for you.

No more Time dimension default member formulas or trying to use VBA and Date now.

Of course the designer still has to have an appreciation of dimensions, hierarchies and levels.

  • Now create a new Dashboard.
  • Click on the filters tab of your new Dashboard and click on New Filter.
  • Select Time Intelligence (the one on the left) from the Dashboard Filter Template.

  • Name your new filter.

  • Select the data source that you used to define the Time mappings.

  • Now start to create your Time Intelligence formulas and add your own user friendly names.

  • Notice the lack of MDX (its still there, just click preview) and the use of Time Intelligence expressions which whilst still having a MDX flavour are also Excel-like.

  • So some examples of Time Intelligence Formulas and Function (courtesy of Microsoft).

The following table shows examples of TI syntax expressions.

TI expression formatExample

» Specify current period with an offset
   Member + Integer
   Member – Integer


» Specify a range of continuous time periods
   MemberExpression1: MemberExpression2


» Specify a parallel period with a specific lag
   (MemberExpression1-Integer). MemberExpression2


» Specify a year- or month-to-date period


» Specify time hierarchy levels with a function


» Specify a list of time periods


You can combine different syntax forms. For example, this expression is a list of several members:


Notice that one of the member expressions in this list is a range expression, Month-5:Month-2. This range expression actually specifies four individual members: Month-5, Month-4, Month-3, and Month-2.

What functions can I use with TI Members?

TI syntax expressions can also include a member function, in the form Member.MemberFunction. This table shows the TI member functions.

Parent The parent level of time aggregation from the data source Day.Parent
FirstChildThe first child aggregation level from the data sourceMonth.FirstChild
LastChildThe last child aggregation level from the data source Month.LastChild
FirstSemesterReturns the first semester of the parent time period. The parent member for this function must be Year. Year.FirstSemester
LastSemester Returns the last semester of the parent time period. The parent member for this function must be Year. Year.LastSemester
FirstQuarterReturns the first quarter of the parent time period. The parent member for this function must be one of semester or year. Year.FirstQuarter Semester.FirstQuarter
LastQuarterReturns the last quarter of the parent time period. The parent member for this function must be one of semester or year. Year.LastQuarter (Semester-1).LastQuarter
FirstMonthReturns the first month of the parent period. Year.FirstMonth (Year-1).FirstMonth
LastMonthReturns the last month of the parent period. Year.LastMonth (Quarter+1).LastMonth
FirstWeekReturns the first full week of the parent period. Year.FirstWeek (Semester-1).FirstWeek
LastWeekReturns the last full week of the parent period. Year.LastWeek (Semester+1).LastWeek
FirstDayReturns the first day of the parent period. Month.FirstDay (Month-1).FirstDay
LastDayReturns the last day of the parent period.Month.LastDay (Month+1).LastDay
FirstHour Returns the first hour of the parent period. Day.FirstHour (Day-1).FirstHour
LastHourReturns the last hour of the parent period.Day.LastHour (Day+1).LastHour
FirstMinuteReturns the first minute of the parent period.Day.FirstMinute (Hour-2).FirstMinute
LastMinuteReturns the last minute of the parent period. Day.LastMinute (Hour+2).LastMinute
FirstSecondReturns the first second of the parent period.Hour.FirstSecond (Minute-10).FirstSecond
LastSecondReturns the last second of the parent period. Hour.LastSecond (Minute+9).LastSecond

  • Then within your filter select a Display Method (I think a simple list works best here) and go onto to finish creating the filter.

So, now how do you wire your new filter into your dashboard? Well, it's much the same process as that for the normal member selection filter.

  • Drag your new filter into either the header area or a zone that you have created for it to live in.

  • Now click on your new filter and select the name of your Analysis Services Cube Database (in the picture mine is just called Cube - you wouldn't call it that in real life!) and drag it into the 'Drop fields to create links area of your scorecard, grid, chart etc.

  • Choose whether you wish to link to filters, rows or columns if connecting to a scorecard or link to your Time Dimension if using an Analytic Grid or Chart.

Hope you find Time Intelligence filters as useful as I have and a great way to improve usability and understanding for your front-end users.

Technorati Profile

Published Sunday, December 02, 2007 5:38 AM by David.Francis
Anonymous comments are disabled
Powered by Community Server (Personal Edition), by Telligent Systems