Not enough temp space for generate report in BO

[former_member585177]

Hi!

I have a strange problem with generating the report in BO Webi. The report is very simple, its based on 3 dim tables and 1 measure from fact table. When I press Start query, BO tries return data until the temporary space has run out. I declared 200 GB for temp space.

It takes about 8 hours and after the error occured. I tried to copy that same query and I ran it directly in Toad. So what? It takes only 10 minutes and max 2 GB from temp space.
In business layer I set projection function as delegated for the used measure.
Any idea how to resolve my problem?

[Joe_Peters]

Sounds like it's generating a cartesian product. This is Oracle? I would suggest using TOAD's Session Browser to watch the query as it's being executed from BO. It's possible that this will be different from what you see in the query panel, and may give a clue as to what's wrong.

Also, when you execute it in TOAD, you may only be seeing the first few rows of the result set.

[former_member585177]

Thanks for reply. Yes, it is Oracle. I checked current statement in Session Browser for BO session and the query is the same as in the query panel.

The result set in Toad, it's 289 rows.

[amitrathi239]

can your share the Webi query SQL?

[former_member585177]

There you go:

SELECT
  Table__190.KOD_PROD,
  Table__3.SERIA_POLISY,
  Table__3.NUMER_POLISY,
  Table__3.DATA_OD,
  Table__3.SERIA_POLISY || ' ' || TO_CHAR(Table__3.NUMER_POLISY),
  Table__263.KOD,
  Table__263.OPIS,
  Table__5.OPIS_ODD,
  Table__138.KOD,
  SUM(Table__160.KWOTA)
FROM
  HDL.DM_PRODUKTY_D  Table__190,
  HDL.DMUB_D_POLISY  Table__3,
  HDL.DMUB_D_SKLADKI  Table__263,
  HDL.DM_JEDNOSTKI_ORG_D  Table__5,
  HDL.V_DMUB_D_DEKRETY  Table__138,
  HDL.DMUB_F_DEKRETY  Table__160,
  HDL.DM_DNI_D  Table__139
WHERE
  ( Table__160.DATA_DOK=Table__139.DZIEN  )
  AND  ( Table__160.DMUB_DKR_KOD=Table__138.KOD  )
  AND  ( Table__190.ID=Table__160.DM_PROD_ID  )
  AND  ( Table__3.PL_ID=Table__160.PL_ID  )
  AND  ( Table__263.ID=Table__160.DMUB_SKL_ID  )
  AND  ( Table__5.ID=Table__3.DM_JED_ORG_ID  )
  AND  
  (
   TO_CHAR(Table__139.DATA_MSC,'yyyy.mm')  IN  ('2018.01','2018.02')
   AND
   Table__138.KOD  IN  ('PRZ_FUN','PRZ_OCH')
   AND
   Table__190.KOD_PROD  IN  ('EXTR_D2')
  )
GROUP BY
  Table__190.KOD_PROD, 
  Table__3.SERIA_POLISY, 
  Table__3.NUMER_POLISY, 
  Table__3.DATA_OD, 
  Table__3.SERIA_POLISY || ' ' || TO_CHAR(Table__3.NUMER_POLISY), 
  Table__263.KOD, 
  Table__263.OPIS, 
  Table__5.OPIS_ODD, 
  Table__138.KOD

[amitrathi239]

are you running from Rich client or BI Lanuch Pad?

[former_member585177]

I have been running from Rich client, but I can try from BI lunch pad if is there any diffrence?