I have an SSAS Cube that has two measure groups (from two fact tables). Grain is not the same, as second measure group has one addition level of grain. Measures are different in each measure group. So basically trying to combine apples and oranges. Data will be served to end users via an SSAS connected Pivot in Excel. I cannot get the Pivot to provide the data in the format I want. Example:
Fact Table 1
Fact Table 2
I am trying to get the Pivot to look like this:
User Date Group Fact1.Measure1 Fact1.Measure2 Fact2.Status1(Measure1) Fact2.Status2(Measure1)... For however many status there are for this User/Date/Group (Same grain as Fact1)
There is also another caveat: The source system that feeds Fact Table 2 will add more "Statuses" in the future.
What I am trying to do could be accomplished with a SQL Pivot statement on Fact two, each "Status" in Fact two would become a column with the Measure being the value of that column. Problem is, this would have to be modified each time a new status is added to the source system.