Forum Discussion
Modelling big data query performance - how to improve?
Hello Guys,
this is my model:
The week point is a bridge table here because i have 68 M rows there.
And now showing some fields from Resources + MEasure to count rows from BridgeTable (Distinctcount rows) is saying that query limit was hit
My meaures (Counting together with Costs caused failures):
New_Resources Count =
DISTINCTCOUNT ('Dim_EA_Resource_Bridge_Tags'[ResourceIdKey])
New_AmortizedCosts = SUM(Fct_EA_AmortizedCosts[CostInBillingCurrency])
Questions:
1. Is this a usual thing if Power BI has too many records in bridge to cause issues like that?
2. What should be next step to model this correctly? What can i do?
3. Counting resources through the bridge table is very heavy if this has 68 M of rows. But this is the purpose to answer how many resources are attached to a specific tag...
my dax is:
// DAX Query
DEFINE
MEASURE 'MeasureTable'[New_Resources Count] =
(/* USER DAX BEGIN */
DISTINCTCOUNT ('Dim_EA_Resource_Bridge_Tags'[ResourceIdKey])
/* USER DAX END */)
VAR __DS0FilterTable =
TREATAS({"Apr"}, 'Dim_Date'[MonthShort])
VAR __DS0FilterTable2 =
TREATAS({"Product"}, 'Dim_EA_Resource_TagKeys'[Key])
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'Dim_EA_Resource_TagKeys'[Key],
'Dim_EA_Resource_TagKeys'[Value],
'Dim_EA_AmortizedCosts_Resources'[SubscriptionName],
'Dim_EA_AmortizedCosts_Resources'[ResourceName],
'Dim_EA_AmortizedCosts_Resources'[ResourceGroupDisplayName]
), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
__DS0FilterTable2,
"New_Resources Count", 'MeasureTable'[New_Resources Count]
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'Dim_EA_Resource_TagKeys'[Key],
1,
'Dim_EA_Resource_TagKeys'[Value],
1,
'Dim_EA_AmortizedCosts_Resources'[SubscriptionName],
1,
'Dim_EA_AmortizedCosts_Resources'[ResourceName],
1,
'Dim_EA_AmortizedCosts_Resources'[ResourceGroupDisplayName],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
'Dim_EA_Resource_TagKeys'[Key],
'Dim_EA_Resource_TagKeys'[Value],
'Dim_EA_AmortizedCosts_Resources'[SubscriptionName],
'Dim_EA_AmortizedCosts_Resources'[ResourceName],
'Dim_EA_AmortizedCosts_Resources'[ResourceGroupDisplayName]
Best,
Jacek
Hii jaryszek
The correct modeling fix is to pre-aggregate or reduce the bridge (e.g., create a tag-to-resource mapping table with unique pairs, or materialize the many-to-many logic in your ETL). Power BI is not optimized to run DISTINCTCOUNT over a 68M-row bridge on the fly. Reducing the bridge cardinality is the only reliable way to improve performance.
10 Replies
- rohit1991Super User
Hii jaryszek
The correct modeling fix is to pre-aggregate or reduce the bridge (e.g., create a tag-to-resource mapping table with unique pairs, or materialize the many-to-many logic in your ETL). Power BI is not optimized to run DISTINCTCOUNT over a 68M-row bridge on the fly. Reducing the bridge cardinality is the only reliable way to improve performance.
- jaryszekSuper User
Thanks,
how this table could look like and how relationships can be designed?
Best,
Jacek- rohit1991Super User
Hii jaryszek
create a slim mapping table that holds only the unique ResourceID–TagID pairs. This becomes your new bridge and connects with simple 1: relationships from Resources and from Tags. By reducing the table to distinct combinations only, Power BI no longer has to scan 68M rows for every DISTINCTCOUNT, and the performance issue disappears.