Denodo Power BI custom connector – Benutzerhandbuch (2023)

Last changed at: 08.04.2022

Download original document

Microsoft Power BIis a set of business analytics tools for analyzing data and sharing information.Microsoft Power BI workspaceis part of these tools and allows you to create advanced queries, models and reports that visualize data and can be easily shared with others. Power BI Desktop combines data from disparate databases, files, and web services with visual tools that help you automatically understand and fix data format and quality issues.

The Denodo Power BI custom connector allows Power BI Desktop users to connect to and access data from the Denodo platform, allowing users to easily query, configure and combine data from their Denodo platform databases to create reports and dashboards to create one that meets the needs of your business. It also allows Power BI to access Denodo data inDirectQueryMode that reduces the need to preload data.

Installing the Denodo ODBC driver

Because the Denodo Power BI Custom Connector is an ODBC-based connector, you must install the Denodo ODBC driver on the computer that configures and publishes an ODBC DSN for use by Power BI. To install the driver, go to<DENODE_HOME>\tools\client-drivers\odbcfolder where you can find two.Postal codeFiles:

• DenodoODBC_x86.zipcontains the ODBC driver for 32-bit clients.
• DenodoODBC_x64.zipcontains the ODBC driver for 64-bit clients.

Unzip and run the appropriate driver version based on the version of Microsoft Power BI Desktop you want to use.msiextracted file. A simple installation wizard guides you through the process.

Create the DSN

After installing the Denodo ODBC driver (see theDenodo ODBC driver installation section), you need to add a new ODBC data source (DSN). On Windows systems:

1. Go toControl Panel > Administrative Tools > Data Sources (ODBC).

2. SelectAdd User DSNÖAdd System DSN. the difference is thatUser DSNcan only be used by the current user andDSN do systemsIt can be used by all system users.

3. ChooseDenodoODBC-UnicodeConductorand clickThe endKnopf.

Enter the following information in the configuration dialog:

• Data Source(necessary): Name of the ODBC data source (e.g. Power BI - Denodo).

• Database(necessary): Database in the Denodo Virtual DataPort.

• ServerjPorto(necessary): Hostname and port of the server running Virtual DataPort. The default ODBC port is 9996.

• user password: Credentials to connect to Denodo. These fields can be empty because the Denodo Power BI Custom Connector requests authentication data from Power BI.

For more information on configuring a DSN, see the Denodo platform documentation.

Create a Denodo compatible connection string

When creating a connection string compatible with Denodo, we must take into account that the Driver field must be omitted, as it is set transparently by the connector itself at connection time.

The connection string has three mandatory parameters: SERVER, PORT, and DATABASE:

Server=<server name>;DATABASE=<database name>;PORTA=<port number>

In addition, it can contain an optional parameter: **SSLmode**:

Server=<server name>;DATABASE=<database name>;PORT=<port number>;SSLmode=<SSL mode>

When writing the connection string, keep the following in mind:

1. The connection string must maintain the correct order of its parameters: SERVER, PORT, DATABASE, and SSLMode.
2. The name of these parameters must always be written in the same way. For example, if you choose to capitalize them, they must always be capitalized; If you choose to capitalize them (by typing the first letter of a word in upper case and the remaining letters in lower case), they must always be written that way.

Authentication parameters must be omitted as authentication will be configured in later steps.

Choosing a custom Denodo Power BI connector

Certified denode connector

The custom connector for Denodo Power BI has been certified by Microsoft since the October 2018 release, so you can use the Denodo connector included with Microsoft Power BI. Using this certified Denodo Connector is the recommended option unless you are using a version of Microsoft Power BI older than the October 2018 release, or you wish to benefit from a patch or fix for the Denodo Power BI Custom Connector (see theDenodoConnect Components section) that has not yet been released for Microsoft Power BI or the on-premises data gateway.

Component DenodoConnect

The Denodo Power BI custom connector is also available onDenodoConnects sectionby DenodoSupport-Website. You can download this component and follow the steps inMicrosoft Power BI-Desktop-SetupjInstalling the custom Denodo Power BI connector sectionsto use this Denodo connector if your Microsoft Power BI does not include the Denodo Power BI custom connector (versions prior to October 2018) or if the version of the Denodo Power BI custom connector available on the support site provides a patch or fix , which is not yet available Published to Microsoft Power BI or an on-premises data gateway.

