Forum Discussion

jaryszek's avatar
jaryszek
Super User
8 months ago
Solved

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

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

    • jaryszek's avatar
      jaryszek
      Super User

      Thanks,

      how this table could look like and how relationships can be designed?

      Best,
      Jacek

      • rohit1991's avatar
        rohit1991
        Super 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.