Power Query Online + SAP BW Application Server: Exception Error

[Anonymous]

Hi Everyone,

For starter, in order to establish a successfull connection between Power BI Desktop and SAP BW Application Server as the data source, I have installed SAP .Net Connector 3.0 for .NET 4.0 on x64 in my laptop, which I believe that the SAP Connector is Version 3.0.25.  The connection is successful and I could access the data from SAP server on Power BI Desktop.                                                         

Now, I tried to do the same using Power BI Services/Website to connect to SAP BW Application Server as the data source, but this time I also installed and configured the On-premises Data Gateway at the SAP server as stated in Power Query SAP Business Warehouse Application Server connector - Power Query | Microsoft Learn.

The On-Premises Data Gateway has been installed and configured successfully as shown below:

But, when I tried to connect to the data source (SAP BW Application Server) using the On-Premises Data Gateway via Power BI Services/Website, I got the following error:

"An exception occurred: This error could've been the result of provider-specific client software being required, but missing on this computer. To download client software for this provider, visit the following site and choose the 64-bit (x64) version of, at minimum, 'SAP .NET Connector 3.0.0.42': https://go.microsoft.com/fwlink/?linkid=872300".

Are there any other configurations or installations that need to be done? As I have checked that SAP Connector had already been installed in my laptop:

Or do I have to install exactly SAP .NET Connector 3.0.0.42 to resolve the error?

Your assistance and advice is very much appreciated. @slorin @AlienSx @BA_Pete @collinsg @ronrsnfld 

Thank you and Best Regards,

Yacheou

[BA_Pete]

Hi Yacheou,

Assuming you're using separate machines for your gateway(s), you will need to install the SAP Provider on every on-prem gateway machine as well.

If you're trying to use your laptop as a gateway, then I'd recommend changing this setup to having separate, always-on, machines instead. As soon as you switch your laptop off, nothing will refresh in the Service.

Pete

[Anonymous]

I see, so currently my administrator has installed the On-Premises Data Gateway in another machine (let's called it Machine A) which act as the SAP server. So, does that mean I have to install SAP .NET Connector to my laptop and ask my administrator to install SAP .NET Connector on Machine A as well?

[BA_Pete]

Yes. Any and all connection providers need to be installed on every gateway machine that the PBI Service can potentially use to connect to your source. Whether the PBI Service can potentially use a particular gateway to connect to a particular source will depend on how you set up/structure your gateways (distinct task/role-based, clustered etc.).

Pete

[Anonymous]

Understood, I will ask my administrator to install SAP .NET Connector on the server as well, and get back on the results. Thank you for your prompt response @BA_Pete!

[Anonymous]

@BA_Pete Currently, SAP .NET Connector Version 3.0.25 has been installed both on my laptop and on the server/gateway machine. But, I face the same error when I tried to connect to SAP BW Application Server with Power BI Services/Website. May I ask if there is any additional configurations needed? As I have configured both laptop and gateway machine according to Power BI documentation as shown here:  Power Query SAP Business Warehouse Application Server connector - Power Query | Microsoft Learn.

[BA_Pete]

This is a huge question, and likely impossible for me to give a direct answer to. Without being able to see exactly what you're seeing, it's really hard to understand what might be causing you these issues to be honest. A lot will depend on your local tech/infrastructure, so you may need to check things like:

-1- Which provider version does your SAP App Server require? Does it always demand the latest version, or do you have to match the provider version to the DB version?

-2- Do you need to run your exernal connections through a proxy server to get through the firewall? If so, you may need to update the proxy values in the following gateway config files:

--- EnterpriseGatewayConfigurator.exe

--- Microsoft.Mashup.Container.NetFX45.exe

--- Microsoft.PowerBI.EnterpriseGateway.exe

-3- Have you correctly added your App Server as a new source on the gateway(s) in the PBI Service? Have you then updated the dataset of any report using this source with the gateway source?

Sorry I can't be of more help here. I myself have always found gateways the most fiddly and frustrating part of setting up any PBI infrastructure, especially when dealing with external Drivers/Providers. I still have nightmares about an old Oracle server I had to connect to where it turned out the provider would only run on Windows 7, so I had to set up a Win7 gateway just for that source! Brrrrrr 😖

Pete

[Anonymous · Accepted solution]

Thank you very much @BA_Pete ! Finally managed to establish connection between Power BI Services and SAP BW Application Server.

What-to-do:

1. Install and configure Power BI Data Gateway in the SAP server environment.

Power BI Data Gateway: Install an on-premises data gateway | Microsoft Learn

1.a) Make sure you have the access to the Power BI Data Gateway in your Power BI Services.   

Source: Add or remove a gateway data source - Power BI | Microsoft Learn

2. Install SAP .NET Connector in the SAP server environment (Currently using SAP .Net Connector 3.0.25)

SAP Installer: https://support.sap.com/en/product/connectors/msnet.html?anchorId=section

3. Create a Workspace in Power BI Services > Create New Dataflow > Choose SAP BW Application Server

4. Enter the required credentials

5. And it's done.

I appreciate your guidance and support very much @BA_Pete ! Thank you again.