Forum Discussion

Yggdrasill's avatar
Yggdrasill
Responsive Resident
7 years ago

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)

Need Help
Show and Tell
Tips and Tricks

6 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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

    • Yggdrasill's avatar
      Yggdrasill
      Responsive Resident
      7 years ago

      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
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Yggdrasill  I know this is an old post, but I'm facing the same issue. Were you able to resolve it?

  • pwagner23AZ's avatar
    pwagner23AZ
    New Member
    5 years ago

    The "Extra" UM is not extra from SAP perspective, money comes with the currency as the UM.  For example, if you add Canadian $ and US $ together you do not get a real amount.     

  • mpi_gov_vn's avatar
    mpi_gov_vn
    Frequent Visitor
    5 years ago

    I know this is an old post, but I also have the exact issue. Were you guys able to handle it?