mariadb-corporation / mariadb-powerbi

Power BI DirectQuery Connector
https://mariadb.com
3 stars 1 forks source link

Unable to configure scheduled refresh of published dataset, unsupported data source #16

Closed polandaj closed 3 years ago

polandaj commented 3 years ago

I used the MariaDB connector in PowerBI Desktop (Aug 2021 version) to connect to a data source and save a .pbix file on a server. I then published that file as a dataset in our Microsoft PowerBI Cloud Premium Workspace. We have the PowerBI Gateway installed on our server for use with our previous MySQL data source, where we could configure the Gateway connection, then schedule automated refreshes of the published PBI dataset.

When we converted from MySQL to MariaDB and attempted the same process we ran into a snag. After publishing the MariaDB dataset to the PBI Cloud Workspace, we are unable to configure the Gateway Connection and setup a scheduled refresh of the dataset.

Do you know if published .pbix files with the 'MariaDB.Contents' connection string are compatible with the PowerBI Gateway? In order to configure the gateway connection and schedule refreshes of the data? I'm trying to determine if our Gateway servers need to have a config enabled, or if MariaDB is not supported by the PowerBI Gateway at all.

Refresh Error: Data source error: Unable to refresh the model (id=697226) because it references an unsupported data source. Cluster URI: WABI-US-NORTH-CENTRAL-E-PRIMARY-redirect.analysis.windows.net Activity ID: 9e19bded-5df1-4386-9c8d Request ID: ad8dd51b-44a8-eaa6-8a3a Time: 2021-09-29 21:23:17Z

PowerBI Workspace Message: Unable to determine the data source due to unknown functions. When custom connectors are used, the error can happen if gateway doesn't have the extension enabled. Details: Static analysis failed in gateway. gatewayObjectId:3ea6e53f-b2f1-48e3-a163-c913c3e, resultCode:FailedWithUnknownFunctions Query contains unknown function name: MariaDB.Contents . Data source for Query1

ilyagithub1 commented 3 years ago

@polandaj Yes, 'MariaDB.Contents' is compatible with Power BI Gateway.

Here is the full list of the supported Power BI features by data source: https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources#data-sources-m-o

Please check the following.

Check: Are you running the latest version of Power BI Gateway?

The latest Gateway version number is 3000.96.1 (September 2021).

Issue-16-PowerBI-Gateway-version

Check: Is your MariaDB Gateway Data Source configured correctly in Power BI Online?

  1. In Power BI Online, click the gearbox menu item in the upper right corner, then the menu item Manage Gateways. This should bring you to the page that lists all the connected gateways and configured data sources - see the screenshot below.

Please make sure that the Data Source Type field is set to MariaDB data source as in the screenshot.

Remember to click the Test all connections button to make sure the MariaDB data source behind the Gateway is accessible.

Issue-16-PowerBI-Gateway-configuration-in-PBIOnline

  1. In Power BI Online, navigate to the Dataset Settings page for your MariaDB dataset and in the Gateway connection section verify that it is mapped to the MariaDB Gateway Data Source - see the screenshot below.

Issue-16-PowerBI-Gateway-configuration-in-PBIOnline-Dataset-mapping


It's all configured correctly, but it does not work

Firstly, make sure it is indeed configured correctly in Power BI Online as described and demonstrated in the screenshots above.

If the configuration is right, the originally reported error should never manifest:

unknown function name: MariaDB.Contents

If this error message is still shown, please make the above checks again.

If the configuration looks good then it is possible that a more generic issue might be there. This is more likely to happen if the Gateway Data Source was created using an earlier version of Power BI Gateway, or maybe using a MEZ file manually copied to the "Custom Connectors" folder. The latest version of Power BI Gateway includes MariaDB Connector, and copying MariaDB MEZ file to the "Custom Connectors" folder is not necessary anymore.

In any case, re-creating the Gateway Data Source in Power BI Online (Settings > Manage Gateways > Data Sources) helps most of the time:

  1. Delete the Gateway Data Source for the MariaDB Data source type.
  2. Create a new Gateway Data Source for the MariaDB Data source type.
  3. Map the new Gateway Data Source to the MariaDB dataset in Power BI Online.