SSAS provides Process Incremental as one of the processing options for cubes, measure groups and partitions. It is important to understand how Process Incremental works because it differs significantly from the seemingly equivalent Process Update for dimensions.
Process Incremental is extremely useful to process facts having journal entries. In a journal fact, updates to existing fact records are balanced by a corresponding negative or positive journal entry. In Process Incremental mode, a SQL query has to be provided that could identify the records inserted into the fact journal since last cube process.
Screen Capture 1 – Process Incremental
Screen Capture 2 – Process Incremental For Journal Fact
Process Incremental mode is also useful if the Data Warehouse has staging tables that contain the delta fact records for a specific run. In that case the SQL would be straight fetch from the delta fact table.
Screen Capture 3 – Process Incremental for Delta Facts
In fact, Process Incremental mode only adds fact records to cubes, measure groups and partitions. It cannot detect an update or delete. It is unlikely that Process Incremental mode would be used on Snapshot fact tables. In a Snapshot fact, records could be updated in place or deleted and replaced by a new fact record. If Process Incremental mode is used in such a scenario, it would introduce duplicate fact records in the partition.
Process Update, on the other hand for Dimensions could detect inserts, updates and deletes. I would say Process Incremental mode for cubes, measure groups and partitions is similar to Process Add of Dimensions and significantly different to Process Update.
Interesting and simple explanation of Process Incremental. Good information to know. Thanks
Posted by patricktx21 | November 3, 2011, 08:05