How to work with STPS formulas in PerformancePoint Services 2010
Simple Time Period Specification
A common requirement of clients is to allow end users to filter data from scorecards and reports on a dashboard based on a Time Intelligence formula. Time Intelligence formulas allow users to view data over time such as Year Over Year (YOY), Last Period, Rolling Average, and Year To Date (YTD).
For example, to implement this in an Analysis Services cube, the cube implementer needs to create that type of formula using a language native to Analysis Services. In Analysis Services, the language is the Multi Dimensional eXpression language (MDX). To implement this against a tabular data source, you need to implement it using a language native to the data source. For example, if your data source is a SQL database, you will need to use T-SQL.
This is still possible to do, but it might hamper the development process and extend the time it takes to release your application to end users.
PPS provides its own expression language called Simple Time Period Specification (STPS). STPS allows you to create time intelligence formulas rapidly. For example, if you want to see aggregated data based on YTD, you can create a Time Intelligence Filter and use the following formula:
Year.FirstMonth:Month
If you want to apply this data to 2009 sales numbers and the current month is July, this STPS expression will aggregate all 2009 sales data from the first month of the current year to the current month. Under the covers, this expression is translated to the data sources native language.
In order for STPS to work, you need to configure the data source so that your expressions properly match the time definition in the data source. Look at the previous code example:
Year.FirstMonth:Month
For this expression to work, you need to explain to STPS how Year and Month are defined in the data source. In other words, you need to map how time is defined in the data source to an internal, conformed master time dimension against which STPS works.
- Both Analysis Services and tabular data sources have a Time tab. To successfully use Time Intelligence features, select a dimension named Date and a hierarchy named Year – Qtr – Month – Date to specify which dimension and hierarchy in the cube defines time.
- Select a reference member and a hierarchy level since all Time Intelligence operations are calculated with these settings as a reference point. Any date within the cube should work fine since PPS can extrapolate other dates after one of them is selected.
- Specify how the hierarchy levels in the data source’s time dimension maps the internal, conformed master time dimension. The available hierarchy levels for the master time dimension are on the right-hand side.
This is an example of complete settings for the Time Member example.
