Microsoft Dynamics 365 Business Management Solution Enthusiast
10. March 2023 Leave a comment
PowerBI supports training machine learning models like classification and regression in PowerBI Online. In preparation for a webcast I’ve recorded the training of regression model to predict mean-time-to-failure. Watch the step-by-step guide in my ERPCoder Youtube Channel
7. February 2023 Leave a comment
Dynamics 365 Finance and Supply Chain Management uses SQL Server Reporting Services for reporting and document generation. In 2022 Microsoft included SSRS (aka. Paginated Reports) in PowerBI Pro license. One may ask if it is possible to use PowerBI Paginated Reports as well for reporting purpose in Dynamics 365 F/SCM.
| X++ SSRS | PowerBI Paginated Reports |
| + Direct access to transaction DB + Business logic in X++ + Integrated with Dynamics 365 FO – Complex development – Embedded in Dynamics 365 FO deployment cycle | + Simple development using Report Builder + Integrated with Business Intelligence + Reuse existing data models – Limited development possibilities – No direct data access |
Paginated reports in PowerBI have only a limited capability to access data. There is no Dynamics 365 FO connector or OData access to entities. You could use different approaches to get Dynamics 365 FO data.
Paginated reports are designed by using the Report Builder. It is an adapted version of the SSRS Report Builder with some more data access features. In theory you could develop an SSRS report using Visual Studio and upload the .RDL file. However, PowerBI Paginated Reports are a limited version of SSRS and you might get errors.
Paginated Reports are a reduced SSRS feature in PowerBI. The Report Builder is very limited. You can define data access, defined parameters and place data on the report sheet. If you want to create printable documents without real-time requirement the Paginated Reports may be an option for you.
16. January 2023 Leave a comment
In some cases you want to filter a table based on a selection which is not related to the table. For example the SalesTable is realted to the CustTable via the Account. The QuotationTable is related to the SalesTable (QuotationID -> Origin). The QuotationTable also has an Account field but is not related to the CustTable.
Such a situation can be avoided by using a better design approach, however you may come across such a problem one day 😉
Create a new measure in the CustTable that returns the selected value. Place a table visual showing the CustTable in the report. For debugging reasons you may want to place a card visual with your measure as well.
M_SelectedValue = SELECTEDVALUE(CustTable[Account])
In the not-related table (QuotationTable) create a new measure that returns the account number which will be used to filter the records
M_Account = SELECTEDVALUE(QuotationTable[CustAccount])
Add a second measure to compare the selected CustTable value to the CustAccount value in the Quotation table and returns a numeric value (not a boolean). If there is no CustTable selected return e.g. 2, if the selected values are equal return 1, else return 0.
M_Filter = IF(
ISBLANK([M_SelectedValue]),2,
If([M_SelectedValue] = [M_Account], 1, 0)
)Place a table visual in your report showing the QuotationTable and both measures
Configure a filter on the visual to show only records where the M_Filter value greater than 0.
If no customer is selected all quotations are shown because the M_Filter is 2.
If a customer is selected M_Filter is 1 for all matching records or 0 otherwise.
Now you can hide the M_Filter in the QuotationTable so your users don’t get confused
21. August 2022 Leave a comment
A recent requirement at a customer was to compare Sales Quantity and Sales Totals of different time spans in one matrix. The customer is selling saisonal as well as all-time products. It should be possible to compare the sale of saisonal products from last season to the actual sales.
The original data contains 6 rows: The product, the corresponding collection, the sold quantity, the unit price, calculated total price (Qty * Price), date.
In order to make the Quantity and Totals available for a row-like visualization the original data was unpivoted. This created a new column called “Attribute” and another Column called “Value”. The unit (pcs, €) have been added to the attribute to make it more readable. The resulting data looks like the following in PowerBI:
To implement 2 independet filter options based on date and collection 4 independet tables have been added. A date table and a collection table for both filter options. The date tables can easily be generated by using the Calendar() function:
Filter1Date = CALENDAR(Date(2021,1,1), Date(2022,12,31))Filter2Date = CALENDAR(Date(2021,1,1), Date(2022,12,31))The collection tables are generated by using the Distinct() function on the collection column in the sales data table. Each of the tables contain a distinct list of collections available for filtering.
Filter1Collection = DISTINCT(Sales[Collection]) Filter2Collection = DISTINCT(Sales[Collection])
Three measures have been create. Two measures that sum up the Value column in the sales data filtered on the selected values in the Filter1* table or Filter2 table:
M_Sum1 = CALCULATE(
  SUM(Sales[Wert]),
  FILTER(
    Sales,    Â
    Sales[Date] >= FIRSTDATE(Filter1Date[Date]) &&
    Sales[Date] <= LASTDATE(Filter1Date[Date]) &&
    Sales[Collection] in VALUES(Filter1Collection[Collection])
    )
  )M_Sum2 = CALCULATE(
  SUM(Sales[Wert]),
  FILTER(
    Sales,    Â
    Sales[Date] >= FIRSTDATE(Filter2Date[Date]) &&
    Sales[Date] <= LASTDATE(Filter2Date[Date]) &&
    Sales[Collection] in VALUES(Filter2Collection[Collection])
    )
  )The third measure calculates the difference between the other two measures:
