Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a source table that will become a fact table in a data mart downstream. The source table has 4 natural keys, but I need to add 2 additional keys that are determined by some analysis.
My thought is to create a two column table, one ID column that matches the ID's in the source table that is to become the fact table, and a second column of a JSON object holding the additional keys.
I then would add the additional columns to hold the keys in the model, also assign the relationship to the appropriate dimension.
In the mappings, I would do a JSON lookup to resolve the key values for the two additional key columns I added.
My thoughts are that this technique would allow me to T2 the fact table, in the case where any of the 6 keys changed (4 keys via natural transactions and the two additional keys populated every time the JSON lookup was performed).
Question 1 is - is this a good idea, the only example I found uses this technique for attribute data and not keys/foreign keys.
Question 2 is - will this technique detect T2 changes in the set of 6 keys?
Question 3 is - is there a documented process for augmenting additional keys, that provides T2 capability, when the source table is lacking keys.
ron
I do not understand completely what you ask, but let me give a couple of ideas from my side, and we can then flesh out the issue so we can get to an answer.
Maybe, provide a bit more clarity on the problem statement will help here. I'm not 100% sure what you trying to achieve.
This was an old issue...I ended up with a workable solution, in production right now. The ultimate solution, was a very wide 'key-table' for the fact row...works great. To explain a bit as my solution is quite involved...The source table has 4 natural keys. These keys are at the bottom of several hierarchies. Basically, my solution was to add all the keys in the hierarchy above the source table to a key table. Had to be done with t-sql.