Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Introduction to the Dimension Processing Destination in SSIS 2012

MSSQLTips author Dallas Snider By:   |   Read Comments (1)   |   Related Tips: More > Integration Services Analysis Services Tasks
Problem

There are situations in SQL Server Analysis Services (SSAS) when rarely changing dimensions need additional values. Examples of these types of rarely changing dimensions are date and currency descriptions. These dimensions are typically not processed nightly and changes to the production environment for a one-off modification or insertion can generate a large amount of quality control and logistics issues.  Check out how to process these dimensions.

Solution

SQL Server 2012's Integration Services (SSIS) provides the capability to insert values and incrementally process dimensions in an SSIS package that can be run separately from established nightly SSAS processing. In this tip, the Dimension Processing Destination in SSIS 2012 will be demonstrated using the Currency dimension in the AdventureWorksDW2012 database. We will also utilize the SSAS objects produced by the AdventureWorksDW2012 Multidimensional-SE tutorial.

The first step is to insert the new records into the Currency dimension table as shown in the T-SQL below.

insert into dbo.DimCurrency values ('BZD', 'Belize Dollar')
insert into dbo.DimCurrency values ('XCD', 'East Caribbean Dollar')
insert into dbo.DimCurrency values ('KYD', 'Cayman Islands Dollar')

The next step is to create a new SSIS package. In this package, we will add a Data Flow Task to the Control Flow as shown below.

MSSQLTips.com Sample Image

Now, we need to create two separate connections in the Connection Manager. One connection should be an OLE DB connection.

MSSQLTips.com Sample Image

The other connection needs to be an Analysis Services connection.

MSSQLTips.com Sample Image

With both connections set up, let's double click on the Data Flow Task to edit it.

MSSQLTips.com Sample Image

The following steps describe how to set up the data flow objects for this tip.

  • Drag an OLE DB Source onto the Data Flow palette from the SSIS Toolbox.
  • Drag a Dimension Processing destination from the SSIS Toolbox to the Data Flow palette.
  • Drag a connector from the OLE DB Source to the Dimension Processing destination.

In the image below, the OLE DB Source has been renamed to "Get New Records".

 

MSSQLTips.com Sample Image

The following steps describe how to set up the OLE DB Source.

  • Double click on the OLE DB Source to display the OLE DB Source Editor.
  • On the Connection Manager page of the OLE DB Source Editor, set the OLE DB connection manager to the SQL Server Database connection we created earlier.
  • Set the Data access mode to SQL Command.
  • Paste the following T-SQL query into the SQL Command Text box. Please note you might have to change the query from what is shown here to ensure you have selected the recently inserted records.
select 
CurrencyKey,
CurrencyAlternateKey,
CurrencyName 
from dbo.DimCurrency 
where currencyKey>=106

 

MSSQLTips.com Sample Image

Clicking on the Columns page of the OLE DB Source Editor will display the columns returned by the query. We will click on OK to close the OLE DB Source Editor and return focus to Visual Studio.

MSSQLTips.com Sample Image

Double clicking on the Dimension Processing destination will launch the Dimension Processing Destination Editor. Select the Connection Manager page and follow these steps.

  • In the Connection manager drop-down box, select the Analysis Services connection we created earlier.
  • Select the Source Currency dimension.
  • Click on the "Add (incremental)" radio button under Processing method.
MSSQLTips.com Sample Image

Next, we will select the Mappings page of the Dimension Processing Destination Editor Under the first box below "Input Column", select CurrencyKey. Under the first box below "Destination Column", select Source Currency Code.Key. On the next row, choose CurrencyAlternateKey under the InputColumn and Source Currency Code.Name under the Destination Column. On the third row, choose CurrencyName under the InputColumn and Source Currency.Key under the Destination Column. When finished the Mappings page should appear as shown below.

MSSQLTips.com Sample Image

Now, let's explain why we did what we did above. Looking below at the properties of the dimension attributes in the Visual Studio SSAS project, we see where the Source Currency Code's KeyColumns and NameColumn properties are not set to the same value. Therefore, the SSIS Dimension Processing Destination requires that we specify these values.

MSSQLTips.com Sample Image

For the Source Currency dimension attribute, notice how the KeyColumns and NameColumn properties are set to the same value. Therefore, the SSIS Dimension Processing Destination requires that we only specify one mapping between Input Column and Destination Column. This will become more apparent in a future tip where we will use the SSIS Dimension Processing Destination to process a hierarchical dimension such as the Date dimension.

