Fact Tables and Dimension Tables in Microsoft BI Datawarehouse

1. A data warehouse is a relational database that centrally stores a consistent view of the data from transactional systems in a denormalised format. 
2. This makes the retrieval of the data more efficient than it would be from a transactional system.
3. A data warehouse will consist of one or more data marts. 
4. A data mart typically represents an area of the business such as sales or finance departments. Within each data mart, there are two types of tables created
5. fact tables and dimension tables

Dimension Tables

1. Dimension tables contain columns that hold information that represents a business entity. 
2. Examples of dimension tables can include time, region and customers. The columns will contain information that is specific to the entity. In the case of the customers dimension table, columns can include Firstname and Lastname. 
3. You can define as many columns as you want to precisely define the customer entity. However, placing unnecessary columns increases the volume and time it would take to populate the dimension table with data by using SSIS.
4. Dimension tables should consist of two columns that are important in loading the data warehouse. The first column is typically a dimension key known as a surrogate key. 
5. This is usually a primary key column that uses an identity constraint to automatically populate the column value. It is also used to provide a relationship to the fact table. The second column is referred to as an application key column. This column is used to hold the value of the record in the original transactional system.
6. You may also wish to add columns to the dimension table that can be used to track changes to the records within the dimension table. As information within the table changes, you can add a ModifiedDate column that can be used to track the date when changes occurred to a record. To keep historical records of changed data, you could include StartDate and EndDate columns that help you to determine the duration that a record was in a particular state.
7. Dimension tables contain columns that hold information that represents a business entity. You can define many dimension tables. 
8. An example of a business entity that is represented as a dimension table could be customers. The columns will contain information that is specific to that entity. In the case of the customers dimension table, columns can include Firstname and Lastname. Dimension tables will also contain a primary key column that uniquely identifies the records in the dimension table. The primary key also defines a one-to-many relationship with an associated column or columns within a fact table.
9. Also note that dimension tables can be shared between different data marts. A common example of this is a Time dimension table. This could be used by different data marts to provide consistent information about time across multiple data marts. These types of dimension tables are known as conformed dimensions. In order to manage the movement of data from source systems to the data warehouse, staging tables may also be created in the data warehouse. This allows tables to act as intermediary tables for cleansing data before loading the cleansed data into the respective dimension tables.

Fact Tables

1. The fact table contains information about historical transactions and typically represents this information as integer data. 
2. For example, you can have three dimension tables named Customers, Region and Time. Each has a primary key column that relates to the fact table. In this example, Customer 6231 in the fact table is equal to Francois Ajenstat in the dimension table. Region 4 is equal to the value of West and the Time value of 3423 is equal to the 18th of August, 1975 in the Time dimension table. The additional columns will contain measures about the transaction such as order quantity and sales amount. Using integer data to represent historical transactions allow SQL Server Analysis Services to process the data much more efficiently
3. The fact table is at the heart of the data mart and typically consists of three groups of columns. The first is a primary key column to maintain the integrity of the fact table itself. The second group of columns are known as foreign keys.
4. Each of these columns relate to a dimension table primary key to provide a relationship between the fact table and the contextual information provided by the dimension table. Imagine that the image represents a Sales data mart, and that the three smaller tables represent dimension tables called CustomerDim, TimeDim and RegionDim. 
5. The fact table, which would be the big table in the image, would contain three foreign key columns that would relate directly to each dimension table.
6. The fact table also contains a third group of columns known as measures. This group of columns hold business metrics such as sales units, sales amounts and cost amounts. As a result, the fact table ideally consists of business information represented by integer values.
7. On occasions, a fourth group of columns may be seen within the fact table. This may be one or more columns that are referred to as degenerative dimensions. A degenerative dimension is information about a business entity that is stored within the fact table itself rather than held within a separate dimension table. Using the example of the CustomerDim, TimeDim and RegionDim, these three dimension tables provide context information about customers, time and regions. A central fact table holds foreign key references to the dimension tables and includes measures such as SalesUnits and OrderQuantity. We can include a SalesOrderNumber for each record of a sale. However, rather than creating a separate dimension table that would require a join to the dimension table just to return one piece of contextual information, it is more efficient to store this piece of information within the fact table itself.
8. Fact tables are created like any table that is created in SQL Server. You also define the primary and foreign key relationships within the database engine. Therefore, you are limited to the 8-KB limit on the amount of information that is stored in a row within a table

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