[0:00]Bismillah Rahman Rahim. Hello everyone and welcome back to the PL300 exam preparation series, where we are discussing the first learning path, which is prepare the data. In this video, we are going to connect to the SQL server and connect to a database within the SQL server. So, before we actually go and connect to the SQL server, you need to install two things on your computer. First is SQL server and then is SQL server Management Studio. So let's look at how we can do that. So here on my screen, you can see that I have a YouTube video that has been opened in my browser. And this is the link to the YouTube video which I am going to paste in the file which is part of the supporting material and you can just go and copy this link and watch the video. So this video is about installing SQL server and the SQL server management studio on your computer. So you would just need to watch this two minute video and you're going to understand the entire process. First, you need to go on this Microsoft link, which is shown here in the browser. So this link is going to take you to the SQL server installation. So here you are going to see SQL server on Azure, SQL server at the edge and SQL server on premises. So we are mainly interested in SQL server on premises. So just go and click on this download now link and install the SQL server on your computer. So once you are installing SQL server, you are going to find a link where during the installation process, it says that install SSMS, where SSMS stands for SQL server Management Studio. So just follow the video and install SSMS on your computer. Once you have installed SQL Server Management Studio on your computer, this is the view that you are going to see on your computer, once you execute the SQL Server Management Studio. So all you need to do is that you just need to go and click on connect and this is the connect to server window that would most likely appear on your screen. So here the setting should be that the server type should be set to database engine. You would have a default server name that you gave while you were installing SQL server management studio and then the authentication should be set to Windows authentication and then all you need to do is to click on connect. Once you click on connect, then this is the view that you are going to see on the left side of the SQL server management studio, where you are going to see the object Explorer. And within the object Explorer, you would have databases. So initially if you do not have any database installed on your computer. So assuming that you are installing SQL Server Management Studio for the first time, then this area would not have anything. So now I am going to direct you to another link from where you need to install the Adventure Works DW 2019 database. So now we are going to have a look at how we can install the Adventure Works sample database. So this is the link that you need to go to, which is already placed in the supporting material and if you come to this link, then if you scroll down below, you are going to find an area where these free sample databases have been provided by Microsoft. So we are actually going to target this Adventure Works DW 2019 database. So just come to this link and click on and click on this link and once you click on this link, this DW 2019.bak file would be, which is the backup file would be downloaded onto your computer. So I am back in SQL Server Management Studio and as you can see that I already have the DW 2019 database installed in my SQL Server Management Studio. But for you, you can just go and click on databases and then you need to right click. So here you're going to find an option which says restore database. So once you click on this restore database, it is going to open up the restore database preview window. So here you just need to go and click on device. And then you need to go and click on the three dots here. So once you click on these three dots here, then you need to actually go and find the dot back file. So here you just need to go and click on Add and then wherever you have actually copied that file, just follow that path and you are going to find the the back file, which would be Adventure Works DW 2019.back file. So just go and click on this select this file, then click on okay and then click on okay and then the rest of the process is very simple. It is going to just take a few seconds and the database would be installed on your SQL Server Management Studio. I am not going to repeat this process because I already have the DW 2019 database installed in my SQL Server Management Studio. But this is how it would look like once it has been installed on your SSMS. Now we are going to go inside of Power BI desktop and now we are going to come and connect to this on premises SQL server Management Studio interface and we are going to connect to the SQL server and then we are going to connect to this particular database and we are going to bring in some data from this database. So now I am inside the Power Query Editor and I am just going to go and click on new source and here I am going to find the connector for SQL server. So if I click on the connector for SQL server, it is going to open up the window where I would be asked to give the credentials for my SQL server database. So here we are going to connect to a database for the very first time. So important thing to note here is that the only thing that you need to pass here, the mandatory thing is that you need to pass the address of your SQL server. And in our case, because this is an on premises SQL server, so we are just going to give the name of the server. If this SQL server would have been hosted in some cloud database or some in some cloud, then we would have given the complete credentials for the for the cloud. So that we are not going to do, but just to remember that you just need to provide the name of the server depending upon whether that server is on premises or hosted in the cloud. The other thing to note here is that you haven't seen anything which is called data connectivity mode, which is giving you options. So here we are getting an option for the first time which says import or direct query. So we have a complete video explaining the difference between import and direct query. So we are not going to discuss this here. So I am not going to change this setting here. So only thing that I need to provide here is the SQL server address and if I want to provide the name of the database, which is optional, I can also provide this here.
[8:04]There is another option which says advanced option. So if I just expand on this advanced options, I am going to see two further areas. The first area shows command timeout. This is the area, command timeout in minutes. So here I can specify a time which actually is the time for the timeout of my query. So this time is important because now we are going to connect to an actual database and if there is no response coming from the database, then the time out is the only way that we can disconnect the connection or we can come out of the connection with the server with the server timeout error. So you can specify a time in minutes here. The default time for this setting is 10 minutes. The other area to look here is this area, which says that you can actually write an SQL statement. So those of you who are familiar with what SQL means, so SQL stands for structured query language. So this is the language that is used to query the databases. So if you know about SQL, then you can write an SQL statement here and that SQL statement would be executed once I click on this okay. So you do not require to write anything here, if you are just trying to make a direct connection with the database. Then there are two flags here that you see on the bottom. The first flag says include relationship columns and the second one says navigate using full hierarchy. So we haven't talked about relationships as yet, but just to mention that there are certain columns in tables which are used to create relationship between tables.
[10:16]So if you select this flag, then you are going to see everything inside a database, whether the table has any records or not. So if you do not select this flag, then you are only going to see the tables which have some kind of a record. So I am not going to do anything with any of these settings and I am just going to click on okay. So this is going to open up another window that we have already seen once we connected to the SharePoint and this is the window where we have to select the option for the credentials. So we have another video where we are going to have a look at all the possible credential options, but remember, once we connected to the SQL server database, we were connecting using the Windows authentication. So this is the authentication that you need to select here. So just go and select the Windows option here and then you can go and select the current credentials which are used to connect to your Windows machine and then you just need to go and click on connect. After a few seconds, this is going to open up the navigator window, which we have already seen, but now instead of seeing Excel files or SharePoint directories, we are actually seeing databases. So the database of our interest is this one, Adventure Works DW 2019. So I am just going to go here and expand this database. So once I click on this expansion icon, then I am going to see all the views and all the objects including tables which are inside my database. So I am just going to bring in a couple of tables. So I am just going to click on DimCustomer, DimEmployee and DimProduct. So these are the three tables that I have actually selected and I am going to see the preview for each of these tables here and then I am going to click on okay. After a few seconds, I am going to see that the three tables that I connected to can be seen inside the queries section on the left, DimCustomer, DimEmployee and DimProduct. And if I go and click on the source applied step, I am going to see that now I have a different M function, which is SQL.databases and then you can see the name of the SQL server on my machine. So we can see that the rest of the process here looks similar to what we have actually seen for all the other data sources. But this is how you can connect to an SQL server database. Similar to an SQL server database, you can connect to any other database including Oracle, IBM, MySQL and host of other relational databases. The process is the same, all you need to do is that you just need to select the right connector and then pass the credentials. So this is how you connect to an SQL server database. We are going to explore this topic in detail once we are going to talk about the storage modes, where we are going to have a look at the import storage mode and the direct query storage mode, but that is the topic for another video. So that is it for this video and I will see you in the next one.
