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