Max (Attribute Value) in a CDC Process

[jkardos]

In a CDC load environment, how do you maintain an attribute value that represents the maximum or minimum value over a set of records? 

For example, if I have an order with three items, I want to create an attribute called MaxItemRequestDate where this value is the max Request date among the three items.

I have tried using a windowing function on both the entity and in the mapping. But, looking at the task list scripts SQL, it looks as if in the CDC process the function will only be performed over the delta rows in the TSTG "staging" table, and not the entire row set like it would be in a full load.

Can I have the best of both worlds ?

Thanks!

[shashi_holla · Accepted solution]

Hi @jkardos 

During CDC the scope is just the current set of changed records and it doesn't extend to all records in the table. What we can do is to create Post Loading ETL and have the necessary query/logic there. 

Please try and let me know.

Thank you,

[Nanda_Ravindra]

Hello @jkardos  Were you able to try what was suggested by Shashi? If you need help with this, please open

 a support case and we can have a meeting and walk you through the steps.

Thanks,

Nanda

[jkardos]

Yes, Please find the Stored Procedure below.  It's used in Post ETL as suggested and passes in the &&1 run number parameter to make sure the Data Marts update by CDC process. Setting "Do not truncate staging tables" enabled, since using TSTG table to minimize records processed.

CEATE PROC [lz].[usp_FactSalesOrderHeaderIsOrderRejected_PostLoadETL] @runno [INT] AS
BEGIN
UPDATE o
SET o.IsOrderRejected = oi_agg.newval,
o.RUNNO_UPDATE = @runno
FROM DV.FactSalesOrderHeader_HUB o
JOIN
(
SELECT DISTINCT oi.FactSalesOrderHeader,
CASE
WHEN MAX(oi.ReasonForRejection) OVER (PARTITION BY oi.SalesDocument_CNK) = '' THEN
'F'
ELSE
'T'
END AS newval
FROM DV.FactSalesOrderItem_HUB oi
WHERE EXISTS
(
SELECT 1
FROM DV.TSTG_FactSalesOrderItem toi
WHERE toi.SalesDocument_CNK = oi.SalesDocument_CNK
)
) oi_agg
ON o.ID = oi_agg.FactSalesOrderHeader
WHERE ISNULL(o.IsOrderRejected, '') <> ISNULL(oi_agg.newval, '');
END;

[jkardos]

SP is for an attribute other than the example  Request Date described originally, but would work in either case.