Load context-sensitive Power BI tiles in Dynamics 365 Finance via Power Apps

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)

If(
    !IsBlank(Param("EntityId")), 
    Set(FinOpsInput, Param("EntityId")), 
    Set(FinOpsInput, "")
   )
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:

"https://app.powerbi.com/embed?dashboardId=d496ce2a-5836-4fc2-a3e9-34a5c1cdd674&tileId=7f1aa48a-e2f8-4eef-a59f-56a0e873e143&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly9XQUJJLU5PUlRILUVVUk9QRS1yZWRpcmVjdC5hbmFseXNpcy53aW5kb3dzLm5ldCIsImVtYmVkRmVhdHVyZXMiOnsibW9kZXJuRW1iZWQiOmZhbHNlfX0%3d&filter=SalesInvoiceV2Lines/InvoiceAccount eq '"& FinOpsInput &"'"

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 Details
Copy 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.

Third week under second Covid-19 lock-down in Austria (6th Dec. 2020)

Third week home office
Third week home office

Three weeks of hard lock-down have passed and we are about to switch back to lock-down “light”. Stores will re-open on Monday 7th, but hotels, restaurants, bars and ski lifts remain closed. The curfew will be reduced from all-day to 18:00 – 06:00. Home-office has never been mandatory but recommended and will be recommended in the future.

Data shows that both lock-down “light” and “hard” lock-down were successful and infection rate is going down to high but stable niveau. Question is how will Christmas and New Year impact? We are eagerly watching the data from US and how Thanksgiving impacted the number of infections.

Infections are dropping after 3 weeks of hard lock-down
https://covid19-dashboard.ages.at/

Austria is staring voluntary free mass-testing. The army is organizing hundreds of testing sites all over the country. Anti-Gen quick tests are used to identify infectious people. These test have a very low false-negative rate. In case someone is identified as positive, a PCR test is done. Hopefully many non-symptomatic infections people can be found and isolated before Christmas to avoid a third wave and a third lock-down in January. While the registration was a digital disaster the testing itself was easy. Make an appointment and fill out a form. Bring the form and an ID card to the test site. If you have no printer at home it will be printed for you. Get tested and leave the test site. The result is sent to your mobile phone after aprox. 15 minutes.

Negativ Covid-19 test result 🙂

It’s all over now, baby blue

No more Microsoft-Hosted Tier1 Dynamics 365 Finance / SCM environments

Microsoft-Hosted Dynamics 365 Finance and Supply Chain Management Tier1 environments are gone. If you need a development or build environment you have to host it at your own costs in an Azure subscription. Typically partners will create an Azure Subscription for the customer and deploy the Tier1 environments there. Depending on the size (recommended ist D12v2) and Auto-Shutdown strategy the costs will be somewhat equal to the former Tier1 Addons.

During the first lock-down in spring Azure reached its capacity limit in Europe. Even Netflix and YouTube reduced the streaming quality to HD and lower. Microsoft shut of Teams functionality like seaing someone typing in the chat window and video conferences. In spring we were advised to deploy Tier2 environments in other regions than Europe. Thankfully this time we did not experience such capacity limits although many partners deployed many virtual machines for their customers in November.

IT-Disaster Austria Part 1 – The empty Online Shop

This week was an IT disgrace for the Austrian government. Last week was Black-Friday week and Cyber Monday at Amazon. In order to strengthen Austrian companies the Ministry for Digital and Economic affairs together with the Austria Commerce Chamber decided invest 700.000€ to develop and launch an Austrian Online Shop. This week the “Kaufhaus Österreich” / “Store Austria” was launched and failed immediately. Because it is nothing more than a link list with a not functional search. The search does not index the products from the vendors but only their company name and description. For Example, if you search for a mouse no results are returned because no registered vendor has “mouse” in their company name 🤣

Kaufhaus Österreich
No mouse in Austria

IT-Disaster Austria Part 2 – COVID-19 mass-test registration

