How To: Create an automated Analysis Services Processing Task (SSIS package) for a Tabular mode model for reporting with PowerBI

PowerBI

The Scenario

For a customer I am implementing additional tools to monitor and administer their BizTalk environment in a technical and functional manner. One of the solutions is to gather data from BAM and a custom error logging database. The result is a report in PowerBI where we can see the amount of errors per error bucket per day. To accomplish this, we have the architecture depicted below.

We collect data from the BAMPrimaryImport and the ErrorLogging database based on the order number, and save the result in the MonitoringData database avery day at 7 o’clock in the morning. This data contains the new, open and closed orders per error bucket per day. I have created an Analysis Services Tabular Project which will collect the monitoring data from the MonitoringData database, and saves it in a model on the SQL Server Analysis server 2012 SP1. The SQL Server Analysis Server is an Enterprise Edition installed in Tablular mode, which is needed for PowerBI. To automate the collection of the data in order to see the latest status in our PowerBI report we need to create a SSIS Package. In this blog post I will describe how I have accomplished this.

The Solution

In Visual Studio 2012 with Update 4 and Microsoft SQL Server Data Tools installed, I have created a new Integration Services Project. On the Control Flow tab I have dragged an Analysis Services Processing Task.

The error indicates that no connection manager is specified. Right click on the Connection Managers and choose New Connection Manager.

Choose OLEDB and click Add..

Click New…

Fill in the SQL Server name where the MonitoringData database is hosted and select the database.

Click Test Connection to verify you configuration and access rights are sufficient.

Click OK.

Click OK again.

Now we need to configure the connection on the Analysis Services Processing Task. Right click the Analysis Services Processing Task and choose Edit…

Go to Processing Settings and choose New…

Fill in the SQL Server Analysis Server name where the Tabular Model is hosted and select the database containing the model.

Click Test Connection to verify you configuration and access rights are sufficient.

Click OK twice and Click Add… to configure the processing information.

Select the top level to allow all objects (cubes, measure groups, partitions, dimensions and mining models) to be processed. For more information , see Processing Options and Settings (Analysis Services).

I have set the Process Options to Process Default which will detect the process state of database objects, and performs processing necessary to deliver unprocessed or partially processed objects to a fully processed state.

Click on Impact Analysis to show which objects will be affected by the processing task.

The configuration I have made currently does not have any effect on any objects.

Click OK and go to tab General. Change the name and description and click OK.

When you click OK you will see that the error is gone.

Deploy the solution by right clicking on the project and then choose build.

Click Next >

Fill in the Server name and click Browse… to select the path where we can deploy the SSIS package.

Because it is a clean install of the SQL Server Analysis Server, we get the notification below.

An Integration Services catalog (SSISDB) was not found on this server instance (“LocalHost”). To deploy a project to this server, you must create the SSISDB catalog. Open the Create Catalog dialog box from the Integration Services node.

Go to the SQL Server Management Studio and connect to the SQL Server Database Engine…

Go to Integration Services Catalogs, right click and choose Create Catalog…

Fill in the configuration and password depicted below and click OK.

After the creation is finished you can find the SSISDB catalog unther Integration Services Catalogs.

When we return to the deployment and click Browse… again, we see the SSISDB catalog. Click New folder.. to create a new folder.

Name the folder and give it a meaningful description.

Click OK, select the created folder and click OK.

Click Next…

Review the selections and click Deploy.

After a successful deployment, click Close to finish the wizard.

`

When we go back to SQL Server Management Studio and expand the tree under SSISDB, we will find the deployed pachage.

Now we can create a new job to schedule the execution of the SSIS package.

Name the job as desired. Assign a category to it and give a description. I have chosen to create one job for the execution of all the SSIS packages that will be created for the MonitoringData database.

Create a new step the automate the first SSIS package.

Fill in the required fields and select the SSIS package.

Go to the Advanced tab and set the On success action: field to Quit the job reporting success.

Click OK, the result will look similar to the screenshot below.

Click OK and the automation of the Analysis Services Processing Task is done.

Leave a Reply

Your email address will not be published. Required fields are marked *