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 or not. Will the report performance get better with the dimension table or not?

 

Thanks

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

1 Reply

  • 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