MSSQLTips.com Sample Image

We will leave the settings on the Advanced page of the Dimension Processing Destination Editor set to the default setting of "Use default error configuration". Click on OK to close the Dimension Processing Destination Editor and return focus to Visual Studio.

MSSQLTips.com Sample Image

Now we are ready to press F5 to run our SSIS package in Debug mode. Upon successful completion, the Data Flow window should appear as below. Because we chose "Add (incremental)" processing above, running the package again will result in a duplicate key error.

MSSQLTips.com Sample Image

In a future tip, we will go into more detail about how to use the Dimension Processing Destination to process a dimension with a hierarchy such as the Date dimension.

Next Steps

Use the following T-SQL code to insert records with the new currency codes into the FactInternetSales table.

insert into FactInternetSales values (310,20050701,20050713,20050708,21768,1,106,6,'SO43697', 
2,1,1,3578.27,3578.27,0,0,2171.2942,2171.2942,3578.27,286.2616,89.4568,NULL,NULL,
'2005-07-01','2005-07-13','2005-07-08')
insert into FactInternetSales values (346,20050701,20050713,20050708,28389,1,106,7,'SO43698',
2,1,1,3399.99,3399.99,0,0,1912.1544,1912.1544,3399.99,271.9992,84.9998,NULL,NULL,
'2005-07-01','2005-07-13','2005-07-08')
insert into FactInternetSales values (346,20050701,20050713,20050708,25863,1,106,1,'SO43699',
2,1,1,3399.99,3399.99,0,0,1912.1544,1912.1544,3399.99,271.9992,84.9998,NULL,NULL,
'2005-07-01','2005-07-13','2005-07-08')
insert into FactInternetSales values (336,20050701,20050713,20050708,14501,1,106,4,'SO43700',
2,1,1,699.0982,699.0982,0,0,413.1463,413.1463,699.0982,55.9279,17.4775,NULL,NULL,
'2005-07-01','2005-07-13','2005-07-08')
insert into FactInternetSales values (346,20050701,20050713,20050708,11003,1,107,9,'SO43701',
2,1,1,3399.99,3399.99,0,0,1912.1544,1912.1544,3399.99,271.9992,84.9998,NULL,NULL,
'2005-07-01','2005-07-13','2005-07-08')
insert into FactInternetSales values (311,20050702,20050714,20050709,27645,1,107,4,'SO43702',
2,1,1,3578.27,3578.27,0,0,2171.2942,2171.2942,3578.27,286.2616,89.4568,NULL,NULL,
'2005-07-02','2005-07-14','2005-07-09')
insert into FactInternetSales values (310,20050702,20050714,20050709,16624,1,107,9,'SO43703',
2,1,1,3578.27,3578.27,0,0,2171.2942,2171.2942,3578.27,286.2616,89.4568,NULL,NULL,
'2005-07-02','2005-07-14','2005-07-09')
insert into FactInternetSales values (351,20050702,20050714,20050709,11005,1,108,9,'SO43704',
2,1,1,3374.99,3374.99,0,0,1898.0944,1898.0944,3374.99,269.9992,84.3748,NULL,NULL,
'2005-07-02','2005-07-14','2005-07-09')
insert into FactInternetSales values (344,20050702,20050714,20050709,11011,1,108,9,'SO43705',
2,1,1,3399.99,3399.99,0,0,1912.1544,1912.1544,3399.99,271.9992,84.9998,NULL,NULL,
'2005-07-02','2005-07-14','2005-07-09')
insert into FactInternetSales values (312,20050703,20050715,20050710,27621,1,108,4,'SO43706',
2,1,1,3578.27,3578.27,0,0,2171.2942,2171.2942,3578.27,286.2616,89.4568,NULL,NULL,
'2005-07-03','2005-07-15','2005-07-10')

Process the AdventureWorks cube and then browse the cube to see the distribution of the sales by currency.

MSSQLTips.com Sample Image


Last Update: 7/22/2013


About the author
MSSQLTips author Dallas Snider
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, July 22, 2013 - 9:10:22 PM - Jeremy Kadlec Read The Tip

Dallas,

Great detailed explanation!

Thank you,
Jeremy Kadlec
Community Co-Leader




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.