DWH - SLOWLY CHANGING DIMENSION (SCD)

When dimension changes over a period of time, it is called as Slowly Changing Dimension and it is very popularly known as SCD. It is used for tracking history of dimension attributes.There are mainly six types of SCD which are TYPE 0, TYPE 1, TYPE 2, TYPE 3, TYPE 4 and TYPE 6.

   EmployeeDeatil table mentioned below is used as an illustration, contains columns Emp_Key, EmployeeID, Name and Designation. Emp_key is a Surrogate Key.

TYPE 0 (Retains original value): This is a passive method. Value remains exactly the same as that of when inserted for the first time. This is not often used.

TYPE 1 (Overwriting old value): In this type, old value is replaced by a new value in the dimension table. Old data is lost in this process. 
           Let us assume Employee has been promoted from Test Engineer to Test Lead. So, Designation column has been replaced with new value as shown below. Thus we are not maintaining any history of changes that has taken place.

Old Value

Emp_Key	EmpID	Name	Designation
111	1001	Sunil	Test Engineer

New Value

Emp_Key	EmpID	Name	Designation
111	1001	Sunil	Test Lead

TYPE 2 (Adding  new row): In this type, a complete

DWH - Data Validation Testing Using Minus Query Method

Data Validation in Data Warehouse has always been challenging. One of the most frequently way of data validation is by using MINUS/ EXCEPT operator. MINUS operator is used in ORACLE database while EXCEPT is used in SQL SERVER database. Before proceeding to testing part, first let us understand the functionality of MINUS operator.

      MINUS operator gives the differences between the two table. Suppose we have two tables. Lets consider table A and B. When we will do MINUS operation between table A and table B (ie. A - B), it will return all those records which are present in table A but not in B. Similarly when we do  MINUS operation between table B and table A (ie. B - A), it will return all those records which are present in table B but not in A.

       One point to remember while doing MINUS operation between first table and second table is that every row in first table is searched in second table and if not found , that row is returned as a result set. Similarly, while doing MINUS operation between second table and first table is that every row in second table is searched in first table and if not found , that row is returned as a result set. 

        If all the rows  present in first table is present in second table as well then output or result of first table MINUS second table will be empty result set or in other words nothing will be returned.  Similarly, if all the rows present in second table is also present first table, then output or result of second table MINUS first table will be empty result set or in other words nothing will be returned.
Let us understand the usage of MINUS operator in data validation. Below SourceTable and TargetTable is used for illustration. 

     SourceTable                                      TargetTable

ID	Name		ID	Name
1	ABC		1	ABC
2	DEF		2	DEF
3	GHI		3	GHI
4	JKL		4	JKL
5	LMN		5	LMN
6	OPQ		8	OPQ

DWH: What is Data warehouse ?

What is Data warehouse ?
      A Data warehouse is a repositary which stores integrated information for querying and analysis purpose. It has data collected from multiple heterogeneous source system.
According to Bill Inmon who is also known as" Father of Data warehouse", Data warehouse should be :

• Subject Oriented : It helps in synthesizing data as per the subjects.
• Time Variant : It stores data over a period of time.
• Integrated : It integrates data from multiple source system.
• Non Volatile : Data in Data warehouse is never deleted.

DWH: OLTP Vs OLAP

DWH: OLTP Vs OLAP

OLTP(Online Transactional Processing)              OLAP(Online Analytical Processing)

1.It deals with Transactional / Operational Data       1.It deals with Historical Data

2.Databases are normalised to facilitate frequent      2.Databases are de-normalised to facilitate              insertion,updation and deletion                                 queries and analysis

3.Old data is purged or archived                              3.Old Data or Historical data is stored to do                                                                                        trend  analysis and future predictions.

4.Queries are small and it deals with small               4.Queries are complex and it deals with                 amount of data                                                          huge amount of data
   Eg: Updation of Account Balance                           Eg: Total sales in North India

5.Updates happens frequently                                 5.Updates does not happen frequently.

6.Response time is very fast.                                  6.Response time is slow

7.Number of Joins are more as Table is                   7. Mostly less number of joins as Table is in           Normailesd.                                                              de-normalised.