Note that installing the Denodo Power BI custom connector is a temporary solution until the required version is released to Microsoft Power BI or the on-premises data gateway. Once the Denodo Certified Connector preinstalled in Microsoft Power BI has been updated to the required version, you must uninstall the custom Denodo Power BI Connector (see theUninstall the Denodo Power BI Custom Connector section).

Microsoft Power BI-Desktop-Setup

If you need to use a different version of the Denodo connector than the one that comes preinstalled in Microsoft Power BI Desktop, you need to lower the security level of the extensions in Power BI Desktop to allow unsigned or uncertified connectors to load:

1. Go toFile > Options and Settings > Options.

2. go tosecurityEyelash.

3. Underdata extensions, SelectAllow any extension to load without warning or validation.

4. Restart a Power BI Desktop.

Installing the custom Denodo Power BI connector

You should skip this step if you are using the Denodo certified connector included with Power BI Desktop. Otherwise, after installing the correct driver version and configuring Microsoft Power BI Desktop, you need to install the custom Denodo Power BI Connector.

The distribution consists of:

• ADistancefolder with the.AverageBinary file for the plugin.

• AdocumentFolder with this user manual.

• README, NOTICE, and RELEASE NOTES files containing information about the Denodo Power BI custom connector.

To use the Denodo Power BI custom connector in Power BI Desktop, you must have the.Averagefile in:

C:\Users\[username]\Documents\Power BI Desktop\Custom Connectors

You will likely need to create the last two folders in this path, so make sure you create them exactly as described above.

Uninstalling the custom Denodo Power BI connector

If the certified Denodo connector version included with Power BI contains the required fixes or updates, uninstall the custom Denodo Power BI connector. To do this, delete the.AverageFile you copied to:

C:\Users\[username]\Documents\Power BI Desktop\Custom Connectors

With the on-premises data gateway, the[username]is the user running the gateway serviceNT-SERVICE\PBIegwServiceBy default.

If you are using a non-certified Denodo connector, after installing the Denodo Power BI custom connector, you must launch or restart Power BI Desktop before you can work with it.

To connect to the data, select theget informationon the Home ribbon and selectDenodonoDatabaseSection.

There are two ways to connect to the data source of your choice:

• Via DSN (ODBC data source name)
• Use a connection string

A new window will ask for the DSN (see theCreating the DSN section) or a connection string (seeCreate a Denodo compatible connection string), the option to enable debug mode and connectivity mode, which can beimportÖDirectQuery.

HimActivate debug modeis an optional field that allows you to add tracing information to log files. Power BI Desktop creates these files when you enable in-app tracing usingDiagnoseinstructions continueoptionsMenu. Note that the default value forActivate debug modeis false and in this scenario there is no trace data from the Denodo Power BI Custom Connector in the log files.

In the next step, before the browser window is displayed that shows a preview of the data available in the Denodo Virtual DataPort, the authentication data is requested. The Denodo Power BI Custom Connector supports two types of authentication:windowjBasic.

Windows authentication

If you choose to use Windows authentication, Power BI Desktop connects to the virtual DataPort using Kerberos authentication.

Is required:

• Kerberos authentication must be enabled on the Virtual DataPort server.

• The Denodo Virtual DataPort database that the data source connects to must be configured with the optionODBC/ADO.net authentication typeadapted toKerberos.

• Power BI Desktop must run in the Windows domain because the ODBC driver requests the Kerberos ticket from the operating system's ticket cache.

• Ensure that the **Advanced Options** page of the DSN settings contains all the necessary settings to use Kerberos as the authentication method:

For more information, see the DSN setup process in the Denodo Platform documentation.

basic authentication

This type of authentication allows you to connect Power BI Desktop to Virtual DataPort data using Virtual DataPort server credentials.

Using DirectQuery

Note that when using DirectQuery mode to access data, changes to the underlying data are not necessarily immediately reflected in views. Reports or dashboards refresh (or refresh explicitly) when opened, otherwise Power BI retains the newly requested data for some time (typically 15 minutes per tile).

For more information, see the links below:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-directquery-about/

Using an on-premises data gateway

