How to Integrate Leapwork with Tableau (Enterprise Edition)
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 Edition 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 look somewhat like below:
Working
Leapwork has created an 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 transformed and loaded into the DataMart Database which is acting as an intermediate SQL Database for the end-to-end ETL process.
Pre-requisites
Before deploying the SSIS package, the user needs to review the following information:
SQL Server Integration Services should be installed. Go to SQL Server Configuration Manager → SQL Server Services and check if SQL Server Integration Services is running.
If SQL Server Integration Services is 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 deployment of the SSIS package on SQL Server should be performed. Before deploying, please make sure that SQL Server supports Integration Services.
First, the user should create the 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] so that the field ‘Path’ will be available and [Next].
The results of the deployment should look like this:
In SSMS, if we refresh ( F5 ) the 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, go to tab Connection Managers and replace default connection settings with your own (i.e. ServerName:VADIMPC\VZMSSQLSERVER or .(dot) to ServerName:Server\MSSQLSERVER). This applies 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 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.