Management Reporter is a real-time financial reporting application for Dynamics ERP systems (e.g. Dynamics SL, AX and GP) and is the replacement of Microsoft FRx for 64-bit environments.
The Data Mart is the new method for integrating financial information from the Dynamics ERP with Management Reporter 2012. It was introduced in MR Roll-Up 3 and is the preferred method of integration moving forward. The (Data Mart) stores summarized numbers in its own SQL database instead of reaching into the GP database each time unlike the old “Legacy” method. This option improves performance when generating reports in Management Reporter 2012 and supports a greater level of integration with Dynamics ERP systems.
In some cases, there are missing financial information (e.g. missing accounts, account format, etc.) or incorrect values in MR reports. This issue may happen if there was an error in the flow of data from GP to MR and DDM database due to timeouts in the SQL process or the Data Mart integration triggers has not yet fired off.
- Log off all users from Management Reporter. These steps ideally are performed during off-peak hours.
- Verify that the domain user who is performing the procedure has the correct permissions (e.g. “sa” or “W2K\svc_mrsvr”) on the SQL server and to the new database.
-
Make sure that the Data Mart (e.g. “DDM”) database is removed from the Availability Databases in SQL 2012 since the steps below will require you to delete and recreate the database.
(Note: Notice how the DDM database above does not indicate it is “synchronized”. This means that it is not added to the Availability Databases for AlwaysOn mirroring.)
-
Start the Management Reporter 2012 Configuration Console. (Click on Start, point to All Programs then click Microsoft Dynamics, clickManagement Reporter 2012, click Configuration Console).
-
Record the name of the database listed under the Database connection heading. The database name will be listed after the SQL server name. For example: SQLServer (ManagementReporter)
-
Under ERP Integrations on the left pane, highlight the Data Mart integration being used and disable the integration by clicking the Disable Integration button.
(Note: In the screenshot above, a “Legacy” integration is also configured. There is no need to delete this as it will get repopulated after the ERP Integration has been rebuilt.)
-
Click Remove to remove the ERP integration.
-
Click Yes to the prompt “Are you sure you want to remove the ERP Integration?”.
-
In the Management Reporter 2012 Configuration Console, click Management Reporter Services.
(Note: In the screenshot above, assume that the Data Mart integration has already been removed)
-
Record the name of the Service Account that is used then exit the Management Reporter 2012 Configuration Console.
-
In the navigation bar, click Management Reporter Services.
-
Click Remove Process Service.
-
Click Yes to the prompt “Are you sure you want to remove ‘Management Reporter 2012 Process Service?”.
-
Click Remove Application Service.
-
Click Yes to the prompt “Are you sure you want to remove ‘Management Reporter 2012 Application Service?”.
-
Login to SQL Server Management Studio and access the SQL instance where the database recorded earlier in step 5.
-
Delete the “DDM” database by right-clicking on it and clicking Delete.
-
In the Management Reporter Configuration Console, re-install the MR Process and Application services and add the DDM integration by going to File and click Configure.
(Note: In the screenshot above, I am only re-installing the Data Mart since I already have an existing Management Reporter Application Service and Management Reporter Process Service.)
-
In the Configure Management Reporter window, install the Management Reporter server components and put a check next to:
a. Management Reporter Application Service – Controls access to Management Reporter data and provides connectivity to ERP systems.
b. Add Microsoft Dynamics GP Data Mart – Installs the DDM database in the specified SQL instance to integrate with the GP company.
c. Management Reporter Process Service – Generates the reports that are created and queued by the Management Reporter client component.
-
Enter the following information in the Service Account area:
a. Select type of account that you are using for the Service account in the Account field.
b. Enter the domain, user name, and password for the Service account recorded in step 10 above.
-
To configure the application service, enter the following information in the Application Service area:
a. Accept the default port for the web application to use, or enter another port.
b. To configure Windows firewall settings automatically, select Open this port in the Windows firewall. If you are using a different type of firewall, you must open the port manually.
-
Under Database Configuration, select the Connect to an existing database check box.
-
In the Database Server field, select the existing “Management Reporter” database.
-
Select an authentication option for the Management Reporter Administrator account:
a. To use your current credentials, select Use Windows authentication.
b. To use SQL authentication, select Use SQL authentication, and then enter the domain, user name, and password of the Management Reporter Administrator account.
-
Configure the data mart integration by using the appropriate credentials below:
(Note: In the screenshot above, I am only re-adding the Data Mart integration since I already have existing MR Application and Process Services.Make sure to use the existing DYNAMICS database for the Dynamics GP connection information and create a new Data Mart database for the Data Mart connection. In my example, I changed the default Database name from “ManagementReporterDM” to “DDM”.)
|
ERP integration
|
Required information
|
|
Microsoft Dynamics GP Data Mart
|
Dynamics GP Connection Information
·Database server– The name of the server that hosts Microsoft Dynamics GP.
·User nameandpasswordof a user account that has permissions to connect to Microsoft Dynamics GP.
·Database– The name of the Microsoft Dynamics GP system database to connect to “DYNAMICS”.
Data Mart Connection Information
·(Data mart) Database server– The server where the data mart database is located.
·User nameandpasswordof the account that is used to create the data mart database. Select Use Windows authentication to use your current credentials.
·(Data mart) Database– The default name of the data mart database to connect to is “ManagementReporterDM”. This can be changed.
|
-
In the Ready to Configure window, review the data integration information, and then click Configure.
-
When a message is displayed that indicates that the data integration has been successfully configured, click Close. (Note: If you use a data mart integration for Microsoft Dynamics GP, company information is imported automatically)
-
In the Management Reporter 2012 Configuration Console, verify that the Data Mart (DDM) (and Legacy integration if applicable) is already configured.
-
Click Management Reporter Services and restart the Management Reporter 2012 Application Service and Management Reporter 2012 Process Service. Verify that the services display a status of Running.
-
Click the ERP Integrations on the left hand side and highlight the Data Mart integration you just configured.
-
Click Enable Integration to allow initiate the integration of financial data from your Dynamics GP company with the newly created DDM database. This way, all current financial information will refresh the DDM database and update the MR reports.
-
Before re-adding databases back to the Availability Databases in SQL 2012, you must satisfy the following requirements:
a. Recovery Model must be set to “FULL”
b. A Full database backup has been created