Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (4)   |   Related Tips: > Analysis Services Administration

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.
Next Steps


Last Update:


signup button

next tip button



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





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 


SQL tips:

*Enter Code refresh code     



Tuesday, March 17, 2015 - 3:26:13 PM - Puneet Back To Top

Hi,

i want to process dimensions first and then the partition in a singe analysis processing task.

But whenever am trying to add dimension to process and then partition to process... everytime partition comes up first in queue for processing instead of dimension.

so is there any way i can process first dimension and then partiton.


Wednesday, November 26, 2014 - 12:06:37 AM - vanmathi Back To Top

 

Hi ,

 

How long it will take to upate the cube when having large amount of data. Please suggest me the pros and cons of update the cube in real time.

 

Thanks,


Sunday, July 14, 2013 - 1:19:44 PM - MBell Back To Top

WOW - Very detailed.

Screen shots are really helpful!

--MB


Wednesday, July 10, 2013 - 9:18:38 AM - John Back To Top

Hi Dr. Snider,

Thanks for the article. Always good to refresh the memory. By chance is it possible to get a copy of the insurance sample db please?

 


Learn more about SQL Server tools