BI reporting on time and attendance module experiences significant lag with multi-company queries

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

We’re running Power BI reports against our time and attendance module and seeing very different performance patterns. When we run reports for a single company, queries complete in 3-5 seconds. However, when we switch to multi-company views that aggregate data across our 8 legal entities, the same reports take 45-90 seconds to load.

We’ve confirmed that indexed views are present on the relevant tables (TimeRegistrationTable, TimeRegistrationTrans). The DAX queries are pulling attendance data, overtime calculations, and absence balances. Single company performance is excellent, but multi-company queries cause significant lag that’s impacting our HR team’s daily operations.

Has anyone encountered similar performance differences between single and multi-company BI reporting scenarios? I’m trying to understand if this is expected behavior or if there are optimization techniques specific to cross-company time attendance reporting.

system 2

I worked through this exact issue last quarter. Here’s what actually resolved the multi-company lag:

Root Cause Analysis: The indexed views you have are company-specific. When Power BI queries across multiple companies, D365 generates a UNION ALL query that hits each company’s view separately, then merges results. This bypasses the indexed view optimization benefits because the query plan treats it as multiple separate operations rather than one optimized lookup.

Single Company Fast Performance: Your single company queries are fast because they hit one indexed view directly. The query optimizer uses the index, statistics are current for that partition, and there’s no cross-partition overhead.

Multi-Company Optimization Strategy:

  1. Create Cross-Company Indexed Views: Build indexed views specifically designed for cross-company scenarios. In SQL Server, create a view that unions the time registration data with NOEXPAND hint:
CREATE VIEW TimeAttendanceCrossCompany WITH SCHEMABINDING AS
SELECT DataAreaId, Worker, RegistrationDate, Hours
FROM dbo.TimeRegistrationTrans
WITH (NOEXPAND)

  1. Implement Partitioning Strategy: Use table partitioning on TimeRegistrationTrans by month and company. This allows the query optimizer to eliminate entire partitions when date filters are applied.

  2. Optimize DAX Queries: Ensure your Power BI measures push company and date filters to the source. Use TREATAS or CALCULATETABLE to force filter context propagation:

MultiCompanyHours =
CALCULATETABLE(
    SUM(TimeReg[Hours]),
    FILTER(ALL(Company), Company[ID] IN {selected companies})
)

  1. Enable Query Folding: Verify that Power BI is actually folding your queries. In DirectQuery mode, check the native query to confirm filters are being passed to SQL Server rather than post-processing in Power BI.

  2. Statistics and Maintenance: Run UPDATE STATISTICS on all time attendance tables across all companies. Multi-company queries are extremely sensitive to outdated statistics because the optimizer needs accurate cardinality estimates for the UNION operations.

Alternative Approach: If real-time data isn’t critical for all reports, implement a hybrid model. Use Import mode for historical analysis (pre-aggregated cross-company data refreshed nightly) and keep DirectQuery only for today’s real-time attendance monitoring within single companies.

Expected Results: After implementing the cross-company indexed view and optimizing the DAX, our multi-company time attendance reports went from 70 seconds down to 12-15 seconds. Not as fast as single company, but acceptable for daily HR operations. The partitioning strategy gave us another 20% improvement by allowing partition elimination on date filters.

The key insight: indexed views present doesn’t mean they’re being used effectively in cross-company scenarios. You need views and query patterns specifically designed for multi-company operations.

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

I’ve seen this pattern before. The indexed views help with single company queries but don’t always optimize well for cross-company scenarios. Check if your Power BI is using DirectQuery or Import mode. DirectQuery will hit performance walls with cross-company joins much faster.

4

Multi-company queries in D365 can be tricky because they involve union operations across multiple partitions. Even with indexed views, the query optimizer might not choose the most efficient execution plan when dealing with cross-company joins. Have you looked at the actual execution plan in SQL Server to see where the bottleneck is? Often it’s in the cross-partition operations rather than the indexed view lookups themselves. You might also want to check if statistics are up to date across all company partitions.

6

We’re using DirectQuery mode because we need real-time data for attendance tracking. I’ll check the execution plans as suggested. The cross-partition theory makes sense given the performance gap.

7

Consider implementing aggregate tables specifically for multi-company reporting. We did this for our time attendance reports and saw dramatic improvements. Create a nightly batch job that pre-aggregates the data across companies into a separate reporting table. Yes, it’s not real-time, but for most HR analytics use cases, overnight refresh is acceptable. We went from 60+ second queries down to under 8 seconds for our cross-company attendance dashboards.

9

Another angle to consider: are you filtering by date ranges in your reports? Multi-company time attendance queries often pull much larger datasets than people realize. If your DAX isn’t properly pushing date filters down to the SQL layer, you might be retrieving months of data across all companies before filtering happens in Power BI. Add explicit date parameters and make sure they’re being used in the WHERE clause at the database level.