Problem using ODBC connection to HANA via Excel

[patrickbachmann]

Hi folks,

I'm having trouble using ODBC connection to access HANA data via Excel 2010.  I have setup my ODBC administration connection and the connection test is successful.  Next when I attempt to connect in excel I get the following message:

"The data connection wizard cannot obtain a list of databases from the specified data source."

I have read other forum posts saying to make sure _SYS_REPO has access to my schema which it does so I'm not sure what the problem could be.

Any suggestions?

Thanks

[Former Member · Accepted solution]

Hi Patrick,

   I had to do it and install the 32 bit drivers on my Windows 7 box running 32 bit Excel.  Here is what I did - basically following steps on page 20 of http://scn.sap.com/docs/DOC-5026

1. Downloaded the client and installed the 32 bit drivers. Success
2. Made sure my HANA instance is running and launched Excel and the from Data ribbon launched the Data Connection wizard and selected Other/Advanced - Success
3. In the Data Link Properties dialog, selected SAP HANA MDX Provider. For host - entered in IP address for HANA instance with a colon and the port 30015; instance number 00, user SYSTEM and password. Clicked Test Connection - Success
4. Feeling giddy now....Clicked OK - darn - I got the error "The Data Connection Wizard cannot obtain a list of databases from the specified data source"
5. Went back to the ODBC Data Source Admin dialog - drat - the ODBC driver for HANA is not listed.

Looks like we are out of luck 😞 Maybe SPS5 will help when it is available on AWS.

Regards,

Bill

[patrickbachmann · Accepted solution]

Hi Bill,

Thanks for your response and trying.  I'm actually using 32bit Excel too.  Looks like you are using the MDX connection.  I have another thread open for that, I can't get MDX or ODBC method to work. Perhaps somebody else who has had this problem will chime in soon.  We can hope. 

Thanks!

[Former Member · Accepted solution]

Hi Patrick,

    I had posted a similar thread back last week and I only hear crickets I did as a friend of mine who works at Microsoft to follow up with SAP on the problem. I had a crash dump when attempting to connect PowerPivot via ODBC with HANA as well and reported to Microsoft as well. Hopefully with the different vectors, we can get SAP's attention. In addition, the Microsoft BI designer for creating AS cubes on top of HANA doesn't work either. Ditto with Reporting Services via ODBC. Everything says - connection succeeded, but when it comes time to enumerate tables - nothing. I even tried a restricted user who had the appropriate repository rights but access to one schema thinking it was a system catalog enumeration issues - same result.

    BTW, when connecting with the MDX provider, did you build a model first?

Regards,

Bill

[patrickbachmann · Accepted solution]

Hi Bill.  Yes I built the model first.  I tried with different credentials including my own which has more privileges than my test user and nothing works.  Hopefully we will get a response soon otherwise I may post an SAP message.

-Patrick

[patrickbachmann · Accepted solution]

Hey Bill, we just located an excel driver on sap marketplace that fixed the problem for the MDX connection method!  The name of the file you want is HDBCLIENTXLS45_45-10011035.EXE.  It's working great now for me.    ODBC connection method still not working though but happy with the MDX.

-Patrick

[Former Member · Accepted solution]

Hi Patrick,

   That's great news for starters - is there a "public" download link for the file? I'm not an official SAP customer so any time it asks me for a customer number, I'm blocked. Maybe someone like Juergen can help.

Thanks,

Bill

[Former Member · Accepted solution]

Hi Patrick,

I am also facing the same issue,

can you post the download link for HDBCLIENTXLS45_45-10011035.EXE.

Thanks,

Singaravadivel

[patrickbachmann · Accepted solution]

You need to have access to SAP Service marketplace:

1) goto service.sap.com/support

2) Click software downloads tab

3) Search for software downloads and paste HDBCLIENTXLS45_45-10011035.EXE

I can not cut & paste the URL.

[patrickbachmann · Accepted solution]

Hi Bill, sorry I missed your comment.  I'm not sure if there is a public place to find this file.  I will ask my SAP contact.

[Former Member · Accepted solution]

The numbering sounds it is at revision 45 which means SPS5. Did you connect it to the same server revision? The one that we have in Developer Center is still at revision 38 unfortunately.

Rocky

[patrickbachmann · Accepted solution]

Ahh yes we just upgraded to SP5.

[patrickbachmann · Accepted solution]

