The Dynamics 365 connector in Power Automate provides the basic operations to interact with your Dynamics 365 FO instance. You can create new records via flow (e.g. a new Customer), read, update and delete records (e.g. Sales Order Lines) and retrieve the list of available entities.
Dynamics 365 Finance and Supply Chain Connector in Power Automate
The connector uses OData to interact with the entities in Dynamics 365 FO. Therefore, it also supports OData syntax for filter expressions. If you want to filter on a boolean Extended Datatype like NoYes and try to use “Yes” or “1” or true it will not work. Dynamics 365 has its own Datatype that comes from the entity definition Microsoft.Dynamics.DataEntities.NoYes .
One Time Customer in Dynamics 365 Finance
For example, if you want to get all One-Time Customers from Dynamics 365 FO, use the Dynamics 365 connector and choose the action “List items in present Table”. Use the Customers entity. To filter on a NoYes field like One-Time Customer you have to use the following syntax:
Power BI goes hand in hand with Dynamics 365 Finance and Supply Chain Management. By default Power BI can be used within workspaces and Dynamics 365 comes with a data warehouse and a large set of reports and dashboards. But wouldn’t it be nice to show Power BI visuals in common forms and filter on the active record? This can be done without coding by using Power Apps:
D365 Parameter to Power BI filter
Dynamics 365 Finance is capable to load Power Apps and pass parameters to the App, while Power Apps can load PowerBI reports and pass a filter to Power BI.
Create a Power BI report
Create a report that can be drilled down to the granularity you want to display in Dynamics 365 Finance. For example if you want to show customer specific information, your report should support filtering on a customer account.
For example I’m using the SalesInvoiceV2Lines and ReleasedProductsV2 entities. The SalesInvoiceV2Lines comes with a table reference to the SalesInvoiceHeaders where the invoice account is stored. The ReleasedProductsV2 can be linked to the lines via the product number.
Dynamics 365 Finance SalesInvoiceV2Lines entity in Power Query editor
Next create the desired visuals. For example a column chart for the revenue by Year and a donut chart for the revenue by product group. Add a filter and inspect the different results you would expect for differnt customers.
Revenue by Year and Product Group
Save and Publish the report. Open the report in Power BI Online and pin the two visuals on a new dashboard. Make sure to give the visuals on the dashboard a useful title and subtitle.
Power BI tiles on a dashboard
Power Apps
Everything comes together in Power Apps. Here the parameter from Dynamics 365 Finance is stored in a variable and passed as filter to Power BI. Open Power Apps via https://make.powerapps.com and create a new canvas app. I’d suggest to use the smart phone layout. According to the documentation add the following code to the OnLoad in Power Apps. This will store the parameter value from Dynamics to a Power Apps variable called FinOpsInput. (Depending on your local settings you may need to replace , with ; in PowerApps)
Read parameter from Dynamics 365 Finance in Power Apps
Next, add the Power BI tiles. From the ribbon go to Insert > Diagram > Power BI. Insert a Power BI tile to the empty screen. Choose your workspace, next the dashboard and finally the tile.
Insert a Power BI tile in Power Apps
The Power BI tile is referenced via an URL. This can be edited by selecting the Power BI tile and switch to Advanced. The syntax is:
&filter=TableName/FieldName eq 'YourValue'
Add a filter on the customer account with the values from the FinOpsInput variable as value. Make sure that the filter matches the field in your Power BI report. For example this would look like the following URL in my example:
Save and publish your App. From the list of your Apps, open the details page of your app and copy the App ID.
Power App DetailsCopy the App-ID from the details page
Add the Power App in Dynamics 365 Finance and Supply Chain
Logon to Dynamics 365 Finance and navigate to the screen where you want to display the Power BI tiles. In my example I’d choose Module Accounts Receivable > All Customers. In the upper right at the ribbon click on the Power App button and select add an App.
Add a Power App to Dynamics 365 Finance and Supply Chain Management
In the Add an app dialog provide a useful name. Paste the App-ID in the second field. From the context dropdown select the field to pass as parameter to Power Apps. In my case this would be the AccountNum. Finish by clicking on Insert.
Dynamics 365 Finance requires a reload of the page (F5). Test your Power App by selecting a record and the from the Power Apps button open the Power App. It will load the Power App and present the filtered Power BI tiles.
Dynamics 365 Finance / SCM Tier1 sandbox environments are heavily used by partners for development and building Dynamics 365 Finance / SCM applications. Microsoft-hosted Tier 1 environments were a great deal because we got well sized VMs with 28 GB RAM and 4 Cores plus SQL Server, Visual Studio and Dynamics 365 Finance pre-installed for a very small fixed price per month available 24/7. Now Microsoft recently announced that they will no longer include Microsoft-Hosted Tier1 Sandbox environments with the Dynamics 365 Financen / SCM license and we will no longer be able to purchase additional Tier1 sandbox Addons. The preferred solution is to use Cloud-Hosted environments instead.
No more Microsoft-Hosted Tier1 environments
Microsoft-Hosted vs. Cloud-Hosted
From a technical standpoint there is no difference between a Microsoft-Hosted or a Cloud-Hosted environment. Both solutions deploy a Windows Server VM in Azure. In both cases the deployment is managed via Lifecycle Service (LCS).
LCS management of a Cloud-Hosted environmentArtefacts of a Cloud-Hosted Dynamics 365 FO Tier 1 environment in Azure
However, there are 3 major aspects to consider:
One big difference is the pricing model. Microsoft-Hosted environments (or Addons) come with a fixed (!) price per month while Cloud-Hosted environments deploy on an Azure subscription and therefore are billed like a classic IaaS (aka. virtual Machine in Azure). Make sure to calculate the costs (!) and turn of the environments if not needed.
Another difference is the ability to choose the sizing of the deployed environment. In contrast to Microsoft-Hosted Tier 1 environments, you are now free to choose a sizing that fits your needs e.g. more (or less) RAM, CPU, Premium SSD storage, etc.
Moreover, in contrast to Microsoft-Hosted environments, we now get an Admin account on our machines. It was understandable that Microsoft tried to lock down the cheap VMs to prevent the misusage with anything else then Dynamics 365. Since we own and pay the VM in a Cloud-Hosted environment its more than fair to have Admin access on the machine.
Video: How to deploy a Cloud-Hosted Dynamics 365 Tier1 developer VM
Make sure to visit my Youtube channel and watch how to deploy a Cloud-Hosted Dynamics 365 FO developer VM using an Azure Subscription.
Update: Management Certificates
The use of management certificates is not supported when using a CSP Azure Subscription. Use a user-connection instead.
Update: Provisioning Admin User
Please note that the user deploying the environment is provisioned as the administrator. Microsoft-Hosted environment had to be signed off using a user from the customer tenant. I’d recommend to stick to this process when deploying a Cloud-Hosted environment
A common requirement during an ERP upgrade project (e.g. from AX 2012 to D365 Finance) and transition phase is to include both systems in the BI or reporting environment. Because of its tight integration with Dynamics, in many cases PowerBI is the preferred reporting and BI platform. PowerBI is capable to combine different data sources like OData feeds from D365 and SQL connections via gateway. However, for the person developing reports, it will become complicated to integrate cloud and on-prem datasources. For example, to create a sales report, one would need to include the Customers, SalesInvoiceHeader and SalesInvoiceLine entities as well as the CustTable, DirPartyTable, CustInvoiceJour and CustInvoiceTrans tables.
Different data sources in one PowerBI report
One way to address this issue can be to separate ETL logic from report design. PowerBI supports this approach by using dataflows. By using dataflows you can place PowerQuery logic direct in the Microsoft cloud and offer reuseable data artefacts. People designing reports simply connect to the dataflow but are not concerned with the ETL logic required to combine data from the old AX installation and a new Dynamics 365 ERP cloud environment.
Use PowerBI dataflow to decouple ETL logic from report design
Example
From PowerBI workspace create a new entity using dataflow. Choose the OData feed for Dynamics 365 and provide the URL for the CustomersV3 entity.
OData feed for entities from Dynamcis 365 Finance
Clicking next will open the Power Query editor and load the customers from Dynamics 365 Finance. Remove all the fields you don’t need in your application. In this example I’m using the DataAreaId, Account, Name, Group, Address and Delivery mode + terms.
PowerBI dataflow based on Dynamics 365 Finance OData CustomerV3 entity
For an on-premises AX 2012 installation you need to install a data gateway, so PowerBI can access the local SQL database. If you already have a gateway, create a new dataflow in PowerBI and use the SQL connection. I’d recommend to create a view on the database instead of loading tables in PowerBi.
CREATE VIEW [dbo].[PBIX_Customer] AS select DataAreaId, DirPartyTable.NAME, ACCOUNTNUM, CUSTGROUP, TAXGROUP, LogisticsPostaladdress.ADDRESS, DlvTerm, DLVMODE from CUSTTABLE join DIRPARTYTABLE on CUSTTABLE.PARTY = DIRPARTYTABLE.RECID join DIRPARTYLOCATION on DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY join LOGISTICSPOSTALADDRESS on DIRPARTYLOCATION.LOCATION = LOGISTICSPOSTALADDRESS.LOCATION where LOGISTICSPOSTALADDRESS.VALIDFROM <= GETDATE() and LOGISTICSPOSTALADDRESS.VALIDTO >= GETDATE() GO
Choose SQL Server data source for PowerBI dataflow
Select the data gateway and provide a user to access the database
Connect a PowerBI dataflow to your on-premises AX 2012 database using a gateway
Select the view and load the AX 2012 data to PowerBI. Save the dataflow
Dynamics AX 2012 customer data via data gateway
After you have created both dataflows return to your workspace, go to your dataflows and refresh both to load the data.
Refresh dataflow from Dynamics 365 Finance and Dynamics AX 2012
Next, create a third dataflow to combine the data from the Dynamics 365 Finance and AX dataflow. This time choose to link entities from the other dataflows:
Link PowerBI entities via dataflow
Select both dataflows
Select PowerBI dataflows to merge
In the Power Query Online editor rename the fields in both dataflow entities so you can append both queries. Be aware that Power Query is case sensitive and dataAreaId is not the same as DATAAREAID. When you have done this, append both queries as new one.
Append queries in PowerBI
From the new query make sure to remove duplicate customers
Remove duplicates in Power Query Online
If your have a PowerBI Pro but not a Premium subscription, deactivate load of the underlying queries.
Deable load when using PowerBI Pro
Save and refresh the dataflow. From the settings schedule the refresh and endorse the dataflow as “Promoted” or “Certified”. This is not necessary but it adds a label to dataflow and your report designer users see that they can trust the datasource. In PowerBI Desktop open Get-Data and choose PowerBI dataflow as data source:
Get data from PowerBI dataflow
Select the merged Customer data source.
Promoted and certified PowerBI dataflows
You can use the dataflows in your PowerBI datamodel but dont have to worry about the logic behind
Linked dataflow sources in a PowerBI data model
Conclusion
Using dataflows has some advantages. It helps you to decouple ETL logic from design logic. Especially when working with older versions of Dynamics AX you have to have deeper knowledge about the data structure. Another advantage is the reuse of dataflows. Typically you are not creating 1 single report, but more reports that require the same dimensions e.g. customers. By using dataflows you don’t need to maintain the load and merge in multiple PowerBI files.
At work we recently discussed a customer requirement regarding sorting of a SalesTable data set in Dynamics Ax. The requirement was to sort by ShippingDateConfirmed. If the order has no confirmation date yet, use the ShippingDateRequested instead.
If exists sort by Shipping Date Confirmed otherwise by Shipping Date Requested
There are several ways to implement this requirement. Depending on the technology you can use SQL code, computed columns in Dynamics Ax 2012+ or a union query in AX 2009.
SQL: Select CASE
The easiest way to achiev the goal is using pure SQL code where you can define a new column within the select statement and use it for sorting. Here is an example:
SELECT
SalesId, SalesName,
ShippingDateRequested, ShippingDateConfirmed,
CASE
WHEN ShippingDateConfirmed = '1900-01-01 00:00:00.000'
THEN ShippingDateRequested
ELSE ShippingDateConfirmed
END
AS ErpSortField
FROM SalesTable
WHERE DataAreaId = 'CEU'
ORDER BY ErpSortField
The result in SQL Server Management Studio for a Dynamics Ax 2009 database looks like this:
SELECT CASE WHEN .. THEN .. ELSE .. END in SQL
You may use such a SQL query as data source for an SSRS report
SSRS Report based on AX 2009 Sales Order
Dynamics 365 F/SCM: Computed Column
Since AX 2012 we can use computed columns in views. One way to address this requirement is to create a column that contains the same CASE – WHEN SQL Statement. To do so create a new view based on the SalesTable. Add a new static method:
Add a computed column to the view and set the method as view method. Build and synchronize.
View with computed column in Dynamics 365 Finance
This will result in the following SQL definition in the AXDB:
Generated SQL view code in AxDB
Use the view as data source in form:
View in Dynamics 365 F/SCM form
Dynamics AX 2009: Union Query
Older versions of Dynamics AX link 2009 computed columns were not supported. One workaround is to use a UNION Query.
First create a new view called ERPSalesTableConfirmed. Set the SalesTable as data source. Add a range based on the ShippingDateConfirmed field and set the range value to != ” (i.e. not empty). Add a view field based on the ShippingDateConfirmed and call it ERPSortField. This view will return all SalesTable records with a confirmed shipping date and a new field with the value in it.
SalesTable with confirmed shipping date
Second, create a new view called ERPSalesTableRequested. Set the SalesTable as data source. Add a range based on the ShippingDateConfirmed and set the range value to = ” (i.e. empty). Add a view field based on the ShippingDateRequested and call it ERPSortField. This view will return all SalesTable records without a confirmed shipping data and use the ShippingDateRequested for the ERPSortField.
SalesTable with requested shipping date
Next, create a query called ERPSalesTableSort. Set the query type to UNION. Add both views as data source. The execution of this query will return all SalesTable records. If the sales order was confirmed, the ERPSortField will contain the ShippingDateConfirmed value, otherwise the ERPSortField will contain the ShippingDateRequested.
UNION query in Dynamics AX 2009
Finally, create a new view called ERPSalesTableSort based on the query with the same name. Use all fields you like to see and the ERPSortField.
Dynamics AX 2009 view based on UNION query
Open the view. The result is a SalesTable dataset that can be sorted on the confirmed shipping date, and if the confirmed date is not present sorted by the requested date.
Sort SalesTable in Dynamics AX 2009 by confirmed or requested shipping date
This is an update to my older post how to extend the SalesTable 2 Line framework. The big difference is that in Dynamics 365 Finance and SCM overlaying is not supported and extensions and delegates need to be used. This post uses the same use case. A sales-notes field from the SalesTable needs to be updated in the SalesLines if it is configured so.
Create a new string datatype and call it ERPSalesNote. Extend the SalesLine and add the ERPSalesNote datatype to the list of fields. Extend the SalesTable and add the ERPSalesNote to the fields. Also add the ERPSalesNote field to the field group HeaderToLineUpdate.
Extend the user interface
Extend the SalesTable form. Place the SalesTable.ERPSalesNote in the Delivery group of the HeaderView tab.
Add the SalesLine.ERPSalesNote field to the delivery group in the LineView tab.
Prepare the update-order-lines dialog
The dialog to choose if and which fields need to be updates at the lines is generated automatically based on the HeaderToLineUpdate field group. There is some code needed to show the fields name in the dialog. This is done by subscribing a custom method to the delegate SalesTable2LineField.lineUpdateDescriptionDelegate
Create a new class that returns a name for the ERPSalesNote field.
Open the Header to Line update dialog by clicking on Accounts receivable > Setup > Accounts receivable parameters > Tab update > update order lines
Extend the framework classes
Create an extension for the AxSalesTable class and create a parm method for the ERPSalesNote field
[ExtensionOf(classStr(AxSalesTable))]
final class AxSalesTable_Extension
{
public ERPSalesNote parmERPSalesNote(ERPSalesNote _salesNote = "")
{
if (!prmisDefault(_salesNote))
{
this.setField(fieldNum(SalesTable, ERPSalesNote), _salesNote);
}
return salesTable.ERPSalesNote;
}
}
Create an extension for the AxSalesLine class. Implement a parm and set method. Use the chain of command pattern to extend the setTableFields method.
[ExtensionOf(classStr(AxSalesLine))]
final class AxSalesLine_Extension
{
public ERPSalesNote parmERPSalesNote(ERPSalesNote _salesNote = "")
{
if (!prmisDefault(_salesNote))
{
this.setField(fieldNum(SalesLine, ERPSalesNote), _salesNote);
}
return salesLine.ERPSalesNote; } protected void setERPSalesNote() { if (this.isMethodExecuted(funcname(), fieldnum(SalesLine, ERPSalesNote))) { return; } this.setAxSalesTableFields(); if (this.isAxSalesTableFieldsSet() || this.axSalesTable().isFieldModified(fieldnum(SalesTable, ERPSalesNote))) { this.parmERPSalesNote(this.axSalesTable().parmERPSalesNote()); }} protected void setTableFields() { next setTableFields(); this.setERPSalesNote(); }
}
Test your implementation
Make sure that the update method in the parameter is set to prompt. Open an existing sales order. Change to Header view and switch to edit mode. Change the notes in the delivery tab and save.
A dialog pops up and asks to update the lines. Click yes.
Check the sales note field in the sales line. The note field at the sales line should be updated with your text from the header.
In Dynamics 365 Finance / SCM we can no longer access the SQL database of the production environment directly. However, we can access the SQL database of the Acceptance Test instance. All required information can be found in LCS. I’ve made a video where to find this information in LCS and how to connecto to the SQL database.
Since version 10 Dynamics 365 for Finance and Operations supports the entity store export to Azure data lake. The main benefits are reduced costs because Azure Cloud storage is cheap and easy access for Business Intelligence tools like PowerBI.
If you are running a local development VM, the data connection tab in system parameters ist deactived by default. However, this can be actived using the SysFlighting table.
The configuration is pretty well documented by Microsoft. I’ve performed all the necessary steps and recorded a video:
ErpCoder 