topic Re: Adding additional keys to fact table in Qlik Compose

Adding additional keys to fact table

RonFusionHSLLC — Fri, 14 Mar 2025 20:49:07 GMT

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

Re: Adding additional keys to fact table

robertcur — Fri, 18 Nov 2022 08:30:38 GMT

@RonFusionHSLLC

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.

If you like to have T2 (type2) tables, remember that you Business Keys in Compose will be in the HUB table and cannot be type 2. They will be type 1. So somehow, you need to get a persistent business key which will then be stored in the HUB.
Then you can set the rest of the keys as normal fields (type 2) which may, or may not relate to another table. If you need to link two tables, just use a relationship between them, even if it is a composite key (more than one field).

Maybe, provide a bit more clarity on the problem statement will help here. I'm not 100% sure what you trying to achieve.

Re: Adding additional keys to fact table

RonFusionHSLLC — Fri, 18 Nov 2022 16:46:01 GMT

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.