SQL Server Analysis Services ProcessAdd for a Partition

By:   |   Comments (2)   |   Related: > Analysis Services Development


I have a need to process some new data into my SQL Server Analysis Services Partition.  I know I can process all of the data, but that is a little bit more time and resource intensive than I would like.  Is there a way to process only the new data for a partition in SQL Server Analysis Services?


Yes, there is a way to just process the new data for a partition in SQL Server Analysis Services.  This is accomplished in SQL Server Analysis Services with the ProcessAdd option for partitions.  Let's demonstrate how that works.

On a related note, we demonstrated how to use the ProcessAdd to add rows to a dimension in SQL Server Analysis Services SSAS.  Feel free to check it out.

Getting started

Let's say that we already have 5 million rows of data loaded and we need to add data to the same partition. With the Process Full option in SSAS, all the data is cleared and all the data is loaded again.

The ProcessAdd option only loads new data. This is more efficient and takes less time. You can add data many times as shown in this image:

SSAS load

As you can see in the image, you can do a Process Full and then add the new data. On occasion, you may need to use the Process Full in situations where there were some errors, particular data is out of date.


  1. The Adventure works Multidimensional project.
  2. SQL Server 2005 or later (in this example I am using SQL Server 2012).


  1. In this sample, we are going to add data from a table named AddPartitionFactInternetSalesReason to our cube:

    SSAS tables to add to cube
  2. In order to do this we are going to create the table first:
    CREATE TABLE [dbo].[AddPartitionFactInternetSalesReason](
     [SalesOrderNumber] [nvarchar](20) NOT NULL,
     [SalesOrderLineNumber] [tinyint] NOT NULL,
     [SalesReasonKey] [int] NOT NULL)
  3. And now we are going to add some data in this new table:
     insert into [dbo].[AddPartitionFactInternetSalesReason]
  4. Now, let's add this information in the SSAS cube.
  5. Open the SQL Server Data Tools or the Business Intelligence Development Studio.
  6. Open the Adventure Works SSAS cube.
  7. Go to the Solution Explorer and double click the Adventure Works cube and go to the Browser Tab.

    SSAS Solution Explorer cubes

  8. In the Browser tab go to Adventure Works cube, measures, sales reason, sales reasons count and drag and drop to the report pane. You will notice that there are 64515 sales reasons.

    SSAS Browser

  9. Go to the partitions tab and select the internet sales reason partition and press the process icon. We are going to do a Process Add to the internet sales reason partition.

    SSAS Partitions

  10. On the Process Cube windows, select "Process Add" from the drop down box for the Process Options.

    Process Add

  11. In the settings field press the Configure link.

    SSAS configure process add

  12. On the Incremental Update for the Adventure Works cube, select the "Sales Reasons" Measure Group. By default the only partition in this measure group is Internet_Sales_Reasons. You can add data from a Data Source view, but in this example we are going to query the table created in step 2. We are going to add the data from that table to our cube. Write the query as shown in the image below and press OK.

    SSAS query loaded

  13. Start the ProcessAdd by clicking on the 'Run...' button.

    Run cube

  14. In the Process Progress screen you can note that 1 row was read. As you can see in step 3 above, this table only contains 1 row.

    Process Progress

  15. In order to verify, go to the browse tab in the cube, press the reconnect icon and repeat the step 8. Notice that the number of sales reason increased from 64515 in step 8 to 64516 (1 new row added).

    Browse with new data

In this tip you added new data to a partition using the ProcessAdd option.

You may want to use XMLA to ProcessAdd in some circumstances. The xmla code can be downloaded here.

SSAS xmla processadd code

There is nothing special with the code. You need to specify the DatabaseID, CubeID, MeasuregroupID and in the query definition, you specify the query to be used.

Next Steps

For more information about ProcessAdd for partitions refer to these links:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, September 3, 2019 - 11:13:40 AM - Gagan Back To Top (82224)

Hi Daniel,

I've tried adding the above condition in one of my partition to return incremented data but post implementing this every time i process the partition the data get doubled/duplcated.

Could you please help me on solving this or provided an idea to handle such scenario. I don't want to add/alter the table with column to store ID/Date to maintain the last inserted records as this is done in production environment changing the structure of a table would be another task and would lead me to another world of reloading uploading data.

waiting for soonest reponse.



Monday, March 17, 2014 - 6:19:56 AM - lixia Back To Top (29780)



It’s very interesting post, I try to follow your instruction step by step, but I found that I can see “Sales Reasons” as measure in Cube Structure tab, but it disappeared as measure in Browser tab. Why?

Sorry I can't pasty my copy screen here.



get free sql tips
agree to terms