Trying setup On Premise Gateway and SAP Hana

[sareed23]

Hi, 

I have tried reading many documents from Microsoft, blogs from people, and posts in this forum.   I feel I am missing somethign simple, and turning too many hairs gray.   

I have SAP B1 Client on my workstation, I have PowerBI Desktop on my workstation.  I have HDBODBC installed and PowerBi successfully sees the table structure and can make reports.  

I installed the On Premise Data Gateway on my workstation and it says successfull.   I go to apps.powerbi.com and while the portal looks a bit different than online help, I am able to get the the Gateway clusters, I see the one I made on my workstation and click on the add new data source.

I give it a custom name and choose SAP HANA as the source type.   From here I have tried most every combination of the below settings.   In the server, I have tried the the IP of the server, I have tried the name of the server, tried it with port# after too.   In the authentication method, I have tried Basic and I have tried Windows.  I have tried my domain credentials, my SAP credentials, I have tried it with and without Validate Server Certificate. 

Everytime, I get a cogwheel turning at the top of the page that will spin, overnight, few days, until I click something else on the page.  I get the following:

• Activity ID1fcf0e62-c8a4-45b7-94c8-6e1fe71d36bb
• Request ID130aeaff-d463-c8b6-bab5-5ec0f6b67fb5
• Correlation ID8226ca70-3080-95b9-1b02-584bf7940078
• Status code404
• ecsCorrelationId1fcf0e62-c8a4-45b7-94c8-6e1fe71d36bb
• TimeThu Dec 23 2021 14:05:43 GMT-0600 (Central Standard Time)
• Service version13.0.17333.37
• Client version2112.2.09063-train
• Cluster URIhttps://wabi-us-central-a-primary-redirect.analysis.windows.net/

Should I not choose SAP Hana?  I did try a couple times with ODBC but I didn't know the connector string.   Is there a semantic way I'm not typing in correctly?  

Thanks in advance.

[lbendlin]

The connection strings must match exactly betwee Desktop and gateway, otherwise the gateway will not be selectable.  This includes the casing.

You also need to have gateway connections defined for all other on-premise data sources in your dataset. Only if all of them are defined in the gateway will you be able to select the gateway.

[v-luwang-msft]

Hi @sareed23 ,

You would need to install the SAP HANA ODBC driver on the machines that have Power BI Desktop and Power BI gateway installed.

To connect to SAP hana in Power BI Desktop, you can follow the guide in this article:https://docs.microsoft.com/en-us/power-bi/desktop-sap-hana.

To add SAP hana data source within Power BI gateway, please follow the instructions in this article:https://docs.microsoft.com/en-us/power-bi/service-gateway-enterprise-manage-sap.

Best Regards

Lucien

[sareed23]

These are the steps I have followed and I only get a spinning circle in adding the Gateway data source.   It is added to PowerBI itself successfully.

[lbendlin · Accepted solution]

"I installed the On Premise Data Gateway on my workstation" - Please don't do that.  Gateways should be installed on dedicated machines that are online 24x7

As for the connection - You must use exactly the same connection type as you specified on the desktop.  So if you use ODBC on the desktop with a System DSN then you need to set up the exact same System DSN on the gateway cluster members. Then choose ODBC and specify 

dsn=<your data source name>  

as the server address.

You can try to use DSN-less connections but be aware that there is a size limit on the connection string that will likely prevent you from going dsn-less.

[sareed23]

I understand about not wanting it on a workstation.  My initial hope was to just ttest connecting out and then setting up properly once I understood the process better.   

As far as the next portion, I had installed the Hana ODBC driver and it created a listing in the ODBC for HDBODBC in the drivers tab.   That is what I have been using within PowerBI successfully.  I do not have any reference under User DSN nor System DSN currently.   When I try to add a System DSN source using the HDBODBC driver, I fail every time at test connection.   Most often it is saying it isn't recognizing the database name, which I copied directly from the SAP B1 interface.  I have tried using the name of the server found in network dns, i tried using the name of the server found in the SAP interface, tried using the IP.   But am not able to get a DSN added.   

[KAATA]

Hi @sareed23 ,
I am facing a similar issue.
I can successfully connect to SAP HANA database in Power BI desktop.
I have installed the SAP HANA client on the Gateway server.
However, when I navigate to the User DSN and System DSN, it is totally blank.
When I try to add User DSN or System DSN, using the HDBODBC driver, I use exactly the same server and port that I used in Power BI Desktop.
However, the connection is not successful and I am getting an error that "No connection could be made because the target machine actively refused it".
Did you experience this error in your case?
Thanks in advance!

[sareed23]

KAATA, I do not recall that specific error message, sorry.   I would make sure you are adding into the SystemDNS and ensure the port is correct.   I will go back thru my notes to see if anything else stood out.   Also, I guess to make sure you are using the correct ODBC of 64bit vs 32bit to match your software.  

[KAATA]

Thanks @sareed23,
I suspect it is the firewall.
Thanks for taking the time to check and respond!

[sareed23]

I did get a message of connection successful in the gateway cluster now.   However, if I publish a report from PowerBi Desktop to My Workspace at app.powerbi.com, I get an error message "There is no gateway to access the data source "

[sareed23]

I now have the gateway saying successful, I am seeing the dataset in my app.powerbi, i see in settings for the dataset it shows connected to gateway and says I can schedule refresh now too.

I went back through from scratch and followed as you described with a pedantic eye to detail.  All seems to be working better.   I am goign to test the refresh schedule and sharing with others next but I think you answered what I needed.   Thank you.