How To Integrate GP Data Into Prophix

Prophix interacts with many types of ERP systems, but one of the most common ones is Microsoft Dynamics GP. If you have used Prophix before, you will know that it uses customizable dimensions to determine an information point where all of the dimensions meet called a fact when storing data. Many Prophix users insert this data into the tables manually or with spreading into data entry forms. If you would like to simply migrate data into Prophix from the GP tables, there is a better tool that exists in the Prophix interface: Process Manager.

Typically, you would either need to use macros or separate integration applications to pull data into an external reporting application. Both of these migration methods can be frustrating and bewildering. Thankfully, Prophix has an easy-to-understand integration tool built right into the application. Process Manager can be used to integrate data directly from GP tables or a flat file into the fact tables. By deploying custom views built by FMT into the SQL database, the views can be called into Prophix on a schedule so that the data can be updated automatically daily.

DISCLAIMER: Please be careful when making changes to SQL databases. Make a backup before you make any changes such as deploying views.

Importing Dynamics GP Data Into Prophix

The first thing that you need to do is to deploy a view that transforms the GP data in preparation for importing it into Prophix. Any information that you would like to pull in will need to be included as columns in the view. If you would like assistance deploying this view, please contact us.

After that view has been deployed to the company database, you can call the view using Process Manager. To do this, go into Process Manager in the Apps section of Prophix and create a new import process.

Click “Run Import Wizard” to start setting up the import process.

Create a data connector that connects to the GP database and call the view using the built-in query window in the wizard.

Set the import options depending on the data that is being pulled in by the view.

Assign the fields and then click on “Finish” when everything is mapped. It's a good idea to customize your view to match the names of the Prophix dimensions so that this step is easier.

In order to make sure that the fact tables are prepared for incoming data and they are updated properly, make sure that you add a Remove process to wipe the data and an Update process to update the cube once it’s all been added. For the Update statement, make sure that you select either “Process Full” or “Process Data”.

In order to set up a scheduled integration, you can click on the “Add” button in the scheduling part of the ribbon in Process Manager. First, set the name and frequency.

Set the start and end date for the process.

Finally, review the schedule information and click “Finish”.

Verify Dynamics GP Data Is Properly Integrated Into Prophix

If you would like to check to make sure that the data integrated properly, a good place to look is in Ad Hoc Analysis. This is Prophix's built-in table generating tool. If you are familiar with GP's SmartList, this is the same idea. The difference is that you will need to specify the dimension members that you would like to look at in Edit Layout. After the process manager process completes, use Ad Hoc analysis to verify that the data was imported.

Hopefully, this has given you an overview of the process for migrating GP data into Prophix and how to schedule these processes. As you can see, much of the data integration creation and management can be done directly in the Prophix interface. If you have any questions about this process, please contact us.