topic Re: Using the tSAPBWInput component and writing MDX Query in Talend Studio

Using the tSAPBWInput component and writing MDX Query

Anonymous — Wed, 04 Apr 2012 16:53:06 GMT

I need some help or assistance! I'm on TOS 5.0.1 and using the tSAPBWInput component to execute a MDX Query (see bleow) via jdbc4olap/xmla on SAP-BW - Multiprovider/BW query. In the query the multiprovider is SD_M015 and the BW query is ZDAR_1.
My issue is how do I suppress or exclude the "nulls" or empty cells that are returned in the result set? More than two-thirds of the returned data is nulls in the measures and depending on the amount of data returned it causes a short-dump on the BW server as it tries to return over 4G of data. Regular MDX query has a "NON EMPTY" keyword to remove empty tuples from a results set.
How can I use the NON EMPTY keyword or a similar keyword in the query below?
FYI... I tried an expression, T0.\".\" <> 'null' this worked until I tried to retrieve lower level data and then I got a java nullpointer exception!

"SELECT
T1.\".\" AS C0 ,
T3.\".\" AS C1 ,
T4.\".\" AS C2 ,
T2.\".\" AS C3 ,
T5.\".\" AS C4,
T0.\".\" AS C5,
T0.\".\" AS C6,
T0.\".\" AS C7,
T0.\".\" AS C8,
T0.\".\" AS C9
FROM \"SD_M015\".\"SD_M015/ZDAR_1\".\"\" T0,
\"SD_M015\".\"SD_M015/ZDAR_1\".\"\" T1,
\"SD_M015\".\"SD_M015/ZDAR_1\".\"\" T3,
\"SD_M015\".\"SD_M015/ZDAR_1\".\"\" T4,
\"SD_M015\".\"SD_M015/ZDAR_1\".\"\" T2,
\"SD_M015\".\"SD_M015/ZDAR_1\".\"\" T5
WHERE T2.\".\"='2012 Spring Season' AND
T5.\".\"='Corn' AND
T1.\".\"='AAAA-US-Production'
"
Thanks for your help!

Re: Using the tSAPBWInput component and writing MDX Query

Anonymous — Thu, 05 Apr 2012 09:02:44 GMT

Hi
As you say, why don't you use Non empty here?

SELECT
  { ., 
    ., 
    . 
  } ON COLUMNS, 
  NON EMPTY 
    .MEMBERS 
  ON ROWS
FROM  
WHERE 
  ( ., 
    . )

Regards,
Pedro

Re: Using the tSAPBWInput component and writing MDX Query

Anonymous — Fri, 06 Apr 2012 00:14:08 GMT

Thanks for response Pedro! Unfortunately, the MDX Query you included does not work in the tSAPBWInput component. I tried the "regular" MDX query like you included and it errored out. It only works with this hybrid sort of MDX query as you can see from the sample I included. It is very odd to me! I don't understand what sort of MDX this is! It has been difficult to find what works and what doesn't, but only by trial and error. I was able to get my query to work but I can't figure out how to suppress the empty rows.
Thanks again for your assistance.