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

SQL / ORACLE- Scenario Based Interview Questions & Answers PART- 11

SCENARIO:  

We have EMP_DETAILS table which contains employee details such as EMPID, GENDER,EMAILID and DEPT_ID columns. We want to display all emailid associated with a particular DEPT_ID to be concatenated with semicolon as shown below.

EMP_DETAILS Table

EMPID	GENDER	EMAILID	DEPT_ID
1111	M	YYYYY@gmaix.com	104
2222	M	ZZZ@gmaix.com	103
3333	F	AAAAA@gmaix.com	102
4444	F	PP@gmaix.com	104
5555	M	CCCC@yahu.com	101
6666	M	DDDDD@yahu.com	100
7777	F	E@yahu.com	102
8888	M	M@yahu.com	102
9999	F	SS@yahu.com	100

     

EXPECTED RESULT:

ORACLE - How to copy column names or header from query result set in ORACLE SQL DEVELOPER ?

When we are working on ORACLE SQL DEVELOPER, we come across many situations where we need to copy only column names of a table or column names along with all data from query result set to excel or notepad. This can be done by the following ways:

COPY ONLY COLUMN NAMES:

When you want to copy only column names from result query, select cells corresponding to the columns which you want to copy. Right click on the column name and select option "Copy Selected Column Header(s)". This way column names get copied in clipboard which you can paste wherever you want. See below screenshot .

COPY COLUMN NAMES ALONG WITH DATA: