Benny Austin

SSAS: Process Incremental – What it is & isn’t

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.

Benny Austin

About these ads

16 comments on “SSAS: Process Incremental – What it is & isn’t

  1. patricktx21
    November 3, 2011

    Interesting and simple explanation of Process Incremental. Good information to know. Thanks

  2. Pingback: Improving cube processing time | James Serra's Blog

  3. bartholic
    May 23, 2012

    I will vouche for your last comment about duplicating records. It took me about an hour of trouble shooting my SSRS report to figure out I had duplicated records. Lesson learned.

  4. bartholic
    May 23, 2012

    Yep, I can vouche for the duplicating effect. I trouble shot an SSRS report for an hour before I figured out I had doubled my counts. Lesson Learned.

  5. Rakesh
    July 17, 2012

    I have an situation where my existing records are getting updated. Lets say the sold amount of XXX item was 100$ and record has been processed into cube. Now the value got changed to 200$. I will use process incremental but how to remove the older value from the cube before doing process incremental?

    • Benny Austin
      July 17, 2012

      Rakesh, for the scenario you mentioned Process Incremental is not the right processing option. Unless the underlying fact table can generate journal type entries, Process Incremental option is not advisable. For instance when the value changes from $100 to $150, instead of updating existing record if a new journal entry is created for $50 then Process Incremental would work.

      • Rakesh
        July 17, 2012

        I agree with you.
        But i cannot create an extra entry for the sale price change. So what is the recommended way to handle this when we cannot use Process Incremental option?

      • Benny Austin
        July 17, 2012

        Process Data the partition followed by Process Index

      • Rakesh
        July 17, 2012

        Another way i found to resolve this issue is that for the changed records, I can do the update cube and update that particular leaf cell.
        For the new records, I can do the Process Incremental.

  6. Pavan Barnala
    October 25, 2012

    It is good to know that query specification is mandatory in case of Process Incremental unlike in the case for ProcessAdd for dimensions. ProcessAdd comes with an optional query for specifying the delta data, which is much faster than just reading the entire table data and then letting SSAS decide what the new records are.

    • Pavan Barnala
      October 25, 2012

      I think this optional query specification for ProcessAdd for dimensions via XMLA could have been made mandatory

  7. Pingback: SSAS: Process Incremental – What it is & isn’t | bloger7791

  8. patrick
    April 9, 2013

    Dear all. Thanks for your input. I have a datawarehouse which i am updating daily. I then process the olap cubes after the etl process, so in actual fact am processing a full update. If i flag the records in the data warehouse after the olap process as updated and then process incremental the next day, having only the unflagged records , will this increment the data in the cube?

    • Benny Austin
      April 9, 2013

      Patrick, your method will work if your fact table is journal type fact where existing records are never updated or deleted and instead updates/ deletes are introduced as new journal entries. However if your fact table is a snapshot type fact then your method will introduce duplicates.

  9. Syed Imam
    May 15, 2014

    So What I understand is,

    For Process Increment if your load each time brings new records and has no connection with previous load (Specially for FACT) which could identify by query

    AND

    Your Dimension are only ADDING then

    You can Use for FACT –> Process Increment(Specify By Query For New Records)
    And For Dimensions –> Process Add (Specify By Query For New Records)

    For Example Each Time When my Load Runs I gave them LOAD_ID and which is unique in FACT and Dimension and if I catch that recent LOAD_ID I will be all set?

    How that sounds?

    • Benny Austin
      May 15, 2014

      Syed, that’s right

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Copyright

© Benny Austin.
Follow

Get every new post delivered to your Inbox.

Join 190 other followers

%d bloggers like this: