Star and Snow Flake Schema

Star Schema

The star schema consists of one or more fact tables referencing any number of dimension tables. The star schema is an important special case of the snowflake schema, and is more effective for handling simpler queries.
File:Star-schema-example.png

Fact Tables

A fact table typically has two types of columns: foreign keys to dimension tables and measures those that contain numeric facts. A fact table can contain fact's data on detail or aggregated level.
In the above image Date_id, Store_id, Product_id are foreign keys. Units_Sold contains numeric values.

Dimension Tables

The dimension tables store the textual descriptions of the dimensions (e.g., customer name, product description). 
Dimensions are usually based on the master files in the business, for example customer master, employee master.

Snow Flake Schema

In computing, a snowflake schema is a logical arrangement of tables in a multidimensional database such that the entity relationship diagram resembles a snowflake in shape. The snowflake schema is represented by centralized fact tables which are connected to multiple dimensions.



















The following example query is the snowflake schema equivalent of the star schema example code which returns the total number of units sold by brand and by country for 1997. Notice that the snowflake schema query requires many more joins than the star schema version in order to fulfill even a simple query. The benefit of using the snowflake schema in this example is that the storage requirements are lower since the snowflake schema eliminates many duplicate values from the dimensions themselves.
SELECT
        B.Brand,
        G.Country,
        SUM(F.Units_Sold)
FROM Fact_Sales F
INNER JOIN Dim_Date D             ON F.Date_Id = D.Id
INNER JOIN Dim_Store S            ON F.Store_Id = S.Id
INNER JOIN Dim_Geography G        ON S.Geography_Id = G.Id
INNER JOIN Dim_Product P          ON F.Product_Id = P.Id
INNER JOIN Dim_Brand B            ON P.Brand_Id = B.Id
INNER JOIN Dim_Product_Category C ON P.Product_Category_Id = C.Id
WHERE
        D.YEAR = 1997 AND
        C.Product_Category = 'tv'
GROUP BY
        B.Brand,
        G.Country

Comments

Post a Comment

Popular posts from this blog

Data Warehouse

Image
Importance of Data Warehouse Data received from different sources We may receive the data from various sources it could be in flat files, mdb, SQL Server or Oracle etc... End of the day our business requires to integrate the data from different sources  to take business decisions. It is not simple task to create a single report by adding all different source of data. It can be handled by data ware house. Data ware house collects data from different sources and store the data in Data Marts. Data marts are well designed for Reporting tools. Load on Production System Users who are working the production database always expects the faster results with present required details. If we use Production System data base to generate   'ad-hoc'   eports with huge data, it could impact the performance for users who are working on the applications. Example: When we are going to buy a product from online store, we always expect ...
Read more

SQL Server Integration Services (SSIS)

Image
Integration Services Project Items Package - DTSX file Data Source - DS file Data Source View - DSV file Package A package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables, and configurations, that you assemble using either the graphical design tools that SQL Server Integration Services provides, or build programmatically. You then save the completed package to SQL Server, the SSIS Package Store, or the file system. The package is the unit of work that is retrieved, executed, and saved.                                                 Data Source A data source is a connection reference that you create outside a package. A data source represents a simple connection to a data store, which includes all tables and views in the data store. For more advanced features, such as the a...
Read more

CTE/Temp/Table Variable

Image
CTE:(Common Table Expression) A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. How to use CTE Refer the Microsoft link http://msdn.microsoft.com/en-us/library/ms175972.aspx Good Example from Microsoft on "Recursive Queries Using Common Table Expressions" http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx Temp tables  1. The table "exists" - that is, it's materialized as a table, at least in memory, which contains the result set and can be reused. 2. In some cases, performance can be improved or blocking reduced when you have to perfor...
Read more