Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (7)   |   Related Tips: > Analysis Services Dimensions

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.

daniel king

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:

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.

    SSDT

  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


    Last Update:






    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





    More SQL Server Solutions











    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    Notify for updates 


    Get free SQL tips:

    *Enter Code refresh code     



    Thursday, May 08, 2014 - 5:03:17 PM - AJ Back To Top

    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.

    Thanks

     


    Tuesday, May 06, 2014 - 3:28:21 AM - Neha Back To Top

     

    Thanks


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

    You are welcome.


    Wednesday, September 04, 2013 - 3:44:33 PM - Alex Whittles Back To Top

    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: http://www.purplefrogsystems.com/blog/2013/09/dimension-processadd-in-ssas/

    Thanks again

    Alex


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

    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

     

    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

    King Daniel,

    Congrats on the new nation!

    Thank you,
    Jeremy Kadlec
    Community Co-Leader


    Learn more about SQL Server tools