CODEWITHSUNDEEP
sql-serverssasolap
ceth
ceth

Reputation: 45295

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?

Upvotes: 2

Views: 722

Answers (2)

ceth
ceth

Reputation: 45295

I have found out the solution:

  • I have two records for one fact in the Facts table - one to the start of fact and one for the end of fact
  • I have one Time field in the Facts table
  • I added two int fields to the Facts table (A and B). Values of these fields are (1, 0) for starting records and (0, 1) for the finishing records.

So, I have records like:

fact1 01.10.2011 1 0
fact1 04.10.2011 0 1
fact2 02.10.2011 1 0
fact2 10.10.2011 0 1

So, sum(A) gives the count of starting facts per date. Sum(B) gives the count of finishing facts per date.

Upvotes: 1

ic3
ic3

Reputation: 7680

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

Upvotes: 0

Related Questions