Customizing Power BI data models for project accounting: direct extension experiences

ERP Microsoft Dynamics 365
, , , , , , , ,
1

We recently extended the default Power BI data models for our project accounting analytics in D365 F&O 10.0.43, and I wanted to share our approach and get feedback from others who’ve done similar customizations. Our finance team needed custom KPIs around project profitability, resource utilization, and budget variance that weren’t available in the standard embedded reports.

We used direct model extension through the workspace provisioning feature, connecting to Dataverse entities for real-time project data. The semantic models support both DirectQuery for live data and incremental refresh for historical analysis. Curious about others’ experiences with this approach - what worked well and what challenges did you face?

system 2

Direct model extension via workspace provisioning is a solid approach for this use case. A few observations and additions based on similar F&O project accounting implementations:

Semantic Model Architecture Choices

The DirectQuery / incremental refresh hybrid you’re running is the right call for project accounting, but watch the Dataverse throttling limits on DirectQuery — high-frequency refresh against msdyn_project, msdyn_projecttask, and related entities can hit service protection API limits under load. Composite models (mixing DirectQuery for transactional tables and Import for dimension/budget tables) often perform better in practice (verify in your version).

Extending the Data Model — Power Query M Example

Dev paradigm: Power Query M (Power BI Desktop / PBIP format)

// Custom Budget Variance measure table via M transform
let
    Source = CommonDataService.Database("your-org.crm.dynamics.com"),
    ProjectBudget = Source{[EntitySetName="msdyn_projectbudgetlines"]}[Data],
    ActualCosts   = Source{[EntitySetName="msdyn_actuals"]}[Data],

    BudgetClean = Table.SelectColumns(ProjectBudget,
        {"msdyn_project", "msdyn_budgetedcost", "msdyn_budgetedrevenue"}),

    ActualClean = Table.SelectColumns(ActualCosts,
        {"msdyn_project", "msdyn_amount", "msdyn_transactiontype"}),

    Joined = Table.NestedJoin(BudgetClean, "msdyn_project",
                              ActualClean,  "msdyn_project",
                              "Actuals", JoinKind.LeftOuter),

    Expanded = Table.ExpandTableColumn(Joined, "Actuals",
        {"msdyn_amount", "msdyn_transactiontype"}),

    Variance = Table.AddColumn(Expanded, "BudgetVariance",
        each [msdyn_budgetedcost] - [msdyn_amount], Currency.Type)
in
    Variance

Then surface Budget Variance as a DAX measure:

Budget Variance % =
DIVIDE(
    SUMX(ProjectVariance, [BudgetVariance]),
    SUMX(ProjectVariance, [msdyn_budgetedcost]),
    0
)

Resource Utilization KPI

Standard entities don’t expose utilization directly. Pull from msdyn_resourceassignment and calculate booked vs. capacity hours against bookableresource capacity. Be explicit about your time-zone normalization on msdyn_plannedwork — it stores duration in contour XML, not raw hours (verify in your version).

Debug Approach

  • Use Power BI Performance Analyzer to capture DirectQuery SQL emitted to Dataverse — identifies slow joins early.
  • Enable Dataverse Plugin Trace Logs if computed columns on project entities return unexpected nulls.
  • Cross-reference OData responses directly: GET /api/data/v9.2/msdyn_actuals?$filter=... to isolate model vs. source issues.

Rollback

  • Store semantic models in PBIP format (source-control friendly folder structure) in Azure DevOps. Each workspace deployment should tag the prior .pbidataset definition.
  • Use Power BI Deployment Pipelines with a dedicated Dev → Test → Prod workspace chain; rollback is a single pipeline stage revert rather than manual republish.
  • Never overwrite the default embedded analytical workspace — clone it and publish to a separate workspace to preserve the OOB reports as a fallback.

The biggest pain point we see consistently is incremental refresh partition misconfiguration when mixing F&O ledger dates with Dataverse transaction dates — ensure your RangeStart/RangeEnd parameters align to the fiscal calendar, not calendar year.

This draft is based on general Microsoft Dynamics 365 knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

This is interesting timing - we’re evaluating the same approach for our manufacturing client. How did you handle the workspace provisioning? Did you create custom workspaces or extend the existing Project Management workspace? Also, what’s your refresh schedule for the DirectQuery vs incremental refresh split?

4

We did something similar for project cost tracking. Extended the ProjectAccountingCube entities through Dataverse and created calculated measures for our custom profitability metrics. One challenge was handling the data latency - DirectQuery is great for current data but performance suffered with complex DAX calculations across large datasets.

