search table error: [34104] Intermediate result is too large in CalculationEngine

[mspareek22]

Hi,

I have a calculation view and one of the tables has more than 2 Billion records. While executing the view in HANA the output of the view is 1050 records and is as expected but when the same view is used to push data in BW4HANA 2.0 SPS06 (CALC VIEW -> Data Source -> Info Object) I am getting an error "column store error: search table error: [34104] Intermediate result is too large in CalculationEngine"

With the assumption that the execution plan while triggering the calculation view from BW is different from the one in HANA I thought of providing SQL HINT (PREAGGR_BEFORE_UNION) in advance properties of the calculation view which did not work but when I changed the engine to SQL it started working as expected.

Although it worked I am still not sure WHY and if this is the correct approach.

Explain Plan

Execution Plan

Best Regards

Madhu

[ghoshsayantan3]

Hi Madhu,

A table containing 2B records ought to be partitioned and then filters according to the partition algorithm can be used.

Further more I am not sure how you used the hint in the advance view properties you can refer to Sap note 2509161.

Also you can drill down further to base table level in plan viz to the table to see if all 2B is getting pulled in .Or in case your able to replicate the failure scenario try to do vis plan nd see at which step its failing.As it may be during column search time too the intermediate result piled up to 2B.

Regards,

Sayantan

[mspareek22]

Hi Sayantan,

I think you are referring to EXPLAIN PLAN, but if you closely look into the execution plan you would see data is aggregated (distinct vendors from transaction data) and more importantly, after switching the engine to SQL in advanced properties the error is gone but I have no clue WHY.

Br

Madhu

[ghoshsayantan3]

Hi Madhu,

If you see the explain plan the records at Column table is 2B after aggregasion i.e. distinct its becoming 867 this process is also memory intensive.Further more if you see the viz plan image its using Bwpop which is the olap engine.Is at this step your query was failing?

After moving to sql engine is this step getting generated in the explain plan did you check?can you post both the plans here.(the failing one and the working one)

Unless you compare you will not be able to identify the change.The change will be the reason WHY it worked.

Regards

Sayantan