Dynamics GP to PerformancePoint Planning Integration Wizard Review
Posted by Alan on July 16, 2008
This will be a long post, so go grab yourself an adult beverage (given that it is summer, may I suggest a Mojito) and get comfortable…
Microsoft has released the integration between Dynamics GP 10.0 and PerformancePoint Server 2007. This integration works in conjunction with the PPS Data Import Wizard and basically creates and out-of-the-box data provider for the wizard. You can find the code for it out on PartnerSource. Based on my playing with it, I can pretty much confirm my earlier thoughts on the subject. In a nutshell — the integration does what it does and that is provide a basic level of integration. However, in my opinion, most Planning deployments will probably need more than this provides. When I have a chance I will do a follow-up post on the integration and how it works with Management Reporter.
A couple important things to note in advance:
- There is no documentation for this product yet! None! Nada! Zip! WTF! Therefore, much of this post is based on my limited (albeit direct hands-on) experience coupled with my “guessing” of how things are working. This has been supplemented by some great help and information I was able to get from a couple of escalation engineers at MBS. As a side note, documentation of some sort is to be released on TechNet at some point.
- The integration does not import data from the Dynamics GP transaction tables, but uses the DynamicsGPWarehouse data warehouse that is created with the installation of the GP Analysis Cubes. This is important to remember — if the customer has not purchased the cubes, then the integration won’t work.
- Currently support is being provided by the MBS group out of Fargo that deals with the analytics components of Dynamics GP. They will take support cases in regards to the wizard and can take them to a certain point and then will hook you up with PPS support directly if needed.
- There are some bugs. I found a major one that has been verified and submitted to development, I have two others in the process of being verified and I heard of another one that has been submitted by someone else. At the time of me writing this post, I have about 4 hours in with support trying to troubleshoot these issues.
In order to test and learn the integration I took the current Microsoft BI image (Business Intelligence VPC Release 6.vhd) and restored a copy of the DYNAMICS, TWO, DynamicsGPWarehouse SQL databases and the GP Analysis Cubes from the latest Microsoft Dynamics GP image (Microsoft Dynamics GP 10 Technical Demonstration Toolkit.vhd). So in all fairness this is not a true production setup but with no documentation available I wasn’t about to mess up an existing system if something went wrong.
How does the integration work? To put it simply, the wizard walks you through a data mapping process. It looks at your GL setup and maps key items in Dynamics GP to PerformancePoint. It creates both dimensions and transfers over actual GL financial values. For instance, if you were working with the Fabrikam (TWO) sample company, it has a chart of accounts structure that is 3 segments long XXX-XXXX-XX. The first segment is department, the second is your natural account and the third segment is your sub-account value. Within PerformancePoint, the wizard creates 3 dimensions for you out of this structure. The first maps to a brand dimension called Segment1, the second maps to the Account dimension and the third maps to a new dimension called Segment3. Other information maps as well such as your different Dynamic GP companies mapping to the Entity dimension and GP currencies. I am not going to post a full list here you can run the wizard to get the complete set, but this gives you an idea. In addition to creating the dimensions and dimension members the wizard also transfers over the actual values in the accounts.
So what are my impressions after working with the tool and spending time with escalation support? Here there are:
- The wizard is easy to use. You don’t need to know SQL or SSIS or MDX or anything really technical to use it.
- It automatically creates the proper dimensions for you based on your setup within Dynamics GP including currencies and transaction dates and other additional important GL related information (pretty much everything you would need).
- It is smart enough to differentiate a chart of account structure that is 3 segments vs. 4 segments vs. 10 segments.
- It also transfers over the actual financial values that have been inputted into Dynamics GP
- The way the wizard maps over GL data you are “forced” to pretty much continue to budget on a GL by GL account basis. I see many companies today that want to budget at either a higher level or more granular level than their GL supports.
- The wizard does not map over any non-GL type information. So it does not help with companies who want to use PPS for sales or production or HR type budgeting/forecasting. This for me is probably the biggest let down of the product.
- If you don’t like the default names of the dimensions (e.g., Segment1 when you want it to show as Department) you cannot change them while in the wizard. You have to create the dimension first and them map to that specific dimension. It is an extra time consuming step but not a make or break.
- No documentation exists as of yet to help you use the tool. Although I would think that this will be remedied eventually.
- There are some bugs and one of the ones I found is a major one at that. If Account Type does not populate correctly and you have to manually adjust each Account and the associated Account Type one at a time then the amount of work required would make this a show stopper. That being said, Microsoft will end up getting these bugs fixed — the question will be simply of how long it will take.
- There is no ability to automatically schedule the Data Import Wizard (as far as I am aware) to run to update financial figures or dimension members. Someone must be tasked (and remember) to run the wizard on some sort of regular basis given the specific situation. Eventually someone will forget, be on vacation, etc., and this step will be missed.
So what do I think? I think that this wizard is a good start and will help for certain deployments, but ultimately it is not what it needs to be in order to be truly useful long term. For that to happen, Microsoft has to continue to enhance the integration and at a minimum add a scheduling component and the ability to pull over non-financial data such as customer, vendor and inventory related information. As far as we are concerned, I think that we might use it for some of our more basic PPS deployments with our Dynamics GP clients, but more likely we will end up creating our own SSIS packages to handle the integration and movement of data.
I took screen shots along the way and they are posted below. The first 11 are the various setup screens of the wizard and the last 4 are some of the results. Many of the images have notes embedded in them. I hope these are useful.