Calling the Inventory Experts

(0) Share

Report

Posted on by Heather Roggeveen

9,142

I am in the process of implementing inventory at one of my client sites. While they have not be "tracking" inventory as such, because of the way we were doing the billing, we had inventory quantity tracking turned on - even though no purchases / stocktakes / adjustments were being done.

Inventory and Cost of Sales have been going to the same GL code to have no GL impact.

We are now turning the inventory on fully - but I have a bit of a problem. We have 4 & 1/2 years worth of inventory transactions (sales) in the system. So my plan was to zero all the balances (with the Inventory and COGS offsetting still). Then enter opening balances with the GL all pointing to the correct place and away we go.

As part of that, I wanted to run Inventory Reconcile, Checklinks etc to make sure the "house is tidy".

This is where I hit my problem. The inventory reconcile took 44 hours to run - 42 of which were for a single product. I used the Manual Logging feature in the Support Debugging Tool (David Musgrave, you are a legend) to continually check on it. What I found was that the majority of that time was going through the records in IV10200 and IV10201.

There are 385700 records in IV10200 of which 280940 relate to one item and IV10201 is similar.

I am now doing a stock enquiry on that item (Enquiry --> Inventory --> Item Stock Enquiry) and it is taking ages to load.

So questions:

- Is this an unwieldy amount of data in these tables - do you have clients with similar data loads and do they experience this?

- Now that I have run inventory reconcile once, will it take that long every time?

- What is the difference with having Item History ticked or not?

- If this is an unmanageable amount of data - what options are there for tidying it? We don't want to lose the sales history, but that is held in sales tables - can I effectively archive data in these tables without impact?

Would love to hear your thoughts on this!

Cheers

Heather

*This post is locked for comments

All responses (16)

Answers (3)

Heather Roggeveen 9,142 on at

Like (0)

Report

RE: Calling the Inventory Experts

Hi Mahmoud

The year end process removed all those cost layers so I am fine with IV10200.

One of my other clients I have noticed has a date filter on the Item Stock Enquiry screen so this is probably a modification we did for them at one stage. I will probably look for a Dex or modified screen solution to help with performance if we need that screen.

Your work around is definitely an option for most sites so other reading this will find that useful. Unfortunately for this one, it won't be for the problematic item.

The problematic item is primarily sold from one site and has the majority of the transactions.

Thanks heaps

Heather
Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: Calling the Inventory Experts

Heather

This is how the item stock inquiry works, it retrieves data from IV00102 (Item Quantities Master) to be filled in the "Quantity on Hand", then all transactions are retrieved from IV30300 (Inventory Transactions Amounts History), this table is a huge and it has got all types of inventory-related transactions (in and out).

Work Around Solution:

If you wan a work around solution, you may try this trick, to "somehow" get a better performance of the item stock inquiry. Fill in the "Site ID" first, and then fill in the "item number", this way the generated SQL statement will be narrowed down to a specific site and it should retrieve the results faster.

Permanent Solution:

Now if you need to solve this permanently, you will have to remove records from IV30300. Of course, the remove history utility does that but it is NOT recommended since it will delete all records in this table. What I am suggesting is to remove the records of the closed cost layers in IV10200. If this is your option, let us know and we may proceed accordingly
Heather Roggeveen 9,142 on at

Like (0)

Report

RE: Calling the Inventory Experts

So finally all the processes are run and I have zero inventory levels.

I copied into test database and ran the year end close - easy, took no time at all.

After the close, IV10200 and IV10201 are clear (nice).

Item Stock Enquiry will still take forever to load but we may find a way around that.

We will probably consider archiving some data at some stage but I think we are now in a position to be able to effectively use the inventory.

Once we have both purchasing and sales data in the live system, I will play around with the remove transactions during year end close so we can see the impact and make some good decisions.

I will also try the PSTL inventory reconcile at some stage in the near future when we have transactions again.

Thank you all very much for your time on this one - especially your input Leslie which has been right on the money!
Verified answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: Calling the Inventory Experts

This is an absolutely interesting case, I am expecting you to spend good times with this case Heather Roggeveen, this thread has lots of valuable information to a certain point that you feel there is nothing more important left behind.

Although, I would throw some insight as well just for the sake of "contributing" !

Initially, according to your explanation of the case, such a reconciliation would definitely require such a long time to be performed, there are lots of associated tables and aspects to be considered, matched, recalculated and corrected (if needed), especially that there was items being tracked which add more complications to the reconciliation process. IV10200 and IV10201 are the primary tables of the reconciliation process, I would even consider them the "reference" for correction in several scenarios. The overall process can be summarized in the graph below:

