Using ProcessAdd to add rows to a dimension in SQL Server Analysis Services SSAS

By:   |   Comments (9)   |   Related: > Analysis Services Dimensions


There is not much documentation about how to do a ProcessAdd for a dimension in SSAS (SQL Server Analysis Services). Doing a ProcessAdd for a partition is pretty easy, but there is not much documentation to do a ProcessAdd for a dimension.  The SSAS documentation says that you cannot do it using the UI, but you can do it with XMLA. It is definitely not a straightforward task neither is using AMO. In this tip we will walk through an example of how to do this for a dimension.


This tip contains a step by step example on how to do a ProcessAdd for a dimension using SQL Server Integration Services. In my opinion it is an easy way to this without XMLA knowledge or AMO knowledge.

In this sample I am going to create a new currency. Let's say that I am a king of a new nation. The new nation is Daniland. The new currency is the Danimoney and the currency code is Dan.

This script will generate a table with that data:


  1. The Adventureworks Multidimensional project.
  2. SQL Server 2008 or later (in this example I am using SQL Server 2012).


Why would I want to do a ProcessAdd? In which scenarios should I use a ProcessAdd and when should I use a ProcessFull and ProcessUpdate?

The ProcessAdd is very useful when you add new members to a dimension, but do not modify or remove old ones. The ProcessAdd is faster than the ProcessUpdate, because the ProcessUpdate updates information in the whole cube.

For example if your SSAS cube contains 5 years of information, the ProcessUpdate will verify all the years and verify if the data was removed or updated. The ProcessAdd instead does not verify old data, it is limited to new members for the dimension. The ProcessUpdate blocks other types of processes. You cannot do a ProcessUpdate of two dimensions in parallel if they are related. The ProcessAdd instead lets you run as many ProcessAdds as you want in parallel. It is extremely fast.  If your SSAS cube is huge, a ProcessUpdate can take hours. If that is the case you should think about using the ProcessAdd.

Getting Started

In this sample we are going to add a new currency type to the currency dimension from a table in the Adventureworks sample.

Let's say that I am a king from a kingdom named Daniel and I have my own currency named Danimony with the currency code Dan.

daniel king

I will create a table with data using this script.

  USE [AdventureWorksDW2012]
/****** Object:  Table [dbo].[DimCurrency2]    Script Date: 01/05/2013 02:00:24 a.m. ******/
CREATE TABLE [dbo].[DimCurrency2](
 [CurrencyKey] [int] NOT NULL,
 [CurrencyAlternateKey] [char](10) NOT NULL,
 [CurrencyName] [varchar](50) NOT NULL
INSERT [dbo].[DimCurrency2] ([CurrencyKey], [CurrencyAlternateKey], [CurrencyName]) VALUES (160, N'DAN       ', N'DaniMoney')

The script creates a simple table with the data I want to add in the dimension:

Table with data

We are going to add the data from this table to the SSAS dimension.

  1. Let's start by opening SQL Server Data Tools (SSDT) in SQL 2012 or the Business Intelligence Development Studio in earlier versions.


  2. Create a new Project.

    New Project

  3. Create an Integration Services Project.

    SSIS project

  4. In the SSIS toolbox drag and drop a Data Flow Task to the design pane.

    SSIS Data Flow Task

  5. Double click on the Data Flow Task and now add an OLE DB Source and a Dimension Processing task.

    OLE DB Source and Dimension Processing

  6. Double click on the OLE DB Source editor and add the query with the new Data. In this example, we will add a new currency.

    Ole DB Source Editor

  7. Now double click on the Dimension Processing task. Click on the connection page and press the new button to create a new connection. Connect to the AdventureworksDW database.

    connection Manager

  8. Now double click in the Processing Dimension task and click on the Connection Manager. Connect to the Adventureworks cube and select the Source Currency dimension. This dimension contains the different currencies for different countries. We are going to add the Danimoney currency to this dimension.

    Dimension Processing Destination Editor

  9. Now click on the Mappings page. What we are going to do now is to map the columns of the table created at the beginning of the Tip (the script before step 1) to the SSAS Source Currency Dimension. Just match the input columns with the available destination columns.

    Step properties

  10. Now run the SSIS package with F5.

    Run Package

  11. If everything is OK, a green check should be displayed.

    Succesful task

  12. In order to verify that the value was added, open SSMS and right click the Browse option of the Source Currency dimension.

    hourly schedule

  13. If everything is OK, you will be able to see the DaniMony currency in the dimension!

    browse dimension

  14. If you select the Source Currency Code combobox you will be able to see the currency code (in this sample DAN).

    Source Curency Code dimension

As you can see, it is pretty simple to do a ProcessAdd using SSIS. In this example we added one single row to the dimension, but you could add thousands of new rows by just adding new rows in the table.

Does it take too much time to process your data? Leave us a comment and we may be able to help you.

Next Steps

    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

    Monday, February 14, 2022 - 12:31:38 AM - Tin Back To Top (89783)
    Hi , because it's for SSAS Multidimensional , so Can I do this way with SSAS Tabular. Thanks

    Friday, June 14, 2019 - 11:12:39 AM - negar Back To Top (81470)


    Do you have a sample with XMLA for process Add dimension


    Thursday, May 8, 2014 - 5:03:17 PM - AJ Back To Top (30685)

    Thanks Dan for the post.

    How does it work in the case where it's not a new record but Type 2 change? So for type 2, existing record needs to be marked inactive and new record needs to be added.

    Looks like this approach will only work for totally new record and not SCD type 2 change.



    Tuesday, May 6, 2014 - 3:28:21 AM - Neha Back To Top (30621)



    Monday, September 23, 2013 - 9:06:59 PM - Daniel Back To Top (26906)

    You are welcome.

    Wednesday, September 4, 2013 - 3:44:33 PM - Alex Whittles Back To Top (26608)

    Great post - thanks for taking the time to share.

    I've taken this as a starting point, and expanded it to automatically detect which source records need to be brought in. I've written it up in a blog posts here:

    Thanks again


    Monday, July 15, 2013 - 3:49:29 PM - Daniel Calbimonte Back To Top (25843)

    Thanks for the comment about the mapping. The mapping is OK.

    To make this sample easier we used the Adventureworks database.

    If you watch the dbo.currency table you will be able to see the column names and data.

    If you compare the date in the dimension attributes and the data in the table, you will find that the alternative key was the only choice to populate the dimension.


    Monday, July 15, 2013 - 12:45:45 PM - David Everson Back To Top (25837)


    Hey Dan,

    It looks like your dimensional mapping is maaped incorrectly. The imput alternate key is mapped to the dest name??? But thanks for the post.

    Monday, July 15, 2013 - 9:02:44 AM - Jeremy Kadlec Back To Top (25833)

    King Daniel,

    Congrats on the new nation!

    Thank you,
    Jeremy Kadlec
    Community Co-Leader

    get free sql tips
    agree to terms