How can I connect SQL Server BI tools to HANA? I'm getting ODBC error with v38

[Former Member]

Is there a trick to getting the SQL Server BI tools to connect to HANA. I was trying to use SQL Server 2012 Reporting Services and SQL Server Data Tools to build reports to try and connect and I'm getting a consistent failure when setting up a data link to the ODBC driver. I'm using v38 of the client tooll x64 bit version.

I first setup a User (or System or File) DSN using the ODBC Data Source Administrator - no problems with connecting via the tool using Server:port imdbhdb:30015 for the port. Clicking Connect prompted me for Use and Password and connected without SSL and got a Connect sucessful! message.

When I go into SQL Server Data Tools or use the SQL Server 2012 Report Builder to create a Data Link - this is where the problems start. The example I'll walkthru here is creating a Tabular Data model for Analysis Services using SQL Server Data Tools.

1. Create a Tablular model project
2. Choose the Model menu and select the Import from Data Source... command that launches the Table Import Wizard.
3. Choose the Others (OLEDB/ODBC) source which uses the OLEDB for ODBC provider to make the connection
4. Click the Build... command to build my connection string
5. I select my HANA DSN, enter the user name and passwod and then click the Test Connection button.

This results in the very unexpected error:

Test connection failed because of an error in imitializing provider. [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application.

Any help would be greatly appriciated.

Regards,

Bill

[Former Member]

Hi Bill,

I'm not a SQL Server 2012 Report Builder tools expert, but I'm guessing that the tools might have been using Microsoft Visual Studio components that runs in 32-bit runtime libraries.

I would suggest you to install 32-bit HANA Client (NTintel), setup a new ODBC with 32-bit HANA ODBC driver, and try the connection again with your SQL Server 2012 Report Builder tools.

Regards,

Ferry

[Former Member]

From Visual Studio, Import from SSAS Tabular Project:

I installed the 32bit drivers and can setup a connection which Tests OK.  But can't get any data out of HANA when import runs

I then get success here:

I choose this:

Then:

Then:

Then when it goes to import, it fails

So close..   Anyone seen this? Or know how to fix it?

[Former Member]

Hi Kirk,

You need to configure the ODBC connection for your HDB server using c:\windows\sysWOW64\odbcad32.exe.

For more details, you can check:

Managing Data Sources

To manage a data source that connects to a 32-bit driver under 64-bit platform, use c:\windows\sysWOW64\odbcad32.exe. To manage a data source that connects to a 64-bit driver, use c:\windows\system32\odbcad32.exe. In Administrative Tools on a 64-bit Windows 8 operating system, there are icons for both the 32-bit and 64-bit ODBC Data Source Administrator dialog box.

Regards,

Ferry

[Former Member]

I think I figured out the ODBC Driver piece.    I created a 32bit and 64bit data source with the same name.

Once I did that, I can issue queries and get data into SSAS Tabular, IF I put in a SQL statement like select * from sflight.sairport

Here is the result you can see returned data in SSAS as well as the query screen in the lower left.

New Failure DIALOG not like the one above: 
BUT if I try to pick multiple tables from the Table Picker tool,  improper SQL syntax is getting generated an causing the following error.   So close now but not what I expect to happen!!

[Former Member]

The error you got is because HANA cannot handle multiple SQL statements execution in one call.

So, you would need to create different SQL query for each table import.

For reference:

Regards,

Ferry