Direct query - how prevent full recordset from loading and only return filtered sets?

[RBunting]

Our users would like to be able to use cross-report drillthru to review detail records from an enormous, 350 col wide, 900 million row table.  

Our 'header' report works well in import mode and we have created keys and other efficiencies so that records our our desired grain can be loaded quickly from the database.   We are close to a working solution.

However, the report cannot be effectively deployed -- the direct query attempts to bring back an outrageous amount of data because it always tries to load the full, unfiltetered dataset from SQL before there is a chance to filter the query according to the preferred drill-through parameters.

Has anyone managed to create leaf level reports over small sections of large tables?  What strategies did you use?  Of principal importance to our stakeholder is that the detail can be reached from a parent-level report so that users don't constantly have to reconfigure slicers/filters on reports over large datasets.

[ps1982]

Hey, appreciate this a few years old now, but did you ever get it to work? I'm just looking at a similar situation now.

[RBunting]

We prototyped a solution where the 'detail' report was kept in a parameterized paginated report that would run a restricted sql query against the main dataset.  The PowerBI report included links to the paginated report that included URL parameters.  It worked well for desktop users, but those URL parameters were not supported in the PowerBI mobile app, and the solution was abandoned.

The business group decided the problem wasn't suitable for PowerBI and took the work to a different technology.

[parry2k]

@RBunting you can also look at composite and aggregation method

[GTPowerBIUser]

This is pretty high level but you can use the following options:

1) Create a SQL query in advanced editor - click on edit queries, then advanced editor

Ex from one of my tables:

let
Source = Sql.Database("yourdatabase-sql.database.windows.net", "yourdatabase", [Query="SELECT * FROM yourtable AS t WHERE t.LOB = 'filterdataonthis'"]),
#"Removed Duplicates" = Table.Distinct(Source),
#"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Role From", "Role"}, {"Full Name", "Name"}, {"Employee ID Number", "Emp ID"}})
in
#"Renamed Columns"

2) Create a new summary table in Sql server - We had a similar issue with our time data, so I wrote a stored proc in SQL Server that created a new summary table - so that power bi would hit the new table and only download the data I needed to download

3) I don't suggest using direct query since it attempts to create a live link with your database, instead use import and then run refreshes at more optimal times (like overnight or every few hours)

4) Use incrmemental refresh to only update new or specific data