Performance optimization of a business rule view

Description

To keep the amount of data that a business rule processes small, the data should be reduced to those that have not yet been processed. Ideally, only the data since the last time the business rule was executed needs to be processed.

Characteristic

In the TEDAMOH ExMeX Framework, there is the option of including a metadata object (ProcessStepDeltaExport) in the business rule or its logic. The characteristic feature of the metadata in this view is that the delta interval for the (raw) data is formed from the batch belonging to the business rule. However, this also means that all (raw) data on which this business rule is based has already been processed in a previously completed batch. Not in the same batch as the transaction rule!

Load Pattern

The following code snippet shows an example of how the metadata object (ProcessStepDeltaExport) is integrated into a business rule. It is important that the business rule references itself by its name (lines 5-7). All satellites should then be restricted by the delta interval (lines 15-17) and the amount of data therefore reduced. This is always necessary for satellites with transactional data. In the case of master data, the restriction may be counterproductive.

 1 CREATE VIEW <YourBusinessRuleViewName> AS
 2 SELECT <YourBusinessRule>
 3 FROM <YourHub> h1
 4
 5 -- First integrate metadata object (ProcessStepDeltaExport)
 6 INNER JOIN MetadataZone.ProcessStepDeltaExport psde
 7	ON psde.SourceEntityClassCode = '<YourBusinessRuleViewName>'
 8
 9 -- Then join with metadata to get delta interval
10 -- For each satellite
11 INNER JOIN <YourSatellite> sc
12	ON  h1.LieferantSK       = sc.LieferantSK
13	-- If necessary reduce on current data
14	AND sc.LoadEndTimestamp  = '2999-12-31 00:00:00.0000000'
15	-- Reduce with delta interval
16	AND sc.LoadTimestamp    >= psde.DeltaExportAssertionTimeFrom
17	AND sc.LoadTimestamp    <  psde.DeltaExportAssertionTimeBefore