Welcome back folks. Today I wanted to go over a quick and simple deployment of the built-in cubes that come with Microsoft Dynamics GP 2016 and how to connect up to them using Microsoft Power BI. This post is all going to resolve around a local setup on a single machine. I’m working on a more detailed post that covers this in a more typical work place environment with a domain controller, and some SQL servers setup.
Getting started is very simple you only need 3 things setup to follow along at home on your own laptop or vm. You will need a copy of Microsoft Dynamics GP 2016, a copy of Microsoft SQL Server 2016 (Developer Edition), and to have Microsoft Power BI Desktop application downloaded and installed.
In this post I’m not going to guide users through the process of installing SQL or the configuration of it as that is outside the scope of this post. I am also making the assumption that you have Dynamics GP 2016 installed and configured as well. So now onto getting started.
This window will guide you in setting up the initial data warehouse for Dynamics. When you populate the name of your SQL server as shown in the 2nd screen capture.
I normally leave these as defaults but you will want to change to fit your needs. Then you will need to enter a password for your data warehouse. In this example I used Pa$$w0rd
Then we click next and you are now presented with this screen.
I type in my SQL server name and hit TAB. Please note that the setup window makes the assumption your system database is DYNAMICS in all capital letters. If you don’t change this to match the actual name nothing in the company list will populate. I click the box and select my company and then click on next.
The next window you get to looks rather complicated but in honesty it is not. This is where you tell the system which of the integration services you want to gather data from.
The next screen we come to is where we specify which cubes we want installed in our analysis service. In my example SSAS is installed locally on my laptop so I will type in my SQL name and hit tab.
Click the next button to continue onwards to glorious cubes!
The above window will allow you to bring in multicurrency information if that is configured in the environment. Click next to continue on.
This next window is very important. If you recall earlier in this documentation we mentioned integration services. This is where you configure and setup a schedule on how often those packages are executed. In this area there is no right or wrong way to setup the schedule as each environment is different.
In my example I say run this job every day at 10:00 PM and there is no end date for the job. In another post I’ll outline how to change this information at the SQL level once the cubes have already been installed.
The final screen is the summary screen outlining all the information you submitted.
This brings us to a decent stopping point. In the next part of this series we’ll examine opening Power BI and getting connected to our cubes.