Forum Discussion
Query Resource Issues - SCD Type 2 Design on a Pro License
In Power BI with a Pro license, complex SCD Type 2 designs can quickly hit resource limits, especially on filtered historical data. Here are some strategies to help optimize performance within the Pro license constraints:
Simplify Filters: Use fewer or more direct filters. Avoid complex expressions within FILTER when possible, as they can slow down calculations.
Aggregate to Higher Granularity: If daily granularity isn’t essential for all data, consider aggregating certain tables (like occupancy data) at a higher level (e.g., monthly or quarterly) to reduce the data volume.
Optimize Measures: In your measure, try to avoid using MAX on dates. Instead, filter by date ranges or leverage relative date filters, which are less resource-intensive.
Consider Using Variables: Break down complex measures with VAR to store intermediate results, which can simplify calculations and reduce memory load.
Incremental Refresh: For large tables with historical records, set up incremental refresh to keep only relevant slices of data, if possible, to reduce the processing load.
These adjustments can help make your model more manageable on the Pro license, even with SCD Type 2.
Thanks FarhanJeelani for your reply, it helps serve as a checkdown for optimization.
My issue is that I have stripped this model down to barebones already. And it does need to keep that daily granularity for everything except finances because those are being aggregated Month level.
In this scenario I am using a measure that itself is pretty stripped down. It just sums point-in-time column values and is running out of resources when filtered by another table that itself is already stripped down to 3 columns (listID, listName, propID) with less than 20k rows. There are no bi-directional relationships (there is the 1:1 between Occupancy and Historical Attributes but it failed the same when forced to be 1:N Attributes filtering Occupancy.
I'm just struggling to see how this is using over a gig of memory to compute since it seems so basic. If I am running out of resources with such a basic measure, how will I possibly be able to perform the more complex analytics without upgrading this license?
Hi ct_tm, Can you share Pbix?
I removed pretty much everything that would be company identifyable. Of course, doing this helped some things work that were running out of resources before. Most of what I removed were text fields from dimension tables. But, I can still generate a few resource errors if I filter enough things.
stripped out PBIX file: Operations Sample