RSS

How to delete unused Financial Dimension

16 Apr

yesterday I came across the following issue  and thought I’d share my findings and solution.

I’ve setup a chart of accounts and shared it between 3 companies.(one of them a test company).i have mapped one ledger account (Main Account) with bank accounts of all three companies.I have attached a “Test” account structure.and made a couple of postings in the test company.

when I want to modify a main account or delete a bank account that was not used (but defined with a main account).The message is the bank account cannot be deleted because the financial dimension related to the account has transactions.

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.

error

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(Ledger,Bank,customer,vendor etc.)

The error means that there are some existing reference records.I can explain the reason for the message and why you are not able to delete the main account, but unfortunately cannot give you a good solution for how to fix it.

When you add new records to financial dimension entities (Main account, Department, Cost center, etc.), they are merely available for use and can be immediately deleted.  However, as soon as they are actually used, additional records are created in tables such as DimensionAttributeValue, DimensionAttributeValueCombination, DimensionAttributeLabelValue, and many others part of the dimension framework.

These tables are used in storing data for a Ledger dimension  Dynamics AX 2012. There will be multiple entries in these tables for a single ledger dimension.AX provides no mechanism for deleting these records.The solution will be to make a utility that deletes all the record in those tables that don’t have any reference in any other tables.

To find out which DimensionAttributeLevelValue record corresponds to the main account, you need to use a few more tables.

LedgerChartOfAccountStructure –> Links COA to account structure

DimensionHierarchy –> AKA Account structure table.  Also links to LedgerChartOfAccountsStructure, so you match the MainAccount by COA all the way to the account structure.

DimensionAttributeValueGroup –> Stores groups of values for the dimension set, which is linked to both DimensionAttributeLevelValue and DimensionHierarchy.

So you can start from DimensionAttributeLevelValue and drill back (or join) in this way…

DimensionAttributeLevelValue.DimensionAttributeValueGroup = DimensionAttributeValueGroup.Recid

DimensionAttributeValeuGroup.DimensionHierarchy = DimensionHierarchy.Recid

DimensionHierarchy.Recid = LedgerChartOfAccountsStructure.DimensionHierarchy

LedgerChartOfAccountsStructure.ChartOfAccounts = MainAccount.LedgerChartOfAccounts

MainAccount.MainAccountId = ‘Your account number’

Simple Solution

1) Error occurring as there are record exist in the DimesionAttributeLevelValue table for the customer

2) Go to the DimensionAttributeLevelValue table –> filter by Bank,Vendor,Customer number in the DisplayValue field –> you can see the records exist in the table for the customer

3) Delete the record from the table

4) After this try deleting the Bank,Vendor,Customer.

(It will allow you to delete the Bank,Vendor,Customer record)\

Happy Daxing.  🙂

Advertisements
 

Tags: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: