Data Warehousing & DataStage

Data Warehousing & DataStage 

Data warehouse:

Data warehouse is a,

· Subject- Oriented.

· Integrated.

· Time variant.

· Non-volatile.

Data warehouse is a Subject-Oriented and Integrated Data base, designed to supplythe Information for decision making.

(Or)

Data warehouse is a repository used to manage business.

(Or)

Data warehouse is a relational database which stores complete information aboutthe transaction.

(Or)

Data warehouse is a powerful database model which can answer any type of thebusiness query (requirement).

Data warehousing:

It is a process of implementing both ETL and Reporting functions.

Data warehousing Architecture:

The following figure shows the Data ware housing Architecture.

Down load this document, here

click here to download.

Staging Area:

This is a temporary storage place which contains complete information of

the source system, there no transactions involved, while extracting the data from

source system and loading into staging tables.

Source Systems:

Generally these are a transactional database system which contains the list

of Transactions.

Ex: Oracle, DB2, Teradata, Sybase, Informatica, COBOL files, Xml files, Flat

files.

ODS (Operation Data Store):

It is a,

· Subject- Oriented.

· Integrated.

· Time variant and volatile data base system.

Tip: ODS is designed, to improve the performance of Updates.

Data Mart:

Sum amount of related information which will be capture from enterprise data

warehouse and loading into small data warehouses is called data marts.

Tip: Data Marts designed, to improve the performance of the select statement.

EDW: Enterprise Data Warehouse.

ETL :  

ETL Means E for Extraction , T for Transformation and finally L for Loading.

ETL: Extraction, Transformation and Loading.

This is the main area; the ETL developer has to concentrate and below is the

sample transformation activities.

1. Dropping unwanted columns.

2.  Adding required columns.

3. Applying filter conditions on source data.

4. Calculate aggregate functions (min, max, avg, sum...Etc).

5. Apply sorting operations.

6. Remove/ capturing the duplicate records.

7. Changing the date formats.

8. Changing the Meta data.

9. Applying join operations.

10. Applying functions (String, Number).

11. Null- rejects handling.

12. Combine the data from multiple data sources.

13. Distributing the data to multiple targets.

14. Generating sequence number.

ETL Tools:

1. Data Stage.

2. Abinitio.

3. Informatica.

4. SAP BI.

5. MS BI.

6. SSIS.

7. Pentaho.

8. SAS BASE.

9. SAS ACCESS.

10. Oracle Ware housing building.

11. Teradata Ware housing building.

12. Business objects Data Integration.

13. ETL4ALL.

14. Clever ETL.

15. MS Excel.

Programmatic GUI Tools:

· PLSQL.

· OWB.

· MS EXCEL.

· TWB.

· SAS BASE.

· SAS ACCESS.

Reporting Tools:

· Cognos.

· Business objects.

· Micro Strategy.

· Hyperian.

· Pentaho (It is an ETL & Reporting toll too.)

· OBIEE.

Differences between OLTP and OLAP:

OLTP (Online Transaction Process) :

1. It contains the current values.

2. These systems are designed to perform transactions.

3. These systems are designed to perform Insert, Updates actively &

Effectively.

4. End users can have direct access to modify the transcriptional data.

5. More joins increase the performance.

6. No duplicates, no redundancy.

7. Normalized data.

OLAP(Online Analytical Process):

1. It contains the current history.

2. These systems are designed to perform analysis.

3. These systems are designed to perform Select statements actively &

Effectively.

4. End users can’t access to modify the transcriptional data.

5. Less joins increase the performance.

6. More duplicates, more redundancy (history).

7. De normalized data.

Meta data:

Meta data is a layout which represents the actual data.

Fact table:

Fact table contains set of FK’s with respect to all the dimension tables

associated to it and fact Information. Facts are also called as “measures (or)

numerical information”.

Types of Dimensions:

1. Conformed Dimension.

2. Degenerate Dimension.

3. Junk Dimension.

4. Large/ Monster Dimension.

5. Small/ Mini Dimension.

6. Slowly Changing Dimension.

7. Rapidly changing Dimension.

8. Static Dimension.

9. Custom Dimension.

10.Dynamic Dimension.

11.Aggregate Dimension.

12. Multi valued Dimension.

Relational Database:

Relational data base provides relations between Tables.

Degenerate Dimension:

