August 12, 2010 at 2:31 pm
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
User
Date
Group
Measure1
Measure2
...
Fact Table 2
User
Date
Group
Status
Measure1
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.
Any ideas????
August 12, 2010 at 2:37 pm
By the way, I am not sure what I am trying to do can be done. I am not very good at Excel Pivots. Any help/discussion/possible solutions would be greatly appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply