The Power of Embedded Analytics in Dynamics 365 Finance & Operations

Embedded Analytics vs. Standalone Power BI

The choice between embedded analytics and standalone Power BI is not binary. Most organizations benefit from both, but understanding the trade-offs is essential for governance and adoption.

Embedded Analytical Workspaces are Power BI experiences built inside the D365 web client. They inherit row-level security (RLS) from Dynamics 365 roles, require no user licensing (count as Dynamics licenses), and refresh in near real-time via Direct Query or scheduled imports. Users never leave the ERP interface. Navigation is seamless, and role-based access is automatic. The downside: workspaces are published and managed through Dynamics app lifecycle, not Power BI Service. Custom visuals and advanced data modeling are possible but require development discipline.

Standalone Power BI offers unlimited design freedom, shareable reports outside D365, and independent governance. However, users must obtain Power BI Pro or Premium licenses, data is replicated and stored in a separate semantic model, and row-level security must be manually configured. Adoption friction is higher—users must context-switch out of their ERP system.

The Hybrid Approach: Most mature organizations embed core financial dashboards (CFO overview, bank management, cost analysis) in D365 workspaces, while publishing exploratory or cross-functional reports to standalone Power BI Premium. This keeps finance teams anchored in their daily workflow while enabling broader self-service analytics for planners, managers, and analysts.

Analytical Workspaces in Finance & Operations

Microsoft ships Dynamics 365 F&O with approximately 25 pre-built analytical workspaces. These are not reports—they are interactive dashboards composed of Power BI visuals, KPI cards, and embedded reports, all wired to the same underlying data model. Key workspaces include:

Workspace	Purpose	Primary Users
CFO Overview	Financial health snapshot: revenue, expense trends, cash flow, working capital	CFO, Controller, Finance Director
Bank Management	Cash position, bank account balances, reconciliation exceptions, foreign exchange	Treasurer, Cash Manager
Cost Management Analytics	Cost rollups by dimension, variance analysis, product profitability	Cost Accountant, Plant Controller
Budget Control Workspace	Budget vs. actual, spending trends, variance explanations, approval workflows	Budget Manager, Department Head
Sales Profitability	Revenue by customer, product, region; margin analysis	Sales Director, Analyst
Inventory Aging	Slow-moving stock, inventory composition, turnover metrics	Supply Chain Manager, Warehouse Manager

Each workspace includes pre-configured filters, slicers, and drill-through capabilities. They are production-ready on day one and require minimal configuration. However, out-of-the-box workspaces often need customization to reflect company-specific dimensions (cost centers, legal entities, product families, operating units). This is where custom analytical workspaces enter the picture.

Entity Store & Aggregate Measurements

The Entity Store is Dynamics 365’s hidden analytical engine. It is a pre-aggregated data warehouse within the Dynamics database that stores denormalized snapshots of transactions, balances, and financial data. Rather than forcing Power BI to aggregate millions of GL transactions in real-time, the Entity Store pre-computes common aggregations (by ledger account, cost center, customer, product, date) and surfaces them as fast-loading tables.

How it works: When you publish an analytical workspace, Dynamics creates or updates Entity Store views. These views are materialized tables or indexed views that refresh via batch processes (typically nightly or on-demand). Power BI then queries the Entity Store directly using Import or DirectQuery mode. Query response times drop from minutes to seconds because the data is pre-joined and pre-aggregated.

Aggregate Measurements are Dynamics artifacts that explicitly define what the Entity Store should pre-compute. They are XML-based declarative rules that specify:

• A base fact table (General Ledger, Vendor Invoice, Customer Invoice, etc.)
• Dimensions to aggregate along (Account, Customer, Product, Date, Department)
• Measures to calculate (sum of amounts, count of transactions, average)
• Filters and partitioning logic

Developers define aggregate measurements in Visual Studio using the Aggregate Data Wizard. Once deployed, the Entity Store framework automatically generates the underlying SQL tables and refresh jobs. This eliminates the need for external ETL tools or manual dimension tables. The entire chain—from transactional data to aggregated views to Power BI dashboard—is declarative and version-controlled.

Key advantage: Aggregate measurements are transparent to the business user. A finance manager does not need to understand SQL or ETL. They see a Power BI dashboard that refreshes every hour and never waits for a slow query.

Building Custom Analytical Workspaces

Building a custom analytical workspace involves four steps:

1. Define Aggregate Measurements
Use Visual Studio to create aggregate measurements that pre-compute the data your workspace will visualize. For example, if you need revenue by product line and geography, define an aggregate measurement that groups GL transactions by those dimensions. Deploy to your development environment and test the Entity Store refresh.

2. Author Power BI Reports
In Power BI Desktop, connect to your Dynamics environment (or Azure Analysis Services if you are using Synapse). Import or DirectQuery the aggregate measurements and configure semantic model relationships. Design visuals—charts, tables, KPIs—following your company’s analytics standards. Avoid custom visuals at first; stick to native Power BI visuals for maintainability.

3. Publish to Power BI Service (Embedded Workspace)
In Power BI Service, create a new app workspace. Publish your report. Configure row-level security (RLS) rules that map Dynamics roles to row filters. For example, if a user has the “Cost Center Manager” role in Dynamics, Power BI should filter the dashboard to show only that cost center’s data. Use the Dynamics role mapping feature or manual RLS expressions.

4. Embed in Dynamics Workspace
In Dynamics, navigate to Workspaces > Create New. Add Power BI tiles pointing to your published report. Configure refresh frequency (real-time DirectQuery or nightly import). Users see the workspace in their home page and access it via role-based visibility rules. The workspace tile counts as a Dynamics license—no separate Power BI licenses required.

Common patterns:

• KPI Card Pattern: A single large number (revenue this month, cash position) paired with a trend sparkline.
• Drill-Through Pattern: Click a summary chart to drill into detail. For example, click a product category in a profitability summary to see individual product margins.
• Slicer Pattern: Add top-level slicers (date range, cost center, legal entity) that apply to all visuals in the workspace.
• Scorecard Pattern: Track a set of financial metrics (gross margin %, days sales outstanding, inventory turns) against targets.

SSRS Operational Reports

While Power BI handles interactive dashboards, SQL Server Reporting Services (SSRS) remains essential for operational and regulatory reports. SSRS reports are pixel-perfect, paginated, and optimized for printing and PDF export—critical for audit trails, regulatory submissions, and month-end close processes.

Dynamics 365 ships with dozens of standard SSRS reports: GL trial balance, aged payables, aged receivables, fixed asset reports, and more. These are accessed via the Report menu in the web client and can be embedded in workspaces or exported on a schedule.

Limitations of SSRS in modern Dynamics: SSRS reports rely on the SQL Server Reporting Services infrastructure, which Microsoft has deprecated in favor of Power BI. New feature development is limited. If your organization is standardizing on Power BI, migrating custom SSRS reports to Power BI over time reduces technical debt.

When to use SSRS:

• Regulatory or audit reports that must match a fixed layout (bank reconciliation statements, tax schedules)
• High-volume batch exports (all customer invoices, all purchase orders)
• Reports that are printed or distributed as PDFs with consistent formatting

Electronic Reporting Framework

The Electronic Reporting (ER) framework is Microsoft’s modern, low-code alternative to SSRS. It is declaratively configured (not code-heavy) and supports Excel, PDF, XML, and JSON output formats. ER is the preferred tool for regulatory reporting and is extensively used in localized versions of Dynamics (especially in Europe).

Why ER matters: Many countries require specific GL layouts, tax schedules, or audit reports in mandated formats. ER configurations are version-controlled, tested, and easily deployed across environments. The framework separates data model from presentation, making it easy to reuse the same report structure for different periods or entities.

Example: A German Dynamics user must file a GL trial balance in a specific tax authority format. The ER framework includes a pre-built configuration for this format. The user runs the report, Dynamics generates an Excel file in the required structure, and the user submits it to the tax authority—all within Dynamics, no manual data export or reformatting.

ER configurations are authored in the Dynamics UI (no Visual Studio needed) and are localized for different countries. If you operate in regulated industries or multiple countries, ER is indispensable.

Financial Reporting & Report Trees

Financial Reporting (also called Management Reporter in older versions) is a standalone service integrated with Dynamics 365 that allows finance teams to build consolidated financial statements, segment reports, and executive summaries without writing queries.

Key concepts:

• Row Definitions: Declare which GL accounts (or account ranges) to include in a financial statement. Each row can be labeled (e.g., “Revenue,” “Cost of Sales”) and reference account numbers.
• Column Definitions: Specify columns for different periods, scenarios, or comparisons. For example, “Current Month,” “Year-to-Date,“ “Prior Year,“ “Budget Variance.”
• Report Trees: Consolidate multiple legal entities, cost centers, or business units into a single hierarchy. A global company can define a report tree that rolls up subsidiary results into a consolidated group statement.
• Linked Reports: Link one financial report to another, supporting drill-through and multi-level analysis.

Financial Reporting is accessed via a web portal or embedded in Power BI. Reports generate Excel or PDF output and can be scheduled for automated distribution. This tool is indispensable for month-end close, CFO reporting, and regulatory consolidation.

Advanced Integrations: Data Lake & Synapse

For organizations that require enterprise-scale analytics beyond embedded Power BI, Dynamics 365 supports exporting data to Azure Data Lake (gen 2) and Azure Synapse Analytics. This enables a modern data stack: Dynamics is the transactional system of record, the data lake is the enterprise data warehouse, and Synapse Analytics or Power BI Premium is the analytics platform.

Azure Data Lake Export: Dynamics continuously exports tables (GL, AR, AP, Inventory, etc.) to parquet files in an Azure Data Lake. The export happens asynchronously, typically every few minutes. The data lake becomes a time-series archive—every change is captured. Analysts can query raw data, apply custom transformations, and build their own dimensional models.

Azure Synapse Integration: Synapse Analytics acts as the enterprise semantic layer. Dynamics data is loaded into Synapse, cleaned, and modeled into star schemas or dimensional tables. Power BI (or other BI tools) then connect to Synapse rather than Dynamics, offloading query overhead from the production database. This architecture scales to billions of rows and supports unlimited concurrent users.

When to implement:

• Your organization has data scientists or BI teams that build complex predictive models or cross-functional data products.
• You need to integrate Dynamics data with non-ERP sources (CRM, supply chain systems, external data feeds).
• You have more than 1000 daily active dashboard users or complex analytics that timeout against the production database.
• You require full audit trails and point-in-time reporting (compliance, forensics).

Cost and complexity increase significantly. This approach is suitable for mid-market to enterprise organizations with mature analytics teams.

Best Practices & Implementation Strategy

1. Start with Embedded Workspaces, Not Standalone Power BI
Embedded workspaces have lower adoption friction, better security, and no extra licensing. Pilot with an out-of-the-box workspace (CFO Overview, Cost Management). Get executive sponsorship and quick wins. Only move exploratory analytics to standalone Power BI if the business need is clear.

2. Govern Entity Store Refreshes
Entity Store tables should refresh on a predictable schedule—nightly at 2 AM or hourly during business hours. Do not refresh on-demand during peak transaction hours. Set retention policies (keep 12 months of history) to avoid unbounded growth. Monitor refresh success with alerts.

3. Design Aggregate Measurements for Reuse
Do not create a separate aggregate measurement for every dashboard. Design measurements to be narrow in focus (one fact table, related dimensions) but reusable across multiple reports. Example: create one “Sales Order Facts” measurement that surfaces order lines, quantities, amounts, and dates. Reuse it across order status dashboards, revenue forecasts, and pipeline reports.

4. Implement RLS from Day One
Configure row-level security in Power BI so that a plant manager sees only their plant’s data, a cost center owner sees only their cost center. This is not a nice-to-have; it is table stakes for finance data. Test RLS rules with different roles before going live.

