cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rynelees Regular Visitor
Regular Visitor

Performance Question: What's better, more tables or more columns?

I have a .pbix that looks at our transactional data. It comes in 3 seperate tables all related by transaction number

  • Transaction Header ~1 million rows, 6 columns 
    • Transaction Number (text)
    • Date (date)
    • Time (time)
    • Customer (text)
    • Employee (text)
    • Location (text)
  • Transaction Lines ~ 3 million rows, 4 columns
    • Transaction Number (text)
    •  Product Number (text)
    • Quantity (float)
    • Sales Total (float)
  • Payment Lines (How they paid for transaction) ~ 6k rows, 3 columns
    • Transaction Number (text)
    • Transaction Status (text)
    • Amount Tendered (float)

 

What would be the most efficient structure performance wise?

  1. Leaving table structure as is and creating relationships in BI
  2. Joining Transaction Header & Sales Lines which would result in 2 tables instead of 3. One table with ~3 million rows with 10 columns (would have to add sales line number to create a relationship with Payment Lines) and one table with ~6k rows with 3 columns.
  3. Join all 3 tables which would result in one table with ~3 million rows and 13 columns.

 

I tried to look up some documentation or previous thread, but couldn't find anything. Could I just gauge it by creating all 3 and looking at .pbix size? Or any other tips? 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Phil_Seamark Super Contributor
Super Contributor

Re: Performance Question: What's better, more tables or more columns?

Hi @rynelees

 

 

Some tips.

 

Try and flatten your tables as much as possible.  If you can do in 3 tables, what your source system has in 6 then you are on the right track.  Extra columns aren't an issue but get rid of any column once you know you don't need it.

 

Calculations that need to cross relationships, especially on large tables, will have a noticeable hit on performance.

 

Avoid bi-directional relationships if possible. 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

1 REPLY 1
Highlighted
Phil_Seamark Super Contributor
Super Contributor

Re: Performance Question: What's better, more tables or more columns?

Hi @rynelees

 

 

Some tips.

 

Try and flatten your tables as much as possible.  If you can do in 3 tables, what your source system has in 6 then you are on the right track.  Extra columns aren't an issue but get rid of any column once you know you don't need it.

 

Calculations that need to cross relationships, especially on large tables, will have a noticeable hit on performance.

 

Avoid bi-directional relationships if possible. 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 126 members 1,790 guests
Please welcome our newest community members: