Comprehensive Guide to Azure Data Engineering

Hello everyone and thanks for joining. So in this particular video we are going to do an end to end azure data engineering project. So most of the people were asking me to create one end to end azure data engineering project and try to include all the possible skills which we generally use in real time. So what I have done I have created this particular project for you. Here we are going to use a SQL server, we are going to use azure data factory, we are going to see how actually we can use a data bricks, we will see about the PySpark and then we will go for the visualization tool that we are going to use as a Power BI. So every skill which we are going to use in this particular project and this will be the end to end. So what actually our agenda is like first we will try to understand the business requirement and we will try to understand the data and we will think about how actually we can solve the problem and then we will go for the practical. So at the end we will create one this kind of the dashboard before starting the project if you are new in this particular channel please do like please do support so that the more videos like this I will try to post. and please do comment so that the more videos like this i'll try to post it okay so let us start let us start with the business requirement so our business requirement is like we have our data that is available into the sql server okay in our on prime and what actually we want to do we want to move into the cloud space that is our first requirement from on-prem sql server we want to move into the cloud that is our first requirement the second requirement is like we have to build one pipeline which will transfer our data and run daily that is the second requirement okay the third requirement is like once you have data that is available into your storage account right so we have to connect it with the help of the databricks how actually you can connect databricks with azure account that we will see and after that what actually we will do we will try to build one aggregate for the reporting. So we have to create one reporting for our business stakeholders and we will try to provide all the kind of the KPI so that the business can take a decision. So for that we will try to create an aggregate with the help of the Spark SQL and the PySpark. And we will try to build certain KPIs. And the final step is like we will try to connect with the Power BI. and then we will try to show the visualization so this all the things actually we are going to do and that this is all our business requirement okay if i will show you so we have a sql server in this sql server we have a data that is available that is our on-prem okay what we have to do we have to create azure data factory pipeline and we will try to move data from a sql server to the storage account okay once we have data that is available into the storage account we will try to connect we will try to mount with the data bricks Okay, with the help of the PI spark and then after once you have connected with the data bricks, right? With the help of the spark SQL, we'll try to create one aggregate table and based on the aggregate table We will try to connect with the power bi. Okay, so let us start with the project So what actually we will do we will try to set up a project Okay, so you also do along with me so that you will also will be in the same page. Okay so what we will do first we will try to go to the sql server we will try to create one database and we will try to create one table so how actually we are going to create a table by uploading a csv file okay so i will go to the sql server i will create one database create database sales database and i will use that particular sales database once you will do that you can see left side right the sales database got created okay after that we will try to upload one file so right click on the sales database go to the task click on the import flat file and then we will try to select the particular file okay so this is the particular file i have i will select that click on the next after that you will be able to see all the data click on the next and after that let us try to change the data type here I'll try to change into the orders details ID and order ID and let us see if any error comes okay and click on the finish once you'll do that you can see insert data successfully close it and after that once you will refresh this particular database right you will be able to see one table will which got created ok this is the pizza says we have created if I show you select star from pizza sales ok if I will execute this you will be able to see all the data so this is the data we have we have order id pizza id quantity order date order time unit price every details we have now we have our data that is available in our sql server okay and this data actually we want to move into the cloud space okay so what we are saying we have our data that is available into the on-prem and we want to move into the cloud okay so for cloud you We will open a Azure cloud. Now what we are going to do now? We are going to create one storage account. There actually we will transfer this. So what we will do? We will search for the Azure storage and we will create one Azure storage account. Search for the Azure storage account and let us try to create one storage account. Click on the create storage and click on the resource group if you don't have resources click on the create new and try to create One resource group ok and click on the storage account, so I will give any storage account. You can also give any name ok Click on the next next next next next review and after that our storage account will be created we have to click on the create you can review what we have selected here okay and click on the create once you will create this your storage account will be ready okay you can see initializing deployment deployment is in progress once it will complete your storage account will be ready okay You can see this is the deployment is in progress. Let us wait it to be completed. You can see deployment is completed. We will click on the go to resource. Once you will open the storage account right in left side you can see container we have. Here we will try to create one container. In this particular container only we will try to store our data from a SQL server to here. So what I will give the name as a input container or I will give as a name as a raw. So this is the raw file. So raw container I am creating click on the create once you will create this right so container is nothing but it's kind of the folder which got created and as of now there is no file available here okay so once we will develop a pipeline right we will try to move data from on-prem sql server to this particular location only okay now let us see what is our next step our storage account is ready our sql server is ready now we will create a one data factory how actually you can create a data factory click on the search click search for the data factory Search for the data factory. Click on the create data factory. Resource group select that. If you don't have click on the create new and give the data factory name. We will give POC data factory or something name. Okay. Okay. And after that I will click on the review and create. Click on the create. You can see deployment is in progress. Once it will complete, our data factory will be ready and then we will go for the data pipeline setup. Now we can see our deployment is completed. So what we will do, we will click on the go to resource and we will try to open our data factory studio. And in this data factory studio only we will try to create a pipeline and we will try to move data from our on-prem SQL server to the cloud space. Now once you will open a data factory studio right this will look like this what actually we will do here only we will try to create a pipeline so you can see in the left side right click on the author go to the author you can see we have an option like the pipeline okay click on the new pipeline. time. Click on the new pipeline. What actually we are going to do? We are going to perform a copy activity from a SQL server to the our cloud space. But that is not easy because SQL server is on-prem server and from on-prem server if you want to move into the cloud space you have to create an integration runtime that actually also we will see. So first let us see what is our source. okay so what is our source so let us try to select that source is a sql server source is a sql server so i'll select the sql server click on the sql server click on the continue link service so what actually we are going to create we are going to create a sql link service so that we can connect with our sql server so click on the new okay here we have to provide all the server name okay so you can see first option is like auto resolve integration runtime by default it is auto auto resolve but because our data is available in on-prem server right so we have to create a new click on the new we have to we are going to create one integration runtime that is the setup we have to do okay so self-hosted we have to select click on the self-hosted click on the continue okay so this is the integration runtime we are going click on the create once you click on the create right you can see we have one option the manual setup so download and install the integration runtime so you have to click on that okay now your uh what actually you have to do you have to click on this download button Okay, and select the first one integration runtime and click on the download Click on the download then the download will start. I have already downloaded I'll now you can see this is the integration runtime which was downloaded. So I'll double click this You can see installation is in progress. So we have to install this Click on the next Accept the license next next install see how the integration runtime setup is completed. click on the finish click on the yes and after that you can see this one is coming okay and here only we have to mention our key okay so what we have to do we have to open our data factory okay you can see this is the key we have right so copy this particular key and click on the register this integration runtime okay and click on the register that's it once you Now you can see integration runtime is now setup click on the launch configuration manager and you can see your self hosted node is completed connected okay and after that we will come here we will close here. ok and replace this ok you can see our integration runtime is created and you can see the right green mark is also there ok after that we have to select the server name what is our server name that actually you have to pass ok so I will go to the my sequel server so this is my sequel server name ok so I will copy this server name here I have to pass server name and the database name database database name is sales database and the username and the password you have to pass okay so i'll pass that what is your username and the password you have to press and after that you have to click on the test connection once the test connection is successful then only you will be able to connect with the sql server okay so let us see you can see this connection is successfully click on the create means we are able to connect with the sql server okay each and every steps i am actually trying to show you okay you can see the connection is successfully okay and after that we have to select our table so what is our table our table is equal pizza sales right this particular table only we want to move into the storage account so i selected that click on the ok once you click on the ok right after that we will be able to preview our data our source is ready if you if i will click on the preview you will be able to see the data okay let us see whether we are able to see or not you can see we are able to see our data okay Now this particular data our source is now ready. Now we have to set up our sink. What is our sink? We want to move into the blob storage. So I have selected blob storage. I am creating a data set. I want to. select as a i want to store as a csv file only so i selected this click on the continue click on the link service so this so i'll select my subscription i select my storage account where actually i want to store and i will click on the create okay this this is a particular storage account where actually i want to move it okay now after that the file path where you want to store so i will select that particular this raw folder only i want to store this raw container only i want to store So, I selected this click on the okay right our sync is also ready okay once you do this right now your source is ready sync is ready click on the publish all okay click on the publish once the publish will complete we will try to trigger it okay we will try to see whether our pipeline is running properly or not if it is failing then we will try to debug if it is not failing then our pipeline is running fine okay. So we have click on the publish completed. Okay, you can see and after that you can see we have an option that trigger now and new and edit. Once you will click on the trigger now, right, this pipeline will run now manually. Okay, so I have selected the trigger now and click on the okay. Okay, now your pipeline will be start running. Go to the monitor. click on the refresh once you click on the refresh side you can see your pipeline is running and this will transfer the data from your on-prem sql server to the blob storage account okay so let us wait it to be completed You can see it is succeed now. So I will go to the my storage account. So I will go to the Azure account. Go to the storage account. In this particular storage account right we have one container. So I will try to search for that container. Okay raw container. Let us see whether file is there or not. You can see this is the particular file is there. If I will click here. Click on the edit. okay you can see our file is available and the size is more also okay you can see this is the file which we have and the 8.73 mb file is available okay now what actually we will do after this we will try to connect with the databricks now let us see how actually we can mount our databricks to the blob storage account so this is my databricks community edition what is the first thing first thing we have to go to the compute click on the create compute so we are creating a cluster so i am giving the cluster name as a project end to end this is the cluster i am giving and you have to click on the create compute okay so this is the one cluster which is getting created okay after that what actually we will do we will try to mount this with our data bridge we will try to mount with our blob storage there actually our data is available how actually we can do that click on the create first we will try to create one notebook click on the notebook once you click on the notebook right so this is the notebook actually which we have so let us give the name as a Project as your we give the name as Azure data engineer as your data engineer This is the project we are giving you can give any name ok you can see by default the language is equal So I will try to convert into the Python ok and that us try to see how actually we can mount this okay you can see in we have one connect option here only this is the particular cluster we are creating right as of now it is not ready once it will be ready you will be able to see and through which we will be able to run our pipeline okay now let us see how actually we can connect with our blob storage account okay so i will show you how actually we have to do okay So let me copy this. Okay. So what actually we have to do we have to mount our we have to mount Databricks with a cloud storage account. Okay. So for that we have to use a db utils fs.mount function. Okay. In this mount our what actually we have to do is we have to mount our database. we have to provide we have to provide our the container name we have to provide our the storage account name we have to provide here the storage account name and we have to provide one the scope name and the key okay this only we have to provide okay after that it will be ready so this is how actually we do in a production environment okay but as of now for the testing perspective what actually we will do i will try to use this okay we are not going to create one secret access key and all we will directly pass it ok so you also do that in the same way ok so what actually I will do I'll just copy this ok and let me show you how actually we can do that ok so first thing is like we have to provide a container name so I will go to the my data factory I will go to the storage account name so you can see what is my storage account name what is my current container name container name is raw right okay so what we have to provide first we have to provide the container name container name is raw so i have provided the raw okay and after that at the rate and we have to provide the storage account name so what is my storage account name this is my storage account name so this i will copy and here i will try to provide this okay once you do this okay and you can see in a bottom also we have to provide fs.azure.accountkey.storageAccountName so here also I will do my azure account name or storage account okay and after that we have to provide the key string okay we are not creating a scope and key that actually we do in a production but here directly will pass a key okay so how actually we can get this go to the storage account click on the storage account okay in the you can see one we have one option access key click on that and click on the so okay so this actually we will copy this what we have to do we have to go to the storage account click on the access key and copy this key okay and directly we will pass here directly we will pass here Okay, and that's it. This is how actually we have to mount it and after that we have to run. So let us see whether our cluster is ready or not. It is still running. Once it will complete we will try to run and we will see if any error is coming or not. You can see our cluster is ready. Let us try to run this. Let us see if any error comes or not. So it is running let us wait it to be completed. If it is successful means we are able to connect with our blob storage successfully. You can see that it is running command. Command is running. So let us wait it to be completed. It is showing me the mount row 2. So we have given row 2. It should be row right. So let me give let me run again. Okay so mount point also we have to give. successful so what actually we had to give in a mount point where actually we want to mount okay so this is the particular location where actually we want we want to mount into this particular blob storage and the raw container so we have given the mount raw 3 okay now let us see we have a let us try to see our file so what we have to write db utils this is the one of the function okay db utils dot fs dot ls okay and after that you have to pass the this location this is the raw location right mount raw 3 and let us see whether what what are the files we have okay let us try to execute this okay you can see this is the particular file path where our pizza sales.txt we have okay so we are able to we are able to fetch our data successfully we are able to mount our data successfully so how actually you have to to mount ok you have to provide your container name you have to provide your blob storage name you have to provide your mount location so give any mount where actually you want to mount it ok so i have provided this particular mount ok and the configuration is given here you have to pass your the storage account and this is a secret access key you have to provide okay and after that you try to execute this command so that you will be able to list all your file okay now let us try to create a data frame okay so our data is available into this particular blob storage and we are directly mounted okay let us try to create a data frame and let us try to read that obviously we can read that so we are creating one data frame so what you have to write spark dot read dot format and after that what is the format this is the csv right so we have to write that and after that we will write the options so we have to pass all the options so what actually we are passing header equal to the true means it will take first row as a header okay and also we will pass the infer schema schema equal to the true ok so that it will consider the schema like if any column should be integer so it will directly take a schema for that ok and after that you have to write the load and the particular location you have to pass that ok and after that you have to load it ok and let us try to execute let us see you can see it is running and after that we will try to read the data frame okay okay it is executed now we will try to see our data how actually you can so that display and the df once you will do display df you will be able to see your data okay You can see we have this particular data frame which got created we have all the details like the order details ID order ID pizza ID and all the informations okay now after that what we will do we will try to create a table okay okay so we have a data. frame now what actually we will do we will try to convert into a one view and so that we can run any sql queries and we will try to create a aggregate table okay so what actually you have to write df dot create or replace time view okay and after that the table name so i will give the table name as a pizza sales analysis okay so this is the table I am creating once you love execute this is the view got created now you can run any kind of the sequel query if I will use a sequel percent sequel and select star from pizza sales analysis you will be able to run any kind of the sequel query okay You can see this is the table we have. Okay, now after that what we will do we will try to create a aggregate table. So for our analysis and we will try to build certain KPI in our power BI. Now we will try to build a aggregate table. okay so what is the important of the aggregate table in that aggregate table you can see here we have a most of the column and all the column will not be important for our kpi okay first let me show you in a visual in visualization layer what kind of the kpi we want to show okay so you can see this kind of the visualization which the business people want okay what actually they want to track they want to take how many pizza which has been sold what is the total order what is the total revenue average pizza which has been sold per order what is the sales versus the order month over month actually it is going on how many total daily order is happening monthly order is happening hourly hour is happening what is the sales which is happening by a pizza category by pizza size and the top five order pizza this kind of the kpi we had to build into the power bi okay so but if you will see in our table right which we have all the column which is not important so in aggregate table we will only take which is important and also we want to track the kpi based on the hourly monthly and the weekly right So, if you will see we only have a order date but what actually we want we also want to get from this order date like we want to get only the January, February we want to get only the day twice like the Monday, Tuesday this kind of the thing actually we want to derive. Okay, so what actually we will do we will create one aggregate table on that aggregate table we will only take the important column which will be helpful for our KPI building. Okay, so let us see how actually we can. do okay so let me show you what kind of the things actually which I will take okay so what actually we will use we will use a sequel right first let me show you what actually I want I want a order ID okay order ID column I want so I will keep as it is okay I don't want pizza ID I want quantity okay so what I will write I will write quantity also because this column also is important for me so I will take that from order date I only want a January February month I don't want the order date I want to take as a month so for that what I will use I will use date format function okay and I will try to convert this order date into a Order date into a January February. So what I have to write mmm. Okay if I will show you from a Pizza sales analysis table. Okay. So if I will show you how actually it will look okay. I will show you first From pizza sales analysis if I will execute this You will be able to see this order date will get converted into the January February You can see this all the January is coming and similarly you will be having fire very month Okay because the base on that actually we want to build a KPI. Okay. So similarly, I want so this column name I will give as a month. Okay, month name. Okay. And I also want to get the Monday, Tuesday from the order date. So that also I will try to derive means what is important from our KPI perspectives in aggregate table that only we will build. Okay, if I'll show you. this is the day name this is the day name okay if I'll execute this you will see the day name it will convert like the Monday Tuesday Wednesday Thursday Friday like that you can see Thursday it is coming like that okay so this is how actually we are building okay so from order date i am taking a month name and the day name okay now let's see order time okay so i want only the hourly time okay because i want to know on which particular hour most of the order is happening so what i am using i am using our function from order time i will get the order time okay whether on the first am it is order is happening or the second am it is order is happening like that actually i want to take okay so i will use a time okay so this is I want and after that unit price I want so I will take unit price ok I also want to take total price so I will take total price ok and the pizza size is also important so I will take that pizza size ok and the pizza category I want to take so I will write pizza category and I don't want the pizza ingredients I will not take the pizza ingredients I will only take the pizza name. Okay, so this only the column I want to store into my aggregate So what I will write pizza Pizza name Okay, so If I will execute now These are the column which is important this only I will take it from this particular table. Okay, so what I have I have order id, I have a quantity, I have a month name, day name, order time, unit price, total price, pizza size, pizza category and the pizza name. Okay. Now if you see there are so many rows actually it is showing. So let us try to group and let us try to insert into one aggregate table. Okay. So what I will do? Order id quantity right. So what I will use? I will use count of distinct order id, distinct order id. I will give the name as a order id. Okay. And Sum of quantity I will use sum of quantity. I will use sum of the quantity so that That actually I will use this I am doing Because I am using aggregate here only okay, and the sum of unit price. I will use sum of unit price Okay, and this I will give as a name as unit price okay sum of total price. I will use ok this will give me the I'll give name as a total sales ok and pizza size pizza name pizza category and we will use a group by group by group by we will group 345 based on the 3458910 8 9 10 ok where I'm not using a sum and the count and that actually I will group it now if I'll execute this it will group based on on this particular category okay the aggregate information okay we will get it now what we will do we will create one table okay we will try to create one table so I will use a SQL and I will try to store this okay so I can create a table or I can directly reconnect this okay so generally in real time what actually will happen you will create one table create table and the aggregate table and you will store this all the information okay you will store all this information on that particular table and you will open a power bi in this power bi you will connect with the get if you had to click on the more option This is the Power BI window. In this Power BI window, you have to click on the get data and you have to search for the Databricks. You have to search for the Databricks. And you can see, you can connect with the Azure Databricks and the Databricks beta. suppose if I am connecting this okay after that all the hostname http path this all you will be having you have to select that and you have to connect okay because we are using uh not uh free edition so what i will do i will just try to download this okay so i will try to download all this and we will try to connect with the csv file and we will try to build a kpi okay that that is how actually we are doing now but in a real time directly you will connect with the databricks we have one connector okay now we will see how actually we can connect how actually we can build a kpi in a power bi okay so i have downloaded downloaded it started once it will complete the downloaded it then we will go to the for KPI building okay so this is a particular aggregate table which we have downloaded okay now what I will do I will open my power bi window okay this is a power bi desktop and we will try to connect so click on the get data and click on the this is a CSV file so I'll select that and this is in download folder pizza aggregate so I'll select that and here only we will try to build a visualization so this is this is what we have you can see we are getting a month name day name order time unit price all the columns details okay which we have considered click on the load okay now we will try to build our all the kpis okay so this is loading once it will download right side you can see we have a field option and in this you can see all the column we have okay now let us see how actually we can build a kpi so first thing what i will select you can see we have one text box right so i'll select that let us give one heading so what i will give sales analysis this name i will give i'll select font size 20 ok font sizes are 20 and I will make in center ok so this is the heading we are giving and you if you want to change the color you can change that okay now let us change the background colors so what we have to do you have to select this okay and after that you can see we have right side you can see we have format option right go to the effects background color you can change on any color you want to convert okay let me convert this this is okay and let me change this color as a white okay now you want to change this background color right so here outside here you have to select and you can see we have format here you can see format right go there go to the background select the color as a blue okay and transparency you have to do that okay Okay, let's make this this much transparency Okay. Now here only we will try to build a KPI. Okay. Now you can see we have a different different kind of the KPI So what kind of the KPI we have to build? First KPI is like total pizza so sold means the total quantity of the pizza which has been sold you can see we have an option pizza quantity right and here you can see in the build visual different kind of the visual which we have okay what actually we want to select we want to select as a a we will select card ok let's select card and we will select the quantity ok once you will select the quantity right here you have option sum of the quantity means it is showing the sum of the quantity means the total number of the pizza which has been sold ok and i will make this ok this is the total quantity of the pizza which has been sold ok now similarly i will create another one i will again click on the card and we will select order ID okay and this is the total order sum of the order ID means total order okay here also I will make it I will make another card ok and I want total sales so I will select that this is the total sales we have ok so let's rename this column ok so this quantity you can see right so I will go to this quantity I will click on this quantity let me give name as a total pizza sold ok this is the name I will give okay here also it is reflecting order id this is the total order i will give okay total order i will give okay so this we have a total order and this is the total sales okay now let us try to do some formatting here so i will click here go to the format go to the general let us try to add some border okay i will add the border click on this let's corner it okay You want any shadow you can add that okay, so similarly for this also and this also you can add the same Okay So you can see it's looking good right so I'll select this also go to the format general And go to the effects Border on and at the corner okay now let us try to add a certain visualization okay so what actually i want to see i will just click outside here anywhere and i want to see how the sales and the order happening month over month so i'll click on this line chart okay and i will select the month and i will select the order and the sales how actually is happening okay you can see this is the graph This is the graph you can see. Okay. The total order and the total sales by the month how actually it is happening. Okay. Suppose you want to change this right. Suppose you want to make a column chart. So I will select the column. You can see this is a column chart. Okay, this is showing the total sales and the total orders Okay, and if you want to change the will make the line chart only and after that you have to click on the format And click on the data labels Okay, so data label also you can see you can see the total order and the total says how month or month it's happening Okay, so this is the chart actually we will show here Okay, let's make this small On the top this also Okay, and this also will make on the top, okay Okay, and I will add here Okay, so this is the total order and total says how a month over month actually how it is happening Okay, you can see the January for every month everything it is showing. Let us try to add a certain more KPI How daily order? is happening let us try to see so I will select the column chart okay and I will select the total order on the daily basis how actually it is happening you can see January February you can see Sunday Monday how many order is happening you want to see the numbers select select this go to the format click on the data labels you will be able to see this how the total pizza size based on the total sales is happening so I will select that this is the one but I want to create this donut chart so I will select that you can see based on the large pizza which has been sold 45 percent ok so this is one KPI Okay, and let us see one more how total sales is happening based on the We have shown based on the pizza size right but pizza category also We will show and we will show based on the pie chart. You can see the pie chart also. We are showing okay Let me add more KPI More metrics here okay i will add here so let me make it small let me adjust this okay now let me add more oh what actually we want to track we want to track the Total sales by the pizza style, total sales by the pizza category. What is the top pizza which has been sold. So pizza name I will select and the total sales. You can see pizza name and the total sales. So I will select on this you can see the column. Here also you can see column. So I will select on the pizza. You can see we have one filter type. So what I will show top end means we want to show only the top 10. ok based on the total sales so i will drag here so it is only showing the top 10 pizza sales ok let me make top 5 only top 5 pizza sales ok if you want to see like this this looks better ok this is the total sales by the pizza name what is the top 10 sales pizza right ok so what actually we will do now now let's see how actually we can do a formatting ok now let us see how actually we can do a formatting so I will select this total order by day name I will go to the format ok click on the columns okay let's see if you want to add a certain other color you can add that okay and you you had to select on the select all so for everything you want to have a separate color you can select from here okay let me add a randomly any color okay so that it will look better let me add as for Sunday white color okay let me add a some more color yellow color okay similarly for this total sales by the pizza name if you want to select you want to have a color so you have to click on the on and you want you want to select any other color you can select from here Okay So all the kind of the formatting we can do based on our requirement. Okay, and What actually we will do now we will try to add a more filter here. Okay, so let us see more formatting here So I'll select here. Let me add a water so that it will look better Go to the general effects Border Corner similarly for this and this also i will go to this i will select this general effects border on let me add a corner this also let me add a corner for this also let me add a corner for this also let me add a corner okay okay so building a visualization is very easy you can see here we have a different kinds of the graph any kind of the visualization you want to create you can create directly from here okay so Okay. Now suppose you want to add a certain filter based on that you want to do. Okay. How actually you can do that? Let us see. Okay. Suppose I want to filter based on the month how actually the sales by the pizza and the pizza category how actually it is happening. So you can see we have another slicer. Okay. So I'll select the slicer. Okay. This is the slicer. So I'll select the month. i'll select the month here you can see all the things actually it is showing okay what i will add i let the drop down okay so we have a drop down okay from here actually we can select that okay so if any based on the month you want to do a filtering you can directly filter it okay now let us add one more slicer Okay, and here based on the day we will add the day. Okay, so let me add the drop down here Okay, and let me add here, okay, so what actually we are doing based on this slicer we can do a filtering Okay okay for taking example suppose I want to see based on the April so it will filter a database on the April okay or even you want to have a multiple you can select that based on that actually you will see the number okay now I will select this Okay, and once you will select this right go to here slicer setting You have to select the multiple multi select or you have to select the show all Okay, once you select the show all you will see we are also getting one option to select all so I will select this Here also we will select So all okay, so based on the so all all the number it will so on. Okay? so this is how we can do a filtering also go to the background you want to have a color right change that okay let's make this also another color general effects let's make a white okay this looks good okay you can see now in this particular it is showing me the percentage as far as the number also i want to see only the percentage so what you have to do you can see you have to go to the details label option label percentage percent of the total only i want to see similarly for this also i want to see only the percentage of the total okay this only i want to see okay now i will add one more slicer so i select the slicer Okay, I'll add the order time. So this will show me the on which particular time the people are ordering. So as of now this is it is showing the in between like 9 to 23 this many hours is happening. So you want to see on the 9am to the 12pm how many order happens so you can slice it like this. Okay, so that kind of the filter also I will provide here. The people are ordering more. So I'll make it list. Okay, so if you will select any time like 9am how many people are ordering if you select this, it will filter. okay so like this also option i want to give so i will make it list and i will add here okay and let me add the slicer option okay let me add a vertical only and the selection let me add the so all also so by default it will be the so all but you will be able to see that let me add the drop down okay now you can see here we this is the dashboard we have we are showing the total pizza store total order total sales you want to do a filtering based on the any month you can select that any day you can select that based on that all the number it will be shown okay so yeah even you want to add a more qpa you can add that and you can do a further analysis okay that's it in this video

Here we are going to use a SQL server, we are going to use azure data factory, we are going to see how actually we can use a data bricks, we will see about the PySpark and then we will go for the visualization tool that we are going to use as a Power BI. So every skill which we are going to use in this particular project and this will be the end to end. So what actually our agenda is like first we will try to understand the business requirement and we will try to understand the data and we will think about how actually we can solve the problem and then we will go for the practical. So at the end we will create one this kind of the dashboard before starting the project if you are new in this particular channel please do like please do support so that the more videos like this I will try to post.

and please do comment so that the more videos like this i'll try to post it okay so let us start let us start with the business requirement so our business requirement is like we have our data that is available into the sql server okay in our on prime and what actually we want to do we want to move into the cloud space that is our first requirement from on-prem sql server we want to move into the cloud that is our first requirement the second requirement is like we have to build one pipeline which will transfer our data and run daily that is the second requirement okay the third requirement is like once you have data that is available into your storage account right so we have to connect it with the help of the databricks how actually you can connect databricks with azure account that we will see and after that what actually we will do we will try to build one aggregate for the reporting. So we have to create one reporting for our business stakeholders and we will try to provide all the kind of the KPI so that the business can take a decision. So for that we will try to create an aggregate with the help of the Spark SQL and the PySpark.

And we will try to build certain KPIs. And the final step is like we will try to connect with the Power BI. and then we will try to show the visualization so this all the things actually we are going to do and that this is all our business requirement okay if i will show you so we have a sql server in this sql server we have a data that is available that is our on-prem okay what we have to do we have to create azure data factory pipeline and we will try to move data from a sql server to the storage account okay once we have data that is available into the storage account we will try to connect we will try to mount with the data bricks Okay, with the help of the PI spark and then after once you have connected with the data bricks, right? With the help of the spark SQL, we'll try to create one aggregate table and based on the aggregate table We will try to connect with the power bi.

Okay, so let us start with the project So what actually we will do we will try to set up a project Okay, so you also do along with me so that you will also will be in the same page. Okay so what we will do first we will try to go to the sql server we will try to create one database and we will try to create one table so how actually we are going to create a table by uploading a csv file okay so i will go to the sql server i will create one database create database sales database and i will use that particular sales database once you will do that you can see left side right the sales database got created okay after that we will try to upload one file so right click on the sales database go to the task click on the import flat file and then we will try to select the particular file okay so this is the particular file i have i will select that click on the next after that you will be able to see all the data click on the next and after that let us try to change the data type here I'll try to change into the orders details ID and order ID and let us see if any error comes okay and click on the finish once you'll do that you can see insert data successfully close it and after that once you will refresh this particular database right you will be able to see one table will which got created ok this is the pizza says we have created if I show you select star from pizza sales ok if I will execute this you will be able to see all the data so this is the data we have we have order id pizza id quantity order date order time unit price every details we have now we have our data that is available in our sql server okay and this data actually we want to move into the cloud space okay so what we are saying we have our data that is available into the on-prem and we want to move into the cloud okay so for cloud you We will open a Azure cloud. Now what we are going to do now?

We are going to create one storage account. There actually we will transfer this. So what we will do? We will search for the Azure storage and we will create one Azure storage account. Search for the Azure storage account and let us try to create one storage account.

Click on the create storage and click on the resource group if you don't have resources click on the create new and try to create One resource group ok and click on the storage account, so I will give any storage account. You can also give any name ok Click on the next next next next next review and after that our storage account will be created we have to click on the create you can review what we have selected here okay and click on the create once you will create this your storage account will be ready okay you can see initializing deployment deployment is in progress once it will complete your storage account will be ready okay You can see this is the deployment is in progress. Let us wait it to be completed.

You can see deployment is completed. We will click on the go to resource. Once you will open the storage account right in left side you can see container we have. Here we will try to create one container.

In this particular container only we will try to store our data from a SQL server to here. So what I will give the name as a input container or I will give as a name as a raw. So this is the raw file. So raw container I am creating click on the create once you will create this right so container is nothing but it's kind of the folder which got created and as of now there is no file available here okay so once we will develop a pipeline right we will try to move data from on-prem sql server to this particular location only okay now let us see what is our next step our storage account is ready our sql server is ready now we will create a one data factory how actually you can create a data factory click on the search click search for the data factory Search for the data factory. Click on the create data factory.

Resource group select that. If you don't have click on the create new and give the data factory name. We will give POC data factory or something name. Okay.

Okay. And after that I will click on the review and create. Click on the create.

You can see deployment is in progress. Once it will complete, our data factory will be ready and then we will go for the data pipeline setup. Now we can see our deployment is completed.

