Swapping dimensions using string manipulation

When a prompt is based on a dimension, the selected value is passed as a full qualifier (Member Unique Name), not just the key. This results in limitation of the prompt use. We cannot use the prompt to filter any other dimension.

In this recipe, we will see how to over-ride this limitation by doing the string manipulation of MUN.

Consider a business case where the cube has two time dimensions (say Billing date and Transaction date). Users want a choice on prompt page to select which time dimension to filter on. Also, they will select a Date (Year or Month) on the prompt page, and we need to filter the appropriate time dimension.

Getting ready

I added a role playing dimension to the GO Sales cube for this. This is called 'Transaction Time Month'. Ideally, it should join to the fact based on a different key than the pre-existing 'Time Month Dim'. However, this being just for illustration, I have used the same key.

You can download this cube from the...