The glib answer is either to get a more powerful server, or to reduce the data load.
20,000 records seems high but if you have 200 stores - 10 cus groups - 10 item groups then the combinations soon create a lot of records. If that is the nature of the business then you need the computing power to run it.
There is a cost for each additional dimension in terms of performance impact, and database size, and of course, the more data entry the more potential there is for data entry errors. The purpose of dimensions is for P@L reporting e.g. by employee, or project, or cost center or customer group. Other analysis can be done through other fields e.g. via attributes.
The performance impact really comes down to how many 'combinations' of dimensions are generated. Dynamics AX 2012 creates a combination (account number) when it is used on either a source document, or a journal entry. By creating and storing the combinations, performance in areas such as validation is improved.
However, other areas may be impacted, e.g. the trial balance list page.
The trial balance list page does not have the ability to page the data, so when selecting a dimension set that contains all of the dimensions to display, the system needs to go through all the combinations for the current company and date range.
It takes a large number of combinations to notice this impact, but some customers have reached 80,000+ number of combinations for a single year based on how detailed they wanted their chart of accounts. (10 dimensions each having only 10 values is 10x10x10x10x10x10x10 x10x10x10 possible combinations and that then has to be multiplied by the number of main accounts)
Another performance consideration for financial dimensions therefore is to consider the number of values for the dimension. For example, if you create a financial dimension based on Customer and you have 10,000 customers, then you will have 10,000 values. Taken into combination with the other financial dimensions and their values, you could have 10os or 1000s of thousands of combinations of account numbers generated which will impact the performance of many queries. What if you want to an expense split like marketing across all customers – how many journal lines will be posted? So consider whether Customer group or line of business might be more pragmatic or whether this analysis can be done another way.
One way to reduce the problem is to have fewer organisation dimensions - you can still aggregate data in any structure you want with MR, provided that you capture it at the lowest level. e.g if you capture cost centre then you can aggregate to department and company in the reporting tree without need for a dimension.
Power BI may also reduce the need for dimensions.
