Query Resource Issues - SCD Type 2 Design on a Pro License

[ct_tm]

I am implenting an SCD Type 2 model for a housing company. I am rebuilding the data model from the original Type 0 design, but at a very early stage I am running into query resource limits and I just don't understand why and am not seeing a way around the limitations. So I am wondering if I am doing something wrong, or if the Pro license just doesn't offer enough resources to actually handle a Type 2 model.

Here is the model currently.

So, the point is to be able to see things historically down to the day in order to track managers and other attributes through time across the company/portfolios. But things break down quickly and I am trying to assess our options.

Here is an example of a measure that works at first, but runs out of resources if outside table filters are added.

Sum Excluded Units = 
CALCULATE(
    SUM(Occupancy[Excluded]),
    FILTER('Calendar', 'Calendar'[Date] = MAX('Calendar'[Date]))
)

Attempting to perform a simple sum of a point is fine even down to the day level, computes incredibly fast, but when I add a filter from the Property Lists table I run out of resources. Even if that list I add has only 1 property on it.

Am I doing something wrong in the design here, or have we just reached the upper limit of operating on a Pro license? 

It almost looks like I can answer my own question by flipping the report settings to Premium Capacity (from Shared) and everything works as intended with no resource issues, but I want to understand why so that I can attempt to mitigate it because this company is not going to go for the cost of Premium.

Thanks!

[FarhanJeelani]

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:

1. Simplify Filters: Use fewer or more direct filters. Avoid complex expressions within FILTER when possible, as they can slow down calculations.

2. 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.

3. 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.

4. Consider Using Variables: Break down complex measures with VAR to store intermediate results, which can simplify calculations and reduce memory load.

5. 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.

[ct_tm]

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?

[FarhanJeelani]

Hi @ct_tm, Can you share Pbix?

[ct_tm]

@FarhanJeelani 

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