So what we will do, we will click on the go to resource and we will try to open our data factory studio. And in this data factory studio only we will try to create a pipeline and we will try to move data from our on-prem SQL server to the cloud space. Now once you will open a data factory studio right this will look like this what actually we will do here only we will try to create a pipeline so you can see in the left side right click on the author go to the author you can see we have an option like the pipeline okay click on the new pipeline.

time. Click on the new pipeline. What actually we are going to do? We are going to perform a copy activity from a SQL server to the our cloud space.

But that is not easy because SQL server is on-prem server and from on-prem server if you want to move into the cloud space you have to create an integration runtime that actually also we will see. So first let us see what is our source. okay so what is our source so let us try to select that source is a sql server source is a sql server so i'll select the sql server click on the sql server click on the continue link service so what actually we are going to create we are going to create a sql link service so that we can connect with our sql server so click on the new okay here we have to provide all the server name okay so you can see first option is like auto resolve integration runtime by default it is auto auto resolve but because our data is available in on-prem server right so we have to create a new click on the new we have to we are going to create one integration runtime that is the setup we have to do okay so self-hosted we have to select click on the self-hosted click on the continue okay so this is the integration runtime we are going click on the create once you click on the create right you can see we have one option the manual setup so download and install the integration runtime so you have to click on that okay now your uh what actually you have to do you have to click on this download button Okay, and select the first one integration runtime and click on the download Click on the download then the download will start. I have already downloaded I'll now you can see this is the integration runtime which was downloaded. So I'll double click this You can see installation is in progress.

So we have to install this Click on the next Accept the license next next install see how the integration runtime setup is completed. click on the finish click on the yes and after that you can see this one is coming okay and here only we have to mention our key okay so what we have to do we have to open our data factory okay you can see this is the key we have right so copy this particular key and click on the register this integration runtime okay and click on the register that's it once you Now you can see integration runtime is now setup click on the launch configuration manager and you can see your self hosted node is completed connected okay and after that we will come here we will close here. ok and replace this ok you can see our integration runtime is created and you can see the right green mark is also there ok after that we have to select the server name what is our server name that actually you have to pass ok so I will go to the my sequel server so this is my sequel server name ok so I will copy this server name here I have to pass server name and the database name database database name is sales database and the username and the password you have to pass okay so i'll pass that what is your username and the password you have to press and after that you have to click on the test connection once the test connection is successful then only you will be able to connect with the sql server okay so let us see you can see this connection is successfully click on the create means we are able to connect with the sql server okay each and every steps i am actually trying to show you okay you can see the connection is successfully okay and after that we have to select our table so what is our table our table is equal pizza sales right this particular table only we want to move into the storage account so i selected that click on the ok once you click on the ok right after that we will be able to preview our data our source is ready if you if i will click on the preview you will be able to see the data okay let us see whether we are able to see or not you can see we are able to see our data okay Now this particular data our source is now ready.

