Datawarehousing revealed..

As we have studied in the previous post that Analytics makes use of common indutry techniques like Datawarehousing and Dimension Modelling, so lets have a brief overview of what is Data Warehouse and what are the Data Warehousing concepts....

What is a Data Warehouse???

A Data Warehouse is a Relational DB that is designed for query and analysis rather than using it for Transactional purposes(eg. OLTP DB). It contains historical data derived from the transactional data and can also include data fromn other sources. In addition to relational DB it also includes an Extraction, Transformation & Loading(ETL) solutions, Online Analytical Processing(OLAP) & Data Mining capabilities.



A data warehouse can be characterized by the following:

- Subject Oriented
 DW designed to help analyze data. Eg: Comapny's sales data. So we can design a DW which concentrates on Sales data for the company.

- Integrated
 DW must have data from various sources in a consistent format so as to help company's sales measures.

- NonVolatile
 This means that once entered into the DW the data should'nt change cz the basic purpose of the DW is to enable you to analyze that what has occured.

- Time Variant
 To analyze the trends in business, analysts require large amounts of data. The DW focuses on change of data over time.


What is a Transactional DB??

A transactional DB is one where the data keeps on changing very frequently depending on the transactions made on the DB and hence it is called transactional DB. An eg. of transactional DB is OLTP (Online Transactional Processing).


Now lets c some Differences between the OLTP and DW and i guess that can better clarify what exactly both of them are:

Workload
DW are designed to accomodate ad hoc queries. The workload in not known in advance in case of a DW and it should be tuned enough so as to fetch any amount of data and to runany comple queries.
OLTP systems support predefined operations and application mite be tuned to only support those operations.

Data Modification
In DW systems Data Modification takes place using ETL processes and the data is modified in bulk. The end users do not directly update the DW.
In OLTP users freq issue individual Data Modification statements to DB and hence OLTP is always up to date with the most recent data.

Schema Designs
DW makes use of denormalized or partially denormalized schemas to optimize query perfomance.
OLTP systems use fully normalized schemas to optimize update/insert/delete performance and have a data consistency.

Typical Operations
A typical DW query scans millions of rows at a time.
OLTP operations accesses only a handful of records

Historical Data
DWs usually stores history data of many months and years.
OLTP systems usually store data for only a few weeks or months.

Other
DW have more indexes as compared to OLTP
DW has less joins as compared to OLTP
Derived Data & Aggregates are common in DW and are rare in OLTP.

Now lets move on the Data Warehouse schemas. You must be thinking what significance all these things have with Analytics an OBIEE but you will get toknow this thing as we move fwd... So lets move ahead....

Some of the most commonly known DW schemas are :

Star Schema
Snowflake Schema
And the comes the Hybrid Schemas (not frequently used)


The Data Warehouse Objects commonly used in DW schemas are :

Fact Tables
Dimension Tables


These things will be covered in detail in upcoming posts....