Practical Business Intelligence

Dates can be tricky things for end users to grasp in OLAP applications. Try explaining to a user that Year to Date actually refers to the current member of the date dimension as displayed on their query interface - they glaze over and lose interest immediately. It's not that the concept is difficult for them to understand, it's more that from their point of view, "Year to Date" should mean exactly that - what happened so far this year up to today. And if they open up a report tomorrow, it should reflect the change in date without them having to alter the selections they previously made.

Let's expand on that a bit. A frequent requirement is for a report to display all the activity so far this year, and all the activity last year to the same relative point in time. If you have structured your date dimension properly, it will be very easy for a user to put years on columns, month names on rows and measures in the data area. They can then restrict the years and months manually to this year, last year, and the set of months that have passed this year. It's easy - but it's a manual process.

Why is that a problem? For a start, it will soon be frustrating for end users to change their report definition every month. It will be worse if your reporting is by day rather than by month! Secondly, any kind of automated reporting will not work. This includes scheduled reports... and (crucially) dashboards. Someone will have to update them every day.

What's needed is a simple way of categorising your data so that you can drag "today" onto your analysis and for the system to know what today is. Or "this month", "this year", "the last 12 months", or whatever the requirement might be.

There are plenty of approaches to the problem. You could create calculated members for every measure individually, using the VBA!Now function to get the current date. You could create a utility dimension which contains a member for each of the custom time periods you want to provide and add it to the cube. You could set your date dimension with a default member which is calculated to the current date.

But the most intuitive way for end users to interact with the cube is not with any of these means - and there are technical drawbacks to any of the above methods as well. What users want is quick access to a set of filters which limit their data to the right set of time periods - and for those filters to sit in a logical place in their application, and to do what they say on the tin. Something like this:

The date ranges are nicely placed in the application - inside the date dimension. Therefore they are applicable to any and all measures. They do not rely on any calculated measures, and will perform as well as your aggregation designs provide (the same as if the user were to manually choose the dates). And they do what the average user would expect them to do. A user can then use the named set as a filter in their application (ProClarity, Reporting Services, Excel, etc.) In ProClarity the set can be dragged into the Background area of the setup panel, where the user can slice or combine the members. In Reporting Services it can be used as a filter when creating a data set.

It is achieved by using named sets to select just the time periods which are included. Named sets are nothing particularly complex to understand or to execute, they are simply a pre-defined set of dimension members, specified using MDX in the same way you would specify a set inside an expression. The date sets work as follows: create a set which contains "today", dynamically calculated; apply that set to a hierarchy within the date dimension; create further sets by traversing the dimension hierarchy. So, the sets are stacked on top of one another, but are all based on a set which knows what today is.

To create the set of "today's" date, on the Calculations tab create a new Named Set by clicking the appropriate icon. Give the set a name, then add the magic code which will always return the correct current date:

filter([Date].[Calendar].[Date].Members,
    [Date].[Calendar].CurrentMember.Properties("Date As Int") 
    = Format(Now(), "yyyyMMdd"))

This will have to be adjusted to fit your own date dimension. In this case my date dimension is called "Date", it contains a hierarchy called "Calendar" in which the day level is called "Date". The dimension has an also has an attribute named "Date As Int" (although this is not in the hierarchy) which is the Date member in the format YYYYMMDD.

Once you've added the member and deployed the cube, you can test it by browsing. Add the new named set to the filter, the Date dimension to the rows and add your default measure. You should see that the only member of the date dimension available in the grid is the current date.

The next step tis to apply the result of the set to your main date hierarchy. Create another named set and add code similar to the following:

exists([Date].[Calendar].[Date].Members, [TodaySet]).ITEM(0).ITEM(0)

Modify it to reference your date dimension properly, and replace "[TodaySet]" with whatever you called the first set you created.

You can now create further sets which include specified members from the hierarchy. For example, all the months in the current year to date would be a set defined as (assuming your calendar hierarchy is Year-Month-Date):

[CalendarTodaySet].ITEM(0).ITEM(0).Parent.Parent.FirstChild :
    [CalendarTodaySet].ITEM(0).ITEM(0).Parent.CurrentMember

All the months in the previous year would be:

[CalendarTodaySet].ITEM(0).ITEM(0).Parent.Parent.Lag(1).FirstChild :
    [CalendarTodaySet].ITEM(0).ITEM(0).Parent.CurrentMember.Lag(12)

After creating these two sets you can join them to get all the months in the current year to date plus the all months in last year up to the same month:

{[Previous Year To This Month], [Current Year to This Month]}

All the days in this month so far would be:

[CalendarTodaySet].ITEM(0).ITEM(0).Parent.FirstChild :
    [CalendarTodaySet].ITEM(0).ITEM(0).CurrentMember

I hope this helps - it's a little tricky to get the hang of and to see the possibilities at first. If you get stuck send me an email or message.