Speeding up the Financial Reporting (Management Reporter) refresh in a Sandbox
Let me first preface this post with giving a shout-out to the Critical Situation Management and Escalation team at Microsoft. I was able to work with them throughout the night to get a recent issue with Management Reporter resolved even though I created my ticket around 5:30 Central Time. They were incredibly responsive and helpful.
The details of the issue are sort of bland. During a recent upgrade from D365FO 7.2 to 8.1.3, a feature change in D365FO impacted the behavior of exchange rates in Management Reporter. Specifically, it seems like any legal entity overrides that existed before the upgrade needed to be deleted and then re-created with the appropriate currency exchange rate type specified on them. If this sounds like something you’re running into, let me know and I can share what I learned.
Also, I’m going to keep referring to this as Management Reporter, but it’s technically called Financial Reporting now. See?
The resolution wasn’t the most exciting part of the night, though. During troubleshooting with Microsoft, they tipped me off to a helpful little script to run against the Management Reporter database in a Sandbox environment. This script is buried in the Docs site here (https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/analytics/reset-financial-reporting-datamart-after-restore#reset-the-financial-reporting-data-mart-for-finance-and-operations-on-premises), but they broke out just the important part for me:
SET IsEnabled=1, RunImmediately=1
FROM [Scheduling].[Task] STK WITH (NOLOCK)
INNER JOIN [Scheduling].[TaskState] STS WITH (NOLOCK) ON STK.[Id] = STS.[TaskId]
INNER JOIN [Scheduling].[Trigger] STRG WITH (NOLOCK) ON STK.[TriggerId] = STRG.[Id]
WHERE STK.TYPEID IN ( ’55D3F71A-2618-4EAE-9AA6-D48767B974D8′ ,’6F6B935B-FC0A-46B9-8F53-27C6AF7437F0′ ,’D81C1197-D486-4FB7-AF8C-078C110893A0′ )
Again, this is only going to work in a case where you have access to the MR database, but it certainly saved me time in the middle of the night waiting for things to refresh.
The Management Reporter datamart is obviously asynchronously updated. Whenever you make a change in D365FO to accounts, or dimensions, or exchange rates (among other things), you need to wait for the integration jobs to run to grab the changes and refresh the datamart. This script sets all the refresh tasks to run immediately instead of waiting for their schedule.
Management Reporter Integration tasks schedule
Since I was in the database anyways, I figured I’d record the normal schedule for these refresh tasks. Best as I can determine, here’s that schedule:
|Task||Unit of Measure||Interval|
|AX 2012 Companies to Company||Second||300|
|AX 2012 Organization Hierarchies to Tree||Second||3600|
|MR Report Definitions to AX7 Financial Reports||Second||45|
|MR Report Versions to AX Financial Report Versions||Second||45|
|AX 2012 Accounts to Account||Minute||7|
|AX 2012 Account Categories to Account Category||Minute||41|
|AX 2012 Dimensions to Dimension||Minute||31|
|AX 2012 Dimension Combinations to Dimension Combination||Minute||1|
|AX 2012 Dimension Values to Dimension Value||Minute||11|
|AX 2012 Exchange Rates to Exchange Rate||Minute||17|
|AX 2012 General Ledger Transactions to Fact||Minute||1|
|AX 2012 Transaction Type Qualifiers to Fact Type Qualifier||Minute||19|
|AX 2012 Fiscal Years to Fiscal Year||Minute||13|
|AX 2012 Companies to Organization||Minute||23|
|AX 2012 Scenarios to Scenario||Minute||29|
This is what it looked like in MY Financial Reporting (Management Reporter) database – yours might be different.