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

[ashrafsharaf]

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

[former_member419030]

Dear Ashraf,

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

[jimmyl]

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

[ashrafsharaf]

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.

[former_member230931]

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

[ashrafsharaf]

will a calculation view in hana perform better than the regular SQL view in this case?