Date Compression

matias_jaure

Hello! I need to compress G/L Entry and Ledger Entry Dimension. We have a 80 GB SQL Database in NAV4.0 and these two tables have 23Gb each one.
This is because the deferral recognition that they used, now we are about to change this procedure and we want to reduce old data with the Data Compression functionallity. We only want to compress these two tables.
I run Date Compress but it run for three days and then gave a log overflow error. The log reach 100 Gb, may be I only need to make it bigger.
The problem is that I really don't know how to configure this compression, because Navision ask me to retain some Dimensions that I don't really care. Also I don't have any good documentation to read. Any advice could help us!... thank you very much for your time!

mstallmann

I would suggest running compression for shorter periods. You will need to run compression several times, but it should get you past the issues you are encountering.

DenSter

If more than half your database is made up by these two tables then those tables are probably very badly over indexed. What you need to do is tune the indexes first and see how big the tables are then.

matias_jaure

DenSter wrote:

If more than half your database is made up by these two tables then those tables are probably very badly over indexed. What you need to do is tune the indexes first and see how big the tables are then.

I am not sure how to do that, any advice? or any paper to read about it? I think that it could save a lot of space but I am not sure which index delete.

David_Singleton

matias_jaure wrote:

Hello! I need to compress G/L Entry and Ledger Entry Dimension. We have a 80 GB SQL Database in NAV4.0 and these two tables have 23Gb each one.
This is because the deferral recognition that they used, now we are about to change this procedure and we want to reduce old data with the Data Compression functionallity. We only want to compress these two tables.
I run Date Compress but it run for three days and then gave a log overflow error. The log reach 100 Gb, may be I only need to make it bigger.
The problem is that I really don't know how to configure this compression, because Navision ask me to retain some Dimensions that I don't really care. Also I don't have any good documentation to read. Any advice could help us!... thank you very much for your time!

Hi Matais,

As a freelancer, one of my great sources of work is to fix up a database that was damaged by someone using the date compression routines. These routines are designed to run on a totally unmodified database, but almost never run on a modified system. (Did you have any mods made to your System?)

In your case, follow Daniel's advise, you need to take a close look at your whole implementation, and how the data is being built, and how you are using it.

And yes you can read papers and search the internet, but where you are now, (46 Gig in 2 tables) your best solution is to get someone like Daniel to help you out.

matias_jaure

ok, May be you could help me to decide with this information:

In G/L Entry Table I have the following keys:

Key
Entry No.
G/L Account No.,Posting Date
G/L Account No.,Business Unit Code,Global Dimension 1 Code,Global Dimension 2 Code,Close Income Statement Dim. ID,Posting Date
Document No.,Posting Date
Transaction No.
Close Income Statement Dim. ID
IC Partner Code

and in Ledger Entry Dimension:

Table ID,Entry No.,Dimension Code
Dimension Code,Dimension Value Code


Any suggestion??

DenSter

Just those keys for the G/L Entry table? I was expecting more than that, are you sure your number of 23GB per table is right? Out of those keys I'd probably only redesign the third one. How about SIFT keys, do you have many sumindexfields in that table? There are probably lots of empty SIFT records.

The dimension table is probably huge because you are using many dimensions.

matias_jaure

DenSter wrote:

Just those keys for the G/L Entry table? I was expecting more than that, are you sure your number of 23GB per table is right? Out of those keys I'd probably only redesign the third one. How about SIFT keys, do you have many sumindexfields in that table? There are probably lots of empty SIFT records.

The dimension table is probably huge because you are using many dimensions.

Yes, just those keys. These are the SumIndexFields

for the key:
G/L Account No.,Posting Date
SumIndexFields:
Amount,Debit Amount,Credit Amount,Additional-Currency Amount,Add.-Currency Debit Amount,Add.-Currency Credit Amount

and for the key:
G/L Account No.,Business Unit Code,Global Dimension 1 Code,Global Dimension 2 Code,Close Income Statement Dim. ID,Posting Date
SumIndexFields:
Amount,Debit Amount,Credit Amount,Additional-Currency Amount,Add.-Currency Debit Amount,Add.-Currency Credit Amount

I have almost 38 million of rows in GL Entry table and a little more than 333 million of rows in Ledger Entry Dimension :shock: , may be it can explain the size

DenSter

yes close to half a billion records will increase the size of your tables for sure

I'd have to take a closer look at your table to know for sure why the G/L Entry table is so big and to see what would improve the performance. In itself the number of keys is not that big, you do have quite a few sumindexfields, but also not huge.

matias_jaure

I will try to explain why GL Entry is so big. Our client has to deferred sells. That is to recognized a part of the sell when the customer buys the product and then, each month, the customer pays the part belonging to the month and system has to recognize that part.
The people who managed the system before us decide to recognize each month creating an entry for each invoice line. creating thousands miles of entries each month. The process takes 3 hours if we are lucky.
So, we decided to change this way and, if it possible, we want to compress the old entries, or at least reduce the size of these huge tables.

I don't know if it is usefull for you, please let me know if you want something else

PerJuhl

Hi Matias

Did you get any further with this ?

For a start you should ONLY compress THE FIRST months.

Try to retain as few fields as possible, the more fields you
retain the less effective compression.

BR Per

pjc@kh.dk