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.
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.
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
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.
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.
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.
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.
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.
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.
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.
The last screen is just the confirmation of all the options you selected in the previous windows. Validate this information and click on Install.
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.
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.
Enter the name of your data warehouse and click on NEXT.
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.
When your data is populate it will look similar to what you see below. Cubes are treated just like a Pivot Table in Excel.
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.