Microsoft Power BI is a suite of business analytics tools for analyzing data and sharing insights. Microsoft Power BI Desktop is part of these tools and it lets you build advanced queries, models and reports that visualize data and can easily be shared with others. Power BI Desktop combines data from disparate databases, files, and web services with visual tools that help you understand and fix data quality and formatting issues automatically.
The Denodo Power BI Custom Connector allows users of Power BI Desktop to connect to and access data at the Denodo Platform making it easy for users to query, shape and mashup data from your Denodo Platform databases to build reports and dashboards that meet the needs of your organization. It also enables Power BI to access Denodo data in DirectQuery mode, which reduces the needs to preload the data.
Given that Denodo Power BI Custom Connector is an ODBC based connector, it is necessary to install the Denodo ODBC driver in the machine that will be configuring and publishing an ODBC DSN for Power BI to use. In order to install the driver, go to the <DENODO_HOME>\tools\client-drivers\odbc folder, where you can find two .zip files:
Unzip the appropriate driver version depending on the Microsoft Power BI Desktop version that you are going to use and then execute the .msi file extracted. A simple installation wizard will guide you through the process.
Once you have installed the Denodo ODBC driver (see the Installing the Denodo ODBC driver section) you will need to add a new ODBC data source (DSN). On Windows systems:
In the configuration dialog fill in the following information:
In order to get more information regarding the setup of a DSN, check the Denodo Platform documentation.
Denodo Power BI Custom Connector is certified by Microsoft since the October 2018 release, so you can use the Denodo connector included in Microsoft Power BI. However, if you wish to use a different version you will need to lower the security level for extensions in Power BI Desktop to enable loading unsigned/uncertified connectors:
You should skip this step if you are going to use the certified connector for Denodo included in Power BI Desktop. Otherwise, once you have installed the correct driver version and configured Microsoft Power BI Desktop, you must install the Denodo Power BI Custom Connector.
The distribution consists of:
In order to use the Denodo Power BI Custom Connector in Power BI Desktop, you must copy the .mez file into:
C:\Users\[userName]\Documents\Power BI Desktop\Custom Connectors
You will probably have to create the last two folders in this path, therefore, make sure you create them exactly as spelled above.
Whether you are going to use a non-certified Denodo connector, after installing the Denodo Power BI Custom Connector you have to start, or restart, Power BI Desktop before beginning to work with it.
In order to connect to data, select Get Data icon from the Home ribbon and select Denodo in the Database section.
A new window will ask you for the DSN (ODBC data source name) that you have configured (see the Creating the DSN section), the option to enable the debug mode and the connectivity mode that can be Import or DirectQuery.
The Enable debug mode is an optional field that allows you to add trace information to log files. These files are created by Power BI Desktop when you enable tracing in the application using the Diagnostics tab in the Options menu. Note that the default value for Enable debug mode is false and in this scenario there will be no trace data, from Denodo Power BI Custom Connector, in the log files.
The next step, before showing the Navigator window that displays a preview of the available data in Denodo Virtual DataPort, will request authentication data. The Denodo Power BI Custom Connector supports two authentications types: Windows and Basic.
When you decide to use Windows authentication, Power BI Desktop is going to connect to Virtual DataPort using Kerberos authentication.
For more information, check the setting up process of a DSN in the Denodo Platform documentation.
This authentication type allows you to connect Power BI Desktop to your Virtual DataPort data using your Virtual DataPort server credentials.
Note that, using the DirectQuery mode for accessing data, changes to the underlying data are not necessarily reflected in the visualizations immediately. Reports or dashboards are refreshed when open (or explicitly refreshed), but otherwise Power BI does keep recently requested data for some time (usually 15 minutes per tile).
See the links below for more information:
The On-premises data gateway (Enterprise gateway) acts as a bridge, providing quick and secure data transfer between on-premises data (data that is in your Power BI Desktop application, not in the cloud) and the Power BI Service, also known as Power BI online, app.powerbi.com or Power BI Cloud. Hence, when you build reports in Power BI Desktop that use Denodo Power BI Custom Connector, you can use the On-premises data gateway to publish and refresh those reports from the Power BI Service. Note that when you publish a report, the associated dataset is also imported, so you can also use it from Power BI Service in order to create new reports.
To enable and use this capability you have to install the August 2018 version of the On-premises data gateway or a later version.
After the installation, you have to sign in and register your gateway. In the On-premises data gateway configurator, selecting the Status tab will allow you to verify that your gateway is online and ready to be used.
Additionally, you can see a Connectors tab with an option to choose a folder to load the custom connectors from in case you want to use a version of the Denodo connector other than the one included in Microsoft Power BI. Make sure you pick a folder that can be accessed by the user running the gateway service which is NT SERVICE\PBIEgwService by default. The gateway will automatically load the custom connector files located in that folder and you should see them in the list of the data connectors. Therefore, in order to allow connectivity via the Microsoft On-Premises Data Gateway using a Denodo Power BI Custom Connector, different to the one incorporated in Microsoft Power BI, you have to copy the .mez file into the folder specified in this step (C:\Users\PBIEgwService\Documents\Power BI Desktop\Custom Connectors by default). Note that when adding or changing a connector you must restart the On-Premises Data Gateway.
After configuring the On-premises data gateway, you need to create a data source for the Denodo Power BI Custom Connector in the Power BI Service using the gateway settings page. Since the ability to connect Power BI Service via the Gateway was introduced in August 2018 as a "Preview" feature, depending on the version of your On-premise data gateway or Power BI Service, you may need to enable the use of custom connectors checking the option Allow user’s custom data connectors to refresh through this gateway cluster.
Now, you will see the Denodo Connector as available data source that you can create under this gateway cluster.
In order to create the data source, you have to specify the DSN that you have configured (see the Creating the DSN section) and the authentication method.
The authentication methods available are Basic and Windows, just as when using the connector in Power BI Desktop.
This authentication type allows you to create a Data Source in Power BI Service to connect to your Virtual DataPort data using your Virtual DataPort server credentials.
When you decide to use Windows authentication, Power BI Service is going to connect to Virtual DataPort using Kerberos authentication.
For more information, check the setting up process of a DSN in the Denodo Platform documentation.
Note that the gateway runs in a service account, NT SERVICE\PBIEgwService by default, so the DSN must be a System DSN or must be defined by the user who runs the gateway.
Once a data source is created for the Denodo Connector, you can refresh Power BI reports using the custom connector in the Power BI Service.
Most of the limitations explained in this section apply only to the DirectQuery access mode, and make Power BI visualizations show an error like the following:
Filtering when there is only one column in the visualization
Microsoft Power BI Desktop allows you to create a visualization with only one column. In this scenario, whether you try to apply a filter where the result should be a single row, the query will fail.
Filtering using “Top N” filter type when N = 1
When the value of N is 1 using “Top N” filter type, the query will fail.
Scenario on date data types
The Report View in Power BI uses the engine from Microsoft Analysis Services' Tabular Models which, at the time of this writing, does not have support for time zones, nor for DATE or TIME SQL data types. It means that when date types are loaded in Power BI, via Import or DirectQuery mode, they will be represented as Date/Time types. However, the query engine that defines data sets for their use in reports in Power BI is Power Query and this engine supports Date, Time, DateTime and DateTimeZone date types. Both engines, Microsoft Analysis Services' Tabular Models and Power Query, work in an integrated way in Power BI but due to the characteristics and limitations of each one of them, there may be operations that you will carry out with a specific one. Accordingly, consider using the Query Editor to apply filters on date data types that do not have specific support in the Microsoft Analysis Services' Tabular Models engine used by the Report View. After applying the filter you can visualize and handle the data as usual in the Report View.
Furthermore, note that ODBC does not support some SQL-92 data types, including the TIME_WITH_TIMEZONE and TIMESTAMP_WITH_TIMEZONE types. Therefore, values with date types that have time zone information will be loaded in Power BI through the Denodo ODBC driver as TIMESTAMP data types, without time zone.
In addition, from Denodo Platform 7.0 in versions prior to update 20190312, you will get an error in the Report View if you try to use a column with TIMESTAMPTZ data type in a report because it is processed as a TIMESTAMP, due to the limitation of the ODBC driver explained above, and the fact that Virtual DataPort in versions previous to the mentioned update did not allow an implicit cast between TIMESTAMP/TIMESPTAMPTZ data types. Nevertheless, bearing in mind that Microsoft Analysis Services' Tabular Models does not support DateTimeZone types either, you can use the Power Query Editor to apply transformations in your data in order to get its information in types understandable by Microsoft Analysis Services' Tabular Models. For instance, you can transform your DateTimeZone column into several columns where each of them will contain a component of the date: year, month, day, hour, minute and second.
From Denodo Platform 7.0 update 20190312 onwards implicit casts between types LOCALDATE/TIME/TIMESTAMP and TIMESTAMPTZ are supported. This means that if you try to use a column with TIMESTAMPTZ data type in a report, the error is no longer thrown. However, keeping in mind the ODBC limitation and the date handling by the two Power BI engines explained above, columns with time zone information in Denodo are always represented as TIMESTAMP types in Power BI and this may cause unexpected effects due to the need to perform implicit cast when dealing with this type of columns.
Loading TEXT types from Microsoft SQL Server
TEXT types from Microsoft SQL Server allow a very limited number of operators, they cannot be compared or sorted, except when using the IS NULL or LIKE operator. Moreover, Power BI Desktop, through the custom connector, does not allow changing the searchable property with the aim of trying to take into account this limitation. Therefore, this scenario causes an error when you try to add a value of this type in a visualization.