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

Problem

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.

Solution

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:

Requirements

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

Introduction

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.

I will create a table with data using this script.

  USE [AdventureWorksDW2012]
GO
/****** Object:  Table [dbo].[DimCurrency2]    Script Date: 01/05/2013 02:00:24 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimCurrency2](
 [CurrencyKey] [int] NOT NULL,
 [CurrencyAlternateKey] [char](10) NOT NULL,
 [CurrencyName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
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:

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.
   
3. Create an Integration Services Project.
   
4. In the SSIS toolbox drag and drop a Data Flow Task to the design pane.
   
5. Double click on the Data Flow Task and now add an OLE DB Source and a Dimension  Processing task.
   
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.
   
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.
   
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.
   
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.
   
10. Now run the SSIS package with F5.
    
11. If everything is OK, a green check should be displayed.
    
12. In order to verify that the value was added, open SSMS and right click the Browse option of the Source Currency dimension.
    
13. If everything is OK, you will be able to see the DaniMony currency in the dimension!
    
14. If you select the Source Currency Code combobox you will be able to see the currency code (in this sample DAN).
    

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.