Fact vs Dimension

[SammyPub]

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

[HashamNiaz · Accepted solution]

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