cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chefe
Helper II
Helper II

How not to relate two data tables

Hi there,

sorry for silly title. It ought to read: how to not relate two data tables given the circumstances in my specific case. But I guess that would have been too long.

Say I have two data tables.

#1: the primary one. In it reside transaction data (purchases and sales of currencies). The [transactionKey] is unique. A rather wide (like in more columns) and short table.

#2: 'logically' the secondary data table; a long but narrow table. But in size it is way larger than #1, because it contains per [transactionKey] and per [weekday]:

- [value] of each transaction
- variations of [value] under scenario assumptions

So let's say there are 10000 records in #1, then there are at a minimum 2x10000 entries in #2, per [weekday] that is. As the year progresses and even assuming #1 remains constant, #2 will quickly be magnitudes bigger.

I have just finished Rob Collie's and Avi Singh's book on Power Pivot and Power BI in its second edition. In it they state to NEVER relate two fact tables to on another using relationships, since it would heavily draw on performance. Instead they argue to use a shared lookup table for both data tables.

Maybe I can't think this through straight, but I cannot figure out how I would do this in this case. How would this table lookup like, ie what fields would it contain? I guess it would have to be a unique combination of [weekday]&[transactionKey], in that case I could add a calculated column in #2 and relate it to the shared lookup table. But what would I do to relate #1?

On the other hand it would be so simple to just relate #1 and two #2 to each other, since #1 contains already unique values for [transactionKey]. But then wouldn't I just treat #2 as the fact table and let #1 the lookup table? Maybe technically this makes sense...?

Maybe another financial analyst here that can relate (pun intended)?

If something is not as clear as it should be let me know and I will do my best to clarify.

Cheers
1 ACCEPTED SOLUTION

You only have one fact-table here: Table 2 with the details. Table 1 is a lookup-table (for the transaction details, with unique transaction key). So not only no reason at all not to link them together, but also no alternative 🙂

 

Regard it as a formerly long and wide fact-table where someone was wise enough to split it up already into an optimized form (this process is called "Normalization")

 

I also don't understand what benefits @Greg_Deckler intermediate table (1) should bring?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
chefe
Helper II
Helper II

Hi

 

First of all thank you all for taking the time to answer!

 

@Greg_Deckler:As reasonable as it sounds, I could not have come up with it, thank you! 

 

@BIXL: Im only just playing around and see what I can do to impact our reporting, as of now, my data basis is a huge flat table (aka Frankentable, with Rob Collie's words 😉 and I am trying to unflatten it and create relationships. The use case is reporting on position keeping and valuation of currency derivatives.

 

@ImkeF: Good to hear my intuition is not completely wrong 😉 Doing this - I guess - will likely be positive for performance in the long term? Afaik, the general rule is to keep data tables narrow and long (!) whereas lookup tables can be wider.

 

Cheers

 

 

 

You only have one fact-table here: Table 2 with the details. Table 1 is a lookup-table (for the transaction details, with unique transaction key). So not only no reason at all not to link them together, but also no alternative 🙂

 

Regard it as a formerly long and wide fact-table where someone was wise enough to split it up already into an optimized form (this process is called "Normalization")

 

I also don't understand what benefits @Greg_Deckler intermediate table (1) should bring?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF - I agree with you that in this case I don't think that the intermediate lookup table is necessary, I guess I was just "answering the mail" on the original question that in the case that you actually have two fact tables and do not want to directly relate them, you can always create a lookup table from the data sources, make sure that you remove duplicates and can then have a common lookup table.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

@Greg_Deckler Yes, this makes sense, as 2 real different fact-tables wouldn't have a 1:n-relationship like in this example here.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

"....On the other hand it would be so simple to just relate #1 and two #2 to each other, since #1 contains already unique values for [transactionKey]. But then wouldn't I just treat #2 as the fact table and let #1 the lookup table? Maybe technically this makes sense...?..."

 

Exactly!

 

If your transaction table has been split up into 2 tables in your source system, that doesn't make it 2 different transaction tables that shouldn't been joined together. The 2 different transaction tables the authors had in mind have nothing in common with your example here.

 

Your table structure can be compared to invoices that have been split up into header (1) and lines (n). Just the fact that in finance-& ERP-systems they are normally stored in different tables (to be joined by invoice ID) doesn't make them different transaction tables. Just the opposite: It's one transaction table that has been split up into 2 due to technical reasons.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

BIXL
Resolver I
Resolver I

Hi  #chefe

 

Can you please decribe in more details what is the business case you need to solve from linking these 2 tables ?

It would also help if you attached some example of your data

 

BIXL

Greg_Deckler
Super User
Super User

You could always create a transactionkey lookup table by just importing that column from the same data source as you are getting #1 and relate both to that.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors