The run time was reduced to a few minutes for the indicated data volume by using a trigger on model level. This trigger fires a SQL statement that uses the Exact Online REST API to retrieve summed quantities of GL Transaction information:
create or replace table itmrev@inmemorystorage
as
select itemcode
, mnd
, sum(qty) qty
from ( select itemcode
, year(date) * 12 + month(date) mnd
, quantity qty
from TransactionLines
where journalcode = '70'
and itemcode is not null
and date > to_date('20160101', 'yyyymmdd')
)
group
by itemcode
, mnd
Then, instead of using the Excel I_EOL_GLTXN
formula, I've used the following formula:
select code_attr
, '=i_eol_itm_description(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' rownum
, '=I_EOL_ITM_SALES_PRICE_VALUE(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' dateend
, '=I_EOL_ITM_COSTS_PRICE_VALUE(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' datestart
, '=I_EOL_ITM_SALES_UNIT_DESCRIPTION(eoldivision,$C{D,.,.,^,.},"ALL_ROWS")' assortment_glrevenue_code_attr
, '=$C{D,.,.,.+2,.}-$C{D,.,.,.+1,.}' assortment_glrevenue_type_attr
, '=0' assortment_glrevenue_balanceside_attr
, '=I_EOL_STOCK_CURRENT_QUANTITY(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' ispurchaseitem
, '=I_EOL_STOCK_PLANNING_IN(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' issalesitem
, '=I_EOL_STOCK_PLANNING_OUT(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' isstockitem
, '=I_EOL_STOCK_PROJECTED_QUANTITY(eoldivision,$C{D,.,.,^,.},,"ALL_ROWS")' assortment_glrevenue_balancetype_attr
, '=$C{D,.,.,.-1,.}*$C{D,.,.,.-5,.}' assortment_glrevenue_description
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & m$11)' assortment_glcosts_code_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & n$11)' assortment_glcosts_type_att
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & o$11)' assortment_glcosts_balanceside_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & p$11)' assortment_glcosts_balancetype_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & q$11)' assortment_glcosts_description
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & r$11)' assortment_glpurchase_code_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & s$11)' assortment_glpurchase_type_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & t$11)' assortment_glpurchase_balanceside_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & u$11)' assortment_glpurchase_balancetype_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & v$11)' assortment_glpurchase_description
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & w$11)' assortment_glpurchasepricedifference_code_attr
, '=i_sql_select_scalar("qty", "itmrev@inmemorystorage", "itemcode=' || chr(39) || '" & $C{D,.,.,^,.} & "' || chr(39) || ' and mnd = " & x$11)' assortment_glpurchasepricedifference_type_attr
, '=SUM($C{D,.,.,.-12,.}:$C{D,.,.,.-1,.})' assortment_glpurchasepricedifference_balanceside_attr
, '=$C{D,.,.,.-1,.}/12' assortment_glpurchasepricedifference_balancetype_attr
from exactonlinexml..items
order
by code_attr
The i_sql_select_scalar
retrieves the Exact Online sales quantity from the small table with totals.
The addition of all_rows
as execution hint to formulas such as I_EOL_STOCK_CURRENT_QUANTITY
enforces that all items are loaded into memory as one big batch on the first formula evaluated. Although this takes longer to calculate the first formula, it is over all formulas significantly faster. Instead of 750 retrievals of an individual item (XML API sometimes takes up to 600 ms each), it now takes approximately 1 minute for the item information to be filled out in Excel.