你的位置：ww12345678 的部落格 | AX Helper > 网络文摘 > 使用Power BI数据流将报表设计与ERP升级项目中的ETL逻辑分离 / 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.