Reference: Inventory Reconciliation Flow

I have worked on similar cases with inventory tables including above million of records, the savior has always been resetting the inventory and deploying the HITB (in case your client upgraded from older versions of Dynamics GP). Of course, once you reset your inventory, another considerable table will be added which is the SEE30303 only for the open cost layers, which can be determined from IV10200. Closed cost layers are not part of this process, and I am totally with @Lesli on her suggestion to remove these cost layers.

Eventually, for the archiving suggestion. It is definitely a necessity in case you were considering the suggestion above; omitting closed cost layers. In spite of the fact that the "Company Data Archive" is a great product, I do believe that; for this specific case, you can do it yourself. It requires considerable understanding of the database structure and specifically the inventory tables. We have recently worked on a similar project for a client willing to completely remove the history details for a specific year for all the modules (supply chain and financials) and keeping them archived in an archive company. The Company Data Archive has been an option but we finally have been able to develop a database solution for the same specific purpose.

Looking forward to hearing back from you,
Heather Roggeveen 9,142 on at

Like (0)

Report

RE: Calling the Inventory Experts

Totally agree Leslie - I have that product at a couple of my client sites. Highly recommend it to anyone wanting to look at archiving.

The real value I feel in that product is the ongoing archiving. Sure, we could achieve a first archive by simply copying the database and then using clear data to remove from the operational. But what happens when you want to append that data?

So I am all for archiving using Professional Advantages tool rather than any form of clear data.

Cheers

Heather
L Vail 65,271 on at

Like (0)

Report

RE: Calling the Inventory Experts

Heather,

An option to 'clearing' data is to archive it. There is a third-party product I have used called 'Company Data Archive' from Professional Advantage. It's the only product out there that does what they do. With this product you can create an archive company and move historical information from your production company to the archive company. You can continue adding transactions each year, so you really have something useable. Also, when you install this software, it adds a 'multicompany' option to your inquiry windows so that you can see that historical information anytime you want to.

I have many clients that have been on the software over 10 years and this product is incredible for them. I was an accountant in my first life, and I NEVER wanted to delete data. This product allows you to free up your production database, but still have the old information easily accessible.

Check it out: www.profad.com/.../company-data-archive

Kind regards,

Leslie
Heather Roggeveen 9,142 on at

Like (0)

Report

RE: Calling the Inventory Experts

Thanks Justin and Arunprasath.

I am pretty sure it is not the server itself causing issues. Reconcile on all the other items together took only 4 hours. It is ticking away at quite a fast pace, it is just the sheer volume of transactions.

I have considered the idea of clearing data somehow, but would not do it at this stage. I am not a fan of the clear data routines as they can break links in the database and create other issues. I would probably choose to create a new item code rather than clear data on the old.

Those options I would consider if after completing year end close etc the day to day operation of inventory is unwieldy.

Thanks for your feedback - I appreciate your time.

Cheers

Heather
Justin Thorp 2,265 on at

Like (0)

Report

RE: Calling the Inventory Experts

Hi,

I just wanted to throw this idea into the mix (it may or may not be applicable depending on your exact requirements):

1.) Make a backup of the company (essentially this is the archive)

2.) Run clear data on: "Inventory Goods Received" and "Inventory Transaction History" (Also Item Serial Number Master and Item Lot Number Master if you've been using Serials/Lots).

3.) Run reconcile on all items, it should be quite fast now, and this should set all quantities to zero.

4.) Enter/post opening inventory balances.

======================================

Of course, test all of the above thoroughly in a test company before implementing on live.

Thanks,
Justin
Community Member Microsoft Employee on at

Like (0)

Report

RE: Calling the Inventory Experts

Heather, my question below.

1. Is the data load issues occurs only in inventory module? How about AR, AP and GL?

2. What is your Sql server configuration?

Can you try recreating indexes for Inventory tables in you Test server? Sometimes indexes would have been fragmented. Just a thought.
Heather Roggeveen 9,142 on at

Like (0)

Report

RE: Calling the Inventory Experts

Wonderful - thanks so much Leslie. I have a way to go this week with getting the client up and running properly. Once I have any further relevant details, I will update for future readers and mark the appropriate responses as answers.

Will consider the implications of the purchase receipts - sounds like it might pay to keep some around for a while, although purchase returns are less likely in my clients line of business.