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

[Anonymous]

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? 

[Phil_Seamark · Accepted solution]

Hi @Anonymous

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.