Combining two Measure groups in a SSAS connected Excel Pivot

  • 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????

  • 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.

  • A Measure Group is a collection/group of measures which belong to the same underlying fact table. In SSAS, typically each Measure Group is tied to each one of the underlying fact tables.

     

    • This reply was modified 3 months, 2 weeks ago by  fguiccia.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply