Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Solved! Go to 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
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.
@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
"....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
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
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |