Integrating D365 PSA and Finance & Operations with the CDS Data Integrator - Customers, Vendors and Contacts

Previously, I posted on getting all of the prerequisites set up in order to integrate Dynamics 365 for Project Service Automation with Finance and Operations. Now that I have my Environment, Connections and Connection Set in place, I'm going to run through the general set up for integrating Accounts in Customer Engagement (CE) to both Customers and Vendors in FinOps, as well as Contacts. Obviously, different integrations will have different requirements which will mean you may have different mappings, but this post will run you through the templates (if they exist) that you need and any amendments all integrations would require. As standard, you'll need unique IDs on the entities you're integrating. You could create these manually and use an Auto-Numbering solution, or there is a ready-made Prospect to Cash (P2C) solution you can install from AppSource that does this for you on the majority of entities in CE - 

Integration Keys
Prior to running any projects, ensure you have integration keys against both the Account and Contact entities. You can do this by navigating to your Connection Set and opening the Integration Key tab as seen below. As I've installed the P2C solution, I'm using the out of the box (OOB) Account Number and Contact Number fields but you can add any fields you've manually created here too that act as keys.

Customers

So the easiest to set up is Customers, as Microsoft have done the majority of work for you already. In order to make sure you only pass Accounts over to the appropriate data entity in FinOps, you'll need fields to capture the type of Account. You could use the out of the box (OOB) Relationship Type field in CE, but I've created two new fields in case an Account can be both a Customer and a Vendor. 

Another customisation you will need to make in CE (unless you can default it in PowerQuery) is Sales Tax Group and Customer Group. As you can see below, I've created two option sets which will map across to the relevant fields in FinOps. 

It is worth noting that for Currency I am mapping over to ISO codes in FinOps and I have also taken out the OOB tasks mapping Sites and Warehouses. Microsoft recommend defaulting these values, although it depends on whether you use either Sites or Warehouses in your FinOps implementation. 

Once you have added the relevant fields to your Account form (you may need to add more depending on requirements), go to www.admin.powerapps.com and create a new Project. Select the below template 'Accounts (Sales to Fin and Ops) - Direct' and give your Project a suitable name that follows the same convention as your Connection Sets etc.

Then select your Connection Set, Organisation and click Create. You'll be redirected to your Project, which will contain one task. 

If you open the task, you will see the pre-populated mappings - 

You can add new mappings, and by clicking map type you can add transformations. Tip: although you can do all of these in Advanced Query, if you add all of your mappings here with it disabled and enable, it will automatically add all of your current transformations to your queries. Here you can set default values, truncate and map values. For instance, for certain fields where they don't exist in CE but you want to default them in FinOps, you can default them with a transformation. 

Alternatively, you can map fields from an option set/free text field to values in FinOps. For instance, the below is mapping I mentioned earlier for the OOB Country/Region field in CE to ISO codes in FinOps. 

To set up Customer Group and Sales Tax Group, you'll need to create new mappings. 

You can then add your transformations so that certain option set values map to values in FinOps. You will need to grab the values from your fields in CE and the codes in FinOps.

Then you can add your mappings (tip: ensure there are no commas or spaces in your values).

Now I have my mappings set up with the relevant transformations, I need to be able to run a query so only Accounts marked as Customers in CE are pulled into FinOps. In order to do this I need to enable Advanced Filtering. Tip: this cannot be undone and it will enable it for ALL tasks on your integration project. Also, if you have one field going mapping to two fields, you'll need to create a conditional column in your table for one as this will throw back errors.

Once enabled, open the Advanced Query and Filtering section. Tip: if you get errors here and are unable to see tables below, check both the URL and schema. Ensure that you have your custom fields in there and amend any schemas as necessary. Unfortunately it can very much be finding a needle in a haystack. 

So now I have all my transformations, I need to filter to ensure only Customers are integrated. Go to the data section and locate your customer field. Right click the field, and filter on the values. 

Insert the step and enter the filter criteria below.

Tip: double check that your rows have been filtered afterwards and only the data that meets your criteria is now picked up. 

Vendors  

For Vendors I created a new task with the below entities on my existing Project. 

Most of the tasks are the same as Customers, although there are different fields you will need to use. Rather than Customer Group, you will need to map to Vendor Group instead. I've created a Vendor Group option set field that uses Business Rules to make it mandatory and visible when Supplier=Yes.

Other key fields that aren't included in the Customers task you need to map to include the following - 

• Vendor Party Type
• Vendor Party Number
• Vendor Organisation Name
• Vendor Account Number

Contacts
Contacts is relatively simple and you could always create a new Project and use a template. However, for Contacts I created a new task with the below entities so I can keep my Accounts and Contact integrations all in one Project. Then I know that they will sequentially run and any when any new Contacts are mapped across, the Account should have been integrated already too.

