TRAINING (DW-4) - DATA WAREHOUSE CONCEPTS - Part4 (Star, Snow Flake, Constellation or Mixed Schema)

Schemas: 

A schema is a collection of database objects, including tables, views, indexes, and synonyms. In a database these database objects (schemas) could be designed variety of ways commonly known as schema models.
In a data warehousing database these schema objects could be organized using below schema models:

1.) STAR Schema

1. Organizes data into a SINGLE central fact table with surrounding MULTIPLE dimension tables
2. Each dimension row has many associated fact rows
3. Dimension tables do not directly relate to each other
4. All Dimension Tables are de-normalized
5. Optimized to read data
6. User friendly ,easy to understand
7. In OBIEE BMM layer only Star or Mixed Star (combination of more then one star schemas) schemas are used

2.) Snow Flake Schema

1. Normalized tables are used
2. Is also called as extended star schema
3. Two dimensional tables will be directly joined
4. Like star schema, it has only one fact table

3.) Constellation or Mixed Schema

1. It contains more than one fact with some common dimensions (Conformed Dimensions)
2. It is combination of some stars or some snows or both

What are CONFORMED DIMENSION ?

• A dimension table is shared by two or more facts then it is called as conformed dimension
• OBIA data model created using conformed dimensions