How to Delete Financial Dimension Sets in D365FO?

If you’re wondering how to delete financial dimension sets in D365FO, you’re not alone. Over time, companies using Microsoft Dynamics 365 Finance and Operations (D365F&O) often accumulate unused or obsolete financial dimension sets. Cleaning them up can help improve system performance, and make your environment easier to maintain. In this post, I’ll walk you through the steps to safely delete financial dimension sets in D365.

What Are Financial Dimension Sets?

Financial dimension sets in D365FO are predefined combinations of financial dimensions used to speed up financial reporting. They allow the system to pre-calculate balances for common dimension combinations, improving the performance of reports like Trial Balance and Financial Statements.

However, as your organization evolves, some dimension sets may no longer be needed. Removing them reduces unnecessary data and ensures your reports run faster.

Deleting Financial Dimension Sets in D365F&O: Important Considerations

Before I explain how to delete financial dimension sets in D365FO, make sure you keep in mind that deleting a dimension set won’t affect your financial data but will remove pre-aggregated balances tied to that set. Also, you have to make sure you have the correct security roles (typically System Administrator user role) to make these changes.

Why Delete a Financial Dimension Set?

There are several reasons why you might need to delete a financial dimension set:

• Obsolete Sets: The dimension set is no longer relevant to current financial reporting, and you simply don’t want to see it in your system.
• Database Optimization: Large numbers of unused dimension sets can increase the database size and impact system performance.
• Data Cleanup: Removing unused data in the system simplifies navigation and improves data management.
• Incorrect Configuration: If a dimension set was created in error, removing it prevents confusion.

Steps: How to Delete Financial Dimension Sets in D365FO?

1. Navigate to the Financial Dimension Sets Page:
   • Go to General ledger > Chart of accounts > Dimensions > Financial dimension sets.
2. Identify the Dimension Set to Delete:
   • Review the list of existing dimension sets, and make sure you find the ones that you really don’t need. Make sure you align on this with your finance department.
3. Delete the Dimension Set:
   • Select the dimension set you want to delete.
   • Click the Delete button (trash can icon) on the action pane.
   • Confirm the deletion when prompted.

NOTE: Make sure you try this on your sandbox environment first.

Common Issues and Troubleshooting

• User permissions error: Ensure your user role has the “Maintain financial dimension sets” privilege.
• Data inconsistency warnings: Rarely, rebuilding balances might be necessary to ensure your reports display accurate numbers.

For some of the dimensions, you will get the error that says: “Cannot delete a record in Dimension set balance (DimensionFocusBalance). Financial dimension set: 0, 0. A time-out occurred in the database while query was executing.“

Here is what the error looks like in D365F&O:

The first step you have to do is to check if there are any unprocessed transactions in the DimensionFocusUnprocessedTransactions table. To do this, make sure you request the access to SQL database of your sandbox environment via LCS. Make sure you select Reason for access with write permissions:

Next thing is to check if you have some unprocessed transactions in your system. You can do this by running the following query on your sandbox environment:

SELECT 'dimensionfocusunprocessedtransactions' AS 'tablename'
	,dfb.focusdimensionhierarchy AS 'dimensionset'
	,count(dfb.recid) AS 'count'
	,dh.description
FROM dimensionfocusunprocessedtransactions dfb
INNER JOIN dimensionhierarchy dh ON dfb.focusdimensionhierarchy = dh.recid
INNER JOIN generaljournalentry gje ON gje.recid = dfb.generaljournalentry
WHERE gje.accountingdate > '2024-01-01 00:00:00.000'
GROUP BY dfb.focusdimensionhierarchy
	,dh.description
ORDER BY count(dfb.recid) DESC

This SQL query will give you a list of financial dimension sets with highest number of unprocessed transactions.

The DimensionFocusUnprocessedTransactions table in D365 Finance and Operations (D365F&O) serves as a temporary holding area for financial transactions that need to be processed into pre-aggregated financial dimension balances. When a new financial dimension set is created or when transactions occur that impact dimension balances, the system records them here first. The table ensures that only new or changed transactions are picked up during the next balance rebuild, improving system efficiency. It helps avoid recalculating the entire general ledger unnecessarily by focusing processing efforts only where changes happened.

You can reduce the number of records in this table by updating balances for the dimension sets you get by running the query above:

If you are still getting the timeout error, you should try deleting the records from DimensionFocusBalance table.

The DimensionFocusBalance table in D365 Finance and Operations (D365F&O) stores the pre-aggregated balances for financial dimension sets. Essentially, once transactions are processed and posted, their summarized balances – grouped by dimension combinations – are saved in this table. This allows financial reports like the Trial Balance to load much faster because the system doesn’t have to sum up millions of transactions on the fly. Instead, it reads the already calculated balances from DimensionFocusBalance table.

But first, let’s see which financial dimension sets (from the ones that you have to delete) hold the largest number of records by running the following query:

SELECT dfb.focusdimensionhierarchy as [dimensionsetid]
	,dh.description
	,count(dfb.recid)
FROM dimensionfocusbalance dfb
INNER JOIN dimensionhierarchy dh ON dfb.focusdimensionhierarchy = dh.recid
GROUP BY dfb.focusdimensionhierarchy
	,dh.description
ORDER BY count(dfb.recid) DESC

You can delete the DimensionFocusBalance records by using this query:

DELETE
FROM dimensionfocusbalance
WHERE dimensionfocusbalance.focusdimensionhierarchy = [dimensionsetid]

If it doesn’t work, and you get some strange SQL error, try deleting the first 5 million records:

DELETE TOP(5000000)
FROM dimensionfocusbalance
WHERE dimensionfocusbalance.focusdimensionhierarchy = [dimensionsetid]

NOTE: Make sure you replace dimensionsetid with the value of you financial dimension set (from the previous query.

If this helped you delete the financial dimension set, you can ask your partner to create an X++ script to do the same in production. It should look similar to this one:

class MyScriptClassForIssueXYZ {
  public static void main(Args _args) {

    ttsbegin;

    DimensionFocusBalance dimensionFocusBalance;

    delete_from dimensionFocusBalance where dimensionFocusBalance.FocusDimensionHierarchy == [recId of one of the desired deleted dimension sets: 124999810, 121157811, 90403022]

    info("Success");

    ttscommit;
  }

}

Final Thoughts

Now you know how to delete financial dimension sets in D365FO safely and efficiently. Keeping your dimension sets clean and relevant not only improves report performance but also helps maintain a healthy, optimized D365 environment.

If you’re planning a larger cleanup of your D365 setup, consider reviewing old financial reports, obsolete dimensions, and unused accounts as part of your housekeeping process!

If you want to read more about Financial Dimension Sets, make sure you visit official Microsoft documentation here: https://learn.microsoft.com/en-us/dynamics365/finance/general-ledger/financial-dimension-sets

Make sure you also check one of my previous blog posts on How to Improve Software Performance.