Over time your database size can grow quite large, depending on you use AX / Finance and Operations. Let's talk about trimming down.

Some of you may not know that I enjoy weight lifting in my spare time. I'm not especially good at it but its still a hobby I enjoy. I thought it would be fun to talk about our AX / F&O databases like they are a body builder. Typically people looking to enhance their physique go through cutting and bulking phases. A cutting phase, or a cut,  is where they are trying to lose fat and bulking phase, or a bulk, is where they are trying to add lean muscle. Typically with a bulk, you are adding some fat as well, hence the need for a cutting phase. You can think of this as an addition period then a cleanup period. If you're here, your database is currently in a bulking phase and you're in need of how to do a cutting phase. it's entirely possible that your database has been only bulking for the past 8 years so you have lots of cutting work to do to get rid of some of the fat. We're going to go over how to do a database cut, and also prevent excessive bulking. We're also going to keep things punny with as many puns related to mass, gaining, flexing, various muscles and all around weight lifting terminology. I'll include links to definitions as needed. We're going to focus on a standard push-pull-legs split and also have a little fun.

In The Begaining...

Your AX / F&O database was created. It was a little database with so much promise and potential. It was given the power rack and the Olympic platform ( as well as master data to process transactions ). The great system administrator said, "let there be 45 pound plates" and so they were, separated into iron and rubber plates, much like legal entities and organizations. Then, a great bar was added to hold the weights so the database could lift the plates, much like master data. Each set of dead lift and squat added gainz in master data, the lower body of the system. So, we had the legal entities, organizations and misc. master data. The first lifting session, the first phase complete.

Next, having a solid foundation, we added transactions to provide symmetry to the system - to add separate lifts for the upper and lower body. We are pushing in as many transactions so we can with great volume and intensity. We add two great lifts: the bench press and overhead press - the sales and purchase orders. We flex our sales order with great pride, to show our swoliness but still attend to our purchase orders as the are important but less prestigious in the eyes of others. So, we have the legal entities, organizations, master data, sales and purchase orders. The second lifting session, the second phase complete.

Finally, having great strength to lift things from the ground - master data, and great strength to buy and sell, we need to report on our activities to others. We now need to pull data from the system so there is a final lift added. In order to pay homage to our data, we use it as weight and pull with great power and technique. The pull up was added - the reports were added. The final lifting session, the third phase complete.

Extreme Allegory Over

The amount of master data that we have over time will only ever go up. There isn't much that we can really do with it as it's the basis for nearly all of our transactions. Although it is very important, that's an area we will only ever gain in traditionally. Always getting gainz in the power cage for squat and deadlift is a good thing. Where we can focus is our transactions and how we report on that transactions - the push and pull. Now, rather than show this as an abstract, I have a (large) AX 2012 database given to me by a client that will be used to illustrate how we can trim down / do a cut on our database and only get rid of things we need. We'll also be tracking our statistics from the beginning to the end. We'll be applying the process laid out below to that database and recording metrics to see what areas we get the best results in. 

Process (The Story of Gain and Wheybel)

We are starting with an AX 2012 R3 CU12 Database with 17+ companies by only 4 are of major significance as all of the other companies are largely for statutory reporting in some form or another. This client does buy, make, sell and retail sale operations globally all in one instance. we'll be running the process below, plus a few others, and tracking the impact for each. The list below has links to the articles that highlight how to run some of the OOTB cleanup functions in AX 2012 R3 / D365 F&O and is ordered in the way that I think makes the most sense to execute. 

General Ledger

First, we're going to clean up our ledger journals. Please check out this for additional details. This will clear out posted journals. 

Procurement and sourcing

Next, we're going to move onto Procurement and Sourcing. There are 2 cleanup routines available for both AX 2012 and Dynamics 365 for Finance and Supply Chain. Depending on your legal entities and what functionality they use, the request for quotations functionality may not offer much value in terms of storage usage savings. We'll start with the purchase order history cleanup.  Next, we'll run the Delete Requests for Quotations routine.

Production control

Next, we'll clean up Production Control. First, we'll start with cleaning up time registrations. Next, we'll clean up production order journals. This will clean up all production order journals based on the production order status. Now that we've cleaned up the journals related to production orders, we're going to clean up the production orders themselves. This will remove production orders based on some filters.

Sales and Marketing

Next, we're going to move onto Sales and Marketing. We'll start with cleaning up the sales order update history. Next, we're going to clean up our sales orders and also our return orders. Lastly, we're going to clean up any sales quotations.

Warehouse Management

Next up is warehouse management. Depending on if you use this functionality, there may be a lot to clean up here but perhaps not. First, we're going to clean up work creation history. Next, clean up any cycle count plans followed by wave batch cleanup, mobile device activity log cleanup, containerization history and lastly work user session log. 

Inventory Management

Up to this point, the order in which stuff gets executed has not been all that important. However, here is where order and timing really start to matter. Each item in the list below needs to be run and its execution be completed in each company before scheduling the next item. Also, these are hosted on another blog as a call out. Furthermore, these can be resource intensive so plan on a low usage time like a weekend to execute these.

Cleanup Load Adjustments

Cleanup Inventory Journals

Cleanup Cost Calculation Details

Cleanup Inventory Settlements

On-hand Entries Aggregation by Financial Dimensions

On-hand Entiries Cleanup

Warehouse Management On-hand Entries Cleanup

Dimension Inconsistency Cleanup

Inventory Dimension Cleanup

System Administration

Two areas in AX 2012 that are not easy to keep track of are management of AIF and Batch logging systems as well as the database log. I wrote 2 items to help automate some of this. The following processes are custom and require custom code but they can be helpful in removing lots of old records. 

AIF and Batch Cleanup

This will help cleanup various messages that have accumulated over the years in the AIF and Batch history tables. You can find that project here. You can change it to suite your needs and/or clean up anything extra. If you are on Dynamics 365 for Finance and Supply Chain, please check out this article on batch history cleanup.

Database Log Cleanup

Next, we have the database log. This taking up a lot of space represents 2 issues. First, that you have potentially lots of unneeded database logs making it so lots of records are tracked and slowing down your system ( and we'll talk more on this in another post ). The second is that these records are never being cleared out based on age. Chances are low the record that Sam updated Item 1234 3 years ago is of any value at all. I wrote something to help clean up records based on table type and age. This will help clear stuff out in batch. Depending on how many logs you have, you may need to run this more than once a week, day or even hour for it to catch up. All database logs are stored in one table: SysDatabasLog. I'll be using the tool I wrote to clean up this table using the defaults highlighted in this article. 

Retail

Also, if you are a retail organization, chances are lots of records have accumulated in your download and upload session tables. If you are not a retail organization, you can skip over this part. I wrote a cleanup routine that allows you to delete records in batches that follows standard functionality. You can find out more here.

Wrapping Up

Now that we know what we're going to do, in the next article we're going to run the above process, record some metrics on what had the biggest impact(s) on various areas and discuss those numbers.