Let's Talk Cust Date Compression

Savatage

So reading this post:
viewtopic.php?f=23&t=53020

got me thinking about date compression again. Now, I have always avoided it on our live system because the concerns brought up by everything & everyone didn't seem like it was worth the possible headache.

We do have a 3.10 database with 3.7 exe's and a backup server with a new backup restored in it.
So I'll test on that!

I will run a Trial balance from the beginning of time to today.
I will run an inventory report (same date range)
and a detailed Aged A/r report.
first.

Then I will attempt "customer date compression" ONLY on one customer which get's approx 100,000 entries per month.
I will do it for 1 year (the oldest) and re-run all the reports again.
They should match.

I was just wondering where else might something or some area go wrong from anyone's experience or even hearsay.
Then I can put this curiosity i've had to bed. One compression at a time.
Thanks.

I don't even want to try G/L compression...YET :twisted:

bbrown

Savatage wrote:

...I don't even want to try G/L compression...YET :twisted:

I'd think the G/L would be the least problematic of the bunch.

Savatage

Really? That's the biggest table (28,042,010) so that's the one I worry about.

I can see how the Item Compression would be the scariest due the potential of screwing up costs on over 100,000 items. That would be total devistation!! [-o< and if the others work then there is no real need to compress that one.

As for easiest, I thought the customer one would be pretty easy due to it only having 3 choices
Invoice, Credit, Payment for us. that's why I'm trying that one first.

bbrown

I wasn't thinking about table size. I was thinking more in terms of data issues resulting from the compression.

David_Singleton

bbrown wrote:

I'd think the G/L would be the least problematic of the bunch.

As long as you don't compress entries generated from any of the Navision sub ledgers, Cust/Vend/Fa/Bank etc. Since they use the same number sequence and things go all screwy.

bbrown

Note that I said "least problematic". Not "problem free".


Anyways I'd not thought about that. But wouldn't new entries just start from the last entry number?

ara3n

if you are using warehouse management, I would go with getting rid of warehouse entry and register new whse journal for balance.
I did for a client on 4.0 without any issues.

David_Singleton

bbrown wrote:

Anyways I'd not thought about that. But wouldn't new entries just start from the last entry number?

With compression, new entries are generally OK, its the application to existing entries that general causes it to fall apart. Bits of the application process look for records that are not there, registers etc and weird errors happen.

The thing is that the compress entries routines were written in the DOS version of Navision, where they sort of worked IF you customized them to suit your application. They also sort of worked in versions up to 2.01, but by 2.6 when warehouse and manufacturing got involved things got bad. The real killer though was detail entries (Cust/Vend and Value) the tools should have been thrown out and started again.

Compression can be done, but not with the Navision standard tools.

As Rashed says, focus on things that can be safely DELETED (not compressed) like Warehouse.

hansika

ara3n wrote:

if you are using warehouse management, I would go with getting rid of warehouse entry and register new whse journal for balance.
I did for a client on 4.0 without any issues.

Very Interesting topic.

Could you explain the above process in detail. ?

Savatage

It obviously goes by the "Closed at Date" field..nice.

I don't like the report options - I think Customer should be the first dataitem not Just Cust Ledger Entry.
Because I would like to test it on Blocked=Yes customers. Customers we no longer do business with.

So I added dataitem:
Customer
-Cust. Ledger Entry
(DataItemIndent to 1)
(DataItemLink to Customer No.=FIELD(No.))

I moved the code from CLE to Customer (Unless you like clicking OK alot)

OnPreDataItem()
IF NOT
   CONFIRM(
     Text000 +
     Text001 +
     Text002,FALSE)
THEN
  CurrReport.BREAK;

Time to test the results....

Savatage

Cust Ledger Entries & G/l Enties compressed without issue.
Looks like 9 million enties crunched down to about 300.

I've looked at the ILE Compression and mine won't even compile. Looks like our nav partner from way back when attempted to "fix it" to allow it to retain Serial no & lot No's and never finished it. I commented out those changes (we don;t use serial & lots anyway). not worth running that report!

last test is to create a backup & restore it to make sure that goes smoothly, better safe than sorry.

Just a few years of compression 2003-2006 gained 12% of the database used back.

Anyone ever run "Delete Empty Item Registers"? What would cause emptry registers?

Alex_Chow

I wouldn't touch compression of ILE or VE if you value your data.

David_Singleton

Savatage wrote:

Anyone ever run "Delete Empty Item Registers"? What would cause emptry registers?

If you delete (or compress) Item Ledger entries, then you will have empty registers. Same with Cust Vand and GL. You need to make sure now tht all your empty cust registers have been deleted. Which is a problem because you can't delete them unless you also delete/compress the associated gl entries, and there is no link now form cust to gl (since the cust entry is gone) so how do you delete it. Also what happened to the Custe Detail entry?

Savatage

It's all being tested on a test server just for curiosity sake. Ile and ve are not going to be touched. As for cust detail I will check what the results are on Monday.

Dan77

Alex Chow wrote:

I wouldn't touch compression of ILE or VE if you value your data.

Same here: faced a migration of 2.60 Financials to 3.70 with compressed ILE and been through a nightmare! But it was really specific to the original version 'cause financials didn't have VE at the time and migration process tried to create these.

No need to say that item values was considered a priority to our customer (he did the compression, in spite of our recommendations, because he considered having payed for all existing features, including data compression)

What a mess :P

ara3n

hansika wrote:

ara3n wrote:

if you are using warehouse management, I would go with getting rid of warehouse entry and register new whse journal for balance.
I did for a client on 4.0 without any issues.

Very Interesting topic.

Could you explain the above process in detail. ?

Here is the link.

http://mibuso.com/blogs/ara3n/2012/05/1 ... use-entry/

Alex_Chow

ara3n wrote:

Here is the link.

http://mibuso.com/blogs/ara3n/2012/05/1 ... use-entry/

Done in SQL. Pure genius.

Savatage

Any speculation on the damage ILE compression would have if it's only done on Blocked (Discontinued) items.

Items that will never be in service again and qty = 0.

Could the process alone cause unforseen damage to active items?

Savatage

Results:
Item Compression Action taken:
-Ran Adjust costs
-Modified the Item Ledger Entry Compression report to include "ITEM" as the leading dataitem so I can control which items should be compresses.
-Ran 4 Stock status reports using Unit cost, Last Cost, Standard Cost & Direct Cost calulations.
-Ran report for 01/01/01/ to 12/31/07 & for BLOCKED items only.
Run time - 20 hours! 114,729 items
-Reran all stock status reports to verify that none of our 4 cost types have been messed up.
All Matched. Space saved - minimal

Customer Compression Action taken:
-Modified the Customer Ledger Entry Compression report to include "Customer" as the leading dataitem so I can control which customers should be compresses.
-Ran Aging reports & Trial Balance Detail/Summary Report
-Ran report for 01/01/01/ to 12/31/07 & for BLOCKED Customers items only.
Run time - 15 minutes
-Reran reports again to verify all balances remained the same
All Matched. Space saved - minimal

G/L Compression Action Taken:
-Ran Trial Balance Detail
-Ran report for 01/01/01/ to 12/31/07
Run Time - 4 hours
-Reran reports again to verify all balances remained the same
All Matched. Space saved - Decent!!!

Obviously some speed gains on trying to gaining access to these tables.
Native system.
It has given me some confidence that if I "HAD TO" do this on our live system it would work.
But it appears hitting the g/l really gives you the best bang for your buck if you are looking for space.
the customer & item compression gave a 2% saving each where g/l was 11% space saved.

60 GB Database - Starting size:85% used // Ending size:70% used
a backup & restore could probably pull another percent or two.

Alex_Chow

I know you guys are not current with the enhancement, but perhaps it's time to look into moving to SQL so you don't have to deal with this again.

Believe it or not, moving to SQL using version 6 executable actually shrunk the database size (from native).

Savatage

These were just test on a seperate server to see the results - also note that those compressions were done using period length "YEAR" and saving NO field contents.

we don't use bins or serial numbers - just the basic inv package.

Our plan has always been to use this one up and start fresh at some point on the newest version.
only transfering open qty's & balances.

This we can always keep the old system running incase we need to refer to it. It's how we did it when we switched from macola. And before you know it you're not visiting the old system anymore.
By 3 months needing info from the old system was minimal - by 6 months it was rarely and at a year - never.