cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dgreen Regular Visitor
Regular Visitor

What is the best way to use extremely large data sets to show summary visuals and have drill through

We have a situation where we we have tables that we can query to provide summary information for visuals such as pivots and charts, but then want to be able to have users get into the details if they want based on a certain line of business,  quarter, etc. Currently this is done with  the main query being used in excel, and then if a user double clicks on the pivot table it calls an SP that then returns the detailed information in a new workbook. We want to move this online though. Does PowerBi offer anything that could do something like this?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: What is the best way to use extremely large data sets to show summary visuals and have drill thr

No problem.  Perhaps this will help, it is a presentation from Microsoft at the Business Application Summit 2019 looking at aggregations over a 1 trillion row data set.

https://www.microsoft.com/en-us/businessapplicationssummit/video/BAS2018-2172

I have not done any work with the aggregations myself but this is along the lines of what you are looking for yes?

View solution in original post

5 REPLIES 5
Super User
Super User

Re: What is the best way to use extremely large data sets to show summary visuals and have drill thr

Hello @dgreen 

Yes, PowerBI has a drillthrough capability that allows you to set up a detailed report, say at the order line level with product, category, price, etc then you set that report as a drillthrough target and add the fields that will be getting applied as filters.

The fields you add as the drillthrough fields are the fields you will be summarizing by on your summary report, line of business, quarter, etc.  When a user is looking at the summary report they can right click on a visual to access the drillthrough feature. 

If they right clicked on a piece of a visual that was displaying Q1-2019 sales for the computers line of business, those two filters would be carried through to the drillthrough but you would be shown the detailed lines.

Here is an article from Microsoft about drillthrough: https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough

dgreen Regular Visitor
Regular Visitor

Re: What is the best way to use extremely large data sets to show summary visuals and have drill thr

Sorry, I should have specified that I am familiar with the regular drillthrough but the detailed data set is far too large to load all of it in at once. I would be best if the only information that was loaded was the information specified but selections from the summary. That is why our excel passes information to the SP so it only pulls back what is needed.

Super User
Super User

Re: What is the best way to use extremely large data sets to show summary visuals and have drill thr

No problem.  Perhaps this will help, it is a presentation from Microsoft at the Business Application Summit 2019 looking at aggregations over a 1 trillion row data set.

https://www.microsoft.com/en-us/businessapplicationssummit/video/BAS2018-2172

I have not done any work with the aggregations myself but this is along the lines of what you are looking for yes?

View solution in original post

Super User
Super User

Re: What is the best way to use extremely large data sets to show summary visuals and have drill thr

The only other thing I have heard of that sounds vaguely like what you are looking for is paginated reports now support parameters in the URL.  Not sure if this is more like what you need but I thought I would mention it.

https://powerbi.microsoft.com/en-us/blog/url-parameters-for-paginated-reports-are-now-available/

 

dgreen Regular Visitor
Regular Visitor

Re: What is the best way to use extremely large data sets to show summary visuals and have drill thr

Thank you for this. I've looked into it some more and it looks like it would work for our use case.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 364 members 3,421 guests
Please welcome our newest community members: