ArcherPoint Dynamics Developer Digest - vol 130
The NAV community, including the ArcherPoint technical staff—is made up of developers, project managers, and consultants who are constantly communicating, with the common goal of sharing helpful information with one another to help customers be more successful.
As they run into issues and questions, find the answers, and make new discoveries, they post them on blogs, forums, social media…so everyone can benefit. We in Marketing watch these interactions and never cease to be amazed by the creativity, dedication, and brainpower we’re so fortunate to have in this community—so we thought, wouldn’t it be great to share this great information with everyone who might not have the time to check out the multitude of resources out there? So, the ArcherPoint Microsoft Dynamics NAV Developer Digest was born. Each week, we present a collection of thoughts and findings from NAV experts and devotees around the world. We hope these insights will benefit you, too.
Slow Inventory Reports – Is Date Compression an Option?
A client that has over 10 million item ledger entries accumulated over a 10-year period approached Alan L. They cannot run any inventory reports in a timely manner. They have requested that we show them how to use date compression on the ILE table. I understand that this is not good practice. (Correct me if I am wrong, but Microsoft does not encourage this.) He asks the team, “What other options do we have?”
Darren suggests, “SQL Perform Data Archiving.”
Matt T. argues, “This falls into the performance issues area, and the first thing that is always suggested is data archival. That may or may not be the best thing. Ten-million records is nothing to SQL Server. More information needs to be gathered. What version are they running? What is their hardware setup? Which reports are they having issues with? Data archival isn’t cheap and in a lot of cases it makes sense to use that money on better hardware or a better reporting solution.”
Christine states, “One option would be for them to upgrade. The newer versions deal with this better and the upgrade will automatically reduce the database due to the dimension change. Darren is right regarding SQL Perform as an option. The upgrade team also has the ability to do date filtering on certain files to reduce the file size.”
Kyle H. notes, “It depends on which inventory reports. We (ArcherPoint) have used a SQL version of the Inventory Valuation and Inventory Reconciliation to G/L. These are not SQL Server Reporting reports, they simply use SQL for the valuation heavy lifting, populate a temp table, and then a normal NAV report does the presentation.”
Jon pipes in, “ILE cannot be archived (IN PART). It is all or nothing and even then, requires attention to the GL, Value Entries, etc. Ten-million is nothing. I’ve seen 180 million and even 1.4 billion with an adj. cost process taking a few hours. SQL Perform cannot compress ILE’s or Value Entries. SSD drives should be considered first, before any other discussion.”
Scott says, “I agree with the comments that we should first understand the need. Some companies think it’s just a good practice to delete old data after a period of time when it really isn’t necessary. In older versions of NAV issues popped up with date compression on ILE’s. I do not recall specially, but I believe examples would be things like choice of costing method along with using the location code, lot code, or serial number. We’ve always coached customers not to do this due to the risks.”
Finally, Chris warns, “I’m pretty sure that this would cause problems for Adjust Cost, at least for those items that have average cost as the costing method. I know that this was removed as an option in version 5.0 as there were a lot of problems with it. Back in my Navision US days, we told the resellers to stay away from it, it was bad news. I found this article which goes into some of the issues.”
Have you run into this issue before? How did you remedy it? Please share your experience in the comments below.
Leadership Lines
This quote from Simon Sinek on LinkedIn caught the eye of several of us here at ArcherPoint, enough to share with you, our Dev Digest readers:
“A culture is strong when people work with each other for each other. A culture is weak when people work against each other for themselves.”
And one more:
Figure 1 – Leadership Qualities
Stay abreast of what is new in the Microsoft Dynamics NAV community and at ArcherPoint by subscribing to our monthly newsletter, Better Business, by completing the form in our Resource Center.
And, if you are interested in NAV development, be sure to see our collection of NAV Development Blogs.