I am tasked with modeling out a rough design of a DW. Main purpose is for searching property information and files, orders that are opened on said properties.
I am struggling with the structure when it comes to A File(Parent level transaction) and order. Files are first opened and then orders are opened under those files. This can be a many to many relationship. I was informed that the main Fact table would be the order and the file would be a degenerate dimension.
But there are other tables like Parties on Orders and Files (i.e. property owner, real estate agent etc) OrderParty and FileParty that need to be considered too.
Any thoughts on how to start this model design?
Dimensional models should be designed based on the queries that they need to answer, not on the structure of any source tables.
Define what you want to measure and this will give you your facts; then define how you want to filter and aggregate these facts and that will give you your dimensions