Building dimensional model from multiple sources

Question

I have a dimensional model built from multiple sources - online and retail. I have following dims - date, teammember, division, store. And fct - sales.

fct table
date_id
teammember_id
division_id
store_id
source
qty
discount
total_amount
etc

The source column above identifies if the data is from online or retail. This is already running in prod and has data populated.

Now for the online sales, there is a channel dimension that has to be added so that the business can filter accordingly. That channel isn't available for the retail. And there is a jobcode that has to be added for the retail that isn't available for online. how can those dimensions be added to the fact table?

Since the new dimensions are not common for online and retail, how to go about the design?

Answer 1

Add a FK to you fact table for the Channel. For Retail sales populate this with the key that points to the "Not Available" (or whatever you've chosen to call it) record in your Channel Dimension

Update

If adding columns to a fact table is not going to change its grain then there is no issue. If the grain of the fact table would change then there are two options:

1. Create a separate fact table so that the grain of the original fact table doesn't change
2. Perform an impact analysis on changing the grain of the fact and, if it is acceptable, re-define the grain and update any downstream objects/processes affected by the change

If it turns out that, for example, your online and retail transactions have significantly different attributes then it might be sensible to create separate fact tables for each type and then, possibly, a third fact able that includes only the common attributes from each. But only you can decide this, based on the degree of overlap between the 2 transaction types, the need to report on each type separately v. report across all transactions, etc