Himlokales Datengateway(Enterprise Gateway) acts as a bridge, providing fast and secure data transfer between on-premises data (data that resides in your Power BI Desktop app, not in the cloud) and thePower BI Service, also known as Power BI online,app.powerbi.com oPower BI cloud. Therefore, when creating reports in Power BI Desktop that use the Denodo Power BI custom connector, you can use the on-premises data gateway to publish and update those reports from the Power BI service. Note that publishing a report also imports its dataset, so you can use it from the Power BI service to create new reports as well.

To enable and use this feature, you must install the August 2018 version oflokales Datengatewayor a later version.

After installation you need to login and register your gateway. In the on-premises data gateway configurator, select theConditionOn the tab you can verify that your gateway is online and ready to use.

You can also see oneconnectionswith an option to select a folder to load custom connectors from in case you want to use a different version of the Denodo connector than the one included with Microsoft Power BI. Make sure you choose a folder that can be accessed by the user running the gateway serviceNT-SERVICE\PBIegwServiceBy default. The gateway will automatically load the custom connector files located in this folder and you should see them in the data connectors list. Therefore, to enable connectivity through Microsoft On-Premises Data Gateway to a custom Denodo Power BI connector other than the one that Microsoft Power BI includes, you must use the.AverageFile in the folder specified in this step (C:\Users\PBIEgwService\Documents\Power BI Desktop\Custom ConnectorsBy default). Note that adding or changing a connector requires you to restart the on-premises data gateway.

After configuring the on-premises data gateway, you need to create a data source for the Power BI Denodo custom connector in the Power BI service using the gateway configuration page. Since the ability to connect to the Power BI service through the gateway was introduced as a "preview" feature in August 2018, you may need to validate using the custom connector depending on your on-premises data gateway or Power BI service version activate. possibilityAllow custom user data connectors to be updated through this gateway cluster.

Now you see themdenode connectoras an available data source that you can create on this gateway cluster.

With the goal to createadata source you need to specify how to connect to the data source of your choice:

• By DSN (cfCreating the DSN section)
• With a connection string (cfCreate a Denodo compatible connection string)

You must also specify the authentication mode.

The available authentication methods areBasicjwindow, as well as when using the connector in Power BI Desktop.

basic authentication

This type of authentication allows you to create a data source in the Power BI service to connect to Virtual DataPort data using Virtual DataPort server credentials.

Windows authentication

If you choose to use Windows authentication, the Power BI service connects to the virtual DataPort using Kerberos authentication.

You need:

• In the data source configuration, enter the username and password to create the Kerberos ticket.
• Kerberos authentication enabled on the Virtual DataPort server.
• The Denode Virtual DataPort database that the DSN connects to must be configured with the optionODBC/ADO.net authentication typeadapted toKerberos.
• In the advanced options of the DSN settings, consider the type of authentication you want to use:

For more information, see the DSN setup process in the Denodo Platform documentation.

Note that the gateway runs under a service account.NT-SERVICE\PBIegwServiceTherefore, by default, the DSN must either be a system DSN or be defined by the user running the gateway.

Single sign-on (SSO)

If you are using Windows authentication, you can (under theAdvanced settingsfrom the data source) Single Sign-On (SSO) authentication scheme to use the same credentials as the user accessing your reports in Power BI to access the required data in Denodo.

There are two options for enabling SSO:Use SSO over Kerberos for DirectQuery queriesjUse SSO over Kerberos for DirectQuery and import queries. When working with DirectQuery-based reports, both use the SSO credentials of the user logging into the Power BI service. The difference arises when you work with import-based reports: In this scenario, the first option uses the credentials provided on the data source page (usernamejpasswordfields), while the latter uses the credentials of the dataset owner.

It's important to note that you must meet certain prerequisites and considerations in order to use Kerberos-based SSO. Some of these important requirements are:

• Kerberos constrained delegation must be enabled for the Windows user running the Microsoft Power BI Gateway and the on-premises Active Directory and Azure Active Directory environments must be configured according to Microsoft's instructions for this purpose.
• By default, Microsoft Power BI Gateway sends themain username(UPN) when performing an SSO authentication. Therefore, you need to check the attribute you use as the login identifier in Denodo Kerberos authentication and whether it differsuserPrincipalName, adjust the gateway settings accordingly.

