cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Datahungry Frequent Visitor
Frequent Visitor

How to use Aggregations and Dual Mode

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Datahungry Frequent Visitor
Frequent Visitor

Re: How to use Aggregations and Dual Mode

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

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

 

 

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: How to use Aggregations and Dual Mode

@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 Frequent Visitor
Frequent Visitor

Re: How to use Aggregations and Dual Mode

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

 

 

Highlighted
Datahungry Frequent Visitor
Frequent Visitor

Re: How to use Aggregations and Dual Mode

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

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

 

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors