How to use MDX in HANA Studio?

[AshF]

I am having difficulty getting an MDX query in HANA studio to return results. I can fire an MDX query such as this

MDX SELECT {[MONTH].[MONTH].[All]} ON COLUMNS 

FROM [FACT_DTD]

WHERE ([Measures].[AverageHc])

CELL PROPERTIES VALUE

But all it returns is a single cell with a GUID value.

Do I need to do something with this GUID value or does the MDX need to be written differently?

[rama_shankar3]

Ashley:

Please go through http://scn.sap.com/thread/2144903 for few details on MDX.

Please can you expand on what exactly you are trying to achieve using MDX. SQL / procedures are flexible and can assist with meeting complex reporting needs within HANA.

If you let me know what you are trying to do with MDX, I can suggest an alternative way if I know one using SQL / procedures.

Hope this helps.

Regards,

Rama

[Former Member]

Hi Gregory/Ashley/Jinlin/Rama,

Appreciate you discussing on this topic. I am working on a similar development and request your guidance/inputs for the same.

Background: I am using HANA DB as a standlone DB with HANA Studio.

Scenario/Issue: I know that it supports MDX via ODBO. But what I am unaware about is:

1/ Can I write a simple MDX using SQL editor in HANA Studio and return desired results? If Yes, how and what would be the syntax.

2/ If I want to connect HANA DB using JDBC and execute an MDX query on my Analytic View, how can I go about it?

Already tried: An MDX query similar to the one above but I threw an error in the SQL editor of HANA Studio.

Prior thanks!

Regards,

Kunal Gandhi

[Former Member]

Hi Ashley,

        Studio is built on top of JDBC, which does know how to interpret multidimensional resultset.

        so all JDBC can do it to pass the MDX query to the engine, and get back a GUID.

         This GUID is like a handle and allows you to access the various resultsets, for example MDX           GET_CELLDATA <guid>.

        And don't forget to release the handle when you are done with it:

        MDX CLOSE REQUEST <guid>

Thanks,

-Jinlin

[Former Member]

Hi Jinlin,

I am not sure hwo to use this GET_CELLDATA function to access the MDX resultsets. I tried couple commands like below in the SQL editor. They all had syntax errors. 505B45842B7C119FE10000000A523B50 is the GUID value returned by the MDX query.

GET_CELLDATA(505B45842B7C119FE10000000A523B50);

MDX GET_CELLDATA 505B45842B7C119FE10000000A523B50;

Could you give me some more information about how to use the GET_CELLDATA funciton. Or any other way to use the GUID to retrieve the MDX resultsets?

Any help would be appreciated.

Thanks,

Wenjin

[Former Member]

The GET_CELLDATA function call works for us after the HANA DB version is upgraded to revision 36.

MDX GET_CELLDATA 505B45842B7C119FE10000000A523B50

Thanks,

Wenjin

[former_member182277]

Hi Ashley,

Please find the below link, it might help you

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/e03fef5e-d82f-2f10-8898-859c4ed57...

Regards,

Neha

[AshF]

Thanks Neha - I have already been through that document and as I mentioned previously Excel is not an option.

[former_member182277]

Hi Ashley,

I am not sure about the same but you can try Hierrarchy option for the same. It might resolve your problem.

Regards,Neha

[Former Member]

thanks neha  singal , i like the linked u gave below it help me as i am preparing for my examination.

regards rehan

[Former Member]

Hi Ashley,

have you tried ODBO connection in Excel? once you select a dimension in the Pivot you can view your MDX query syntax in the performance tab and can tweak your VBA to show it as well. do you know if studio should allow MDX results displayed from the SQL Editor? after copying an MDX statement from the tab, i also get a GUID.

[AshF]

Hi Gregory,

Excel is not an option for what we are developing. I am not sure if/how HANA Studio supports MDX query results which is why I posted here.