Lakehouse and Power BI Aggregations

[DennesTorres]

Hi,

I have two different situations:

First: Power BI connecting to the lakehouse using the SQL Endpoint. All the tables come from the SQL Endpoint. When making an aggregation, it works perfectly.

Second: Power BI connecting to the lakehouse using a local dataset. The direct query tables comes from this main connection. The aggregated table comes from a SQL Endpoint connection, using Import method.

On this second method, the aggregation configuration works pefectly, there is no problem in configuring the aggregation. However, when executing, the aggregation is completely ignored and the query goes directly to the tables configured with Direct Query, instead of using the Import aggregated table.

I don't know where to start investigating this. Why the aggregation works in one method and not in the other one? 

I suspect it may be related to query reduction but I don't know how to disable it and I'm not sure where to start.

Kind Regards,

Dennes

[v-nikhilan-msft]

Hi @DennesTorres ,
Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this.
We will update you once we hear back from them. Appreciate your patience.
Thanks

[v-nikhilan-msft · Accepted solution]

Hi @DennesTorres 
If you create a report in "live" mode, and then do "Make changes to this model", it is switching the report into a composite (aka proxy) dataset, to allow you to personalize/customize it.
That means you are going from:
  Report -> Core Dataset, to
  Report -> Proxy Dataset -> Core Dataset
Aggregations are not expected to work in the Proxy Dataset above, because the proxy dataset is running in SSO mode, and aggregations are likely to be dangerous in SSO mode. This is pretty standard behavior for datasets, so not really specific to Lakehouse.

[DennesTorres]

Hi,

What do you mean SSO mode? I missed the meaning of this, I don't think it's single-signOn.

Just to be sure weare talking about the same implementation, the proxy dataset is a composite one. The aggregated table comes from another connection, a SQL Endpoint connection.

If this doesn't work, it's just a bit strange it allows to configure the aggregation without any complain. 

Kind Regards,

Dennes

[v-nikhilan-msft]

Hi @DennesTorres ,
SSO here means Single Sign On. Essentially it means: the user's credential (who opens the report), flows automatically through the system to the underlying source datasets.
Datasets in Direct Query mode against another core dataset are always in SSO mode.

Hope this helps. Please let me know if you have any further questions. Glad to help.