How to use Aggregations and Dual Mode

[Datahungry]

Hello,

I am trying to build a report that combines Direct Query with Import Mode to improve performance and allow the scheduled refresh to be carried out for aggregated data only.

I was tried to do what is explained in the "Aggregations based on group-by columns" section on the following

https://docs.microsoft.com/en-us/power-bi/desktop-aggregations#aggregations-based-on-group-by-column...

,since my dataset lives on a SQL DB and is composed of one SalesData table with denormalized dimension values (ie. repeated dimension values) into it, rather than one fact table connected to dimension tables.

Could someone please explain this process step-by-setp?

Also some questions:

1) Is the aggregation table supposed to be created in SQL or PowerBI?

2) Is it necessary to create a table in the SQL DB to pre-aggregate the data?

Thanks for your help

[v-yuta-msft]

@Datahungry,

The aggregation table is generated by power bi and cached in power bi model. In addtion, based on my understanding, you don't need to pre-aggregate the table because the aggregation feature in power bi has cover this step. 

Three reasons for using aggregation table in power bi:

• Query performance over big data - as users interact with visuals on Power BI reports, DAX queries are submitted to the dataset. Boost query speeds by caching data at the aggregated level, using a fraction of the resources required at the detail level. Unlock big data in a way that would otherwise be impossible.
• Data refresh optimization - reduce cache sizes and refresh times by caching data at the aggregated level. Speed up the time to make data available for users.
• Achieve balanced architectures - allow the Power BI in-memory cache to handle aggregated queries, which it does effectively. Limit queries sent to the data source in DirectQuery mode, helping stay within concurrency limits. Queries that do get through tend to be filtered, transactional-level queries, which data warehouses and big-data systems normally handle well.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Datahungry]

Hi,  

Thanks for your reply.  Could you please explain what you mean by "The aggregation table is generated by power bi" and how this can be accomplished?

I understand the reasons for using aggregations, my dataset is actually rather large, several GB in size, so using DirectQuery the visuals on the report take between 12 and 15 seconds to refresh (sometimes longer). This makes the user experience terrible, since everytime filters or slicers are used the user has to wait too long before all filters are applied.

I don't want to upload the whole dataset to the PowerBI Service as it's too big and eventually the limit would be reached, so I am sure aggregations would solve my problem (given the data to upload would be a fraction of the original dataset).

Could you possibly explain how this can be done?

Thanks

[Datahungry · Accepted solution]

As it turns out, most answers about this topic can be found here:

https://docs.microsoft.com/en-us/power-bi/desktop-aggregations