Time Intelligence Post Formula Filter Template in Performance Point Server

By: Ray Barley | Read Comments (7) | Print Ray is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert. Related Tips: More

Problem
I'm trying to setup a time intelligence filter in a Performance Point dashboard.  I've heard about the Time Intelligence Post Formula filter template but I'm unsure of how to implement it.  Can you provide the details and an example of how to do it?

Solution

As the name suggests, the Time Intelligence Post Formula template filter allows you to specify a formula to be used as a filter on the dashboard.  The formula is based on the Simple Time Period Specification (STPS) which defines the types of expressions that can be used in a time intelligence filter.  The key feature of this filter is that you can link it to one or more objects on the dashboard and specify a formula for each dashboard object.  This allows you to use a single filter to calculate different date ranges; e.g. last 5 years, last 8 quarters, last 4 quarters, last 12 months, etc. all on the same dashboard.  The filter provides a reference date which is used in the formula.  By default the reference date is the current date, but you can set it to any date you like using the familiar calendar picker control. 

Let's take a look at a sample dashboard that we will build to demonstrate how to implement the time intelligence filter:

The above dashboard has an Analytic Chart, a Scorecard, and an Analytic Grid; all three objects are filtered using the Date filter at the top.   Each object uses the formula Quarter:Quarter-3 which translates to the last four quarters based on the reference date specified in the Date filter.  Assuming a calendar year, the date September 30, 2006 is the third quarter of the year 2006, so the filter will include the following quarters: Q4-2005, Q1-2006, Q2-2006, and Q3-2006.

As a starting point we will use the following dashboard:

Note that in the above dashboard there are no filters.  Let's add the time intelligence filter and hook it up to each object. 

Step 1: Launch the Dashboard Designer from the Microsoft Office PerformancePoint Server 2007 program group and open the workspace file mssqltips_tipf_filter_start.bswx (you can download the file here).  You will see the following in the Workspace Browser (on the left hand side of the Dashboard Designer):

Right click and select Publish from the context menu for each item under the Data Sources, Reports, Scorecards, and Dashboards.

Step 2: Click PDW under Data Sources then click the Time tab.  You should see the following:

The Time tab is an important point to note.  It represents a mapping of the Time dimension in the cube to the standard member levels that are used by the Time Intelligence Post Formula template filter.  In other words it specifies what column in the Time dimension is the Year, the Quarter, etc.  In the above example the column names are the same as the Member Level.  The Time tab allows exists so that the column names can be whatever you like.  Pick the lowest level in the hierarchy that you want to use in any formula (Day in this case), select a sample member, enter the date of the member, then map the column names to their respective Member Level.

Step 3: Select the Dashboard under Dashboards in the Workspace Browser and you should see the following:

Step 4: Click the Filters tab, then click the New Filter icon:

Step 5: Select Time Intelligence Post Formula as the Dashboard Filter Template:

Step 6: You will be led through a wizard to define the filter.  Use Date as the filter name.  Select PDW as the Data Source (this is a sample SQL Server Analysis Services cube for PerformancePoint).  The filter will be added under the Filters node in the Details pane (on the right side of the Dashboard Designer).

Step 7: Click on the Editor tab to return to the designer page for the dashboard.  Drag the Date filter you just created from the Details pane and drop it onto the Header zone of the dashboard:

Step 8: Hover the mouse over the Date filter in the Header; drag PDW (it's the name of the Data Source that the filter is based on) and drop it onto the Drop fields to create links rectangle of the Analytic Chart:

When you drop the Date filter onto the "Drop fields to create links" rectangle of the Analytic Chart, the Edit Filter Link dialog will be displayed.  The purpose of this dialog is to specify the object to link to the filter.  Select Time.Calendar (see explanation below) from the Dashboard item endpoint dropdown list then click the Filter Link Formula button:

Enter the formula Quarter:Quarter-3 (this will get us the last 4 quarters based on the reference date in the Date filter):

The Analytic Chart was originally created to display the last 4 quarters by using the Last 4 Cal Quarters w/ Sales named set defined in the cube.  The Analytic Chart is defined in the Dashboard Designer as follows:

The named set always gets the latest 4 quarters by using the latest sale date as a point of reference.  The Date filter allows the user to set the point of reference (which can be any date) and the last 4 quarters are calculated based on the date in the filter.   Selecting Time.Calendar from the Dashboard item endpoint dropdown list overrides the Last 4 Cal Quarters w/ Sales named set with the formula per the Date filter.  After deploying the dashboard to a SharePoint site (or just previewing it), you can click the glyph next to the Date filter to display the standard calendar picker and change the reference date from the current date to the date of your choice; e.g.:

For this type of filter I like to select the ending date of the quarter.  The quarter is determined based on the date selected.  For any day in September the quarter will be 3.  The date is only used to determine the quarter; it is not used to filter the data; i.e. you will always get all data for the quarter.

Step 9: Hover the mouse over the Date filter in the Header; drag PDW and drop it onto the "Drop fields to create links" rectangle of the Scorecard (this is the same as step 5 except you are applying the Date filter to the Scorecard).  Once again the Edit Filter Link dialog will be displayed.  Select Column from the Dashboard item endpoint dropdown list.  This will display the four quarters as columns in the scorecard.  The other options are Row which will display the four quarters under each row in the scorecard or Filters which will apply the filter in the background (no visible indicator of the date range).

Step 10: Hover the mouse over the Date filter in the Header; drag PDW and drop it onto the Drop fields to create links rectangle of the Analytic Grid.  This again is the same as step 5 except you are applying the filter to the Grid.  Once again the Edit Filter Link dialog will be displayed.  Select Time.Calendar from the Dashboard item endpoint dropdown list then click the Filter Link Formula button and enter the formula Quarter:Quarter-3.  The Analytic Grid is defined in the Dashboard Designer as follows:

Selecting Time.Calendar from the Dashboard item endpoint dropdown list overrides the Last 4 Cal Quarters w/ Sales named set with the formula per the Date filter.

Step 11: Click the Preview icon on the Edit tab at the top of the Dashboard Designer to see what the dashboard will look like when it's rendered in a web page.  Here is screen shot after setting the Date filter to September 30, 2006:

Next Steps

• Take a look here for examples of expressions using the Simple Time Period Specification.  You can create a formula in a filter to get just about any range of dates.
• Download the sample SQL Server Analysis Services database backup here.  This was used in the above examples.  You will need to restore it to your SQL Server Analysis Services instance.
• Download the sample Dashboard Designer workspaces here to experiment with creating time intelligence filters using the Time Intelligence Post Formula filter template.

Share:

Share

Tweet

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code