exasol / powerbi-exasol

Microsoft Power BI Connector

Power BI - Exasol Connector

The Exasol Microsoft Power BI Connector enables you to connect from Power BI Desktop to Exasol in Direct Query mode (classical import mode is supported as well).

The Exasol Microsoft Power BI Connector is a Certified Custom Connector now and is also shipped with Power BI desktop and the on premise data gateway. The powerbi-exasol repository will contain the latest release which might contain modifications not yet included in the stable version.

Prerequisites

• Install the latest Exasol ODBC driver (EXASOL_ODBC_*.msi) (if you aren't sure on whether you need the 32 Bit or 64 Bit version install both versions)
• Install the Visual C++ Redistributable
• Install the current version of Power BI desktop

Official Updates

❗ The Power BI Release 2.66.5376.1681 64-bit (February 2019) contains all recent fixes of the EXASOL connector regarding unicode.

❗ A new version of the Exasol connector is scheduled for bundled release with PowerBI. This new version of the connector comes bundled with the September 2021 release of Power BI Desktop scheduled the week of 9/13. The main new feature is DSN support. For a brief overview on how to use DSNs see the user guide.

Usage

1. Choose Get Data -> Database -> Exasol

2. Enter the Exasol connection string and choose Direct Query

3. Enter credentials

4. Select tables

5. Review relational model, it's recommended to have foreign keys set in the database so Power BI can autodetect the relations

6. Build fast dashboards on billions of rows in Exasol

Using DSNs and Kerberos authentication

Please see the user guide

How To use the Exasol Power BI Connector with the On-premises data gateway

The Exasol connector was tested successfully with the On-premises data gateway version 3000.8.452 (August 2019) and also supports Direct Query.

❗ As the Exasol Connector is now shipped with the On-premises data gateway it is not necessary to install the Exasol.mez from this repository in the Custom Connectors folder of the Gateway. Actually the data gateway only works with the shipped connector from Microsoft. If you upgrade from older version versions of the On-Premises data gateway please delete the Exasol.mez from the custom connectors folder and restart the gateway service.

Also make sure you install the latest Exasol ODBC driver and Visual C++ Redistributable on the machine/VM where you install the data gateway, just as you would when you intend to use the connector for Power BI Desktop, for more information see section Prerequisites.

Custom queries

Although we could not get this functionality certified and bundled in our connector because of possible security risks it might pose (warning!) we also provide a custom connector that allows you to use custom queries (import mode only), please see the user guide.

Additional information

• Changelog

powerbi-exasol's Issues

Feature: OpenId / personal access token

Use a personal access token for authentication.
Warning: you need at least odbc driver 7.1.0 (experimental at this time) for this.

Tests: write full scope integration tests

09/03 meeting it was decided to go ahead and start writing tests covering the functionality of the exasol power bi adapter

ODBC Driver Log for Exasol power BI connector

Provide a field where users can set the path to a logfile (optional) for the odbc driver to log to.

Connector Submission Feedback Changes

• See if you can support DSNs (instead of the current logging path which is a blocker for certification).
• See if you can easily split up the connector into 2 different parts, an Exasol.Database and Exasol.Query part for the reasons mentioned in the feedback.

Feature: Custom query in Exasol Power BI Connector

Enable end-users to fetch data using a custom query in the exasol Power BI Connector

Odbc Parameters

Allow the end-user to enter additional ODBC parameters e.g.: turn ODBC logs on for debugging purposes.

Licensing information

We need to add a license file.

Errors when using incremental refresh with on premise exasol and power bi service

Hello everyone,
Microsoft support send me here to present my issue.

We are trying to use incremental refresh in out reports which does not work.

Our environment:

• Newest PBI Desktop
• two Exasol clusters (DEV/PRD) currently running on different versions
• PRD is running on version 6.1.4
• DEV is running on version 6.2.3
• On premise data gateway running
• we are using a test-report for each exasol-environment. Both consist of a simple calendar-table
• MS-support has checked on our implementation of the incremental refresh and did not find any errors
• Refreshing within PBI Desktop works
• Our reports are working and refreshing fine after being published to the service if incremental refresh is turned off.
• We updated the Exasol ODBC drivers in our gateway ~3 weeks ago
• Multiple users working on different machines with multiple reports are having this issue

When trying to refresh a dataset with incremental refresh enabled, we get two different errors.
PRD:
There was an error when processing the data in the dataset. Please try again later or contact support. If you contact support, please provide these details. Data source errorUnable to refresh the model (id=3585480) because it references an unsupported data source. Cluster URIWABI-WEST-EUROPE-redirect.analysis.windows.net Activity ID0cd4ba70-fd4d-4555-8093-6782f4ee1d9c Request ID3100f5ea-b72f-8ad7-790c-165e88f65c09 Time2020-06-10 11:14:23Z

DEV:
There was an error when processing the data in the dataset. Please try again later or contact support. If you contact support, please provide these details. Data source error{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"We're sorry, an error occurred during evaluation."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}}],"exceptionCulprit":1}}} Cluster URIWABI-WEST-EUROPE-redirect.analysis.windows.net Activity ID0cd4ba70-fd4d-4555-8093-6782f4ee1d9c Request ID7068c88c-aeef-1b62-f650-75c975d26045 Time2020-06-10 12:11:38Z

