Introduction

Power BI is a business intelligence platform developed by Microsoft. It features a collection of software services, apps, and connectors that provide data visualization tools and insights.

The connection between Power BI and Denodo Platform is usually made through the use of a DSN and Dendo’s ODBC driver. Even though the ODBC driver is always required, after a Power BI update released in May 2022, it is possible now to connect through the use of a Connection String, a connection that before could only be achieved by using the Denodo Custom Connector.

This document will highlight the steps to connect to Microsoft Power BI Desktop using both a DSN connection as well a DSN-less connection through the use of a Connection String. We will also showcase a workaround for those having issues with input parameters with Denodo and Power BI.

Pre-Installation

For the instructions presented in this document to work, there are a few steps that should be taken prior to the start of this tutorial:

• The ODBC driver should be installed in the machine where you are going to use Power BI Desktop. If you do not have it installed, go to the folder  <DENODO_HOME>\tools\client-drivers\odbc of your Denodo installation, where you will find a .zip file with the ODBC driver distribution for Windows: denodo-vdp-odbcdriver-windows.zip.

• Unzip the driver and execute the .msi file extracted that fits your system architecture. An installation wizard will show up with guided instructions.

• Make sure you have installed the latest version of Microsoft Power BI Desktop, this should be the update from May 2022 onwards.

Power BI and Denodo

DSN Connection

Users can connect to their databases in Denodo from Power BI, through the use of a DSN. This can be easily achieved since Denodo and Microsoft have partnered to bring the Denodo Connector into the Power BI platform.

Therefore, in order to connect from Power BI to Denodo, the connection is straightforward:

NOTE: Bear in mind that this tutorial assumes you have created your DSN connection already. If you have not done so yet, you can find how to do it through this Denodo Documentation on Creating a DSN.

1. Open Power BI Desktop and navigate to “Get Data”.

2. A pop-up window will appear with a search bar, in which you should search for “Denodo”. Once the connector is highlighted on the right side, you can click on it and select “Connect”.

3. On the following screen, you will need to insert your DSN name and click “OK”.  Following this screen you will be prompted with the Login Credentials for your database. You have connected to your Database in Denodo Platform through Microsoft Power BI.

Connection String (DSN-less Connection)

Sometimes, a DSN connection might be enough to connect, however in some cases, depending on the user needs (e.g. whereas you need to manually provide an access token for OAuth Authentication, or for business users that do not have administrator privileges to access the DSN and need to connect) the use of a Connection String might be the best approach.

If you are a Denodo and Power BI Desktop user looking to create a DSN-less Connection between these two platforms, the steps got simpler as Power BI has made that option available directly on their platform.

All you need to do is make sure all the pre-installation steps above were achieved and then you can head to Power BI Desktop and follow the steps below.

1. Open Power BI Desktop and once prompted through their initial screen, navigate to “Get Data”.

2. On the second screen, you should search “Denodo”. Once the connector is highlighted on the right side, you can click on it and select “Connect” at the bottom of the window.

3. Now, a new window will show up titled Denodo Connector. Notice that what before was only “DSN” is now “DSN or Connection String” which means we can simply use any of these to achieve this connection.
   You should now insert the Connection String in the first field, using the following pattern:
   

SERVER=<hostname>;DATABASE=<dbname>;PORT=9996

After inserting the Connection String, you should press “OK”.

4. If your Connection String was correct, you should see the following window where you are prompted to insert your login credentials. Finally, after connecting, you will be able to use Power BI with Denodo!

Querying Views with Input Parameters through the Denodo Connector

If you are experiencing an issue like the one above (No search methods ready to be run. The following fields are obligatory) when trying to query a view with mandatory input parameters from Denodo in Power BI using the Denodo Connector, you can use the following steps to get this sorted.

From Power BI, right-click on the query you are having issues with and select the option to modify the query: Advanced Editor.

In the editor, you should make sure you add these:

Value.NativeQuery and [EnableFolding=true] just like the example below shows:

Example 1: Old Version

Example 2: Updated Version

You can see that the following line where the queried view is just referenced:

has been replaced with a line where the actual select statement is provided and the input parameter needed is included in the query:

After updating the values, the query with input parameters should work. Have in mind that for this to work, you will have to have Power BI Desktop - May 2022 Release update or later.

References

Denodo Power BI Custom Connector - User Manual

Power BI May 2022 Feature Summary