Resetting the DataMart in Financial Reporting for Dynamics 365 for Finance and Operations

There is a universal solution for most problems in Financial Reporting (used to be Management Reporter) and that is to reset the DataMart. The DataMart is an internal data warehouse for Financial Reporting and when something goes wrong it is usually because of bad data in the DataMart. Another reason for resetting it when you do a refresh of the transaction database in D365/AX. Here is how you do it.

  1. In D365 search for “Financial Reports”
  2. Depending on if you see reports or not either select a report and click edit or click new. This will open the Report Designer. Note that you will need to enable pop-ups. Also note that if you are doing this in Chrome you will need an extension called ClickOnce in order to run the designer.
  3. You will need to log in using your Dynamics Account.
  4. Choose a default company /which does not really matter for this operation)
  5. If there are any open report definition close it

  6. Go to Tools – Reset Data Mart…
  7. Check Reset data mart and select the correct reason for the reset

  8. Click OK

    The Data Mart is now reset and filled with fresh data.

  9. Wait for the reset to finish

  10. When all lines have Status RanToCompletion the reset is done.

PowerBI breaks Workflow Editor and Report Editor

I am so sorry about the click bait title… I could not resist Smile

Todays adventure consists of troubleshooting why Report Editor and Workflow Editor stopped working in our Dynamics 365 for Operations environment. First som history:

This all started in one of our environments where we noticed that Report Designer for Management Reporter (Financial Reports) was not working… after some troubleshooting we called MS Support and did two days of troubleshooting I got the suggestion to try this in another environment (I am a little annoyed that I did not do this before) and of course it worked. Well, that did not really provida a lead to the root cause. One day later I got a request to configure PowerBI in the environment where it worked and later that day once again tested in the  “working” environment and low and behold… it did not work!!! What the_____? This got me thinking about what could have caused this… I installed PowerBI… could it be… Yes it could.

I remembered that I had read in the description of the PowerBI configuration that there was a caveat in the configuration of the Azure AD Application: 

App ID URI: This value is mandatory, but isn’t required for the workspace integration. Make sure that this App ID URI is a mock URI like https://contosoAX, since using the URL of your deployment can cause sign-in issues in other AAD applications such as the Excel Add-in.”

So I went back to Azure AD and I had unfortunately not done this…

Bildresultat för facepalm

I changed this in both environments and… Voilá… it worked.

Note: This has been changed in my original post on PowerBI Smile

Links:
http://blog.johanpersson.nu/?p=2351
https://ax.help.dynamics.com/en/wiki/configuring-powerbi-integration/

Error when syncing DataMart – Invalid column name ‘PARTITION’. Invalid column name ‘Key_’.

Todays issue is a problem with integrating Management Reporter CU 15 with AX 2012 R3. When doing the initial integration we got this error:

Invalid column name ‘PARTITION’.
Invalid column name ‘Key_’.

and the integration never finished. After doing some research online I found someone with this issue and the recommendation was to downgrade to Management Reporter CU13. When doing so we bumped into an error in CU 13 causing deadlocks in SQL. I called Microsoft support and got the recommendation to once again upgrade to CU15.

Here is a good place to insert some history:

The customer upgraded from AX 2012 Feature Pack directly to AX 2012 R3. Before upgrading from AX 2012 Feature Release to AX 2012 R3 we had installed MR CU13. In AX 2012 R2 Microsoft introduced a feature called PARTITION. This is used to be able to host AX in a multi tenant environment with isolated customers.

When installing Management Reporter (CU13 and Later) and integrating it to AX it creates three views in the database.

image

When we upgraded directly from AX 2012 Feature Release directly to AX 2012 R3 these views where migrated to R3 and when we installed CU15 these views were never updated (the PARTITION column was missing), Management Reporter just verified that they were there and thought all was fine.

  1. Resolution:
    Remove the integration
  2. Remove the three views
  3. Recreate the integration
  4. Enable the integration and sync the datamart

That is all for today

/Johan

Installing Management Reporter and connecting it to AX 2012 R2

If you use Dynamics AX 2012 R3, Management Reporter is included from the beginning. It is simply a feature you add from the install application. Prior to AX 2012 R2 you will need to download the install from Microsoft and install it. This is a short description on how to install Management Reporter and integrate it into AX 2012 R2.

My server Environment consists of one SQL server called SQL1, one AOS server called AOS1, one Management Reporter Server called MR1

I start by installing SQL locally on the MR1. The features installed are:

image

Then next thing is to install Management Reporter. This is a simple Next, Next, Finish install and when it is done you start the Configuration Console to do the actual work. The first thing to do is to select the AX 2012 Data Mart integration.

image

Now start the hard stuff… I will try to explaing the values as good as possible and also where it is easy to go wrong

image     image

  1. This is a Service account used to run Management Reporter. It needs to be a user in the AOS instance you want to connect to and it needs the role System Administrator
  2. This is the database server where Management Reporter keeps its own databases. In this case it is locally on the MR server.
  3. To create the databases on the MR server we use Windows authentication which is the account you are currently logged in as
  4. The name of the Management Reporter database
  5. This is an encryption key Management Reporter uses to encrypt information stored in its database
  6. This user is added to the Management Reporter as an admin
  7. This is the port which Management Reporter Client will use to connect to the Management Reporter Server
  8. This is the port used to connect to the AOS server instance. Note that if you have more than one instance on the same server this will be different for all instances. You can find port information for each instance in Microsoft Dynamics Ax Server Configuration Utility (Image below). In my case this is the third instance and the ports are 8203 and 2714.
  9. This is the AOS server (Note: NOT the instance name)
  10. See 8
  11. This is the database server where the AX database is located.
  12. This is the AX database name. Note that the service account will need atleast db_reader access to both the AX database and to the AX model database
  13. This is the database where the AX Data mart is stored. In my case this is located in the MR Server 

image