23 Feb 2022 Leverage Your Power Platform Solutions Using Microsoft Dataverse

1.Introducing Dataverse

Dataverse allows users to store data dynamically in a scalable and safe environment. It’s a great tool to connect different apps and services for business requirements.

1.2.Dataverse Structure

Figure 1: Dataverse Structure

Microsoft Dataverse is a new type of relational database that stores its data within a set of tables or entities. A table is a set of rows (registers) and columns (fields). As usual, every column is designed to store a specific type of data. This set of entities, known as the Common Data Model, outlines and hierarchises all the information generated by an organisation.

We can create standard tables (usual solutions) or custom tables to achieve specific objectives by using Power Query. These tables are stored with a safe and scalable cloud architecture in order to allow users to create and define solutions for their company.

Dataverse uses SaaS, making it very easy to handle data and guaranteeing its compatibility, accessibility, and availability.

Dataverse stands out because of its simplicity of usage. In fact, it requires little or no coding, so anyone can work with it, from knowledge workers to professional developers. In addition, knowing that it is based in Azure, organisations that choose Dataverse can be confident that it will be globally available, compatible, scalable, and secure.

1.1.1. Microsoft Dataverse vs. Azure SQL Database

Both Microsoft Dataverse and Azure serve as data storage in the cloud. However, there are a few key differences:

Despite these differences, Dataverse and Azure can be integrated together, so a company can import data from Azure into Dataverse (also when it comes as SaaS, PaaS and IaaS), use Dataverse in Azure apps (with functions or plug-ins), and export data from Dataverse to Azure services (data flows, Azure Data Factory, Power Query, Data Export Service and Power Automate).

1.1.2. Microsoft Dataverse vs. Common Data Model

Put simply, Microsoft Dataverse differs from Common Data Model in terms of functionality: on the one hand, Dataverse stores and structures business process data and interconnected apps, whilst on the other, CDM works as a data scheme; the tables are like spreadsheets and are used to structure the information.

In short, Dataverse stores data and CDM is where this data is stored.

1.2. Behind Dataverse

After this brief introduction, you may be wondering what’s behind Dataverse. Dataverse stores table data in Azure SQL, Azure SQL Storage, Cosmos DB, Azure Data Lake, and Cognitive Search.

For instance, Cosmos DB is used to store audit logs. In addition, all these data storage types are encrypted at rest using a Microsoft-managed encryption key. Only table data stored in Azure SQL can be encrypted using a self-managed key for now.

1.3. Data Integration

It’s so easy to connect Dataverse to any supported platform. The most common connectors for Dataverse are Power Platform applications (Power BI, Power Apps, Power Automate and Power Virtual Agents), Office 365, Azure, and Dynamics 365.

There are 3 different types of data integration in Dataverse:

• Planned Integration: Data held in another application can be periodically synchronised with Dataverse to take advantage of data from other applications in Power Apps.
• Transform and Import Data with Power Query: Transforming data by importing it into Dataverse can be done through Power Query, a tool commonly used in Excel and Power BI — from many online data sources.
• Single Data Import: Simple import and export of Excel and CSV files can be used for single or infrequent data imports into Dataverse.

2.Use Case: How to Connect Dataverse with Power Platform Services

In order to experience all the connections and capabilities of Dataverse, we designed a use case that integrates the different functionalities of Power Platform mentioned above.

In our Proof of Concept (POC), we wanted to track the prices of different kinds of fuel for boats in Spain. To do so, we will need to get the data periodically, create some reports with Power BI, set up some alerts for the managers, and develop an app for visualising and modifying daily discounts.

Figure 2: Workflow of the application

Aiming to maximise Dataverse capabilities, we used this Microsoft tool as the cornerstone of our POC. This can be seen in the workflow depicted in figure 2, consisting of the following steps:

1. ETL is done through Power Apps using the dataflow functionality, and data is stored in 3 different Dataverse tables.
2. A Power Automate flow is running every day to detect abnormal fuel prices and to alert the managers by email.
3. After receiving an alert, the managers can access a Power BI dashboard offering an accurate visualisation of the fuel stations’ data and have the chance to apply some discounts to their premium clients thanks to Power Apps.

Going deeper into more detail, each process performs the following steps:

The Power Apps dataflow functionality allows the user to connect to a data source, in our case an API with open data from the Spanish government, and manage it using Power Query, the same tool used in Power BI. Data can be distributed in different tables, and we can easily modify the type in order to complete the ETL.

The data is saved in Dataverse and will be updated every 24 hours. The dataflow can be configured to trigger at a scheduled time, so we will leverage that to get the daily fuel prices.

Every time the prices are updated, we want to alert the managers if the prices in a particular petrol station show a difference of +-10% compared to the rest of the petrol stations in the same province. As the alert is about a very specific group, Power BI alerts were not practical, so we found the solution by combining Dataverse with Power Automate. Using a cloud automated flow, an email is sent if all the conditions are met:

Figure 3: Trigger action for the flow

As we can see in figure 4, this email will contain information about the petrol station, the location, the kind of fuel and the differences in prices; it also allows the receiver to access a Power BI dashboard:

Figure 4: Email template

The Power BI dashboard shows various important metrics and a visual map with all the petrol stations in Spain. This map can be filtered by province, and gives information about the daily prices and historical price evolution:

Figure 5: Dashboard for managers

In addition, there is an embedded Power Apps application, where the managers can apply a discount for that day to their premium clients. They will be able to select one of the petrol stations and a discount will be applied for the selected fuel:

Figure 6: App developed with Power Apps

We can see the final result in the following gif:

Figure 7: Final result

3. Conclusion

With this POC we were able to exploit some strong points in Power Platform, like the facility of integrating different tools thanks to the connectors. Even for external tools outside Microsoft, Power Platform can adapt easily. The combination of automation, data visualisation and apps makes complex projects easier and faster to implement in one single platform.

The whole process, from ETL using Dataflow with Power Query to the visualisation with Power Apps and Power BI, can be carried out using all the elements of Microsoft Power Platform.

In this blog post we have seen some of the capabilities Dataverse can offer to Power Platform. We have built an alert system using Power Automate, a report using Power BI and a canvas app using Power Apps, taking the data from Dataverse as the unique source.

As Power Platform is a very new tool, there is the potential for some improvements, like the execution of queries in Dataverse datasets, the loop speed in Power Automate, and table linkage in Power Apps.

If you want to get deeper insights into your business using Dataverse with Power Platform, don’t hesitate to contact us and we will be happy to advise you!