ENABLE_HANA_MDX different formula syntax?

[ChristianSass]

Hi,

we have recently activated ENABLE_HANA_MDX support and now we have difficulties with creating formulas

A simple formula like:

[ACCOUNT].[NODE_999]

is not an issue but if we add a 2nd selection like

([ACCOUNT].[NODE_999],[FUNC_AREA].[A])

we do get "Internal error: no result set" during activation.

The same formula worked without ENABLE_HANA_MDX

Thx!

[former_member186338 · Accepted solution]

You are talking about dimension member formulas?

[ChristianSass · Accepted solution]

Yes. You are right. It could have been logic script too

[former_member186338 · Accepted solution]

P.S. And do you have some model in the same environment with ACCOUNT dimension but without FUNC_AREA dimension?

[former_member186338 · Accepted solution]

"logic script" - is another story!

[ChristianSass · Accepted solution]

Yes

[former_member186338 · Accepted solution]

Reason

This formula will be invalid for this model!

Dimension check is done for all models

[ChristianSass · Accepted solution]

now it's clear ....

[former_member186338 · Accepted solution]

You can test the formula with TIME dimension present in each model (assuming you have single TIME dimension )

[ChristianSass · Accepted solution]

It works ....

this wasn't an issue without ENABLE_HANA_MDX ....this is really bad

[former_member186338 · Accepted solution]

After ENABLE_HANA_MDX all formulas are checked against all models...

In theory if you remove the model without FUNC_AREA, then you will be able to save formula. If you then add model without FUNC_AREA then the formula may work

[ChristianSass · Accepted solution]

and it's me again ...

I've copied the Environment and removed all models except one to check the performance gain. I can activate the mentioned formula form above and now I would like to activate some complex formulas, like:

AVG(PERIODSTODATE([TIME].[LEVEL00],CLOSINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER)),[NODE99]))

and again I can't activate the formula

Please don't tell me that this formula doesn't work with MDX support ... I've seen a note that AVG works with HANA MDX

[former_member186338 · Accepted solution]

It works but I remember some notes that with HANA MDX Levels have different ids, like LEVEL 00 instead of LEVEL00 (space inside)...

Please search notes!

[ChristianSass · Accepted solution]

That's what I've found so far

He talks about a document "Instruction on Measure Formula.docx" but I can't find it

I've changed the formula to:

AVG(PERIODSTODATE([TIME].[LEVEL 00],CLOSINGPERIOD([TIME].[LEVEL 02],[TIME].CURRENTMEMBER)),[NODE99]))

with error in activation

[former_member186338 · Accepted solution]

I don't have HANA system in front of me - not able to test. Please try to use very simple formula with time level!

[former_member186338 · Accepted solution]

Ups, but formula itself is incorrect!

Read: PeriodsToDate (MDX)

Correct:

AVG(PERIODSTODATE([TIME].[LEVEL 00],CLOSINGPERIOD([TIME].[LEVEL 02],[TIME].CURRENTMEMBER)),[NODE99])

In you formula extra ")" at the end!!!

[ChristianSass · Accepted solution]

now it works ...!

[former_member186338 · Accepted solution]

In general you have to be EXTREMELY accurate with MDX syntax

[ChristianSass · Accepted solution]

I've noticed that ....

[Former Member · Accepted solution]

last question for today

AVG(PeriodsToDate([TIME].[LEVEL 00],CLOSINGPERIOD([TIME].[LEVEL 02],[TIME].CURRENTMEMBER)),CoalesceEmpty([NODE99],0))

but

AVG(PeriodsToDate([TIME].[LEVEL 00],CLOSINGPERIOD([TIME].[LEVEL 02],[TIME].CURRENTMEMBER)),[NODE99])

works ....

I can activate both but I get a dump with the first one.

CoalesceEmpty([NODE99],0) is probably not supported?

[former_member186338 · Accepted solution]

CoalesceEmpty has to be supported!

Can you provide DUMP info?

[ChristianSass · Accepted solution]

Hi Vadim,

this is the dump ....

[former_member186338 · Accepted solution]

Not a lot of useful information

You can always check what is supported by "run function module BAPI_MDPROVIDER_GET_FUNCTIONS via transaction SE37."

[ChristianSass · Accepted solution]

Great! I was searching for this one ... I had in mind that there was a report or something like that.

I've also tried Report "UJHANA_MDX_TEST" but there is nothing to adjust. You can't even choose the environment

[former_member186338 · Accepted solution]

Sometimes reading help is useful

Just at the end of page: Library of MDX Functions - SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP ...

[former_member229290]

Hello Christian,

Please see this SAP Note: it describes your issue: 2089700.

Regards,

Noura

[ChristianSass]

Thank you but I think this is not the same issue