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???
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.
