Use Power BI dataflow to decouple report design from ETL logic in an ERP upgrade project

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.

Video: Configure PowerBI for Dynamics 365 Finance in a Cloud hosted Environment

I’ve recorded a walkthrough how to configure PowerBI for Dynamics 365 Finance and Supply Chain Management and deploy the standard dashboards.

Start and open a specific record in Dynamics Ax 2012

At work we recently discussed ways to startup Dynamics AX 2012 and navigate to a specific record. The requirement was to open Dynamics AX from a DMS client that manages invoices and other documents.

There are different approaches to achieve this goal. One way is to use the Startup Command framework which is used to instruct Dynamics to execute several functionalities during startup e.g. compile, synchronize or navigate to a menu item. In order to startup a menu item, you provide an XML file which contains the menu item name and point to this file from the .axc Dynamics AX configuration file.

Startup Dynamics AX 2012 with an XML configuration file

Reference the record in the startup XML file

For many forms in Dynamics AX it is sufficient to call the corresponding menu item with an Args object that holds the record. To specify a record in Dynamics AX you need to provide at least the TableId and the RecId. For example the Customer “Adventure Works” can be defined by using TableId 77 (CustTable) and the RecId 22565422070. Add two additional attributes RecId and TableId to the XML file which is used to open the CustTable form. The XML file looks like this:

<?xml version="1.0" encoding="utf-8"?>
<AxaptaAutoRun 
 exitWhenDone="false"
 version="4.0"
 logFile="$HOME\axEXProd.log">
<Run 
 type="displayMenuItem" 
 name="CustTable" 
 RecId="22565422070" 
 TableId="77"/>
</AxaptaAutoRun>

Modify the SysAutoRun.execRun() method

At the SysAutoRun class, open the execRun() method. At the top declare the following variables:

RecId recId;
TableId tableId;
Args arg = new Args();
Common common;
DictTable dictTable;

At the bottom, find the place where a menu item is started. Before the if(mf) statement add the following code to read the RecId and TableId from the XML file and select the corresponding record:

recId = str2int64(this.getAttributeValue(_command,'RecId'));
tableId = str2int(this.getAttributeValue(_command,'TableId'));
if(recId != 0)
{
   dictTable = new DictTable(tableId);
   common = dictTable.makeRecord();
   select common where common.RecId == recId;
   arg.record(common);
}

Within the if(mf) block, add the Args object when the menu fuction is called to pass the record.

mf = new MenuFunction(name, menuItemType);
if (mf)
{
   this.logInfo(strfmt("@SYS101206", mf.object(), enum2str(mf.objectType())));
   mf.run(Arg);
   result = true;
}

Test your configuration

Now you can test your configuration. Create a new .axc file and point it to the XML file. Make sure the XML file has a valid TableId and Recid property. Start Dynamics AX using the .axc file and the defined menu item should open and view the record.

Video Training: Advanced Development for D365 F/SCM

In cooperation with Learn4D365 I’ve recorded another course on Dynamics 365 Finance and Supply Chain Management Development. This video training presents techniques to extend Dynamics 365 F/SCM and discusses the application frameworks. Please find the online course here:

Development II for Finance and Supply Chain Management 

If Sort-Field is empty sort by another field

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
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
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:

private static server str compColShippingDate()
{
  #define.ViewName(MBSSalesTableView)
  #define.DataSourceName("SalesTable")
  #define.FieldConfirmed("ShippingDateConfirmed")
  #define.FieldRequested("ShippingDateRequested")
  str sReturn;
  str sRequested, sConfirmed;
  DictView dv = new DictView(tableNum(#ViewName));

  sRequested = dv.computedColumnString(
                   #DataSourceName,
                   #FieldRequested,
                   FieldNameGenerationMode::FieldList);
  sConfirmed = dv.computedColumnString(
                   #DataSourceName,
                   #FieldConfirmed,
                   FieldNameGenerationMode::FieldList);

  sReturn = "CASE WHEN " 
          + sConfirmed + " = '1900-01-01 00:00:00.000' THEN " 
          + sRequested + " ELSE " + sConfirmed + " END";

  return sReturn;
}

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
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
Generated SQL view code in AxDB

Use the view as data source in form:

View in Dynamics 365 F/SCM 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
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
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
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
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
Sort SalesTable in Dynamics AX 2009 by confirmed or requested shipping date

Power Automate: Deploy and Execute an Ethereum Smart Contract

Power Automate (aka. Microsoft Flow) is a great cloud-based tool to automate all possible tasks. There is a Ethereum connector (Beta) that can be used to deploy a Smart Contract to an Ethereum Blockchain network and execute functions.

Ethereum Network

You need to connect to an Ethereum network. There is a fully managed Blockchain Service in Azure. I’m running my private network with Proof-of-Authority. At the Azure management portal, go to the transaction node to get the required information to connect.

Blockchain Service in Azure
Blockchain-as-a-Service in Azure

Smart Contract

I’m using Visual Studio Code with the Ethereum Blockchain Development SDK to implement a Smart Contract in Solidity. You can find the link to the SDK at the Azure Blockchain Service portal.

Blockchain Development Kit for VS Code
VS Code with Azure Blockchain SDK

The SDK requires a lot of other software products to download and install. I found that the solidity compiler installed was newer than expected. As result the demo smart contract you get from the SDK did not compile. The simplest solution was to change the pragma of the contract

pragma solidity >= 0.5.16 <= 0.7.0;

ABI and Bytecode

In order to automate the deployment of a Smart Contract via Power Automate you need to provide the ABI and Bytecode. Both can be found in VS Code, at the build directory in the context menu.

Solidity ABI and Bytecode
Copy ABI and Bytecode directly from VS Code

Power Automate

You can directly provide the ABI and Bytecode in the Deploy Smart Contract action. However, I decided to place both in an Azure Table Storage and fetch it from there. To do so, I create a table with a column for the Bytecode, a column for the ABI and a name.

Store ABI and Bytecode in Azure Storage Account

The first step in my flow is to connect to the Azure Storage account and get the smart contract I need. The result from the storage is a JSON string which is parsed so the ABI and Bytecode is available for the next steps

Deploy Ethereum Smart Contract from Flow
Fetch the smart contract binaries from the storage account

Next the Deploy Smart Contract action is used to deploy the contract. There you need to provide the connection to your Ethereum network. In my example there are two parameters for the constructor and for testing purpose these values are hardcoded. In real life you would provide values from the calling sources. The third parameter for the connector action requires the Bytecode which is taken from the storage account. The result from the deployment is the smart contracts address which is stored in a flow variable.

Deploy a Smart Contract to a private Ethereum Blockchain Network
Deploy a smart contract

Interacting with the smart contract

After the Smart Contract has been deployed to the Ethereum Blockchain Network, use the Execute Smart Contract Function action in the flow. For each step you have to provide the address, the ABI, the name of function and the parameter as JSON string. A function without parameters has to be called with {} because the parameter property is mandatory.

Execute a Smart Contract Function via Flow
Execute a Smart Contract function via Power Automate / Flow

Here is an example for a function with some parameters. These parameters have to be provided as JSON string in Flow.

function SetupMachine(int sawLength, 
                      int waterTemp, 
                      int rpm,
                      int speed) public
    {
        if (State != StateType.Assigned)
        {
            revert('Assign to a machine first');
        }

        SawLengthMM = sawLength;
        WaterTempDgrC = waterTemp;
        ExtruderRPM = rpm;
        ExtruderSpeed = speed;

        State = StateType.Setup;
        Worker = msg.sender;
    }
Execute a Smart Contract Function via Flow
Execute a Smart Contract function with parameters

PowerBI dataflow for Self-Service BI

A typical challenge in a BI project is to integrate data from different sources. For example files stored locally, ERP databases, cloud services, etc. On the other hand, PowerBI (desktop) is designed for power users to develop reports quickly. However, power users may have business knowledge but in most cases lack the technical knowledge to integrate all the data they need. With PowerBI dataflow it is possible to break the workload into a technical IT-related part and a business analysis part.

PowerBI dataflow hides the complexity of integrating differnt data sources, but provides an ready-to-use data source for PowerBI desktop. I’ve recorded a video how to integrate Excel expenses from a local folder with Dynamics 365 Sales customers and promote result as certified data source. The power user accesses this promoted data source in the report.

https://youtu.be/1umwqpiozbM

Seventh and hopefully last week under COVID-19 lock-down in Austria (4th May)

General Situation

From 1st May the restrictions to access public space has been revoked. Stores, exception restaurants and hotels, are open again. Shopping malls opened on Saturday 2nd May. With all the shops back open, and people lining up before boutiques and storming the malls there is a fear of a second wave coming soon.

Number of infected and increase rate

Back at the office

On 4th May we are back at the office!

Back at the office
Back at office

Sixth week under reduced COVID-19 lock-down in Austria (26. April)

General Situation

Two weeks have passed sind the first restrictions were repealed. Home depots and shops smaller than 400m² were allowed to open up again. Thankfully this did not lead to an immediate second wave of infections. One reason may be that the total number of infected persons is very low. So there is only a very small propability to meet an infected person outside. Another reason may be the rule to wear a mask in shops and public transportation to protect others.

COVID-19 cases in Austria

Global Azure Bootcamp – “Corona Edition”

This weeks highlight was the Global Azure Bootcamp. At the dawn of COVID-19 the GAB was officially canceled with the option for local chapters to hold the GAB if possible. Soon GAB was shifted from local meeting to virtual presentations via Microsoft Teams. Again this year was full of good presentations. #GlobalAzure

Global Azure Bootcamp 2020
Global Azure Bootcamp 2020
Global Azure Bootcamp 2020 – Virtual Corona Edition from Home Office

Azure Cloud capabilities

When the COVID-19 pandemic was raising in europa an most countries announced lock-down rules, the cloud usage increased up to 700%. In this time, the azure regions West/North Europe and UK were at their limits. In particular we saw this when deploying a Dynamics 365 Finance / SCM Tier 2 which had to be moved to US-South. Now, after a few weeks it seams that cloud capabilities are normalizing. In contrast to the last weeks I could deploy a Windows VM and Ubuntu VM in West-Europe.

Windows VM deployment in Azure region West-Europe
Ubuntu VM deployment in azure region West-Europe
Windows Server 2016 and Ubuntu VM deployment in West-Europe succeeded

Contact Tracing Apps

An everlasting topic are the apps to fight the corona pandemy. After a month Accenture has released the source code for the Stopp-Corona app at Github. Since the first apps appeared, there was the discussion how these apps could be made interoperable. The euroepan PEPP-PT project first came up with an approach to make the national develop apps interoperable. The announcement of Apple and Google to implement a contact tracing API in their operating systems immediately killed PPEP-PT. The Austrian Stopp-Corona app is evaluation the use of Apple / Google API. The German government will use Apple / Google. ETH Zurich has also left PPEP-PT.

Weekly lock-down menu

Scrambled eggs, Bratwurst, Appfelschlangerl, Gemüsefleckerl, Kaiserschmarrn, Chicken filet with rice and vegetables, Vanilla pudding with chockolate, Spice meat in sauce, Schnitzel, Turkey rolls with ham and cheese.

Cooking in quarantine

Fifth week under COVID-19 reduced lock-down in Austria (20. April)

General Situation

Staring from 14. April shops with less than 400 m² were allowed to open up, not including restaurants or bars. Home supply and garden center were also alowed to open. Using masks is now mandatory not only for grocery stores, but for shopping and public transport.

COVID-19 infected and increase rate
App. 14.500 positive tested so far

The number of infections is stalling and the increase rate is stable under 5% for a long time now. The big question is how much will the infections increase the next days. With a median of 5 days plus weekend delay will see in the middle of next week. The plan is to allow sport activities like tennis, golf, etc. at 1st May and allow restaurants to open on 15. May, under the assumption that corona infections will not dramatically raise.

Remote Assist and Augmented Reality Apps

At work we are promoting remote assist apps like Dynamics 365 Remote Assist and Dynamics 365 Product Visualize for a long time. But from my experience for most people augmented reality was something between cool and useless but definitve not usefull or business enabler.

Remote Assist Tools
Training a remote assist app for griller mainteance (Summer 2019)

Now, with all the restrictions some folks start to understand that remote assiting tools can help them to operate their business, e.g. to provide technical support for customers from employees at their home office.

Office 365 and Teams retroperspective

More than a month has passed since the lock-down started. Thankfully we were already using remote and collaboration tools for a while now. We were using SharePoint almost 10 years from now. Long before Skype for Business and Teams was released we had Lync On-Premises but switched quickely to Skype and Teams.

In the last weeks the active Teams applications used became a little more diverse. Windows apps are still mostly used but mobile and web clients are also present. For example, I’m working with my private Laptop and have my own Office 365 Tenant, therefor I’m not using the Teams application from the company but preferr the web application.

Diverse Teams clients
Collaboration from different plattforms works seamless

Statistics show that chats are the most widely used way to communicate. One to one discussions are done via chats, like we’d talk to the colleagues at work. For what would be a meeting at work, calls are arranged to discuss a specific and very certain topic.

Teams chat increase
Chats substitute face to face talks

Microsoft once reported a 775 % increase in team usage sind the lock-down in most european countries. On a smaller grain size, we see a very similar increase in our usage. While Mail, SharePoint and Onedrive are not more affected than before, our Teams usage has increased by 540%.

540 % increase in Teams usage
540% increase on Teams usage at work

Apps and APIs

Some think tracing apps can help to reduce the spread of corona, by tracking contacts and informing endangered persons. At the beginning there was the proprietary Closed-Source Stopp Corona tracking app, promoted by the Red Cross and developed by Accenture. After a few weeks the PEPP-PT (Pan-European Privacy-Preserving Proximity-Tracing) approach was announced which should make apps from different venders interoperable. Last week Apple and Goolge announced to provide interoperatble APIs for the same purpose. Questions are (1) will Apple and Goole implement PEPP-PT standards or build their own proprietary solution (2) which one will the developers in europe choose? From my perspective it is not very likely that Apple and Google will give a damn about european data privacy concerns and flood the world with their solution.

Another very concerning development is that the Austrian State Printing House (Staatsdruckerei) is working an app called Restart.ID to proof a persons immunity status. The idea is to link a persons immunity state with the persons passport. This is practically the same surveillance and discrimination approach like authoritarian regimes like China have implemented. Only if you have the app installed, obeyed the rules and have a green marker you may pass the border, access the subway, etc.

COVID tracking app from china
Role model China, ID + tracking + immunity status in an app
https://edition.cnn.com/2020/04/15/asia/china-coronavirus-qr-code-intl-hnk/index.html (Online 19.4.2020)

I’d expect that these development will have a negative effect on the acceptance and backfire. An Austrian proprietary closed-source app like Stopp-Corona is somewhat excepted. An app based the US companies Apple and Google, which regulary parise themself on how much they know about their users and actively use this information to make profit will have much less acceptance. An app that links a persons ID, passport, medical history and vaccination state is in my oppinion not acceptable.

BTW: Climate crisis is still here

Warm weather and COVID-19 lock-down is bad combination. Last week Wolfsberg, Frantschach St. Gertraud, St. Andrä and St. Paul had to limit the usage of water. This week another district is running out of water. Neuhofen a.d. Krems announced that it running out of water, because peoples’ excessive use for swimming pools and car washing. It looks like we will not be able to go abroad this summer, and the climate crisis will came back with a blast this summer.

Repetitive cooking

After five weeks of cooking on my own, it’s time to repeat good recipies and improve others: Geschnetzeltes, Pork, Spagehtti, Noodle Gratin, Spicy meat with rice, Toast

Cooking

#stayhealthy