SQL Server Analysis Services ProcessAdd for a Partition

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

Problem

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?

Solution

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:

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.

Requirements

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

Steps

1. In this sample, we are going to add data from a table named AddPartitionFactInternetSalesReason to our 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]
    values
    (
    'SO43697',1,1
    )

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.
   
   
   
   
   
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.
   
   
   
   
   
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.
   
   
   
   
   
10. On the Process Cube windows, select "Process Add" from the drop down box for the Process Options.
    
    
    
    
    
11. In the settings field press the Configure link.
    
    
    
    
    
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.
    
    
    
    
    
13. Start the ProcessAdd by clicking on the 'Run...' button.
    
    
    
    
    
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.
    
    
    
    
    
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).
    
    
    
    
    

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.

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:

• https://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=4
• http://technet.microsoft.com/en-us/library/ms174774.aspx.

About the author

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