Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support Teams Worldwide

Database indexes to improve Management Reporter 2012 for Dynamics AX 2012 R3 data mart integration performance

To get the best performance for Management Reporter 2012, it is always recommended to be up-to-date and have the newest version installed. You can check your version information against the version list at:

 

Management Reporter feature and version number summary

http://blogs.msdn.com/b/dynamics_financial_reporting/archive/2014/03/25/management-reporter-feature-and-version-number-summary.aspx

 

If you already have the newest version and have a large amount of data in Dynamics AX 2012 R3 and Management Reporter 2012 is running slowly when performing the incremental synchronization of transaction data to the data mart database (or if you just want to try and optimize your environment when used with Management Reporter 2012), here are some indexes which may assist. Make sure to verify these in a test environment first and confirm that the indexes do not have a negative performance impact for your daily AX business processes as well as other less-frequent items such as those ran during month end/year end. Thanks to Microsoft’s Mark Prazak for helping to develop these.

 

The indexes must be created as listed for the queries used by Management Reporter 2012 to hit the index. It would not be suggested to modify any existing indexes which may have been created for Dynamics AX as that may negatively impact your Dynamics AX implementation. All the indexes listed here can be created and maintained through the AOT. For more information on how to create an index in the AOT for Dynamics AX 2012, refer to:

 

How to: Create an Index [AX 2012]

https://msdn.microsoft.com/en-us/library/aa607289.aspx

 

The list of suggested indexes are below:

 

Table

DIMENSIONATTRIBUTEVALUECOMBINATION

Keys

MAINACCOUNT, PARTITION

Included Columns

RECID

   

Table

DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION

Keys

DIMENSIONATTRIBUTEVALUECOMBINATION, PARTITION

Included Columns

(none)

   

Table

GENERALJOURNALACCOUNTENTRY

Keys

GENERALJOURNALENTRY, PARTITION

Included Columns

RECID

   

Table

GENERALJOURNALACCOUNTENTRY

Keys

LEDGERDIMENSION, PARTITION

Included Columns

GENERALJOURNALENTRY

   

Table

LEDGERENTRY

Keys

GENERALJOURNALACCOUNTENTRY, PARTITION

Included Columns

CONSOLIDATEDCOMPANY, ISBRIDGINGPOSTING

   

Table

TRANSACTIONREVERSALTRANS

Keys

REFTABLEID, REFRECID, PARTITION, DATAAREAID

Included Columns

TRACENUM, REVERSED

   

Table

VENDPACKINGSLIPTRANS

Keys

PARTITION, DATAAREAID, INVOICETRANSREFRECID

Included Columns

RECID