data-warehousesnowflake-schemastar-schemafact-tabledatamart

How different fact tables are handled to share some common dimension tables in data marts in star model?


I am quite new to DW and I am just learning the stuff. I read on the Internet that after the ETL process, DW data is then stored in some data marts for some reasons such as ease of use. Each data mart can use a structure. Let's say a data mart is using star structure. Now my questions arise:

  1. First of all, can a data mart use two structures, for instance, star and snowflake?

Assume we have two data marts that are using star structure. Suppose that both of them have only one fact table and some dimension tables. The thing is, it turned out that some of the dimension tables in the first data mart are the same as the second one.

  1. While considering they are in different data marts, what should we do? Should we duplicate the tables in different data marts?
  2. What if the fact tables were in the same data mart? Should we duplicate dimension tables or just create a foreign key to the table we already have?

Solution

    1. Snowflake describes one or more objects in your model. So some parts of your model could be snowflaked and others not

    2. Datamarts are logical groupings of your facts and dimensions, not physical ones. So you don’t duplicate these tables and they can appear in as many datamarts as necessary