Microsoft-hosted Dynamics 365 Finance Tier1 Sandboxes are dicontinued: Switch to Cloud-Hosted

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 environment
Artefacts 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.

Pivot Financial Dimensions into a single Record

At work we had the requirement to provide a SQL view of PurchLine Records including their financial dimensions. However, since Dynamics AX the financial dimension data model has been enhanced to be more flexible. In contrast to older versions where Dynamics AX 2009 supported by default 3 dimensions, you are now free to configure as much as you want.

Financial Dimensions in Dynamics AX 2012

The tables involved are the DimensionAttributeValueSet, DimensionAttributeValueSetItem, DimensionAttributeValue and the DimensionAttribute. The following statement collects the financial dimension values from the image above (RecID for the set is 52565498264).

select
DIMENSIONATTRIBUTEVALUESET.RECID as DIM_ID,
DIMENSIONATTRIBUTE.NAME as DIM_NAME,
DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE as DIM_VALUE
from DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUESETITEM
on DIMENSIONATTRIBUTEVALUESET.RecId = DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUE
on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID
join DIMENSIONATTRIBUTE
on DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID
where DIMENSIONATTRIBUTEVALUESET.RECID = 52565498264

The result looks like this:

The PIVOT is used to switch row values into columns. The following statement creates a single record with columns for the dimensions:

SELECT * FROM
(
select
DIMENSIONATTRIBUTE.NAME as DIM_NAME,
DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE as DIM_VALUE
from 
DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUESETITEM
on DIMENSIONATTRIBUTEVALUESET.RecId = DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUE
on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID
join DIMENSIONATTRIBUTE
on DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID
where DIMENSIONATTRIBUTEVALUESET.RECID = 52565498264
) AS SourceTable
PIVOT
(
Max(DIM_VALUE)
FOR DIM_NAME IN 
([Department], 
[ItemGroup], 
[CostCenter], 
[BusinessUnit], 
[Project])
)
 AS PivotTable

The result looks like this:

If some dimensions do not hold values it will be NULL in the SQL but the statement will not fail

In order to join the financial dimension pivot record with the transaction like (e.g. PurchLine) add the RecId from the FinancialDimensionAttributeValueSet and join it on the Dimension reference field from the transaction table. Here is an example:

select
PurchId, ItemId, PURCHQTY, PURCHPRICE,
FinDim.Department, FinDim.ItemGroup, FinDim.CostCenter, FinDim.BusinessUnit, FinDim.Project
from PURCHLINE
left join
(
SELECT * FROM
(
select
DIMENSIONATTRIBUTEVALUESET.RECID as RECID,
DIMENSIONATTRIBUTE.NAME as DIM_NAME,
DIMENSIONATTRIBUTEVALUESETITEM.DISPLAYVALUE as DIM_VALUE
from DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUESETITEM
on DIMENSIONATTRIBUTEVALUESET.RecId = DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUESET
join DIMENSIONATTRIBUTEVALUE
on DIMENSIONATTRIBUTEVALUESETITEM.DIMENSIONATTRIBUTEVALUE = DIMENSIONATTRIBUTEVALUE.RECID
join DIMENSIONATTRIBUTE
on DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE = DIMENSIONATTRIBUTE.RECID
) AS SourceTable
PIVOT
(
Max(DIM_VALUE)
FOR DIM_NAME IN ([Department], [ItemGroup], [CostCenter], [BusinessUnit], [Project])
) AS PivotTable
) as FinDim
on PurchLine.DefaultDimension = FinDim.RecId
where PURCHID = '000081' and DATAAREAID = 'USMF'

The result set are two records, one for each PurchLine, and the corresponding financial dimension values:

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