locked
Need 2 Measure Groups for One Fact table RRS feed

  • Question

  • All,

    I have one fact table that contains all the measures.  The problem is that I want to have two measure groups that point to this one fact table.  Some measures would be in measure group A and some in measure group B but the underlying source still comes from the one fact table.  I haven't found a way to do this.

    My solution currently is I have created another fact table that is an exact copy of the main one, that way I can create two measure groups.  The issue is performance, it takes 4 minutes to build and if I took one of those fact tables out it would be cut in half.

    I would have thought there would be a way to create a measure group and drag what you want in there..but it seems that this isn't the case that you can only create measure groups based on how many fact tables you have.

    Any help would be appreciated.


    Thanks,
    Mindy

    Wednesday, September 22, 2010 2:06 PM

Answers

All replies

  • Hi,

    If you have distinct count measure you can put that in separate measure group:

    http://www.ssas-info.com/analysis-services-faq/28-design/118-how-create-distinct-count-measure-separate-measure-group-from-same-fact-table

    Another option you can cansider is to use Display Folder property of the meausre if your requirement is so.

    Thanks

    Wednesday, September 22, 2010 2:38 PM
  • just create two seperate views at the database level both pointing to the same fact table but only with the measures that you want. Then pull those views into the DSV.
    FJK
    Wednesday, September 22, 2010 3:20 PM
  • I will see about the distinct counts but some of hte measures are sums and some are counts..so I don't know.    Also these are not calculated measures they are just measures from the tables.

     

    Frank,  this is what I have now...which to me doesn't make sense why I have to create two views going to the same table. Why can't I be able to create a measure folder and put which measures from the one fact table into that measure group?

    Wednesday, September 22, 2010 5:10 PM
    • Marked as answer by Raymond-Lee Friday, October 1, 2010 10:13 AM
    Wednesday, September 22, 2010 8:08 PM
  • Hi,

    We can create 2 Measure groups for a single fact table. For Example: Create a measure and use aggregate function "Distinct Count". This will automatically go in Measure group1 and then create another measure with aggregate function "Count". This will go automatically to another measure group2. But after that if you create any measures those will go to measure group2.

    Please correct me if wrong


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Friday, November 1, 2013 3:22 AM