Management Reporter 2012 – Reset Data Mart for Dynamics AX

Management Reporter 2012 utilizes two databases. One of these databases stores report metadata, user information and other application information. Management Reporter also uses a data mart database which stores financial information in a format optimized for financial statement reporting. At times the data in this data mart database can get out of sync and need to be reset.  At this point there is not an option to reset this data through the application. Instead, we need to reset the data mart which involves recreating the data mart database and setting up the integration to Dynamics AX again from the MR Configuration Console.

Summary of steps to reset the data mart database:

1. Remove integration to AX
2. Stop the Management Reporter service
3. Delete the data mart database
4. Start the Management Reporter services
5. Use the Management Reporter configuration utility to create the data mart database
6. Enable the integration

Detailed steps to reset the data mart database:

1. Remove integration to AX

a. Log on to the Management Reporter server

i. Must use an account that is an MR Admin and an admin on the server.

1. Account needs the following access

a. Local administrator on the server

b. Sysadmin or user within SQL with rights to create the database

c. System Admin user role within AX

d. Security Admin user role within AX

b. Start the Management Reporter Configuration Console

i. Click Start and type in Configuration Console

c. Click on the current integration in the Navigation Pane (left side)

d. Note the name of the Management Reporter data mart database (top blue header)

i. In screenshot below:  ManagementReporterDM

e. Click to Disable the integration

f. Remove the integration

g. 

2. Stop the Management Reporter services

a. Click on the Management Reporter Services in the navigation pane (top left)

b. Click STOP on both the Application and Process services

i. 

3. Delete the data mart database

a. Open SQL Server Management Studio

b. Right click on the data mart database and choose Delete.

i. Database name noted in step 1-d. above.

c. 

d. 

4. Start the Management Reporter services

a. Go back to the Configuration Console for Management Reporter

b. Click on the Management Reporter Services in the navigation pane (top left)

c. Click START on both the Application and Process services

d. 

5. Configure the data mart

a. From the Configuration Console navigate to File / Configure

i. Select Add Microsoft Dynamics AX 2012 Data Mart

ii.  

iii. Click NEXT at the bottom

iv. Complete the two sections to deploy the data mart

1. Enter required information as shown below

2. 

3. Click Next at the bottom

4. Click Configure

a. If errors view Deployment Log

i. C:\ProgramData\Microsoft Dynamics ERP\Management Reporter\Logs\….

6. Enable the integration

a. Click to expand ERP Integrations on the Navigation Pane

i. Click on the ERP integration that was just created

b. Click Enable Integration

i.  Integration may take several minutes to transfer all needed financial information from AX to the newly created data mart database.

c. 

d. Once complete the Status shows “Initial integration is complete. Periodic integrations will occur”

e. 

7. Complete.

Users can now run reports.