By Mary Branscombe – The goal of Power BI (and any business intelligence tool) is to replace the hunches and opinions businesses use to make decisions with facts based on data. That means the insights in that data have to be available quickly, so you can pull up a report while people are still discussing what it covers, not five minutes later when everyone has already made up their mind. To make that happen even with large data sets, wherever they’re stored, Microsoft now uses machine learning to tune how the data gets accessed.

When you have enough data to make decisions with, you need to consolidate and summarize it, while still keeping the original dimensions—so you can look at total sales combined across all departments and get an overview but then slice it by region or month to compare trends. Most Power BI users need these aggregated queries, CTO of Microsoft Analytics Amir Netz told TechRepublic.

“They don’t care about the individual tickets on the plane or the orders in the supermarket; they want to slice and dice data at an aggregated level.”

Those aggregated queries need to scan a lot of data but what they produce is very condensed, he explained. “I can scan 250 billion rows of data if I ask for sales by month by geography; the results, even though it has 250 billion rows underneath, sales by month by geography will have maybe 1,000 rows in it. So it’s a huge reduction in volume.”

If the data getting aggregated is billions of rows, you probably want to leave it in your data warehouse rather than copying it into Power BI, but that can make query performance much slower as you wait for the data to be queried, loaded and aggregated. Querying and aggregating 3 billion rows in 30 seconds might not seem long, but you have that delay every time you change how you want to slice the data. “That’s going to get on the user’s nerves; waiting 30 seconds for every click is very disruptive.” Read On: