 Hey guys, welcome to SSUnity at Susiliside and today we are going to see about the Rank Transformation in Azure Data Factory. So what is Rank Transformation? Use the Rank Transformation to generate an ordered ranking based upon sort condition specified by the user. So this is basically the same thing we have seen in the SQL server Where we are going to generate the rank of any table So the same thing we can see here as well. So that is the rank transformation Next we can use the case sensitive or case insensitive option If sort column is of the type of string on that case, it will be factored into the ranking So basically we could say that if you are going to use the string function in the sort Where you can use the case sensitive or insensitive option. So don't worry for now. We will see that in practical Next option is the dense After that we can see the rank column. So in case of the dense rank, so this will be the same thing If you are going to enable the dense rank by clicking on the checkbox So that we will see The rank column will be the dense ranked Each rank count will be a consecutive number And rank value would not be skipped after a time In case of the rank column It will be going to generate the rank value and the column will be the type of long Then we can see the sort condition So where we can choose the sort condition by which we want to do the sorting So all these we are going to see in the practical. So go to on the Browser and we'll try to implement that So first we can see this employee data new file So this is under the azure blob storage under the input folder So here it is having the data for the employee id employee name salary and department So what we want to do So we just want to create a one more column here and that column will be going to generate the rank So and we want to do the order by On the salary. So for example, if we can See the salary like 5000 is coming one and then we can see the 5000 is coming for the HR department Two times 5000 is coming So on that column we just want to generate the ranking So 5000 is the tie breaker we can say so it will be going to 11 and then next will be the 6000 So 6000 is john So in case of rank that will be three and in case of the dense rank that will be two By which we can calculate the first salary Or the second highest salary. So that we can calculate So go to the azure data factory and we'll try to implement that in practical So here let me go and try to create a new data flow here So under this data flow, let me click on add source and under this We have already created one of the source So we can select that one Employee data So if we can open this and here it is going to point the same location under the ssu testing Then we can see the input folder and the file which is the employee data new.csu file So this is having the same data set now Here we can go in the source option now we can go in the projection So under the projection we can see the employee idea as strings. So this would not be string. This would be integer And the salary salary should be integer as well Now we can go in the data preview and try to refresh it So we will see the data that is available in the source table So here we can see all the data for the it and hr department Next we just want to use the rank so we can click on this plus symbol and after that we can Select the rank here So under this rank here we can see the option For the case insensitive then the dense and the rank column that we were discussing over slide First of all we are going to do the sorting on the basis of Salary so salary is not the string type column So case sensitive or insensitive will not work on that case So here we can see the sort condition. So let me try to select the salary here And we want to do the sorting on the ascending order that is okay After that now we want to create a new rank. We can write the column that is a rank Now go to the data preview and try to refresh it So on that case we will be going to see the rank there directly. So here as we can see the rank So it is going to sort on the basis of salary So here we can see the type record is 5000 and 5000 So rank is one and one for these two and after that we can see the 6000 6000 So this will be going to have three and three So one number is going to skip here because we are using the rank function So this is the property of the rank. So it is going to have the one one And two will be skipped then three three Then four will be skipped then we can see the five six seven seven So this is the rank now we can go in the rank setting And here let me try to select this on dense rank So on that case it will be going to have one one then two two then three So it will not gonna skip any number So that is the property of the dense rank. So here we can see one one then two two then five five five four three So this is basically we can say like If we can sort on this then it will be going to have the same values like one one two two then three four five and five So if you want to see the highest salary on that case On the salary we can do the descending order here And go back to the data preview and try to refresh that So here we will see Which employee is going to get the highest salary? So the rank of that employee will be one And the second highest salary that rank will be two and going forward. So here We can see this one. So the highest salary is nine thousand. So nine thousand salary is getting two employees Tom and Ken. So here we can see the rank one. So we can directly do the filter On this rank. So that will be going to filter if we want to see the second highest salary Then we can fit out by using this rank column with two Going forward for three four and five. So we can do the filter Now let me try to Load this in the sink location. So here we can see the sink. So let me go here Now let me try to use the inline dataset And here we want to load that into the related text file And after that, let me select the link service from here Now go to the Settings and here we need to select the folder by which we want to keep the file So file should be on the output folder And after that first row as header. So we can select that one Then we can see the file name option. So the file name option should be output to single file and the name of the file will be Rank data Now we can go inside the Optimize and need to select the single partition here. Now we can directly publish this So Data flow one will be going to publish In between let me try to create a new pipeline here So this pipeline will be going to execute and Your data flow will be executed So we can use the data flow activity for execution of the data flow that we have created Here we can go in the setting and try to select the Data flow that we have created like data flow one Now let me try to debug it. So go to the output folder of This container So as of now in the output folder, we can see only these two. So one file should be here After executing of this data flow activity under the pipeline So now that is in progress we can see that We can refresh. So here we can see the rank data So under the rank data We could verify that so go to the edit And here we can see like we are having all the ranks As we have seen there. So this is all about the rank transformation. So thank you so much for watching this video See you in the next video