One of the most underutilized capabilities of D365FO is its native Power BI integration. D365FO allows you to embed Power BI reports directly within Dynamics workspaces, giving finance and operations users access to real-time analytics without leaving their primary application. This guide walks through the complete setup and design process.
Understanding D365FO Analytical Workspaces
D365FO ships with built-in analytical workspaces for most modules — Financial Overview, Purchase Spend Analysis, Sales and Profitability Performance, Warehouse Performance, and more. These workspaces use Power BI Embedded to display reports based on the Entity Store (D365FO's dedicated analytics database).
You can either customize these built-in workspaces or build entirely new ones for your specific business needs.
Step 1: Configure the Entity Store
The Entity Store is D365FO's OLAP-style read replica database, optimized for analytics. You must first enable and schedule the Entity Store data refresh:
- Navigate to System Administration → Setup → Entity Store
- Enable the aggregate measurements relevant to your reports (Retail Channel Performance, Vendor Invoice V2, etc.)
- Schedule refresh intervals (15 minutes to daily, depending on data freshness requirements)
- Monitor the last refresh time and error logs
Step 2: Connect Power BI Desktop to Entity Store
The Entity Store exposes an Azure SQL Database connection string available in LCS:
- In Power BI Desktop, select "Get Data" → "Azure SQL Database"
- Enter the Entity Store server and database name from LCS
- Use DirectQuery mode for near-real-time data (subject to refresh frequency)
- Import mode for static reports with scheduled refresh
Step 3: Key Financial Aggregate Measures
The Entity Store pre-aggregates D365FO data into analytical models. Key aggregate measures for finance dashboards:
| Aggregate Measure | Use Case | Refresh Frequency |
|---|---|---|
| LedgerTransactionMeasure | General ledger transaction analysis | Daily |
| VendInvoiceTransactionMeasure | Vendor invoice and payment analytics | Hourly |
| CustInvoiceTransactionMeasure | Customer invoice and AR aging | Hourly |
| InventValueMeasure | Inventory value and turnover analytics | Daily |
| PurchaseOrderMeasure | Procurement spend analysis | Daily |
| SalesOrderMeasure | Sales performance analytics | Hourly |
Step 4: Building the CFO Dashboard
A comprehensive CFO dashboard for D365FO should include these report pages:
Financial Performance Page
- Revenue vs. budget (current month and YTD) — KPI cards with variance
- Gross margin trend by month (line chart, current year vs. prior year)
- Operating expense breakdown (waterfall chart)
- EBITDA trend (combo chart with bar + line)
Cash & Liquidity Page
- Cash balance by bank account (clustered bar)
- Projected cash flow (based on open AR and AP)
- Days Sales Outstanding (DSO) and Days Payable Outstanding (DPO) trends
- Working capital ratio trend
AR/AP Analytics Page
- AR aging buckets with drill-through to individual invoices
- Top 10 overdue customers with contact information
- AP aging and upcoming payments due
- On-time payment rate trend
Step 5: Embed in D365FO Workspace
After publishing your report to the Power BI service:
- In D365FO, navigate to the relevant module workspace (e.g., CFO workspace, Financial Overview)
- Select "Power BI" and link your published Power BI report
- The report appears as an embedded tab within the D365FO workspace
- Users with the appropriate D365FO role automatically have access
- The report respects D365FO legal entity context — users only see data for their authorized entities
Row-Level Security and Multi-Entity
For organizations with multiple legal entities, implementing Row-Level Security (RLS) in Power BI ensures users only see their authorized entity data:
- Create RLS roles in Power BI Desktop using DAX filter expressions on the DataAreaId field
- Map roles to D365FO user groups or Azure AD groups
- Test thoroughly — data leakage across legal entities is a serious compliance issue