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
Optrix
New Member

Accessing Filters (particularly date filters) to improve PowerBI Custom Connector performance

Quick Summary:

Is it possible to read the query filters - particularly date filters - from a PowerBI Custom connector?

 

Long Version:

I'm trying to write a Custom Connector for PowerBI, and while I've got an idea of how to proceed, I'd like to first double-check that there isn't an easier way to do what I'm attempting.


My database has hundreds of millions of time-series records over several years on slowish servers. As such, I can't simply blurt all of them out to the user - the traffic would be immense, particularly when using it via the Gateway Service.

I'd like to give the user access to the most recent information if they don't have a time filter on their report, or all of the information from the appropriate time range if they have included a time filter.

The catch is that the only way it seems I can get my hands on the filter is if I override Table.View and build an almost complete DirectQuery data source.

If I was to follow this documentation - https://docs.microsoft.com/en-us/power-query/handlingqueryfolding (which by the way I needed StackOverflow to find...no idea why it's not linked to from the actual TableView documentation) - it indicates that the only way to get the filters might be to override TableView.GetExpression to properly filter the data?

This also means I've got to override TableView.OnInvoke to let PowerBI know what functions I'm capable of dealing with - a function which is totally undocumented, so I've got very little idea of what I'm supposed to return.

I'm assuming that I'm simply missing something. I would have assumed that limiting your returned results based on a time range would have been one of the most elementary features of PowerBI connectors, and I'm a little startled to find out that the process is so difficult. 

2 REPLIES 2
lbendlin
Super User
Super User

Fascinating.  I think philosophically the issue is that Microsoft were not thinking the way you are thinking when they designed the original version of Power BI as a strict one way street (in terms of data flow).  Now they are scrambling to accommodate the new customer requirements for timeseries based reporting as well as data source write-back. 

 

Really looking forward to what you find out. Best of luck. @ImkeF  - fyi

But controlling your date range is so fundamental to reporting, I've got no idea how the system could have been designed without this in mind.

While not ALL reports reflect date ranges (many dashboards only show the latest values), the point of the majority of all reporting is to summarise the activities across a date range. The idea that you should query all of recorded history for a report that only covers the last 24 hours is...not very clever.

I know I'm worst-case-scenario for this sort of thing (1-second resolution data over several years of recording), but surely this limitation is causing headaches for a large number of users, particularly if you're only limited to 16MB of data from a Gateway connector. 

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.