Pages

Men

rh

7/10/2013

Configuring the Analysis Services Processing Task in SQL Server 2012 Integration Services

Problem

How can we gain more control over the processing of the Analysis Services objects in SQL Server 2012?

Solution

The Analysis Services Processing Task in SQL Server Integration Services (SSIS) allows for the processing of one to many to all Analysis Services objects in an SSIS package. Once the SSIS package is created, then a job can be created within the SQL Server Management Studio which will allow for scheduling, restarting, alerts and notifications.

The Analysis Services Processing Task is in the SSIS Toolbox when the Control Flow tab is selected.

The Analysis Services Processing Task is in the SSIS Toolbox when the Control Flow tab is selected

Drag an Analysis Services Processing Task from the SSIS toolbox to the Control Flow palette. The red circle with the white "X" indicates that no connection manager is specified

Drag an Analysis Services Processing Task from the SSIS toolbox

A new connection manager can be created by right-clicking in the Connection Managers window and selecting "New Analysis Services Connection...".

A new connection manager can be created by right-clicking in the Connection Managers window

A new connection manager can also be created by double-clicking on the Analysis Services Processing Task, and then clicking on "Processing Settings" in the Analysis Services Processing Task Editor. This will bring up the dialog shown below. Click on "New...".

A new connection manager can also be created by double-clicking on the Analysis Services Processing Task

Either method above will display the Add Analysis Services Connection Manager. The connection string can be edited manually or generated by Visual Studio by clicking the "Edit..." button.

Either method will display the Add Analysis Services Connection Manager.

In the Connection Manager window, the "Provider:" drop-down box defaults to "Native OLE DB\Microsoft OLE DB Provider for Analysis Services 11.0". The "Initial catalog:" drop-down box lists all of the Analysis Services databases in the server instance. Select an Analysis Services database and then click "Test Connection" to verify the credentials are valid. A message box will pop-up indicating a successful or failed connection test.

the "Provider:" drop-down box defaults to "Native OLE DB\Microsoft OLE DB Provider for Analysis Services 11.0".

MSSQLTips.com Sample Image

If successful, click on "OK" twice to return the focus to the Add Analysis Services Connection Manager.

Changes to the connection string will be shown in the text box below the "Create a connection to a computer running Analysis Services" radio button. Click on "OK".

Create a connection to a computer running Analysis Services" radio button.

Select the newly created connection manager in the "Analysis Services connection manager:" drop-down box, if the drop-down box is not populated.

Select the newly created connection manager in the "Analysis Services connection manager:" drop-down box

Click on the "Add..." button to display the "Add Analysis Services Object window. By default, nothing is selected.

Click on the "Add..." button to display the "Add Analysis Services Object window.

Checking on the top level checkbox will allow for all objects (cubes, measure groups, partitions, dimensions and mining models) to be processed. In this example, there are no Mining models; therefore the box to show or hide the Mining model objects is not displayed.

Checking on the top level checkbox will allow for all objects

Selecting a checkbox at the cube level will process the cube and all of its objects. In the image below, the InsuranceExample cube will be processed.

Selecting a checkbox at the cube level will process the cube and all of its objects

Selecting a checkbox at the measure group level will process the measure group and all of its partitions. In the image below, the Fact Patient Claims measure group will be processed.

Selecting a checkbox at the measure group level will process the measure group and all of its partitions

Selecting a checkbox at the partition level will process the specified partitions. In the image below, only one partition exists and it will be processed.

Selecting a checkbox at the partition level will process the specified partitions.

Selecting checkboxes at the dimension level will process the specified dimensions. In the image below, the provider and patient dimensions will be processed.

Selecting checkboxes at the dimension level will process the specified dimensions.

Clicking on "OK", will add the objects to the "Object list:" in the Analysis Services Processing Task Editor. By default, the Process Options for the Dimension type are set to "Process Update"
.
By default, the Process Options for the Dimension type are set to "Process Update".

Several processing options for each object are available in the "Process Options" drop-down list box.

Several processing options for each object are available in the "Process Options" drop-down list box.

Clicking on "Impact Analysis" in the Analysis Services Processing Task Editor will display a list of the objects that will be affected by the processing task. Because the process option for the dimensions is "Process Update", there is nothing displayed in the Object list box as shown below. Clicking on "OK" returns the focus to the Analysis Services Processing Task Editor.

Clicking on "Impact Analysis" in the Analysis Services Processing Task Editor will display a list of the objects that will be affected by the processing task.

However, when we change the process option for the dimensions to "Process Full" and then click on "Impact Analysis" again, the object list in the Impact Analysis window is populated with the cube, measure group and partition.

change the process option for the dimensions to "Process Full" and then click on "Impact Analysis"

the object list in the Impact Analysis window is populated with the cube, measure group and partition

Clicking on "Change Settings..." in the Analysis Services Processing Task Editor will display the Change Settings window. There are two tabs in this window, the "Processing options" tab and the "Dimension key errors" tab. The "Processing options" tab allows for parallel or sequential processing and also for the automatic processing of affected objects.

MSSQLTips.com Sample Image

The "Dimension key errors" tab allows for the specification of dimension key error handling. Clicking on "OK" returns the focus to the Analysis Services Processing Task Editor.

The "Dimension key errors" tab allows for the specification of dimension key error handling

Click on "General" to set the Name and Description of the task. Click on "OK" to return to the Control Flow palette.

Click on "General" to set the Name and Description of the task.

Click on "OK" to return to the Control Flow palette.

Additional Analysis Services Processing Tasks can be added to ensure complete processing.

Additional Analysis Services Processing Tasks can be added to ensure complete processing.

Processing options can be changed and the tasks can be arranged on the palette to optimize performance.

Processing options can be changed and the tasks can be arranged on the palette to optimize performance.


Source collected from MSSQLTIPS.COm

No comments :

Post a Comment