Friday, 24 November 2017
Task Pane 
Login
username:
password:
Remember Me
|Signup
Our Sponsors
Current Poll
SQL Server 2008
Are you ready for the upcoming SQL Server 2008 release?







[show results]
Dynamic time filtering in Analysis Services
This article describes how a dynamic "current month" time filter can be created in an Analysis Services cube.
Author:  Created on:

Purpose
The overall idea is to create a calculated member which when selected will null out anything but values for the current month and its descendants. This is really valuable for say an Excel or OWC pivot table report where you want to always see a current month trend. Simply expand the report to the level of granularity you need (e.g. daily), then apply the dynamic time filter and there you go - an automatically updating current month trend!

Current month dynamic filter


Firstly we need to get the time dimension member corresponding to the current month.
There are two general ways of doing this i.e. what is the current month:

1. Based on system date:

STRTOMEMBER(
"[Date].[Year].&["+ cstr(FORMAT(VBA!Now(),"yyyy")) +"].&["+ cstr(FORMAT(VBA!Now(),"q")) +"].&["+ cstr(FORMAT(VBA!Now(),"m")) +"]")

N.B. the above syntax will depend on your time dimension and how the uniqueness of members is defined.

2. Based on the filtered crossjoin technique and a measure (or even better - a specially designed active dates measure that you populate)

(
TAIL
(
(FILTER
(
DESCENDANTS([Date].[All Date], [Date].[Month],leaves) ,
NOT ISEMPTY( ([Measures].[temp hidden Active Date Range]) )
)
)
).ITEM(0)
)

Either way the above to techniques will give you the current month member which I will from now refer to as Current_Month_Member. There are obvious advantages and disadvantages to either depending on the specific application. Sometimes you want to filter based on the current system date and sometimes you want to do so based on the data in the cube.

Constructing the dynamic filter
Rationale:
The rationale here is to construct a dynamic calculated member in a dimension which can be used to slice against all other dimensions.

Firstly we must construct a "placeholder dimension" where our calculated member can live (and slice against all other dimensions including the measures). This is a simple process of creating a one record table (or view) something like:
Placeholder_dim_key | Placeholder_dim_value
1 dummy_member

Next you must add a column to our fact table where the placeholder dimension can link to and populate it with the dummy_member's key. (again it is easiest if you fact table is a view where you can add the column as easy as "Select 1 AS Placeholder_dim_foreign_key")

Placeholder dimensions have other useful purposes such as having universal MTD and YTD members which slice against all measures. (but thats another article alltogether)

When designing dynamic time filtering you must keep in mind that if you try to use the same placeholder dimension for multiple purposes then you may be limiting your design i.e. if you wanted to see MTD (placeholder calculated member) only for the current month (another placeholder calculated member) this is impossible if both are in the same placeholder dimension. The solution is however easy - simply create as many placeholder dimensions as you need (within reason). Also as there are no real members - you can use the same dummy_member Placeholder_dim_foreign_key column in the fact table and join that to each placeholder dimension.

Thus I recommend that a placeholder dimension called Periodicity is created exclusively for use of dynamic time filters.

Ok so this is how you do it:

Step 1 – the current month itself
The idea can be as simple as making the new member something like:

Iif([Date].currentmember is Current_Month_Member, [Periodicity].[All Periodicity], Null)

Note: the [Periodicity].[All Periodicity] refers to the all member of the periodicity dimension and thus results in no filtering when slicing against all the other dimensions (including measures)

The above calculated member will result in null values for everything in the cube except values against the Current_Month_Member.

The problem with that is that all descendants and ancestors of the Current_Month_Member will also appear to be null.

Step 2 – the current month’s descendants
Thus to the above we must add a check to see if the date currentmember is a descendant of the Current_Month_Member:

Iif
(
[Date].currentmember is Current_Month_Member
OR
Isancestor(Current_Month_Member, [Date].currentmember)
, [Periodicity].[All Periodicity]
, Null
)

This again isn’t too difficult as we are simply showing the unfiltered values for descendants.

Step 3 – the current month’s ancestors
This is where it gets a bit tricky – normally the current month’s ancestors will contain the aggregated values for the current month as well as its siblings etc.

As we are filtering all but the current month’s values we want its ancestors to display essentially just the current month’s aggregate value (all the way up to the All member of the time dimension)
The values of the ancestors can be replaced by expanding the code as follows:


Iif
(
Isancestor(DATE.currentmember, Current_Month_Member)
,
(Current_Month_Member, [Periodicity].[All Periodicity])
,
Iif
(
[Date].currentmember is Current_Month_Member
OR
Isancestor(Current_Month_Member, [Date].currentmember)
, [Periodicity].[All Periodicity]
, Null
)
)

Note the tuple (Current_Month_Member, [Periodicity].[All Periodicity]) gets the unfiltered value of the current month and replaces all ancestor’s values with this.


Conclusion
Ok, so this isn't exactly a beginner's article but if you've done a bit of MDX hopefully you'll be able to put some of the above to good use. The neat part is that the hard work is done in the cube and the users can just reap the rewards.
The bottom line is that this functionlity should prove really valuable to anyone using pivot table trend reports in particular.


User Rating of Article: 10(2 votes with a rating total of 20) | Comments Thread | Number of Views: 7480