Forum Discussion
Helper IIHow not to relate two data tables
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?
Helper IIHi
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
- ImkeF
Community Champion
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?
- Greg_Deckler
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.
- ImkeF
Greg_Deckler Yes, this makes sense, as 2 real different fact-tables wouldn't have a 1:n-relationship like in this example here.
