- 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
