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.
- 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.
The following table shows examples of TI syntax expressions.
| TI expression format | Example |
|---|
» Specify current period with an offset
Member
Member + Integer
Member – Integer |
Month
Day+10
Year-1 |
» Specify a range of continuous time periods
MemberExpression1: MemberExpression2 |
Month:Month+5 |
» Specify a parallel period with a specific lag
(MemberExpression1-Integer). MemberExpression2 |
(Year-1).Day |
» Specify a year- or month-to-date period
Year.MemberFunction:Member |
Year.FirstMonth:Month |
» Specify time hierarchy levels with a function
Member.MemberFunction |
Year.FirstChild |
» Specify a list of time periods
MemberExpression1,MemberExpression2 |
Day,Day-5 |
You can combine different syntax forms. For example, this expression is a list of several members:
Year-1,Week+2,Month-5:Month-2
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.
| MemberFunction | Description | Examples |
|---|
| Parent | The parent level of time aggregation from the data source | Day.Parent |
| FirstChild | The first child aggregation level from the data source | Month.FirstChild |
| LastChild | The last child aggregation level from the data source | Month.LastChild |
| FirstSemester | Returns 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 |
| FirstQuarter | Returns 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 |
| LastQuarter | Returns 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 |
| FirstMonth | Returns the first month of the parent period. | Year.FirstMonth (Year-1).FirstMonth |
| LastMonth | Returns the last month of the parent period. | Year.LastMonth (Quarter+1).LastMonth |
| FirstWeek | Returns the first full week of the parent period. | Year.FirstWeek (Semester-1).FirstWeek |
| LastWeek | Returns the last full week of the parent period. | Year.LastWeek (Semester+1).LastWeek |
| FirstDay | Returns the first day of the parent period. | Month.FirstDay (Month-1).FirstDay |
| LastDay | Returns 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 |
| LastHour | Returns the last hour of the parent period. | Day.LastHour (Day+1).LastHour |
| FirstMinute | Returns the first minute of the parent period. | Day.FirstMinute (Hour-2).FirstMinute |
| LastMinute | Returns the last minute of the parent period. | Day.LastMinute (Hour+2).LastMinute |
| FirstSecond | Returns the first second of the parent period. | Hour.FirstSecond (Minute-10).FirstSecond |
| LastSecond | Returns 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