How to connect POWER BI with SQL and build an AUTOMATED Dashboard [SQL to Power BI]

what's up you later friends is Yanni's here and welcome to my channel in this video I'm going to show you how to fully automate the power bi dashboard using SQL which is something that you will probably need to know how to do as a data scientist or a data analyst maybe it's not power bi and it's click or tableau but the idea is the same I'm going to start this video by explaining how to automate the raw data input so from raw data in Excel into SQL and then how to schedule this process to run itself then I'm going to show you how to connect SQL with power bi and how to create this automated dashboard in order to run itself so every time you have Muro data into excel SQL is going to pick it up update the database and then send the data into power bi where we can see the updated data and before we continue with this video let me just say that if you're new to my channel and you're passionate about data science then please consider liking this video and subscribing to my channel and enabling notifications for my future videos right the first thing you want to do is to open SQL Server management studio and connect to your database now if you don't know how to use SQL Server management studio I'm gonna have a link in the description of my previous videos where I explain how to download SQL and SQL Server management studio how to create a server how to create a database and how to run SQL queries so I go through the most important SQL queries so as I just watching that if you don't know how to use SQL Server management studio right the first step now is to create an empty table so we can take the raw data from Excel and input it into this empty table and just to quickly go over our raw data this is a file that I have downloaded from a public data set online from kaga and it basically has some indicators which is things around GDP and population health country and per year and then device so what we need to do now is to take all these column names go back into SQL and create table this is a new table which I'm going to name it as raw data GDP and I'm going to specify all the column names so as you can see demo int is actually the column name of this one then indicator location country then over here we have indicator location country etc etc and next to each column name I want to specify the type so this is a character with 200 characters again a character character character character here on the value actually half load because this is float is is a number and then I have again character character and then I close my table and then I create this table this code at the top what it basically says is that if this table which is this table is not null so if it exists then I want to drop the table in the reason I have this code like this is because I can rerun it as many times as I want so if we quickly run this table above so if I copy this and paste it about here and they're running so select everything from this table we can see that we don't have any data in it it's just an empty table right the next step now is to import the data from Excel into these new empty table and to do this you will need to do a bulk insert into this table name from and this is going to be the path that you have your CSV file safe with format equals CSV to do this now there is a few things to know the first thing you have to do is to go in the location that you have saved this file and then kobe these paths or ctrl C and then ctrl V into your code over here until here and then have a slash at the end with the name of the file so this is actually the name of my file which is this one over here and I also have a dot CSV RTL a species a CSV file second thing you have to know is that your file which is this one over here you have to delete the column names as you can see I've deleted the column names and this is because we have already specified the column names up here the third thing you have to know is that you will need to actually close this file in order for the query to work so if I try and run it with the file open I should get an error as you can see I get the error over here now if I close this file which is my raw data file and then I rerun the query so I go back and rerun this query you can see that it works fine and if I select everything from this query now you can see that we have all the data that we had in the CSV file which is this raw data over here right the next thing now is to create a view that is only going to have the data we need so we can send it into our dashboard just because let's say we don't want to send all these raw data into our dashboard we want to write a query that selects some of that data and it applies some applications do this I have created this view down here and just to explain how these join what my first table table a if I run it I have country year and the GDP by you my table B if Ronnie is going to have country year and the GDP per capita and then I join my a and my B I actually left join it or country and then on here and then if I select everything from the top so a dot everything in B dot TV paper Canada I have country here GDP value and GDP per capita and I'm going to create a view and name it as GDP Excel input and then save this query into this view so if I run this view I should get an error and this is because this view actually exists I have rerun this before so if I quickly drop the view again and then rerun it so the first time you you don't have to have this drawer over here and then you don't need to have it ever again you only create this one and that's fine and as you can see it works fine so if I select everything from this view now we can see it pops this is not the view this is a distinct indicators select all from could be used and if I run this this is our new you consider to have country yeah GDP value and GDP per capita and we want to send this view into our dashboard now so we can automate our dashboard right the next step now is to create a stored procedure so we can actually schedule all these code we just need to run per day or per week or per month etc etc so what I did over here is that I have copied step one so this is step one and copied step two I don't need to copy and paste step three into the stored procedure as the view have created it's always going to get the latest data from this table and this table is actually the table we have just created so down here where we have the stored procedure I only copied and pasted step one and step two and at the top I say create procedure and then I give it a name and then ask and then I run this and then they should give me an error again because I already created this third procedure but if I drop it and then rerun it it is going to work fine and just to test it if I say execute procedure and the procedure name and if I run this what this is going to do is going to run step a and then it's going to run step B so it's going to drop this table if it exists and then it's going to bring all the raw data from our excel file and with this stored procedure you only have to create it once and the next step will be to schedule it to run itself but to schedule it we have to create a job into our SQL Server agent now if you're not able to see the jobs down here then you will need to go into the SQL Server Configuration Manager we're going to start a new typing SQL and it should show up and you need to make sure that your server I'm using zero eight is actually started so you right-click and you click start otherwise these jobs activity over here is not going to show in your SQL Server management studio and since we are here again we need to confirm that the server eight and the server eight on here is actually running the next step you have to enable here is that you will need to go into the actual server over here and make sure you have this TCP / IP enable as this is going to enable us to connect SQL with our front-end visualization tool which could be Excel or power bi or any other visualization to to make sure you enable these and you also start your SQL server over here another thing you will need to do just to make sure that your SQL Server is going to always start over here itself so you don't have to go on and start it yourself so the schedule is going to work fine then you will need to double click on your server go into service and change this start mode from manual to automatic so what this is going to do you click apply and then okay is that it's going to start the server every day itself so you don't have to actually start it from here hence all your scheduled jobs are going to run daily without you having to start this yourself so you know daily depending when do you schedule them right after configuring all these you will have to go back to SQL Server management studio and then close it and reopen it so the SQL Server agent is going to appear the next thing to do now is to create the job and scheduling so we right click on the SQL Server agent and click on new job here we have to give it a name so I'm just gonna say job test one then we can also give a description if we want to the next thing will be to go to down two steps and click on you and here is where we are going to specify our steps so step name is going to be I'm just going to say step what it's going to be a tea SQL script around us I think we can leave it as it is and down here we need to specify the database and the database is going to be this database we use over here so i select my database and then the command the command is going to be this command we have over here so we actually want to execute that store procedure so if I copy this exact and then the procedure name which is the procedure name over here let me just quickly double check it yeah this is the one I go back into the job and I put it over here and then I click OK and then if we go down to sketches now we create click on new and then here me to give it a name so this is going to be I say just schedule one this is going to be recurring that's fine this is going to occur every and over here I can set it everywhere I want this to run I'm going to say every week I want to run this every Monday let's say yeah we need to specify the time so I'm just going to say 7 a.m. in the morning and then I click OK the next thing here would be if I want to set any alerts and then if you want to set any notifications but we don't want to do that for now then we want to click OK and then if we go down to just now we can see we have this job test one who have just created you can actually double click it and inspect it or change it or adjust it as we want right so now we have created and scheduled our job we want to go back into power bi and connect power bi with this view you have created before that has all the data we need so very quickly I'm going to show you how to create the connection and how to create this dashboard over here to create the connection now you want to click on get data and then select the SQL Server into a server you want to go back into SQL click on disconnect and then get this name from here so I'm just going to copy it quickly then go back into power bi paste it over here the database name is optional but if you do want to put it in then you can select this database you're gonna have over here then I hope I'm into power bi I click OK then in this navigator you are going to see all the databases you have within the server but I only have one database and now I want to choose the name of that you have created which is GDP underscore Excel input which is this one over here GDP underscore Excel inputs particle bhai I selected I can inspect it very quickly so I can see all the data and then I click on load and then what is it if I go back into my data over here once it finishes but go back here we can see the table name over here I actually have two and there is enough to is because I already did this so you will only see one word here and you are going to see your four fields very quickly now you want to right click into these and click on edit query and we want to go and check the data types but this side into transform you can see this is text is fine this is text which is not fine so I want to make this into a whole number because it stay here this is a by you but it did decimal numbers is correct and this is it doesn't number so it's correct so we want to go back into home and click close and apply but now we have created our connection and change the data types the next step now is to create these visualization and in order to show you quickly how to do it I'm going to go into here click on page size and then increase the size of the page I've also increase the width into 1500 and I'm also going to do 1,500 so I have some space down here to show you how to create stuff the first thing I did is that I go into insert into a text box where I have my title then I also go into insert into images in order to add this image over here and then I also go into insert into shapes I select a line to create this line you see over here the blue and then into the rotation of 90 so it's like a horizontal line not a vertical and I have also changed the color of the line to this one over actually this one over here if you want to change the palette themes you can go click anywhere else in the report go into view and then from here you can change the palette themes I tend to use the classic one over here right to add these three slices now we want to click on the slicer over here and then into the field we want to start with the country from the cemetery wall so as you can see we have the countries now which is similar to this slicer over here next I want to click on these three dots and add the search so we can actually search for countries now so Cypress for example same case with this top certify to side cruz and then selected everything should readjust as you can see I want to unselect it and go back these also want to do but here next I want to click on it go into items and add the background for all the fields so as you can see we have this background I think it's more Louis I think is this one over here the next thing I have is that I add a border so I go down to the border over here I clearly need and I added which is this port over here there you go you can see the water next thing I have is that at the title so I enable the title and I remove the slice and header and in the title of the title of this country also change the background of the title to this zone and the phone to be black and also put it in the middle and put it under 12 there we go so it's exactly the same I just have to resize it now to feed at the top right right to create these two types of slices now you want to create these slices as it is and go into a general thing and change the orientation to be horizontal so as you can see we have exactly the same slicer now but in this format over here so we can just click on it and test how it works as you can see it works fine and so you can copy this paste it twice and then change the country with the year and you are going to help these two slices in this type and then this country slicer in the searchable time to help over here the next step now is that delete this quickly is to create this line graph and in order to create it you just click on this visual at the top left you add the year into axis into values you want to add the GDP by you then you want to go into the data colors change the color to be this color over here then I also added the data levels and change the colors of the data labels and then you want to keep scrolling you want to go down to the title you want to add a title you want to change the background color the font the alignment and the text size of the title to be exactly as a habit over here and then you also want to go down into the border and not the same border color which is this one over here as we have with our slices in order to add these format by the way very creepy you can click on one of the slices go into format painter and then touch on this visual over here and then the border and the title and the color and should be exactly the same as this one here the next visual you have is a map so a click we click on the map over here we want to drag into location on the country and into size we want to drop the GDP value we also want to touch on this visual touch inform a painter and then touch on the map and what this is going to do is that it's going to add the same color Porter and the same type of title so you can go and change the name of the title then you want to go into the bubbles and make them into minus 22 and you can also change the mouse ties to these styles we have over here I'm going to leave it as light and the last thing I have is that I changed the color to be red the final visual will help over here now it's just a simple table and then into this simple table we want to drag the country that GDP by you and the GDP per coming as you can see over here you also want to clip on GDP value in order to order it from largest to smallest then you want to touch on this visual touch on format painter and then touch back on the table in order to add the border and the back same coloring on the title then you want to go over here and change the title name and the last thing I have is this conditional formatting over here and in order to do it you want to go under conditional formatting then you want to select the GDP per capita then you want to enable the background color so if I disable it and enable it quickly you want to click on advanced and you want to change this into to lighter colors as I have over here so it's easier for the eye to see numbers right if you want a more slow and detail power bi tutorial on how to build power bi dashboards I'm gonna have a few links of my previous video in the description so you can go and watch those as the aim of this video is around automation and not on how to build a power bi dashboard right now what I want to do is to test if these automation we have just created works correctly so what I'm going to do is I'm going to go back into the Excel raw data and delete any data after 2010 so any data from here and after we want to delete but to do this I go back into my Excel files which is this one over here let me see is this one over here then I want to add a new line at the top I want to go on and filter it on a filter pie here want to unselect everything and I only want to select everything up until 2010 okay and then I select all of these lines and I delete them right click delete rows right so now I want to unfilter this so select everything and I want to delete this line at the top I want to click on save and I want to close these files so we can execute our stored procedure so we'll go back into SQL now we just execute our stored procedure and then if we go back into power bi right-click on our table and click refresh data we see straight away that any data after 2010 so the last date we have is 2009 is God so you can see that all this automation worked within severals now I want to go back into Excel I want to delete this one and I want to change the name of this to be the actual one so this file has all the data so click OK I want to execute our sort of procedure again with all the data so this is what new day that comes in and then if I go back to power bi as you can see no data after 2010 and if i refresh our view quickly we should have new data coming in there you go so you can see we have data until 2017 so you consider all these automation and the process with a father would work within seconds and it works perfectly so this is why data scientists need to know how to automate all these things write a reference so this is the end of this video so I hope you enjoyed this video and you've gained enough value out of this video and if you feel like you did please click the like button subscribe to my channel and enable notifications for my future videos if you have any questions please let me know in the comments below otherwise thank you very much for watching this video and I'm gonna see you in the next video

Channel: Data 360 YP

Views: 224,457

Keywords: How to connect POWER BI with SQL and build an AUTOMATED Dashboard, SQL to Power BI, How to connect Power BI with SQL, How to create an automated Power BI Dashboard, power bi to sql server connection, power bi gateway, power bi, how to automate power bi reports, sql vs power bi, power bi sql, power bi sql tutorial, power bi sql server, automating power bi, how to use sql and power bi, how to automate power bi refresh, how to automate power bi dashboard, data360yp, sql power bi

Id: WgE8pVLgWGs

Length: 23min 23sec (1403 seconds)

Published: Fri May 08 2020