One Fact Table or Two

[KKumar]

I am working on a star schema for a construction project model. We have 2 groups of users- one who wants to see only approved projects and the other group want to see project (approved and unapproved).

One option was to build 2 facts, one only with approved and other one with all the project. But this option will increase ETL Maintenance and increase db storage.

Second option was to create one fact table with all the projects and adding an indicator to the fact (Approved or Unapproved) and then creating a view that filters only approved projects for the first group of users. This option is less load on ETL but not sure if keeping indicators in fact is good idea.

Not sure if there is any other option.

Please advise.

Thanks

[hkandpal]

you should try the 2nd option, keeping the indicator in your fact. You can store the indicator in a junk dimension. If tomorrow you decide to have stage other than approved and unapproved for a project then this way you don't have to change the ETL code.


thanks