locked
Distinct Count RRS feed

  • Question

  • How many times we can assign Aggregationfuntion = DistinctCount for a cube.
    Saturday, May 22, 2010 11:52 AM

Answers

  • You can add as many distinct count measures to a cube as you like using different measure groups and your are not limited to the existing Measure Groups of your cube. You can add as many Measure Groups to your cube as you like using Names Queries.

    One of my customer uses a cube with 3 "real" Measure Groups and 7 Disitinct Count Measures and a lot of partitions ;-)

    If you add an distinct count measure to an exisiting cube using BIDS you will see that a new Measure Group will be created automatically. This happens due to the fact that Distinct Count is treated completely different than other aggregate functions for exmaple SUM and is recommended best practice.

    If you nee further information on this topic I would recommend the following white paper from SQLCAT

    http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=65df6ebf-9d1c-405f-84b1-08f492af52dd

    or the following book that describes the query engine and the storage engine in great detail

    http://www.amazon.de/Microsoft-Server-Analysis-Services-Unleashed/dp/0672330016/ref=sr_1_1?ie=UTF8&s=books-intl-de&qid=1274548596&sr=8-1

    Hope this helps

    Tom

    • Proposed as answer by Darren GosbellMVP Monday, May 24, 2010 12:58 AM
    • Marked as answer by Jerry Nee Friday, May 28, 2010 8:49 AM
    Saturday, May 22, 2010 5:22 PM

All replies

  • Hi,

    Only one for each measuregroup in your Cube. With 4 measuregroups you can have maximum 4 distinct count measures.

    hth,

    Cees 


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Saturday, May 22, 2010 2:39 PM
  • You can add as many distinct count measures to a cube as you like using different measure groups and your are not limited to the existing Measure Groups of your cube. You can add as many Measure Groups to your cube as you like using Names Queries.

    One of my customer uses a cube with 3 "real" Measure Groups and 7 Disitinct Count Measures and a lot of partitions ;-)

    If you add an distinct count measure to an exisiting cube using BIDS you will see that a new Measure Group will be created automatically. This happens due to the fact that Distinct Count is treated completely different than other aggregate functions for exmaple SUM and is recommended best practice.

    If you nee further information on this topic I would recommend the following white paper from SQLCAT

    http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=65df6ebf-9d1c-405f-84b1-08f492af52dd

    or the following book that describes the query engine and the storage engine in great detail

    http://www.amazon.de/Microsoft-Server-Analysis-Services-Unleashed/dp/0672330016/ref=sr_1_1?ie=UTF8&s=books-intl-de&qid=1274548596&sr=8-1

    Hope this helps

    Tom

    • Proposed as answer by Darren GosbellMVP Monday, May 24, 2010 12:58 AM
    • Marked as answer by Jerry Nee Friday, May 28, 2010 8:49 AM
    Saturday, May 22, 2010 5:22 PM
  • Hi Tom,

    My apologies, you're right about this. I should have formulated my answer more carefully.

    r,

    Cees


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Sunday, May 23, 2010 6:01 PM