We ended up using hybrid mode: DirectQuery for transactional project data (invoices, timesheets) and import mode with incremental refresh for historical trend analysis. Refresh runs every 4 hours during business hours, nightly for full historical data.

5

Good question on workspace provisioning. We extended the standard Project Management workspace rather than creating new ones from scratch. This preserved the existing reports while adding our custom visuals and measures. The key was using the workspace extension framework properly - we created a new workspace configuration that references the base workspace but adds our custom semantic model.

For refresh strategy, we mirror what project_controller described - hybrid approach works best. Our setup refreshes transactional data every 2 hours via DirectQuery, historical aggregations overnight. Performance improved significantly once we implemented aggregation tables in the semantic model.

6

The Dataverse integration is crucial here. Make sure you’re using the right virtual tables for project accounting entities - some entities sync better than others. We had issues with ProjectContract and ProjectInvoiceProposal entities where the Dataverse sync lagged behind F&O by 10-15 minutes.

Solution was to use the change tracking features in Dataverse and implement incremental refresh based on modified dates. Also recommend enabling the dual-write infrastructure if you need bidirectional data flow, though for analytics it’s usually one-way from F&O to Dataverse to Power BI.

7

From an architecture perspective, the direct model extension approach is solid for project accounting because the data volume is usually manageable compared to something like inventory transactions. The semantic model structure in D365 already has good star schema design for project dimensions.

One tip: leverage the built-in row-level security from D365 through the Dataverse connection. It automatically respects the project security roles, so users only see data they’re authorized for. This saved us weeks of custom RLS implementation in Power BI.

8

After three months running this solution in production, here’s my consolidated experience with direct Power BI model extension for project accounting:

Direct Model Extension Benefits: The semantic model extension capability in D365 10.0.43 provides excellent support for custom KPIs without breaking the upgrade path. We added 12 custom measures for profitability analysis, resource utilization rates, and variance tracking. These integrate seamlessly with the standard project accounting measures, and users see them as native functionality in the workspace.

Key advantage is maintaining the standard model structure while extending it. When Microsoft updates the base semantic model, our extensions remain intact. We’ve been through two platform updates without touching our custom measures.

Workspace Provisioning Approach: Extending the existing Project Management workspace proved more effective than creating custom workspaces. The workspace extension framework allows you to add report pages, custom visuals, and measures while preserving the standard navigation and security context. Users access everything through the familiar workspace interface.

Critical configuration: Set your workspace extension to reference the base workspace ID and specify your custom semantic model as an additional data source. This creates a unified experience where standard and custom reports coexist naturally.

Dataverse Entity Integration: Dataverse virtual tables for project accounting entities work well, but entity selection matters. High-performing entities for our use case: ProjectTable, ProjInvoiceTable, ProjTransPosting, ProjectContract. These sync reliably with minimal latency.

Challenging entities: ProjectInvoiceProposal and some project budget entities had sync delays of 10-20 minutes. We worked around this by using the change tracking metadata and implementing smart refresh logic that prioritizes recently modified records.

The dual-write infrastructure isn’t necessary for pure analytics scenarios. Standard Dataverse sync handles the F&O to Power BI data flow adequately. Enable change tracking on your key entities to support incremental refresh efficiently.

DirectQuery and Incremental Refresh Strategy: Hybrid mode is essential for balancing performance and freshness. Our production configuration: DirectQuery for current period transactions (last 30 days of project costs, timesheets, invoices), import mode with incremental refresh for historical data beyond 30 days.

Refresh schedule: Every 2 hours during business hours (8 AM - 6 PM) for DirectQuery partition, nightly full refresh for historical aggregations. This keeps reports responsive while ensuring data accuracy for real-time project monitoring.

Performance optimization: Implemented aggregation tables in the semantic model for common roll-ups (project totals by month, resource utilization by department). Reduced report load times from 8-12 seconds to under 2 seconds for most visuals.

Unexpected Benefits: Row-level security inheritance from D365 through Dataverse was a major win. Project managers automatically see only their projects, finance sees everything, resource managers see their team’s data - all without custom RLS configuration in Power BI. The security context flows naturally through the connection.

The extension approach also simplified our DevOps process. Custom semantic models deploy as solution packages through ALM, versioned alongside our other D365 customizations.

Overall, direct model extension provides the flexibility needed for custom project analytics while maintaining upgradeability and leveraging the platform’s native capabilities. Recommended approach for organizations needing tailored project accounting insights beyond standard reports.