Speeding up the Financial Reporting (Management Reporter) refresh in a Sandbox

 In Blog

Background

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?

RunImmediately

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:

update strg
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.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Have a question? Want more information? Let us know!

Not readable? Change text. captcha txt