Open Forum

Expand all | Collapse all

Error on data mart rebuild for Management Reporter

  • 1.  Error on data mart rebuild for Management Reporter

    Posted Oct 30, 2021 11:42 AM
    #Technical #BIandReporting
    I am trying to rebuild the data mart in Management Reporter and bumped into the message in the screen shot.  The database this refers to has been deleted and does not appear in the database list in SSMS.
    Thanks in advance!
    Shelagh
    ​​

    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Error on data mart rebuild for Management Reporter

    TOP CONTRIBUTOR
    Posted 29 days ago
    Edited by Sean Reynolds 29 days ago
    Hi Shelah -

    Were you able to get this resolved?

    ------------------------------
    Sean Reynolds
    Accounting IT Manager & MS Dynamics GP System Administrator
    MIC Global Services
    Plano TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Error on data mart rebuild for Management Reporter

    Posted 29 days ago
    No I haven't.  Any suggestions?

    S

    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Error on data mart rebuild for Management Reporter

    TOP CONTRIBUTOR
    Posted 29 days ago
    I apologize for asking these, but it's just my troubleshooting mind at work:

    • Is your Management Reporter databases on the same SQL server as your GP database or a separate server?
    • Have you refreshed your SQL instance just to make sure the DM was deleted? 
    • Did you stop/start the services after deleting the DM? 


    ------------------------------
    Sean Reynolds
    Accounting IT Manager & MS Dynamics GP System Administrator
    MIC Global Services
    Plano TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Error on data mart rebuild for Management Reporter

    Posted 29 days ago
    No worries Sean.  I appreciate the help.
    • The MR databases are on the same server. 
    • I refreshed the SQL instance through SSMS.  The DM does not appear in the list. 
    • I did stop/start the services after deleting the DM.  


    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Error on data mart rebuild for Management Reporter

    TOP CONTRIBUTOR
    Posted 29 days ago
    Try running this script on the SQL server and see if your ManagementReporterDM is listed in the result set:

    SELECT name, database_id, create_date
    FROM sys.databases;

    ------------------------------
    Sean Reynolds
    Accounting IT Manager & MS Dynamics GP System Administrator
    MIC Global Services
    Plano TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Error on data mart rebuild for Management Reporter

    Posted 29 days ago
    I ran it ..the db is not in the list.
    I went back to configure and changed the name to DM2.  Got a little further and now in the deployment log it says the disk is full.  IT is working on that now.
    I will let you know what happens.  Thanks so much.
    Shelagh

    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Error on data mart rebuild for Management Reporter

    GPUG ALL STAR
    Posted 29 days ago
    Shelagh,
    Likely what happened is that whoever who deleted the DM database on SQL didn't have enough permissions on the disk level and the files did not get deleted, still using the full space and the name on disk.Which means any time you try to re-create the same DB name, it's going to complain as the file name is used by the system..
    IT need to clean-up the data drive.. .

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Error on data mart rebuild for Management Reporter

    Posted 28 days ago
    Hi Shelagh,

    If you deleted the DM db, then I suspect the issue now is you need to delete the log and transaction files.  The documentation is helpful telling you to delete the db, but it doesn't mention these files.  I had the same issue a few months ago after rebuilding our DM.  I had to comb through the log file to discover it was trying to recreate the ldf and mdf files, but the problem is they already existed.

    ------------------------------
    John Buchman
    Controller
    Rio Marine
    Houston
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: Error on data mart rebuild for Management Reporter

    Posted 28 days ago
    Hi John,

    Thank you.  I found the .mdf and .ldf files lurking about in another directory.  I am going to attempt the rebuild shortly but right now IT is solving our space issue.  I knew there had to be something else somewhere because the configure advanced once I changed the db name.  There are log and transactions files in the same directory now for the 'new' db.


    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: Error on data mart rebuild for Management Reporter

    Posted 26 days ago
    Update...  the database has now been deleted and IT cleaned up the drive a bit.  I was able to complete the rebuild but it didn't work.

    This apparently is way above my head.  Can you look at the error below and give me any suggestions?   THANK YOU!


    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 12.  RE: Error on data mart rebuild for Management Reporter

    TOP CONTRIBUTOR
    Posted 26 days ago
    Hi Shelagh -

    It looks like whatever credentials were configured to connect to the GP Database server are incorrect. Are you using the 'sa' credentials when recreating the connection?

    ------------------------------
    Sean Reynolds
    Accounting IT Manager & MS Dynamics GP System Administrator
    Atlantic Aviation
    Plano TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 13.  RE: Error on data mart rebuild for Management Reporter

    Posted 26 days ago
    Yes I am but it looks like it may need to be the NT Authority etc user?

    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 14.  RE: Error on data mart rebuild for Management Reporter

    TOP CONTRIBUTOR
    Posted 26 days ago
    Are you all using a service account, not NT Authority, to run the Application and Process services?  We have a specific named account which runs both services.

    ------------------------------
    Sean Reynolds
    Accounting IT Manager & MS Dynamics GP System Administrator
    Atlantic Aviation
    Plano TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 15.  RE: Error on data mart rebuild for Management Reporter

    Posted 26 days ago
    This is what I see for the service account.  If there is a better way to do this I am all ears.  Thanks again.
    S


    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 16.  RE: Error on data mart rebuild for Management Reporter

    TOP CONTRIBUTOR
    Posted 26 days ago
    In our environment, we have a separate SQL and MR console server for Management Reporter.  We also use a domain account (noted in the screen shot) to run the Application and Process services.

    I would try updating the Dynamics GP credentials with your 'sa' creds (assuming the password hasn't changed) and see if that get the adapter to connect.



    ------------------------------
    Sean Reynolds
    Accounting IT Manager & MS Dynamics GP System Administrator
    Atlantic Aviation
    Plano TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 17.  RE: Error on data mart rebuild for Management Reporter

    GPUG ALL STAR
    Posted 26 days ago
    Shelagh,
    Agree with Sean, MR 2012 specifically has to be setup with a domain Account, as directed in the installation guide from Microsoft.
    That same domain account has to be granted access on the SQL security side and given permission as 'db_creator' in order to create a MR database and the DM database.
    Make sure that domain account is setup as a service and granted local right to run as a service on the server where MR is installed. Make also sure that account has a password that never expires..

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 18.  RE: Error on data mart rebuild for Management Reporter

    Posted 26 days ago
    Thank you .. I will look at this tomorrow.   I really appreciate the help!

    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 19.  RE: Error on data mart rebuild for Management Reporter

    Posted 22 days ago
    Hi all,

    @SeanReynolds @Beat Bucher @JohnBuchman

    Thank you for your help.

    The rebuild seems to be working now.  I did not use the correct server name so the rebuild worked with errors as on the screen shot.  My fault as I should know the server name.  I called our GP partner and she spotted the error fairly quickly.   Changed the server name and used 'sa' credentials and the rebuild was complete in about thirty minutes.   Opened MR and all the reports, row, columns etc are there.  Also the fiscal to calendar year changes are there which is the trigger for the rebuild in the first place.

    There is still an error being generated in the Service logs that I do not understand but was told it isn't a problem.

    Shelagh​​​


    ------------------------------
    Shelagh Esteb
    NA
    Stafford TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 20.  RE: Error on data mart rebuild for Management Reporter

    TOP CONTRIBUTOR
    Posted 22 days ago
    I checked my Management Reporter console service logs and I don't see these same error messages, but to me this would be a concern.

    As @Beat Bucher suggested, I would have the IT group create a domain service account with a password that doesn't expire, add it to the SQL server with rights to all databases, configure the Application and Process services to use the new account then let's see if the messages continue.

    The only other thing I can think of is if you are logged into the Management Reporter console and it's using your credentials to try and pull data and you don't have proper security, it's possible this is what is causing the problem.  ​​​

    ------------------------------
    Sean Reynolds
    Accounting IT Manager & MS Dynamics GP System Administrator
    Atlantic Aviation
    Plano TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role