Rocky just to clarify;  I used hdb client 45 to connect excel 2010 to hana and it worked via MDX connection to both SPS4 hana db and SPS5 hana db.  However I still can not get ODBC connection to work using hdb client 45 to either hana db version.  I still get message 'The data connection wizard cannot obtain a list of databases from the specified data source'.

-Patrick

[Former Member · Accepted solution]

Hi Patrick,

It sounds like a bug. I will take your feedback to the team and will report back as soon as I hear back from them.

Best,

Rocky

[patrickbachmann · Accepted solution]

Awesome thanks Rocky.  Often I'm just doing things for curiosity/testing sake but in this case I actually have a legitimate business need to use ODBC so it's much appreciated.

-Patrick

[Former Member · Accepted solution]

Hi Patrick,

Try to load the 32-bits ODBC Data Source Admin tools to create a 32-bit ODBC entry, not the 64-bit ODBC Data Source Admin tools.

In windows, it’s quite misleading, but you can find the info from http://support.microsoft.com/kb/942976

• The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
• The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

So, run C:\Windows\SysWoW64\Odbcad32.exe, then try to create a new Data Source entry with different name than the one you created in 64-bit ODBC Data Source Admin tools (so that you can differentiate when connecting with Crystal Reports).

After you created the new Data Source entry for 32-bit ODBC, you can try to open Excel to see if your new Data Source entry shows up in the database connection list.

I hope this information helps to answer your ODBC issue.

Regards,

Ferry

[julio_galli]

Buen día, necesito conectar Power bi desktop con sap s4hana y no logro realizar la conexion. Agradecido por sus indicaciones.

[Former Member]

This message was moderated.

[randy_middleton]

Hi Bill,

Client OS - Windows 7 64 bit(win7 home premium 64bit)

Apache OpenOffice 4.0.1

AOO401m5(Build:9714)  -  Rev. 1524958

2013-09-20 11:40:29 (Fr, 20 Sep 2013)

ODBC  - Version 1.00.56.49638 (64 bit)  ( I first installed 64 bit ODBC and from SAP HANA client and studio 1.56 version ) , When connecting for the first time make sure that User whom you are connecting has MODELING permission and connect through SAP HANA Studio to make sure that you can login and change the password if needed.

SAP HANA Studio

                    Version: 1.0.56

                    Build id: 201305141923 (377318)

SAP HANA Database

                    version 1.00.45.371235

This is first time hands on HANA,

[ Successfully set up connection]

This is the set up so far.  Have a 2nd SAP HANA server will  do screen shots with UID and PW.

server name hana68.

Clk start button.

clk admin tools.

clk DATA Sources (ODBC)

open to User DSN tab.

CLK to tab; System DSN.

clk add button.

Now there are two ODBC connections to two SAP HANA servers. Hana62 and Hana68.

[former_member182114]

Hmmm, Nice to hear that 64bits client works on Excel... At beggining only 32bits could provide ODBC for Excel.

Thanks Randy.

Regards, Fernando Da Rós

[Private_Member_310716]

Have you been able to also consume data in OpenOffice / LibreOffice?

[Former Member]

I was able to successfully connect to SAP Hana and Pivot a table using Excel 32 bit and ODBC Drivers 32 bit on a 64 bit windows 7 .

[Former Member]

Hi Rajesh,

   Can you please share with us what version of Excel you are using and what version of the SAP HANA Client and engine you are running with? Now that SPS5 is on AWS, I can check it out myself with 64bit Excel 2013.

Thanks,

Bill

[Former Member]

Hi Bill,

Client OS - Windows 7 64 bit

Excel - 2013 32 bit version

ODBC 32 bit - Version 1.5.14.16 (32 bit)  ( I first installed 64 bit ODBC and unable to view the driver so installed 32 bit ) , When connecting for the first time make sure that User whom you are connecting has MODELING permission and connect through SAP HANA Studio to make sure that you can login and change the password if needed.

SAP HANA Studio

                    Version: 1.0.38

                    Build id: 201210022202 (368649)

SAP HANA Database

                    version 1.00.36.366896

This is first time hands on HANA,

By the Way i am using the SAP HANA DB which is on https://en.ucloudbiz.olleh.com/

[rogerloe]

Hi

One thing to bear in mind is that you HAVE to have at least one view defined in HANA before the Excel MDX connectivity will work - this is what the blow message is referring to

"The data connection wizard cannot obtain a list of databases from the specified data source."

In addition you need to correct libraries as above

regards

Roger

[Former Member]

Hi Roger,

   The ODBO MDX driver for Excel is working fine. The problem is the SAP HANA ODBC driver for relational table access still fails to work with the latest HANA One build .52. The ODBC driver is supposed to allow access to the base tables and views in a database without the need for creating Analytic views. The Microsoft BI stack rely on using the ODBC driver for things like Reporting Services, Analysis Services, PowerPivot, and Excel - via traditional ODBC access. As it stands - when these tools attempt to connect to the HANA server, these client tools make a request to enumerate the databases on the server. The problem is that the ODBC driver assumes that there is only one database and the developers of the driver failed to implement the interface to enumerate databases as only returning 1 for to the initial catalog.

Regards,

Bill

[Private_Member_310716]

Hello,

I'm struggling about this topic after I received a new hardware where I needed to re-configure the ODBC clients. So I installed the 32 Bit client Revision 69 and the connection is successful, however it fails to show any datebase object. So is there still no solution to this problem? On my old hardware the ODBC Connection worked properly in Excel (maybe with Revision 43 or something like that).

The MDX driver is working properly and can access Analytical views, just the ODBC is failing (also a colleague of mine is running into the same error "Der Datenverbindungs-Assistent kann keine Datenbankliste in der angegebenen Datenquelle finden" when he tries to request data via ODBC 32 Bit.

Thanks,

Daniel

[Dan_Wroblewski]

I'm not sure the documentation will help in this case, but I wanted to make you aware that we tried to document all the client interfaces in the SAP HANA Developer Guide (see chapter 14, Using Database Client Interfaces).

If there is something that is missing from the guide (like setting the proper permissions or whatever), please let me know and I will update the guide.

[srinivas_rapthadu]

Hi Patrick,

Connection from Excel to HANA for OLAP analysis uses MDX  via ODBO (OLE DB).  Therefore, you can select the Analytic views and it facilitates the OLAP analysis.  In order to achieve this, you follow the steps below :

1. From the Data tab on the Excel tabstrip, select the From Other Sources button, and select the From Data Connection Wizard option
2. Select the Other/Advanced item from the list and click Next
3. Select the SAP HANA MDX Provider from the list and click OK

Now, coming to ODBC connection. It is Microsoft-driven specification for relational reporting and requests are made via SQL . The standard ODBC connection from Excel is therefore relational which means you have the ability to select the tables and not Analytic Views.  If you do want to test this, follow the steps below :

Go to “Data” --> From Other Sources -->  From Microsoft Query --> Choose the HANA Data Source created with ODBC connectivity and click OK.

Once the Provider Server details, user id & Password are provided, click OK.

Now go over the list tables and columns available for your user access and locate the table you want to pull the data from.  The subsequent steps are the same as you do for a MDX connectivity with regards to PivotTable.

As you see here, Excel-ODBC connectivity will not  let you consume the Analytic views directly. Therefore to connect directly to a HANA system from Excel and create a Excel PivotTable spreadsheet using HANA data, it is recommended to use MDX( ODBO) interface.

Regards, Srini

[patrickbachmann]

Hi Ferry, I do not seem to have a directory in windows called SysWoW64.  I only have the System32 folder.

Srinivas, I did your method and was able to successfully return some table results.  That is unfortunate that I can not pull in VIEWS however.  I did not realize that this is not supported.

Thanks,

-Patrick

[Former Member]

Hi Patrick,

Would you mind to try to search "odbcad32.exe" in your Start->Search bar? It appears that the driver is installed as 32bit version and you can only see it if you open the 32 bit version of ODBC control panel. Unfortunately, Windows is not very obvious about this.

Let me know if this works out.

Rocky

[patrickbachmann]

Hi Rocky.  It's the exact odbc administration that I'm already using;

%SystemRoot%\system32\odbcad32.exe

[srinivas_rapthadu]

Hi Patrick,

ODBC is supported by HANA as such. However, if you want to consume the views from Excel, you can go thourgh MDX/ODBO interface.

Overall interface support and connections to HANA are highlighted below. Hope this should answer your question.

Is there any specific scenario you have in mind that you cannot do with MDX/ODBO connectivity on Excel and want to use ODBC from Excel ?

Regards, Srini

[patrickbachmann]

I would like to feed a HANA view into a third party sales planning tool so I can forecast demand.

[Former Member]

Hi Patrick,

   Well - saved me from downloading the client to my home laptop to see if I could get Excel 2010 running. I had the same problem with Excel 2013 and thought it may be a version issue.  BTW, are running 64 bit Excel or 32 bit. It could make a difference as the demo videos used to show hooking up to HANA look to be using 32 bit Excel.

Regards,

Bill