By: Dallas Snider | Comments (1) | Related: 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.
Now, we need to create two separate connections in the Connection Manager. One connection should be an OLE DB connection.
The other connection needs to be an Analysis Services connection.
With both connections set up, let's double click on the Data Flow Task to edit it.
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".
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
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.
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.
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.
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.
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.
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.
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.
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.
- Please refer to the following tips for further assistance with Analysis Services and Integration Services:
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
