Power BI DirectQuery and SQL Databases: A Conversation with Bob Ward
Вставка
- Опубліковано 4 лип 2024
- Power BI DirectQuery can have performance implications. Bob Ward shares his knowledge to help make SQL Databases perform better!
Automatic tuning: docs.microsoft.com/azure/sql-...
ColumnStore Indexes: docs.microsoft.com/azure/sql-...
Forced parameterization: docs.microsoft.com/sql/relati...
Intelligent Query Processing: docs.microsoft.com/sql/relati...
PolyBase: docs.microsoft.com/sql/relati...
Connect with Bob Ward: / bobwardms
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #SQLDatabase #GuyInACube - Наука та технологія
One tip - if using a DW where referential integrity is assured, make sure you turn that option on in the PBI relationship. It’ll help the generated sql come out with inner joins rather than outer.
Great video!
Scott
I have an SQL db with 500 Mn row items. How do you suggest creating a connection between PowerBI and Database to improve the performance? Currently, it takes 15 mins to create a visual from the direct query table.
Thanks so much for this video! I’m definitely going to try Polybase now. One thing our team does (we ONLY use direct query) is make views so that Power BI only queries the columns we need to retrieve. For us, It’s a way of increasing performance and security at the same time. Thanks again!
Bob mentioned that they have done some optimizations for Polybase. Just be sure to review the limitations and definitely test your performance.
@SQL Devdba , How much difference are we talking about when using views? If i have a table with 1.5 million rows and 20 columns and Power BI only requires 14 columns ,should I create a view of the same?
will the data lake on Azure for example eliminate the issue of fetching data from Productions DBs?
I still believe that dimensional modeling, the right data types and aggregate tables are the best way to ensure the best performance for direct query in Power BI.
Thank you very much for the wealth of information here. Very helpful! My question: Is it possible to create a SQL read replication database on “on-premise” server?
Hi, where I can find the option in Power BI to add the scroll-down bar in the tab so I can make the tab bigger and add more charts? I prefer this way rather than having many multiple tabs as I usually do. Thank you
you need to change the page size to custom
Hey Adam !
Actually I am using SQL server express edition with RDS instance on AWS to use direct query . But what happens is my visual usually give error of memory error or time out error or resource governing error . When I removed all calculated columns and measures from model then it worked for some more data but then again , it gave memory error. Please suggest some thing or point me to a direction I am really confused and tensed about same issue from long time !
I hope you reply soon !
Many of our users are hitting the 1000000 row limit when creating a visual from a DQ source table.
As mentioned in the video. The visual creates DAX then DAX is translated to SQL. In the error cases it creates several SQL queries ond being unexpected and hitting the limit.
1. Can I control what SQL is created?
2. How can i avoid the ”extra” SQL query that is causing the error?
This video is awesome.. I have got more insights.. Thanks both of you :)
I have two questions..
Question 1. Is SQL server Express/standard doesnt support DirectQuery Storage mode?
Reference : www.microsoft.com/en-us/sql-server/sql-server-2017-editions
Question 2:
I have created views and created Column store index for those. I am using those views in PowerBI.
I havent created index on the tables. Do I have to create Index on the table as well to get better Performance?
Thanks guys for this videos, question how you were able to scroll down and has a such long report. How to trigger that.?
Set you page height to something like 1500 and go to the view tab. Set Page view to 'Fit to width"
@@Mrpronos hey ,,i am new to Power BI ..want to know how to stored procedure having multiple parameters in POwer BI .?? and also how to embed power bi reports in My Application?...need help
how do you make you convex scroll down?
great content, thanks
Is it possible to call a stored procedure under Power BI DirectQuery? I'm really interested in finding a solution to the "Incorrect syntax near keyword 'exec'" error under DirectQuery. Also, it appears that Power BI sees all SQL objects as views.
@21:07 you mention Patrick's videos on jumping from aggregates to direct query. I assume these are burried in one of your courses. Have you guys done a UA-cam chating about the basic practicalities? I use PBI to summaries operational data, find problem areas but but need something like direct query to delve into logging of a specific instance of a problem. P.S. Keep it coming, loving the passion.
BAH. forgot to link up the playlist. Here is the Aggs Playlist that Patrick did - not part of a course. Just on the UA-cam channel :) guyinacu.be/aggseries
It would be great if you could do a video on dynamic queries via Row-Level-Security (RLS) using Direct Query against SQL Server. If a particular user logs into PowerBI service, i want to be able to use RLS and dynamically pass in the UserPrincipalName (which I realize is DAX). I tried generating an M-query dynamically and stringing in a PowerBI variable, but I have not been able to figure out how to dynamically change a PowerBI parameter based on the user that is logged in. In summary, how can I do Direct Query and only retrieve rows for the logged in user?
Yup this would be DAX using the USERPRINCIPALNAME() function. The result is a WHERE clause predicate in the underlying T-SQL. RLS can't be done with Power Query directly. It's either done in the data model, or you are just passing the user back to the data source and letting the data source do that (Single Sign On)
@@GuyInACube - that is where I'm lost - I cannot figure out a way to 'pass' the current logged in userid (USERPRINCIPALNAME) back to the data source via a direct query in order to filter at the data source - is there some documentation on how that is done
How do you do storage procedures with Power BI?
Karen Estrada I don’t believe this is possible, we use views to limit the queries on our side, but if you haven’t yet try column store indexes. They increase performance by up to 90%.
So how long does it take to load report w 80 queries??
For the demo I used, it was the lowest SKU of Azure SQL Database and it finished after about 2 minutes. If you had a higher SKU it would go much faster. It actually was a small database. Although the point of showing that, wasn't the speed, but rather the amount of queries generated.
How would you submit a stored proc from Power BI without getting the "Incorrect syntax error"? Is there a reference for this? How would I submit a SP from Power BI DirectQuery? How???
very valuable
That's what BI Architects are for. Create a separate schema for reporting views and smaller transformation datasets.
YES! 👊
Hey guys, where do I find detailed examples on OAuth2.0 based custom connectors?
The samples have one. Not sure if you've looked at that - github.com/Microsoft/DataConnectors
@@GuyInACube I did but that didn't help..
@@GuyInACube I keep getting 400 error this is what I tried in query logic
@@GuyInACube et
url = "api.example.com/auth/v2.0/oauth/token",
body = "{ ""granttype"": ""password"", ""username"": ""user"", ""password"": ""pass"", ""tenant_id"": ""tenant""}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"], Content = Text.ToBinary(BuildQueryString) ] ))
in
Source
Does anyone use PowerBI against a PRIVATE BigQuery? Our company will not allow BigQuery to be public, and I am not sure how to connect PowerBI to BigQuery in this case?
Thanks
Most welcome 👊
4:20 what!!!! 😱 How that's possible?? Guy you need to explain how to extend the canvas in that way. 😲😲
Set you page height to something like 1500 and go to the view tab. Set Page view to 'Fit to width"
How come the report canvas is so big 🙆♀️
I was wondering the same thing
It was for effect in the video :) but, i've seen some customers that have done that. Glad you caught that. 👊
There is no denying that azure synapse is powerful and its good that Microsoft is working to make it the tool of tools, especially with the new studio experience coming soon with it. But will it be worth the 💰. Its fairly expensive to run.
It is a tool in your toolbelt. A lot of what we were talking about was just SQL Server or Azure SQL Database, not necessarily Synapse. The Materialized View comment I made was probably the only Synapse specific thing mentioned in the video.
@@GuyInACube didn't mean to troll. The rest of the video was really good. Direct query is great for datasets that are far too big and its right to dig down into how many queries are hitting the server.
I'm a first watcher... |o|
👊