cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Yggdrasill Member
Member

SAP BW MDX Query and Measure properties in Power Query

When I query this MDX statement 

with member [Measures].[Revenue] AS [Measures].[TECHNICALNAME1]
     member [Measures].[Total Cost] AS [Measures].[TECHNICALNAME2]     

select {[Measures].[Revenue],
        [Measures].[Total Cost]} on columns,
non empty
{[YEARMONTH].[LEVEL01].Members} on rows
from [CUBEtechnicalname]
where [YEARMONTH].[2018/09]: [YEARMONTH].[2018/10]


on SAP BW Cube, I get this result

 

Year/Month | Revenue| UNIT_OF_MEASURE | Total Cost | UNIT_OF_MEASURE

2018/0912346798EUR14451EUR
2018/10132456789EUR65684132EUR

 

Why on earth are do I get extra columns with Unit of measure, and how do I get rid of it in the query statement (not Power query editor)

3 REPLIES 3
Community Support Team
Community Support Team

Re: SAP BW MDX Query and Measure properties in Power Query

Hi @Yggdrasill,

 

I have consulted an expert. The answer is the [YEARMONTH].[LEVEL01].Members is the source of the unwanted columns. Please check if it's a user hierarchy and what it has.

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yggdrasill Member
Member

Re: SAP BW MDX Query and Measure properties in Power Query

Thanks for taking the time Dale.
However if I take out the [YEARMONTH] mentioned dimension I still get the unwanted columns with UNIT_OF_MEASURE after each and every "Key figure" (measure) which is defined in SAP BW.
I don't think the dimension has anything to do with the unwanted UOM columns I get for every measure I drag in the query. I think this has something to do with the measure property rather than the dimension property.
Question is, what do I have to do with the measure, to only get the value and NOT the UOM value.

Here's where I'm at now

WITH Member [Measures].[Revenue] AS [Measures].[00O2TQDIHUNPIH5VRVH89Z3HR] 
Member [Measures].[Cost] AS [Measures].[00O2TQDIHUNPIH5VRVH8A1WMN]
SELECT NON EMPTY
{ [Measures].[Revenue],
[Measures].[Cost], } ON COLUMNS,
NON EMPTY
{ {[ZPERIOD].[LEVEL01].Members} *
{[0COMP_CODE].[LEVEL01].Members} *
{FILTER([0PROFIT_CTR].[LEVEL01].Members, [Measures].[Revenue] <> 0 ) } *
{[0PAYER].[LEVEL01].Members} *
{[0SHIP_TO].[LEVEL01].Members} *
{[0SOLD_TO].[LEVEL01].Members} *
{[ZLOADCTYF].[LEVEL01].Members} *
{[ZDISCTYF].[LEVEL01].Members} *
{[ZLOADPRTF].[LEVEL01].Members} *
{[ZDISPORTF].[LEVEL01].Members} *
{[ZTLCLD__ZTLCLDG].[LEVEL01].Members} *
{[ZTLCLD__ZTLCLDC].[LEVEL01].Members} *
{[ZLOGSETY].[LEVEL01].Members} }
DIMENSION PROPERTIES
[techname].[tech name for key],
[techname2].[tech name2 for key] ...
ON ROWS
FROM [cube tech name]
WHERE [ZPERIOD].[2018/09]: [ZPERIOD].[2018/10]

 In return I still get that extra column 

[Measures].[Revenue][Measures].[Revenue].UNIT_OF_MEASURE[Measures].[Cost][Measures].[Cost].UNIT_OF_MEASURE
99999EUR9898EUR
Community Support Team
Community Support Team

Re: SAP BW MDX Query and Measure properties in Power Query

Hi @Yggdrasill,

 

I would suggest you go to Forums/sqlserver/en-US/home?forum=sqlanalysisservices for professional support. 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.