Austria will conduct free corona mass tests in December. To organize these tests an online registration page. There you have to provide name, social security number, Email or phone number. After the registration a mail is sent with a link to make an appointment in a local test center. The registration site was announced in the media but the registration site failed permanently with an unexpected error. People report that 5 or more try were required to get registered.

österreich-testet Corona Anmeldung
Registration site for Corona mass-test failed

But the “unexpected server error” on submit was not the only problem. The Austrian news paper Der Standard reported a list of problems:

  • The site went offline only few hours after launch because of an DDOS attack
  • People could not book an appointment in their home district
  • Someone managed to book so many appointments that a test site was exclusively blocked
  • Some Email addresses were deleted
  • The website had no imprint

Much more problematic is a huge data leak. More and more people report that they get personal data from other people. For example, on Wednesday a teacher from Carinthia reported that he got an appointment for another person from Vienna.

Second week under second Covid-19 lock-down in Austria (29th. Nov)

Home-Office and Remote-Work

The first lock-down in spring was a game changer. Home office has become normal. But also in non-lock-down times the usage of Microsoft Teams now is on a high volume. For us it has become the collaboration backbone.

Teams usage is stable no matter if lock-down
Teams usage is stable no matter if lock-down

Webcast Marathon

Last week on 26th November we held a webcast marathon. We presented 6 talks for Dynamics 365 Finance and Power Platform. The webcasts were organized using Microsoft Teams Live Events. The registration pages were quickly built using Microsoft Forms and automated using Power Automate. When a new participant registered for a webcast, PA read the registration from Form, picked the corresponding .ics calendar, sent a mail to the participant with the calendar .ics as attachment and created a new record in Azure table storage.

Webcast Registration via Power Automate
Webcast Registration via Power Automate

It turned out that the combination of Office 365, Power Platform and Teams work great together and allows to manage a complex distributed scenario like a webcast with different speakers exclusivly in the cloud.

Lock-down results

In contrast to the very strict first lock-down in spring, the actual “hard” lock-down allows more exceptions.

Monday morning: Some stay at home but many (have to) go to work (30th November)
Monday morning : First week of first lock-down in spring (16th March)

In theory we have a curfew all day. Schools and Universities are closed, many retail stores are closed, home depots are closed for B2C customers and gastronomy is limited to delivery. But since going to work, sport and shopping is allowed we don’t really feel the pressure like in spring. However, it seems to work and the numbers are dropping.

New infections per day are dropping
New infections per day are dropping
Source: https://covid19-dashboard.ages.at

First Week under second Covid-19 lock-down in Austria (22nd. Nov)

Back in Home Office

Back in Home Office for 3 weeks

The second Corona wave hit us hard in Austria. After a very chilly summer, Corona strikes back. Compared to the first wave, testing capacities have rising up to 4x times. But even when considering a high number of unidentified cases in spring, the number of infections is much higher now and hospitals are now reaching their capacity limit.

Source: https://covid19-dashboard.ages.at/

We had a Soft-Lockdown in October with mainly restaurants closed and a pseudo-curfew from 20:00 to 06:00 but a lot of exceptions. Obviously the Soft-Lockdown came to late and had less effect, so we are now under a stricter Lockdown. Although the situation is more severe than in Spring, the restrictions are less hard now.

Notebooks are sold out

Home-Schooling and Home-Office has become a widely accepted practice. However, this results in a high demand of devices. At this moment almost all business grade laptops are sold out. No matter if you are Looking for Lenovo ThinkPads or ThinkBooks, HP Elitebooks, etc.

Webcast Marathon

Due to the pandemic our annual company event for our customers is canceled 😥 Since 10 years we are organizing an yearly event with our customers including good food, news and presentations.

Better times (2019)

This year we are presenting a day full of webcasts regarding the Microsoft Power Platform and Dynamics 365 Finance & Supply Chain. We will discuss collaboration in times of Home Office. Moreover we will present Power Apps, Power BI, Power Automate and Dynamics 365 Finance & SCM ERP projects.

Azure Backup Server agent installation trouble

Taking backups is crucial. I prefer to use the Azure cloud for storing backups. In case a disaster strikes on-premises, the data is at least save in the cloud. Microsoft is offering a great solution with Azure Backup. For taking simple file-based backups you only need the recovery agent installed on the source server. For taking more complex backups e.g. from SQL Server and HyperV the Azure Backup Server is required.

Azure Backup Server (aka. DPM)

The Azure Backup Server is a re-branded System Center Data Protection Manager. Backups can be stored locally on disk and in an Azure Backup Vault. Like the SCDPM Server, the Azure Backup Server requires agents to be installed on the source systems. This can be done using push or pull techniques. Within a domain you can instruct the DPM Server to install an agent on a server. You may also install the agent by hand and instruct DPM to connect to an already installed agent.

Azure Backup Server Console

I had one legacy server hosting a SQL Server 2012 instance, which was protected with System Center Data Protection Manager 2012 a while ago. The old agents were uninstalled years ago but left some entries that blocked the installation of the new DPM agent.

Identifying the problem

When the installation fails, a Log is created in C:\Windows\Temp. A look in the log file revealed that the installer found an installed product that should not be installed.

Agent installation started
 The agent bootstrapper is doing prerequisite checks
 Querying for Product with Upgrade code: {0BEE7F6A-CE2A-A5CF-FFEB-8E0F8A8CDE75}
 Querying for Product with Upgrade code: {EFF053DE-592F-5574-9AA3-64662A944952}
 IsProductInstalled: MsiEnumRelatedProducts returned ERROR_SUCCESS and product code found is {EECBB752-2C6E-45B7-9F18-2327B886309A}
 IsProductInstalled: Product: {EECBB752-2C6E-45B7-9F18-2327B886309A} is installed
 PerformAgentInstall failed with errorcode=addfd060
 Install ProtectionAgent failed with errorcode=addfd060
 Failed: Hr: = [0x80990a2d] DPMAgentInstaller failed, error says: [(null)]
 Failed: Hr: = [0x80990a2d] : SC-DPMRA found. Cannot install Microsoft Azure Backup Agent
 Failed: Hr: = [0x80990a2d] : Encountered Failure: : lVal : PerformAgentInstall(installargs, silent, skipKB)
 Failed: Hr: = [0x80990a2d] : Encountered Failure: : lVal : InstallProtectionAgent(false , false )

To identify the problem get_wmiobject can be used to display ID and Name. A old version of System Center Data Protection Manager Agent was not removed properly.

get-wmiobject Win32_Product | Format-Table IdentifyingNumber, Name, LocalPackage -AutoSize
DPM 2012 Agent leftover

Remove DPM agent leftovers

A first attempt to get rid of the DPM 2012 was to clean the registry. Therefore the regedit.msc was called and all entries referencing {EFF053DE-592F-5574-9AA3-64662A944952} were deleted. This was not sufficient to install the new agent.

Microsoft provides a tool to remove entries from uninstalled programs. The tool MicrosoftProgram_Install_and_Uninstall.meta.diagcab can be downloaded here: Fix problems that block programs from being installed or removed . It found the entry for DPM 2012 and removed it.

Fixit for blocking installation / uninstallation

The tool was a great step in the right direction, however the installation failed again because the DPM service could not be installed. The log file showed the following entry:

Received type [0x01000000] message [Service 'DPM CPWrapper Service' (DpmCPWrapperService) could not be installed. Verify that you have sufficient privileges to install system services.]

It turned out that there was already a CPWrapper Service but it was not functional anymore. The path to binary was no longer working. Therefore the property dialog from the service MMC was also not working. But there exists a tool to remove corrupt service entries. Process Hacker can be used to simple delete the service entry.

Process Hacker

Finally, the agent installation was successful

Azure Backup Server agent installation finished

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.

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

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.