Scorecard page filters for "today"

Calendar page filters are a common feature of just about every OLAP-based scorecard I have worked on. As soon as this functionality is implemented users will ask if the scorecard can auto-slice data based on the current day/month/quarter etc. Then, if needed, they can slice by another time period of their choosing.

When creating scorecard page filters BSM Builder gives developers the opportunity to specify a default member. Problem solved? Not really. Both the key and the display name of this default member are saved as part of the scorecard view definition, with time dimensions this won't do us much good at all. For example, the key of today's date may be something like [Calendar].[Day].&[20070426]. Tomorrow this value is useless as a "current day" and must be manually changed again either by the user in WSS or the developer using Builder.

One possible solution is to configure default dimension attribute members in the source cube. This alleviates the need to set a default member in Builder, however there are a few downsides:

1. the scorecard data does indeed get sliced by the default member but the member name is not displayed in the page filter box, users can't be sure what data they're looking at
2. if the cube that contains scorecard data is accessed by applications other than BSM default time member/s may cause confusion
3. you can't set a default member on a hierarchy (Year-Qtr-Month-Day) only the individual attributes that make it up; mostly the time page filter requested for a scorecard will be a hierarchy

A possible solition

So, if the scorecard view page filter remembers the default member key (and name) as set in Builder then why not pass it the same value all the time? If the current quarter member's key is always [Calendar].[Current Calendar].[Month].&[1] and the corresponding member name is always [Calendar].[Current Calendar].[Month].[Current Month] then BSM will have no problem slicing KPI data by this member every time. All we need to do is ensure that the correct members are assigned the correct key and name depending on the current date. A hierarchy that implements this type of functionality will look something like the picture below. Note that the year 2007, Q2 and April have all been replaced with Current Year, Current Quarter and Current Month respectively. Under the covers the keys that identify these members are also [Calendar].[Year/Quarter/Month].&[1]


We can ensure that the current day/month/year members of an attribute always have the same key by using a view in the data warehouse that references the base calendar dimension table to dynamically calculate these values using GETDATE() as a reference point. To correctly configure each attribute in the dimension the view will need 3 columns (key, name and order) for each "current" attribute we want to create.

1. ...Key - a numeric key for all values (e.g. 200702 = February 2007) except the calculated current period which will be assigned a 1
2. ...Name - human-readable name (CY 2007, April etc.) for all members except the current which will be assigned a static value like "Current Month", "Current Year" etc.
3. ...Order - a numeric key to order the attribute members so that the "Current ..." sits in the right place.

Listing 1 contains the SQL code for creating (and populating) a sample calendar dimension and listing 2 contains the SQL for creating a view off that table and the appropriate sets of 3 fields.

Configuring the Dimension Attributes

When it comes to the time dimension itself it is important to note that I am very definitely not talking about dynamically altering the values of the base dimension key. The dimension key is sacred and does not get touched. All we want to do is add extra attributes that are related to the dimension key via attribute relationships. On top of the regular (Year, Month, Day etc.) attributes we will also have Current Year, Current Month, Current Day etc. attributes thanks to the output of the view. The T-SQL view code determines who gets the key value of 1 and name of "Current..." each day. We can then create separate hierarchies, one containing the regular attributes and one for the current attributes.

When configuring one of the new attributes in BIDS use the ...Name and ...Key fields in the KeyFields and NameFields attribute properties. The trick to getting members ordered correctly (because we have a 1 floating about in the key field) is to:

1. add the ...Order field as a new dimension attribute
2. create an attribute relationship on the attribute that will use the value in the ...Order attribute to order its members
3. in the attribute to be ordered set the OrderBy property to "AttributeKey", and set the OrderByAttribute property to reference the ...Order attribute created in step 1
4. set the newly created ...Order attribute's AttributeHierarchyEnabled property to "False" - we don't need it for anything other than ordering

The shot below shows the Current Calendar Day attribute, it has a relationship to the Current Calendar Day Order attribute to enable ordering. Meanwhile the Current Calendar Day Order attribute itself has been disabled, hence greyed out.

Processing

Because we are changing a few values in the time dimension as often as the lowest granularity (normally a day) the calendar dimension needs to be regularly processed. Again, we are not doing anything to the dimension key here, all that is being done is changing the key and name values of some attribute members that are related to the key. A ProcessUpdate will be fine to ensure the appropriate attributes contain the most relevant information.

Summary

This is certainly not a silver bullet solution for all calendar dimensions. In SSAS 2005 tuning dimensions to extract maximum performance from your cube is essential. Adding extra attributes that (in this case) necessitate regular processing of a dimension like time, which mostly quite static in nature, should be something which should be carefully considered before moving forward.

That said this technique can be extended further if need be. For example you can incorporate "previous" and "next" periods, all that needs to be done is to agree on what the key and name values will be, maybe -1 for "previous" and 2 for "next". The current attribute members can also be useful when creating cube-based reports that need some form of "current" functionality - the MDX becomes much easier to write.

Now, in BSM (once the appropriate dimension attributes and hierarchies are in place) you can simply choose "Current Month", "Previous Quarter" or whatever you have configured as your default member of a scorecard page filter with confidence knowing that it will always slice on the right value without any user intervention (as long as the Calendar dimension is regularly processed).

Source code .zip file containing the SQL code to a sample calendar dimension and the subsequent view, database backup and an SSAS solution containing a regular Calendar hierarchy and also a Current Calendar hierarchy can be downloaded here.