Power BI Pipedrive Integration: Easy Steps

This article discusses some of the best ways to extract data from Pipedrive and connect with Power BI for analysis. Power BI is a powerful platform that enables organizations to collect data from multiple sources and share insights. However, Pipedrive is a CRM platform that helps in the management of long sales processes. Let’s discuss how to establish Power BI Pipedrive integration.

Table of Contents

• What is Power BI?
  • Key Features of Power BI
• What is Pipedrive?
• Power BI Pipedrive Integration: Easy Steps
  • Step 1: Pipedrive to PostgreSQL
  • Step 2: Load Data from PostgreSQL to Power BI
    • Configure PostgreSQL
    • Configure Power BI
• Conclusion

What is Power BI?

Power BI is a Microsoft Business Intelligence (BI) tool that allows organizations to analyze and visualize raw data and create interactive insights. It connects multiple apps, connectors, and software services together to convert their data sources, visualize them, and discover vital information to make data-driven decisions. 

In simple terms, Power BI enables organizations to collect data from different sources, visualize them, and share insights. You can create multiple graphs and charts to visualize these data and build interactive dashboards.

It is good for any organization as it provides streamlined publication, comprises distribution capabilities, and allows support to integrate with multiple Microsoft products and services.

Another feature that makes it a top choice is it provides real-time stream analytics, i.e., you can access different sources to fetch data and get real-time analytics for better business decisions.

Overall, Power BI is a great business tool that offers a secure connection to data sources in the cloud or on-premises, supports hybrid configurations, and helps build insightful reports. Power BI Data Gateway, Power BI Desktop, Power BI service, Power BI Mobile Apps, and Power BI Report Server are a few types of Power BI tools that one can use to generate actionable information.

Key Features of Power BI

Power BI is one of the popular Business Intelligence tools currently used by many enterprises for its excellent services and features. Here are a few key features of Power BI that make it a top choice in the market.

• Streamlined Analytics: Power BI supports stream analytics features that further aid businesses in timely decisions. Through stream analytics, the report developers can run real-time analytics on multiple streams of data collected from sensors, devices, social media sources, or other applications.
• Supports Multiple Data Sources: Another vital feature of Power BI tools is it supports multiple data sources, including CSV, SQL Server, Web files, Excel, PDF, XML, MySQL database, etc., to generate graphs and charts for interactive visualizations.
• Power BI Desktop: Using this free software, you can easily access data and create reports. In order to run or use Power BI Desktop, all you have to do is download or install the software. Also, you do not require any expertise or query skills to run and build a report.
• Quick Collaboration: Using the Power BI’s excellent features, one can easily collaborate and distribute their reports within the organization. Power BI fulfills both the self-service and organizational data analytics requirements on one screen. It supports semantic models, an open connectivity framework, and an application lifecycle management (ALM) toolkit that allow coworkers and teams to share reports, datasets, and workbooks quickly.
• Deep Insights: Power BI’s visualization, analytics, and reporting capabilities enable businesses to track the data regardless of its size/type and interpret it. It supports various custom Q&A buttons, graphs, and charts that further help in generating interactive and insightful reports for quick decisions.
• Easy Implementation Process: The process to implement Power BI tools across enterprises is a quick process. You hardly require any engineering abilities or technical knowledge to install the software for performing analysis and generating insightful reports. All you need is to develop an API key or plug it inside the software to implement the tool.

What is Pipedrive?

Pipedrive is an all-in-one CRM platform designed with an approach to selling more with minimum manual work. The CRM software is easy to use and hardly takes any time to install or run. Also, the platform allows syncing with other 300+ tools, including Xero, Asana, Kixie, Google, etc. 

With the help of this platform, users can prioritize their activities, monitor deals, and schedule calls and emails as per their requirements. It also supports other features like one-click contact data collection, task automation, customization, integration, data protection, etc.

Today, most small- and medium-sized businesses use Pipedrive to drive more sales, monitor deals, and manage the pipeline. Its simple user interface helps in better management of sales deals and streamlines every action to make a successful sale.

Get full visibility into the different Sales Pipelines using Pipedrive. It is a popular Cloud-based application that can be accessed from any location via web browser and mobile applications. There are more advantages of using Pipedrive, such as it improves customer retention and sales productivity, builds customer satisfaction, and allows execution of better forecasting and reports.

Now that you’re familiar with Pipedrive and PostgreSQL, let’s dive straight into the Power BI Pipedrive integration.

Power BI Pipedrive Integration: Easy Steps

Many companies support Power BI Pipedrive integration as it helps perform an in-depth analysis of Pipedrive data. Connecting Pipedrive with Power BI is not possible without coding. However, there are a few other ways through which you can replicate to the Data Warehouse and then try connecting with Power BI. Another method is to use Restful API for the same or build a script.

You can not integrate Power BI Pipedrive directly. You will need to extract your Pipedrive data via the APIs provided by Pipedrive, load those results into a PostgreSQL Database, and then load the data from PostgreSQL to Power BI.

Follow the below-mentioned steps to establish a Power BI Pipedrive integration.

