Search code examples
sql-serverssasolap

One measure on the two different dimensions

I have the facts table with two date columns: StartDate and FinishDate:

enter image description here

And I have Time table which have been linked with the facts table with two different relations.

I have created Time dimension based on the Time table and included this dimension to my cube:

enter image description here

So in the result I have two different dimensions (Created_On and Updated_On) based on the Time table.

Now I am trying to browse my cube and I want to get the two counts: count of facts which have been created in some period of time and count of facts which have been updated in the same period of time.

I can get each of them separately, but cannot in the same time:

enter image description here

How can I get it? Do I need to change the warehouse structure? Do I need to change my cube? Or do I need to use MDX in this case?

Solution

  • MDX Calculated members is what you're looking for. What we need is to 'change' the dimension from 'Start' to 'Finish' date. How to convert a Start Date into a Finish date?

    We can use a string operation and change 'manually' the name of the dimension:

    StrToMember(  REPLACE( [StartDate].currentmember.uniqueName, "StartDate", "FinishDate" )

    This, if both date dimensions share their structure, will change a member from 'start date' to 'finish date' dimension.

    Now we can just create our measure by creating a tuple :

    WITH MEMBER [MyMeasureOnFinishDate] AS ([Measure].[MyMeasure], [StartDate].defaultmember,StrToMember(REPLACE([StartDate].currentmember.uniqueName, "StartDate", "FinishDate" )

    It's not tested but you get an idea how you may solve this