Data Warehouse Structures normalised,star,snowflake,hybrid schema
- 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
- Data warehouse structures are hosted as tables within the database engine of SQL Server 2008 and 2012.
- There are different data warehouse schemas that can be implemented when creating a data warehouse as outlined in the table below.
- Normalisation is the process of organising data and tables within the SQL Server database.
- This is implemented by creating tables and setting relationships between the tables to remove repetitive and redundant data.
- Transactional systems typically contain tables that are structured in a normalised manner.
- The idea is that it is easier to make modifications to the data. However, this means that retrieving the data is not as efficient.
- A star schema describes the layout of the fact tables and the dimension tables within a data mart.
- In a star schema, the fact table resides at the centre of the data mart.
- There are dimension tables that have a direct join with the fact table.
- Within a snowflake schema, the fact table resides at the centre of a data mart.
- Some dimension table have a direct relationship to the fact table.
- However, there are dimension tables that have a direct relationship with the fact table, but have another relationship to another table.
- hese relationships can also extend to further tables as well.
- An example of this can be related to a data mart about a products business entity.
- In this example, you have a one-dimension table that lists columns about the product including colour, size, category and subcategory.
- 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.
- 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.
- A hybrid schema is a data warehouse structure that combines star schema structures and snowflake schemas.
- 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.
- Dimension tables may also be shared across different data marts to provide data consistency