As Microsoft pursues its One Dynamics, One Platform initiative, substantial investments are made to integrate the D365 Finance and Operations suite with the Power Platform and Dataverse.
These includes the ability to surface most F&O tables as virtual entities in Dataverse. Bringing full CRUD capabilities, but also opening up the whole Power Platform ecosystem to consume and act on F&O data. Think Power Automate, Model-driven and Canvas Apps, PCF Controls, Power Pages, etc.
While experimenting with Finance and Operations virtual entities, I found gaps in the tooling and processes required to enable and manage them. This led to the inception of my latest tool for the XrmToolBox, the FinOps Virtual Entity Manager.
Here are the main features that I’ll explain later on :
– 📋List/Filter available Finance and Operations entities – 👁️Enable/Disable visibility of entities in Dataverse (Virtual entity enabled) – 🔁Enable/Disable change tracking of entities – 🔄Refresh entity metadata – 🤝Integrated with the Metadata Browser Companion from Tanguy Touzart to explore virtual entities attributes
But first, let’s put things in context.
My ode to XrmToolBox
For most Power Platform developers, the XrmToolBox needs no introduction. But if you come from the F&O world you might not be familiar with this ecosystem and could be wondering what the fuss is all about.
To make a long story short, the XrmToolBox is a versatile and robust toolkit for Dynamics 365 and Power Platform professionals developed and maintained an by Tanguy Touzart.
With its user-friendly interface to organize environment connections and extensive range of powerful plugins developed by the community, the XrmToolBox helps users to streamline development, customization, administration and troubleshooting tasks within Power Platform environments.
Quite frankly, I think I can count on my fingers the days that I haven’t opened the toolbox on my day to day activities. Some of these tools have literally saved my sanity more than once.
Also as a tool author myself, the platforms provides a level of reach and exposure that would be impossible to achieve independently, thanks to its widespread use.
So, wait no more and install the toolbox and be sure to download the FinOps Virtual Entity Manager (…and my other tools at the same time… shameless plug 😏 )
My tools on the XrmToolBox
Background and Pre-requisites
Even if they are often marketed together (under the Dynamics 365 umbrella), Finance and Operations and Power Platform / Dataverse are two entirely distinct product offerings. Each possesses its own unique architecture and serves vastly different purposes.
For the digital archeologists, Finance and Operations is derived from Dynamics AX whereas PP/Dataverse get its roots from Dynamics CRM and has dramatically evolved to the current PaaS (Platform as a service) that we know and love today.
In an effort to integrate and bring these 2 products together, the product team have made it quite easy to integrate a Finance and Operations environment to a PP/Dataverse environment through the Lifecycle Services portal. This setup goes beyond the scope of this blog post but you can find relevant info here.
Once the integration completed you can appreciate a new Finance and Operations URL when navigating to the linked Dataverse environment from the Power Platform Admin Center (PPAC)
Power Platform Integration from LCSFinance and Operations Environment integrated to Dataverse in PPAC
This is where the fun begins 😎.
Connecting to the Tool
I won’t go in details on how to setup connections for the XrmToolBox, it’s a vast topic, but here is an helpful link
Once connected to an FinOps/Dataverse linked environment, if you fire up the FinOps Virtual Entity Manager, you’ll be presented with the respective URLs of the Dataverse and the FinOps environments.
By clicking on the Load/refresh entities the list of Available Finance and Operations entities will be loaded on screen.
If you are interested on what is happening under the hood, I simply fetch the data from the mserp_financeandoperationsentity table that is itself a Virtual entity that is created part of the FinOps/Dataverse integration.
As you can see here, I’m querying the entity using SQL 4 CDS by Mark Carrington, another essential tool of the xrmTooolBox.
Navigating through the entities
There are about 3000 exposed entity coming from FinOps, so its important for the tool to be able to filter the tables to give a better user experience.
The tool, currently supports filter on the physical name, visible (virtual enabled) and change tracking enabled fields.
Filter available FinOps entities by table name
Act on selected Finance and Operations entities
By selecting a table from the list of available entities, this will populate the side-pane with the table info and give users the possibility to take action on certain parameters of the table.
Enabling visibility of a F&O table will automatically expose that table as a Virtual entity in Dataverse. The new virtual entity will be prefixed with mserp_ in the environwement.
In the PowerApps maker portal you will see the table like this.
FinOps Virtual table in the maker portal
From now on, the bar is open for that table on Dataverse side. you can for example :
Query the data from Dataverse like any other native tables
Act on the data with Power Automate, Dataverse plugins, Custom APIs
Create rich user interfaces with full CRUD capabilities like Model Driven apps, Canvas apps and Power Pages.
Create relationships between native Dataverse tables and FinOps tables (that one is pretty impressive)
Relationship between a Dataverse and a Finance and Operations Table
Relationship between Dataverse and FinOps record showed in a model driven app
Note that the security/licensing context is not bypassed here, all consuming users need to be properly configured in Finance and Operations.
🔁Enable/Disable change tracking of entities
For certain scenarios like Synapse link / Fabric integration of the tables data, its necessary to enable the change tracking at the virtual table level.
Unfortunately the switch to enable change tracking is grayed out if you want to set it up at the table level in the maker portal.
Track changes not editable from maker portal
By using the tool you can easily enable the change tracking of the FinOps virtual entity.
The virtual entity will then show up with the Track Changes switch enabled in the maker portal.
Track changes enabled in the maker portal
As mentioned by Nurlin Aberra in a recent post, if you run into errors while activating the change tracking property, you might want to check these requirements on the Finance and Operation side
If fields are added on an F&O tables afterward, you will want to update the metadata of the virtual entity, this is where you will use the Refresh Metadata switch on the entity
I haven’t really tried it but, it speaks for itself.
Explore Metadata
This feature is among my top favorites in the tool, mostly because I only had to write one single line of code to integrate my tool with the awesome Metadata Browser Companiontool from Tanguy Touzart.
The end-result is quite slick IMHO as the user doesn’t even notices that there is a second tool involved. Kudos to Tanguy for making his tool extendable.
As you can see, clicking on the Explore with Metadata Browser opens the companion tool and we can drill-down on all the available metadata of the virtual entity including the newly created relationships demonstrated earlier
Metadata Browser companion tool
TakeAway
That’s it for now, I hope the tool will be helpful to some of you, as I really think it fills a gap. Please reach out if you have any comments or have any ideas for improvements, feedback is greatly appreciated (link to the github repo below).
Speaking of the FinOps/Dataverse convergence, I see a lot of potential especially on the edges of the Finance and Operations ERP. Some scenarios like data entry, acting on business events and reporting might benefit from the Power Platform in the long run.
Also please note that I consider myself a total newbie on Finance and Operations, as I’ve only recently been exposed to it. Therefore, the views expressed in this post might be tainted by the Power Platform lens which is what I know best. I would love hear from Finance and Operations experts on that topic and understand what challenges they foresee with such architectural approaches.
Until then…
Links
GitHub – drivardxrm/Driv.XTB.FinOpsVirtualEntityManager: XrmToolBox tool to manage Finance and Operations Dataverse Virtual Entities
XrmToolBox tool to manage Finance and Operations Dataverse Virtual Entities – drivardxrm/Driv.XTB.FinOpsVirtualEntityManager
This article explains how to enable the Microsoft Power Platform integration by using Microsoft Dynamics Lifecycle Services for finance and operations apps and Dataverse.
This article provides general information about virtual entities for finance and operations apps.
Home · XrmToolBox
XrmToolBox is a Windows application that connects to Microsoft Dataverse. Dynamics 365 Customer Engagement (CE) applications, like Dynamics 365 for Sales, Service or Talent also use Microsoft Dataverse as their foundational data service. XrmToolBox, provides tools to ease customization, configuration and operation tasks for anything built on Microsoft Dataverse, including Dynamics 365 CE (formerly CRM) and model-driven PowerApps. It is shipped with more than 30 tools to make administration, customization or configuration tasks easier and less time consuming. And more than 100 other tools are available in the Tool Library.
Connecting to an environment · XrmToolBox
XrmToolBox is able to connect to any kind of environment from Microsoft Dynamics CRM 2011 to Microsoft Dynamics 365 for Customer Engagement and Power Apps Common Data Service, using one of the available connection methods.
SQL 4 CDS · XrmToolBox
SQL 4 CDS allows you to use standard SQL syntax to query and manipulate your data and metadata in Dataverse / D365.
Welcome to Metadata Browser companion
Today, I’m releasing my first companion for XrmToolBox. What? You don’t know what a XrmToolBox companion is? It’s a compact tool designed to be used aside other tools in XrmToolBox.
One Dynamics One Platform – Dataverse C# Plugin for Dynamics 365 F&O — PowerAzure365
An article part of the One Dynamics One Platform convergence : on the Dev side : How to deploy a Dataverse C# Plugin for a Virtual Table of Dynamics 365 Finance Operations.
Is it feasible to employ T-SQL for querying Dynamics 365 FO virtual entities and executing advanced queries?
Could T-SQL queries be employed to access Dynamics 365 FO virtual entities? Although querying Dataverse tables is known, can Dynamics 365 FO virtual entities be queried would it be possible to perform joins across different Dynamics 365 FO virtual entities, along with aggregate functions ? Initially
I have the guid of the ce-entity stored inside my virtual entity and I need to create a n:1 Relation (Virtual (n) : Native (1)) but that doesnt work.
Any idea how to implement this?
I have the guid of the ce-entity stored inside my virtual entity and I need to create a n:1 Relation (Virtual (n) : Native (1)) but that doesnt work.
Any idea how to implement this?