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
(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?
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?
For the combined key try to use this direct query column function.
Avoid bi-directional joins. Refer this link
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.
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.
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
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...
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.