How to Ingest SQL Server On premise data to Microsoft Fabric Lakehouse Object
Show More Show Less View Video Transcript
0:00
Hello and welcome to this Microsoft Fabric tutorial
0:07
In this tutorial, I'm going to show you how to ingest data into the Synapse Data Engineering Lakehouse object of Microsoft Fabric from online environments such as SQL Server
0:21
So, let's get started. Now, in order for you to be able to try this out, you need a free Microsoft Fabric account
0:29
which you can easily create. My case, I have my own personal tenant, so I am properly logged in
0:35
Now, when I click on this icon, I can see my login details and of course, I'm using
0:39
the trial version, which allows me to try different objects within the Microsoft Fabric technology
0:47
Now, before we dive in fully, I'm going to head over to my SSMS, the SQL Server Management System
0:55
So, in the SSMS, I need to provide my server name and the authentication, such as the Windows that is associated with my personal laptop
1:05
I can even use the SQL Server authentication. Of course, all this will set up when I install SQL Server on my local system
1:13
So I can use the system administrator and then I can put in my password and just click on connect
1:19
So you can see I'm connected to the SQL Server instance and of course you can see the Object Explorer
1:25
And I can click on this database to expand and see all the databases within my SQL Server
1:30
And of course I want to point to the sales data database and we actually want to connect to this fact sales table
1:37
Now I'm just going to click on new query. let's see all the records within the fact sales table that we actually ingest across in the cloud
1:45
service of microsoft fabric now by default the sql server management system going to point to the
1:53
master system database now we need to use our own sales data and just click on execute and then we
2:00
are right now within the sales data database now we can just write a simple select all state names
2:07
to fit all the records in the fact sales table so click on execute so these are all the records we
2:15
have 55 552 rows and that's fine so let's go back to the microsoft fabric now in microsoft fabric
2:24
the first thing i want to do is to create a personal workspace now to do that at the bottom
2:31
left and corner of the screen you're going to see this microsoft fabric icon so click on that
2:37
uh we'll point to rbi now in the power bi we're going to create workspace click on workspaces and
2:45
click on new workspace now we need to give name for the new workspace we're going to call it lake
2:51
house all other things are optional you can even click on this advanced and you can add users and
2:56
and groups to the workspace and of course you have to specify the license board I actually using the trial version so which is available for 60 days so click on apply all right so we need to create the workspace so the next thing we going to do
3:13
is to switch to the synapse data engineering component of the microsoft fabric so click on
3:20
data engineering and then we have the lake house so just click on lake house preview and then we
3:27
we can just give it a name let's call it SQS server and then we click on create
3:33
we are now within the SQS server lake house that we just created now we can go
3:40
ahead and fetch data using different kind of method or channels I'm gonna click
3:46
on get data I can upload the files directly if anyone set up new data
3:52
pipeline and use data flow gen 2 and the data flow gen 2 is exactly the same thing as power
3:59
query that is embedded in Microsoft Excel and Microsoft Power BI however this is online cloud
4:08
based power query service okay it has some more functionality than the desktop versions so I'm
4:16
to click on new data flow gen 2 right now we are in the data flow 1 so this is a familiar environment
4:27
the power query okay now because we actually want to get the data from sql server you can click on
4:33
input from sql server from here or click on get data and choose sql server database now we need
4:39
to provide the name for the server we can even optionally provide the database name and of course
4:45
we need to provide the connection credentials however we do not have any power bi data gateway
4:52
already settled now in order to be able to connect to on-premise data set we need to create a power bi
5:00
data gateway so i'm just going to cancel this i'm going to come back to this one later i'm going to
5:05
close this and i'm just going to exit so i'm going to come back to this bottom left here click on that
5:12
and choose power bi i'm going to see this icon to download so click on that and they want to choose
5:18
data gateway now we can see we can connect to on-premises data sources with power bi
5:27
gateway okay now we can easily download the download standard mode now i have it downloaded
5:34
already so it's actually here so what you need to do is to go ahead and install so you just double
5:40
click on that we can see the on-premises data gateway installation it's quite easy so it's
5:45
going to be installed to this destination which is fine just go ahead and click on i accept the
5:50
terms of use and privacy statements so click on install so you're going to actually click on this
5:56
yes to make changes to your personal device so just go ahead and click on yes that's fine
6:02
so you can see installing your on data gateway just wait for some few seconds for the installation to conclude now it fully installed on our personal laptop now you
6:17
can see installation was successful now we need to provide the email address to
6:22
use with this gateway now the email address must be the same email address
6:26
that we use you know in it that is connected to our tenant so I'm just
6:31
to come back here now this is going to be my email address excel jones
6:43
now this is going to be the email address my own personal email address
6:46
or my tenant so just go ahead and click on sign in
6:50
right so you can see you are signing as blah blah blah
6:54
and you're ready to register the gateway now that's probably fine so
6:58
register a new gateway on this computer let's click on next so i need to provide a name for the
7:05
on-premises data gateway so let's just call it sql server microsoft fabric okay now we need to
7:16
specify the recovery key now i'm just going to type in just like my password but i can actually
7:22
remember easily so we confirm the key and then when you're good to go click on
7:27
configure so we can see on-premises data and gateway the gateway SQL server
7:33
Microsoft Fabric is online and ready to be used so we can see the version of the
7:39
gateway so everything is working pretty fast power automate and of course the
7:43
power bi default environment now my tenant is actually registered in the
7:49
United Kingdom South so I can see all other things like the service settings
7:54
diagnostic network connectors and of course the recovery key so go ahead and
7:59
close this tab so I'm just going to click on get data and I want to point to
8:05
SQL server database so I need to provide a name for my server now when I swipe
8:12
over to the SSMS I can see the server name at the top here so I'm just going
8:18
come back to the Microsoft Fabric and type in my server name and of course I can optionally provide
8:25
the name of the database which is in this case sales data I can even write in a SQL statement
8:32
in the power query online just like I've done lots of videos on writing SQL in power query that
8:40
is embedded in Excel now I'm just going to scroll down for the connection credentials I'm just going
8:46
point to the data gateway so click on this drop down I can see the on-premises
8:51
SQL server Microsoft fabric that I just created now that's pretty fine and of
8:58
course for the authentication kind they can use the windows and of course
9:02
can specify the area but let's just click on this drop down you can even use
9:06
in a basic and I just going to type in SA and then for the password I going to just type in my password and click on next
9:21
All right, so we can see get data, choose data. So these are all the tables within the sales data database
9:31
That's amazing. Now I'm just going to check this box So you can see the preview of the fact sales table in it, which is awesome
9:40
Now what I'm going to do is just click on Create Amazing
9:46
So we can see the data in Power Query Online That is super cool
9:53
So that is the extraction from the on-premise SQL Server To the cloud service Power Query using the Gen2 connector
10:03
So now we can just perform different kind of information that we want to do here
10:07
Now, probably I want to change this date time data type to date data types
10:12
I'm going to click on this icon and I'm going to choose date. And of course, we can see the steps being recorded in the applied steps and everything is fine
10:24
So we just go ahead and need to publish to the Nick house
10:29
okay so when i click on this icon i can see we can publish right now or we can even publish later
10:36
so let's go ahead and publish right now all right so this is the lake house workspace that we created
10:43
so we can see the data flow one flow two and of course we can see the sql server we can see the
10:50
default data set and for we can see the sql server sql endpoints now in the sql server endpoint we can
10:57
write sql statement against the data set in the lake house so when i click on that i can see the
11:05
fact sales table that is ready to be used within the lake house that is super amazing and of course
11:15
you can just click on this new query to begin to write new query you can even click on new visual
11:21
query and create your measures let's just play around and write some few queries against the
11:28
data set so click on new query and that's you know the same thing as clicking on this
11:34
new query in the ssms so back here we can just write our simple select
11:41
from and then we can copy and even graph just like we do in ssms just grab that and then click on run
11:49
and that's amazing so we have our record in working pretty far this is basically how we
11:58
can connect to datasets on SQL Server on-premise environment to leak house in Microsoft Fabric
12:07
I hope you enjoyed this video if you do please like share comment and
12:12
and support this channel thank you and bye for now Cheers
#Educational Software
#Computer Education