Now we have to set up our sink. What is our sink? We want to move into the blob storage.

So I have selected blob storage. I am creating a data set. I want to.

select as a i want to store as a csv file only so i selected this click on the continue click on the link service so this so i'll select my subscription i select my storage account where actually i want to store and i will click on the create okay this this is a particular storage account where actually i want to move it okay now after that the file path where you want to store so i will select that particular this raw folder only i want to store this raw container only i want to store So, I selected this click on the okay right our sync is also ready okay once you do this right now your source is ready sync is ready click on the publish all okay click on the publish once the publish will complete we will try to trigger it okay we will try to see whether our pipeline is running properly or not if it is failing then we will try to debug if it is not failing then our pipeline is running fine okay. So we have click on the publish completed. Okay, you can see and after that you can see we have an option that trigger now and new and edit. Once you will click on the trigger now, right, this pipeline will run now manually.

Okay, so I have selected the trigger now and click on the okay. Okay, now your pipeline will be start running. Go to the monitor.

click on the refresh once you click on the refresh side you can see your pipeline is running and this will transfer the data from your on-prem sql server to the blob storage account okay so let us wait it to be completed You can see it is succeed now. So I will go to the my storage account. So I will go to the Azure account. Go to the storage account.

In this particular storage account right we have one container. So I will try to search for that container. Okay raw container. Let us see whether file is there or not.

You can see this is the particular file is there. If I will click here. Click on the edit. okay you can see our file is available and the size is more also okay you can see this is the file which we have and the 8.73 mb file is available okay now what actually we will do after this we will try to connect with the databricks now let us see how actually we can mount our databricks to the blob storage account so this is my databricks community edition what is the first thing first thing we have to go to the compute click on the create compute so we are creating a cluster so i am giving the cluster name as a project end to end this is the cluster i am giving and you have to click on the create compute okay so this is the one cluster which is getting created okay after that what actually we will do we will try to mount this with our data bridge we will try to mount with our blob storage there actually our data is available how actually we can do that click on the create first we will try to create one notebook click on the notebook once you click on the notebook right so this is the notebook actually which we have so let us give the name as a Project as your we give the name as Azure data engineer as your data engineer This is the project we are giving you can give any name ok you can see by default the language is equal So I will try to convert into the Python ok and that us try to see how actually we can mount this okay you can see in we have one connect option here only this is the particular cluster we are creating right as of now it is not ready once it will be ready you will be able to see and through which we will be able to run our pipeline okay now let us see how actually we can connect with our blob storage account okay so i will show you how actually we have to do okay So let me copy this. Okay.

So what actually we have to do we have to mount our we have to mount Databricks with a cloud storage account. Okay. So for that we have to use a db utils fs.mount function.

Okay. In this mount our what actually we have to do is we have to mount our database. we have to provide we have to provide our the container name we have to provide our the storage account name we have to provide here the storage account name and we have to provide one the scope name and the key okay this only we have to provide okay after that it will be ready so this is how actually we do in a production environment okay but as of now for the testing perspective what actually we will do i will try to use this okay we are not going to create one secret access key and all we will directly pass it ok so you also do that in the same way ok so what actually I will do I'll just copy this ok and let me show you how actually we can do that ok so first thing is like we have to provide a container name so I will go to the my data factory I will go to the storage account name so you can see what is my storage account name what is my current container name container name is raw right okay so what we have to provide first we have to provide the container name container name is raw so i have provided the raw okay and after that at the rate and we have to provide the storage account name so what is my storage account name this is my storage account name so this i will copy and here i will try to provide this okay once you do this okay and you can see in a bottom also we have to provide fs.azure.accountkey.storageAccountName so here also I will do my azure account name or storage account okay and after that we have to provide the key string okay we are not creating a scope and key that actually we do in a production but here directly will pass a key okay so how actually we can get this go to the storage account click on the storage account okay in the you can see one we have one option access key click on that and click on the so okay so this actually we will copy this what we have to do we have to go to the storage account click on the access key and copy this key okay and directly we will pass here directly we will pass here Okay, and that's it. This is how actually we have to mount it and after that we have to run. So let us see whether our cluster is ready or not.

It is still running. Once it will complete we will try to run and we will see if any error is coming or not. You can see our cluster is ready. Let us try to run this. Let us see if any error comes or not.

So it is running let us wait it to be completed. If it is successful means we are able to connect with our blob storage successfully. You can see that it is running command. Command is running.

So let us wait it to be completed. It is showing me the mount row 2. So we have given row 2. It should be row right. So let me give let me run again.

Okay so mount point also we have to give. successful so what actually we had to give in a mount point where actually we want to mount okay so this is the particular location where actually we want we want to mount into this particular blob storage and the raw container so we have given the mount raw 3 okay now let us see we have a let us try to see our file so what we have to write db utils this is the one of the function okay db utils dot fs dot ls okay and after that you have to pass the this location this is the raw location right mount raw 3 and let us see whether what what are the files we have okay let us try to execute this okay you can see this is the particular file path where our pizza sales.txt we have okay so we are able to we are able to fetch our data successfully we are able to mount our data successfully so how actually you have to to mount ok you have to provide your container name you have to provide your blob storage name you have to provide your mount location so give any mount where actually you want to mount it ok so i have provided this particular mount ok and the configuration is given here you have to pass your the storage account and this is a secret access key you have to provide okay and after that you try to execute this command so that you will be able to list all your file okay now let us try to create a data frame okay so our data is available into this particular blob storage and we are directly mounted okay let us try to create a data frame and let us try to read that obviously we can read that so we are creating one data frame so what you have to write spark dot read dot format and after that what is the format this is the csv right so we have to write that and after that we will write the options so we have to pass all the options so what actually we are passing header equal to the true means it will take first row as a header okay and also we will pass the infer schema schema equal to the true ok so that it will consider the schema like if any column should be integer so it will directly take a schema for that ok and after that you have to write the load and the particular location you have to pass that ok and after that you have to load it ok and let us try to execute let us see you can see it is running and after that we will try to read the data frame okay okay it is executed now we will try to see our data how actually you can so that display and the df once you will do display df you will be able to see your data okay You can see we have this particular data frame which got created we have all the details like the order details ID order ID pizza ID and all the informations okay now after that what we will do we will try to create a table okay okay so we have a data. frame now what actually we will do we will try to convert into a one view and so that we can run any sql queries and we will try to create a aggregate table okay so what actually you have to write df dot create or replace time view okay and after that the table name so i will give the table name as a pizza sales analysis okay so this is the table I am creating once you love execute this is the view got created now you can run any kind of the sequel query if I will use a sequel percent sequel and select star from pizza sales analysis you will be able to run any kind of the sequel query okay You can see this is the table we have.