The Microsoft Power BI gateway configuration file namedMicrosoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config, stored in\Programme\Local Data Gatewayhas two properties namedADUserNameLookupPropertyjADUserNameReplacementPropertywhich enable the gateway to perform on-premises Azure AD lookups at runtime. HimADUserNameLookupPropertyYou need to specify which attribute of local AD this ismain usernamewhich comes from Azure AD, so in this scenarioADUserNameLookupPropertyShould beuserPrincipalName. Once the user is found, theADUserNameReplacementPropertyThe value specifies the attribute to use to authenticate the impersonated user (the attribute you use as the Denodo login ID).

Note that changes to this configuration file are made at the gateway level and therefore affect all sources with which SSO authentication is performed through the Microsoft Power BI gateway.

please check theConfigure Kerberos-based SSO from Power BI service to on-premises data sourcesDocumentation for more information.

Once a data source for Denodo Connector has been created, you can update Power BI reports. To publish a report on powerbi.com you need:

1. From the File menu, choose: Publish > Publish to Power BI
2. Save the report to your computer.
3. Select the workspace you want to publish to.

Most of the limitations discussed in this section apply only toDirectQuery access modeand cause the Power BI visualizations to display an error like the following:

Filter if the view contains only one column

Microsoft Power BI Desktop lets you create a single-column visualization. In this scenario, if you try to apply a filter that requires the result to be a single row, the query fails.

Filtering with the filter type "Top N" at N = 1

CIf the value of N is 1 using the Top N filter type, the query fails.

Date data type scenario

The report view in Power BI uses the reporting engineMicrosoft Analysis Services-Tabellenmodellewhich does not support time zones or for at the time of writing this articleMEETÖHOURsqldata type. This means that date types loaded into Power BI either via import mode or DirectQuery are rendered as date and time types. However, the query mechanismdefines the records for use in reportsin Power BI it isenergy consultingand this mechanism supports Date, Time, DateTime and DateTimeZone date types. Both engines, Microsoft Analysis Services and Power Query tabular models, work perfectly with Power BI, but due to the characteristics and limitations of each engine, there may be operations that you perform with a specific one. Therefore, consider using the Query Editor to apply filters to date data types that are not specifically supported by the Microsoft Analysis Services tabular model engine used by the Report View. After applying the filter, you can view and manage the data as usual in the report view.

Also note that ODBC does not support some SQL-92 data types, including theHORA_WITH_TIMEZONEjTIMESTAMP_WITH_TIMEZONETypes So values ​​with date types that have time zone information are loaded via Denodo ODBC driver as in Power BITIME STAMPData types, no time zone.

Also, starting with Denodo Platform 7.0 on versions before update 20190312, if you try to use a column with , you will get an error in the report viewTIMESTAMPData type in a report because it is processed as TIMESTAMP, because of the ODBC driver limitation discussed above, and because Virtual DataPort in versions prior to the above update did not allow an implicit conversion betweenTIMESTAMP/TIMESPTAMPTZdata type. However, because Microsoft Analysis Services tabular models also don't support DateTimeZone types, you can use the Power Query Editor to apply transformations to your data to put your information into types that Microsoft Analysis Services tabular models understand. For example, you can turn your DateTimeZone column into multiple columns, each containing a date component: year, month, day, hour, minute, and second.

Starting with Denodo Platform 7.0 update 20190312, implicit conversions between typesLOCATE/HOUR/TIME STAMPjTIMESTAMPthey aresupports. This means that if you try to use a column withTIMESTAMPdata type in a report, the error no longer occurs. However, given the limitation of ODBC and the processing of data by the two Power BI engines discussed above, columns with time zone information in Denodo are always rendered asTIME STAMPTypes in Power BI and this can lead to unexpected effects because an implicit conversion has to be done when dealing with these column types.

Loading TEXT types from Microsoft SQL Server

TEXTMicrosoft SQL Server types allow a very limited set of operators, they cannot be compared or ordered except when usingis zeroÖIFOperator. Also, Power BI Desktop doesn't allow modifying the lookup property through the custom connector to try to accommodate this limitation. Therefore, this scenario throws an error when trying to add this value to a view.