This article will cover the following topics:
NOTE: BI Feeder is a paid-for add-on to Pipeliner. Please contact your Account Manager directly to discuss your needs for exporting Pipeliner data for use with your Business Intelligence (BI) Solution or email firstname.lastname@example.org and we will ask your Account Manager to contact you.
The BI Feeder allows you to export your Pipeliner data so it can be queried from any BI (Business Intelligence) solution that can connect to a PostgreSQL database (for example Google DataStudio, Microsoft Power BI, Tableau, Click...). This will allow your business to report on aggregated data across the whole of your environment and to connect sales data to data from other company departments.
Pipeliner data can be queried and edited inside of the BI solution, without affecting the speed of the Pipeliner App.
Easily build custom dashboards within your BI solution.
Daily synchronization ensures that your data will be always ready for reporting.
Connect and visualize sales data with other departments like Marketing or Finance in your BI solution.
Display Sales Dashboard with current data on your big screen in the office!
No maintenance costs for a client-side database from client-side.
Currently, we support exporting only to a PostgreSQL database.
Activating the BI Feeder
Before you can connect Power BI to your exported database, you need to activate and configure the BI Feeder in the Automation Hub. If you haven’t already done this (and made a note of the credentials to use to connect, please review this article.
Power BI Desktop
First, you need to access and install the Power BI desktop application. You can do this from the Store ⤵
Launch Power BI Desktop and Sign in with your Microsoft account.
Install PostgreSQL ODBC Drivers
If you don’t have ODBC drivers installed for PostgreSQL, you can access them from this page. You’ll need to scroll down to find the file ”psqlodbc_12_02_0000-x64.zip“ (or the 32-bit equivalent) ⤵
Download the file and then install the "psqlodbc_x64.msi" from the zip file.
Create ODBC Connection to use in Power BI
Next, you’ll need to create a PostgreSQL ODBC connection. Search for ODBC Data Sources and select the appropriate variant for your machine ⤵
In the ODBC Data Source Administrator, click on the "System DSN" tab ⤵
And then Add and choose PostgreSQL Unicode(x64) or your equivalent if you’re on a 32-bit machine.
You’ll now need the connection credentials from the Pipeliner Automation Hub. If you haven’t made a note of them already, open up the Pipeliner Admin Module and find BI Feeder in the Automation Hub tab and click on it. The right-hand panel will display the "Server URL" and "User Name" and you can click again on "Generate Password" to view and copy the one-time secure password to use for your connection. You can easily copy the value to the clipboard.
NOTE: Every time you click on Generate Password, a new secure one-time use password is created. If another user subsequently creates another password, you’ll need to update all of your connections to reflect the new password ⤵
Complete all the values in the PostgreSQL setup dialogue box ⤵
Give your data source a descriptive name so it’s easy to select within Power BI.
This is the second half of the Server URL. For example, if your server URL is "xxxxxxxx.pipelinersales.com/export_v100_m10_marketing", paste "export_v100_m10_marketing" into the Database field.
This is the first part of the server URL. For example, if your server URL is "xxxxxxxx.pipelinersales.com/export_v100_m10_marketing", paste "xxxxxxxx.pipelinersales.com" into the "Host Name" field.
This is the "User Name" generated for you in the Automation Hub.
Additional descriptive information if you want to add it.
Click on the dropdown and choose “require”.
Unless you have a specific port set up to use, you can leave this blank. The default port by PostgreSQL is "5432".
This is your one-time secure password from the Automation Hub.
Click on the "Test" button to test your connection before clicking on "Save" and then "OK" to exit the ODBC Data Source Administrator ⤵
In Power BI, select "Get Data" to set up your connection. You’ll need to click on "More"…and can then easily search for ODBC ⤵
Once you’ve selected ODBC, click on "Connect" ⤵
You’ll see the connection you just set up in the "Data Source Name" (DSN) dropdown list ⤵
Select it and click on “OK”.
You’ll need to re-add your "Username" and "Password" for BI Feeder ⤵
Click on “Connect” and then, in the Navigator, you’ll be able to open up your export database to preview and select the tables included in it.
NOTE: Remember the Relation Tables in the Configuration of the BI Feeder in the Automation Hub. These tables act as the bridge between the different entities and specify the fields you need to use in your joins ⤵
Now you’re ready to start building your Power BI reports ⤵