Below are just a few of the fields you can map, but Associated Party Number (associated Account Number in CE) and Contact Person Party Number (Contact Number in CE) are two that MUST be mapped along with First Name, Last Name etc.

Run Project
Now I've set my Project up, I can run it. 

After running (you can run it with all data for migration purposes), you will be redirected to Execution History. If you open this (give it some time), it will go and pick up any records that meet your criteria that have yet to be integrated. 

It will look at the Modified On field in CE and if there are changes, you will get upserts against tasks which could either be new records created or updates to existing records. Tip: in Finance & Operations, the Data Management section under Workspace will give you more information about your import https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/data-entities/configuration-data-projects?toc=/fin-and-ops/toc.json. It provides you with an import log and the error messages are much more rich in information. You can see whether something made it into staging and if a field not being populated was the reason something failed.

After a few refreshes, there we have it - success! At least each task has created or updated a new record in FinOps. As you can see, there is a warning on my Vendors task, so I can either click into the Task's execution history or check FinOps to see what the error was.

Tip: once you're happy with your Projects, it's always useful to save them as Templates, in case you want to use them in the future or to add them to new mappings between Business Units and Companies.

Tip: it's always worth setting up email alerts for any warnings or failures. You can do this under the Scheduling section of your Project.

Integrating D365 PSA and Finance & Operations with the CDS Data Integrator

Over the past few months, I've been delving deep into the realms of integrating the different modules within Microsoft Dynamics 365. One thing I've noticed when coming up against obstacles is the sheer lack of content online for this aspect of the Common Data Service (CDS). As a result, I thought it would be a great chance to blog about the common issues I've come across and the necessary configuration when integrating Project Service Automation with Finance and Operations. 

Prerequisites 

In order to use the CDS Data Integrator (DI), I needed Administrator access and licenses to the following:

• Power Apps
• D365 Customer Engagement
• D365 Finance and Operations

First Steps - Environment

The first step to using the DI is to create an Environment (if you use Flow or PowerApps you'll be familiar with this concept already). An Environment essentially stores all of your flows, apps and data in one place. A default one will be created for you, but it's always good practice to create your own to maintain consistency and if you need to restrict access. To create an environment, log into https://admin.powerapps.com and go to Environments. 

Then click New Environment in the top right of the UI.

Give your environment a name and select the Type, then click Create Environment.

Connections

The next point of call is to create connections to your D365 instances. In order to do this, you need to go to https://web.powerapps.com and go to Connections (tip: make sure you are in your newly created environment as per below). 

Navigate to Connections under the Data drop down.

Click New Connection and add connections to replicate the screenshot below. You will need to verify your credentials for each credential (tip: if you are trying to connect to D365 for Talent, that uses the Customer Engagement connection). Often, if your integration projects are failing and the error message isn't clear/data isn't making it into staging in FinOps; this should be your first check to ensure all connections have a healthy 'Connected' status.

Connection Sets

Now I've created my Environment and connected to the relevant data sources, I need to create a Connection Set to link the two together. Go back to https://admin.powerapps.com and open the Data Integration tab.

Click Connection Sets and then New Connection Set.

Give your Connection Set a relevant name and try to be consistent in your naming conventions, this will make your life easier in the long run. Select your D365 Sales instance for the first Connection and then the D365 FinOps environment for your second. If these don't appear, it's likely due to security permissions. 

Once you've done that, you need to add your mappings. Essentially, these are your default Business Unit in Customer Engagement and the main Company in FinOps. If you are unsure what these are, a good tactic is to enter jibberish in the field and click Create - the error message thrown back will give you the values you can use. Tip: you can always add more mappings at a later stage.

Integration Projects

In short, that's the prerequisites complete. You can now go and create integration projects from templates, create tasks against these and use advanced filtering. 

By clicking New project, you can give your project a name (remember to be consistent), select a Template, select the Connection Set and select your Organization (environment). It's worth noting that you can't change these details in the future, so if something changes you would need to create a new project i.e. if your Business Unit name is changed in Customer Engagement, your project would fail. Then you would need to create a new Connection Set, save your project as a template and create a new one from the template.

Once created, your Project will have any tasks imported from the template. From the Project page, you can do the following:

• Run and schedule the Project
• View Execution history (although you will get better error logs in the Data Management section of FinOps)
• Add Mappings
• Enable Advanced Filtering (more on this later..)
• View the Connection the Project is set for

Out of the box templates are a great starting point, but often you will need to make changes and add filters relevant to your requirements, but more on the mapping specifics next time - starting with Accounts and Vendors! If you want to take a look at the Microsoft road map for this space, you can find details here - https://docs.microsoft.com/en-us/business-applications-release-notes/april18/dynamics365-finance-operations/integration-dynamics365-project-service-automation.

Thanks for reading and a big shout out to Nathan Rodgers for offering his help and enlightening me to the world of the Data Integrator.