MDX vs SQL

[Former Member]

Hi Bpc Friends

I would like to know:

in which cases do you recommend me to write script in MDX instead of SQL and vice versa?

Thanks

Michele

[former_member186338 · Accepted solution]

Simply never use MDX syntax 🙂

[Former Member · Accepted solution]

thanks Vadim

[Former Member · Accepted solution]

sorry for this stupid question, the below code is SQL or MDX?

I know that WHEN is SQL and *COMMIT is MDX, am I right?

*WHEN ACCOUNT
*IS FL1015
*WHEN PRODUCTGRP
*IS<>PG_8001
*REC(EXPRESSION=(LOOKUP(FLAG)==1&&LOOKUP(PGIND)==0)?1:2,ACCOUNT=CL1015)
*REC(EXPRESSION=(LOOKUP(FLAG)==2&&LOOKUP(PGIND)!=0)?1:2,ACCOUNT=CL1015)
*ELSE
*REC(EXPRESSION=(LOOKUP(FLAG)==1&&LOOKUP(PGDEP)==0)?1:2,ACCOUNT=CL1015)
*REC(EXPRESSION=(LOOKUP(FLAG)==2&&LOOKUP(PGDEP)!=0)?1:2,ACCOUNT=CL1015)
//*REC(FACTOR=(LOOKUP(FL1005)>0.9&&LOOKUP(FL1005)<1.1)/%VALUE%,ACCOUNT=CL1000)
*ENDWHEN
*ENDWHEN

*COMMIT

[former_member186338 · Accepted solution]

Yes, COMMIT is used only for MDX syntax to submit data. For SQL COMMIT is doing nothing, only reset the scope to the initial script scope. In some cases you can use COMMIT when you want to skip some code if no members for some dimension.

[Former Member · Accepted solution]

thanks Vadim

[former_member186338 · Accepted solution]

Just read my comments here:

https://answers.sap.com/questions/174837/fx-trans-usign-xdim-filter.html

*XDIM_SKIPNULLCHECK CURRENT

[Former Member · Accepted solution]

thanks Vadim

just to understand: all the instruction starting with *XDIM are MDX code?

[former_member186338 · Accepted solution]

Wrong understanding!

*XDIM - scoping instructions not related to SQL or MDX

SQL:

WHEN/IS/REC/ENDWHEN

MDX:

[DIMNAME].[#MEMBER]=... "#" - target MDX member

Sample from help:

[P_ACCT].[#CE0661000] =[P_ACCT].[CE0004010]/[P_ACCT].[CE0652000]+[P_ACCT].[CE0004020]/[P_ACCT].[CE0652000]+[P_ACCT].[CE0004030]/[P_ACCT].[CE0652000]

also MDX:

*ADD / *ENDADD
*SELECTCASE / *ENDSELECT

[Former Member · Accepted solution]

thanks a lot Vadim