MDX: join measures from two fact tables if you have bridge table that connects dimension keys

0
votes

I have two fact tables:

fact_A:

DimensionOneKey    Amount1
---------------------------
       1              10
       2              11

fact_B:

   DimensionTwoKey     Amount2
---------------------------------
           1              5
           2              6

And I have bridge table that contains many-to-many relationships between DimensionTwoKey and DimensionOnekey

BridgeTable:

DimensionOneKey      DimensionTwoKey
------------------------------------
      1                   2
      2                   2

Currently in the cube I have Measure Amount1 and Measure Amount2, but I do not have connection between those two amounts that bridge table provide me.

One solution that comes to my mind is to join Bridge table with one of the facts and to add missing measure, for example add Amount2 to fact_A. But that way I would duplicate Amount2 in the cube.

Is it possible to somehow associate these two measures in the cube with the help of bridge table, just by adding some kind of relationship between two measures, and not with the way above, so the end result would make possible to create next report:

Result:

DimensionOneKey DimensionTwoKey Amount1 Amount2
------------------------------------------------
1                    2            10       6
2                    2            11       5
1
ssasmdx

1 Answers

1
votes

Is it possible to somehow associate these two measures in the cube with the help of bridge table, just by adding some kind of relationship between two measures, and not with the way above, so the end result would make possible to create next report: Yes you can do this. You need to take a look at Many-to-Many relationship in DimensionUsage tab of your SSAS project. The Idea is that your DimensionOne will join to a BridgeTable just as you have decribed. This bridge table then joins to DimensionTwo.