Using PowerApps for data integration between Dynamics 365 FinOps and SQL Server On-Premises

Global context

Today’s modern business run and interact with data daily to analyze and take strategic decisions during all the phases of project and business development via gaining insights from multiple sources of data (ERP & CRM, Files, 3rd party apps), thing that can’t be achieved easily if we are not deploying and integrating an efficient and agile logic / data architecture design, a scalable architecture that can allow data integration, eases interaction and empower all users, teams and stakholders from the CEO to the front-line workers to respond on their daily needs with minimum effort.

The importance of a global Data integration strategy

I think in the Cloud and FinOps age, the businesses not only have to anticipate the future technological demands to develop their activities, but more than this they are invited to develop a cultural shift in a challenging environment in which strcutured data plays a major and accelerator role.

An accelerator role to develop smart, efficient, sustainable system to make continuous improvements in term of incomes, and positive rates in the return on investment.

In pallalel of working in a cloud-FinOps oriented cultural shift, it’s essential to develop an organizational data governance approach based on good understanding of how desingning a good data architecture can help businesses be more connected and flexible with external data, invoice management and other ERP systems.

Why Power Apps ?

One single access

Microsoft presents a global integrated solution/tool to manage customer implementations process by using Power platform, which Power Apps one of it’s four key products with Power BI, Power Automate and Power Virtual Agents.

All in one, everything integrated

Includes a set of built-in templates, data connectors and services to allow maximum of integrations with Dynamics 365 apps and associated services.

Rich data implementation scenarios

Having a one signle platform with deferent data connectors, unlock the potential to develop wide range of creative solutions and multiple possibilities to connect different technological stacks and build customized scenarios.

Power Apps as data integration solution  

Working in one single platform with access to multiple products, services and data connectors, it’s just amazing !

Power Apps provides complete and rapid low/no code development environment for building custom apps and take action on data with more than 275 built-in data connectors and different types of Power Apps applications to develop even without having strong technical background!

Today’s scenario : PowerApps as data integration solution/tool between D365FO and SQL Server

The goal of this scenario is to use PowerApps for data in data integration between Dynamics 365 For Finance and Operations and SQL Server On-Premises.

Global architecture
  • Extract data from SQL Server instance via an On-Premises data gateway to pull external items :
    • The on-premises data gateway acts as a bridge to provide managed data transfer connection between Power Apps and SQL Server datasource.
  • Push extracted items data to Dynamics 365 Finance and Operations via Power Apps as new released products
    • Power Apps has an array of standard Dynamics 365 connectors, Dynamics 365 For Finance and Operations connector provides access to data entities, in this example we will use « ReleasedProductCreationsV2 » (as data entity in FinOps term, but as Table in Power Apps concept)
    • 2 different data connectors will be used (FinOps + SQL Server)
    • In Order to create new record action, « ReleasedProductCreationsV2 » data entity FinOps connector will be used to start pulling data.
Dynamics 365 For Finance and Operations data connector
  • Push new items to SQL Server database thourgh PowerApps interface
This screen used to make CRUD data operations, & associated to SQL Serever data connector

Basic configurations

Power Apps can be an incredible tool to build rapide and adapted Low-code to No-code applications with a maximum of flexibility to manage application design, controls and create personalized user experience.

Before creating new Power Apps application, let start by some basic configurations :

Environment overview

The question to ask here why we really need environments in Power Apps ? why it’s matter to create and configure a new one ?.

As we work in an organizational levels, the concept of environments in power Apps fit into this context, it’s like a container where we can store, manage and share specific applications at an organizational level, when each environment can have business requirements and expectations different to other business units, or users with specfic security permissions to access to those applications and associated datasource and connectors.

Having logical separation it’s important in this case, to not just only work with the ‘Default‘ environment which is offered by default.

All environments are accesible in Power Platform Admin Center

Power Platform Admin Center

On-Premises data gateway

In order to connect to SQL Server on-premises instance, installing local data gateway is essential to access and ensure data transfer connection with the database.

Depending on the complexity of the scenario, it’s recommended to take into consideration the gateway type to install.

