Skip to main content

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 Tables

5

Good to use for large database

5

Good to use for small database

6

Less number of joins

6

More number of joins

7

When dimension table has less number of rows we can go for this.

7

When dimension table is relatively big in size it is better as it reduces space.

8

Recommended

8

Not Recommended

 

 

 

 

 

 

 

 

ETL

Cube Process

Star Schema

Slow

Fast

Snow Flake Schema

Fast

Slow

 Double Star Schema:

 

**Remember:

Even though star schema is recommended but in real time scenario most of the companies are using Snow flake schema due to the large amount of data.





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