Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply

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

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.

1 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! 🤠

View solution in original post

4 REPLIES 4

@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/

@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! 🤠

@Daniel29195 , Thanks for the detailed explaination 👍.

Daniel29195
Super User
Super User

@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! 🤠

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.