Skip to Content

How to deal with financial reports with massive data on CR for Business One?

What can we do to improve the performance of financial reports that has to pull technically all the data in the db with millions of records? I've seen reports take up to 4-5 hours of runtime on a procedure in a hana database.


One thing that always cause troubles is opening balances, if you select a date like 1.1.18 so the report has to pull everything before that and perform calculations on it.


Any tips would be appreciated,

Thanks.

Ashraf

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Feb 01 at 10:20 PM

    Hi Ashraf,

    You can consider a data warehousing solution to overcome run time overload. Instead of calculating opening balances on the fly, create a table in a separate database and update this table daily/weekly depending on you reporting requirements.

    Cheers

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 02 at 07:27 PM

    Hi Ashraf,

    Based on what I learned about hana - columnar-stored, in-memory computing, and capable for OLTP and OLAP within one database; it is designed and supposes to handle the scenarios like yours well. However, if your the query or procedure still takes hours to run on hana, that means there is really a LOT of data.

    Another thought is about the query or procedure you mentioned, is that optimized for hana?

    I don't have much hands-on experience about hana data modeling, but your idea of using a calculation view seems helpful. You can make hana only look at the opening balance column so the time consumed to get the running total of open balance should be reduced, as a benefit of hana's columnar-stored structure.

    Cheers,

    Jimmy

    Add comment
    10|10000 characters needed characters exceeded

    • Actually i think it the view and procedure were originally made for SQL Server and translated to work on HANA. So i guess it's not really optimized for HANA.

  • Feb 12 at 08:38 AM

    Dear Ashraf,

    If you are using SAP 32 bit, you mat try change to SAP 64 bit.

    Add comment
    10|10000 characters needed characters exceeded