Using Power BI in your GP to BC Master Record Migration
One of the most painful parts of leading a BC implementation project is waiting for good, clean data from the customer’s legacy environment. If migrating an ERP is new to you, especially for the large number of GP users and environments that will likely be re-implementing Business Central over the next 10 years, it should be known that your partner can set up your new environment much faster when we have good, clean, master record data.
Not having this required data is often a choke point for project managers, and can ultimately be the root cause of project overruns. It just seems so easy to run a bunch of SmartLists and export to Excel, doesn’t it? Of course! But there are seemingly insurmountable hurdles or excuses to not having the data prepared on time, such as someone with proper permissions needs to log in and do the exports, or the data is dirty or incomplete, or nobody wants to or has time to do the actual work. After all, it could be a lot of files to manage, and what columns do you need anyway? Suddenly this simple task got complex.
That’s where Power BI comes in. And this free report I created and will maintain to help you get this critical task done efficiently.
My latest energy in helping users point their ERP vessel in the Business Central direction, and chart a new course in a thoughtful and measured way, is a GP to BC Master Record Analyzer report. The idea of this report is to overcome the challenges of getting the key data out for everyone to begin reviewing.
Previously on the Migration Station page, I had posted an analysis of the GP Master Record database tables. The goal of that deep-dive analysis was three-fold:
To identify GP master record SQL table fields from each record type that matched BC fields cleanly (you definitely need these fields; this is what my new Power BI Report gets for you! Read on!)
To identify the GP SQL table fields that did not map cleanly to BC (you probably won’t need these fields, but more fields are easily added to the Power BI report queries)
To identify the BC master record table fields that did not have a good match coming from GP (a great opportunity to explore what else BC has to offer, but this also sounds like a great post for another time!)
My Master Record Analyzer is primarily based on the same SQL views the SmartLists in GP use. I did this because the column headings translate cleanly to plain English, and the enums and integers that live in their respective data tables are translated nicely as well, which helps dramatically with readability. Some of the tables I added to the model don’t have a SQL view or SmartList, so I pulled in the table instead. As I continue to work on this report and incorporate end-user feedback, I will update the posted release.
The idea here is simple. Download, connect, refresh, save.
Grab and Go? Dine and Dash? Smash and Grab? All of the above, actually.
You now have all of your master records in one, portable file to use for analysis, discussion, and eventual export for loading to BC! This can be great for helping the team stay focused on their daily tasks, while moving forward with the initial sandbox environment setups that are needed in Business Central to perform initial walk-thrus and familiarity training. Seeing your GP data in BC goes a long way, and the master records are where it all starts.
The first page of the report has the instructions for connecting the free download to YOUR Dynamics GP SQL Server, System and Company databases. These are configured as parameters in the report, so the first step is to assign these. The Power BI report will detect a change, but you still need to confirm authentication is reset using YOUR credentials per YOUR GP environment. The user you choose to connect with can be a domain user or a SQL database account.
The second page comes from the DYNAMICS database and pulls in the Users, Currencies, and Companies to the report. Nothing fancy, but it is real data for you to start with. You may not need all of these values or companies in BC, but you can start to see how quickly Power BI can extract useful intel that will help with your migration project. You can also see how many records of each list exists in GP on each dashboard.
The User list you initially see will probably have active users who should be inactivated or deleted in GP. It is also likely not all of them will end up becoming Business Central users.
The Currencies list can be somewhat misleading, but if you truly are in a multicurrency environment, this is important information to have. In BC, unless you use more than one currency, you don’t need to set up any currencies.
The Companies list will include both production and test companies, and not every company may end up migrating to Business Central.
The third page displays some of the important setup lists that reside in the COMPANY database, including Payment Terms, Shipping Method, and Salesperson ID.
Every vendor and customer has a Payment Terms field, so you want to take a look at the list and make sure you clean it up. Also entertain the idea of converting to the convention typically seen in BC. Any terms on vendors and customers should exist in the setup list, and would need converted in their respective files as well.
The Shipping Method field from GP opens up to multiple fields in Business Central (Shipping Agent, Shipping Method) so review how this impacts your BC setups.
The Salesperson list in GP has a counterpart in Business Central, however you may wish to revisit the IDs and naming convention and expunge any unneeded records. Some BC environments sync their Salespeople from D365 Sales (CRM) and that potentially allows for a completely fresh start.
The Sites list also has a counterpart in BC, but like the other master records, it is good to review this list for accuracy and completeness.
The rest of the dashboards are purely master record extraction, discussion, and analysis devices.
Each page initially displays the total number of records found in the database, and also has a series of suggested filters that can be used to whittle down the selected records. Some of the filters are based on status, some based on class, others based on relative last invoice or payment date. You can even remove my filters and add your own!
Leading with an example from the Financial series, the GP Accounts dashboard page returns all of the fields needed to map to Business Central. Keep in mind, however, BC has Dimensions instead of Segments, so the final product that you load to BC gets much more refined. This free analyzer helps you filter out the inactive accounts, narrow down Account Categories, and review the accounts and dimension values you have.
One thing to keep in mind about all of these dashboards - if you find bad or incomplete data, or want to inactivate records so they are excluded from future dashboard refreshes, fix them in GP! The analyzer can be used offline from GP to review data and prepare exported lists for importing to BC. The report does not have a direct link to GP to go change anything, so if you wanted to clean up GP, do that in GP. And then refresh the analyzer, of course, to see the fruits of your labor. Clean data = good data.
I think you get the idea at this point, so here come the rest of the reports included in the free Power BI dashboard! As new ones are added, I will update this post.
This is a free digital download available from the Downloads section of the site.
You will need a valid email to get the link for downloading.
There is no payment info required, even though the checkout makes it look like you may need to enter some of your address just to get through the form.
Happy Analyzing!
I’m grateful should this experience or these insights be helpful to you on your journey…until next post!