Open Forum

Expand all | Collapse all

Dynamics GP Analysis Cubes

  • 1.  Dynamics GP Analysis Cubes

    Posted 6 days ago

    We're attempting to get Dynamics Analysis Cubes up and running. We're running into a problem that we've been able to reproduce in two different test environments, each on different SQL servers.

    We're running Dynamics GP 2018 R2

    SQL 2016 SP2

    We run through the setup wizard and everything seems fine.

    There are 40 companies selected.

    The only module we are selecting is Financials

    We select the option to create the agent job.

    After the wizard is done, we run the agent job to populate the warehouse Db.  This is where the problem is. The job will fail with the following error:

     

    Code: 0xC002F210

       Source: Calculate Retained Earnings Balances Execute SQL Task

       Description: Executing the query "select 1 from  SystemVersion where CompanyID = ? -..." failed with the following error: "Conversion failed when converting date and/or time from character string.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    End Error

     

    The really odd thing is that it's not always the same step ID/company that this happens on. Maybe it happens on the first company one time, then the next time it's the 15th, then the next it's 8th, then the 30th….. etc. Even though it doesn't seem to have any pattern to the which company it errors on, it does seem to be the "DynamicsGPWarehouse_Package_Master" step associated with the relative company that's always failing.

     

    We haven't been able to make it through the entire job once. There isn't any other activity going on in the dynamics environment during this time.

     

    Any help would be greatly appreciated.

     

    Thanks



    ------------------------------
    Lynne Mosiman
    Green Plains Inc
    Omaha NE
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Dynamics GP Analysis Cubes

    GPUG ALL STAR
    Posted 2 days ago
    Edited by Beat Bucher 2 days ago
    Hi Lynne,
    I'm feeling your frustration and pain.. To be honest, you're the first GP client I read about trying to install the Excel Analysis Cube add-on for GP.. in 15+ years I've been using GP, I never came across a single setup where it was deployed..
    I had played with the mind of installing it years ago while I still was working as a GP Admin for a company (on the user side) and always pushed it back as I had better things to do :-).
    Recently a client of mine asked if that would be something to use, as I was going over a full-loaded GP setup they never really understood and barely used 20% of it.. And my answer was: "nowadays there are way more efficient tools then Analysis Cubes IMHO to tackle this challenge, like PowerBI Desktop and Excel PowerQuery tools, or SSRS".
    I know it's not quite the same, but in the end, what you want is looking at the data and be able to "slice & dice" it at your hearts content, and PowerBI is really good at it, and Excel too up to some degrees.
    Why go the hard route of installing (and maintaining) Analysis cubes for Excel (that requires extra SQL setup) when there are simplier methods to get the data.
    I'm stil wondering why that software is still part of the setup in GP's distribution DVD, though I think it hasn't been updated in years possibly going back as far as GP2010.
    I know it's probably not of much help, but the only recent blog post I found about Excel Analysis Cubes is by @Ian Grieve over at AzureCurve UK, which goes back to GP 2016.
    Hands On With Microsoft Dynamics GP 2016 R2: Configure Analysis Cubes – azurecurve

    This short video from Armanino back in 2014 shows how to use it in GP : https://youtu.be/aloEPVhuZ0o

    PS: this training video about Excel shows how you can actually create a dashboard with just Excel & PowerQuery (not GP specific)
    Since GP already has a wealth of embeded SQL views for more modules in GP, it is fairly quick and easy. How to build Interactive Excel Dashboards
    YouTube remove preview
    How to build Interactive Excel Dashboards
    Download file used in the video with step by step instructions and links to more tutorials: https://www.myonlinetraininghub.com/workbook-downloads In this vi...
    View this on YouTube >


    ------------------------------
    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


  • 3.  RE: Dynamics GP Analysis Cubes

    TOP CONTRIBUTOR
    Posted 2 days ago
    If I remember correctly - Charles Allen used to do webinars for me at GPUG on this.  Charles Allen of BKD in Houston.

    ------------------------------
    Kim Peterson
    701-261-7307
    Kim@DynamicsConnections.com
    #KimPetersonGP
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Dynamics GP Analysis Cubes

    GPUG ALL STAR
    Posted 2 days ago
    Lynne,
    Kim is right, there is wealth of recorded webinars about GP reporting and data analysis here:
    https://www.gpug.com/communities/community-home/librarydocuments?communitykey=f5effb5c-1971-4d11-af16-0817422d8c8d&tab=librarydocuments&LibraryFolderKey=&DefaultView=folder

    Also, there was an interesting thread about this topic with lots of good information to use PowerBI:
    https://www.gpug.com/communities/community-home/digestviewer/viewthread?MessageKey=ddb6dd4b-581d-44a5-aa39-12456beddccb&CommunityKey=4754a624-39c5-4458-8105-02b65a7e929e&tab=digestviewer#bmddb6dd4b-581d-44a5-aa39-12456beddccb

    Also, this thread suggests that the product itself might be very sensitive to the SQL collation :
    https://www.gpug.com/communities/community-home/digestviewer/viewthread?MessageKey=04f4135d-23cc-40f4-a9eb-421caea635c3&CommunityKey=4754a624-39c5-4458-8105-02b65a7e929e&tab=digestviewer#bm04f4135d-23cc-40f4-a9eb-421caea635c3

    Good luck.

    ------------------------------
    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


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