Deploying the GP 2013 R2 Analysis Cubes

One of the most amazing aspects for my management team is their ability to leverage a lot of the built-in reporting tools that come packaged with Microsoft Dynamics GP. The biggest asset to my CEO / CFO / Controller is the ability to instant access to mission critical information from inside our accounting software and they get that not only through traditional SQL Reporting Reports but also Analysis Cubes which I installed with no problem at all for Dynamics GP 2013 R2.  In this post I will guide you through the basic deployment of the Analysis Cubes for GP 2013 R2.

BASICS

Before deploying the cubes you will need to make sure that you have installed and configured SQL Server Integration Services and SQL Server Analysis Services. These topics are outside the scope of this posting but Microsoft offers a wealth of information on this.

In my dev environment I am running Microsoft SQL Server 2014 Developer Edition on Windows Server 2012 R2 Standard Edition.  I’m also running Microsoft Dynamics GP 2013 R2 with the default test company and sample data from Fabrikam. As always please test these instruction in a controlled development environment before trying to push out to production.

 

Getting Started

The first thing to do is make sure that you have the Analysis Cube Service Library registerd as an option for your Dynamics environment.  You may do this by going to the registration window under setup in GP.

Microsoft Dynamics GP > Tools > Setup > System > Registration

image

image

As you can see by the screen captures above this items is already registered for my environment. With that out of the way we can now continue on to the installation of the cubes.

Open your Dynamics GP 2013 R2 setup window and locate the option for Analysis Cube Server and click on Install.  It will ask you where to place the program files just like any other software install.  Once it’s completed you will notice 3 new icons on your desktop.

image

image

You will want to click on the option that matches your Microsoft SQL Server environment.  In my instance I choose the icon for Microsoft SQL Server 2014.  Once you click on the icon you should be presented with a screen that looks like the one below.

image

You will want to populate name of your data warehouse server, the cubes data warehouse name, and the password for your Dynamics data warehouse.  I would like to note that for the ease of the demo I supplied my SA credentials.  You might check with your DBA on what password to use here.

Once you have clicked on Next the screen below should now appear.  The information requested here is simply the name of the SQL Server you host your Dynamics GP information on and the name of the Dynamics GP system database.  Then hit the TAB button after you enter that and you will be prompted to select the companies you wish to deploy cubes for.  Since this is a test I only have one company Fabrikam so place a check in the box and hit next.

image

The next window you should really stop and take some time to think about.  The first thing is to determine what modules you would like to deploy cubes for.  Then you need to choose how you are going to bring the financial information in.

Things to really consider is that depending on how many companies you have, and how much history you have your SSAS could grow to be really big.  This will also increase the amount of time it takes to populate your data warehouse each night.  Again for the purpose of keeping it simple I chose all the options and did not specify any cutoff dates. If you want more information on these options refer to the included documentation on the setup page for GP 2013 R2.

image

On the next screen you will be asked to specify what cubes to create and to also populate your SQL Server Analysis server information. In my case my SSAS server is just HPDEV and I left the default name for the actual SSAS database name.

image

We are next asked if you want to include multicurrency information.  I do not have this function setup in my test environment so I simply clicked next.

image

The last part of the install is setting up a schedule for the data warehouse to be populated.  I chose to setup the schedule now and for testing I keep this at 1 time a day and it is to fire off at 7:30PM every night.

image

image

The last screen is just the confirmation of all the options you selected in the previous windows. Validate this information and click on Install.

image

This process may take some time depending on the size of your Dynamics GP environment. Once the install has completed you will get a window telling you that it was done.  Simply click on Exit.

 

Accessing Cubes

Now the fun part which is getting to open your Cubes.  In my example I use Microsoft Excel 2013.  The below steps will guide you on accessing your cubes.

When you open Excel click on the DATA tab and choose the option of From Other Sources and choose Analysis Services.

image

Enter the name of your data warehouse and click on NEXT.

image

The next screen will be a list of all the new cubes you just installed. I chose the Payables cube.  Make your selection and click on Finish.

image

When your data is populate it will look similar to what you see below.  Cubes are treated just like a Pivot Table in Excel.

image

Conclusion

That covers the entire process from start to finish.  I hope you enjoyed the content and if you have any questions or comments please let me know.

7 thoughts on “Deploying the GP 2013 R2 Analysis Cubes

  1. Mohamed Thaufeeq Reply

    Hi,

    I have installed the Analysis Cubes on my GP v2013 as per the instructions provided in here.

    Everything went good, and after installation, I can see the warehouse in the Database Engine and Cubes in Analysis Services. But I don’t know there are no rows / data neither in the warehouse database nor in the cubes.
    Am I supposed to pull the data to warehouse database or to cubes? If so, how can I?
    Can you please help me in this?

    Many thanks in advance.

    • Jonathan P Cox Reply

      I am sorry for the late reply as I had been on hiatus. If the cubes have no data in them this tells me that the SSIS packages are not running. If you open up SSMS connect to the SQL server hosting the data warehouse, under SQL Agent Jobs you will see a job for the SSIS package. Run this package and that will populate the warehouse. Then all you need to do is setup a schedule for the job to run. I hope this helps!

  2. Pingback: Deploying the GP 2015 Analysis Cubes « Jonathan P Cox

  3. Jimmy Reply

    Hi Jonathan,

    Thank you for the nice post! I followed the steps and it worked beautifully on my machine.

    But when I tried on another test environment with some real-world data, it threw out an error message saying something is wrong when calculating Retained Earnings Balances – failed with the following error: “Conversion failed when converting date and/or time from character string”. I have googled many places but without any luck, so I return to your post wondering if you can shed me some lights.

    It’s on GP 2015 with SQL 2012. Any idea or comment?

    Thanks,
    Jimmy

    • Jonathan P Cox Reply

      Are you installing over an already existing installation of these objects or is this just clean Dynamics GP databases and no cubes. Does the SQL environment on the test that worked match that of the others? It almost sounds like it’s found a data that it’s not able to fully convert. Any idea which module produced the error?

      • jimmyliangjm Reply

        Hi Jonathan,
        Thanks for the reply. I spent some time digging into the SSIS packages and, most likely, it’s because of the “GLTransactions” package. One of the steps is to “calculate Retained Earnings Balances”. What I found was that if the “Divisional Retained Earnings” box is checked in the General Ledger setup menu, it goes with a different path where the conversion error will occur. If I un-check the box, the whole process will go through.
        So now, I am checking if it’s because of some dates with bad format; or it’s a known bug as mentioned in a post from the forum (https://community.dynamics.com/gp/f/32/t/116275).
        Cheers,
        Jimmy

        • Jonathan P Cox Reply

          Jimmy,

          Wow what a really odd thing to have occur. I read the post you linked to and while I hate to assume, I’d be hard pressed to find it fixed in 2015 or even 2016. I’ve book marked that post and set a reminder to check back on it every couple of days as I really want to know for sure.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.