Data Warehouse Structures normalised,star,snowflake,hybrid schema

1. In this article i will explain the different schemas for datawarehouses with SQL Server 2012 2008 such as normalised schema,star schema,snowflake schema,hybrid schema 
2. Data warehouse structures are hosted as tables within the database engine of SQL Server 2008 and 2012.
3. There are different data warehouse schemas that can be implemented when creating a data warehouse as outlined in the table below.

Normalised schema

1. Normalisation is the process of organising data and tables within the SQL Server database. 
2. This is implemented by creating tables and setting relationships between the tables to remove repetitive and redundant data.
3. Transactional systems typically contain tables that are structured in a normalised manner. 
4. The idea is that it is easier to make modifications to the data. However, this means that retrieving the data is not as efficient.

1. A star schema describes the layout of the fact tables and the dimension tables within a data mart.
2. In a star schema, the fact table resides at the centre of the data mart. 
3. There are dimension tables that have a direct join with the fact table.

1. Within a snowflake schema, the fact table resides at the centre of a data mart. 
2. Some dimension table have a direct relationship to the fact table.
3.  However, there are dimension tables that have a direct relationship with the fact table, but have another relationship to another table. 
4. hese relationships can also extend to further tables as well.
5. An example of this can be related to a data mart about a products business entity.
6.  In this example, you have a one-dimension table that lists columns about the product including colour, size, category and subcategory. 
7. If you have 10,000 products spread across 4 categories and 25 subcategories, it is more efficient to store the subcategory information within a separate table with one record for each subcategory and relate this to a separate category table.
8. Rather than repeating the same information many times within a single dimension table to unnecessarily increase the size of the dimension table, it is more efficient to store this information in separate tables.

1. A hybrid schema is a data warehouse structure that combines star schema structures and snowflake schemas.
2. As additional data marts are added to a data warehouse, the schema of the entire data warehouse hosts a combination of the star and snowflake schema to store the data efficiently within the data warehouse for each data mart.
3. Dimension tables may also be shared across different data marts to provide data consistency