Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Datahungry
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

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
v-yuta-msft
Community Support
Community Support

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

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

 

 

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

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.