Query HANA using MDX from .Net

[colin_overton]

Hi,

I need to query HANA using MDX from a .NET client.

I have seen that it is possible using JDBC (e.g. https://archive.sap.com/discussions/thread/3485526) and hoped to do the same from .Net

I have tried using both the ODBC driver and the ADO.Net library. (Example code below)

In the case of the ODBC connection I get an error (also below) implying that the cube does not exist. It does, and I can successfully query it from the Eclipse HANA Studio.

In the case of the ADO.Net HanaConnection the query runs, but no recordset is returned so I can't get the GUID I need to fetch the axes and cells.

Am I doing something wrong, is this a bug, or is there another approach I should be using? Note I can't use ODBO as that doesn't support SAML.

Example code, ODBC:

using (var conn = new System.Data.Odbc.OdbcConnection("DRIVER=HDBODBC;ServerNode=hxehost:39015;DatabaseName=HXE;UID=SYSTEM;PWD=XXXXXX;"))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = mdx;
using (var rdr = cmd.ExecuteReader()) //exception thrown here
{
//read the guid, in theory
}
}
}

Example code, ADO.Net:

using (var conn = new Sap.Data.Hana.HanaConnection("Server=hxehost:39015;Database=HXE;UserId=SYSTEM;Password=XXXXXX;"))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = mdx;
using (var rdr = cmd.ExecuteReader())
{
//reader has no fields or rows
}
}
}

Error from ODBC MDX execution:

ERROR [42000] [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;256 sql processing error: Error occurred while executing a MDX statement. The error message is: Cube SOMETESTVIEW could not be found. Please check if the Cube is valid and exists. The detail error message: Entity cache: cache failed to load correctly: cube SOMETESTVIEW could not be found in "_SYS_BI"."BIMC_CUBES"

[johanduplessis_sanlam]

Better late than never, and hopefully this can help somebody else.

I was trying to execute MDX from Python and the way I found it works best is to wrap MDX in "DO BEGIN EXECUTE IMMEDIATE 'MDX <mdx statement here>' END;"

This trick also works in HANA Studio if "Prepare Statement" is enabled.

This method bypasses all the driver differences that might pop up and is from my experience the most reliable way to execute MDX statements if you don't want to use ODBO.