Interview Questions on .Net and Msbi Technologies

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.

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

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 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...".

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".$

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.

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

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.

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 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 partition level will process the specified partitions. In the image below, only one partition exists and it will be processed.

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

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.

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.

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.

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.

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.