data-warehousedimensional-modelingfactstar-schema-datawarehouse

Best approach to build factless fact in star schema DWH

I have a case where I'm building factless fact table for my DWH. There are 2 dimension that I want to ask for this case: location and store. I have 2 approach.

  1. Building dim_store and fact_account. Then put all the location data into the fact_account table

  2. Building dim_store, dim_location, and fact_account. Then put the store_id and location_id on the fact_account

Here is the visualization for these 2 approaches:

1. FIRST APPROACH

2. SECOND APPROACH

Which is the best approach and why?

Thank you in advance.

Solution

  • Option 1 is definitely wrong, what is described there is not a dimensional model.

    Option 2 is a correctly designed dimensional model. Whether it is the best way to dimensionally model your data depends on your reporting requirements.