Skip to Content

How can improve the refresh time in WEBI ? Help!!!

Hi Experts,


actually i have one report in webi (SAP BI 4.2 SP04), this particular report have 33 millions records (3 years of data at less), the total time when refresh it is aprox. 10 min. using this parameters in universe:


Array fetch size: 1000 (we already test with increase this value at 2000 with no success)

Array bind size: 5000

parallel queries: 10

also we used

DISABLE_ARRAY_FETCH_SIZE_OPTIMIZATION = true

and its take 10 or 11 min., exist something that we can do for optimizing these refresh times ?

btw , we're using SAP IQ 16 as datasource and the report have just one query as follow (generated by webi query panel):

SELECT
Dim_Time.Year,
Dim_Time.Month,
datepart(dd,Modelo_Ventas.
FECHA),
Modelo_Ventas.TIENDA,
Modelo_Ventas.Des_Depto,
Modelo_Ventas.Des_SubDepto,
Modelo_Ventas.Des_Categoria,
Modelo_Ventas.Des_Marca,
Modelo_Ventas.Des_Grupo,
Sap_Compradores.Compradores,
Modelo_Ventas.Proveedor,
SAP_LFA1.name1,
Sap_T024X.LBTXT,
trim(convert(varchar(18),
convert(numeric(18,0),Modelo_Ventas.Material)))+' '+TRIM(Modelo_Ventas.maktg),
Sum(Modelo_Ventas.VTA_PVTA) ,
SUM(Modelo_Ventas.VTA_UND),
Sum(Modelo_Ventas.Ganancia)
FROM
Dim_Time INNER JOIN Modelo_Ventas ON (Modelo_Ventas.FECHA=Dim_Time.
GENERATED_DATE)
LEFT OUTER JOIN Sap_Compradores ON (Modelo_Ventas.DPTO=Sap_
Compradores.Cod_Dpto)
INNER JOIN SAP_IVMATERIAL ON (Modelo_Ventas.Material=SAP_
IVMATERIAL.MATNR)
LEFT OUTER JOIN Sap_T024X ON (Sap_T024X.LABOR=SAP_
IVMATERIAL.LABOR)
LEFT OUTER JOIN SAP_LFA1 ON (SAP_IVMATERIAL.KOLIF=SAP_
LFA1.lifnr)

WHERE
Dim_Time.Year IN ( 2017,2018,2016 )
GROUP BY
Dim_Time.Year,
Dim_Time.Month,
datepart(dd,Modelo_Ventas.
FECHA),
Modelo_Ventas.TIENDA,
Modelo_Ventas.Des_Depto,
Modelo_Ventas.Des_SubDepto,
Modelo_Ventas.Des_Categoria,
Modelo_Ventas.Des_Marca,
Modelo_Ventas.Des_Grupo,
Sap_Compradores.Compradores,
Modelo_Ventas.Proveedor,
SAP_LFA1.name1,
Sap_T024X.LBTXT,
trim(convert(varchar(18),
convert(numeric(18,0),Modelo_Ventas.Material)))+' '+TRIM(Modelo_Ventas.maktg)

thanks in advance, just let me know your recommendations or suggest.

Server details

OS= Windows Server 2012

RAM= 50 GB

HDD= 1 TB

Cores = 6

network between servers are in same segment. without any firewall or something that can be affect.

Regards,

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 23 at 01:44 PM

    Hello Carlos,

    I hope you have checked the performance tuning wiki page https://wiki.scn.sap.com/wiki/x/QCK7Gg

    It talks about all the possibility one can do to minimize the report performance. Have a look.

    At the same time, i agree with Jawahar " Webi is not meant for such huge data extraction."
    Webi is an ADHOC tool and is designed to analyze the data not to retrieve/store huge amount of data . This data is way too much for any reporting tool . Moreover, this is not the best way to design webi reports.
    Consider alternatives for delivering the same content in a couple, linked documents, using features such as query drill or prompts

    Thanks,
    Neha

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 21 at 02:53 AM

    you are pulling that many records to webi. Webi is not data dump tool. Use it as reporting tool. no one is going to look at 30 million records in the report.

    Add comment
    10|10000 characters needed characters exceeded