Forum Discussion
Data modeling question
AlexisOlson Thank you for your prompt reply! Actually its a reverse engineering where the original design has the cross join table with all campaign/procedure combinations, has relationship between fact tables (procedure referrals, to referral/campaign cross joint table), and use many calculated columns to compare campaign date and procedure date. This orignal model expands the granularity because a procedure can have multiple referring physician, and a campaign (Called TAEvent in the model) can have multiple targeted physicians. I easily lose track of whether we are taking about campaign, referrals, or procedures. below is my model, of course it breaks all existing visuals but hope to be more extensible and easier to understand - does this make sense? should I keep these circled 1 to many relationships as seperate tables, or join them in Power query to be more star schema like?
Here I believe procedures and TAEvents (campaign) are fact tables.
Much appreciated!
Hmm. I can't quite tell how all this works but having relationships between fact tables is a good way to get confused. For example, to me, it seems like Procedures should be a dimension table of unique types of procedures rather than including a date dimension. You've got those dates in the Referrals fact table.
Expanding granularity isn't necessarily a problem as long as you make sure your numerical columns will still aggregate properly. Note: just in case it's not clear, expanding granularity isn't synonymous with a crossjoin; though it is like a filtered crossjoin.
There's obviously a lot going on and I only have a vague understanding of it, so I can't give a lot more specific advice. My main high-level piece of advice though is to structure your model so that each table can clearly be categorized as a dimension table or a fact table, not a hybrid.