Forum Discussion

SammyPub's avatar
SammyPub
Frequent Visitor
5 years ago
Solved

Fact vs Dimension

Hi,   I have a fact table with 5M rows. Some columns only have two distinct values "Y" and "N". In my report I will use the columns as slicers. Should I create a dimension table for these columns ...
Need Help
  • HashamNiaz's avatar
    HashamNiaz
    5 years ago

    Hi SammyPub !

     

    You can create a dimesion with simple 2 value and another surrogate key with tinyint 0/1, you can save this tinyint value in your fact table. Current [Y/N] are storing as char and taking more space than tinyint.

     

    Also, when you use [Y/N] from fact table for slicer your query is hitting 5M rows, instead you can use dimension to get slicer selection.

     

    Hope you understand the performance benefit.

     

    Regards,

    Hasham

HashamNiaz's avatar
HashamNiaz
Solution Sage
5 years ago

Hi SammyPub !

 

You can create a dimesion with simple 2 value and another surrogate key with tinyint 0/1, you can save this tinyint value in your fact table. Current [Y/N] are storing as char and taking more space than tinyint.

 

Also, when you use [Y/N] from fact table for slicer your query is hitting 5M rows, instead you can use dimension to get slicer selection.

 

Hope you understand the performance benefit.

 

Regards,

Hasham