All language subtitles for 009 Connecting to a Database_en

Instructor: So far, we've seen the process

for connecting to and loading data from a local file

into Power BI,

but chances are you won't always just be working

with flat files.

So in this lecture,

I'd like to cover the process of connecting to a database.

Now, Power Query can connect to data

from many different database sources like SQL Server,

Microsoft Access, MySQL, Postgres, Oracle, SAP,

and many, many more.

And the great thing is that once you understand

how to connect to one database,

the process is very similar

to connect to other database sources.

From the Get Data window,

you'll select the database connection you want

and enter the database's specific credentials.

Once you've added in the appropriate credentials,

there's some advanced options

that allow you to configure the command timeout

and even write custom SQL statements to customize, limit,

and shape the data before it's added into the query editor.

Once these details are all set,

we'll be able to select the specific tables

and then load them into the query editor

for QA, shaping, and transformation.

So for this demo,

I'm going to be using a database that we use

in Maven's advanced MySQL course

called Maven Fuzzy Factory.

And if you've taken the MySQL course,

you should have the community server

installed on your machine

and can likely follow along with this demo

and practice the connection steps.

Just to call this out, specifically,

we will not be installing Community Server

as part of this demo.

So if you don't have it installed,

no worries, just sit back, relax, and enjoy the demo.

So with that, let's jump on over to Power BI

and we'll check this out.

All right, so from the relationship view here,

we can come up to Get data

and we'll click on more sources here.

And we can actually just type in mysql to search,

and we'll connect to our MySQL database.

All right, so to get started here,

we're gonna enter in our server and database name.

And this is my local server address.

And the database name is mavenfuzzyfactory,

and mine is called _development.

All right, and like we saw in the slides here,

we have some advanced options.

We can set a command timeout.

Or here you could type in some custom SQL statements

to either limit, or transform,

or shape the data prior to importing it.

But we're just gonna connect to the database as is.

So click OK.

And at this step, when we're prompted for the credentials,

I actually want to use the database credentials.

So I have a username and a password set up.

And my username is root, and I'll enter my password.

And we'll connect from here.

Awesome, so we have our kind of preview window here, right?

Looks similar-ish to when we connect to the CSV, right?

Except for now, we have our different table names.

And if we click on each one of these table names,

we're gonna see a preview of result

on the right-hand side, right?

So we've got all of these different tables

that we can click through and kind of see, right?

And we're gonna connect to all of these tables, right?

All six.

And again, we've got load and transform data options.

We're gonna click Transform Data,

and that'll bring us back into the query editor.

And now that we've got the data

all loaded into the query editor,

from here, we can go through

all of those same kind of applied steps.

We can update our table name,

we can go through and check all of our data types,

rename column headers,

go through really all of those different QA

and any sort of transformation steps that are needed.

But because we're not using this data throughout the course,

I actually don't need to perform

any sort of transformations here.

The one thing that I do want to do

is I want to disable this data from being loaded

into the data model, right?

I'd love for it to still stay in the query editor for now,

but I don't need it within my model.

So if I right-click this table,

I'm gonna click this Enable Load.

And there's a very subtle name change here

where the name is now italicized,

and that indicates that it is not going to be loaded, right?

So we'll go through each one of these

and remove the Enable Load.

And again, this is just a great tool to use

when you don't want to actually load those tables

into your data model.

All right, so from here, we're gonna click Save,

I'll apply my changes, and we'll be good to go.

All right, so that is your demo

on connecting to a SQL database.

All right, so now that everything has saved,

that's gonna wrap up this demo

on connecting to a SQL database.

Up next, we're gonna check out

how we can actually scrape data from the web.