Distinct count for multiple fact tables in the same cube RRS feed

  • Question

  • I'm fairly new to working with SSAS, but have been working with DW environments for many years.

    I have a cube which has 4 fact tables.  The central fact table is Encounter and then I also have Visit, Procedure and Medication.  Visit, Procedure and Medication all join to Encounter on Encounter Key.  The relationship between Encounter and Procedure and Encounter and Medication are both an optional 1 to 1.  The relationship between Encounter and Visit is an optional 1 to many.

    Each of the fact tables join to the Patient dimension on the Patient Key.  The users are looking for a distinct count of patients in all 4 fact tables.  

    What is the best way to accomplish this so that my cube does not talk all day to process?  Please let me know if you need any more information about my cube in order to answer this.

    Thanks for the help,


    Thursday, June 12, 2014 3:24 PM


  • Hi Andy,

    Each distinct count measure cause an ORDER BY clause in the SELECT sent to the relational data source during processing. In SSAS 2005 or later, it creates a new measure group for each distinct count measure(it's a technique strategy for improving perormance). Besides, please take a look at the following distinct count optimization techniques:

    • Create Customized Aggregations
    • Define a Processing Plan
    • Create Partitions of Equal Size
    • Use Partitions Comprised of a Distinct Range of Integers
    • Distribute the Hash of Your UserIDs
    • Modulo Function
    • Hash Function
    • Choose a Partitioning Strategy

    For more detail information, please refer to the article below:
    Analysis Services Distinct Count Optimization: http://www.microsoft.com/en-us/download/details.aspx?id=891

    In addition, here is a good article about SSAS Best Practices for your reference: http://technet.microsoft.com/en-us/library/cc966525.aspx

    If you have any feedback on our support, please click here.

    Hope this helps.

    Elvis Long
    TechNet Community Support

    • Marked as answer by Elvis Long Monday, June 23, 2014 2:53 AM
    Monday, June 16, 2014 6:06 AM