Post reply

Data warehouse - how to handle 2 FACT tables?

  • July 25, 2022 at 9:15 pm

    #4068094

    Hello, I am quite new to data warehousing and was just wondering what people think the best approach would be for the below? We have a star schema in place however I have been tasked to add a new Fact table (the payments one) into our existing SSAS model.

    I am planning on using the Contract Dimension as a conformed dimension to filter both Sales and Payments (joining on the ContractKey column). My only problem is I can imagine the business will eventually want to see PaymentAmount by SoldTeamKey (this would be the total payments grouped by team, however this only exists currently on the FACT.Sales table)

    What would the best approach be? To add all of the conformed dimensions to my Payments table so it also contains SoldDateKey, SoldTeamKey etc.. or to move the SoldDate/SoldTeam columns to the Contract dimension table and use the fields on there to filter both? Just wondering what people think the best approach would be?

    Appreciate any advice

    Jess

    Capture

  • July 26, 2022 at 11:35 am

    #4068323

    Yes, assuming it is possible to add other dimension keys into the new fact table, it should always be your goal to have all the relevant (conformed) dimensions linked to any new fact table you create.

    You're starting to think at what end users may want to see, which is a good thing 🙂

  • July 29, 2022 at 2:15 pm

    #4069987

    From the man who "wrote the book" on Star Schema: http://blog.chrisadamson.com/2011/11/conformed-dimensions.html

    And the Data Warehouse people:  https://www.kimballgroup.com/2011/06/design-tip-135-conformed-dimensions-as-the-foundation-for-agile-data-warehousing/

    For Microsoft Power BI types:  https://guyinacube.com/2021/02/10/handling-multiple-fact-tables-in-power-bi/

  • July 29, 2022 at 2:18 pm

    #4069990

    And to answer your question, I'd make the SharedTeamKey it's own dimension and link to both Sales and Payments.

    You need a shared Date dimension as one of the conformed dimensions. May choose to do a role-playing dimension to handle

  • August 23, 2022 at 9:13 am

    This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply