[MUSIC PLAYING]
Welcome to MSPTDA video number 11.
Hey.
In this video, we want to talk about importing SQL data using
Power Query, We want to import it into Excel and into Power BI
Desktop.
Now, what's SQL data, and what's an SQL database?
An SQL database is a relational database that uses SQL code--
that is structured query language--
to query or communicate with the database.
Now in our class, we'll use Power Query
to generate the M code that allows
us to connect to an SQL old database
and extract data from that database.
Our class is not about learning how to write SQL code?
However, I will show you one example later in this video
of how, if you know how to write SQL code,
how to put it into Power Query to get Power Query to send it
back to the SQL database.
Now to take a brief look at an SQL database.
I'm using Microsoft's program SSMS
to look into this database.
The name of the database we're going to have to connect to,
which is an online SQL database, is Boomerang.
For this video, we're going to have to pull the table's F
transaction and D product.
Later in this class, we'll pull more tables
and create a full data model.
For this video, we're going to make two transformations
in Excel, one using Power Query User Interface, the other SQL.
Then we'll switch over to Power BI Desktop
and pull data into Power BI Desktop.
Now here is a look at the F transaction table-- date,
website, product, quantity, revenue, discount,
and some other columns.
Here's D product table-- product, retail price,
and a bunch of other columns.
Now let's go back over to Excel to start our first importing
of SQL data.
Now to take a look at the one, two transformations we're
going to do in Excel using Power Query,
these are the two results.
They'll be exactly the same results.
One is done with Power Query User interface.
The is done with SQL.
And our goal is to pull data from both F transaction and D
products, include only transactions where
the quantity sold is greater than or equal to 100,
calculate net revenue, group by product
to get total net revenue, include only group
by aggregations that are greater than 100,000,
and then sort the product column.
So we're using Power Query to import and transform
to get these two reports.
Now over here in the start file, these
are going to be our credentials to connect to our SQL database.
And if we're using Power Query User Interface
or typing your own code, we want to consider
these important points.
Now when we're using the Power Query User Interface,
the steps we create are sent back to the SQL database.
This is called query folding.
And this is because an SQL database is often
more efficient--
meaning the performance or the speed of the query-- than Power
Query.
Wow, that's pretty cool.
So whatever we do over here and Power Query,
Power Query is programmed to send it back.
And because we're connecting to an SQL database,
the transformations, when possible,
will be performed at the database.
Now some Power Query features, like custom M code--
and we've been doing a lot of that lately--
and functions like the Table Unpivot function cannot be
understood by the database.
So they will not be sent back.
They'll be performed natively in Power Query.
Also, if you want to prevent query folding,
you use the Table.Buffer function.
For your own code, unless you're master SQL coder,
it's usually more efficient to use the Power Query User
Interface.
Hey, as Miguel Escobar and Ken Pauls in their book
says, "unless you're an SQL ninja,
don't bother typing the code."
But for convenience, some people like to write SQL code.
It's totally possible.
So we'll see how to do that.
Now let's scroll over to the side.
All right, we'll create our Power Query User Interface
solution first.
To connect to an SQL database, we go up to Data, over to Get
and Transform, and Get Data from Database.
And there's a few different options for SQL databases.
This is the one we're going to use.
There are others, including other Microsoft services
and other databases.
Even over in Azure, we have SQL database.
And we want to select the first option.
This is the SQL database dialog box.
We want to select Advanced Options.
Now this dialog box looks the same or similar
for most of the database connections in Power Query.
So we need to list what server computer we're pointing to.
We're pointing to Pond.Highline.edu.
The database in that server computer is called Boomerang.
Now when we're pointing to that database,
if something's going on in the database,
it may take a long time to connect.
So we can specify in minutes how long we're willing to wait.
We're going to wait one minute before a time-out.
Now this is where you write your SQL code.
We're not writing any now.
We're going to use the Power Query User Interface.
Now our goal is to take the transaction table
and get from a related table the retail price.
So we definitely want to check Include Relationship Columns.
Now normally if you're importing the fact
tables and the dimension tables, you'd
uncheck this and build your relationships
in the data model.
But for us, we want that checked.
Now when I click OK, it's going to ask
for my username and password.
Click OK.
Now we're going to connect to a database.
Our username is excelisfun, all lower cases.
Our password is capital E-X-C-E-L, capital I-S,
capital F-U-N!
That password is listed in the PDF notes
and in the Excel workbook.
Now we can choose which level, either the server or just
the database.
We're going to choose just the database.
Now I'm going to click Connect.
Now it's going to tell us that we're
unable to connect in an encrypted way, that is fine.
I'm going to click OK.
This is our Navigator step.
And this should look very similar to what
we did with Excel and Access, because we
have multiple objects in this database.
Now later we'll select this and select Multiple Tables,
but all we want is F Transactions.
Now I want to go down and click Edit.
This brings me into the Power Query.
Editor I want to come over and rename
this ProductReportPQUserInterface.
Now I want to go back to Source, and we
can see there's an M code function called SQL Database.
There's the server.
There's the database.
And there's our time-out code.
And the SQL database returns a table with objects
and attributes.
We've seen similar tables earlier in the class
for our source data.
Now, notice that transactions from the item column,
that's the table we wanted.
We want the schema DOB.
So the Navigation step, which is our lookup step,
hey, there's the source table in curly brackets.
That's look up the row, and in square brackets,
that's look up the column.
And sure enough, it delivers our F transaction table.
Now we're going to scroll over and actually the first step
is from the product column, I'm going to use Expand.
These are the related columns from the product table.
I want to uncheck everything.
I only need to use retail price as part of our net revenue
calculation.
Click OK.
Now part of this transformation and query
is we need to filter quantity to only show quantities
100 or more.
So down to Number Filters, we want
to go down to please Include Greater Than or Equal to,
and we're going to hard code in 100.
Click OK.
Now look at that there's our table
dot select rows, greater than 100.
Now we have quantity.
We have our retail price over there and our revenue discount.
Now this is the discount amount, so we're
going to have to take 1 minus that
and multiply it by quantity and price.
We're going to do that by adding a column, custom column.
The name of the column will be net revenue.
Now we come down.
Right at the bottom, I'm going to double click retail price
times quantity double click times in parentheses
1 minus double click revenue discount close parentheses.
Now some of the earlier examples in the class we rounded,
we're not going around here.
Click OK.
Now we can use this column and scroll over
and product to group by product and aggregate
our new custom column.
Right click group by.
Group by product will give us a unique list of product names,
the new column name.
The aggregation will be sum, and we're
going to work on our new net revenue column.
Now when I click OK, I have a unique list of products
and all of the aggregation.
Now one of the other criteria for this report
is that once we get our aggregations and our group
by step, we need to filter this to only include rows
where the numbers are greater than or equal to 100,000.
Now when I click OK, there is another table dot select rows.
Now we have one last step.
We're going to sort the product column ascending.
There's our table dot sort function.
These are all the steps created using the Power Query user
interface.
They will be sent back, and if possible, they will be executed
and the transformation will take place over in the SQL database.
Now we're ready to go to home, close and load,
close and load 2.
Now table is fine.
Existing L 11 is fine.
Click OK.
We can see over in the queries and connections 19 rows loaded.
There's our report, all using the Power Query user interface.
Now most of the time, that's what we want to do.
But if you like writing SQL code,
we'll go ahead and see how to do that.
Well, we go to the same place, data, get data from database,
SQL server.
We put it in our credentials, and we write our SQL statement
here.
And here's the code we're going to use.
Please select the product column.
And because the product column exists in both tables,
we have to preface it with the actual table name dot
product, comma.
And then we specify our aggregate calculation
just as we did in Power Query but in SQL it
comes in the first line with the sum function wrapped around it.
Then we'll use as, and that's the name of the new column.
Then we say from which tables.
Well, F transactions is one of the table, but guess what?
We're going to have to explicitly join,
so we say join with D product.
And because there's a relationship
between these tables one to many,
we can simply say on and list the two columns as equal.
Notice for both of these product columns,
we had to preface it with our table name.
Where?
That's actually filtering the quantity column
in the F transactions table.
Group by, we simply say group by product.
Because there's an aggregation already in the first line,
it will know to do that aggregation
for each unique item in the product column.
Then because we want to filter the result,
we say having, and we repeat the aggregate calculation
and give it our comparative operator and limit.
Finally we say order by.
And because the default is ascending,
we simply list the product name followed by a semicolon.
When I click OK, the actual finished report
will be return to Power Query.
So I click OK.
And there it is.
I'm going to click Edit.
Now I'm going to name this query, something like product
report typed SQL code.
Now I do want to go look at the first step here.
We can also go up and look at in advanced editor,
but here it is, SQL database.
Well there's the server.
There's the database.
And look at that.
In square brackets, there's a query.
And the query is all in double quotes.
Now if you want to change the query,
you could edit it up here, but probably it's
easier to come back to the gear icon and double click.
And you can edit it here.
But let's take a look here.
It lists the select statement, and when
it gets to a line feed, that's line feed in Power Query, pound
and then in parentheses LF.
And so for each one of our steps, there's a line feed.
Finally, double quote at the end and there's our time out.
Now I'm going to close and load, close and load 2, existing.
And it looks like it has the right place.
O 11, click OK.
Now we have both of our reports, user interface SQL.
Now, of course, most of the time,
we're going to be doing Power Query user interface.
Because of course when possible, those steps
will be sent back to the SQL database.
But if you want to write code, it's totally possible.
Now I'm going to close this Excel file,
and we're going to open up Power BI Desktop.
Now Power BI Desktop, the free download,
I just opened up a blank workbook and saved it.
We go up to get data if we're trying to connect to an SQL
database, and look at that.
It's right there, one of the first options.
So I'm going to select that.
This looks exactly the same except for one big difference.
There's import and direct query.
Now direct query allows you to connect to an SQL database,
and you do not import the data.
Here, of course, the data's brought into the file.
If you use this, performance can be very slow sometimes
because every time you do a query or change
something in one of your visualizations,
it has to send that request back to the SQL database.
Now the real reason that this exists
is because for really big data, like maybe you
have a gigabyte of data you're trying
to import, that can be a problem in terms of importing.
So then you want to use direct query.
Now there's some other considerations also
like some of the amazing time intelligence
functions that we'll learn in DAX
don't work if you use direct query.
Because remember some of the things
that we do in Power Query and Power BI Desktop and DAX also--
if you try to send them back to an SQL database,
it might not understand.
Now there's a link below in the comments
and in the PDF notes from Microsoft
about all the things you want to consider
if you use direct query.
We don't have anything big enough in this class,
so we're simply going to import.
I went ahead and put my credentials,
put a minute time out.
I'm going to uncheck this because I do not
want related columns.
Now I'm going to click OK.
Here's the navigator.
We want to select F transactions, D product, and D
country.
I'm going to select edit to bring it into the Power Query
editor.
Now we have 1, 2, 3 different separate queries.
But again over here source, it's doing the same thing.
And we can see one extra step here,
create navigation properties equal false.
That's in the square brackets with our time
out command because we didn't want related column.
And, of course, there's our table,
and navigation looks up the correct three tables.
If I select each table, I can see a different lookup formula.
I'm going to click this refresh.
It looks like it's been two days since I touched this database.
Now you can do any transformations you want.
I'm just looking through and verifying.
The columns are there the data types are there.
Those are looking good.
D country are looking good.
Now over here it's close and apply,
so I'm going to select close and apply.
And this will import the tables into the columnar database
in the data model.
I click close and apply.
You can see it's chugging through all of the rows.
Now if we come over on the left because we
did an import and not a direct query,
I can actually look at the tables.
Over here, there's country, product, and F transactions.
If we go to relationships, oh, look at that.
It detected one of the relationships.
We're not going to do anything with this right now.
Actually we will start talking about Power Pivot data
modeling, which is the same over here in Power BI
Desktop in about two videos.
All we've done here is we went ahead and imported the tables.
So all we did up in edit queries was source and navigation.
That was a lot of fun with connecting to an SQL database
either over here using Power Query in Power BI Desktop
or over here in Excel where we use Power Query user
interface and SQL code.
If you liked that video, be sure to click that thumbs up,
leave a comment, and subscribe because there's always
lots more videos to come from Excel Is Fun including we'll
have probably one, maybe two more Power Query videos,
and then we'll jump into the introduction
to Power Pivot and data modeling.
We'll see you next video.