Dimension Table VS Fact Table in Kimball DWH

Dimension tables contain descriptive information about a subject or matter. 

Dimension tables contain hierarchies of attributes that aid in summarization. 

For example, 

A "Student" dimension table contains descriptive information about Student -> (Name, Age, Gender, Address, Phone_No etc.)

A class dimension table contains descriptive information about class -> (Class_Name)

and a Subject dimension table contains detail information about subject -> (Subject_Name).

Note: Dimension tables contain attributes that describe fact records in the fact table.

Fact table contain summarized data to provide useful information to the analyst. 

The fact data is a relation/ Referential integration between various Dimention(Subject) Table data.

Here we can see it is a collection of only ids of other dimension Tables and forms useful information to the analyst.

So, from this table we understood following details given below:

1.  Pritam Das is studying in class "Spoken English" and "Hindi" with subject "ABC Tutor" and "Hindi Tutor".
2. Rina Das is studying in class "Hindi" with subject "Hindi Tutor"
3. Ramaprasad Das is studying in class "Spoken English" and "Bengali" with subject "ABC Tutor" and "Bengali Tutor".

Data Warehouse VS Data Mart

A Data ware house is a logical collection of information gathered from many different operational subject area used to create business intelligence that supports business analysis and top level decision making purpose.

It can holds multiple subject areas with very detailed (Historical) information.

We created Data ware house to integrate all homogeneous/ heterogeneous data sources.

Example: A "HRCORE" Data ware house is collection of 4 subject areas

1. Employee Assignment

2. Employee request

3. Employee Time sheet

4. Employee Expenses

A Data mart is a logical collection of only one subject area information.It is 

a subset of the data warehouse which is usually oriented to a specific business line or team.

Example: In "HRCORE" Data ware house, each subject area is one data mart.

Employee Assignment, Employee request, Employee Time sheet and Employee Expenses.

MORAL: Data warehouse can contain many subject areas, and a data mart can contain just one of those subject areas. A Data Mart is subset of a Data ware house.

Data warehouse design concepts Inmon vs. Kimball

There are two most commonly approaches in DWH architecture introduced by Bill Inmon and Ralph Kimball.

Bill Inmon -> Top-Down Approach

According to Bill Inmon, first make a normalized data model (ware house) then split into data marts.

Dimensional data marts are created only after the complete data warehouse has been created. 

To built data ware house it will take more time compare to Ralph Kim-ball approach. Client requirement should be fixed before start of development.

Ralph Kimball -> Bottom-Up Approach

Ralph Kimball says "The data warehouse is nothing more than the union of all the data marts"

It means, first create data marts and then make a schema relation.

Dimensional data marts (Di mention and Fact table) provide a narrow view into the organizational data, then they related by start or snow flex schema based on requirement.

To built data ware house it will take less time compare to Bill Inmon approach. Client requirement need not be fixed before start of development.

What is operational database ?

It is collection of current information or data that is required to run the business.
It is also referred to as OLTP On Line Transaction Processing databases.
Operational databases are used to store, manage and track real-time business information, not historical data. Operational databases are just part of the entire enterprise data management 
For example, an operational database might contain fact and dimension data describing transactions, data on customer complaints, employee information, etc. 

understanding on Surrogate key

To understand Surrogate key concept we need to have clear picture on OLTP database and OLAP data Database.

In Data Warehouse (OLAP), there are 2 types of table (Dimension and Fact). By default we set a primary key on each dimension Table. This primary key column is the same primary key column (Business Key) form OLTP database table.

This is not recommended to be used as primary key in dimension table because of following reason mentioned below.

1. In OLTP database the the datatype of primary key column will be Unique-identifier or Alphanumeric character.
which consumes lot of indexes space when used as primary key. Since index size big, it makes data pooling slower.

2. In MNC business (Multiple Data source system) when we pool the data from various different source, there will be chance to lose control over record identifiers.(Same Primary value cumming from 2 different sources for 2 different objects)

3. SCD Implementation is not possible.(Historical data)


A surrogate key is a primary key, also having name meaningless key. We are using this key because, it is small and so efficient to store. To join dimension table and fact table using only surrogate key is recomanded, not business key. 

Why surrogate key required ?

1. Surrogate keys are generally small integer numbers, which makes index size smaller      when used as index column. This gives better performance due small index size.

2. We can tell something about the record just by looking at this key.

3. Historical versions of same data can be evaluate.

Implementation :

Semi-join in SQL Server

This is very interesting and good to know that Correlated sub queries is called as Semi-join in SQL Server.

Semi-Join returns rows from a table that would join with another table without performing a complete join.

Ex:

SELECT a.FirstName, a.LastName

FROM Person.Person AS a

WHERE EXISTS

(SELECT *

    FROM HumanResources.Employee AS b

    WHERE a.BusinessEntityID = b.BusinessEntityID

    AND a.LastName = 'Pritam');

GO

Stuff number inside square brackets (Ex: [123]) from a string in SQL Server

When you Search square brackets from a string... you can't get it if you write query
like '%[ ]%'

Here my goal is to remove the number with square brackets but not the character  inside square brackets.

To achieve this goal you have to follow the code given below.

DECLARE @TEXT VARCHAR(MAX),@INITIAL INT,@FINAL INT,@PATSTART 

INT,@PATEND INT;

SET @TEXT='ASFASFA [123] ASF [ABC] AFAFS [567] 123 AFASFA [123] AFASF PRITAM[12]DAS'

SET @INITIAL=1;

SET @INITIAL=PATINDEX('%[[][0-9]%',@TEXT)

WHILE (@INITIAL>0)

BEGIN

       SET @PATSTART=@INITIAL-1;

       SET @PATEND=CHARINDEX(']',@TEXT,@INITIAL);

       SET @TEXT=STUFF(@TEXT,@PATSTART,@PATEND+1-@PATSTART,'');

       SET @INITIAL=PATINDEX('%[[][0-9]%',@TEXT);

END

SELECT @TEXT;