Okay, now after that what we will do we will try to create a aggregate table. So for our analysis and we will try to build certain KPI in our power BI. Now we will try to build a aggregate table.

okay so what is the important of the aggregate table in that aggregate table you can see here we have a most of the column and all the column will not be important for our kpi okay first let me show you in a visual in visualization layer what kind of the kpi we want to show okay so you can see this kind of the visualization which the business people want okay what actually they want to track they want to take how many pizza which has been sold what is the total order what is the total revenue average pizza which has been sold per order what is the sales versus the order month over month actually it is going on how many total daily order is happening monthly order is happening hourly hour is happening what is the sales which is happening by a pizza category by pizza size and the top five order pizza this kind of the kpi we had to build into the power bi okay so but if you will see in our table right which we have all the column which is not important so in aggregate table we will only take which is important and also we want to track the kpi based on the hourly monthly and the weekly right So, if you will see we only have a order date but what actually we want we also want to get from this order date like we want to get only the January, February we want to get only the day twice like the Monday, Tuesday this kind of the thing actually we want to derive. Okay, so what actually we will do we will create one aggregate table on that aggregate table we will only take the important column which will be helpful for our KPI building. Okay, so let us see how actually we can. do okay so let me show you what kind of the things actually which I will take okay so what actually we will use we will use a sequel right first let me show you what actually I want I want a order ID okay order ID column I want so I will keep as it is okay I don't want pizza ID I want quantity okay so what I will write I will write quantity also because this column also is important for me so I will take that from order date I only want a January February month I don't want the order date I want to take as a month so for that what I will use I will use date format function okay and I will try to convert this order date into a Order date into a January February.

So what I have to write mmm. Okay if I will show you from a Pizza sales analysis table. Okay.

So if I will show you how actually it will look okay. I will show you first From pizza sales analysis if I will execute this You will be able to see this order date will get converted into the January February You can see this all the January is coming and similarly you will be having fire very month Okay because the base on that actually we want to build a KPI. Okay. So similarly, I want so this column name I will give as a month. Okay, month name.

Okay. And I also want to get the Monday, Tuesday from the order date. So that also I will try to derive means what is important from our KPI perspectives in aggregate table that only we will build.

Okay, if I'll show you. this is the day name this is the day name okay if I'll execute this you will see the day name it will convert like the Monday Tuesday Wednesday Thursday Friday like that you can see Thursday it is coming like that okay so this is how actually we are building okay so from order date i am taking a month name and the day name okay now let's see order time okay so i want only the hourly time okay because i want to know on which particular hour most of the order is happening so what i am using i am using our function from order time i will get the order time okay whether on the first am it is order is happening or the second am it is order is happening like that actually i want to take okay so i will use a time okay so this is I want and after that unit price I want so I will take unit price ok I also want to take total price so I will take total price ok and the pizza size is also important so I will take that pizza size ok and the pizza category I want to take so I will write pizza category and I don't want the pizza ingredients I will not take the pizza ingredients I will only take the pizza name. Okay, so this only the column I want to store into my aggregate So what I will write pizza Pizza name Okay, so If I will execute now These are the column which is important this only I will take it from this particular table. Okay, so what I have I have order id, I have a quantity, I have a month name, day name, order time, unit price, total price, pizza size, pizza category and the pizza name. Okay.

Now if you see there are so many rows actually it is showing. So let us try to group and let us try to insert into one aggregate table. Okay.

So what I will do? Order id quantity right. So what I will use? I will use count of distinct order id, distinct order id.

I will give the name as a order id. Okay. And Sum of quantity I will use sum of quantity.

I will use sum of the quantity so that That actually I will use this I am doing Because I am using aggregate here only okay, and the sum of unit price. I will use sum of unit price Okay, and this I will give as a name as unit price okay sum of total price. I will use ok this will give me the I'll give name as a total sales ok and pizza size pizza name pizza category and we will use a group by group by group by we will group 345 based on the 3458910 8 9 10 ok where I'm not using a sum and the count and that actually I will group it now if I'll execute this it will group based on on this particular category okay the aggregate information okay we will get it now what we will do we will create one table okay we will try to create one table so I will use a SQL and I will try to store this okay so I can create a table or I can directly reconnect this okay so generally in real time what actually will happen you will create one table create table and the aggregate table and you will store this all the information okay you will store all this information on that particular table and you will open a power bi in this power bi you will connect with the get if you had to click on the more option This is the Power BI window. In this Power BI window, you have to click on the get data and you have to search for the Databricks. You have to search for the Databricks.

And you can see, you can connect with the Azure Databricks and the Databricks beta. suppose if I am connecting this okay after that all the hostname http path this all you will be having you have to select that and you have to connect okay because we are using uh not uh free edition so what i will do i will just try to download this okay so i will try to download all this and we will try to connect with the csv file and we will try to build a kpi okay that that is how actually we are doing now but in a real time directly you will connect with the databricks we have one connector okay now we will see how actually we can connect how actually we can build a kpi in a power bi okay so i have downloaded downloaded it started once it will complete the downloaded it then we will go to the for KPI building okay so this is a particular aggregate table which we have downloaded okay now what I will do I will open my power bi window okay this is a power bi desktop and we will try to connect so click on the get data and click on the this is a CSV file so I'll select that and this is in download folder pizza aggregate so I'll select that and here only we will try to build a visualization so this is this is what we have you can see we are getting a month name day name order time unit price all the columns details okay which we have considered click on the load okay now we will try to build our all the kpis okay so this is loading once it will download right side you can see we have a field option and in this you can see all the column we have okay now let us see how actually we can build a kpi so first thing what i will select you can see we have one text box right so i'll select that let us give one heading so what i will give sales analysis this name i will give i'll select font size 20 ok font sizes are 20 and I will make in center ok so this is the heading we are giving and you if you want to change the color you can change that okay now let us change the background colors so what we have to do you have to select this okay and after that you can see we have right side you can see we have format option right go to the effects background color you can change on any color you want to convert okay let me convert this this is okay and let me change this color as a white okay now you want to change this background color right so here outside here you have to select and you can see we have format here you can see format right go there go to the background select the color as a blue okay and transparency you have to do that okay Okay, let's make this this much transparency Okay. Now here only we will try to build a KPI. Okay.

