locked
Need multiple distinct counts, have 1 fact and 1 dimension RRS feed

  • Question

  • I am using SSAS 2005. I have 1 fact table and 1 dimension. I would like to create multiple distinct counts in 1 Measure group, at least I would like them to appear as in 1 measure group to end-user. I have tried role-playing dimensions, and a roll your own approach that work in limitation but didn't scale. Any help and advice would be great.
    Friday, September 17, 2010 7:12 PM

Answers

  • Unfortunately because distinct counts always fall in their own measure groups, there isn't any "neat" way of doing this that I can think of.

    One thing you can do though that is kind of a hack way to do this and would require some maintenance. You could define a calculated measure for each of your distinct count base measures and specify your distinct count (do this for each distinct count). Then put all of your "distinct count calculations" in the same display folder. Then set the visible properties for your original (base) distinct count measures to False so the user doesn't see them. if the front end tool is something that can see and display folders (like Excel 2007 and up), then they would just see something a folder called something like "Distinct Counts" with all of your DC measures in there.

    Not pretty, but it would work


    FJK
    Friday, September 17, 2010 7:20 PM
  • You can create a measure with aggregation function SUM within the same measure group, and then change the aggregation function to DistinctCount. However, it is recommended that create a single measure group for distinct count, because the Distinct Count requires their own specific processing/partitioning strategy. In this way, you can get better performance and this reason why BIDS create distinctCount in a new group.

     

    Hope this helps,

    Raymond
    Raymond Li - MSFT
    Monday, September 20, 2010 1:28 PM

All replies

  • Unfortunately because distinct counts always fall in their own measure groups, there isn't any "neat" way of doing this that I can think of.

    One thing you can do though that is kind of a hack way to do this and would require some maintenance. You could define a calculated measure for each of your distinct count base measures and specify your distinct count (do this for each distinct count). Then put all of your "distinct count calculations" in the same display folder. Then set the visible properties for your original (base) distinct count measures to False so the user doesn't see them. if the front end tool is something that can see and display folders (like Excel 2007 and up), then they would just see something a folder called something like "Distinct Counts" with all of your DC measures in there.

    Not pretty, but it would work


    FJK
    Friday, September 17, 2010 7:20 PM
  • Thanks for the tip. I probably will not have any results until next week. I will get back to you then.
    Friday, September 17, 2010 7:54 PM
  • You can create a measure with aggregation function SUM within the same measure group, and then change the aggregation function to DistinctCount. However, it is recommended that create a single measure group for distinct count, because the Distinct Count requires their own specific processing/partitioning strategy. In this way, you can get better performance and this reason why BIDS create distinctCount in a new group.

     

    Hope this helps,

    Raymond
    Raymond Li - MSFT
    Monday, September 20, 2010 1:28 PM
  • Thanks for all your help. I have played around with both approaches and I am still getting a wrong answer. That tells me I am doing somehting wrong somewhere. I hope somebody can ofer some help.

    Here is the data

    WeaponDescription	offenderid
    Blunt Object        20132
    Knife	         4654
    Knife	         4654
    Knife	         4654
    Knife	         4655
    Knife	         4655
    Knife	         4655
    Other	         3893
    Sharp Object       14285
    Sharp Object       14285
    Sharp Object       14285
    Sharp Object       14285
    Sharp Object       18194
    Sharp Object       18194
    Sharp Object       18194
    Sharp Object       18194

    Here are the results (sorry about the formatting)

                 Count   Distinct
    Blunt Object       1       1
    Knife            6       2
    Other           1       1
    Sharp Object      8        1
    

     

    Can anyone tell me why I am getting 6 and 2 for knife, which is correct, but 8 and 1 for Sharp Object. Shouldn't it be 8 and 2??

     

    Thanks.

    Monday, September 20, 2010 7:10 PM
  • If you have measure1 and measure2 in measure_group1, and want to separate measure2 in measure_group2, do following:

    1, create a fake measure using DISTINCT COUNT. This aggregation will let you to choose a separate group (here name it to measure_group2).

    2, set the fake measure to invisible.

    3, create a calculated member, point to [measures.measure2], and associate it to measure group2.

    done.


    • Edited by Coonlee Saturday, August 5, 2017 10:33 AM
    Saturday, August 5, 2017 10:33 AM