Skip to main content

Types of dimensions:

Types of dimensions:

·         Role playing Dimension

·         Conformed Dimension

·         Junk Dimension

·         Degenerate Dimension

·         Slowly Changing Dimension

·         Rapidly changing Dimension

·         Inferred Dimension

1. Role playing Dimension:

When dimension table is having more than FK relationship with Fact table is called Role playing dimension.

Ex: SaleDateKey and DeliveryDateKey









    

2. Conformed Dimension:


·         A conformed dimension relates to multiple fact tables within the same DWH.

·         DimDate is a common conformed dimension because its attributes (day, week, month, quarter, year, etc) have the same meaning when joined to any fact table.

·         Ex: Calendar (Here meaning is changing so it’s not conformed dimension means Financial year starts with Apr so its first month of FY and Normal year starts with Jan so it’s the first month of normal calendar. So here meaning is changing so it’s not Conformed Dimension)

 

Normal Calendar

Financial Calendar

Jan 1     Q1

Feb 2

Mar 3

….

Dec 12

Apr 1          Q1

May 2

June 3

…..

Mar 12 

        





 

      



  3. Junk Dimension: *****Vimp

 

·         Junk dimensions are used to reduce the number of dimensions in the dimensional model.

·         And reduce the number of columns in the fact table.

·         It will help us to reduce size of the fact table.

 



 

 

    4. Degenerate Dimension:

 

·         The term “degenerate dimension” was originated by Ralph Kimball.

·         Degenerate dimension describes a dimension field in the fact table that doesn’t have a corresponding dimension table.

·         Placing these text attribute will slowdown the performance of Fact tables.

·         Ex: Voucher number, Bill number etc

Note: Check above example for Degenerate dimension.

 

5. Slowly Changing Dimension (SCD):

 

·         In Data Warehouse there is a need to track changes in dimension attributes in order to report historical data.

·         Slowly changing dimensions are used when we wish to capture the changing data within the dimension over time.

-      





Note:

Whenever we implement slowly changing dimension (SCD) tables. PK will not be implemented we have to create a new key which is called surrogate key as we need to keep historical data.

-      We do not have the Surrogate key in OLTP, we only have the surrogate key in OLAP in few tables only.

 

6. Rapidly Changing Dimensions:

 

·         This cannot be implemented at all in DWH.

 

Ex. Share market

 

·         We cannot keep such type of data in DWH which is changing in every minute

 

7. Inferred Dimensions:

 

·         Inferred Dimensions are called Late Arriving Dimensions

·         Early arriving Facts, Late arriving dimensions

 

ð  For some reason if the data in Dimension table did not come first and fact data comes first it is called inferred dimensions. But while inserting in fact table you will get error when checking the PK/FK reference.

ð  So removing a FK from fact table is the first solution in such cases. And second solution is you keep dummy value in dimension table and insert the values in fact tables.

 

How do you load the data in DWH?

Ans: First Load Data in Dim tables (Step1) and later Load data in Fact tables (Step2)



Labels:

Popular posts from this blog

How to remove special characters in the Data(using Script component Task and Regex)

Start with the Below screen shot ( Before Script Task) and you will come to know about the senario. Note : The Script Component also works for the special characters within the Data as well . for ex "A$hish" Hover over the below Regex. it will show > Show potential fixes > in that Select System.Text.RegularExpressions. The Below will disappear.  Use the Below Script.  public override void Input0_ProcessInputRow(Input0Buffer Row) { Row.CustomerID = RemoveSpecialCharacters(Row.CustomerID); Row.NameStyle = RemoveSpecialCharacters(Row.NameStyle); Row.Title = RemoveSpecialCharacters(Row.Title); Row.FirstName = RemoveSpecialCharacters(Row.FirstName); Row.LastName = RemoveSpecialCharacters(Row.LastName); Row.CompanyName = RemoveSpecialCharacters(Row.CompanyName); Row.SalesPerson = RemoveSpecialCharacters(Row.SalesPerson); Row.EmailAddress = RemoveSpecialCharacters(Row.EmailAddress); Row.Phone = RemoveSpecialCharacters(Row.Phone); } public static string ...
Read more

DBMS

Database : database can be defined as a collection of information stored at a particular place written in a predetermined manner, belongs to a particular topic.  DBMS: (DATA BASE MANAGEMENT SYSTEM):  it is defined as software which is present inside the database which is used o maintain or manage the data within the database .                                 In the above diagram, if the user needs any info from the database. He has to contact with  dbms , then the dbms goes to the database, takes the information from the  database and gives it back to the user. Database models :  these models are broadly divided into the following types: 1.         FMS/FDMS 2.         HMS/HDMS 3.         NDBMS 4.         RDBMS 5.      ...
Read more

Different Schemas in DWH

  SCHEMAS:   Star Schema: -       Star Schema means Dimension Tables are directly linked to the Fact Table. Snow Flake Schema: -       Dimension Tables are indirectly linked to the Fact Table OR -       Dimension table is linking to another dimension table .       Star Schema   Snow Flake Schema 1 Has redundant data and hence tough to maintain/change 1 No redundancy and hence more easy to maintain/change 2 Less complex queries and hence easy to understand 2 More complex queries and hence less easy to understand 3 Less foreign keys so faster execution time of cube. 3 More foreign keys so take more execution of cube 4 Has De-Normalized Tables 4 Has Normalized Tabl...
Read more