TDMA Fact table points to the wrong dim record

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

I am having an issue where TDWH business key are not being referenced properly in the TDMA. The wrong dim record is being selected. We are running Compose May2022.5.0.304.

Transactional fact table with a column called change_date as the transaction date, the fact table has 'update fact with changes to Type 2 data warehouse entries'

For example

5 records in the TDWH (HUB table that becomes the fact table) all have the same business key in a column (all values = 1873)

Each of the 5 records has a different change_date

In the TDWH the dims have T2 enabled. The records that these business keys point to have not had a T2, only one record in the S01 table.

When the TDMA fact table is created, 3 of the 5 records point to the correct dim record (surrogate ID=538, business key value=1873), and the other 2 are pointed to a business key with a value of 0 and a surrogate ID=5908 in the current case.

So, basically, for 3 records of the 5, the proper business key value was found (1873), and for the other 2 records a business key of 0 was found

business keys TDWH HUB for fact table
LN_CURPLAN_ID CHANGE_DATE
1873.000000000000 5/9/2022 3:11:00 PM
1873.000000000000 5/25/2022 7:58:00 AM
1873.000000000000 8/3/2022 6:47:00 AM
1873.000000000000 8/15/2022 2:16:00 PM
1873.000000000000 5/25/2022 7:58:00 AM

Dim HUB table from TDWH
ID ID_InsPlan Alias
538 1873 UNMED

Dim SO1 table
ID FD TD LastModDt RUNNO_INSERT RUNNO_UPDATE
538 1/1/1780 12:00:00 AM 12/31/9999 12:00:00 AM 3/21/2022 9:16:00 AM 2 2

From this, it's pretty clear that 1873 should point to ID=538, however the TDMA fact table has 2 of the 5 pointing to 5908 with a business key of 0 not 1873???

Fact table in TDMA
CURCInsPlan_OID CURCInsPlan_VID
538 565
538 565
5908 6464
5908 6464
538 565

Dim table in TDMA

CInsPlan_VID CInsPlan_FD CInsPlan_TD CInsPlan_OID ID_InsPlan Alias
565 1/1/1780 12:00:00 AM 12/31/9999 12:00:00 AM 538 1873 UNMED
6464 1/1/1780 12:00:00 AM 12/31/9999 12:00:00 AM 5908 0 CLOSED

Re: TDMA Fact table points to the wrong dim record

Nanda_Ravindra — Thu, 19 Jan 2023 15:01:34 GMT

@RonFusionHSLLC this appears to be a duplicate of this post:

https://community.qlik.com/t5/Qlik-Compose-for-Data-Warehouses/Fact-table-that-uses-Reference-Dims-populates-business-keys-and/td-p/2027357

I'll be marking this as a duplicate. Let me know if you have any questions.

Thanks,

Nanda

Re: TDMA Fact table points to the wrong dim record

lokango — Sat, 25 May 2024 06:30:28 GMT

If i understand correctly, your volume information should be a fact table. A general rule of thumb i use for dim tables is “do i want to slice my report by it?” If yes, then it should be a dimension table.

topic TDMA Fact table points to the wrong dim record in Qlik Compose

TDMA Fact table points to the wrong dim record

Re: TDMA Fact table points to the wrong dim record

Re: TDMA Fact table points to the wrong dim record