Compose Feb 2021 data mart adjustments

[jtompkins]

This is in reference to the following from the February 2021 technical preview:

2.2 Data Mart Improvements

In previous versions, whenever data mart tables needed to be rebuilt, Compose dropped and recreated the tables. This would often result in the loss of custom properties. Now, whenever a data mart full build is required, instead of dropping and recreating the tables Compose will truncate them. This will preserve any custom table properties that were applied (e.g. security).

Is there a way to prevent either a truncate or a drop from happening? We need the values of our VIDs to persist because we have those values referenced in historical snapshots, and if the values change, these tables will lose their referential integrity, which is a huge problem for us.

[TimGarrod]

Hi @jtompkins  - this is only related to those data marts that leverage FULL RELOAD capabilities. 

For example - Aggregate Fact tables, State-Oriented Facts or where you may tell Compose to reload the entire data mart.   For transactional fact tables, and for all Dimensions - they are incrementally updated and thus the VID will not change. 

Please note however - if you do alter a dimension (e.g add a column to a type 2) - then the VID's will be recreated becuase the nature of the type 2 could have changed. 

If you are tracking VID's outside of Compose for a specific use case requirement, it is recommended to track the OID and then validate / update VID's as necessary.  OID's will always represent the ID in the root entities HUB table and thus will be consistent on drop / create.  VID's are pure surrogates and could be different if you drop / recreate / full rebuild data marts. 

Hope this helps!

[jtompkins]

Just to confirm: if you add a column to a type 2 dimension, it truncates and repopulates it with VIDs that may not be what they were before?

[TimGarrod]

Yes.   Becuase adding that column completely changes the historical nature of the data.  If you add say phone number - and it wasn't there before then the FROM_DT and TO_DATES are no longer valid.  Previously the dimension didn't care if the phone number change, but now it does - so FD/TD's will be different. 

This is why keeping the OID in anything you build outside / beyond Compose is important.  The OID will not change.  VIDs' will in that type of scenario.

[jtompkins]

Our situation is that we have several extremely large (500+ million rows to over 1 billion in some cases) fact tables that span decades worth of data, and each day we only source and transform a day's worth of transactions. That day's worth of processing gets appended to the fact table that is outside of Compose. Compose only ever processes a single day's worth of transactions in these instances, and the TDWH HUB table gets truncated and repopulated daily. So if we make a change to a type 2 dimension that is used in one of these massive fact tables, all of the historical references in the fact will be invalidated and need to be updated manually?

[TimGarrod]

IF you have altered the dimension? - Yes.   Which would be the same with any dw implementation.  VID's would change even if you manually built surrogate keys...   Cause the relationship of a VID to an EFF/END DATE would have changed. 

You Truncate HUBS??   I'd be very cautious about truncating a HUB table.   If its transactional it may not cause much issue or grief.  BUT when we do things like inferring missing references for related tables we are tracking HUB IDs.  (this may not happen for fact tables - but I'd still caution you). 

If this is a "snapshot use case" (I think we've discussed this before 🙂 ) - have you considered building the snapshot fact table as simply a "HUB" in the main DW.  Create the relationships for OID's and you could perform lookups for VID's ?   Its a way to manage a snapshot requirement in Compose and track the data there instead of pushing to a secondary non-Compose managed table.   

Either way - if you don't have the Dimension OID for your fact tables that are outside Compose - I'd recommend adding it and updating them (yes, lots of udpates, but well worth it) as you can always check OID/VID combinations then and correct as / if neccessary.  

[jtompkins]

Say I have a type 2 dimension named "customer" with columns first name, last name, address. My source data is 20 records. No history has happened so far so VIDS 1-20 exist. I then go and add phone number to the customer entity. The behavior I would come to expect would be to insert a new row in the satellite with a FD of today with the phone number, and then insert a new VID for the current row in the TDMA table, so essentially inserting another 20 records to account for the phone number change, so VIDS 21-40. Any old snapshots that reference VIDS 1-20 should still be valid provided that they reference the correct OID, the only difference would be they would refer to the old (1-20) VID from before the phone number was populated. And anything processed today would have the "new"/current VID (21-40) referencing the record that was created with the phone number populated. Is that not what is happening?