Learnings on SAP BI Analysis Process Designer APD to extract a query result to a file

While there are several limitations on the extraction process from a query to a file, the benefit is that you get a result extracted quite fast as compared with some other methods. Once valid concern is that if the query results in a large dataset you can face issues, but there are some workarounds to overcome that limitation, such a splitting into several APDs or setting the query element to divide the data collection into Packages, in the Extended Settings for the element. Here are some of my learnings:

• Unless you use a logical path / filename, you cannot specify a file termination, the APD check fails explaining that file names may only contain the characters A-Z, 0-9 and _.
• If you have a column in the report that is set as hidden, it will be extracted regardless of its show/hide status.
• The order of the fields on the file for a report I used with characteristics in rows and key figures in columns: it listed first all key figures in the file and then all characteristics: it did not follow the order of the key figures in the BEX report, it sorted the key figures by description and that was the order. For the characteristics it did respect the order as specified in the BEX report.
• The format of the key figures is ignored: for restricted key figures it used 3 decimals and for formulas / calculated key figures it used scientific notation. Also, for the restricted key figures with unit it added a column for the unit and for the calculated key figures / formulas it did not add a unit column.
• If you need to debug the extraction to understand it a little bit better, use the context menu on the query node and select the option display data after setting a breakpoint at method CL_RSCRMBW_BAPI->GENERATE_MDX.
• A way to overcome all these limitations regarding formats, column order, etc., would be to insert a routine between the query source element and the file target element. Then you can select what columns to pass as a source, removing for example the unit fields, you could also set the order of the fields and for fields the APD could not determine an infoObject for reference (hence the floating point type), you could use standard KF InfoObjects such as 0AMOUNT and 0QUANTITY.
• The BEx report cannot have display hierarchies neither as filters nor as elements in the report (rows or columns).
• If you have key figures showing inside key figures (expand / collapse), you will also get the warning “RSCRM 252 does not support display hierarchies” like in the image below.

• As an example of an MDX generated query, see below

SELECT   {
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[7ZYK7HLNG4UC6JD3PY2AH9IQK],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8AM6Y5EMB1JOWE9WS8LWOVQJG],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8CQX3HD7OFAD1KGGZWBFJ7DP8],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8EVN8TBT1T116QN17K0YDJ0V0],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[823ACTK8TIL0BPJNXLRTBL5WC],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[86CQNHHFKA2CM1WSCX6V08G7W],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8H0DE5AEF6RPBWTLF7QH7UO0S],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8J53JH8ZSKIDH305MVG026B6K],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8L9TOT7L5Y91M96PUJ5IWHYCC],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8NEJU566JBZPRFDA26V1QTLI4],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8480I5IU6WBOGVQ859HC5WT24],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8PJ9ZH4RWPQDWLJU9UKKL58NW],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[88HGSTG0XNT0R83CKKWDUK3DO],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8RO04T3DA3H21RQEHIA3FGVTO],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8TSQA51YNH7Q6XWYP5ZM9SIZG],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8VXGFH0K0UYEC43IWTP544658],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[8Y26KSZ5E8P2HAA34HENYFTB0],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[92BMVGWC506ERMN7JSTPN33MK],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[94GD0SUXIDX2WSTRRGJ8HEQSC],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[96L364TIVRNR1Z0BZ48RBQDY4],
[7VP3WTOGPDCZW6ZZAMN8SM8F0].[98PTBGS495EF756W6RYA6213W]}  ON COLUMNS,
NON EMPTY CROSSJOIN(CROSSJOIN({
[0MATERIAL].LEVELS(01).MEMBERS},
{[CRMSET_0PLANT]}),
{[0MATERIAL__0PRODH2].LEVELS(01).MEMBERS})
ON ROWS FROM [-yourProvider-/-yourQuery-]
SAP VARIABLES [-versionDate-] INCLUDING
[-calDayVar-].[20141112]
[-UOMVar-] INCLUDING  [0UNIT].[CS]
[-PlantVar-] INCLUDING [0PLANT].[plantLow]:[0PLANT].[plantHigh]