According to MS-Support the reason for these errors lies within this connector. Could anyone help us with this issue?

If you need any further information from us, please feel free to ask.

Invalid data type in cast

I connected Exasol in DirectQuery mode. I often get this error.

OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42000] [EXASOL][EXASolution driver]invalid data type in cast

Custom Connector location

Readme says:
"Create a [My Documents]\Microsoft Power BI Desktop\Custom Connectors directory"

Does not work in [My Documents]. Should be from [Program Files] / [Program Files (x86)].

Documentation: Usage of geospatial type in Power BI

Figure out how to get Geospatial type from Exasol DB to Power BI and use it there.

User guide for 1.0.7

For our new pre-release we need to write a user guide.

UI Automation Tests

Provide ui automation tests.

identifier is ambiguous

Hello,

I have been facing this issue wherever I have the same column name in two or more tables. I found that the query doesn't use the alias for one of these columns. Please do check and do the needful.

Error Message:
{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"ODBC: ERROR [42000] [EXASOL][EXASolution driver]identifier City is ambiguous. (Session: 1649888863845149397)"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"Exasol"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"{"server":"**********************","encrypted":"Yes"}"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}}

Still see "beta" on the name of connector in PBI

Hi! Great to know that the Exasol connector is now supported!!!

Please give me some orientation. I have the october (2018) PBI version, I removed the MEZ file from the "custom connectors" folder (I was using the beta version from a while). restarted PBI but still see the "beta" version on my data sources. Any suggestion?
Hugo.

Connect to Websocket

Was wondering whether there will be a way of connecting to Websockets.
This would allow certain situations where Proxies could be used.

Improvements to query.pq file

Go over the query.pq file and improve readability among other things.

Power BI Desktop February update issue

Hello,

one of the clients of my firm would like to set up a direct-query connection between Microsoft Power BI and Exasol data-base.
However, through the latest Power BI version we get the following error:

The import connection works correctly but it's not the desired solution.

The previous Power BI version works perfectly (also the direct-query connection), however by this version you cannot edit the report which were already created by the latest version.
Here the error:

Could anyone support me for this issue please?

Thank you in advanced and best regards,
Lorenzo

Update documentation

Authentication: Kerberos

Connect to a datasource via Kerberos.

Disable HideNativeQuery

This was disabled as part of the certification process.
Some of our customers are inconvenienced by this so we're now going to submit a new version.

System schemas in metadata

The system schemas

• SYS
• EXA_STATISTICS

should be also shown in metadata