Forum Discussion
How not to relate two data tables
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
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?
7 Replies
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?
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.
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.
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
"....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.
