Forum Discussion
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
Hi MCacc
1. The relational database source can be optimized in several ways, as described in the following bulleted list.
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:
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/809831#M389380
Thank you very much for the great info, I will try to follow the suggestions in the links.
- 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.
I would suggest you use this method for large scale data models.
Create a table of the information that you need and aggregate it - for performance.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?
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.
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.
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
