How to integrate Leapwork Reporting with PowerBI(Enterprise Edition)

Leapwork’s Enterprise version offers a kind of direct integration with PowerBI for visualization purposes. This integration works in a kind of three-layered mechanism where end to end things at the architecture level looks somewhat like below:

Working

Leapwork has created SSIS Package which when deployed in SQL Server extracts the data from Leapwork’s Enterprise Databases(Assets and Reports) and performs the data transformation.

This data is been transformed and loaded into the DataMart Database which is acting as an intermediate SQL Database for End to end ETL process.

Pre-requisites

Before deploying the SSIS package the user needs to review the following information:

SQL Server Integration Services were installed. Go to SQL Server Configuration Manager → SQL Server Services and check if SQL Server Integration Services is running.
If SQL Server Integration Services are not installed, you will need to do it following the instructions
The user needs to be a member of db_dtsoperator, db_dtsltduser, or db_dtsadmin. See Integration Services Roles
DTEXEC which can be installed with Integration Service at the SQL Server setup media

Deploying SSIS package

As a first step, the DataMart SQL Script should be executed and then the deploying of the SSIS package on SQL Server should be performed. Before deploying, please make sure that SQL Server supports Integration Services.

Like a first step, the user should create DataMart database with the structure from the attached DBSetup_Creation.sql file.

To deploy the SSIS package to SQL Server, please open your SSMS (Microsoft SQL Server Management Studio) and go to Integration Services Catalogs.

Create a catalog (e.g. SSISDB):

Input the password here and create a folder where the user will put their project:

Deploy the project:
Click [Connect] that field ‘Path’ will be available and [Next]

The results of the deployment should look like this:

In SSMS if we refresh (F5) Projects tree, we should see our project:

Job Creation and Scheduling

Once the SSIS package is deployed, then a SQL Server job should be configured which should execute the SSIS package based upon the recurrences provided in the configuration process. The configuration should be performed by following the below steps:

Create a new job in SQL Agent for scheduled package execution:

Create a new step:

On tab Configuration >> Parameters we should go to tab Connection Managers and replace default connection settings with your own (i.e. ServerName:VADIMPC\VZMSSQLSERVER or .(dot)to ServerName:Server\MSSQLSERVER) ones for every Connection Manager(e.g. Asset, DataMart, Report):

The Step was created:

Now create a schedule for the current task:

The Job was created.

Make a test Job execution:

Execution of the SQL Server Agent job

Once the job is scheduled and all corresponding configurations are made, the job should be executed. Once the job is executed successfully, the data DataMart database tables are populated with the data sets.

Visualizing data in PowerBI

To visualize the data populated in the DataMart database tables, the PowerBI dashboard already created should be deployed in the PowerBI instance, and then corresponding connection settings should be made. After configurations are made, click on refresh so that the data from the datamart table is populated in PowerBI.

The below steps presents the connection of the Power BI dashboards to the DataMart database that was previously created and synced with the Leapwork database. Also, users need to have the Power BI Desktop version installed in order to make changes on the dashboards, even for configuration of them.

Get the .pbix that we have provided(attached).
Open the Desktop version of Power BI and click the File option at the top.

Browse for the .pbix file and then open it in Power BI (Desktop version)

Now click again the File option at the top and click Options and settings-> Data source settings.

In case there is a data source defined user will need to change that. The Server needs to be the server where the DataMart schema resides and the data from the Leapwork installation gets synced. This was created in the previous guide when deploying the SSIS package. DataMart database should remain as is. In case the user does not have any Datasource in the list you need to create a new one with the values described before, representing the Server and Database where the SSIS package is writing the data to.