M_Diff = [M_Sum2]-[M_Sum1]Finally the report contains 5 visuals. Two slicers for date and collection on the left, another two slicers for date and collection on the right, and a matrix in the middle. The matrix shows the Attribute column as row and shows M_Sum1, M_Sum2 and M_Diff as values.
For example comparing collection “Summer 2021” from 1. Jan. 2021 till 30. Sep. 2021 to the aggregated values of “Collection 2022” + “Basic Wear” from 1. Jan 2022 till 30. Sep. 2022.
The slices on the left only affect the M_Sum1 measure which shows quantity and totals in the Filter1 column while the slicers on the right effect only M_Sum2 measure which shows the results in Filter2 column. The difference is calculate dynamically when adjusting the slicers.
7. June 2022 Leave a comment
PowerBI goals are used to keep track of targets like sales quantity, quality, etc. Goals can be linked to values from PowerBI reports. This video shows how to create a scorecard with goals based on Dynamics 365 Finance data.
12. January 2022 Leave a comment
Dataverse is the name of the Dynamics 365 Apps database (e.g. Sales, Service, Field Services, etc.). PowerBI desktop comes with a builtin Dataverse connector. However, if you have multiple environments not all of them may show up in the connector.
In order to make the environment visible in PowerBI Desktop Dataverse Connector you have to enable PowerBI Embedded in your environment.
Now when you open PowerBI Desktop and use the Dataverse Connector you should see the missing environment.
9. June 2021 4 Comments
Dynamics 365 for Finance and Supply Chain Management offers a broad range of reporting and business intelligence options. You can utilize the integrated Power BI dashboards, link the Power BI report gallery within Dynamics, use integrated SSRS reports or develop Power BI reports and dashboards that connect to Dynamics 365. Sometimes it can be hard to decided which one to choose. Here is a guideline which one to choose depending on the reporting requirements.
Decision Tree for Power BI / Reporting in Dynamics 365 Finance & SCM
What is the purpose of the report? Is it an interactive report / dashboard or is it a static list or document like artifact? For example, sales analysis is typical an interactive report while a collection letter is a printed document. Power BI is great for interactive data analysis, SQL Server Reporting Services (SSRS) is the right tool for lists and page oriented printable documents.
Do you need to see transactional data as soon as it is generated in Dynamics? For example posting and invoice and immediately printing the document. If so, you need to access the transactional database (AxDB). There are two ways: Use integrated Reporting Services or query entities via OData. However, using entities allows you to access the AxDB but Power BI doesn’t support Direct Query mode for OData, i.e. you have to hit refresh in order to get the latest data.
Is Dynamics 365 Finance the only data source for your report, or do you need to integrated external data sources as well? An example could be to develop a revenue analysis which includes actual sales data from Dynamics 365 as well as demographics and household income. Integrated Power BI dashboards in Dynamics 365 use direct query to access the AxDB and cannot integrate other data sources. It is also not recommended to load external data into Dynamics 365 AxDB because you have a limited cost free database size in your subscription. Additional SQL storage has to be paid.
Dynamics 365 Finance and Supply Chain Management includes the rights to view the integrated Power BI dashboards. No additional Power BI license is required. Reports developed using the integrated SQL Server Reporting Services technology are also covered by the Dynamics license. External Power BI reports, dashboards and paginated reports require additional Power BI licenses. At least Power BI Pro for reports and dashboards, Power BI Premium Capacity or Premium per User for paginated Reports.
An interactive custom Power BI Report viewed in Dynamics 365 Finance via the users Report Catalog option
Production Performance is part of Dynamics 365 Finance & SCM and directly connects to the entity store (aka. AxDW)
A paginated report in Power BI (Premium)
A static SSRS paginated report in Dynamics 365 Finance and SCM
Before you start working with a certain product, make sure to understand the requirements. Identify the data sources and how to access them. Then choose the right tool for the job. Don’t try to make a printable Power BI or fancy SSRS. By leveraging the full reporting and BI potential you can deliver a great user experience that adds value to Dynamics 365 Finance and SCM.
9. December 2020 1 Comment
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:
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 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.
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.
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.
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, "")
)
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.
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.
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.
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.
22. September 2020 Leave a comment
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.
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.
From PowerBI workspace create a new entity using dataflow. Choose the OData feed for Dynamics 365 and provide the URL for the CustomersV3 entity.
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.
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
Select the data gateway and provide a user to access the database
Select the view and load the AX 2012 data to PowerBI. Save the dataflow
After you have created both dataflows return to your workspace, go to your dataflows and refresh both to load the data.
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:
Select both dataflows
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.
From the new query make sure to remove duplicate customers
If your have a PowerBI Pro but not a Premium subscription, deactivate load of the underlying queries.
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:
Select the merged Customer data source.
You can use the dataflows in your PowerBI datamodel but dont have to worry about the logic behind
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.
14. July 2020 Leave a comment
I’ve recorded a walkthrough how to configure PowerBI for Dynamics 365 Finance and Supply Chain Management and deploy the standard dashboards.
ErpCoder 