Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Switching from Data Ma...
Microsoft Dynamics GP (Archived)

Switching from Data Mart to Legacy Provider

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello:

If there is such a thing, can someone please send to me step by step instructions on how to "change" Management Reporter from the Data Mart to the Legacy Provider?

Yes, I'm well aware that Microsoft is going to stop supporting the Legacy Provider.  But, that date is not set in stone.

More importantly, the Data Mart is giving us erroneous results.  We can't keep recreating it. 

From what I understand, I'm not the only one "out there" who is having trouble with the Data Mart.

If I'm unable to get Management Reporter working for my company, I'm going to be fired.  Please help!

John

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Switching from Data Mart to Legacy Provider

    Okay.  Thanks for the insights on this, Beat!  It's much appreciated!

    John

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Switching from Data Mart to Legacy Provider

    Thank you Mark,

    This will prove to be very helpful in the case I have to switch my whole MR2012 configuration once from the Legacy to DM or back..

    John,

    I only tested one company (the largest) with the DM on my TEST system and decided that there was no enough performance gain to justify the move to DM... Actually it took more time to generate the same report over the DM than the Legacy connector. However, once the DM is enabled, you got a whole lot more stuff running on the server side and interfering with your GP databases, something I don't like. Plus the DM connector enables on SQL the Change Tracking for some of the DB tables... something that I ran into issues once when I was testing with the DM and couldn't figure out from where the problem came. One more reason to not use the DM to me.

    Change Tracking is something that a DBA should be aware of when a database is setup and an application implemented, aside of being fully documented.

  • Mark101 Profile Picture
    Mark101 139 on at
    RE: Switching from Data Mart to Legacy Provider

    John,

    I have converted clients from Data Mart to Legacy and from Legacy to Data Mart. Some clients run with both connectors. I prefer the Legacy connector. IT usually takes between 1-2 hours to switch - less if the server is fast.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Switching from Data Mart to Legacy Provider

    Ah!  I got it!

    Thanks for these, Mark!  Brilliant work, on your part!

    Please tell everyone at Tribridge that I said, "Hello!"  That's an awesome group!

    John

  • Mark101 Profile Picture
    Mark101 139 on at
    RE: Switching from Data Mart to Legacy Provider

    The scripts as written help you identify and convert report elements (reports and trees) from Data Mart to Legacy (from TWO to TWO-Curr)

    You can also flip the order of the values to convert from Legacy to Data Mart.

    When my clients need to switch between connectors, the manual process (switching each report and tree manually) can be tedious and slow. This makes it easier to accomplish.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Switching from Data Mart to Legacy Provider

    Hi Beat:

    So, in updating to CU14, were you still able to maintain the Legacy Provider?  

    If you switched to the Data Mart, were the figures in your reports accurate?

    Do you still have to recreate the Data Mart, in general, even after updating to CU14?

    Thanks!

    John

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Switching from Data Mart to Legacy Provider

    Hi Mark:

    Forgive my ignorance, but what is the "thrust" of these scripts?  Are they used to switch  from the Legacy provider to the Data Mart?  Are these run in connection with the CU14 update?

    Thanks!

    John

  • Mark101 Profile Picture
    Mark101 139 on at
    RE: Switching from Data Mart to Legacy Provider

    /* Legacy to Data Mart Scripts

    These are sample scripts that allow you to perform the update from SQL.  

    Please note, use these scripts at your own risk, and please be sure to make a backup before you begin!

    */

    --Run all scripts against the ManagementReporter database.

    --PLEASE make a backup of the Management Reporter Database

    /* Selects list of Companies

    - remove Reporting. for PRE-CU13 databases */

    SELECT ID, Code, Name

    FROM Reporting.ControlCompany --CU13 and higher

    /* Select a list of Reports and Tree’s that will be affected

    - remove Reporting. for PRE-CU13 databases */

    SELECT *

    FROM Reporting.ControlReport

    WHERE CompanyID=(SELECT [ID] FROM Reporting.ControlCompany WHERE [Code] = 'TWO') --CU13 and higher

    /* Selects affected Tree Definitions

    - remove Reporting. for PRE-CU13 databases */

    SELECT *

    FROM Reporting.ControlTreeDetail

    WHERE CompanyID=(SELECT [ID] FROM Reporting.ControlCompany WHERE [Code] = 'TWO') --CU13 and higher

    /* Update Reports currently set at 'TWO-Curr' to 'TWO' company

    - remove Reporting. for PRE-CU13 databases */

    UPDATE Reporting.ControlReport

    SET CompanyID= (SELECT [ID] FROM Reporting.ControlCompany WHERE [Code] = 'TWO-Curr')

    WHERE CompanyID= (SELECT [ID] FROM Reporting.ControlCompany WHERE [Code] = 'TWO') --CU13 and higher

    /* Update Trees currently set at 'TWO-Curr' to 'TWO' company

    - remove Reporting. for PRE-CU13 databases */

    UPDATE Reporting.ControlTreeDetail

    SET CompanyID= (SELECT [ID] FROM Reporting.ControlCompany WHERE [Code] = 'TWO-Curr')

    WHERE CompanyID= (SELECT [ID] FROM Reporting.ControlCompany WHERE [Code] = 'TWO') --CU13 and higher

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    RE: Switching from Data Mart to Legacy Provider

    Hi Mark,

    I got all of a surprise when I tested the latest CU14... in previous upgrades, when I tried to add the DM to my existing Legacy connected companies, MR would create new entries for every company with the extension -CURR... In CU14, it replaced the existing company entries completely !!!  Which in some ways is OK, as you wont have to change all the definitions in your reporting trees. However, un-installing the DM of CU14 doesn't reverse the settings for the companies... rendering your legacy company entries useless.. unless you un-install everything and have to re-add it, but even when I did this, CU14 was naming the Legacy connected companies with the -CURR extension !?!?..

    So I had to resort to SQL to update the connection strings in my MR2012 database so I could continue to use the Legacy connector with my existing definitions..

    Would love to see your scripts to reverse the DB entries :-). Send me a PM about this if you don't want to publish them here.

  • Mark101 Profile Picture
    Mark101 139 on at
    RE: Switching from Data Mart to Legacy Provider

    Just an FYI - you can switch the building blocks from Data Mart to Legacy using SQL. I have converted several clients between the two connectors and the SQL scripts are fairly simple. They need to be run for each company.  Email me if you want a copy.

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,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans