cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Using Direct Query my pbix is extremely slow

Hello, 

 

I'm using Direct Query import mode to connect my dashboard to Oracle SQL DB. 

 

I don't have any problem when I launch queries directly on my DB in the sql Editor. The queries work well and super fast. 

 

The problem is that my pbix is extremely slow. I thought it was the high amount of rows uploaded (16 million), but things didn't change after I have uploaded 355 rows using a where condition from my DB. In both scenarios, my pbix is VERY VERY slow. I'm talking about 1-2 minutes to upload a slicer and 3-minutes to upload a line chart.

 

I don't think it's normal. 

 

I thought the issue could be because I used a concatenation of fields to create my table key to be used in my pbix in the direct query.  

 

In direct query I did something like this:

SELECT field_1  ||   '_'   ||  field_2   ||   '_' ||   field_3 as table_key
field_4,
field_5,
field_6,
FROM table

(I thought it could be the || operator, but it gives me the same problem when I use CONCAT)

 

I read somewhere that fields concatenation can be an issue when in direct query mode. 

 

Do you guys have any idea?

 

Thank you

 

 

9 REPLIES 9
Highlighted

Re: Using Direct Query my pbix is extremely slow

It would help if you could clarify a few things, as it seems you may be confusing a few concepts. There are 2 modes for power BI. Direct Query and Import Mode. If you are using direct query, the data is not loaded into power BI tables - all queries are sent to the source DB. If you are loading data into power BI, then it is import mode. 

 

You then say you are uploading slicers and line charts. What do you mean?  Are you saying it is taking a long time for those visuals to display in your report?

 

You say your report is very slow. What is your database design (assuming import mode)?  Have you built a star schema following dimensional modeling principles?  Or have you just imported your Oracle DB?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Super User IV
Super User IV

Re: Using Direct Query my pbix is extremely slow

For the combined key try to use this direct query column function.

https://docs.microsoft.com/en-us/dax/combinevalues-function-dax

 

Avoid bi-directional joins. Refer this link

https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

https://www.youtube.com/watch?v=4kVw0eaz5Ws

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Using Direct Query my pbix is extremely slow

Hi  MattAllington,

 

I'm using direct query mode to upload the schema, not the data, of one table which contains all my info. In my DB, it can be considered as a giant fact table with no other tables to be put in relation with in my pbix.

Consider this, I didn't specifically make this table, but I know it's a left join between two tables. 

 

And yes, it's taking a long time for those visuals to display in my report.

 

 

 

Highlighted

Re: Using Direct Query my pbix is extremely slow

If you are using direct query mode, then query performance is managed by the source DB, not Power BI. You will need to have a dba anaylse the queries being sent to the DB and optimise the DB to support the queries. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Frequent Visitor

Re: Using Direct Query my pbix is extremely slow

I would suggest you use this method for large scale data models.

Clicky Clicky draggy droopy 


Create a table of the information that you need and aggregate it - for performance.

Highlighted
Community Support
Community Support

Re: Using Direct Query my pbix is extremely slow

Hi @MCacc 

 

1. The relational database source can be optimized in several ways, as described in the following bulleted list.

https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance#optimize-data-source-p... 

2. A DirectQuery model can be optimized in many ways, as described in the following bulleted list

https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance#optimize-model-design 

3. Optimize the report design:

https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance#optimize-report-design... 

4. The benefits of Import and DirectQuery models can be combined into a single model by configuring the storage mode of the model tables. The table storage mode can be Import or DirectQuery, or both, known as Dual. When a model contains tables with different storage modes, it is known as a Composite model. For more information, see Use composite models in Power BI Desktop.

There are many functional and performance enhancements that can be achieved by converting a DirectQuery model to a Composite model. A Composite model can integrate more than one DirectQuery source, and it can also include aggregations. Aggregation tables can be added to DirectQuery tables to import a summarized representation of the table. They can achieve dramatic performance enhancements when visuals query higher-level aggregates. For more information, see Aggregations in Power BI Desktop.

 

Alternative, you can use the performance analyzer to exam report element performance https://docs.microsoft.com/en-us/power-bi/desktop-performance-analyzer Or the dax studio: https://community.powerbi.com/t5/Desktop/VAR-and-FILTERS-combination-to-make-an-efficient-column/m-p... 

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted
Helper I
Helper I

Re: Using Direct Query my pbix is extremely slow

Thank you very much for the great info, I will try to follow the suggestions in the links. 

 

 

Highlighted
Helper I
Helper I

Re: Using Direct Query my pbix is extremely slow

What the MVPs fail to mention is that Microsofts connector for Oracle is quite frankly speaking, garbage. The default vaules, which are not configurable by the user when using this connector, are the main source of this pain.

If you don't mind importing your model, you can utilize extended connection string properties in combination with Ole DB to increase your througput. But this will sacrifice your ability to utilize DirectQuery.
Highlighted
Helper I
Helper I

Re: Using Direct Query my pbix is extremely slow

Thank you, this is the problem, unfortunately... I don't see any solutions if not trying to improve dba performance 

 

Thank you very much for your help

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors