Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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/09 | 12346798 | EUR | 14451 | EUR |
2018/10 | 132456789 | EUR | 65684132 | EUR |
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)
I know this is an old post, but I also have the exact issue. Were you guys able to handle it?
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.
@Yggdrasill I know this is an old post, but I'm facing the same issue. Were you able to resolve it?
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
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 |
99999 | EUR | 9898 | EUR |
Hi @Yggdrasill,
I would suggest you go to Forums/sqlserver/en-US/home?forum=sqlanalysisservices for professional support.
Best Regards,
Dale
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |