Management Reporter AX 2012 Integration Overview

Management Reporter has several maps that integrate subsets of the AX financial data into the data mart database. A portion of the MR Process Service code called the Scheduler runs these integration maps at regular intervals (1 or 5 minutes by default) to ensure the report data is up-to-date. During the integration, a number of SQL statements are run against the AX database and the resulting records are transformed and inserted into the Management Reporter and data mart databases to be available for reporting.

AX 2012 Integration Maps Fiscal Years to Fiscal Year Accounts to Account Exchange Rates to Exchange Rate Transaction Type Qualifiers to Fact Type Qualifier Dimensions to Dimension Companies to Organization General Ledger Transactions to Fact Scenarios to Scenario Dimension Combinations to Dimension Combination Dimension Values to Dimension Value Account Categories to Account Category Organization Hierarchies to Tree Companies to Company

Integration data processing overview:

Prior to MR CU10, all data and its related data was pulled from the AX database and fully processed in the MR services before inserting into the data mart. Processing all the records this way caused a number of performance issues with larger AX data sets. With CU10 and later, MR loads the separate sets of data into staging tables in the data mart and does the processing/validation in SQL stored procedures. The processing (resolving) of the staging data in the data mart is done by the Maintenance Task which runs every minute. It if cannot resolve the staging data on the first attempt, it will continue to try to resolve the data at an increasing (exponential) interval.

The formula for the retry schedule is: 3^(AttemptNumber):

Example:

If staging data was just attempted for its 4^th time, that means at least 129 minutes have passed since the data was integrated originally and the next attempt won’t occur for another 243 minutes. Retry information about the current sets of staging data can be found in the data mart Archive table.

Note: It is possible that MR will not be able to resolve the staging data due to invalid AX data or an integration bug. When this occurs, the unresolved data will be not available in reports until the data is fixed. You may need to rebuild the data mart after it is fixed.

Example of invalid AX data causing an unresolved (“stuck in staging tables”) transaction:

• A new transaction is posted in AX.
  • The dimension combination used on the transaction has never been posted to previously.
• The dimension combination is integrated into the DimensionCombinationStaging table.
• The transaction is integrated into the FactStaging table.
• The Maintenance Task attempts to resolve/validate the records in the DimensionCombinationStaging table.
  • It cannot resolve the dimension combination because one of the dimension values does not exist in the Data mart. It does not exist in the Data mart (DimensionValue table) because it does not exist for the company the transaction is posted to.
• The transaction also cannot resolve because its dimension combination did not resolve.
• Both the dimension combination and transaction will not be available on MR reports.

Please see Troubleshooting Management Reporter Data Mart Staging Records for troubleshooting steps to identify why data mart staging records cannot be resolved.

Greg Byer [MSFT]

See more articles from this author