How to integrate Leapwork Reporting with Power BI

Introduction

In this article, you will learn how Leapwork’s Enterprise version integrates directly with Power BI for visualization purposes, using a three-layered mechanism that defines the end-to-end architecture.

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.

• Once the data source has been updated the graphs should appear rendering similar to the ones below.

• In case the refresh is not done automatically we can trigger it manually, from here.

Dashboard would look like below:

Resolve Data refresh

When you try to load data into the Power BI after providing the DB details, an issue may occur that stops the data load with error:

• LE DB or ODBC error: Exception from HRESULT: 0x80040E4E. An unexpected exception occurred.
• Column ‘I n Table ‘’ contains a duplicate value ‘’ and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

Refer to the below link for the description of the error and its resolution.

https://community.fabric.microsoft.com/t5/Desktop/Get-quot-not-allowed-for-columns-on-the-one-side-of-a-many-to/m-p/69118/page/2

Resolution

1. From the left side of the Power BI Desktop or Modelling on the Menu Bar, in Model section > Manage Relationships, select and delete all the relationships (or connections) amongst the tables.
2. Once deleted, click Close.
3. In home, click Refresh or click Apply Changes.

Attachments:

Power BI resources

For any clarification, please contact our Priority Support.