ODBC output versus Dage Report Output

[gazmoz17]

Hi Bruce,

Is a report via ODBC into excel always going to be slower than directly output from Sage natively? (as in just normal report output within sage itself)

Is this because:

Program/system wise its got to wait for the odbc driver/dsn pull from the data tables into power query then run wahtever steps/transformations?

But also for groupings in power query?...

...eg Report "Product Sales by Stock Category"

Will this always be slower in power query because it has to list all the individual Product itemised prods then have a secondary "Group by step" to group them via Stock category.

[*]Same principle for cost price per invoice item and overall cost price per invoice?

I dont think theres a data field for total cost price per invoice item line is there or even for invoice cost price overall? So in power query Im merging invoice, invoice item and stock table (for lastcostprice). To give me itemised cost prices (on each invoice item line) and then summing this with a last Group by transformation....to give me a total cost of each invoice and from there (ref that new custom cost col) to give me profit etc with custom columns. Or is there a quicker more succinct step than this to reduce my transformations and speed of query.

Many Thanks

[brucedenney]

The sage report designer, extracts data form sage using the ODBC drivers, so performance should be comparable.

The drivers are slow.

What you do with PowerQuery may be complex and slow things down even more.

A popular solution, where data does not have to be up to the moment, is to cache the data in a different more reliable database and then run SQL queries against that