Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Analysis Services ProcessAdd for a Partition


By:   |   Last Updated: 2014-03-17   |   Comments (1)   |   Related Tips: > 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:

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.

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:

    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]
     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.

    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:



Last Updated: 2014-03-17


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 

 

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.

 

 


Learn more about SQL Server tools