Our customer, using Dynamics NAV 5.0 with native database, has a database size of 68 GB after 1 year of operations.
The following tables are occupying most of the database sapce.
Sl.No. Table No. Of records Size
G/L Entry 4148142 10 GB
Item Ledger Entry 1048735 3.5 GB
Item Application Entry 1556504 4 GB
Ledger Entry Dimension 113092579 17 GB
Posted Document Dimension 37809783 7 GB
Analysis View Entry 41829535 11 GB
Change Log Entry 4216708 1.5 GB
Customer’s database has lot of dimensions.
Each items have approx. 10 dimensions with value posting as same code, and no. of transactions done on items are high.
There are about 160 analysis views, with update on posting ticked.
On average every day about 125 no. of sales orders and credit memos are posted with 30 items on each document.
On average, there are about 7000 of inventory transactions entered on the system.
All dimensions are selected by default on transactions, and analysis views are updated on postings.
The increase in database is alarming, and we want to reduce the rate of increase of the database size. Please suggest, what steps can be taken to reduce the database size ?
With current hardware capabilities tenths of Gigs is not so much - there are Navision DB’s out there reaching hundreds of Gigs [:)]
In next years of operation you will be able to “compress” previous FYs data, thus slowing the growth of DB size.
What worries me more in your description, are these 160 Analysis wievs, updated on posting. How about performance? That much AW updating on the fly takes time, and can lead even to frequent locks of underlying tables, slowing performance dramatically. Maybe you may consider leaving part of them to update manually before users performing analysis need them? Again, in the following years you may set up AWs with Starting date of current FY, this will save space, too.
Besides, you might revise the ChangeLog setup - are you really logging only needed changes? ChangeLog, if set up to log actually unnecessary info, can grow in geometrical progression…
Well, I doubt that all Dimensions and Analysis Views are really needed - I recommend to reduce the amount as much as possible. NAV is an ERP system, not a BI thing - you should look into alternative solutions for analysis & reporting.
Selling a native NAV system one year ago was IMHO not at all a smart thing to do; I guess there have been reasons … anyway, I suggest to look into changing to SQL Server. The sooner, the better, as scalability with native C/SIDE is actually quite limited …
Also, if your customer needs “extended” functionality in A & R then SQL Server’s features might be the better choice …
I don’t even know what to say here. Of course I would like to concur with Modris and Jorg. You need to move to SQL and there is no way you should have allowed the customer on Native with a database like this.
But getting to Modris’s comments, you need to take good note of this. This is very far from an optimal implementation, as the Navision partner, it is your responsibility to know what you are selling and to show the customer the optimum way to solve their business issues using Navision. If you are not ready to implement a big system like this, then start small, or do a joint venture to get someone to help you implement.
I see that now you are going to have to learn to do it the proper way at your clients expense, and I will bet that there is no budget left to now fix this.