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
Okay. Thanks for the insights on this, Beat! It's much appreciated!
John
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.
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.
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
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.
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
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
/* 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
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.
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,113 Super User 2024 Season 2
Martin Dráb 229,918 Most Valuable Professional
nmaenpaa 101,156