The below instructions are for Management Reporter when used with Microsoft Dynamics SL or Microsoft Dynamics GP. When information is specific to either of the ERP systems, separate instructions will be provided.
Resetting the Management Reporter data mart is the process of clearing all the integrated data from the ERP system. This is done by disabling all the integration tasks, deleting the data mart data, and then re-enabling the integration. The ManagementReporterDM database is not removed during the process and the reset is done using a PowerShell script. This process is only available in Management Reporter 2012 CU15 or later.
Rebuilding the Management Reporter data mart is the process of disabling the integration, removing the integration, deleting the ManagementReporterDM database and then creating a new integration.
When to reset the data mart in Management Reporter:
- When accounting data/transactions are missing.
- After repairing data when data validation issues are found.
- When PSTL or other third-party tools are used to change segments or accounts.
When to rebuild the data mart in Management Reporter:
- When the ERP database is restored from backup.
- Copying your production ERP database into a test environment.
- When changing Analytical Accounting (activating/de-activating) in Dynamics GP.
Other changes that would not require a data mart reset or rebuild:
- Account Category change — If an account is moved to a different account category, the account category change won’t occur until a transaction is processed for that account combination. It could be an actual or budget transaction, but all related combinations would need to see an update.
- Account Description change — If an account description is changed, the new description will not be available until the data mart is reset. The descriptions entered into the row definitions for the account will require a manual update for changes to be reflected.
Overview of Steps
In this series of blogs we will walk through how to back up Management Reporter databases, how to find your Management Reporter Administrator account and how to reset or rebuild the Management Reporter data mart.
- Back up Management Reporter databases (below)
- Find your Management Reporter Administrator account (below)
- Reset the data mart (part 2) COMING July 19
- Rebuild the data mart (part 3) COMING July 26
Back up Management Reporter Databases
Before starting either method (rebuild or reset) the Management Reporter (ManagementReporter) and Data Mart (ManagementReporterDM) databases should be backed up. Here’s how to do that:
- Open SQL Server Management Studio (SSMS) and login with either a SQL account or Windows account that has membership in the sysadmin role on the SQL server.
- In the Object Explorer pane, expand the Databases node.
- Right-click on the ManagementReporter database then select Tasks > Back Up…
- Choose the backup settings and path. Then click the OK button.
- Repeat the same steps for the ManagementReporterDM database. This is optional if rebuilding the data mart but recommended if resetting the data mart.
Finding an Administrator account in Management Reporter
Prior to resetting or rebuilding the Management Reporter data mart and working with the Configuration Console, you will also need to find a Windows account that is configured as an Administrator in Management Reporter. This account will be used to log on to the Management Reporter server and perform the tasks below. You can find this account by using either SQL Server or Report Designer.
Find an Administrator account using SQL Server
Run the following script against the ManagementReporter database in SSMS to find the Management Reporter administrators:
SELECT u.UserID, u.UserName,
CASE WHEN u.RoleType = 5 THEN ‘Administrator‘
WHEN u.RoleType = 4 THEN ‘Designer‘
WHEN u.RoleType = 3 THEN ‘Generator‘
WHEN u.RoleType = 2 THEN ‘Viewer‘
ELSE ‘Unknown‘
END as Role, p.Name as DomainAccount, u.WindowsSecurityIdentifier
FROM Reporting.SecurityUser u
INNER JOIN Reporting.SecurityPrincipal p on u.userid = p.ID
WHERE u.UserName <> ‘Management Reporter‘
Find the Administrator account using Report Designer
- Open Report Designer on a computer/server where the Management Reporter client is installed (Start | Microsoft Dynamics | Report Designer)
- If prompted select a company to log into.
- In the navigation menu on the left, select Security at the bottom.
- This will display a list of all the users configured in Management Reporter. Under the Effective Role look for the Administrator users.
Now you’re ready to reset or rebuild the Management Reporter data mart.
