Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Is it Data Mart or Leg...
Finance | Project Operations, Human Resources, ...
Suggested answer

Is it Data Mart or Legacy?

(0) ShareShare
ReportReport
Posted on by john.ellis 3,482

Hi:

A GP end user's environment crashed this past week, due to ransomware.

They bought new hard drives, including for SQL, and imaged the data over.

The IT Administrator, also, changed the Administrator password for the Application and Process Services for Management Reporter.

The users cannot access Management Reporter.  I do not see a database in SQL ending with the letters "DM".  And, although the Configuration Console mentions that the Application Service is not allowing for accessing reports from the Data Mart, how do I know whether this install is even Data Mart or Legacy?

For that matter, if it is Data Mart, how can I confirm this since there is not a database ending in "DM"?  If this is Data Mart, why is there no such database?

Should I simply create a new database called "ManagementReporterDM"?

By the way, I just tried doing so in File...Configure.  But, it did not work.  I then tried creating "Legacy" in File...Configure.  That did not work, either.  I simply got a red "X", both times, and the deployment log did not give me very useful information.

Part of the issue I think is that the Process Service randomly keeps stopping and I have to keep manually restarting it.

How do I fix this "mess"?

Thanks!

John

Categories:
Microsoft Dynamics GP
  • Suggested answer
    john.ellis Profile Picture
    john.ellis 3,482 on at
    RE: Is it Data Mart or Legacy?

    Hi All:

    Here was the remedy from Microsoft:

    We ran this initial script to see who the MR Admin users are:

    select Name from SecurityPrincipal join SecurityUser on SecurityPrincipal.ID = SecurityUser.UserID where RoleType = 5 and name <> 'Management Reporter'

    One of the users returned was the user we were logged in as so we took a closer look at the tables:

    select * from SecurityUser

    select * from SecurityPrincipal

    We compared those results to a whoami /user command prompt. We found that the SID from the command prompt did not match the WindowsSecurityIndentifier in the MR database. That is likely because of how the domain was restored after the ransomware attack. We updated the table to match for the specific user:

    update SecurityUser set WindowsSecurityIdentifier = '123' where UserID = '123'

    After doing that, we restarted the Configuration Console and the message about not being an Administrator and not having access to the companies went away. The legacy provider automatically showed up as well. We tested Report Designer and confirmed we could log in. When then deleted and re-added the two other users in the Security section so that their SID’s are now correct.

    John

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Is it Data Mart or Legacy?

    Hi John

    Crashing a SQL server and having to start from scratch is one of the worst situation for a GP Admin.. especially if nothing was documented and you have to guess everything.. SQL version, GP version & build, MR version and build, etc.. name it.

    There are a few tables in each systems that might help to figure out to some extend what version of your apps was configured and how.

    SQL : no way, unless you have a documented server snapshot to know the exact SQL version and build. At least you need to install the same version or higher (you cannot downgrade DB's during restore).

    GP:  DYNAMICS..DU000020 & DB_Upgrade.. those give you the best hint on the GP vers. & build

    MR: The DM database is usually not backed up, for the simple reason that it holds no reference data... only a consolidation / aggregate of other data (hence the word DataMart);

    Only the ManagementReporter DB is important to restore and from it you'll be able to figure out your previous installed connector(s) and version/build.

    Aside of Lisa's referenced link for the restore of the DB on a new SQL setup, here are a few queries that will help (the schema is valid for CU13 & up) :

    Select * from Reporting.ControlProperties  --> provide current version & build

    Select * from [Reporting].[ControlCompany] --> provides a list of the configured companies

    The company code can provide a hint of it's using the Legacy or the DM connector. A company code with no extension is generally using the DM, and those with -Curr or -AA are setup with the Legacy connector. But, be careful, as back in time when transitioning to the DM, the Legacy connector used to have the company code setup without any extension.

    Last but not least, MR Security:

    Select * from [Reporting].[SecurityUser] -- > list of all configured user in MR Security.. This hold both the internal UserID and the AD (Windows) SID, which is the one that is critical to let you into the MR configuration console and the client. So you have to make sure that on the new server, the Windows user SID matches with at least one of the listed user from the above table. If you can't find one that matches the WindowsSecurityIdentifier value, you'll have to substitute the string of this field with a new value from a valid Windows SID user code.

    Good luck.

    PS: I know how difficult this process is, as I went thru it a few times for customers that got hit by ransomware.

  • Suggested answer
    Lisa at AonC.com Profile Picture
    Lisa at AonC.com 3,141 on at
    RE: Is it Data Mart or Legacy?

    Have you tried following this community post/KB regarding moving to a new server?  It may help.

    How to move your Management Reporter 2012 database to a new server - Microsoft Dynamics GP Community

    Regarding the services stopping, check Event Viewer.  It should provide a clue.

    With GP 18.x upgrades we have been moving clients from Data Mart back to Legacy.  (We had moved them to Data Mart when Microsoft was pushing it.  Now that performance is much better with Legacy reporting, we prefer to avoid the extra maintenance of Data Mart when something goes wrong.)

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    RE: Is it Data Mart or Legacy?

    You are going to need to perform a fresh installation of MR. You cannot simply create a database called ManagementReporterDM. If you have a copy of the MR building blocks you can simply import them after reinstalling MR. If not they will need to redefine their reports. You need to allow MR create the services and databases. If they currently exist , delete them.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans