Fact record does not update

[ubanerjee]

Hello,

We have noticed our fact tables are not updating appropriately. The data in the RAW layer gets loaded into the Satellite with the correct start and end dates. However, our fact table does not update to reflect this change.

The fact where we noticed this issue is a order detail fact and the transaction date is set as the order date.  The fact is defined as a transactional fact and during the load Compose applies this filter on the satellite of the primary entity of the fact to identify records to update

"U"."TXN_DT" >= "E2"."DW_START_DT"
AND "U"."TXN_DT" < "E2"."DW_END_DT"

Based on my understanding, this means it will never update the fact record for changes after the Order Date.

Is this expected behavior? How can we ensure the latest state of the record in the satellite shows up in the Fact table?

[jtompkins]

I am experiencing a similar issue regarding transactional fact tables not getting updated. I have an outstanding support case that has been open for a month now and has not yet been resolved.

The cause of my issue is there not being a matching dim record that the transaction date falls between, even though there is a matching record, but it does not have an obsolete__indication of 0, which is also in the filter criteria.

So the problem they are trying to figure out is why records were being marked as obsolete without a non-obsolete record being added in its place. 

Transactional fact tables paired with SCD2 dimensions has caused us countless headaches.

[TimGarrod · Accepted solution]

Hi,    in Qlik Compose with transactional fact tables, an update was made to the data mart that enforces updating the fact data.     If you edit the "star schema" - i.e. fact table you should see the check box below in the General tab.  "Update fact with changes to Type 2 data warehouse entities".  This should be checked - which will then cascade updates to the transactional fact tables.      If you don't see this as an option, you will need to upgrade to a newer release of Qlik Compose. 

[jtompkins]

In which version of Compose was this option introduced? Because I am on November 2020 and do not have that option.

[ubanerjee]

Thanks Tim, that helps. I am able to see that option in 2021.8.0.139 version. However, we are not able to upgrade to that yet due to another showstopper issue with this version on our environment. 

[jtompkins]

In case anyone is interested, I identified what causes records being marked as obsolete without a non-obsolete record being inserted in its place, thus creating missing fact records and gaps between from dates and to dates. Seems to be a bug.

The explanation makes more sense with an example:

I have an entity called OPERATOR that has almost all type-2 attributes. It has a relationship to another entity, DEPARTMENT, and the history of department changes is kept. An operator can change departments or have no department (so a Department_OID of 0). Department is not part of the business key, it is just an attribute.

If a change to the Department entity is detected, it cascades to the Operator dim by adding a new row with the updated department info, and marks the others as obsolete, including valid historical or current records, leaving them orphaned without a corresponding row where the OBSOLETE__INDICATION = 0. 

So if we start with a record set that looks like this:

OPRID	DEPT	DESCR	OPERATOR_FD	OPERATOR_TD	OBSOLETE__IND
jsmith	ABC	Sales	1/1/1900	3/29/2013	0
jsmith	XYZ	Cust Svc	3/29/2013	4/25/2016	0
jsmith	(NULL)		4/25/2019	12/31/9999	0

An update to Department description for dept code XYZ with everything else unchanged will result in this:

OPRID	DEPT	DESCR	OPERATOR_FD	OPERATOR_TD	OBSOLETE__IND
jsmith	ABC	Sales	1/1/1900	3/29/2013	0
jsmith	XYZ	Customer Service	3/29/2013	4/25/2016	0
jsmith	XYZ	Cust Svc	3/29/2013	4/25/2016	2
jsmith	(NULL)		4/25/2019	12/31/9999	2

As you can see, the most current record gets incorrectly identified as obsolete...thereby having a cascading effect for transactional fact tables where it cannot find a matching dim record with an obsolete indication of 0.

@TimGarrod , thoughts?

[TimGarrod]

This definitely looks like a bug.  

Obsoletion occurs when you have a mix of type1 / type 2 attributes in the modeled layer and denorm them into a single dim in the target side.  Looks like this is what is happening with the 3/29 - 4/25 record. 

But - it should not invalidate the 12/31/9999 row.  Looks like its missing re-adding the 12/31/9999 row ?   Have you submitted a case for this?  

If not - I recommend submitting it (feel free to PM me the case number and I'll take a look also). 

(Also - what version of Compose are you running ) ?

[jtompkins]

Yes, I have a case open that has been unresolved for over a month now ( 00005008: dimension incorrectly marking rows as obsolete). I am on the November 2020 version (7.0.0.551).