Now Reading: Using PowerApps for data integration between Dynamics 365 FinOps and SQL Server On-Premises
-
01
Using PowerApps for data integration between Dynamics 365 FinOps and SQL Server On-Premises
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.
- 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.
- Push new items to SQL Server database thourgh PowerApps interface
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
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 :
- Personal : to work on local data sources, can’t be shared with others.
- 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
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.
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.
Creating new login will be used to configure the Powe Apps SQL Server data connector
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
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.
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
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.
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
Main screens to developp
The Home screen
This screen represents a listing of the items stored in the external products table in SQL Server.
- Associated controls & properties :
- Data table : shows the products dataset
- Data source : Access to all tables resides on the database
- 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
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
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.
Here is the list of added fields from FinOps data entity
The Success screen
After each succefull data operation we need to notify the user that the current operation has been succefully completed.
Publish a specific version
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
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 :
Or specialized 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
