HANA MDX - issue with FILTER function in Member Formula

[former_member570917]

Hi all,

I'm attempting to get HANA MDX working in a model. Said model had been built with around 60 member formulae. I then tried to turn on HANA MDX but got a generic error about member formula.

So, to test this, I copied the model in Dev and then deleted all member formula. I then successfully turned on HANA MDX. I then started adding the formula back in again one-by-one.

I seem to have hit an issue now that I've got to a formula with a "FILTER" function in. Said formula works absolutely fine in the "non-HANA MDX" model but when I try to process the same formula in the HANA MDX model, it throws an error saying "Internal error : no result set".

The formula is as below. I've also tried adding {} around my set expressions, with no luck.

SUM(FILTER(DESCENDANTS([BUSINESSUNIT].CurrentMember,80,LEAVES),[BUSINESSUNIT].CURRENTMEMBER.PROPERTIES("BU_PROPERTY")="Y"),[C_ACCT_MDX_TEST].[TP111M]-[C_ACCT_MDX_TEST].[TP1111]);SOLVE_ORDER=5

I've correctly set the global, environmental and model level parameters required to turn on HANA MDX. I've also run BAPI_MDPROVIDER_GET_FUNCTIONS and can confirm that I have the "FILTER" function in my list. I have got other formula containing the "SUM" and "DESCENDANTS" functions working just fine, hence why I think it's the FILTER function that's an issue.

Can anyone shed any light? I know there are lots of issues with HANA MDX, just hoping someone out there might have come across something similar. This is a BPC 10.1 Standard model by the way.

Thanks

Joe

[former_member186338]

HANA MDX can result in many issues compared to standard MDX 🙂 And nothing is documented...

"Out of interest, do you know the process of actually checking if a function "works" in HANA MDX? Is it just going to SAP Support to try to get them to confirm?"

For sure you can try to debug in ABAP 🙂 But it can be a very complex process!

Other way is to ask SAP support! Create a very simple test case using copy of ENVIRONMENTSHELL with formula SUM(FILTER(...))

[former_member186338]

In general FILTER may not work even it's defined 🙂

After loading data you can perform reclassification using script logic to the required new accounts! As a result - no dimension member formulas required.

[former_member570917]

Yep - that is my suggestion to the business also. Sadly, getting approval for that is taking a while (even though it's a relatively simple piece of logic). So I was hoping to turn on HANA MDX to at least help with performance in the meantime 🙂

Out of interest, do you know the process of actually checking if a function "works" in HANA MDX? Is it just going to SAP Support to try to get them to confirm?

[former_member186338]

Aggregating members by property is a bad idea in general! Why not to use extra hierarchy?

[former_member570917]

Hi Vadim,

I agree completely that ideally we would just use a standard hierarchy. Here the business have a very specific way they want to report their data.

They do have their P&L in their BPC account dimension which is just a normal P&L with normal rollups. However, for their management reporting, they change this to various splits of the same data, depending on the business unit type. There are about 5-6 types of businessunits, (that property in the MDX above is just an example). This forms a pseudo-P&L, but the way they load data into BPC doesn't 100% match this. For example, they may have a revenue line called "Commission" in their account dimension which is a base level member that they load to. Then on the face of their P&L they have "Commission - Type 1", "Comission - Type 2", "Commission - Type 3" etc. Depending on which business unit is selected on the report, the data will slot into one of these (and one business unit can actually be various types).

It gets more complex as they then also want to sometimes be able to add business unit itself to the column axis. So we can't nest account and BU into the rows to create this report.

I have pushed for them to change how they load from their source system, to instead create a much more granular P&L in BPC (i.e. where Commission 1/2/3 would be 3 distinct base level members) or perhaps even introduce a BU_TYPE dimension and store the data correctly. But, for various reasons, the business is unable to do these right now and so these MDX formula have been introduced as a short-medium term fix.

Not sure if that clarifies at all? But long story short, the business does not store their data in a sufficient way to report on it using a hierarchy within the account dimension currently, so we have had to go through this approach.

So - if anyone out there has any idea of why "FILTER" wouldn't work in HANA MDX/if I've made a syntax error, I'd hugely appreciate it!