Skip to Content
0

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

Feb 01 at 01:54 PM

97

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Dick Smith Feb 01 at 10:20 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
Jimmy Liang Feb 02 at 07:27 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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.

0
I H Feb 12 at 08:38 AM
0

Dear Ashraf,

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

Share
10 |10000 characters needed characters left characters exceeded