Power BI Direct Query with Impala

[Anonymous]

Currently, I am working on the PowerBI report where I need to show the granular data.  For this we are using the Direct query approach as the dataset size is quite huge (290 million records approx). Our data source is impala.

When I am opening the PBI file for the first time, it is crashing because it is trying to load all the data from source system. How do I tell my report to load the data only if the filter is selected? I have already tried ISFILTERED and HASONEVALUE in my DAX measures to allow showing the visual only when slicer is selected but it does not help.

Can someone please help me with the approach needed for this use case?

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

When you are using dq mode, the time that's needed to refresh the visualization depends on the performance of the underlying data source. When the data needed to service the request has recently been requested, Power BI Desktop uses recent data to reduce the required time to show the visualization. If you select Refresh from the Home ribbon, all visualizations are refreshed with current data.

Here are some tips to improve refresh time:

• You can delete unneeded columns and rows in PowerQuery, or filter unneeded data.
• You need to optimize the data model. It is recommended to use a star structure and not to use a many-to-many relationship.
• Avoid using functions such as summraize, and reduce the use of aggregation as much as possible.

Guidance:Using DirectQuery in Power BI - Power BI | Microsoft Docs

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs

Improve Power BI Performance by Optimizing DAX | MAQ Software

Hope it heps!

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-janeyg-msft - Thanks for your reply. The requirement from my customer is to show 60 columns in a single table on a single page that too a granular level of data and not aggregations. I have already got star schema, and pulling 60 columns only from the table that has approx 290 million records using direct query.

It looks like I need to work on optimising the performance of data source as you have said as we are using on-premise data lake as the data source.

Thanks for sharing the URLs as well. Cheers!

[amitchandak]

@Anonymous , do you want to create a parameter page, so user select filter first and use

refer to the page: https://www.youtube.com/watch?v=F3qMwQtWjvo

[Anonymous]

Thanks @amitchandak for your suggestion. We have a slightly different requirement in this report. 

We are using FilterByList custom visual as one of the slicers as shown below so that end users can pass the list of values separated by comma. 

This visual has a cross button to clear the filter selection. What happens is, on inital page load, we are passing a random value to the report so that it does not break using direct query, but if I click on this "X" icon, it clears the filter selection and crashes the report page. If we can somehow hide this icon, it will help.

We need the solution with this custom visual only as it is the only available visual where you can pass list of values separated by comma. Thanks for the help.