How to Integrate Leapwork with Tableau (Enterprise Edition)

In the current scenario, Reporting is all about Visualization for better representation of the data. Leapwork's Enterprise Editon provides a unique experience of reporting with the help of Dashboards, where the user is independent enough to design the views of how the data should look in a report. 

Although many users heavily rely on third-party tools like Tableau for analytics purposes as a part of their existing frameworks and want to perform analytics around the automation flows executed in Leapwork through these tools themselves.

Leapwork’s Enterprise version offers a kind of direct integration with Tableau 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 with Tableau 

To start using the Tableau integration with Leapwork, make sure all the above steps have been created and followed as mentioned. 

Also, users need to have the Tableau Desktop application downloaded in their local system.

Below Steps need to be followed to start visualizing the data using Tableau

•  Open the Tableau Desktop application
• Click on "Microsoft SQL Server" to connect to the DB
• Enter the details of your SQL server/DB and Click "sign-in".( use (.) to connect to your default DB.
• After the successful sign-in, please select the DB where your data is present.
• Select(Double Click) the table/data which you want to visualize
• Click on "Sheet 1" to start visualizing your data.
• From the Tables where the column names are present, please select or Drag & drop the columns to the respective columns and Rows. Your Data would look like this:

 For any clarification, please contact our Priority Support.