PPS M&A - Intelligent Time Intelligence
Time Intelligence (TI) in BSM doesn't really deliver the functionality that many thought it is supposed to. The use of the LASTNONEMPTY function in conjunction with the LAG and LEAD settings in an Actual or Target creates more confusion than anything else. When data is allowed to enter the source cube for next month, next year or any point in the future it throws the whole thing off. The bottom line is BSM TI doesn't know what day it is today and has only limited knowledge of data source time dimensions. I recently posted one way to make OLAP-based scorecards a little more time intelligent, but PPS has something far better in store, and not just for OLAP data sources...
Last week I presented a couple of PPS sessions with Steve Handy of MS at the Asia Pacific SharePoint conference in Sydney. Steve is a PM on the M&A (Monitor & Analyze) team. One of the features he is most proud of designing in the upcoming product is proper time intelligence. When it comes to TI PPS will know what day it is and will add quite a bit more to the equation as well. The current CTP2 build of PPS does not include TI features, however we will see these new bits in CTP3 which is slated for release some time in June.
How Will TI Work?
Time dimension meta data will be configured as part of the definition of a data source (more on that below). The actual TI functionality will be hooked up using filters in the Dashboard element. BTW, the term filter will be used in place of parameter in PPS. All filtering will be done at the dashboard level, so scorecards (or scorecard views) will not have page filters as part of their definition like they do in BSM. The functionality & flexibility filters will provide is going to be great and worthy of a post of its own at some point in the near future.
MS has tried with both BSM Builder and Dashboard Designer to provide tools that are not specifically geared towards developers. This is one of the reasons these applications reside in the Office family and we do not use a Visual Studio snap-in to design and publish our scorecards & KPIs. The hope is that power users and analysts will be empowered do more of the PPS design work. With this in mind the M&A team designed the STPS (Simple Time Period Specification) to enable easy configuration of dynamic time periods. PPS will also allow the use of pure MDX for this kind of functionality too (which is also great news) but for those with little or no MDX experience (read: Analysts, PowerUsers) the following example commands, and quite a few others, will be very useful.
Single members:
Month = current month
Year = current year
Month-1 = last month
Quarter-2 = two quarters ago
Ranges:
{Month-6 : Month-1} = last 6 whole months
{Year.FirstMonth : Month} = year-to-date
{Month : Year.LastMonth} = current month to current year end (forecasting range)
We will also be able to configure a Fiscal year start month value, so if your financial year starts in August a call to Year.FirstMonth will return August and not January. There will not be functionality just yet for really funky fiscal calendar rules but this should suffice for most.
Multi-data source TI
So STPS makes it easier to do what we could otherwise achieve with simple MDX? Great. The fact that we will be able to use MDX to create dynamic filters is in itself a big step. However, what if our data source is not multi-dimensional? We can’t use MDX but we can use the STPS. TI will enable time filtering homogeneously across both OLAP and non-OLAP data sources through dashboards using STPS. Similar functionality will also allow cross data source filtering of other common dimensions other than time. This is the stuff that blows the “you can’t have page filters data in a multi-data source scorecard” right out of the water. Think of an OLAP cube containing revenue numbers sliceable across time and territory dimensions. The monthly budget figures per territory are held in an Excel spreadsheet that contains datetime and territory columns. Thanks to TI and the some new PPS goodness called the Tabular Data Provider (TDP), filtering across these common “dimensions” will be possible. Multi-data source filtering and the TDP are also worthy of a post on their own at some point soon.
Update 26-Jun-2007: Apparently the common dimension slicing capability mentioned above e.g. Geography will probably not be available. The cross data source TI capability is being worked on at the moment.
Data Source Configurations
As mentioned above, data sources are where we will configure the TI foundation meta data. The two screenshots below are of the CTP3 Time tab in the OLAP and Excel data source UIs. TI uses this meta data to create internal mapping structures that are referenced by the STPS to enable the cross-data source time slicing.
In the OLAP area we need to configure one reference member of the time dimension, relative to which all others will be calculated. So we tell it that the reference member is, say, 01 July 2006 and subsequently pick the same date from the “Maps to…” date picker. Now that PPS “knows” which member of the dimension we consider to be July 1st 2006 it can then calculate every other date and period relative to that one. Armed with this knowledge (and the current system time) calculating time relative to now/yesterday/last year becomes much easier. Naturally, we also need to provide more meta data about the levels that make up the time dimension, and of course the dates contained in the base dimension need to be contiguous.
In Excel we don’t define date hierarchies so all that is needed is meta data about how we want the date column data to be broken down. You can also see the fiscal start month dropdown in the bottom right corner.
