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 and 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 blogs (modules within the GP client itself). There would be a few obvious places to start if unsure of what external products may be in use.
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 the initial implementation. Historically, if a client didn't require 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 the system configured and the opening balances and master records integrated relatively cheaply and easily. If that's the situation with this 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 the SQL server and there is no shortage of tools that can interact with the SQL server, so it isn't possible 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, investigate what tools the team(s) are using in the 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 most clients have 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 the 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.
As far as general reporting is concerned, 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 the ability to publish a default set of SSRS reports, but 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 the 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 the environment, and/or specifically built for the 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.
What else?
Beyond integration and reporting tools, there could be a long list of possible applications integrating to or from Dynamics GP. If the 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 the 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 the GP user list to the SQL logins list, and ignore all of those for now.
The other SQL logins that don't correlate to Dynamics GP user IDs (outside of the obvious service accounts for SQL services themselves) should be investigated to see why they are there. 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 be a clue. Also, if there are 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 that can be 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: some customers 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 the environment that may have a connection to GP because they may not be visible 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.
Summary
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!