Data modeling question

[NanPowerBI]

I have a business case, where we push marketing campaigns to potential referring physicians (a fact table), and we also track the referring physicians on a medical procedure (another fact table) . The general direction is to determine, if the marketing campaigns are effective in driving growing procedure referrals. 

How should I construct the 2 fact tables with dimensions such as doctors, date table? that the 2 facts are connected by the doctors, and there is a lot of business rules to compare the campaign date, and the procedure date? Should I create a cross join table for all possible combinations of campaigns and procedures?

[AlexisOlson]

I doubt you want to do any crossjoins, at least not for setting up the data model.

I'd imagine you'd have multiple dimension tables like Date, Physician, and Procedure that are all related to both the marketing information and procedure referral fact tables. You might have other dimension tables like Campaign that are related to one or both of the fact tables.

I'd recommend doing a bit of reading on star schemas before starting:
https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

[NanPowerBI]

@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!

[AlexisOlson]

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.