Now you can see we have a different different kind of the KPI So what kind of the KPI we have to build? First KPI is like total pizza so sold means the total quantity of the pizza which has been sold you can see we have an option pizza quantity right and here you can see in the build visual different kind of the visual which we have okay what actually we want to select we want to select as a a we will select card ok let's select card and we will select the quantity ok once you will select the quantity right here you have option sum of the quantity means it is showing the sum of the quantity means the total number of the pizza which has been sold ok and i will make this ok this is the total quantity of the pizza which has been sold ok now similarly i will create another one i will again click on the card and we will select order ID okay and this is the total order sum of the order ID means total order okay here also I will make it I will make another card ok and I want total sales so I will select that this is the total sales we have ok so let's rename this column ok so this quantity you can see right so I will go to this quantity I will click on this quantity let me give name as a total pizza sold ok this is the name I will give okay here also it is reflecting order id this is the total order i will give okay total order i will give okay so this we have a total order and this is the total sales okay now let us try to do some formatting here so i will click here go to the format go to the general let us try to add some border okay i will add the border click on this let's corner it okay You want any shadow you can add that okay, so similarly for this also and this also you can add the same Okay So you can see it's looking good right so I'll select this also go to the format general And go to the effects Border on and at the corner okay now let us try to add a certain visualization okay so what actually i want to see i will just click outside here anywhere and i want to see how the sales and the order happening month over month so i'll click on this line chart okay and i will select the month and i will select the order and the sales how actually is happening okay you can see this is the graph This is the graph you can see. Okay.

The total order and the total sales by the month how actually it is happening. Okay. Suppose you want to change this right. Suppose you want to make a column chart.

So I will select the column. You can see this is a column chart. Okay, this is showing the total sales and the total orders Okay, and if you want to change the will make the line chart only and after that you have to click on the format And click on the data labels Okay, so data label also you can see you can see the total order and the total says how month or month it's happening Okay, so this is the chart actually we will show here Okay, let's make this small On the top this also Okay, and this also will make on the top, okay Okay, and I will add here Okay, so this is the total order and total says how a month over month actually how it is happening Okay, you can see the January for every month everything it is showing.

Let us try to add a certain more KPI How daily order? is happening let us try to see so I will select the column chart okay and I will select the total order on the daily basis how actually it is happening you can see January February you can see Sunday Monday how many order is happening you want to see the numbers select select this go to the format click on the data labels you will be able to see this how the total pizza size based on the total sales is happening so I will select that this is the one but I want to create this donut chart so I will select that you can see based on the large pizza which has been sold 45 percent ok so this is one KPI Okay, and let us see one more how total sales is happening based on the We have shown based on the pizza size right but pizza category also We will show and we will show based on the pie chart. You can see the pie chart also. We are showing okay Let me add more KPI More metrics here okay i will add here so let me make it small let me adjust this okay now let me add more oh what actually we want to track we want to track the Total sales by the pizza style, total sales by the pizza category.

What is the top pizza which has been sold. So pizza name I will select and the total sales. You can see pizza name and the total sales. So I will select on this you can see the column. Here also you can see column.

So I will select on the pizza. You can see we have one filter type. So what I will show top end means we want to show only the top 10. ok based on the total sales so i will drag here so it is only showing the top 10 pizza sales ok let me make top 5 only top 5 pizza sales ok if you want to see like this this looks better ok this is the total sales by the pizza name what is the top 10 sales pizza right ok so what actually we will do now now let's see how actually we can do a formatting ok now let us see how actually we can do a formatting so I will select this total order by day name I will go to the format ok click on the columns okay let's see if you want to add a certain other color you can add that okay and you you had to select on the select all so for everything you want to have a separate color you can select from here okay let me add a randomly any color okay so that it will look better let me add as for Sunday white color okay let me add a some more color yellow color okay similarly for this total sales by the pizza name if you want to select you want to have a color so you have to click on the on and you want you want to select any other color you can select from here Okay So all the kind of the formatting we can do based on our requirement.

Okay, and What actually we will do now we will try to add a more filter here. Okay, so let us see more formatting here So I'll select here. Let me add a water so that it will look better Go to the general effects Border Corner similarly for this and this also i will go to this i will select this general effects border on let me add a corner this also let me add a corner for this also let me add a corner for this also let me add a corner okay okay so building a visualization is very easy you can see here we have a different kinds of the graph any kind of the visualization you want to create you can create directly from here okay so Okay. Now suppose you want to add a certain filter based on that you want to do. Okay.

How actually you can do that? Let us see. Okay. Suppose I want to filter based on the month how actually the sales by the pizza and the pizza category how actually it is happening.

So you can see we have another slicer. Okay. So I'll select the slicer.

Okay. This is the slicer. So I'll select the month.

i'll select the month here you can see all the things actually it is showing okay what i will add i let the drop down okay so we have a drop down okay from here actually we can select that okay so if any based on the month you want to do a filtering you can directly filter it okay now let us add one more slicer Okay, and here based on the day we will add the day. Okay, so let me add the drop down here Okay, and let me add here, okay, so what actually we are doing based on this slicer we can do a filtering Okay okay for taking example suppose I want to see based on the April so it will filter a database on the April okay or even you want to have a multiple you can select that based on that actually you will see the number okay now I will select this Okay, and once you will select this right go to here slicer setting You have to select the multiple multi select or you have to select the show all Okay, once you select the show all you will see we are also getting one option to select all so I will select this Here also we will select So all okay, so based on the so all all the number it will so on. Okay?

so this is how we can do a filtering also go to the background you want to have a color right change that okay let's make this also another color general effects let's make a white okay this looks good okay you can see now in this particular it is showing me the percentage as far as the number also i want to see only the percentage so what you have to do you can see you have to go to the details label option label percentage percent of the total only i want to see similarly for this also i want to see only the percentage of the total okay this only i want to see okay now i will add one more slicer so i select the slicer Okay, I'll add the order time. So this will show me the on which particular time the people are ordering. So as of now this is it is showing the in between like 9 to 23 this many hours is happening.

So you want to see on the 9am to the 12pm how many order happens so you can slice it like this. Okay, so that kind of the filter also I will provide here. The people are ordering more.

So I'll make it list. Okay, so if you will select any time like 9am how many people are ordering if you select this, it will filter. okay so like this also option i want to give so i will make it list and i will add here okay and let me add the slicer option okay let me add a vertical only and the selection let me add the so all also so by default it will be the so all but you will be able to see that let me add the drop down okay now you can see here we this is the dashboard we have we are showing the total pizza store total order total sales you want to do a filtering based on the any month you can select that any day you can select that based on that all the number it will be shown okay so yeah even you want to add a more qpa you can add that and you can do a further analysis okay that's it in this video

Transcript for:Comprehensive Guide to Azure Data Engineering

Transcript for:
Comprehensive Guide to Azure Data Engineering