What are Power BI data connectors and why should you care?

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
- Yooo! Adam Saxton with Guy in a Cube. And in this video, we are gonna look at connectors. The heck is a connector? We're gonna look at how it relates to Power BI, maybe a little bit of Excel, and why you should care about these, especially when it comes to your data. Let's go. (upbeat music) If you're finding us for the first time, be sure to hit that subscribe button to stay up to date with all the videos from both Patrick and myself. All right, connectors. What do you mean, Adam? What is a connector? If I wanna get data from an Excel file, from maybe a SQL Server, or an Access database, I need to connect to that data source and that's what connectors do. They allow that communication between Power BI or Excel or some other services as well, and allows it to connect and use data from those data sources. This is all in the context of Power Query. All right, enough of all this talking, you know how we'd like to do it on Guy in a Cube. Let's do what? Let's head over to my computer. All right, so we're inside a Power BI Desktop and you'll see the great Get data menu item. We click on this, all of these items that you see here, these are all connectors. And if we wanna look at the entire list, we can go to More. And here you will see all of the connectors that are available for Power BI. You'll also see a term called data source. These are all the same thing, right? They utilize these Power Query connectors to work with that data. So let's get some data from Excel. We can connect to this Excel workbook. We hit Open, this is actually now connecting to that data, getting the list of the worksheets. That's a no-brainer, and then if we wanna connect to SQL Server, in this case, an Azure SQL Server, and I'll say import. We'll come back to that, hit OK, so now I'm connecting to that Azure SQL database. And then I can see all of the tables. So the connector allows me to work with the data and get that into Power BI. All right, so that's Power BI. I mentioned a little bit of Excel. So if we go to Excel and we go to the Data tab, you'll see the Get Data item there same thing as Power Query, right? And so Power Query has a bunch of connectors that are available for it. And you can use this from an Excel perspective. You can use it from Power BI Desktop, and there are other items as well, like Azure Analysis Services. The thing to keep in mind with these though, is that when we're connecting to that data source, this is an important concept, so I want you to think about this for a second. The connection is happening from the machine that you're running Power BI Desktop. So if I'm inside a Power BI Desktop or Excel, and I'm connecting to that data source, the connection's coming from your machine. So it's not coming from the cloud. It's not coming from your buddy's machine. It's coming from the machine that you're using right now. So the desktop PC that's in front of me, and connecting to Azure SQL Server. So that has all sorts of network implications, authentication, implications, all of that. So just be mindful that that's where the connection is coming from. The other thing I wanna mention is the type of connection. This is specific to Power BI. Inside of Power BI, we had that dialogue when we connected to SQL Server where it showed import or DirectQuery. Different connectors have different options for connecting to data. So I did a video a while back, where I talked about the differences between import, DirectQuery and live connections. So go check out that video. I've got it up above and down in the description below. You can learn more about the different types there. I'm not gonna go into it in this video. All right, so your next question may be, and I hear this a bunch of times from customers is, they've got some data source that they're using, is it available inside of Power BI or inside of Excel? Let's go look. All right, so the first thing I do when someone asks me, "Hey, can I connect to this data source?" Power BI Desktop is right in front of me and I don't necessarily have the website in front of me. What I'll do is I'll search for your data source. So there we go. We've got different options for Salesforce. Let's see SAP, we've got different options for SAP. Oracle, and we've got an Oracle database connector as well. So you can just search for these items and see if they're there. The other thing you can do if it's Power BI specific or actually just connectors in general, there is resources, and I'll have these down in the description below, but there's a doc for Power BI that lists all of the data sources. And remember when I say data source, think connector, it's the same thing. So you can go through and see if those items in search in here and see if it's available. And it'll also tell you the type of connection. So DirectQuery, live connection, connecting from desktop, the service, the gateway, all of those things. Also, there's a list of Power Query connectors. This tells you if it's available in Excel, Power BI, Power Apps, Analysis Services, and so forth. So this is a good way of going to look and see, is that connector available? Sometimes we run into where there is not a actual connector inside of Power Query or Power BI that I can use to connect to that data source. So don't freak out. This is something that may still be available for you. Let me show you. Inside of the connector, one thing you can do is search for ODBC. So the ODBC connector is a generic connector, right, so this is kind of going old school. This is how we connected to items like in SQL Server Reporting Services, or on-prem. We either had to have an ODBC driver or we had to have an OLE DB provider or ODBC driver. Then as part of that, you could configure the ODBC DSN or data source name inside of the ODBC Data Source Administrator using the drivers that are available and installed on your machine. This means you may need to install the driver as part of this. So if I was trying to connect to Oracle, you can see, I don't have Oracle in this list, so I need to go connect those drivers, in order to use it. And then once the DSN is created, then you can use ODBC to connect. So if I connect to that, it's gonna ask me what data source name am I going use? So you have to define the data source name, or you can just give it the raw connection string and go from there. One thing to call out about maybe ODBC, as well as just other connectors itself, you may get a dialogue, like if you're trying to use Oracle or SAP, where it says that you need to install those drivers. Some connectors inside of Power BI and Excel, they don't ship with the actual drivers. A lot of them do, but some of them don't. And so you may have to go to that third party, go install those drivers on the machine that you're connecting from. And then you can use them inside a Power BI or Excel or whatever. One thing I wanna call out here is that bitness matters. Power BI Desktop is a 64-bit application. The gateway that you may use to connect to that data is also 64-bits to make sure you're using the 64-bit drivers. So if you install the driver, you don't see it listed, check the bitness. Is that 32-bit or is it 64-bit? Probably needs to be 64-bit. All right, so you've done all of this. You're looking for connectors. There's just nothing available, like, you're not sure what to do. Where can we go from here? Power Query allows for the capability of creating what's called a custom connector. So this is not something that ships with Power BI directly, but it's something that is potentially available that you can use inside a Power BI Desktop, as well as the gateway when you wanna publish it to the service. One of the ones I usually point folks to is CData. So this is cdata.com, and here, if we go to Connectors, you can see all of the connectors that they have available. One of them recently I was talking to about was Presto. So they want DirectQuery with Presto, which is not available, I believe outside of the box in Power BI. You can see all the different items up above. I can click Power BI and then you can see this one does support DirectQuery. A lot of these are certified connectors, which means your organization is probably okay to use it. Just one thing to be mindful about custom connectors is its performance may not be as good as, like, a default connector inside of Power BI. So that's something you should always measure, and test, and validate. But what about when I publish to the service? If it's an on-premise data source or a cloud data source a Power BI can't connect directly to, you've got to use the Power BI gateway. These connectors will work with the gateway, and I showed you the Power Query doc, which will show you if it's available through the gateway and also the Power BI doc. So you can validate if that connector is available in the gateway and go ahead and use it. I talked about the ODBC or the OLE DB and also like drivers for like the Oracle connector. When you install those, you're installing those on your Power BI Desktop machine. And I said before in Power BI Desktop, you're connecting from your machine. That's different if we go to use the gateway. So if your gateways on some other machine that's out there in your network, that's where the connection is going to be from. So that's where you need to install the drivers also. And so, yeah, you already installed them on the Power BI Desktop machine, but did you install them on the gateway? And then also, is there any other network items that come into play as part of that? Right, I know that was a lot of information. Hopefully that helps you be more aware of these data sources that you're connecting to, what's needed for them, where that connection is coming from, and what connectors are all about. If you have any questions, let me know down in the comments below. Let's keep the conversation going. If you like this video, be sure to hit that big thumbs up and smash it, if you so desire. If it's your first time here, hit that subscribe button. And as always from both Patrick and myself, thank you so much for watching, keep being awesome. And we'll see you in the next video
Info
Channel: Guy in a Cube
Views: 32,421
Rating: undefined out of 5
Keywords: power bi, power bi connector, power bi dashboard, power bi data sources, power bi demo, power bi desktop, power bi desktop power query, power bi desktop tutorial, power bi desktop tutorial for beginners, power bi for beginners, power bi training, power bi tutorial, power bi tutorial for beginners, introduction to power bi, learn power bi, power query in power bi, power query, power query connectors, power query excel, excel power query, business analytics, excel
Id: 1dRhH-EO-rI
Channel Id: undefined
Length: 8min 33sec (513 seconds)
Published: Thu Oct 07 2021
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.