none
Best star schema solution for one to many to many relationship.

    Question

  • Advancing data visualization requirements are stretching the capabilities of my Kimball Model based (start schema) data ware house.  I have a need to display measures and provide filters for two dimension attributes (different dimensions).  The fact table has a one to many relationship to the first dimension.  The second dimension belongs currently to another measurement group, but customers want to filter the first fact table by the second dimension.  The only way I see to do this is either to create a bridge table, which seems to break the star schema, or to create a whole new fact table with a different grain (which is doable, but seems overkill).

    Example:

    Fact table: FactPurchases

    Dimension 1: DimCustomer

    Requested Dimension: DimCustomerAvailablePaymentMethods

    One customer has zero to many purchases.  One purchase has one customer.

    One customer has one to many available payment methods.  One available payment method has one to many customers.

    How would I model the available payment method in relation to customer in relation to the fact table so that users could filter purchases by available payment methods, not by the payment method used which could be captured normally in the fact table?  I understand this requires unduplicated MDX logic on the calculation.  

    My data actually revolves around education, the fact is enrollment, the first dimension is student and the dimension trying to be added in is student group.  One student can be in multiple student groups and one student group can have multiple students.  I have a student group fact table with complimenting dimensions, but enrollment measure are in the enrollment fact measure group.

    Thanks for any insight into this.  I go now to read my Kimball bible again.

    Thursday, April 05, 2018 11:49 PM

Answers

  • Is this for Tabular or Multidimensional? Either way, you will need a bridging fact table. I imagine your second fact table would suffice?

    By the way, you should see your Kimball book as a traveller's guide, not a bible. Following it religiously will get you into some awkward situations.


    Richard

    • Marked as answer by Greenlizard72 Wednesday, April 18, 2018 4:29 PM
    Monday, April 09, 2018 12:59 AM

All replies

  • Is this for Tabular or Multidimensional? Either way, you will need a bridging fact table. I imagine your second fact table would suffice?

    By the way, you should see your Kimball book as a traveller's guide, not a bible. Following it religiously will get you into some awkward situations.


    Richard

    • Marked as answer by Greenlizard72 Wednesday, April 18, 2018 4:29 PM
    Monday, April 09, 2018 12:59 AM
  • Multidimensional.  I decided to create a new fact table with the grain I need.  I will still need to create a bridge table because of the cardinality, but it won't be the fact table.  I will update this with my working solution once achieved.

    I hear you, but I think instead I will still look to it as a bible and then chastise myself when I have to break the rules.  A more Catholic approach.

    Wednesday, April 18, 2018 4:29 PM