PowerApps and the Microsoft Common Data Model
PowerApps provides a number of out-of-box connectors to store data on SQL, Dropbox, SharePoint and many other platforms. While setting up these connections are simpler, using a data store not only involves thinking about where the data will be stored, but also how the data will be structured and shared with one or more applications. Data also needs to be synchronized between different external sources of data and applications.
Today, we are excited to announce the Microsoft Common Data Model (CDM) as an out-of-box business database for storing and managing business entities. CDM will be shipping soon in preview as a first class business database for PowerApps with features rolled out incrementally.
What is Microsoft Common Data Model (CDM)?
Microsoft Common Data Model (CDM) is a secure business database, comprising of well-formed standard business entities that can be deployed for use in your organization. CDM is secure – it is encrypted at rest. CDM is built for scale – using the best of Azure technologies such as Service Fabric and elastic SQL. CDM provides not only standard entities, but also allows you to build custom entities either extending standard entities or adding new entities that can relate to standard entities. CDM provides structured metadata, rich data types, auto numbering, lookups, business data types like Address, Currency, capabilities like referential integrity through metadata configuration, and cascade deletes – making a compelling functionality. CDM will roll out features incrementally to expand entity capabilities, processes for entities and pre-canned forms for these entities among others.
How does CDM help you?
Data is critical for building business applications. But many companies quickly run into silos of information, missing out on a unified view across apps. With CDM, enterprises can improve operational efficiency with a unified view of business data. Using CDM organizations can analyze unified view of data to figure out the right actions and empower their employees to maximize their results which will lend itself to a sustainable competitive advantage for the organization. CDM will enable organizations to connect business data from applications with information residing in Office 365 productivity platform - for example, a calendar appointment or an Outlook task with data in Opportunity entity.
Organizations will also benefit from the wealth of rich ISV applications which will be built atop the CDM model for various industry verticals and functions. CDM is also customizable and runs in the cloud bringing in the advantage of applications accessing data to run on both desktops and mobiles.
Power users armed with the deep knowledge of unique business challenges of their organization can quickly build and deliver solutions without writing code.
Entities in Common Data Model
CDM provides standard entities common across most industry domains – Sales, Purchase, Customer Service, Productivity among others. Leveraging the years of experience with Dynamics business applications and Office 365 and working with thousands of enterprise customers we have defined and implemented these standard entities that will connect to Microsoft’s first party business applications and support a broad ecosystem of ISV and customer solution development.
Entities are built using the following features:
- Structured metadata: Entities are structured with data definition, behavior modeling and defaulting.
- Rich data types: Entities support rich data types including business abstractions like Address, Email, Currency, Auto-numbering (for invoices, orders etc.) etc. Modern types such as images, geographic location, Phone, Website URL will enable building modern, mobile applications on enterprise data. These data types are implemented using the latest SQL Server data types.
- Data constructs: Support for modeling relationships, lookups, aggregates, containment etc.
- System attributes: Each entity has system attributes such as RecordVersion, RecordId, DataPartition, CreatedByUser, CreatedByDateTime, ModifiedByUser, ModifiedByDateTime. There are used by the CDM platform to provide functionality like data manipulation, concurrency management, auditing, allowing data to be shared across the enterprise or isolated to a division.
- Declarative referential integrity is implemented in data model which ensures no orphan data is left in the system
- Entity and field level security can be configured per entity
- Optimized for performance: The data model is designed adhering to standard database normalization patterns. In cases where we will get a significant performance benefit based on learnings from Dynamics business applications we de-normalize the data model and avoid complicated relationships. The structure of the entities has been kept simple enough to maximize performance.
- We prevent the need for schema modification for instance, by allowing for addition of communication channel preferences for Contacts in a separate entity avoiding the need to modify schema.
- Ensure simple mapping to data model of Microsoft business applications
- Default value support for fields
- Data validation for mandatory and unique field data and checking for invalid foreign key references
- Data encryption at rest
Entities can be created and managed using PowerApps.com.
Entities will be available based on the categories you select.
Import and Export data
PowerApps makes it easy to import existing business data from system of records into CDM entities, with auto-detection of relationships between entities, data validation, and defaulting. Data import can be used for both initial bulk data migrations into CDM from external source or for ad hoc import of data where CDM platform can intelligently insert or update data. We have enabled high performance, high scale, asynchronous, bulk import of data into multiple entities in one shot while ensuring all modeled business rules are enforced. Millions of rows of data can be imported in a few minutes. Additionally, we can automatically map source data to standard and custom entities and their respective fields. In cases where we can't auto map user can provide the mapping. Source data itself can be in standardized formats like Excel files with multiple sheets and .CSV. Data import itself supports all the rich data types that CDM supports such as Address, Images etc.
Export data, on the other hand, enables user to output data in an asynchronous fashion for integration or quick analysis. You can also bulk edit the exported data in excel and then import the data back.
Excel add-in to manipulate data
CDM will offer an Office add-in for Excel users to bulk edit data and analyze using Excel. Users can refer to any entity and interact with its data in Excel. Data exported to Excel can be used to build pivot tables and charts. You can refresh the Excel data anytime and the formula and charts derived from the exported data are also refreshed.
You can update data or add new rows for the entity in the Excel worksheet. Any errors encountered on publishing data are indicated by highlighting the erroneous rows.
Build PowerApps with CDM
Common Data Model provides an easy and customizable business database, which can be used to power business apps. Using PowerApps Studio, point to the Common Data Model by adding as a new data source in an existing app or creating your app from data from CDM. For PowerApps created from app from data flow, the data cards will contain UI controls mapping to the rich CDM data types. You can publish and share apps with entities in the same way you would share other apps. Server side paging and delegation of functions on CDM platform along with power of Azure will enable your app to scale to millions of records.
When can I try this out?
Very soon! We plan to open access to CDM as a preview in August. You will have the ability to define entities, use Excel add-in to modify data and also create business apps using entities from PowerApps Studio.
Happy app building.
- PowerApps Team