In my last post, I continued on the “know your environment” topic. So far I have 2 parts to that with a background on the elements to be aware of with core Microsoft components of Dynamics GP plus ISV products, where they are modules/functionality within the Dynamics GP client. In this post, I will delve into external ISV products.
Posts in the series
What external products have access to Dynamics GP?
This is not quite as “universal” to describe as the previous sections – modules within the GP client itself. There would be a few obvious places to start if you are unsure what external products you may have.
First, let’s start with the 2 most obvious places where an external integrating application is likely to exist – getting data into Dynamics GP (an integration tool) and getting data out of Dynamics GP (a reporting or BI tool). Even with core Microsoft-only installs, there is Integration Manager and Management Reporter, to name one of each type of tool.
Many clients have an integration tool, even if it was Integration Manager “for data conversion” during your implementation. Historically, if a client didn’t have a need for ongoing integrations of data to Dynamics GP, they still had Integration Manager installed and often with temporary keys, that were good for 90 or 180 days or something along those lines. That was long enough for a partner or consultant to get your system configured and your opening balances and master records integrated relatively cheaply and easily. If that’s the situation with your environment(s), that’s OK. It might still be installed somewhere and if it’s not registered any longer, there is no need to upgrade it or install a new version of it. The other two most common integration tools as it relates to Dynamics GP are SmartConnect and Scribe. (As I write this, Scribe appears to be an old product name so the link is to what seems to replace it, although they still list Dynamics GP as a valid connector). That being said, GP runs on SQL server and there is no shortage of tools that can interact with SQL server so it isn’t possibly to list one or two things to look for. I will be speaking more about integrations in another topic in this series.
On the reporting side of things, if you are investigating what tools your team(s) are using in your environment, there are three primary types of reporting I would ask about:
- Financial Reporting and/or Budgeting/Planning
- General reporting tools
- Business Intelligence tools
While Dynamics GP has some internal financial reporting capabilities, my experience is it’s pretty rare that there isn’t some kind of external tool dedicated to financial reporting – FRx (some clients may still utilize this), Management Reporter or there are ISV solutions in this area as well. Check with your accounting/finance teams on this one. It most likely not going to be a generic reporting tool like Crystal or SSRS that is used for financial reporting.
In general reporting, as I list above, the most common tools I see are Crystal Reports and SSRS (SQL Reporting Services) as well as plain old Microsoft Excel. Not only does Microsoft Dynamics GP have an ability to publish a default set of SSRS reports, it also has a default set of Excel reports. From an upgrade standpoint, all of these will have connection strings to a server to consider. If your environment has these, and they haven’t been modified at all, it would be easiest to republish after the upgrade to automatically republish items with the right connection strings. Focusing on the reports that have been altered or customized to your environment, and/or specifically built for your environment will be the key.
On Business Intelligence tools, the most common ones would be Power BI, Tableau and others in the space. In general terms, from an upgrade perspective, reporting tools that connect to GP are connecting to the SQL server so a change in server name may mean needing to update reports too.
Beyond integration and reporting tools, there could be a long list of possible applications integrating to or from Dynamics GP. If your environment has any applications or websites around time tracking, payroll/HR, expense reports, payables automation, warehouse management etc., those may be things with an integration point or connection to your GP environment. That’s, of course, just a short list of examples of applications I have seen.
If I am reviewing someone’s environment for things that touch GP, I’m going to start with the SQL server to see what logins exist. Dynamics GP logins are encrypted to prevent using a GP login outside of Dynamics GP. So, compare your GP user list to the SQL logins you see, and ignore all of those for now.
What other SQL logins are there that don’t correlate to Dynamics GP user IDs (outside of the obvious service accounts for SQL services itself)? What service accounts are listed? Often there is a distinct naming convention on service accounts but at the core, any login that isn’t a GP user, ‘sa’, or ‘DYNSA’ means a possible connection to GP data. Check the services running on the SQL server, and what accounts are used to run them. If any of the services are accounts that also have SQL access, the names of the services may give you a clue. Also, if you have web servers, it’s pretty common that external applications are web-based and they may not be installed or run on the SQL server machine itself. Check services there, and IIS to see what may be there that could be related to GP.
Back to SQL, there are SQL scripts you can run to identify what unknown logins have access to and what permissions are granted to the objects (“select” access is one thing (reading data), “insert, update, execute” types of permissions are another thing entirely). Non-GP-user logins that have DYNGRP roles are also worth looking at as the DYNGRP roles give VERY broad permissions that typically are not needed if a user just needs access to run a report. That’s a whole story for another day!
Caveat: there are customers that have other applications on the same SQL server instance as GP, which are entirely unrelated to Dynamics GP. While this typically isn’t recommended, it also means it could be a little bit harder to identify what logins are used for what purpose. Seeing what has a server role like sysadmin (access to all databases) and what has specific user mapping to any of the GP databases is important.
What is harder to find are things on OTHER servers in your environment that may have a connection to GP because you may not see that on the GP side. For example, machines may have an ODBC connection to read Dynamics GP’s data pointing to the SQL server instance. During an upgrade, it’s those small things that can be forgotten – whatever it’s doing to read or write to GP is going to the wrong place if the old servers are still running after going live on a new server.
Once again, this is a long-ish post and there are parts of this just scratching the surface! Coming up in the next few posts I’ll dig a little deeper into integrations, reports, and customizations to name a few upcoming topics!