Dimension Processing Destination...

Dimension Processing Destination

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.

Insert into dbo.DimCurrency values ('BZD', 'Belize Dollar')

Insert into dbo.DimCurrency values ('XCD', 'Caribbean Dollar')

Insert into dbo.DimCurrency values ('KYD', 'Cayman Islands Dollar')

 Create two separate connections in the Connection Manager. One connection should be an OLE DB and others should be Analysis Services connection

 

Double clicking on the Dimension Processing destination will launch the Dimension Processing Destination Editor and Click on the "Add (incremental)" radio button under Processing method

Select the Mappings page of the Dimension Processing Destination Editor and do the proper mapping based on Column Name

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

Insert Record for Fact table W. R. To Dimension Currency

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 it