It is an attribute in the fact table and mainly generated to satisfy the

customer requirements.

Example:

Schema:

Set of tables having relations with each other.

Data stage developer has to load the data into data warehousing.

Data base average size is 1500 GB.

Junk Dimension:

The dimension which contains textual codes and flag variables is called junk

dimension.

Junk dimensions are used to reduce the memory space occupied by the data in data

warehouse and while generating the reports.

Junk dimensions are never maintaining any relationship with fact table.

Large/ monster Dimension:

If the dimension contains more number of columns (>100) or more number

of rows (>2milloins) is called Large Dimension.

Small/Mini Dimension:

If the dimension contains less number of columns (around 5) and less

number of rows (<1000) is called Small Dimension.

Slowly Changing Dimension (SCD): 

The dimension which changes slowly with respect to number of rows or

number of columns is called SCD.

There 3 types in SCD. They are:

SCD Type1:

It maintains the latest data only.

See figure SCD Type1 for further details.

SCD Type2:

It maintains complete history about all the transactions.

See figure SCD Type2 for further details.

SCD Type3:

It maintains Current, Immediate previous transaction values.

See figure SCD Type3 for further details.

Rapidly Changing Dimension (RCD):

The dimension which changes rapidly with respect to number of rows or

number of columns is called RCD.

Ten (10) to Fifteen (15) changes in a year – SCD.

More changes in a year – RCD.

Surrogate Key (SK): It is a normal primary key.

Custom Dimension:

The dimension in which the attributes are varied based on the incoming record is

called Custom Dimension.

These types of dimensions are never recommended in Data ware housing schema.

Static Dimension:

If the dimension is loaded with data and will never change, then it is called

as Static Dimension.

Dynamic dimension:

By default every dimension is dynamic dimension expect static.

Aggregate Dimension:

The dimension which is loaded at base level and is possible to calculate all

aggregate functions (min, max, sum, count) is called as Aggregate Dimension.

Multi Valued Dimension:

If the dimension contains many to many relationship with in the data

(attribute (or) values) then it is called Multi Valued Dimension.

It is not recommended in DWH.

Fact Table:

It is a large control table in DWH schemas. Contains set of foreign keys

with respect to all the dimension tables associated to it fact information and

degenerate dimensions.

Snap Shots:

It contains the information at exact point of time.

Ex: Sales Fact Table.

Cumulative Fact Tables:

It contains the information about it period.

Ex: Sales Fact Table.

Fact less Fact Table:

The fact table which does not contain fact information is called fact less fact

table.

Examples:

1. To track the attendance of a student.

2. To check the flight availability in airport or not.

3. To check the product availability in store.

The Following figure shows about the fact less fact table.

Types of Facts:

1. Additive Facts (AF).

2. Semi Additive Facts (SAF).

3. Non Additive Facts (NAF).

Additive Facts:

The facts which are calculated from any of the dimension table

independently called Additive Facts (AF).

Ex: Sales Account, Sales quantity and Revenue.

Semi Additive Facts:

The facts which are calculated from one or few dimension tables (but not all)

are called as Semi Additive Facts.

Ex: Account balance, quantity in hand.

Non Additive Facts:

The facts which are calculated by applying arithmetic operation on the

combination of additive, semi additive, non additive facts and de generate

dimensions (DD) are called as Non Additive Facts.

Ex: Avg of goods per order.

Avg no of goods per order = Sales quantity (AF) / Order no (DD)

Snow Flake Schema:

The schema not all the dimension tables are directly connect with fact table

is called as Snow Flake Schema. In Snow Flake Schema design both the dimension & fact tables are

normalized. When we keep on hitting the same dimension table many times with query

de normalized table is not suggestible. So that De generate dimension (DD) can be split into two or more normalized dimension tables.

FAQ’s in DWH

1. Difference between OLTP & OLAP?

Ans: OLTP is a Transcriptional Systems (data), operational data.

OLAP is a Data ware house, decision support system (DSS).

2. What is ODS and what are its functionalists?

3. What is Star schema and how it is differ from Snow Flake Schema design,

which is better?

4. What is dimension table, types of dimension tables (CD, DD, Junk, SCD*)?

5. Explain the functionalists of fact less fact table with example?

6. What is fact table, what are its types?

7. What are the types of facts?

8. What is data mart, what are its benefits?

9. What are the general transformation activities implemented by ETL

developer?