• Step 1: Pipedrive to PostgreSQL
• Step 2: Load Data from PostgreSQL to Power BI

Step 1: Pipedrive to PostgreSQL

The first step of Power BI Pipedrive integration requires you to load data from Pipedrive to Power BI. To analyze all your data using Power BI, one must load the Pipedrive data to a PostgreSQL Database and then connect it with Power BI. Pipedrive provides a RESTful API that helps developers to reach the stored information in the platform.

First, you will need to create a schema in PostgreSQL so that the schema can ingest the data from Pipedrive. 

Note: This schema should have a combination of tables with appropriate data types ( Integer, Float, JSON, String, etc.) to house each of the Pipedrive datums.

Connect to your PostgreSQL shell and create a table using the following query:

CREATE TABLE activities (
activity_id Integer SERIAL, 
success BOOLEAN 
data JSON,
related_objects JSON,
additional_data JSON, 
created_at TIMESTAMP WITH TIMEZONE 
); 

Next, fire an API call to “GET/v1/activities/{user_id}” to get all “Activities” assigned to a particular user. You can use the PostgreSQL library called urllib2 to GET/POST data to URLs.

import urllib.parse
import urllib.request

"host" = "api.pipedrive.com" 
values = {"base_path" : "/v1", 
          "api_token" : "hg567JKNM_0ghTY67",
          "scheme" : "https" }

data = urllib.parse.urlencode(values)
data = data.encode('ascii') # data should be bytes
req = urllib.request.Request(host, data)
with urllib.request.urlopen(req) as response:
   result_json = response.read()

You will get an aggregate JSON object called result_json after running the above query.

Next, use the above JSON object to create a PostgreSQL JSON array:

jsonb_agg(result_json) 

Next, formulate a function that can insert multiple rows into PostgreSQL.

For example:

CREATE OR REPLACE FUNCTION insertdata(json  result_json) ) 
  RETURNS VOID AS 
$$ 
 INSERT into activities values ( 0, json_to_recordset(result_json) ) ; 
$$ 
LANGUAGE sql STRICT; 

In the above query, 0 is used to allow PostgreSQL to generate auto-increment (SERIAL) integers that will act as unique IDs for each record. 

You will also need to create tables for User/UserSettings/Teams/Subscriptions/Product/ProductSettings/Persons/PersonSettings/Organizations/OrganizationFields/OrganizationRelationships etc., as well as for Notes/Leads/Calls/Deals/Files/Billing etc. 

Next, make appropriate API calls to fetch the most recent data and populate these tables. 

After completing the above steps, you will also need to run this periodically, and update/insert any new data that has been recorded by Pipedrive since your last run. 

Most people would add timestamps to each data row being entered in PostgreSQL to mark pre-existing data, on every new run of the program. So, this was the first step of Power BI Pipedrive integration.

Step 2: Load Data from PostgreSQL to Power BI

The second part of Power BI Pipedrive integration requires you to load data from PostgreSQL to Power BI. You will need to configure both PostgreSQL and Power BI to connect PostgreSQL to Power BI. Follow the below-mentioned steps to accomplish the Power BI Pipedrive integration.

• Configure PostgreSQL
• Configure Power BI

Configure PostgreSQL

First, edit the PostgreSQL pg_hba.conf configuration file and allow remote connections to all Databases:

sudo nano path/to/your/file/pg_hba.conf

Change the following line:

# TYPE   DATABASE   user    ADDRESS        METHOD
host        all                     all      0.0.0.0/0          md5

Save and close the file, then edit the postgresql.conf configuration file:

sudo nano path/of/your/file/postgresql.conf

Disable the SSL by changing the following line:

ssl = false

Save and close the file, then restart the PostgreSQL service to apply the configuration changes:

sudo service postgresql restart

Configure Power BI

You’re almost there, here’s the last bit of Power BI Pipedrive integration. Open the Power BI Desktop tool. Click on “Get data” and choose the option “More“.

Next, select the “PostgreSQL database” under the database option and click on the “Connect“.

In the next screen, you will need to enter the PostgreSQL Server IP and the Database name.

Next, provide the user name and the password of the PostgreSQL and click on “Connect“.

In the “Navigator” dialog, select and load the table you want to work with. Click on the “OK” if Power BI warns you about encryption.

This brings us to the end of Power BI Pipedrive integration.

Conclusion

The best practice to analyze Pipedrive data using Power BI is to convert data to a Data Warehouse platform and connect with Power BI for analysis. Using the Power BI platform, one can gain access to perform an in-depth analysis of Pipedrive data. Follow the above-listed steps to integrate Power BI Pipedrive.

This article introduced you to Power BI and Pipedrive and later took you through the Power BI Pipedrive integration. However, it’s easy to become lost in a blend of data from multiple sources. Imagine trying to make heads or tails of such data. This is where Hevo comes in.

visit our website to explore hevo

Hevo Data with its strong integration with 100+ Sources & BI tools allows you to not only export data from multiple sources & load data to the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools.

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of establishing Power BI Pipedrive integration in the comments section below.