MDX Query Editable in BO 3.0?

[Former Member]

Hello, everyone,

We are building BObj Universes in Version 3.0 against SAP BEx Queries. We have installed the "flattening error" service packs on the BW side, and have read and worked through the forum posts. Our issue is creating objects in the Universe, with the guidelines outlined by Didier in "OLAP universes best practices" document.

We can create simple objects in the Universe, but when we try to add the MDX functions to perform a rolling aggregate across time (the last 12 fiscal periods) or to find the corresponding value for the same period for one year ago, we get an error in the report.

So, we have a couple of questions:

1. Is there anyplace to see / edit the MDX query, like can be done in a relational universe?

2. Where exactly in the Universe does the syntax outlined in Didier's Best Practice document go? We have tried in the Universe object definition, but cannot see anywhere else to place the syntax.

Thanks for any assistance!

Regards,

Judy

[Former Member]

We are certain that we are using the correct MDX syntax in the BO Universe. The project team will pursue installing the package to view the entire query.

[Former Member]

Hi Judy,

I would like to know if you could solve the problem because I'm facing somethin like that, I get always that kind of errors when try to use a Function.

Thanks in advance.

[Former Member]

Hi Judy,

1. Is there anyplace to see / edit the MDX query, like can be done in a relational universe?

You need to activate the WebI traces and ODA traces (see attached zip file).

2. Where exactly in the Universe does the syntax outlined in Didier's Best Practice document go? We have tried in the Universe object definition, but cannot see anywhere else to place the syntax.

You can only write MDX in objects and filters. In objects you need to enclose MDX syntax with the following XML tags: <EXPRESSION></EXPRESSION>

Regards,

Didier

[Former Member]

Hi, Didier,

Thank you so much for the quick response and the tips! I was able to get the correct syntax in the Universe Objects (hooray!) and have successfully created Current Year, Year to Date objects, but am having trouble using the PrevMember function in the where clause for Previous Year calculations. Here's what I've done for a Previous Year, Year to Date object:

SELECT part of the Universe Object:

<EXPRESSION> SUM(YTD([0CALMONTH].CurrentMember), [Measures].[4OHSNHMBZDWBV5J6NOYCWXZXV]) </EXPRESSION>

WHERE part of the Universe Object

I've tried:

( [Measures].[4OHSNHMBZDWBV5J6NOYCWXZXV], [0CALMONTH].[LEVEL01].CurrentMember.PrevMember)

( [Measures].[4OHSNHMBZDWBV5J6NOYCWXZXV], [0CALMONTH].[LEVEL01].PrevMember)

( [Measures].[4OHSNHMBZDWBV5J6NOYCWXZXV], [0CALMONTH].PrevMember)]

The above does not give a syntax error in the report, but the columns for PY and CY have the same values.

Thanks again for any help!

Regards,

Judy Posey

[Former Member]

Sorry, there was a typo in the previous:

The last example should be:

( [Measures.4OHSNHMBZDWBV5J6NOYCWXZXV],[0CALMONTH].PrevMember)

Thanks again!

[Former Member]

Hi,

Can you explain a little bit more what you is the formula you want to achieve?

For instance the Where clause has the same syntax than a filter.

So I want to be sure to understand before giving you an answer;

Regards,

Didier

[Former Member]

Thanks, Didier.

What we are trying to do is create objects in the universe that will dynamicaly calculate historical measures based on the latest closed accounting period (default) or an accounting period chosen in a prompt. This universe will support both canned reports and adhoc reports, so we would like these calculations to be universe-based, rather than report-based, if possible (we have already discovered that they cannot be BEx Query-based). If we cannot implement in the universe, we will have to implement in the report, but that solution will not support the adhoc users.

Here is a general sampling of the types of dynamic historical measures needed:

- Current Year (CY) Year To Date (YTD) -- I believe we have this one

- Previous Year (PY) YTD aggregates for the corresponding accounting period in the default or the selection (example, if the user selects May, show the Current YTD and the Previous YTD through May)

- The Same Period in the Previous Year for the Current Year Accounting Period (either default or selection)

- The prior three months from the the Current Year Accounting Period (either default or selection)

