With the launch of Microsoft PowerApps and Microsoft Flow, I thought it would be useful to have a close look at the underlying database and integration capabilities of these new systems.
In this, the first of two posts on the topic, I summarize the native integration capabilities and limitations of PowerApps and Flow. The second posts will show you how to do a bulk import of data from Dynamics CRM or Salesforce into the Common Data Service.
Power BI, PowerApps, and Flow are part of a major push to bring application development and integration to the hands of the ‘power user’ – allowing them to get done what would have in the past required programming skills, with each playing a specific role:
- Microsoft PowerApps – rapidly build web and mobile business applications without coding
- Microsoft Flow – automate business processes through simple configuration
- Microsoft Power BI – analyze and visualize the data that drives your business
All of these can connect to various data sources such as Dynamics CRM, Salesforce, Excel, MailChimp…(full list for Flow here), but Microsoft is also providing the Common Data Service (CDS) as part of PowerApps to provide native database capabilities for the apps and BI.
The grand scheme that Microsoft is working towards looks like this:
The general idea is that enterprise users will be able to pull data from disparate sources into the database, and then rapidly develop apps and analytics to use this data. To quote Microsoft:
This data service adds powerful data storage and modeling capabilities to PowerApps. Our objective for the Common Data Service is to enable the following capabilities for the PowerApps, Microsoft Flow and Pro development communities:
- An easy to provision, yet scalable data store
- A common data model with standard entity schema and behavior
- A powerful data access layer with support for data import, export and security
- Integration with Microsoft Office for Excel and Outlook
The Common Data Model (CDM) database implemented by the CDS is a standardized set of entities and data types to ensure that apps are able to safely and reliably access data. The database is extensible – users will be able to add custom entities, but the standard fields in the standard entities cannot be modified. The objective is to ensure that PowerApps and PowerBI have a core set of entities and fields that can be relied on.
On a broader front there are indications that the CDM will form the basis of the Dynamics 365 platform going forward. Jukka Niiranen has an excellent blog post providing quite a lot of context on the Dynamics 365 architecture and relationship with Azure.
Structure of the Common Data Model
At time of writing (November 2016), the only way to see a CDM database is to log into Power Apps and click the ‘Entities’ option in the side menu.This displays a list of the entities (aka tables) in the CDM database attached to your subscription. A detailed Entity Reference document is available, although at time of writing it does not accurately match the released database.
For a CRM user, the set of entities looks a little unusual – heavy ERP flavor, with multiple entities concerned with Purchase Orders, Sales Invoices, Sales Orders, Supplier Invoices etc. More unusual was the set of entities concerned with people in the August preview which had Contact, but also: Alumnus, Contractor, Customer, Donor, Employee, Family Member, Fan, Household Member, Team Member, Tenant. As Jukka Niiranen noted in another post:
OK, great, so I can store the name, address and Twitter handle of a physical person into 11 different entities now. I don’t recall ever wishing for such a possibility to exist, but it’s what’s coming at us now when Dynamics 365 arrives.
Thankfully, with the November release, this has been significantly rationalized, and a ‘Type’ field added to contact. We can expect the set of standard entites to evolve rapidly. Note that Microsoft is monitoring the way users customize the standard database and doubtless will incorporate some of the more popular ideas.
Working with the Common Data Service
InaPlex is an integration specialist, so our particular interest is how to integrate with CDM. Currently there are two options for getting data into a CDM database:
- Use Flow, or
- Import Excel spreadsheets
Flow will be discussed in more detail in a later post, but for now note that the challenge with using Flow is that it is transactional – a Flow is defined as a connection between a data source and sink, and the the flow has to be triggered by some event. For example, you may set up a Flow that connects Dynamics CRM Companies and CDM Accounts. Each time a company is created in CRM the flow is triggered, and the company is created in CDM.
The problem with this approach is the initial data load. If you already have a few thousand companies in CRM, how do you get this data into the CDM database?
Importing Data into CDS Using Excel
The only answer at the moment appears to be to import Excel spreadsheets. There are some tight constraints on how this can be done:
- The worksheet must have the primary key of the table, and matching appears just on the primary key. If there is a match the record is updated, otherwise created.
- The field names should match the field names in CDM exactly. If the field names in the Excel file do not match the field names in CDM exactly, you’ll have to manually map them during the import. You will want to avoid doing this because there is not currently a way to save a mapping, which means you have to do the mapping again if you do another import.
- All required fields must be mapped, even if you do not have data.
- Picklist fields need to have the correct values.
The easiest way of setting up the Excel spreadsheet initially is to do an export to a template from the CDM database. This gives you a work sheet with all the correct field names. You could also do a data export – this also provides an Excel worksheet with the field names, but additionally shows you how the data needs to appear.
Once the Excel file is populated here are the steps to import:
1.Select Entities, then Click the Import Data selection in the menu at top right.
2.Select the entity or entities you want to import into and click next.
3.Select the Excel file to be imported. The engine will load the file and check that matching (the Id field) and mapping (the column headers in the work sheet) are OK. If the field names in your work sheet do not match the field names in CDM, click the ‘Show Mapping’ link and select the correct mapping. Notice that the upload has selected the worksheet from the Excel file – you can have more than one work sheet for different entities.
4.Click import, and the Excel will be uploaded and imported.If there are errors, an error report in the form of an Excel file will be generated and can be downloaded.
Limitations of Importing with Excel
Importing through Excel is relatively straightforward, if tedious. The bigger problem is getting the data into the Excel file in the correct format and ensuring you have data for all the required fields. For example:
- Required fields have to be mapped.
- Picklists have a restricted set of allowed values that probably do not match your source values.
- You may need to translate values to meet data type requirements.
Suppose you have a couple of thousand accounts and contacts in Dynamics CRM or Salesforce and you want the data in your local CDS database. You can drop the data into an Excel file, but then you have to manually reshape it, and if you do not rename all columns you will have to remap each time you do the import. And no, it will not work correctly first time…