HomeНаука и техникаRelated VideosMore From: Guy in a Cube

Walkthrough: SQL Server DirectQuery with Power BI

264 ratings | 42550 views
I walk through using a DirectQuery connection with SQL Server and the Power BI service. I start with Power BI Desktop, show how to configure the enterprise gateway, and then show how it looks in the Power BI service. SUBSCRIBE! https://www.youtube.com/channel/UCFp1vaKzpfvoGai0vE5VJ0w?sub_confirmation=1 LET'S CONNECT! Guy in a Cube -- http://twitter.com/guyinacube -- http://www.facebook.com/guyinacube -- http://aka.ms/guyinacube (YouTube) -- https://beme.com/guyinacube -- Snapchat - guyinacube -- https://www.instagram.com/guyinacube/ Adam Saxton (Microsoft Employee) -- http://twitter.com/awsaxton -- https://www.facebook.com/asaxton
Html code for embedding videos on your blog
Text Comments (110)
arunreddy gajja;a (7 days ago)
Hi are we connecting database from same machine from another machine
Shweta C (2 months ago)
Hi, I have a question. How can I connect to SQL Server on AWS EC2 instance from Power bi desktop?
Guy in a Cube (2 months ago)
You should have a connection string for that server. The server name would be either an IP address or fully qualified name, similar if you were doing it in Azure. Are you able to connect to it using something like Management Studio from your computer? If so, use the same server name with Power BI Desktop. Hope that helps!
guda krishna (2 months ago)
Hi How to connect SAP ECC?
guda krishna (2 months ago)
Thank you for your Reply. I'm trying , unfortunatly unable to findout the ODBC driver. If any otherway please let me know. Thanks in advance. Regards Venkat
Guy in a Cube (2 months ago)
Currently Power BI only has connectors for SAP HANA and SAP BW. Outside of that, you would need to see if there is an ODBC driver available for that.
Jayaprakash Tatagari (3 months ago)
Adam it's helpful thanks for your video.
Ramakrishna Bandam (3 months ago)
How to join two tables with custom query, with examples please
Marcos Leite (5 months ago)
Great video Adam. Thanks a lot. New subscriber. Here is my question: I have developed a MS-SQL database for many different companies. They all have the exact same database, but obviously, their SQL Server names are different. I want to create a PowerBI report and send it to them. From what you are showing, it seems that, because the SQL Server names are different (than the Server name I am using on my desktop), it will not work when they open this PowerBI report on their site. This makes no sense. I can create a PowerApp application using the same premise and export the app to my clients. When they import, it asks for the MS-SQL database information they are using on their site. As long as it is the same database, the program works beautifully. Why is PowerBI different? Why can't I export a PowerBI report and, when the user import on their end, just ask what is the server name of the SQL they want to attach to? I don't even care if the database name has to be the same. But it seems to make no sense to bundle the MS-SQL entire server information into a created PowerBI report. How could I work around this problem? Thanks! And keep up the great work!
becekkkkk (6 months ago)
Hi, do you know how to change datasource from import to direct query ? is it possible ?
Osaid Haşmi (7 months ago)
Hi, I am trying to fetch data from a "view" in my SQL server, but unable to do so. It gives me this error: The data source can't be accessed by a data gateway. Couldn't load the model schema. Can it be because I am using "view" as my data source and not a "table"? Many thanks!
Kamaizziati Arkmaz (7 months ago)
Hi Adam, I do direct query on my local desktop not in server desktop but failed to connect the database. Do we need to connect direct query only on server desktop?
Guy in a Cube (7 months ago)
It shouldn't matter. I've had it working on a machine running Windows 10. Chances are there is either some connectivity issue happening, or the data source drivers/providers aren't configured properly on the gateway/Power BI Desktop machine. For SQL Server, there is nothing you need to do from a driver perspective, as we just use the .NET SQLClient libraries. Typically, I recommend testing connectivity outside of the gateway or Power BI Desktop to make sure that works. For SQL Server, you can use Management Studio, or a UDL file. If that doesn't work, you have a bigger issue and it isn't Power BI related.
mohammed ALI (7 months ago)
Hi Nathan, please help me When getting data from SQL DB, it prompts "The data source you're trying to access doesn't support encryption. To access this data source using an unencrypted connection, click OK".". What does this message mean?
Guy in a Cube (7 months ago)
It means your SQL Server isn't set up for SSL connections. This is the default SQL Server configuration. It is an informational message. It won't prevent you from getting data. Just know that it isn't being done with an encrypted connection between Power BI Desktop / On-Premises Data Gateway and the SQL Server. Again, this is the default configuration for SQL Server. You would need to manually configure SSL encryption for SQL connections on the SQL Server if you wanted to use that.
Jeff Kaemmerling (7 months ago)
Adam, I'm dealing with a SQL server that's over 25 million rows and I keep running into errors where Power BI either can't show the visualizations or it runs out of memory. I'm only access one table(and it's a SQL view), I'm not doing any fancy calculations. I'm using Gateway of course and everything appears to be working, but I must be doing something wrong,
Guy in a Cube (7 months ago)
I would need to look more at how it was implemented. You may need to find a way to limit the resultset coming back for the visual, filtering or otherwise. Pulling that much data back for a single visualization is not really optimal.
Leonardo Gargano (8 months ago)
Hi Adam Thanks for your video. Please, do you have some tutorial about add a new datasource on PBI gateway using db2 database, with ODBC? Thanks!
Guy in a Cube (7 months ago)
I don't specifically for that. What problem are you hitting? You would need to make sure the DB2 ODBC Driver is installed (and make sure it is 64bit. Then provide the ODBC connection string.
sudeesh varier (8 months ago)
Is there something different to be done to connect to a sybase database? Especially when am inside a company network?
Nawly Booger (8 months ago)
Hi mate...... Due to security reasons my company will only allow to use direct query and i am unable to find where to write dax in DQ. When i am trying to use add column it’s giving some error message.
Saurabh Gandhi (11 months ago)
I am able to use direct query if I use Power BI desktop but I am not able to load data if I use power BI DEsktop from Citrix . Is there any enable/disable feature that I can check or any other tools or drivers need to install on Citrix for SQL server and Oracle ?
Ahmad Hatahet (1 year ago)
Hi Adam, I can't connect Power BI to mysql online database Can you help to connect my online data to power bi
Guy in a Cube (1 year ago)
Is it just in a VM somewhere? Do you have other tools that connect to it fine from the same machine that Power BI Desktop is running on?
Thanks ! very informative. My Source database is from an Azure SQL Warehouse. I need to build reports on the Desktop and publish it Online for sharing it to a wider audience. When my source database gets updated, Will it automatically reflect in the PBI service (which i just hosted) ? Or is there any Gateway i need to set up ?
felipe sepulveda (1 year ago)
Have you even try to contect to a Oracle database??
felipe sepulveda (1 year ago)
I could configurate Oracle DB as data source ONLY in PowerBi Desktop...i surrender with the gateway configurataion....i'll reaaaaaally apreciate a video for Oracle DB configurations...you'll save my job :')
Guy in a Cube (1 year ago)
I have used Oracle servers before with both Power BI Desktop and through the gateway. Are you hitting a problem? Or would you like a specific video on it?
goldprint22 (1 year ago)
Thanks for your great videos , Question: What permissions does the gateway user need on the SQL server.
Dinesh Reddy Allam (1 year ago)
Adam! Great insight. I have a situation though. Will this work if we import the database to PI desktop instead of Direct Query. I'm gonna test it and see.
Guy in a Cube (1 year ago)
I'm not sure I understand the question. The video was specific to working with DirectQuery. If you choose to import the data, instead of using DirectQuery, then it will be based in the cloud. You can then use the gateway to do scheduled refresh.
Muhammad Adil Khan (1 year ago)
Can I make a live connection using SQL server ( NOT Azure SQL server) ?
Guy in a Cube (1 year ago)
It would be DirectQuery, not a live connection. Live connections are for Analysis Services only.
Nishith Shah (1 year ago)
How Can I use Stored Procure which returns multiple Result Sets. (Multiple tables as Results)
Pete Talbert (10 months ago)
Nishith Shah a really simple solution would be to just insert the result sets into separate tables, and a drop if exists clause at the top so it recreates them every time it’s executed. Then you can just grab those tables in power bi. Last, you can schedule a job to execute the sproc on a schedule.
Gabriel Rustice (1 year ago)
That's an awesome video! After several months struggling to make the CRM On Premise (IFD) work with Power BI I finally decided to go and make the SQL Server connection directly. Not my preferred approach but so far so good. My one challenge now is that if I add a new field in my data source, Power BI errors out in the next refresh. Does that mean I will need to recreate my data model every time a field is added in the source?
Gabriel Rustice (1 year ago)
DataSource.Error: Microsoft SQL: The type of column "new_ENICustomermigration" conflicts with the type of other columns specified in the UNPIVOT list. Details: DataSourceKind=SQL DataSourcePath= '(our SQL server database goes in here...) Message=The type of column "new_ENICustomermigration" conflicts with the type of other columns specified in the UNPIVOT list. Number=8167 Class=16
sicKer sicKer (1 year ago)
Thank you so much man, I just didn't know how to make it works, amazing video, thank you!!!!, now I`m suscribed :)!!!.
Guy in a Cube (1 year ago)
Glad it helped! Thanks for watching!
Saul Espinoza (1 year ago)
I love you Bro!!!!!!!, once i finish my BI theory course i will dive deep in your videos!!
Guy in a Cube (1 year ago)
Woot!
Cristian Dominguez (1 year ago)
HELLOOOOO!!! Can you help me please...my question: I´m conected to a database with PBI desktop (free version) and I have loaded in PBI about 5 HEAVY tables (1000k rows each growing every minutes) which I have to get them related and Im done with it... everything works good as I want but it takes too long (10 to 15 minutes) to get the data refreshed becasue I guess the program has to load the whole tables before getting the information I need which is always the last 100k rows... I´m wondering if I can use TOPN or a filter which allows me to get just the information I need in "directquery" to get just the last 100k rows of those tables directly of the database (DB2) instead of loading everything and after that start getting the 100k rows with the topn filter...if that is possible...do you know any link where I could learn that?...Im looking and I cant find something like that...Thank you "guy in a cube" in advance!
Guy in a Cube (1 year ago)
There is not currently a way to do an incremental refresh. It does a full refresh every time. This was indicated as a feature coming with the new Premium updates down the road. You could potentially partition things out and then append them into a resultant query. This could take advantage of the "Include in report refresh", although i think the end result would still be the same for you. Another option would be to go the DirectQuery route. This currently requires a Pro license as a gateway is involved, although with the free changes coming, that will be available.
phani ramakrishna (1 year ago)
Hi, I have a scenario where i need to connect two different databases and get common data using sql queries how could that be possible in direct query
Guy in a Cube (1 year ago)
Unfortunately, when you choose DirectQuery, you are limited to one data source. You could potentially do linked server queries at that point though and expose them via Views if that made sense. The other option would be to consolidate into a Data Warehouse and connect to the Data Warehouse.
Djazair Laroui (1 year ago)
totally Subscribed!! thank you for your profetionnal work sir, u ROCK!!
Guy in a Cube (1 year ago)
Thanks Djazair! Much appreciated!
OUMAIMA HOUARI (1 year ago)
Hi Adam!! i'm working on my final year project..in my app i have to type the name of a facebook page and i will have as a result the same dashboard but different data (different facebook pages).PLZ How can i do that??
Guy in a Cube (1 year ago)
You could do that a couple of different ways. You would need to do that by way of a Filter though. You could pass in a filter into the URL. I recently did a video on how to do that. Or if you embed the report into a web page, you could control the filter by way of the JavaScript API. You could also add a slicer to your report based on the different facebook pages, change it to a drop down and then can type in the name into the search box to select the page. The Power BI Service right now doesn't support parameters though.
Mallikarjun M K (1 year ago)
Adam, 1. I Created a MVC WebApi and it is returning data in XML format in browser 2. I pass a parameter to WebApi url in browser it returns respective data based on parameter what I pass (http://localhost:51919/api/getdbinfo/GetCustomerByID/?customerID=1) and this gets data from parameterized stored procedure 3. I built a report using power bi desktop using Get data - From web(http://localhost:51919/api/getdbinfo/GetCustomerByID/?customerID=1) 4. I published this report in Power BI service it works fine for only customerID=1, I am trying to pass different customerID each time and see the report in Power BI service, which is not working 5. I need a way where I can pass customerID as a parameter from my application and it should show me the corresponding data of the customerID which I pass
Mallikarjun M K (1 year ago)
Adam, 1. I Created a MVC WebApi and it is returning data in XML format in browser 2. I pass a parameter to WebApi url in browser it returns respective data based on parameter what I pass (http://localhost:51919/api/getdbinfo/GetCustomerByID/?customerID=1) and this gets data from parameterized stored procedure 3. I built a report using power bi desktop using Get data - From web(http://localhost:51919/api/getdbinfo/GetCustomerByID/?customerID=1) 4. I published this report in Power BI service it works fine for only customerID=1, I am trying to pass different customerID each time and see the report in Power BI service, which is not working 5. I need a way where I can pass customerID as a parameter from my application and it should show me the corresponding data of the customerID which I pass
Shakthi Venugopal (1 year ago)
hi i have 6 charts as my requirement. 1. Region Map. 2. Male & Female Count 3. Age range (1-18,19-40 ect) 4. Some Studying info 5. marks info 6. List of School details. Interaction also created in same order (just imaging top 3 chart down 3 charts). so Now i selected 1. Clicked on region A =>all my charts are filter and showing filtered data . now click on Female pie chart then 3. Age range 4. Some Studying info 5. marks info 6. List of School are filtering. but my 2. Male & Female Count chart and 1. Region Map. are reset into default visual . i nee my chart no 1 and chart no 2 should remind with the value i selected . please help me out
Guy in a Cube (1 year ago)
I would think it would behave as you expect if the interactions were defined that way. I'd want to see a local repro to understand what was going on though. It is hard to understand what is happening over this format. It may be worth spinning up a discussion on community.powerbi.com.
Amit Shukla (1 year ago)
Hi Adam, ***Urgent ..... We need power BI pro account to use schedule refresh feature could you please tell me if i set up the schedule refresh on my report through personal data gateway using power BI pro account and I will share this dashboard/report with a person without Power BI pro account (Standard power BI account) so will he be able to view the report and refreshed data ?
Guy in a Cube (1 year ago)
Make sure your voice is heard over on ideas.powerbi.com. There are two items that go along with your though. https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6769950-create-an-read-only-and-external-license-tier https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/10239816-share-a-pro-dashboard-with-a-standard-user-need You can vote them up and comment on them.
Amit Shukla (1 year ago)
I believe consumption of report should be independent of license but as of now no comment on that part.
Guy in a Cube (1 year ago)
The way it works currently is if your report uses Pro content, which refreshing with a gateway is Pro, then anyone touching that report/dashboard requires a Pro license.
Amit Shukla (1 year ago)
Guy in a Cube In my organization I have to share the report with around 80 people so all of them required the pro license?
Guy in a Cube (1 year ago)
+Amit Shukla consumers of report would also need a pro license
sonocapace (1 year ago)
Hi Adam, Your videos are very informative and helpful. Thanks so much for posting them! I've got an on premises gateway set up and I am pulling from SQL server views to populate data in the Power BI reports. The views rely on a user's windows auth to determine what data they should be able to see in the reports. I thought that this would work to show them only their data when they open the report(s) I published to Power BI but the data just remains set to the data *I* am able to see. Am I missing something?
Guy in a Cube (1 year ago)
Unfortunately, for SQL Server, you would have to have stored credentials if going through the On-Premises Data Gateway currently. So, the username/password you provide for the data source in the gateway will be shared by all viewers of the report.
Amir Sharif (1 year ago)
Hi Adam, you are doing really a great work! I have one question, is it possible to map direct query over pre-builted reports using data import?
Guy in a Cube (1 year ago)
Thank you for your kind words! Unfortunately no. You have to make that selection at design time. You can go from DirectQuery to imported after the fact within Power BI Desktop, but not the other way around.
Steven Chan (1 year ago)
Adam, quick shout-out! Your powerbi video's are awesome and super helpful. Keep up the good work!
Guy in a Cube (1 year ago)
Thanks for the kind words Steven! And, thanks for watching!
Christopher Mueller (1 year ago)
Have you ever worked with Azure SQL? Would this be the same process ?
Christopher Mueller (1 year ago)
Thank you!
Guy in a Cube (1 year ago)
I have worked with Azure SQL Database. From a Power BI Desktop perspective it is the same process. The difference though is that you don't need a gateway once you publish to the service as we know where to go for Azure SQL.
Pramod Narayan (1 year ago)
1) Is caching(of data) part of direct query. can we configure this in the power BI service 2) Can we embed individual charts/tables (in power BI) in a website.
Guy in a Cube (1 year ago)
For directquery/live connections, we do cache some data for the dashboard tiles, but not for the reports. There isn't a way to control that. You can embed tiles into a report, which could be from an individual chart/table if you pinned it to a dashboard. It wouldn't be interactive though.
Nirav patel (1 year ago)
Adam ! Your video is great help to understand PBI with SQL. But I have one question regarding direct query. How do I refresh sql data once I published report to web using iframe ? Also Do I need pro account to get live connection to sql using gateway ??
Guy in a Cube (1 year ago)
When you have a report that uses DirectQuery, you don't need to refresh as we issue live queries to the data source as you are interacting with the report. We do refresh the dashboard tiles, i believe every hour. A Pro account is required when using the gateways.
jay epps (2 years ago)
Is "add table" supposed to be disabled with DirectQuery? Currently I'm connected to a SQL database, and when i try to add in a date table, I'm running into issues.
VEvolutionary Thompson (2 years ago)
Awesome video Adam. Can you delve deeper into Security Setup for Power BI?
Guy in a Cube (2 years ago)
Thanks! I've tried to do that with different videos I've had relating to Azure Active Directory. Is there something specific you would like covered?
Ravi Rao (2 years ago)
Thank you so much Adam. nice video. I need to call a power BI dashboard from a website (HTML5) with parameters. user needs to select/enter the filter criteria from web interface and then it will call up PowerBI report with filtered data/report. Appreciate your help and guidance here. Please
Guy in a Cube (2 years ago)
Thanks for watching Ravi! Have you checked out the new Javascript API? it allows for filtering to be passed in. https://powerbi.microsoft.com/en-us/blog/intro-pbi-js-api/
Mkane (2 years ago)
Hi Adam, Just one query, what are the limitations of Power BI with respect to online report/MIS that we can generate using other tools. I am not at all clear about this as I am just exploring Power BI right now. A Thanks in advance :)
Mkane (2 years ago)
Thanks, I will check and will try to implement these. Sorry for this but I forgot to ask, how can I pass parameters at run time to the stored procedure
Guy in a Cube (2 years ago)
You can supply custom queries, which would include something like EXEC mystoredproc, and choose DirectQuery. On the Get Data dialog, if you select advanced options, you will see the location for the SQL Statement. You can also added a text box or an image to the report that is not part of the data set.
Mkane (2 years ago)
What I mean to say is like using of complex queries from sql server, stored procedures and combination of them.Also like putting some formatted text within the report with images etc. I tried to explore but couldn't find such help. Please confirm on these points. Thanks
Guy in a Cube (2 years ago)
I'm not sure I understand the question. Is there something specific you are looking for?
Llewellyn Holtshausen (2 years ago)
Thank you so much for this, it's exactly what I was looking for. So let me see if I get this right. So in order to connect Power BI Desktop to SQL Server 2016 onsite (to be published to Power BI.com later when dev has been completed), I need to 1. Download and register the gateway on the server machine (where the SQL Server is located), 2. Source data in Power BI Desktop from SQL server using the same credentials as the gateway, model the data and create visuals, 3. Load the .pbix on Onedrive for Business or publish directly onto Powerbi.com. 4. Enjoy the awesomeness of what you've just done!! Now if this is correct, I have two questions, and excuse me if I sound retarded here 1. Can you only connect to the SQL Server onsite from Power BI Desktop from the same machine the SQL Server resides on? 2. From step number 3 above, is the assumption that you don't have to use OneDrive for Business at all and can just publish your .pbix file to Powerbi.com? Again, thank you so much for your videos, love them......may the force be with you!!!
Llewellyn Holtshausen (2 years ago)
+Guy in a Cube Thank you so much! Keep up the good work!
Guy in a Cube (2 years ago)
As long as your laptop/desktop can connect to the SQL Server you can do it from there. It doesn't have to be done on the actual SQL Server machine.
Llewellyn Holtshausen (2 years ago)
+Guy in a Cube thank you so much. That does answer my question. So the solution we'll be using will be as follows, development will be done in Power BI desktop, data is loaded onto a onsite SQL server 2016, from there I aim to pull the data into Power BI Desktop, once development is finished I will publish the file to the PowerBI.com site for the users. Does that seem feasible? One more clarification question, in this scenario, does the Power BI development need to happen on the same machine as the server or can I pull the SQL Server data from any laptop / desktop with the right credentials?
Guy in a Cube (2 years ago)
You are on the right track. This video was specific for using DirectQuery with an on-premises SQL Server. That is one of the options you can connect to SQL Server with. That is if you want to interact with live data. You can also connect to SQL Azure using DirectQuery but this doesn't require a gateway as we understand it is a cloud source. For imported data, instead of DirectQuery, the On-Premises Data gateway isn't required. You could use the personal gateway to scheduled refresh if you wanted to instead. For any of the above, the PBIX file does not need to be stored in OneDrive for Business or SharePoint Online. You can just do a Get Data > Files > local file and take it that way. OneDrive/SharePoint Online offers the concept of Package Refresh to easily update the references. Does that help?
Loki DS (2 years ago)
Adam, how do i get data to my Power bi Desktop from SQL server which is on a remote win server?
Guy in a Cube (2 years ago)
Are you hitting a specific error? You should just need to provide the server name and optionally the database. Also provide the credentials to connect. You may need to make sure that the firewall on the SQL Server will let it through.
Steven Tjin Aton (2 years ago)
if i publish a dashboard to the internet can i retrack the published file?
Guy in a Cube (2 years ago)
If you used Publish to Web you can revoke the embed code. Go to the gear icon and select embed codes.
yamil castillo (2 years ago)
Hi, several user can use the same gateway?
Guy in a Cube (2 years ago)
In the service, you can list who are admins of the gateway, and for a given data source, you can list the Users that can publish items using that gateway. The consumers of the reports/dashboards just need to have a Pro license.
yamil castillo (2 years ago)
Thanks for your answer, Where I share it, in the server? or administring gateway in my session in the web
Guy in a Cube (2 years ago)
The On-Premises data gateway is a gateway that can be shared by different users within your organization. This is more of the Centralized IT version of the gateway. The Personal Gateway is for individual use.
Gabriel Amato (2 years ago)
Hello Adam, if you could please IM me, I'd like to elaborate with you a major issue I have with my org. I'm trying to implement PowerBI as our MI tool for the whole business and their major concern is data hosting on third-party server, basically we cannot use anything other then DirectQuery otherwise that would configure a data breach. Regarding DirectQuery, does the SQL Server security permissions still transfer to the viewer, what I mean is, a developer publishes a report and share with someone who does not have access to the database, he will still be able to see the report? If yes, how can I setup security permissions for the viewers of that report? Only RLS? Does DirectQuery prevents Microsoft from hosting the data, meaning that the query and the gateway must be active for any user to view the report? Your videos are of great help and have provided great proof of concept for the IT Security teams in our business. Keep it up!
Guy in a Cube (2 years ago)
Not seeing any options to IM you. :( You can send a direct message on the YouTube Channel, or hit me up on Twitter (@GuyInACube) or Facebook (facebook.com/guyinacube)
Guy in a Cube (2 years ago)
Thanks for watching Gabriel. Will send you a direct message. But the short of it is that the user's credentials on the Power BI/O365 side are not passed to the on-premises SQL Server. Currently you set an account for the data source within the enterprise gateway. That is the account used to connect to SQL Server. It is a static account. You can do RLS on the cloud side. That is the only real option i can think of.
Mahima Bedi (2 years ago)
When I create a report in PBI desktop by connecting live using my SQL credentials, if I send the desktop file to another person will be need their own credentials to view the data (live a live cube connection) or will my credentials get passed along to them. Also once in the cloud, will the persons user name be used as a credential or only the credentials in the gateway. So basically if a person dosnt have access to the SQL DB but has access to the PBI report, will they be able to view it using the credentials of the publisher?
Mahima Bedi (2 years ago)
Thanks!
Guy in a Cube (2 years ago)
That's correct.
Mahima Bedi (2 years ago)
So the below scenario would true? "A" has access to SQL DB "A" creates a report/dashboard using direct query. Hardcodes the SQL admin credentials in the gateway. "B" does not have access to the SQL DB "A" shares the report/dashboard with "B" Since for SQL only the hard coded credentials are passed to the datasource, and not the user credentials- "B" will be able to view the report content
Guy in a Cube (2 years ago)
If you choose Windows Auth for the data source credentials, within Power BI Desktop, it will use the current user's windows token. Your's on your machine, and their's on their machine.When you publish to the service, and create a data source within the enterprise gateway, you hard code the credential it will use to connect. That will be the same credential for anyone that uses it within the service.
Amer Laceb (2 years ago)
Thank you  so much Adam for this very helpfull video ! I was working on this and you've just helped me save a lot of time. :)
Guy in a Cube (2 years ago)
Awesome! glad to hear! Thank you for watching!
DANIEL DAMICO (2 years ago)
Hello Adam! your videos are great! kudos to you! let me ask one question (not related to direct query/enterprise gateway). 1)I have signed up for the free version of powerbi service. 2)I have a csv file on my one drive for business 3)I created a pbix file using power bi desktop which connects to/reads the csv file. 4)I imported the pbix file to the power bi service I'm now struggling to have the dataset/reports updated automatically after I add more/change information to the csv file Considering this scenario, Is it possible to have the dataset/report updated automatically using the free version of power bi? Any help will be much appreciated! Thank you
Guy in a Cube (2 years ago)
Thanks Daniel! Unfortunately, going through Power BI Desktop, it will connect to the CSV file that is synced to your local drive. And because Power BI Desktop sees the connection as a local drive, it will require a gateway to refresh it. I haven't seen a way to work around that from Power BI Desktop or Excel.
河端善博 (2 years ago)
Can I use Row-level Security with DirectQuery connection with SQL Server and the Power BI service and Power BI Gateway ? I want to use it.
Guy in a Cube (2 years ago)
Correct! :)
河端善博 (2 years ago)
Thank you, Adam for your quick reply. My understand for 2 types of Row-level security with DirectQuery connection with SQL Server. OK - Row-level security of Power BI Service, with DirectQuery connection to SQL Server. NG - Row-level security of SQL Server 2016, with DirectQuery connection to SQL Server.
Guy in a Cube (2 years ago)
You can use the cloud-based Row-level security with DirectQuery connections. Right now, you cannot use the new RLS functionality within SQL Server 2016.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.