Datawarehouse and Data-marts

Data marts are 2 type

• 1) Dependent Data-mart:  Data marts build on Data warehouse. Top down approach. First Data warehouse got built and to ease of use later on Data marts built which are dependent on existing Data Warehouse.
• 2) Independent Data-mart: This is bottom up approach. Data marts have been built based on company or organization requirements and later on created DW by integrating all these data-marts.

Data Warehouse is:

• Subject Oriented
• Integrated
• Non Volatile
• Time Varient

Difference between Star schema and Snow-flake schema:

Comparison chart

Snowflake Schema versus Star Schema comparison chart

	Snowflake Schema	Star Schema
Ease of maintenance / change	No redundancy, so snowflake schemas are easier to maintain and change.	Has redundant data and hence less easy to maintain/change
Ease of Use	More complex queries and hence less easy to understand	Lower query complexity and easy to understand
Query Performance	More foreign keys and hence longer query execution time (slower)	Less number of foreign keys and hence shorter query execution time (faster)
Type of Datawarehouse	Good to use for datawarehouse core to simplify complex relationships (many:many)	Good for datamarts with simple relationships (1:1 or 1:many)
Joins	Higher number of Joins	Fewer Joins
Dimension table	A snowflake schema may have more than one dimension table for each dimension.	A star schema contains only single dimension table for each dimension.
When to use	When dimension table is relatively big in size, snowflaking is better as it reduces space.	When dimension table contains less number of rows, we can choose Star schema.
Normalization/ De-Normalization	Dimension Tables are in Normalized form but Fact Table is in De-Normalized form	Both Dimension and Fact Tables are in De-Normalized form
Data model	Bottom up approach	Top down approach