Connecting to HeavyDB using PowerBI Desktop

While PowerBI supports ODBC connectivity, and HeavyDB does as well, allowing communication with PowerBI through the Windows ODBC Driver Manager, installing the ODBC driver on the client host is just a pre-requisite. Still, more is needed to exploit the functionality and speed of the HeavyDB Analytical Engine that the user will get in Direct Query mode.

The necessary steps for enabling the full functionality between the two software are:

• Obtain an ODBC driver with the PBI's Custom Connector from HEAVY.AI sSpport, by submitting a support ticket or sending an email to support@heavy.ai asking for a package containing the ODBC driver and the PBI Custom Connector.
• Install and configure the ODBC driver on the same Windows machine where PowerBI Desktop is installed.
• Perform a manual install of the PBI Custom Connector and the security configurations the Power BI Desktop needs to use the Connector itself.

Installing the HeavyAI ODBC driver.

Download the installation package using the URL provided by the support, or in case you get as an email attachment, copy it into the target machine.

Typically the package name is Omnisci_Install_[version].zip



Extract all the files from the package and run the Installer as Administrator.

The Default path will be in the Program Files folder.

To check that everything is in working order, open the Windows ODBC Data Sources in 64-bit mode and create (add) a new Data Source to connect with your HeavyDB instance.

Please fill out the form with the connection name, user, password, hostname, and database, and click on the Test button to make sure you can connect to the database.

Manual installation of the Power BI Custom Connector

Locate a folder named OmniSciPBIC into the installation package and open it.

It should contain multiple files.

Copy the OmniSciPBIC.pqx file into the folder C:\Users\[username]\Documents\Power BI Desktop\Custom Connectors *

* If the Power BI Desktop\Custom Connectors folders don't exist, create them.

Security configuration for the Custom Connector

The OmniSciPBIC.pqx file is signed with a certificate. Still, for the signature to be trusted, the thumbprint of the signing certificate must be in the Trusted Certificate Thumbprints list of PowerBI in the machine.

The Custom Connector can also be used without being trusted, but using PowerBI Desktop with a trusted certificate is a good practice.

Using the Custom Connector as a trusted data source (recommended)

 
All you have toi to do is opening the Regedit, navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft and, if not present, create a new Key called Power BI Desktop.

To add a new TrustedCertificateThumbprints Multi/String Value under Power BI Desktop:

Open the registry editor and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Power BI Desktop, then Right-click an empty space and select New > Multi-String Value.

Name it TrustedCertificateThumbprints,then add the content of thumbprint.dat file in OmniSciPBIC folder as Value data and press OK as shown in the picture

Note: A warning will appear, stating that this object cannot contain empty strings. Press OK to ignore it.
then press OK.

To ensure that everything is functioning properly, open PowerBI Desktop and select Get Data from Another Source. Look for OmniSciPBIC.

Using the Custom Connector as a non-certified source (not recommended)

If, for whatever reason, the thumbprint can't be added to the registry, you can use the Connector as non-certified; but this implies lowering the security settings of the PowerBI Desktop.

Open the PowerBI Desktop and press ok to close the modal windows that warn the user about an uncertified connector's presence.

Navigate the menu FILE->Option and Settings->Options

Select Allow any extension to load with validation or warning in the Security section, then press OK.

Restarting the PowerBI Desktop, the Warning windows should be gone, and the Custom Datasource will be in the data source lists.

Connect to a HeavyDB data source and then create a simple dashboard.

To check that everything works, open PowerBI Desktop, select Get Data from another source and then look for the OmniSciPBIC drive.

Enter the name of the ODBC data source created in the first step, select DirectQuery*, and click on the Ok button to connect.

*The DirectQuery mode will use the HeavyDB Engine on live real-time data

Select the default database and the FLIGHTS_2008_7M table in the dialog box, then click the load button.

Add a simple bar chart using CarrierName on both Axis to get a count of the number of flights for each Carrier.