Cannot delete an unused main account in Dynamics AX 2012 R2

After creating a new financial dimension (about a week elapsed), a decision was made to delete the newly created financial dimension.

We knew these dimension values weren’t used on any posted or un-posted transactions, so the expectation was that we could simply delete these without any issues. So you can imagine the surprise when we tried to delete the dimension values only to receive the following error message:

A financial dimension value is based on the <dimension value> record and has been used on a transaction. You cannot delete the <dimension value> record.

                                    

This error is displayed by the system if records exist within the DimensionAttributeLevelValue table for the given Dimension Value.

So before I move on and explain how the issue was resolved, let me start off with a little caveat…

The description below isn’t a comprehensive breakdown that covers all possible scenarios that result in records being inserted into the DimensionAttributeLevelValue table, but it illustrates the use case scenario that caused the above-mentioned issue.

So when does the system create records in the DimensionAttributeLevelValue table?

One of the use cases that result in records being created in this table is when a user, as an example, creates a new general journal line and enters in the financial dimensions on that line.

At this stage the system creates (if it doesn’t already exists) an entry in the DimensionAttributeValueCombination for the financial dimension combination specified on the general journal line. The make-up of the specified financial dimension combination is then stored in the DimensionAttributeLevelValue table.

For Example:

If the financial dimension combination reads 0100-OU_1-OU_3566, (where 0100 represents the Main Account Id, OU_1 the Department Financial dimension, and OU_3566 represents the Cost Centre Financial dimension), the system will create 2 records in the DimensionAttributeLevelValue table, namely

• One record will be for the Department Code OU_1
• The second record will be for the Cost Centre OU_3566.

So knowing this, we were able to deduce that a Journal Line was created to test financial dimension structures and hierarchies. The Test journal line was deleted (not posted), but the action of entering in the journal line resulted in records being created in the above-mentioned tables.

To resolve this, and allow the Dimension Value to be deleted, the records created in the above-mentioned tables would need to be manually deleted.

I’m very interested to know if there is a tool that can be used to ‘clean-up’ any un-used Financial Dimension combinations i.e. financial dimension combinations that were created in the system, but that aren’t associated with any posted or un-posted transactions.

Does anyone know of a tool that does this? (Feel free to leave some comments)