Data Warehouse: Snowflake Schema

Snowflake Schema
In data warehouse, 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.

It is similar to the star schema. However, in the snowflake schema, dimensions are normalized into multiple related tables, whereas the star schema's dimensions are normalized with each dimension represented by a single table.

Example:Snowflake schema used by example query.

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

Read more: http://en.wikipedia.org/wiki/Snowflake_schema

Data Warehouse: Star Schema

Star Schema
In Data Warehouse, the combination of facts and dimensions is sometimes called a star schema.

The star schema (also called star-join schema, data cube, or multi-dimensional schema) is the simplest style of data warehouse schema. The star schema consists of one or more fact tables referencing any number of dimension tables.

Model (refer the graphic above)

• The fact table holds the metric values recorded for a specific event. Because of the desire to hold atomic level data, there generally are a very large number of records (billions). Special care is taken to minimize the number and size of attributes in order to constrain the overall table size and maintain performance. Fact tables generally come in 3 flavors - transaction (facts about a specific event e.g. Sale), snapshot (facts recorded at a point in time e.g. Account details at month end), and accumulating snapshot tables (e.g. month-to-date sales for a product).
  
• Dimension tables usually have few records compared to fact tables, but may have a very large number of attributes that describe the fact data.
  
  Read more: http://en.wikipedia.org/wiki/Star_schema

Data: Data Warehouse

I've been employed by new sub-govern company since last 2 weeks.
And till now I've been assigning different task from the first week to another weeks...
First, I was asked to backup finance system, then last week I was asked to help collection module for new system .. and today I just knowing will be assigned new task.. to handle data warehouse.
What is data warehouse?

Data Warehouse (DW or DWH) is a database used for reporting and data analysis. It is a central repository of data which is created by integrating data from multiple disparate sources. Data warehouses store current as well as historical data and are commonly used for creating trending reports for senior management reporting such as annual and quarterly comparisons.

The data may pass through an operational data store for additional operations before they are used in the DW for reporting.

ETL(extract, transform and load ) refers to a process in database usage and especially in data warehousing that involves:

• Extracting data from outside sources
• Transforming it to fit operational needs (which can include quality levels)
• Loading it into the end target (database, more specifically, operational data store, data mart or data warehouse)

The staging layer or staging database stores raw data extracted from each of the disparate source data systems. The integration layer integrates the disparate data sets by transforming the data from the staging layer often storing this transformed data in an operational data store (ODS) database.

The integrated data are then moved to another database, often called the data warehouse database. 

Read more: http://en.wikipedia.org/wiki/Data_warehouse 

.