Granularity In two different Dimensional Models

[caladsantiago]

I have built two Dimensional Models, one for Sales business process and another for a Budget business process.
The Sales has a Client dimension with an attribute called channel, the Budget model can only have channel but not Client, the company doesn't do it in the client granularity, so I create a Degenerate Dimension in the Budget Fact table for the channel in this Model. Is this approach ok ? How can I join this two different dimensions inside an Analysis Services Cube? I've tried to create a relation between this two attributes in the DSV but when I explore the cube, the Budget value its the same for every channel when I use the client dimension channel, and its the same using the DD channel from budget, Sales are always the same for every value of the DD.

[Mike Honey]

Hi ... calad ?

It sounds to me like you should take a step back from SSAS and redesign your dimensional model. Probably Channels should be a new dimension with relationships to your Sales and Budget Facts. Move all the Channel-related attributes to this new dimension.

Once you have your data structured that way, you can build a single Channels dimension in SSAS and relate the two Facts to it, then use that in your multi-Fact analysis.

Good luck!
Mike