Get rid of Data Silos using a Common Data Model

Frequency of data silos

Nowadays, many companies rely on several operational applications. For example, a CRM supports the sales department, while an ERP handles the financial department.

Most of the time, these applications each have their own database. In addition, business analysts often use heterogeneous files and additional databases for ad-hoc analysis.

Based on our experience, data duplication is extremely common. In some organizations, the same data appears dozens of times, each copy following its own lifecycle.

Figure 1: Data silos in a company

Departments will always exist in an organization. It is natural for each one to keep control of its data due to different goals, priorities, and responsibilities.

However, data silos harm productivity. They create confusion, since it is unclear where the most recent version of the data resides.

Silos also complicate integration. When combining systems that were not originally designed to work together, silos quickly become a bottleneck.

Figure 2: Several database applications can contain the same data

Integrating different systems and databases is a challenging task. Many companies invest substantial money and resources to build an integrated model.

Yet, every time a new application or database appears, the model risks breaking. Non-negligible parts of the project may require rework.

One way to avoid this difficulty is to design a shared data model that follows a simple principle: create a single version of the truth.

This shared approach is called the Common Data Model (CDM). It simplifies architecture, ensures efficiency, and provides clarity for accessing the latest version of the data.

Figure 3: Common Data Model

The Common Data Model is a shared data language that unifies data across applications and sources. In the Microsoft ecosystem, it serves as the standard for both business and analytical apps.

It includes standardized and extensible schemas published by Microsoft and its partners to ensure consistency across applications and processes.

Moreover, Microsoft, SAP, and Adobe Systems launched the Open Data Initiative. Its goal is to deliver a single data model for insights based on behavioral, transactional, financial, and operational data.

Applications such as Dynamics 365, Office 365, Power Platform, Adobe Experience Platform, SAP ERP, and SAP BW all work with the CDM.

CDM Schemas
The CDM includes more than 340 standardized and predefined schemas. These cover:

• Entities
• Attributes
• Semantic metadata
• Relationships

These schemas represent common concepts such as Accounts, Contacts, and Resources.

In addition, reference entities are organized into subject areas for easy discovery. Documentation and schemas are published on the public GitHub repository.

This model has evolved over decades, backed by thousands of hours of investment from Microsoft and its partners such as SAP and Adobe.

Figure 5: Common Data Model entities

Where to start?

There are several ways to begin with the Common Data Model. As consultants, we often observe two recurring patterns.

1. Dataverse Application DevelopmenT

The first pattern is reusing CDM entity definitions instead of creating a brand-new model. The CDM can be used across services including Microsoft Dataverse (formerly Common Data Service), Dynamics 365, Power Platform, and Azure. As a result, all services can access the same standardized data.

By using Dataverse, you can start application development directly with the CDM. Built-in business logic, security, and integration accelerate the process.

There is no need to reinvent a model. The CDM structures your data in a standard format, making it easier to use, share, and analyze.

For example, in Power Apps you can select the CDM entities you need. Once added, entities become available according to the selected categories. You can also connect to an existing CDM folder as a data source in Power Apps Studio.

Figure 6: PowerApps studio – Add entities to your app.

Figure 7: PowerApp studio – Mapping attributes

Figure 8: PowerApp studio – Connect to existing CDM.

2. Integration with Power BI and Azure Data Lake Gen2

The second pattern that we often see with our clients is when Power BI and Azure Data Stack interact with the CDM. As already mentioned, the CDM provides a shared data layer, and this concretely translates into a specific data file structure stored in Azure Data Lake. The CDM definitions are open and available to any service or application that wants to use them.

Figure 9: Common Data Model architecture with Azure services

A CDM folder has the following appearance:

You can check the definition of all these files at the following link: https://docs.microsoft.com/en-us/common-data-model/data-lake. Keep in mind that correctly modeling the CDM folder is one of the main pillars of the CDM architecture.

This data file structure can be viewed as a rich semantic data layer for applications such as PowerBI and Azure Data Stack. You can leverage the power of the data-preparation capabilities of these components using:

– Power BI dataflows to ingest key analytics directly from the CDM folder.

– Azure Data Factory, which supports native inline CDM datasets (as source and destination)

– Azure Databricks formats and prepares it for the later steps and then writes it back to the DataLake.

– Azure Machine Learning trains and publishes a machine learning model that can be accessed by Power BI or other applications.

Did you know?

You need to correctly design the CDM to benefit all the existing accelerators. When we write this blog, for instance, seven Microsoft Industry Solution Accelerators are used: Healthcare, Financial Services (including Banking and Insurance), Manufacturing, Media and Entertainment, Nonprofit, Automotive, and Education (including Higher Education and K-12). You can access the updated list at the following link: https://docs.microsoft.com/en-us/common-data-model/industry-accelerators.

Power BI dataflows are both a data producer and data consumer of the CDM. Power BI dataflows can write data in CDM folders in Data Lake Storage Gen2 and read data in the CDM folder format. Power BI dataflows also offer an experience to map your data to CDM standard entities through the mapping transformation in Power Query Online.

As already mentioned, correctly modeling the CDM folder is one of the main pillars of master data management. To achieve good results and generate a successful project, your company will have to:

• 1. Create a map of the concerned systems;
• 2. Democratize this notion of data excellence within your organization;
• 3. Define roadmap, roles, and responsibilities (governance);
• 4. Start the implementation of such a project while keeping a holistic view;
• 5. Have the necessary skills and/or to be accompanied by a specialized partner.

At MDW, as a triple Microsoft Gold Partner, we are a group of data engineers with extensive expertise incloud technologies; we help our clients define their cloud strategy, design their modern datawarehouse architecture, and implement all the necessary bricks. Would you like some advice to help you implement a common data strategy for your organization? Go ahead and make an appointment for a free consultation call when it best suits you.