- Quarter To Date (QTD) cumulative aggregates

- Rolling Annualized measures with the formula : (last full period and prior 11 periods) / 12

- Rolling Prior 12 accounting periods from the Current Year Accounting Period (either default or selection)

Thanks very much,

Regards,

Judy

[Former Member]

Hi Judy,

Here is an example of YTD on previous member:

<EXPRESSION> SUM(YTD([0CALMONTH].PrevMember), [Measures].[4OHSNHMBZDWBV5J6NOYCWXZXV]) </EXPRESSION>

You can also use MDX expression such as PERIOSDTODATE or PARALLELPERIOD for returning the value of a n previous member at a given level.

Example, measure for the 3rd previous member at any level:

<EXPRESSION> ( [Measures].[4OHSNHMBZDWBV5J6NOYCWXZXV], PARALLELPERIOD( [0CALMONTH].currentmember.level, 3, [0CALMONTH].currentmember)   ) </EXPRESSION>

Example, YTD with parallelperiod:

<EXPRESSION> SUM(YTD( PARALLELPERIOD( [0CALMONTH].currentmember.level, 1, [0CALMONTH].currentmember)  ), [Measures].[4OHSNHMBZDWBV5J6NOYCWXZXV]) </EXPRESSION>

Please have a look on the Microsoft documentation for parallelperiod:[http://technet.microsoft.com/en-us/library/ms145500.aspx]

Best regards

Didier

[Former Member]

Thnak you again, Didier.

We are using the syntax you described, but are still getting a syntax error when running the report -- the report shows the error as a syntax error on a single quote, but there are no quotes in the Universe Object - I recreated it to make sure there were no extraneous characters.

We will investigate the BEx query and the configuration to try to find the source of the single quote -- unless you know of something that would cause the single quote. The error message is below.

Thanks again for any ideas,

Judy Posey

Query 1 - USSAPBW_SI_Plan_Actual

A database error occured. The database error text is: The MDX query WITH MEMBER [Measures].[E84A613D-85D8-4E99-89,9,4E,66,BA,DA,FD,F4] AS ' (SUM(YTD([0CALMONTH].PREVMEMBER),[Measures].[4OHSNHMBZDWBV5J6NOYCWXZXV]) ' SELECT { [Measures].[4OHSNHMBZDWBV5J6NOYCWXZXV], [Measures].[4OE9EYV31J7XU9SN1LKBPUQ0J], [Measures].[E84A613D-85D8-4E99-89,9,4E,66,BA,DA,FD,F4] } ON COLUMNS , NON EMPTY CROSSJOIN( CROSSJOIN( CROSSJOIN( { Descendants( [0CALMONTH 0YEA_MON].[LEVEL00].MEMBERS, [0CALMONTH 0YEA_MON].[LEVEL01], LEAVES) } , [ZBUSDAYS].[LEVEL01].MEMBERS ), [ZSKU].[LEVEL01].MEMBERS ), { Descendants( [ZPRD_TYP PRODUCT TYPE HIERARCHY].[LEVEL00].MEMBERS, [ZPRD_TYP PRODUCT TYPE HIERARCHY].[LEVEL02], LEAVES) } ) DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [ZMP_PLAC/ZSISAPLAC] failed to execute with the error Invalid MDX command with '. (WIS 10901)

[Former Member]

Hello again, Didier,

I searched for a 'single quote' error, and found that SP3 is needed, then checked the Version of BObj installed at this office, and found 12.3.0.601, or SP 3.

Regards,

Judy Posey

[Henry_Banks]

It's this bit here that is tripping up your query :

AS ' (SUM(YTD(0CALMONTH.PREVMEMBER),Measures.4OHSNHMBZDWBV5J6NOYCWXZXV) '

[Former Member]

Thanks, Henry, I can see that that is the problem. There seems to be extra 'stuff' being added to the full MDX query from Business Objects in this environment, and that is the path the project will pursue.

Didier,

I am marking this question answered, as with your help we have ensured that the MDX syntax in the Universe objects is correct.

The project team will pursue installing the package from your earlier post to review the entire MDX query being passed to BW.

Thanks very much for your help!

Regards,

Judy Posey