Having column with low cardianility in the same fact table or as dimension table

[SanthiyaMallow]

I have a table called  Users where the column "created_by" contains two values app, form. Do i need to create dimension table for this two column or can keep the column in the same table?

Please provide suggestion on this design.

[SanthiyaMallow]

@Daniel29195 , Thanks for the suggesion. whats the advantage fo having it as a separate table, with only one column even with very low cardianility? 

Don't having the column in the same table perform better than having it as a separate dimension table and joining with fact underhood.

Eventhough this will have storage optimization, i would like to know in terms of performance/

[Daniel29195 · Accepted solution]

@SanthiyaMallow 

1. Improved Performance: By creating a separate dimension for the "createdby" column, you can reduce the size of the fact table. This can improve query performance, as Power BI needs to scan fewer rows when filtering or aggregating data based on the "createdby" values.

2. Enhanced Data Model Structure: Introducing a dimension for the "createdby" column helps in organizing the data model more effectively. It separates the descriptive information (e.g., "form" or "app") from the fact table, leading to a clearer and more understandable data model.

3. Scalability: As your dataset grows over time, maintaining a separate dimension for the "createdby" column allows for easier scalability. You can efficiently manage changes to the "createdby" values or add additional attributes to the dimension without impacting the fact table's structure.

4. Consistency and Reusability: A dimension table for "createdby" facilitates maintaining consistent values across the dataset. It enables reusing the same dimension table in multiple fact tables if the "createdby" values are shared among different datasets or reports.
   
   all these, +  working with complex DAX code.  ( performance would be better), 
   
   
   hope this answer your question .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[SanthiyaMallow]

@Daniel29195 , Thanks for the detailed explaination 👍.

[Daniel29195]

@SanthiyaMallow 

create a dimcreation table with these 2 values and with ids. 

and add the id column into the fact table. and link both tables on the id . 

hope this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