Problem: The business dealership information is being captured on multiple external systems. There is a need for business to capture 360°
data view dashboard to keep track of information of dealers, sales data, and many other dealership information at one place. And this dashboard is for business users on the organization for monitoring.
Let us take an example of dealership architecture, where dealer identities/domain information are stored on external Azure AD, and basic details of dealership is stored on Azure Cosmos database.
Note: The dealership use case is an example for us to explore the possibilities. Similarly, this can be replaced with any other data model.
Design & Solution Considerations
The following elements/components are considered for building this solution.
- Azure Cosmos Database, which holds dealership’s basic information
- Azure Active Directory, which holds the domain/identity information of dealership users [This is external/separate domain, holding only dealership users]
- Power Automate, to integrate and push the data to CRM system.
- Microsoft Common Data Service, which acts as intermediate storage and containing subsets of information from two other systems. [Azure Cosmos DB and Azure AD]
- Power Apps – model driven app, which has necessary dashboard for business users.
The following shows high level design of 360 degree architecture, integrating data from multiple systems.
 |
| High level design for Dashboard providing 360 degree of dealership data |
The following illustrates the design.
- The necessary data model or entities are created on Microsoft CDS to capture the data from multiple system. For now in this usecase, let us focus on one entity, I.e., dealers.
- The flow configured on Power Automate runs as a scheduled job, to synchronize the data into CDS. Pulls minimal information from Azure AD and Azure Cosmos database, and synchronize the data subset into Microsoft CDS.
- Power Apps, which has views, forms and dashboards pulls and shows the information from entities, which is configured in the underlying CDS.
Now let us get deeper into the solution, to see how these are configured.
Configure Data Model / Entities on Microsoft CDS
From the power apps portal,
- expand data section, and click on entities.
- Click on new entity to add new dealer entity, for data integrations.
- Provide a name/display name. In this case, Dealers.
- Provide primary field as dealerid.
- Click on create.
 |
| Creating new entity for dealer data |
The above step might take a while to complete. Once created, the default OOB fields will be provisioned and listed.
Add the following fields. In short, these fields are used to hold the dealer’s basic information. These fields are required to capture the information from external systems. Later in the article, you will understand what data is captured from where.
 |
| Add necessary fields for dealer entity |
The Microsoft CDS data schema is ready with the entity for our use case.
Looking at Azure Cosmos DB
The following snapshot shows the dealers data model from the external system (Azure Cosmos DB). This is a sample data base created for this demo. As we see, it has basic details like, dealer name, branch name, region name, email, dealership start date, and dealership status. Likewise, in the real scenario, there can be hundreds of additional field. But data from minimal fields will be moved onto CDS for 360 degree view.
 |
| Sample Database for integration |
Please note the account ID and keys from the Cosmos database. This will be used on the power automate for integrations.
Looking at Dealership Domain (Azure AD)
For this use case, we are using a separate domain to hold the dealership details. This can be any other system. But for simplicity, we are having this as Azure AD, which acts a domain for dealership users. This domain/Azure AD will be used by dealership, for all dealership applications [we are not focusing on this, as we are only going to focus on the data captured by these applications].
Feeding Data into CDS, using Power Automate
A simple flow is configured which captures the information from above external systems. These captured data is then integrated into CDS, using the same flow.
 |
| Power Automate - Flow to integrate data from two different systems into CDS |
The following steps illustrates the flow.
- Capture all dealership data from Cosmos Database. This is a master dealership list. Parse and keep the fields (captured from Cosmos DB) ready for use.
- Go through each record from Cosmos database.
- Capture dealership basic details like phone, address, etc from Azure AD using Office profile connector.
- Check if the record already exists on CDS. This can be done with the help of Filter Query on List records action. For my case, email ID is used to filter the data.
 |
| Query to check If data already exists on CDS |
- If already exists on CDS, update the record on CDS.
- If not exists on CDS, create a new record on CDS.
 |
| Create / Update Records on CDS |
Once this flow runs, the data is synchronized to CDS dealers entity.
 |
| Dealership Data Captured on CDS, once flow ran successfully |
Configuring Power Apps Model Driven App
Then comes a dashboard for users. This needs to be created for business users to monitor and visualize the data captured on CDS.
- From Power Apps portal, click on new app -> Model driven type. Provide a unique name.
- Configure the site map for navigations, by clicking on edit. Add a new area, new group and then new sub area. In our case, one area for dealership is added, two groups are created (dealers and sales).
- Let us only focus on dealers data. Click dealers group, and add new sub area.
- Select dealers entity type for dealers sub area.
- Save, publish and close.
The following snapshot shows the above configuration.
 |
| Sitemap configurations for navigation on PowerApps dashboard |
Now let us customize the forms and view, for our need.
- Views – Landing screen for business users. This helps to show the list of dealership users, with minimal fields.
- Forms – Shows detailed view of a user. All captured fields are shown here.
From the dashboard app designer. Under dealers entity, click on views. On the right side, the views will be listed, Click on new, or edit the existing view.
 |
| Power Apps Dashboard and components - View selected to configure view of landing page showing dealers list |
Add the necessary filters, and configure the design to add/remove more fields from the fields listed on the Column attributes available on the screen. Once configured, save, publish and close.
 |
| Dashboard view - Configured to show dealers list along with email and name |
Configuring Entity View: From the entity view, under dealers entity, click on forms. On the right side, the forms will be listed. Click on edit option over the Information form.
 |
| Power Apps Dashboard and components - Forms selected to configure view of particular dealer/user |
Modify the form by adding fields or by dragging and dropping on to the design. Then save and publish.
 |
| Configuring Form Screen to show necessary fields of a particular dealer |
Now from app designer, save, validate, publish and play the app. Once published, the app can be played by clicking on the app from power apps list.
 |
| Power Apps Model Driven Dashboard showing Dealers List for business users to monitor/track |
The following is the view configured to show the dealer email and name.
 |
| Power Apps Model Driven Dashboard showing a dealer information using a form configured |
Conclusion
Hope this is clear to see how we can capture and show a dashboard to track/monitor data (dealership information) from multiple systems. In our use case, we have used Azure AD, Office 365 connectors, and Azure Cosmos DB to integrate the data using Power Automate. And dashboard was then created on Power Apps model driven app, using the underlying CDS model.
This provides 360 degree view of data for business, regardless of source systems. As a simple use case, we have focused only on one entity, and one view on the dashboard. Likewise, many other system data can be integrated, with as many entities and with creating necessary relationships.