Types to consider :

  1. Personal : to work on local data sources, can’t be shared with others.
  2. Shared : One single gateway. Multiple users can connect to multiple on-premises data sources.

One of the prerequistes is to check if Power Apps environment and the installed On-premises Data gateway located in the same « Region », they have to be configured in the same region

On-premises Data gateway as a bridge between On-premises and Azure cloud environments

New Canvas application from data

Power apps help build and concept new canvas app and connect it to hundreds of built-in datasource connectors.

The goal here is to create a new application and start building new screens, adding form controls and configure new SQL Server data connector.

New Power Apps Canevas app

Configure new SQL Server login

As a security principal, it’s important to configure new login while connecting with other entities and systems to offer a secured access permission to specific databases, tables and data ressources with a mapped database user.

SQL Server login configuration

Creating new login will be used to configure the Powe Apps SQL Server data connector

New SQL server new login

New Power Apps SQL Server Data connector

Once installed and connected with the data gateway, back to Power Apps studio to configure new SQL Server connection, you will be asked to enter the following information

Power Apps SQL Server data connector configuration

External products table in SQL Server

As a mapped user database i have to acces to ExternalProductsTable in SQL Server database.

Let take a look into the table fields.

Data structure table

In Power Apps each table field value will be associated to the ReleasedProductCreationsV2 entity fields.

Power Apps Dynamics 365 For Finance and Operations data connector

FinOps connector

As mentioned previously, the goal is to make new data action and create new record into « ReleasedProduct » data entity in FinOps.

In this case a new data connector will be added in Power Apps which will be called once we select an item from the items table.

Power Apps FinOps data connector with all available data entities

Let’s build it

Once all configurations done, it’s time to start building main screens and controls in Power Apps Studio, let’s see the application hierarchy

Power Apps screens and controls project hierarchy

Main screens to developp

The Home screen

This screen represents a listing of the items stored in the external products table in SQL Server.

Power Apps studio
  • Associated controls & properties :
    • Data table : shows the products dataset
    • Data source : Access to all tables resides on the database
SQL server data connector fields
  • Add icon OnSelect property : Define the default action to take when the user clicks an item, the needed action here is navigate to « Add form » 

The Add screen

Add screen to push new released products

Through this screen a new record can be created, the data source associated to this form is « ReleasedProductCreationsV2 ».

Associated controls & properties :

  • Data form Default property : This value will be automatically changed after each item selection , the goal here is to map every value from ExternalProducts table with « ReleasedProductCreationsV2 » form data field value.
  • Add button : submit the form values into FinOps Released products data entity, the inserted values will trigger « add action » and push new released product in Dynamics 365 For Finance and Operations
Form field Default property

Associated data source :

Once the user select an item from main data table, the Add form fields will take automatically the values associated to the selected line.

FinOps data connector

Here is the list of added fields from FinOps data entity

FinOps  »RelesedProductCreationsV2′ Power Apps table fields

The Success screen

Power Apps Success operation screen

After each succefull data operation we need to notify the user that the current operation has been succefully completed.

Publish a specific version

Power Apps application versions

After validating and testing all controls and how the different functionalities works, it’s time to publish and make a live version and share it with specified users to give them access.

Accessible via : File > Save > Publish

Publish new Power Apps version

Dynamics 365 For Finance and Operations Power Apps integration

The application now is ready to be embedded in Dynamics 365 For Finance and Operations.

The great thing now is to integrate the application in Finance and Operations, the standard offer the possibility to do that via different manners.

What about emebd it in Released products form :

Embedded Power Apps application in Released products form

Or specialized workspace :

Embeded Power Apps application in Released product maintenant standard workspace

Embed as you want in Dynamics 365 Finance and Operations 

Integration new Power App application accessible via Options menu point in the ribbon and select Personalize this form. Clicking on the … button allows you to add a PowerApp

Personalize in FinOpns and embed Power Apps

Un commentaire sur “Using PowerApps for data integration between Dynamics 365 FinOps and SQL Server On-Premises

Votre commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:


Vous commentez à l’aide de votre compte Déconnexion /  Changer )

Photo Facebook

Vous commentez à l’aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s