Data warehouse - how to handle 2 FACT tables?

  • 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

  • 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 🙂

  • 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

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

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