Can Date Compression be utilized for reducing database size?

niteshnav

Hi,

We have a typical retail scenario..HO & Shops...HO is running with SQL & shops are running with native database with regular replications...We are facing problems with shops growing data exponentially....Can date compressions be utilized to compress the native database? since the shops look for sales to the HO, can date compression for GL, Customer & vendor ledgers etc. be a useful feature of navision?

regards,
Nitesh

ara3n

how have you setup replication for the ledger tables? Are you posting in stores? I would not post in stores. Replicate the Statements in HQ and post them, and replicate the cust ledger, item ledger, value entry tables back to Stores. Also posting statements combines the items etc. Are you doing that?

Kowa

Date compression can be set to different period lengths ( day , week, month, etc.) so if e.g. only sales per month are required for the past years the item ledger entries can be compressed to one entry per month and location. This can effectively reduce the size of the database especially for ledger entries of fast-moving items. All fields values not retained are blank in the compressed entries, so check if any additional information is needed.

G/L entries and customer /vender ledger entries should only be compressed if no one in the financial department needs the detailed information anymore.

Take care of what field contents and dimensions are to be retained. Once compressed , this information can not be restored.

kriki

Before compressing, it can be a good idea to take a backup of the DB for savekeeping during the process and and even more for having a backup of the original entries in case the need arises to see them.

niteshnav

We are replicating only the Bank, Customer, Vendor, Item table. And we are running batch jobs on the NAS scheduler to import sales information.

Does date compression for the Ledger tables affect the HQ data after replication?

regards,
Nitesh

kriki

Date compression deletes ledger entries and creates new ledger entries that are a sum of 1 or more old ledger entries. With this you lose some detail. So if your HQ needs those details, you can't compress them.
Your HQ will probably get messages that some entries are deleted and others have added. Depends on how the replication works.

ara3n

Yes it will effect your headquarter. THe newly created entries will be replicated to HQ, doubling the balance.
You have to create another replication Job with a new counter that would delete the entries in HQ.

Please Test this in your test environment before doing it on live system.

niteshnav

I still didn't get a clear idea:
Condition: I am doing the Date compression only at the Stores & not the HO. Compression is done on following tables:
a. Customer Ledger
b. Vendor Ledger
c. Item Ledger
d. General Ledger
e. Bank Ledger

1. Does the date compression at Stores cause problem at HO on replication?
It depends on the Tables that are replicated from Stores to HO. If i am not replicating my ledger tables from stores, old entries are deleted & all new entries are created in ledger tables at STORES ONLY unless they are replicated to HO.

regards,
Nitesh

niteshnav

2. If i am replicating ledger entries as well, then i will have to create a new replication job with replication counter plus, create new entries?

regards,
Nitesh

kriki

If you don't replicate ledger entries : no problem.
If you do : date compression deletes some ledger entries and creates new ones. So if you replicate only the new ones, you figures will increase (or double in case you compress all entries). So you have also to replicate the deleted entries, meaning you have to delete them also in the HQ. If this is not possible, you have to delete all entries in the HQ can replicate all entries again from the stores.

kriki

What is growing fast (=how many GB per week, how many records in which tabels per week)?

Closing the fiscal year is maybe not necessary, because you can compress only data before the fiscal year.

You might also check the keys of the tables (e.g. Table 32 : only 1/3 of the tablesize is data and 2/3 is keys). Maybe you have a lot of keys and some of them are not used, or only used for 1 report. So if you redesign the report, you can delete the key.
You might also consider SQL. With SQL you can decide to NOT maintain a key or SIFT-index. (e.g. not maintaining big indexes (because SQL prefers NOT to use those), and creating new small indexes), gaining space and speed.