We have an Excel spread sheet which has horizontally all financial periods considered (13, one year plus one month) and vertically a list of article codes. The intersecting cells contain the sold quantity in that period of an article, using General Ledger transactions in Exact Online.
The list of article codes, stock quantity and sales is filled through the following query:
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
...
from exactonlinexml..items
order
by code_attr
With 750 articles and 13 periods, the General Ledger transactions are accessed thousands of times. Retrieval of General Ledger transaction information in Exact Online through the XML API is slow when looking for article transactions, since you need to download all transactions of the sales journal first, then filter out those without an article code (50%) and them sum the sales quantity.
Run time is 45 minutes and even worse when the Exact Online APIs are slow.
How can I improve the time needed to fill the spreadsheet?
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.