5. Decouple Data Refresh from Report Distribution
Use scheduled refreshes in Power BI to update data once per day, not every five minutes. Unless you are using DirectQuery (which has its own performance trade-offs), daily or weekly refreshes are sufficient for financial reports. This reduces load on the Dynamics database and improves report load times.

6. Document Calculations and Definitions
Define what “revenue,” “profit,” and “variance” mean in your organization. Different GL structures may calculate these differently. Document the business logic in Power BI tooltips and in a centralized glossary. Users should never wonder if a number includes inter-company transactions or accruals.

7. Plan for Localization and Compliance
If you operate in regulated countries, use Electronic Reporting for tax and audit reports. Do not rely on Power BI alone. Audit trails, regulatory formats, and localized GL structures require ER.

8. Monitor Adoption & Iterate
Track workspace usage via Power BI Service telemetry. Identify workspaces that nobody visits; retire them or redesign them. Collect feedback from finance teams on missing metrics or usability friction. Update dashboards quarterly based on feedback and business changes.

Frequently Asked Questions

Q: Do embedded Power BI workspaces require users to have Power BI licenses?
A: No. Users access embedded workspaces via their Dynamics 365 license. The Dynamics subscription includes Power BI licensing for workspace viewing. Standalone Power BI reports (outside Dynamics) require separate Power BI Pro or Premium licenses.

Q: How often should the Entity Store refresh?
A: For financial reporting, nightly refresh (after GL batch closes) is typical. For operational dashboards (inventory, orders), hourly or every 4 hours is reasonable. DirectQuery mode (no pre-aggregation) refreshes in real-time but is slower and only suitable for smaller datasets.

Q: Can I use both SSRS and Power BI in the same Dynamics environment?
A: Yes, and most organizations do. Use Power BI for interactive dashboards and analysis. Use SSRS (or Electronic Reporting) for regulatory, audit, and paginated reports. Over time, migrate SSRS reports to Power BI to reduce technical debt.

Q: What happens if my aggregate measurement has a bug?
A: The Entity Store will compute incorrect data until the measurement is fixed and the refresh is re-run. This is why you should test aggregate measurements in development with known sample data. Always have a change control process before deploying measurements to production.

Q: Can I row-filter a Power BI dashboard based on a user’s cost center in Dynamics?
A: Yes, using RLS. Create a table that maps Dynamics users to their cost centers, configure an RLS rule in Power BI that filters dashboard data by the logged-in user’s cost center, and ensure the Dynamics authentication passes the user context to Power BI. This requires development effort but is standard practice.

Q: Is Azure Synapse Analytics required for embedded analytics?
A: No. Entity Store & Power BI are sufficient for most organizations. Synapse is an add-on for enterprises that need unlimited scale, cross-system data integration, or advanced data science. For a typical mid-market company with 100-500 finance users, embedded workspaces are enough.

Q: How do I migrate from SSRS to Power BI?
A: Identify SSRS reports used by finance teams. For each report, assess whether Power BI can replicate the functionality. Build a Power BI version using the same underlying data. Test with stakeholders. Deploy the Power BI report and embed it in a workspace. Retire the SSRS report. Do this in phases; retiring all SSRS reports at once creates risk.

Q: What is the difference between Direct Query and Import in Power BI?
A: DirectQuery sends every user interaction (click, filter, drill) as a query to the underlying database (Entity Store, Synapse). Responses are real-time but slower. Import loads data into Power BI’s in-memory cache; interactions are instant. Import scales to 10 GB; beyond that, DirectQuery is necessary. For Entity Store data, Import with nightly refresh is typical.

Q: Can I embed Power BI reports in my company portal or SharePoint?
A: Yes, if you have Power BI Premium or use “publish to web.” However, these are standalone views outside Dynamics. For Dynamics-integrated workspaces with RLS inherited from Dynamics roles, embed in the Workspace control within the D365 web client. For external portals, use Power BI Embedded (a separate Azure service) with your own authentication.