2862994 - multi-join BPC query hangs - SAP BPC Business Warehouse on SAP ASE

On SAP BW-BPC systems on SAP ASE database, a BPC query with more than 7 JOINs referencing BW temporary tables, never completes.

Status of the ASE process (spid)  is COMPILING  -- when checked via DBACockpit->Process Monitor or sp_who in isql.

Example query:

select "F"."KEY_UW6HTC" as "S____20890" , "H1"."PRED" as "S____20874" , "H2"."PRED" as "S____20876" , "H3"."PRED" as "S____20883" , "H4"."PRED" as "S____20878" , "H5"."PRED" as "S____20881" , "H6"."PRED" as "S____20875" , "H7"."PRED" as "S____20880" , "H8"."PRED" as "S____20889" , SUM ( "F"."/B28/S_SDATA" ) as "Z____041_SUM" , count( * ) as "Z____154_SUM"
from "/B28/FUW6HT" "F" join "/B28/DUW6HTP" "DP" on "F" . "KEY_UW6HTP" = "DP" . "DIMID"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000192" where SEQ_NR = 0 or SEQ_NR = 83024 union select SUCC , PRED , FACTOR from "/BI0/0200000193" where SEQ_NR = 0 union select SUCC , PRED , FACTOR from "/BI0/0200000195" where SEQ_NR = 0 union select SUCC , PRED , FACTOR from "/BI0/0200000196" where SEQ_NR = 0 ) "H1" on "F" . "KEY_UW6HT1" = "H1" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000162" where SEQ_NR = 0 or SEQ_NR = 83025 ) "H2" on "F" . "KEY_UW6HT3" = "H2" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000046" where SEQ_NR = 0 or SEQ_NR = 83026 ) "H3" on "F" . "KEY_UW6HT9" = "H3" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000063" where SEQ_NR = 0 or SEQ_NR = 83027 ) "H4" on "F" . "KEY_UW6HT5" = "H4" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000202" where SEQ_NR = 0 or SEQ_NR = 83028 union select SUCC , PRED , FACTOR from "/BI0/0200000065" where SEQ_NR = 0 ) "H5" on "F" . "KEY_UW6HT8" = "H5" . "SUCC" join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000067" where SEQ_NR = 0 or SEQ_NR = 83029 ) "H6" on "F" . "KEY_UHIW6HT2" = "H6" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000083" where SEQ_NR = 0 or SEQ_NR = 83030 ) "H7" on "F" . "KEY_UW6HT7" = "H7" . "SUCC"
join ( select distinct SUCC , PRED , FACTOR from "/BI0/0200000026" where SEQ_NR = 0 or SEQ_NR = 83031 ) "H8" on "F" . "KEY_UW6HTB" = "H8" . "SUCC"
where ( ( ( ( "F"."KEY_UW6HTA" = 3 ) ) AND ( ( "F"."KEY_UW6HT4" = 23 ) ) AND ( ( "F"."KEY_UW6HT6" = 13 ) ) AND ( ( "F"."KEY_UW6HTC" = 251 ) ) AND ( ( "DP"."SID_0CHNGID" = 0 ) ) AND ( ( "DP"."SID_0REQUID" <= 1072711 ) ) ) ) AND "H1"."SUCC" <> 2000008999 AND "H2"."SUCC" <> 2000008999 AND "H3"."SUCC" <> 2000008999 AND "H4"."SUCC" <> 2000008999 AND "H5"."SUCC" <> 2000008999 AND "H6"."SUCC" <> 2000008999 AND "H7"."SUCC" <> 2000008999 AND "H8"."SUCC" <> 2000008999
group by "H1"."PRED" ,"H6"."PRED" ,"H2"."PRED" ,"H4"."PRED" ,"H7"."PRED" ,"H5"."PRED" ,"H3"."PRED" ,"H8"."PRED" ,"F"."KEY_UW6HTC"
order by "S____20874" , "S____20876" , "S____20883" , "S____20878" , "S____20881" , "S____20875" , "S____20880" , "S____20889"
PLAN '(use optgoal sap_olap) (use fact_table F) (use parallel 4) (prop F (parallel 4))'

• SAP Adaptive Server Enterprise (ASE) 16.0 SP03 for Business Suite
• SAP Business Warehouse - All versions
• SAP BusinessObjects Planning and Consolidation for SAP Netweaver - All versions

BPC, COMPILING, slow, hangs, 810688, CR#810688, CR810688, slow , KBA , BW-SYS-DB-SYB , BW on Adaptive Server Enterprise , Problem