Data Warehouse Designer Views

1.Top-Down View

• Allows the selection of relevant information necessary
• This information matches the current and coming business needs

2.Data Source View

• Exposes the information being captured, stored and managed by operational systems
• This view is often modeled by traditional data modellingtechniques such as ER Model or CASE tools

3.Data Warehouse View

• Includes fact tables and dimension tables
• Represents precalculated totals and counts
• Provides historical context

4.Business Query View

• It’s the perspective of a data in the warehouse from the viewpoint of the end user

Features of a Warehouse:

•It is separate from Operational Database.
•Integrates data from heterogeneous systems.
•Stores HUGE amount of data, more historical than current data.
•Does not require data to be highly accurate.
•Queries are generally complex.
•Goal is to execute statistical queries and provide results whichcan influence decision making in favor of the Enterprise.
•These systems are thus called Online Analytical Processing Systems (OLAP).

Types of Schemas

Star Schema

In the star schema design, a single object (the fact table) sits in the middle and is radially connected to other surrounding objects (dimension lookup tables) like a star. Each dimension is represented as a single table. The primary key in each dimension table is related to a forieng key in the fact table.

All measures in the fact table are related to all the dimensions that fact table is related to. In other words, they all have the same level of granularity.

A star schema can be simple or complex. A simple star consists of one fact table; a complex star can have more than one fact table.

Let's look at an example: Assume our data warehouse keeps store sales data, and the different dimensions are time, store, product, and customer. In this case, the figure on the left repesents our star schema. The lines between two tables indicate that there is a primary key / foreign key relationship between the two tables. Note that different dimensions are not related to one another.

Snowflake Schema

The snowflake schema is an extension of the star schema, where each point of the star explodes into more points. In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy.

For example, the Time Dimension that consists of 2 different hierarchies:

1. Year → Month → Day
2. Week → Day

We will have 4 lookup tables in a snowflake schema: A lookup table for year, a lookup table for month, a lookup table for week, and a lookup table for day. Year is connected to Month, which is then connected to Day. Week is only connected to Day. A sample snowflake schema illustrating the above relationships in the Time Dimension is shown to the right.

The main advantage of the snowflake schema is the improvement in query performance due to minimized disk storage requirements and joining smaller lookup tables. The main disadvantage of the snowflake schema is the additional maintenance efforts needed due to the increase number of lookup tables. 

Galaxy Schema:

Galaxy schema contains many fact tables with some common dimensions (conformed dimensions). This schema is a combination of many data marts.

Fact Constellation Schema:

The dimensions in this schema are segregated into independent dimensions based on the levels of hierarchy. For example, if geography has five levels of hierarchy like teritary, region, country, state and city; constellation schema would have five dimensions instead of one.

Types of Fact Tables in Data Warehouse

Types of Facts

There are three types of facts:

• Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.
• Semi-Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
• Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.

Let us use examples to illustrate each of the three types of facts. The first example assumes that we are a retailer, and we have a fact table with the following columns:

Date

Store

Product

Sales_Amount

The purpose of this table is to record the sales amount for each product in each store on a daily basis. Sales_Amount is the fact. In this case, Sales_Amount is an additive fact, because you can sum up this fact along any of the three dimensions present in the fact table -- date, store, and product. For example, the sum of Sales_Amount for all 7 days in a week represent the total sales amount for that week.

Say we are a bank with the following fact table:

Date

Account

Current_Balance

Profit_Margin

The purpose of this table is to record the current balance for each account at the end of each day, as well as the profit margin for each account for each day. Current_Balance and Profit_Margin are the facts. Current_Balance is a semi-additive fact, as it makes sense to add them up for all accounts (what's the total current balance for all accounts in the bank?), but it does not make sense to add them up through time (adding up all current balances for a given account for each day of the month does not give us any useful information). Profit_Margin is a non-additive fact, for it does not make sense to add them up for the account level or the day level.

Types of Fact Tables

Based on the above classifications, there are two types of fact tables:

• Cumulative: This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative fact table.

Snapshot: This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table.

what is the difference between database vs datawarehouse

Data Base :
Database is optimized for transactional activity for keeping a small amount of data.
A Computer Database is a structured collection of records or data that is stored in a computer system. The structure is achieved by organizing the data according to a database model. The model in most common use today is the relational model. Other models such as the hierarchical model and the network model use a more explicit representation of relationships (see below for explanation of the various database models).

A computer database relies upon software to organize the storage of data. This software is known as a database management system (DBMS).





Data Warehouse :

A data warehouse is a repository of an organization's electronically stored data. Data warehouses are designed to facilitate reporting and analysis.
However, the means to retrieve and analyze data, to extract, transform and load data, and to manage the data dictionary are also considered essential components of a data warehousing system. Many references to data warehousing use this broader context. Thus, an expanded definition for data warehousing includes business intelligence tools, tools to extract, transform, and load data into the repository, and tools to manage and retrieve meta data.


 

Operational Data Store(ODS)

ODS is also a small DWH which will help analyst to analysis the business. It will have data for less number of days. Generally it will be around 1 month to 6 months. Like DWH here also surrogate keys will be generated, error and reject handling will be done.

           Operational Data Store is used by many organizations for analysis purpose as well as for data backup and data recovery. Data Stored in ODS is usally in Normalized form as in transactonal DB's.

           While in DWH data will be denormalized ODS is actually a replica of Tacnsactional database, collection two or more business functions data.

DWH Architecture

Most of the data warehousing done, posses three tier architecture.

The base level from which data is extracted is operational system(oltp) and the lagacy systems, from which data is transformed and loaded into the warehouse darabase. So the middle level is the data warehouse and the top most level is the analytic system(olap). OLAP systems utilize the data warehouse to proivide multi dimensional view. Functionally a data warehouse can be divided into following:

• Data Extraction
• Transformation and Scrubbing
• Data Loading
• Data Analysis
• Data Delivery

What is Data Warehouse?

A Data warehouse is a database used for Reporting. A data warehouse is a Subject Oriented, Integrated,  Non-volatile and Time-variant collection of data in support of management's decision making process.

Subject Oriented :- A data ware housing can be used to analyze subject area. This leads to the gathering of information around these subjects rather than around the applications or process.
For Example :Sales, Finance, HR... These are the different subject areas in organization.

Integrated :-  A data warehouse integrates data is scattered around different tables, databases or even servers but the data is integrated consistently in the values of variables, naming conventions and physical data definations.

Non-volatile :- The data in the data warehouses should not be changed or updated - once its loaded from operational system.

Time-variant :- The value of operational data changes on the basis of time. The data warehouse kept the data historical. and we can retrive the data months wise,year wise.