Siganvio Process Intelligence extraction - Der Open SQL command is too big

[santiagolc]

Hi

I'm trying to extract data from an S4 hana system. I'm trying to extact EKPO table which has lots of rows, the EKKO has 863.097 so the EKPO could have at least x4 more rows. I tried some strategies but always it's the same error. I'm doing what said the documentation: https://documentation.signavio.com/suite/en-us/Content/process-intelligence/getting-started-initial-...

Below the code that i'm using:

tableSyncConfigurations:
  alias: ekpo
  sql: |
    -- Your SQL extraction script --
    SELECT
      CONCAT(EKPO.MANDT, EKPO.EBELN, EKPO.EBELP) AS c_key,
      CURRENT_TIMESTAMP() AS c_fetchdate,
      EKPO.ABSKZ,
      EKPO.AEDAT,
      EKPO.BANFN,
      EKPO.BNFPO,
      EKPO.BUKRS,
      EKPO.EBELN,
      EKPO.EBELP,
      EKPO.EGLKZ,
      EKPO.ELIKZ,
      EKPO.KNTTP,
      EKPO.KONNR,
      EKPO.KTMNG,
      EKPO.KTPNR,
      EKPO.KZABS,
      EKPO.LABNR,
      EKPO.LEWED,
      EKPO.LGORT,
      EKPO.MANDT,
      EKPO.MATKL,
      EKPO.MATNR,
      EKPO.MEINS,
      EKPO.MENGE,
      EKPO.NETPR,
      EKPO.NETWR,
      EKPO.PLIFZ,
      EKPO.PSTYP,
      EKPO.UEBTK,
      EKPO.UEBTO,
      EKPO.UNTTO,
      EKPO.WERKS
    FROM EKPO
    WHERE EKPO.EBELN IN (
        SELECT DISTINCT EKKO.EBELN AS "ekko_keys"
        FROM EKKO 
        WHERE EKKO.AEDAT >= :partition_date 
        AND EKKO.AEDAT <= DATEADD('WW', 1, :partition_date)
        )
        
  keyColumn: c_key
  mostRecentRowColumn: c_fetchdate
  partitions:
    - name: partition_date
      values:
        - '2022-04-01'
        - '2022-04-08'
        - '2022-04-15'
        - '2022-04-22'
        - '2022-04-29'
        - '2022-05-06'
        - '2022-05-13'
        - '2022-05-20'
        - '2022-05-27'
        - '2022-06-04'
        - '2022-06-11'
        - '2022-06-18'
        - '2022-06-25'
        - '2022-06-30'

Below the error:

ExtractionJob failed
| source-system: SAP
| cause:
    Exception in Advanced Extractor occurred by
      com.signavio.pex.sap.exception.ConnectorException
        java.sql.SQLException
          Der Open SQL command is too big.
| sql-query: [-- Your SQL extraction script --
SELECT
  CONCAT(EKPO.MANDT, EKPO.EBELN, EKPO.EBELP) AS c_key,
  CURRENT_TIMESTAMP() AS c_fetchdate,
  EKPO.ABSKZ,
  EKPO.AEDAT,
  EKPO.BANFN,
  EKPO.BNFPO,
  EKPO.BUKRS,
  EKPO.EBELN,
  EKPO.EBELP,
  EKPO.EGLKZ,
  EKPO.ELIKZ,
  EKPO.KNTTP,
  EKPO.KONNR,
  EKPO.KTMNG,
  EKPO.KTPNR,
  EKPO.KZABS,
  EKPO.LABNR,
  EKPO.LEWED,
  EKPO.LGORT,
  EKPO.MANDT,
  EKPO.MATKL,
  EKPO.MATNR,
  EKPO.MEINS,
  EKPO.MENGE,
  EKPO.NETPR,
  EKPO.NETWR,
  EKPO.PLIFZ,
  EKPO.PSTYP,
  EKPO.UEBTK,
  EKPO.UEBTO,
  EKPO.UNTTO,
  EKPO.WERKS
FROM EKPO
WHERE EKPO.EBELN IN (
    SELECT EKKO.EBELN AS "ekko_keys"
    FROM EKKO 
    WHERE EKKO.AEDAT >= :partition_date 
    AND EKKO.AEDAT <= DATEADD('WW', 1, :partition_date)
    )
    

]
| query-params: {}
| anonymize-columns: []

[fprice]

Hi Santiago,

please create a ticket for dedicated error handling support via SAP.me.

The error means that the SQL result set is too big to be handled. You'll have to create smaller partitions. Usually, also SUB-Joins are not as performant. I'd personally recommend using the field EKPO.AEDAT as a partitioning field without joining the header documents (EKKO). It might deviate little due to the nature of the field, but in general should lead to 99.9% same values.

Two solution approaches:

a) use standard extraction via parameter EKPO.AEDAT as a partitioning parameter - usually nearly similar number of results

b) if you want to stick with the EKKO.AEDAT field, which I can also understand, I'd suggest creating smaller partitions, e.g. on a daily basis:

WHERE EKPO.EBELN IN (
SELECT DISTINCT EKKO.EBELN AS "ekko_keys" FROM EKKO
WHERE EKKO.AEDAT >= :partition_date 
AND EKKO.AEDAT <= DATEADD('WW', 1, :partition_date)
)

and insert:

WHERE EKPO.EBELN IN (
SELECT DISTINCT EKKO.EBELN AS "ekko_keys" FROM EKKO
WHERE EKKO.AEDAT >= :partition_date 
AND EKKO.AEDAT <= DATEADD('dd', 1, :partition_date)
)

You will then also have to adjust your partitions to daily.

Hope this helps.

Best, Fabian