Data Warehousing Basics

Data                                          

Any Meaningful information is called as data.

In IT industry this information is passed or transferred from one system to the other depending upon the flow and nature data is divided into two types in terms of Datawarehousing prospective. They are  

• Transactional Data
• Analytical Data

Transactional Data

Any business keeps running because of transactions. There are various kinds of transactions  involved depending upon the nature of the business which leads to generation of Transactional data.

• ·         Transactional data is runtime data and is generated every day.
• ·         Transactional data is current and detail.
• ·         Transactional data is useful to run the business.
• ·         It is stored in OLTP.

Eg: Ecommerce Transactions, Banking transactions ..etc.

Analytical data

Analytical data is data that is generated because of some group of transactions and this analytical data is useful for Strategic business decision making purposes.

• It is Historical data useful to Analyze the business.
• It is stored in OLAP or Data Warehouse 

\

    Data Warehouse :

Data warehouse (EDW), is a system used for reporting and data analysis. DWs are central repositories of integrated data from one or more disparate sources. Or  Collection of data from different OLTP Systems.

In Data warehouse entire data is organized in the form of fact and Dimension tables and stored in their respective data mart/Subject area.

Datamart: A datamart is a subset of data warehouse.

All dataware houses were built on Dimensional Modeling. In Dimensional modeling depending upon the type of data that is present tables are divided into Dimensions and facts.

If a table contains at least one measure column then it is called as fact table or else it is called as a dimension table.

Fact:

•       Contains business measures or metrics.

•       Data is often Numerical.

•       It is central table in the star.

Dimension

•       Contains attribute or characteristics of the business.

•       Data is often descriptive.

Star-Schema

In star schema data is organised into a central fact table with surrounding dimension tables.

• Even dimension row has many associated fact rows.

• Dimension tables do not directly relate to each other.

• All dimension tables are de normalized.

• All dimension tables are de normalized.

• Optimized to read data

• User friendly, easy to understand.

• In OBIEE BMM layer only star schemas are used.

** Star schemas are easy to understand and user friendly.

Snow Flake Schema

•      Here two dimensional tables will be joined to each other.  

•      It is even called as extended star schema. 

•      Even It has only one Fact Table like star schema.

     Mixed Or Constellation Schema:

•       It contains more than one fact tables which share some common dimensions.

•       Common Dimensions that were shared by two Facts in Mixed schemas are called as Confirmed Dimensions.(These Confirmed dimensions were widely used in data models of OBIA )

DW TOOLS

        Depending upon their Functionality these data warehousing tools are divided into two types they          are ETL tools and reporting tools.

ETL	REPORTING
Informatica	OBIEE
ODI	SAP-BO
Datastage	Cognos
Abinitio	Qlickview
SSIS	Tableau
Pentaho	Microstrategy