locked
Distinct count across multiple fact tables RRS feed

  • Question

  • I'm trying to get a distinct count measure working across multiple fact tables and can't figure out how to do it.

    Here is my design.  I have a Ship fact table and a Open Order Snapshot fact table.  They don't share some of the same dimensions so it didn't make sense to combine them.  Also, the Order table is a snapshot and Ship is aggregate.  This all works fine. 

    But we also have some calculations that aggregate Ship and Open Order into a single value.  Users understand that this does not provide useful data if they filter on a dimension specific to one fact table (for example a Reason dimension that is on the order but not ship).  These aggregates on Qty, $, etc. work fine, except for a distinct count on the product number (product dimension is on both fact tables). 

    I tried various methods of adding a distinct count - the distinct count measure, a many to many setup with bridge table, but no matter what, in the measure group for the count, I need to set the associated fact table in the relationship with other dimensions.  I need to pick either Ship or Open Order, which will limit my count.  I tried adding duplicate tables so that I can have multiple distinct counts, then associating one with Ship and one with Open Order, but then I have an inflated number.  For example, if Ship has product A and B, while Open Order has product A and C, I can calculate distinct count 2 + distinct count 2 = 4, but there are only really 3 distinct products. 

    I'm trying to come up with a calculation or maybe a dynamic set that will union my product numbers and distinct over that, but haven't had much success with that.

    Any thoughts on how I may get this to work?

    Here is a simplified version of my dimension usage:

    

    Thanks,

    Scott


    Scott

    Thursday, April 18, 2019 1:49 PM

Answers

  • To also be subselect-safe, declare a dynamic set of [product dimension].[product].[product], and create a calculated measure along the lines of

    count ( existing nonempty ( [dynamic product set], {measures.sold , measures.open} ) )

    No need to add measure groups that way.


    Expect me to help you solve your problems, not to solve your problems for you.

    • Marked as answer by ScottT003 Tuesday, April 23, 2019 1:15 PM
    Friday, April 19, 2019 8:31 AM

All replies

  • Start by defining the calculation logic. The users need to know the number of products that are related to Ship and/or Open Order - how, exactly?

    Expect me to help you solve your problems, not to solve your problems for you.

    Thursday, April 18, 2019 3:48 PM
  • Hi Alexei,

    For example, they would filter on a date (common to both fact tables) and need to see the total distinct products on that date.  If product A and B were sold on that date and product A and C are open on that date, they need to see a count of 3 (A, B and C).  But now I can only figure out how to give them 4 (A and B = 2 distinct across Ship + A and C = 2 distinct across Open Orders).

    I'm trying to figure out how to get the products as they relate to Ship union with the products as they relate to Open Orders and distinct over that.  Qty and $ work fine because a sum of the two numbers is correct.

    Scott



    Scott

    Thursday, April 18, 2019 11:02 PM
  • Hi ScottT003,

     You could try to change dimension table which will show records related to Ship Fact table  and Order Fact table.

    create  table allr (name varchar(30), id int)  insert into allr values ('a',1),('b',2),('c',3), ('d',4), ('e',5)
      create  table f1 (v1 int, id int) insert into f1 values (12, 1), (2,4)
       create  table f2 (v2 int, id int) insert into f2 values (12, 2), (3,1)
       
       select a.*  from allr a  where  exists(  select 1  from f1 b where a.id =b.id) or exists ( select 1  from f2 c where a.id =c.id)
      

    Then create measure count row to see whether it works or not

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 19, 2019 7:09 AM
  • To also be subselect-safe, declare a dynamic set of [product dimension].[product].[product], and create a calculated measure along the lines of

    count ( existing nonempty ( [dynamic product set], {measures.sold , measures.open} ) )

    No need to add measure groups that way.


    Expect me to help you solve your problems, not to solve your problems for you.

    • Marked as answer by ScottT003 Tuesday, April 23, 2019 1:15 PM
    Friday, April 19, 2019 8:31 AM
  • Thanks Zoe and Alexei,

    Zoe, SQL solutions didn't work for me as it wouldn't be accurate depending on the filtering context.

    Alexei, that worked well.  I couldn't come up with an accurate calculated measure until your suggestion.

    Scott


    Scott

    Tuesday, April 23, 2019 1:18 PM