MDX query question

[Former Member]

i have very little SAP experience and am hoping that someone can help me with what i hope is a trivial problem.

i want to build and compare reports off a BW cube using 2 methods - BEx and XMLA.

i am able to run a bex-analyzer query against a cube to obtain reports on key-figures against chareteristics . i am also able to create reports of key figures against an attribute of a characteristic e.g. 0MAT_PLANT is the characteristic and the report was run on the 0PROFIT_CTR attribute of 0MAT_PLANT. (trivial enough).

i now want to obtain the results using the equivalent MDX query, my ultimate goal is to have the query submitted via XMLA to obtain results. i am trying the query out in the MDXTEST first. i am able to refer to a charecteristic of the cube in the MDX and obtain results e.g. material-plant.

e.g.

SELECT {

[Measures].[0ACT_DL_QTY] } ON COLUMNS,

{ [0MAT_PLANT].MEMBERS}ON ROWS

FROM [<my-cube>]

but i am unable to reference the profit-center attribute in the MDX query. i am guessing that i am running into a syntax issue. i have tried to refer to the attribute as [0MAT_PLANT__0PROFIT_CTR], [0MAT_PLANT].[0PROFIT_CTR]

but the MDXTEST always errors out.

how can i refer to the profit_center or any attribute in MDX?

i hope that i have got all the SAP terminology straight.

thanks in advance.

[Former Member]

Hi,

as far as I know, navigational attributes are not available directly on the InfoProvider. You need to create a query in the Query-Designer with all relevant characteristics key figures and navigational attributes in order to use them. Release the Query for OLAP (for example your test query you used in BEX).

Of course you can use display attributes. But as far as I understood your question, you want to aggregate on the attribute.

As Marc already stated, you should have a look on ta mdxtest.

The correct syntax should be

SELECT {

[Measures].[0ACT_DL_QTY] } ON COLUMNS,

{ [0MAT_PLANT__0PROFIT_CTR].MEMBERS}ON ROWS

FROM [<my-cube>]

Heike

[Former Member]

hello heike,

i tried the syntax that you suggested in the MSXTEST and the error that i got was "invalid MDX command with <dim./hier>.

what am i missing here?

thanks.

[Former Member]

Hi,

did you drag & drop the navigational attribute from the left side of ta mdxtest to the right side? Could you see the navigational attribute in the available objects session?

Heike

[Former Member]

hello heike,

yes, the attribute is visible in the "available objects" of the cube. i can see the attribute Profit Center under the "attributes" tree of the 0MAT_PLANT characteristic. actually i can see 3 distinct attribs for profit center - (key), (Name) and (Medium Name) in the list of its attributes. when i drag them across into the MDX editor pane, they come accorss as [0MAT_PLANT}.[20PROFILE_CTR], [0MAT_PLANT].[10PROFIT_CTR] and [0MAT_PANT].[50PROFIT_CTR] respectively.

but when i run the query

e.g. SELECT {

[Measures].[0ACT_DL_QTY] } ON COLUMNS,

{ [0MAT_PLANT].[20PROFIT_CTR}ON ROWS

FROM [<my-cube>]

i get an "invalid MDX command with <internal>.

jibi.

[Former Member]

Hi,

when you want to aggregate on the Profit Center, it should be a navigational attribute. So independent on the attribute you should see it as Characteristic as well, as Navigational attributes behave the same as Characteristics, so you should see 0MAT_PLANT__0PROFIT_CTR also in the available dimension tree (like 0MAT_PLANT). If you don't see it, your underlying query doesn't have the Profit Center navigational attribute inside in this case adept your query.

Regards

Heike

[Former Member]

heike,

thank you for clarifying that. i was assuming that since i was able to do the same in the BEx analyzes, it may have been able to do the same in SAP using MDX.

a side question -

is there an easy way of getting the display attributes (not to aggregate on them, just to display) of a characteristic either using MDX or some other means? would this involve having to write BAPIs to get the deisred display attributes?

e.g. i have 0MAT_PLANT as a characteristic (navigational) in a cube and want to display the profit center display attribute of the material plant that i retrieve using MDX.

thanks,

[Former Member]

Hi,

SELECT {

[Measures].[0ACT_DL_QTY] } ON COLUMNS,

{ [0MAT_PLANT].MEMBERS} DIMENSION PROPERTIES [0MAT_PLANT].[20PROFIT_CTR] ON ROWS

FROM [<my-cube>]

A good mdx reference can be found on the msdn

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxadvanced_8fqr.asp

Regards

Heike

[Former Member]

heike,

thank you very much for helping me with this. i have got all my queries answered.

JT.

[former_member93896]

Hello JT,

the attributes are equivalent to MDX properties (of a member). You can find the mapping in the online help at http://help.sap.com/saphelp_bw33/helpdata/en/9b/c1993c54966f3ae10000000a114084/frameset.htm.

In MDXTEST select your InfoCube from the dropdown box (not a query cube). Then drill down in the available object frame to characteristic 0MAT_PLANT. Under "Properties" you can find the attributes. There's a naming convention for the key value and the various texts. I think you are looking for [0MAT_PLANT].[20PROFIT_CTR]

Regards,

Marc

SAP NetWeaver RIG, US BI

[Former Member]

hello marc,

thank you for the reply. i tried the following in the MDXTEST editor

SELECT { [Measures.[0ACT_DL_QTY] } ON COLUMNS,

{ [0MAT_PLANT].[20PROFIT_CTR] } ON ROWS

FROM [<my-cube>]

i got an "invalid MDX command with <internal>" error.

anything else that i can try?

jt.