DirectQuery Connection in Power BI How does it work Limitations and Advantages
Vložit
- čas přidán 28. 09. 2019
- In the last post, you learned about Import Data or Scheduled Refresh as a connection type. In this video, you’ll learn about the second type of connection named; DirectQuery. This type of connection is only supported by a limited number of data sources, and mainly targets systems with a huge amount of data. DirectQuery is different from another type of connection which I’ll talk about it in the next post named Live Connection.
Read the blog article related to this video here:
radacad.com/directquery-conne...
Other Types of Connection:
Import Data:
radacad.com/connection-types-...
Live Connection:
radacad.com/live-connection-w...
Composite Model:
radacad.com/composite-model-d... - Věda a technologie
Hi Reza! Simple and clear about quite complex things. Thanks a lot!
Thank you very much for the video ! you're explaining things very well, I liked the way you simplify by giving real time examples
Thanks in a million! Very well explained. This is the nth time that I am watching this again. Great content. Awesome. I couldn't find this explanation--simply put anywhere else. “Great teachers are hard to find”. Grade: A++ 💥
Excellent explanation..!! Well done!!
This has been so helpful. Thank you
Nicely explained!
Thank you, Raza, for this awesome video! DQ is the direction that I will be going with because of the size of our dataset. One question that I do have, and this is question that Ilse Espino Barros already asked, is refreshing a data from a DQ to a SQL database and Excel file.
Many thanks!
oP
When you use DQ, there won't be any scheduled refresh. the data is fresh anytime report is refreshed, or you select a slicer or anything that normally triggers a query sent to the database.
For the dashboard however, because there is no interaction, there is a automatic refresh of the visuals every 15 minutes (or you can change it to up to 1 hour if you want)
Excellent thanks
Very helpful
well explained
thank you sir
Best Explaination
👊👊
Is there a way to view the data in the tables while using Direct Query? I have to connect to an unfamiliar database using Direct Query. Any tips are greatly appreciated.
Hello, Thanks for your tutorial. How can I see real-time data from Cassandra? Will direct query work? if yes how? if not what are other ways that I can get real-time data from Cassandra to BI.
Hello good day, I have a problem, I use live connection to build the reports and power bi embedded to visualize them, when I am going to export the data to excel xlsx it does not export completely, I mean , the count is 90.000 and the rows exported are 59.000, it is a kind of restriction due to the live connection? Thank you so much!
Do composite model's many to many relationships create duplicates?
Hi Raza. Thanks for the video , do you know how can i prevent Powerbi to load initial data? i mean when user open report nothing show , until they click on select any filer or click on apply filter button ?
Is there any way to call a stored procedure that has no parameters from Power BI in DirectQuery?
Hi Raza,
I am trying to connect multiple SAP BW queries in the same time in direct query mode. Normally I am using import query (with no problem), but this time I need the direct query mode because of the data size - millions of lines.
The problem is, that if I choose direct query mode with SAP BW connection, BI doesn't let me to connect to a second query in direct mode. Do you know if it is a limitation on BI side, or is it a kind of gateway limitation from SAP BW side? I hope it's only a bad method or settings problem.
Hi , is it possible to have direct query with an ODBC connection to Aurora Postgress database hosted on AWS?
Here you can find all the data sources supporting DQ: docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources
Hi Raza...I am trying to connect Azure SQL DW in Direct Query mode with SSDT and get this error - Failed to save modifications to the server. Datasource has authentication kind OAuth2. This is not supported in DirectQuery 1400 mode.
I read in the MS docs that Direct Query mode is not supported with OAuth credentials.
Need help.
Does Power BI allow us to create a shared dataset using Direct Query? I want to avoid the hassle of selecting Tables and creating relationships for every report.
Hi Radacad, Thnx for the video! How do you create date hierarchy for date columns when you import SQL data by direct query mode? In import mode the columns have automatically the right hierarchy, in diect query mode they haven't. Do you have a video that shows how to do that?
Hi Sofie, can you tell me if you solved this issue, please? I'm with the same problem.
Hi i am getting the below error in advance query option please help me resolve this
"this query contains transformations that can't be used for directquery."
Hi Raza, Thanks for this video! Can we say direct query is best for real time data, with less number of well managed(column store indexed) records ?
not for real-time data. real-time data needs a data PUSH scenario, which uses streaming datasets.
DirectQuery is for scenarios with HUGE tables, trillions of rows for example.
@@RADACAD what do you suggest for live data ?
Do you have any videos based on live data?
How to write SQL query & where? I dont see any query we can right in power bi like tableau
Thanks Reza , can we add these as well ?
Pro- real time reporting
Con- 1M rows only
It won't be real-time reporting. real-time is a totally different story. real-time is about when the change happens in the data source, Power BI dashboard automatically shows it, which is not possible at the moment with DirectQuery. You have to use Streaming datasets for that, similar to this demo I showed: radacad.com/monitor-real-time-data-with-power-bi-dashboards
It can be done through REST API, or Azure Streaming analytics or PubNub.
And for your second comment: 1M rows is not the amount of the data shown in the report. With DQ you can have unlimited dataset size. However, if one query fetches more than 1M rows of the data it won't be possible. That means you have a table or matrix visual which is showing more than 1M rows of data. even if such a thing is possible, it is the worst type of visualization anyways. visualization should be always filtered. there is no point of showing 1M rows in the table when you can only see 20 rows in the page and have to scroll each time to see rows under that. to get to the 1M row you have to scroll down a LOT.
@@RADACAD Hi Reza, I am a new fan of yours. Especially your Date-Dimension creation tutorial was amazing and very informative.
I am very confused about this particular statement
" 1 - Million Rows" and would appreciate some clarity. On Microsoft website it says,
"There's a one-million-row limit for returning data when using DirectQuery, unless using a Premium capacity. The limit """ doesn't affect aggregations or calculations used to create the dataset returned using DirectQuery.""" It only affects the rows returned. Premium capacities can set maximum row limits, as described in this post.
For example, you can aggregate 10 million rows with your query that runs on the data source. The query accurately returns the results of that aggregation to Power BI using DirectQuery IF """ the returned Power BI data is less than 1 million rows. If over 1 million rows are returned from DirectQuery, Power BI returns an error (unless in Premium capacity, and the row count is under the admin-set limit)."""
So if the source data has more then 1 million row and I perform an aggregation, it will give me an error if not on Premium?
Hi Raza, thank you for your video! I have a question, if I have a report with a Direct Query in SQL and another table coming from Excel, how does the refresh work? I tried to make a change in my Excel file but the report did not seem to update that.
This is something that I would like to know as well. I also have a DQ to a SQL database and will be needed to get data from an Excel file that holds data that is not available in the SQL database.
If you have part of the data DQ (such as SQL Server database data), and part of it imported (such as Excel data), then your connection is Composite Mode. Meaning it uses DirectQuery for some tables, and Import for some others. In a case like that, your Import tables will be dependent on the scheduled refresh of the dataset to get refreshed, and the frequency of their refresh will be determined in the scheduled refresh of the dataset.
@@RADACAD thank you!
Hello Reza, thank you for the video. I find that when I enable DIrectQuery in SSDT from the existing 'Import Data' mode, if the tables have relationships, then I am not able to enable DirectQuery. If I delete relationships between tables, then I am able to enable DirectQuery. Do you know why it is so? Can I have active relationships before I enable DirectQuery?
What do you mean by "enable DirectQuery from the existing Import Data mode"? Is it Import Data or DirectQuery?
@@RADACAD Currently, I have imported data in SSDT with table relationships. When I change the DirecQuery mode to 'ON' it doesnt work, with no error messages. If I delete the relationship, then it works. Why is that?
@@NeverendingFootstepss I am not sure if composite mode is available in SSAS using SSDT editor. This is a Power BI feature
I love the content. very helpful thank you. but it sounds like you have a cold which makes it so hard to listen to your voice.
Hi Radacad,
When i am importing data from datamart with direct query getting below error. Could you please help me on this.
this query contains transformations that can't be used for directquery
So, can we say that, in a Direct Query, if the 'Query folding' doesn't happen then the visuals will show an error.
In direct query mode: query folding will happen for sure. There is no in memory processing. If a set of power query transformations cannot be folded into SQL statement, it will come up with the error in the query editor itself and won't allow you to close an apply until resolving it.
Hello Radacad, can i make MEASURES in Direct Query mode?
Yes, you can. However, sometimes complex measures might result in big, slow, and complex queries to the source database.
Can parameters used in DirectQuery?
no
Did I hear you right... powerBI report server is limited to 2 GB? Seems rather strange
the PBIX file size yes. if not using Import, then that won't be an issue.
Unfortunatelly direct query is super slow whe you have some calculations :(
Yes, DirectQuery is the method you should use as the last option, only if other options doesn't work. You can also combine DirectQuery with Import data using aggregations and composite model to speed up things much faster.
@@RADACAD thank you for your answer:)
Much appreciated!
I tried to connect SAAS via live version default mode in SAAS direct query but also was super slow, but with SAAS default mode import was good but when I changed the data in SQL did not change in SAAS :/
@@jujubalismel Import is always the fastest option. If you use SSAS live connection, you should make sure the connection and server are responding fast too.