Hey guys, welcome to SSU Retail Society site and this is continuation of Power BI Projects, so this is part 5 of Power BI Projects, so today we will see about the attendance overview of any organization by their employees. If you haven't subscribed my channel yet, so I would strongly request to you all please subscribe my channel and press the bell icon, so you can get the notification of our newly uploaded videos. And also like this video and do your comments if you like the video. So let's get started with our project. So this dashboard will be done in this video. So let's start with the left top. So here we can see this is the image of employee head count. So you can download from Google and you can use that image for your project. After that you can see the attendance overview. So this is a text box and the background color is grey and the font color is black and text is attendance overview. In the right side we have used a card and this card will be having for current date. After that in the left top we can see total number of employee by gender. Percentage of male employees and the percentage of female employees will be displayed over here. So in my data it is male employees is 215 and the female employees is 93. So the percentage of the main employee is 69.81% and the female employee is 30.1% approximate. After that present and absent by country. So here we can see the present which is displayed in the orange and in the light we can see the absent. So the country is united states then India then France. So here we can see the total number of days present is 4.4K then 3.0K is the absent. Then in India is 0.7K and in India for absent is 0.5K similarly for the France. The value is very less in the France that's why we are seeing 0.00. In the right side top we can see this matrix inside that matrix we are having the department names. So the department name and after that the present and absent days for that department. Here we can expand on that department. So we can see the employees which is working on that department. Now go to on bottom. So here we can see the percentage of present and absent by months. So here we can see the percentage that is 80% present in the month of Jan. And 20% is absent in the month of Jan. In the month of we can see the 39% is present and 61% is absent. Then the month of March 56% is present and 44% is absent. So this is something column chart which is containing the month in the axis and present and absent in the columns. Now in the left bottom we can see the matrix which is containing the days in the columns like 1120, 1220, then 1320 like this. And in the rows we are having the employees and if the employees present on that particular day then that will be 1 and background color for that should be light green and if employees absent for any particular day then that should be 0 and background color will be red. So if we can move on the right side then we will see we will have the data like this. After that here we can see we are having this slicer. So this slicer will help us to filter the data only for the matrix which is available on the bottom. It will not affect any visualization which is present in our dashboard. So if we will change this let's make it for only 7 days. Then here we will see the data for only 7 days. And the 4th and 5th is our weekend that's why we don't have the data for that and rest we are having 1, 2, 3, 6 and 7. So the absent is only 1 day for the amrita and if we move bottom then we will see more. Here you can see the more. So this is something like that. For example if we are having all the data for all the days and we want to filter for any particular employee. So we can also do that and this employee filter will also help to filter the data in the matrix which is on the bottom of this. Here let me filter for hersting jora. So now we can see we are having the data for him. Now let's move to left side and here we can see these are the days and move to right side. So we can see the absent and present only for that employee which will be selected on this drop down. We will apply the filter on the employee and narrow down the days. So this will be created in this video. So I would request you all please watch this video step by step. So you will not miss any step. This video might be longer because this is a big project not a small project. Let's have a quick look of our source. So we have 2 files. First is the master table. So it would have few columns which is containing the information of the employee. So first is the business entity ID that is the employee ID. Then national ID number. Then the name, birth date, monetary status, gender, higher date, department name, email ID then address one city, country and postal code. So we are having this data. After that let's move to attendance table. So in the attendance table we are having only 3 columns. In actual it might have more than 3 columns. But in our project we require to have only 3 columns. The next column might be the in time then next will be the out time. But that is not required to us. So that's why I have excluded that columns and containing only these 3 columns. So first is the employee ID. Second is the date then the status. For example employee 1 date is 1-1-2020. He was present. That's why you can see P. If we move to 1-2020 then we can see absent. So he was absent for that day. That's why we are having the A. So we are having data like this. So how we can load this data and how we can play with that data. We'll see right now. So go to on start button and let's try to open one more Power BI desktop. So we'll try to import that data on that. After that we'll try to create the beautiful dashboard which we have seen. So we need to wait until it will not be opened. So now this is opened. Now we need to get the data. So click on this and select the excel option. So first let me select the master table and click on open. So it will take few seconds. So we need to wait. So here we need to get the data from it by master file. So we need to select that one. And here we can see the two options. First is load and second is transform data. So I'm very happy to go on transform data. So it will open query window. So inside the power query window we'll see and if any modifications are required on this data so we can do that. So we need to wait few seconds. So now here we are. Now we are good with this data. But one thing let's open this. And here we can see the nulls. So nulls are not required to us. So just eliminate it and click on OK. So it will filter the data for the nulls and one step is added in the applied steps. So we are very happy with it. Now we have done it. Now we need to get the data from another file. So we can go on the new source and let me select the excel. And this time we get the data from attendance file. So we need to select the attendance and click on open. So that will also be opened inside the power query window. So we need to get the data from seat 1. So we can select it. Now we can click on OK. Now here we can see the column 4 and column 5. So these two are not required to us. So we can delete that one. So we can remove columns. So these two will be removed. And one step is added in the applied steps. After that we can also rename this seat 1. So let me call this as attendance. Now we need to click on close and apply. So this will be added and load in the power gate desktop. So we need to wait. Now this is loaded. And here we can see we don't have any relationship between the attendance and employee master table. So now go to on the relationship. So inside the relationship view we need to build the relationship between the attendance and employee master table. So here we have employee ID in the attendance and in the employee master table we have business entity ID. So let's drag this employee ID and drop in the employee master table. And we need to wait few seconds. So here the relationship is done and here we can see business entity ID is mapped with the employee ID. Now double click on this and you need to wait few seconds. And here you can see the relationship is done with one to many. Now cancel it and go to on the report view. So we have done the relationship. Now the first thing is we need to get the image in the left top. So first minimize this filter and now go to on the insert. And here you can see the option for image. So click on that image and this is the employee head count. So let me select it and click on open. You can also download from the Google and you can use that one. We can place this at here. Now we need to use a text box. After that we need to drag this text box and place in the top side. Now try to minimize from the bottom and maximize from the right side. After that we need to write the attendance overview. So we can write attendance overview. So this is attendance overview. Let me select this and try to increase the font size. So let me select the 24 and place is in the middle. After that change this style with the area. Now we are ok with it. Now let me select it and go to on the background and let me try to change this background color with the gray. So this is the gray and the transparency should be 50. Let me make it from here. So this is 50. After that we need to create a measure that will contain the today's date. So go to on the employee master table. After that here you can see the option for new measure. So let me click on that. After that we need to wait few seconds. Here we can see this measure. So now let me call this as today and the formula that should be today. So this function will help us to get the today's date. Now let me click on commit. So it will be committed. And here we can see the format. So we can also change this format what we want. So I am very happy to use the DDMMYY. After that we need to use a card. So now let me click on the card. And try to make it smaller and do one thing just drag this today and drop inside the field. After that make it smaller from the left side and put this in the top. And here do one thing go to on the format and just off this category. After that go to on the data level and try to make this smaller as text. So I am very happy with the 255Ks. Now we can make it smaller from the bottom as well and put it over here. After that we can make the background color of this as we have for the text box. So go to on the background and put on the color let me make it with gray transparency should be 50. So now we have done with the header part of the dashboard. Next thing go to our existing one. So here we need to use this visualization that will have total number of employees by gender. So for that we need to use this donor chart. So we can select this one make it smaller from the right side put it on the top and let me bigger from the bottom. So now go to on the employee table and here you can see an option for gender. So let me drag and drop this gender inside the legend. After that you can see the option for the employee ID that is business entity ID in the employee master. So we can drag this and drop inside the values and here we can see the count of business entity ID so we are good with it and here we can see this is our male and this is our female. So we need to do the formatting only for this. So go to on the format and here you can see the data color so in the female we want to use the orange. So we can choose the custom color and here we can use double F double 6 double 0 so this is for orange. After that go to on bottom little bit here we can see option for detail labels and here we can select the all detail labels. So now we can see the male is having 215 and the female is having 93 and the % is 69.81 and the female % is 30.1 so this is very similar as we have. So next thing what we need to do we need to modify the title of this visualization so go to on the format after that here go to on the titles and here let me call this title as total employees now it should be double E let me try to scroll down the font color let me put it as white and the background color that should be dark blue and put it on the middle now try to make it smaller. Next thing we need to add the border for this again scroll down and here go to on the border and here we want to use the radius so it will be on the curly shape so now we can see this in the curly shape. So I guess we are good with it so let's move to next visualization so what was that so here we have the present and absent by countries so now go to our dashboard and here before going to add this visualization we are required to use two columns first for the present and second for the absent so go to on the data and inside the data let me select the attendance table and in the attendance table we need to add one column that will have the data for the present and second column that will have the data for the absent so here we can see the option to add a column so go to on the table tools and here you can see the new column so we can click on this and it will add one more column and here we can specify the DAX formula so what we want to do this is for the present and the condition if the value of this status is p then that will be the present so comma 1 comma 0 so now we can click on commit so if we will have the status p then in this column we will get 1 if status is a then we will get 0 on that case so let me add one more column that will have the data for absent so we can click on new column and here we need to wait and let me call this as absent and again if status is a then comma 1 otherwise 0 let me commit it so it will add one more column that will have the data for absent so this is absent and this is the present now go to on the report view so here let me add column chart so we can click on this and let me put it on the right side of the existing one and here go to on the attendance table and here let me try to drag and drop the present inside the values from the attendance and the absent in the values so now we are ok with it but here we want to display the data by country wise so the country information is available in the employ master table so here we can drag the country and drop inside the axis so it will have the countries in the axis and the data over here so now what we need to do so go to on the formatting here we can see the country is available in the bottom of this axis and the present and absent over here so we don't want to display these two titles so go to on y axis and scroll down scroll down here we can see the option for title so we need to off this and go to on the y axis and in the y axis we will also get the option for title so let me scroll down and here we can see the title so let me off this so now I guess we have added this one after that we can see the present and absent by country the only thing we need to modify this as the background color of this title and the font of this title so we can select the font as white and the background color as dark blue so I guess this is the same one after that we need to add the border as we did before the format and scroll down here we can see the border let me on that one and here let me make it radius as I guess 25 that is good so we are okay with it so here we can see the data that is available like 0 to 5K so what we need to do we need to make it little bit bigger from the bottom that will have the data more accurate for our information so here we can see it is having the data for 0K, 1K, 2K 3K, 4K, 5K so next thing what we need to do we wanna add the data labels on these columns so the format and here we can see the data label we can on that one so now we have the data as we have in our existing one next we need to add a matrix so go to on the our existing one and here we can see the matrix this is our matrix that will have the department name and if we expand it then we will see the information for the employees and in the right side we have present and absent so we need to create this matrix so go to on the dashboard which we are working on and here let me add a matrix now let me try to make it right side of this and try to make it bigger after that here as I told you we need to have the department name and employee names so how we can create the hierarchy for that so we can press the left mouse button and drag this name in the department name it will create the hierarchy so we need to wait few seconds and it will add one more column that will be the hierarchy like this so now we need to drag this and drop inside the rows so here we can see we are having the rows if we expand it then we can see the employees that is working on that department next thing we need to add the present and absent so that is available inside the attendance so here we have two columns first is present and second is absent so let me try to drag this present inside the values after that the absent inside the values now we can see we are having the same data as we seen in our existing so now go to on the format and do the formatting of this so here go to on the style first and let me try to add alternate rows that might be good after that we can also increase the font size of this so how we can do that here go to on the values and let me try to scroll down and here we can see this text so let me try to increase it so that will be increased like this so that is good after that go to on the columns and the rows so in the rows header also increase the size that is text size is term so that is good and if we want to make it bigger then we can also drag it like this and we can put like this and here if we want to make it little bit smaller then we can also do that here if we want to make it bigger then we can do that and let me try to put it here and here let me try to make it bigger totally by gender now I guess we have made a great progress in our project the only thing we need to create the bottom of this visualization what was that so here if we can see we are having right side that is the percentage present and percentage absent by month so how we can do that we need two measures first for the percentage present and second for the percentage absent so how we can create that one so for that go to on the attendance table after that here go to on the table tools and quick measure so click on that and we need to wait few seconds after that select a calculation so we can click on that after that is called down in the numerator we need to use the present and in the denominator we need to use the employee ID that will be the count now we can click on ok so it will add one more that will have the information for the present percentage so we need to wait few seconds that measure will be added so this is our measure so we can click on that measure and here we can see the percentage so we need to rename this as present percentage and we need to multiply it by 100 so we will get the percentage now we can commit it and next we need to add one more for the absent percentage so we can click on the measure for here so go to table tools and click on the new measure and this time I have copied this formula so we will use that one so let me paste that formula instead of present it should be absent we are doing the sum of this present so it should be the absent now we can do the commit so these two will be the calculating of the percentage of present and percentage of the absent so now we can use the column chart so now go to on the column and let me select that one put it on the right side and make it bigger and make it bigger from the top as well and make it bigger this after that let me select this column here as I told you let me include this percentage of present so let me drag this and drop inside the values and this absent percentage in the values now we need to add the months so here we can see the date that is available inside the attendance table so let me drag this and drop inside the axis and here we need to delete this year then we can also delete the quarter month wise so here we can see the month that is Jan so the percentage is 80 is present 20 approximate is absent so here we can see we are having the data that is in decimal places but in case of present it is in non decimal places so go to on the absent percentage let me select that one in the formatting we can use the whole number we need to wait and here we will see the 20% that is good after that 39% and 61% then 56% and 44% so I guess we have made a great progress after that we don't want to include this month as a title in the x axis in the y axis we don't want to see this percentage present and absent so go to on the format and here go to on the x axis and here is called down and is called down here we can see the title let me off this and is called down for the y axis and we will see the title so let me off that one then next thing we need to do the formatting of this so go to on the data colors and here so in the present it should be green so here go to on the custom colors instead of this we need to use 008000 so this is the green after that for the absent it should be red so we can click on this and go to on the custom color here we can see the red so we can select that part so this is red and this is the green that is good after that we can also enable the data level for this so we can click on that we can see the 80% 20% now the next thing is the title so is called down here we can see the title let me open that one and here let me select the background color that should be the dark blue and the font of that that should be white so let me select that one and make it alignment as center so now I guess we are good with it the only thing we need to take care for the matrix that will have the information for the employee with the dates so we will do now so now go to our existing one and here we can see this is our matrix in the rows we are having the employee names in the columns we are having the dates and here we are having the status that will be the present or absent so how we can create that one so go to our the Power BI desktop which currently working on here let me select a matrix and let me try to make it bigger now what we need to do here we need to drag and drop the employee names so we are having the employee name in the master table so drag this and drop inside the rows and here we can see this is good next thing we need to include the dates so this is our date so let me try to drag and drop in the columns so it will have the data like this so click on this arrow and here we don't want the hierarchy for this so let me select the date so it will have the date only so we can see this is not in the correct format as we are expecting so we can select this date and here we can see the formatting option so we can click on that and here we can select the MD wide so it may be good and will not take a lot of space from the column so we need to wait now I guess it's good after that we need to add the present in the values so let me try to drag this present and drop in the values now it will have the values like 1 1 1 and 0 where we are having the option so now we need to do the formatting so go to on the format and here first go to on the style in the style let me select this so this time I am very happy to use the alternate rows so this might be good or you can select any other option so we are having a lot of options we can select any one of them so I guess this is the good the next thing we need to take care for the present absent if we are having the present the background color should be light green if we will have the absent then that will be the red and the font color should be the white in case of red so again here we can see the option for the conditional formatting so let me scroll down and here we can see the conditional formatting now let me scroll down here we can see the background color so we can on this and after that we will wait few seconds click on the advanced control here we can see we will have the option for color scale so I am going to use the rule here some of the percentage that is good and here if the value of that is 1 so that will be the present here I am using the number not the percentage then that will be the light green so we can use like this after that let me add a new rule and this will for the absent so if the value is 0 of that number then we want a background color as red so we can go to the custom color and select the red now click on ok now we need to wait here we can see we are having the data like this but again as I told you the font color that is white then black that is not good for us so we need to change that so here we can see the font color click on that and here let me scroll down little bit and click on the advanced control so in the advanced control we can see the color scale so I am going to use the rules and the first is if the value is 0 then we need to make it as white now we can add a new rule and value of the the present is 1 then we need to specify that as black now we can click on ok so here we can see we are having the 0 and 1s as we are expecting so we are in the good and we can scroll on the right side and we can check the values that is our expectations now the next thing and the last thing we need to add two slices first we will be going to filter the data that is available in the dates second will be narrow down for the employment names so first let me try to make it little bit smaller and this as well and go to on the matrix and try to make it smaller this matrix as well now here go to on the slicer let me select first slicer and try to make it smaller and it will have the data for the start date and end date that will be filtered so here we can go to on the attendance and let me try to drag this date and drop inside the fields so we can see this and let me try to make it little bit bigger so now we can see that after that we can add one more that will help us to filter the data for the employees so now we can add one more slicer and this time make it smaller and put it on the right side of that slicer and the name from the employee master table now go to on the employee master table and try to drag this name and drop inside the field after that go to on the formatting and here go to on the selection control we want to see the select all option over there and here let me click on this arrow and make it drop down and try to make it smaller from the top now we can click on this and do some more formatting like for the border we can on the border of this and we can open it and here let me scroll down and the radius it should be 25 I guess next thing we need to make the background color of this we need to make these two changes so click on the format and here we can see the background so we can click on that go to on the color we can select the gray and scroll down transparency that should be I guess around 50 so we can select form here 50 so now go to on the name format and here we can see the background color click on that scroll down in the colors we can select the gray and transparency that should be 50 so now we are good with it and here let me try to make it bigger I guess this is good if we will scroll then we can see all these values the only thing we need to take care for the title of this name so here go and off this slicer header and try to use the title on this title and let me call this as employee name and here let me try to scroll down and the font color is black so we need to make it as white and the background color is white and we can use as black so we are good now here let me select this one and try to go on the date inputs and here the font color that is good the background color is not very good so we can select and this is the background color the font color should be white for this so now I guess we are good now we can make it bigger from the top make it bigger this as well and we have done this and the only thing if we will make the change on these two slicers then it will affect all the visualization which is available but what our expectation is if we will make the change inside this then it will only affect our matrix which is available on the bottom so click on that go to on the format and here we can see edit interactions so click on that here we can see the none so we can click on that none none and none now select the employee name and here we can click on none none on all these now we can again click on edit interactions now if we will make the any change over here it will not affect any other visualization it will only change the values inside the matrix now we can see it is going to narrow down let me select any employee so let me select the amrata here we can see the data only for amrata and there is no any change for other visualization so let me select all so this is what we did this video thank you guys for watching this video I hope you have understand how you can create this beautiful dashboard in the Power BI and if you need the source it will be available on the google drive I will paste the link of the google drive on the description of this video so if you need then you can download their files and you can do the practice on your own and still you have any doubt then you can comment on the video and I will try to response on that and if you are having any project or any scenario then you can share with me and I will try to record the video on the same thank you so much guys for watching this video and do subscribe my channel press the bell icon and like the video thank you so much guys


The relative side load area for a given window shape reaches an asymptotic unzero limit and this is the origin of what is called the Gibbs phenomenon. First have we exposed the Gibbs phenomenon in sinusoidal series approximations. When we decompose a periodic function into its Fourier series expressions, what happens is the more involved the number of Fourier series terms that you retain in the in the expansion, the better and better are the discontinuities approximated. So you see if you recall if you have a square wave and if you take one cycle of a square wave and after all what is an example of one cycle of a square wave? The ideal period of the DTFT, so if you take from minus pi to pi the ideal DTFT and then you look at the impulse response essentially as the Fourier series coefficients of this. Now that perspective is not difficult to see at all, think for a minute, after all if this be H ideal omega, what is the impulse response H ideal of n? H ideal of n is 1 by 2 pi integral from minus pi to pi H ideal omega e raised to the power j omega m d omega. So effectively these are like the Fourier series coefficients of this periodic DTFT on the variable omega, that is another perspective on the impulse response. The impulse response is like a Fourier series expansion of the periodic DTFT. Now we are looking at it the other way, all this while we thought of the DTFT as a consequence of the impulse response. Now we are thinking of the impulse response as arising from the DTFT and that is not unreasonable because it is the ideal that we know in the frequency domain and we want to find out the impulse response coefficients that should give us that ideal and that process of finding the ideal impulse response is akin or in fact is identical to a Fourier series expansion. And when we truncate this impulse response what we are effectively doing is to truncate the Fourier series of that periodic function and that is what leads us to what is traditionally understood as the Gibbs phenomenon. So what happens is on the same you see you have what happens is the more and more Fourier series coefficients you retain you have this oscillation here you get more oscillations as you approach discontinuity then you get a rise and then you get the largest oscillations here and then you get smaller ones in between you get larger and larger oscillations and then again a drop and then the largest oscillations and finally it reaches a steady situation and Gibbs phenomenon says that this oscillation does not reduce no matter how large you make the length all that happens is that these oscillations get confined to the boundary near the discontinuity but the magnitude the height of those oscillations reaches an asymptotic limit you cannot go below a certain value for the rectangular window but of course you could go below that value if you took a different window but of course at the cost of the transition bandwidth. So what I am trying to emphasize is that once again let me say it very clearly you see all windows have the tuning parameter given by the window length n. So all windows can give you as small a transition band as you desire but what they cannot do is once you have chosen a window you have bound the maximum deviation in the pass band and stop band. So the tolerance of the pass band and the stop band cannot be influenced beyond the point you can influence the transition band but you cannot influence the tolerance and unfortunately the pass band and stop band tolerances are equal or fortunately if you want to call it whatever it is now the Kaiser window allows you one more tuning parameter and that is like a shape parameter. So you see as you see it is the shape which gives you this maximum deviation relative side lobe area. So it helps you control the shape and the shape parameter allows you a tolerance between the main lobe width and relative side lobe area. So this Kaiser window is described by Wk is equal to I0 beta n that is a complicated expression but that is not bothered too much about it. Square root 1 minus k by n the whole squared divided by I0 beta n with beta always greater than 0. So beta goes from 0 to infinity and of course we have written the expression for I0 before it is the modified Bessel function of first kind and order 0 in x. So it is very easy to see that beta equal to 0 corresponds to the rectangular window. There is what is called a shape parameter. So the windows take different shapes. Now of course you must not forget that this window is described only between minus capital n and plus capital n. The modified Bessel functions of course last for all values of the variable but this window is defined only between minus capital n and plus capital n that is to be understood. And of course you can then draw the envelope of the window and then you could resample it to get the even length window. So the Kaiser window is in some sense an optimal, optimal in this game of compromise between the main lobe width and side lobe area. It was studied in depth by the mathematician Kaiser and now typically it is the Kaiser window which is used when one uses FIR filter designed with windows. In fact in the assignment that all of you would do to design the FIR filter using the window approach you would use the Kaiser window. Now how one chooses the shape parameter and the length is only by empirical experiments that have been done by Kaiser and other researchers there is no closed form for choice. And what I shall do is to put down certain guidelines so to speak for the choice of length, the empirical design equations for the Kaiser window. You see what we are assuming here is that we will take the case of the low pass filter but a very similar kind of argument can be used for other kinds of filters. Of course you have pi here, you have omega s there, you have omega p, this is 1 plus delta up there and 1 minus delta down here and delta down here plus delta and if you like we will call it minus. So the response is between plus delta and minus delta and 1 minus delta and 1 plus delta. Now notice that the pass point and the stop point problems are the same. We have no choice on that front. So the empirical design equations are as follows. The steps are define delta omega t or the transition bandwidth to be omega s minus omega p. Define a to be essentially the logarithmic value of the tolerance or the db, decibel value of the tolerance. We define alpha to be beta times n and therefore the first step becomes the choice of n. So n is chosen according to 2n plus 1 that is essentially the window and I am talking about an odd length window here. So you see if you want to design even length then you can just choose one more or one less after all these are all empirical expressions. So 2n plus 1 is greater than or equal to 1 plus. Now these numbers have been arrived at by empirical considerations 2.285 delta omega t. This is the guideline for the choice of n. So see more about these steps one could look up any standard text on DSP. For example one could look at the text by open hind. So of course we also need to determine the shape parameter. For the shape parameter which we could either call alpha or beta. Alpha is of course equal to beta times n. So we will write an expression for alpha and then you could of course correspondingly write an expression for beta. Alpha is equal to 0 for a less than 21. So you notice that alpha the choice of alpha is governed essentially only by the fast band tolerance for stop band tolerance and that is to be expected because alpha which is of course beta times n you know the shape parameter having chosen the length then the choice of shape parameter has only to do with the tolerance. So alpha can be called the shape parameter here beta times n and what this means is that as long as a is less than 21 you could as well do with a rectangular window because you know beta equal to 0 alpha equal to 0 essentially implies a rectangular window. Now if a is between 21 and 50 then it does not matter you can say less than or equal to. Then we choose this according to 0.5842 again all these are empirical a minus 21 to the point to the point 4 plus 0.07886 a minus 21. So it is the additional a above 21 which you are using in the expression yes the question is when a is less than 21 we are effectively saying the shape parameter is 0 and we are essentially saying the rectangular window does very well what it means is the rectangular window gives you a tolerance which is less than 21 decibels and finally when a is greater than 50 the expression to be used is 0.1102 a minus 8.7 alpha is equal to this and of course let us not forget the beta is equal to alpha by n and the beauty is that you see for every kind of window there is an equivalent alpha which gives you the same tolerance what I mean by that is take any window shape triangular for example now the triangular window reaches an asymptotic limit in terms of its passband and stopband tolerance what I mean by that is after a while after you keep increasing the length of the triangular window you cannot reduce the passband for stopband tolerance beyond a certain point that is called the asymptotic tolerance for that window asymptotic delta for that window the delta leads to an a and the a leads to an alpha in the Kaiser window so for every window there is an equivalent alpha in the Kaiser window which gives you a tolerance the beauty is for that alpha of course you also have an m corresponding to you know when you have a the beauty is that you know once you have chosen the for the Kaiser the beauty is the Kaiser window does better in terms of the transition bandwidth than most of the windows for that same alpha from the comparative platform once you've chosen the alpha to match the tolerance in the passband and stopband asymptotic tolerance in the passband and stopband for the window the transition bandwidth is slightly reduced so one can construct a table for each window you can put down the way the transition band varies as a function of length and one can put down an asymptotic tolerance and the asymptotic tolerance you can find out the equivalent alpha and then once you find the equivalent alpha you can study how the transition bandwidth changes for the Kaiser window of that alpha within and the transition bandwidth does better for the Kaiser window than the original window that is typically the case that's interesting that's why the Kaiser window is preferred we take just one example to illustrate this you see for the HUN window the peak relative side load amplitude or you know the effective so the peak error the peak well let's not worry about the side load amplitude the main width is 4 pi by n and the peak error in approximation that is essentially the A that we have talked about is 53 and 7 this is 44 for the HUN window and essentially taking this data from a table 53 actually happens to be for the HUN window the HUN window and the HUN window have the same main load width in fact I can write it down here HUN 44 53 for HUN window the if you take the equivalent Kaiser window equivalent Kaiser window with the same alpha for the HUN window the corresponding main load width or the transition width becomes 5.01 pi by 2n and for the HUN window it becomes 6.27 pi by 2n plus a greater A is better a greater A means a smaller delta you must understand this so greater A is better so in the sense of A the humming window is better than the HUN window in fact that's why you see those fun numbers that were there 0.54, 0.46 instead of half and half but of course better in the sense of tolerance and worse in the sense of the you know actually equal in the sense of main load width but humming is better in terms of tolerance that's why the humming window would be preferred over the HUN window in a sense the HUN window is easy to write but you know it's not as good as the humming window in terms of its tolerance but of course you see in the corresponding Kaiser window of course you see the compromise the main load the transition bandwidth increases little bit so as you go from beta equal to 0 to beta equal to infinity it's a perpetual gain at the rectangular window you have the very best transition width and the very worst tolerance and as you go towards larger and larger beta you get more and more transition bandwidth but you of course do better and better in terms of tolerance this is how we design FIR filters using the Kaiser window and now you are all equipped to complete your assignment individual assignment on the design of filters both IRR and FIR with the specifications that you have been given yes there is a question yes. We are trying to approximate e raised to x so why not use directly e raised to x so the question is you know it seemed like when we went from the rectangular to the triangular and then to the HUN humming and then going further to the Kaiser you are trying to make the function smoother and smoother and we also remarked that when we looked at the Kaiser window it seemed to temptingly resemble e raised to x so why not use an exponential itself the answer is that it's just a resemblance the Taylor series is not really the expansion of an exponential it looks like that you know it looks temptingly like that it's it's much it's you know it's it's different because you have a square term the x by 2 the whole to the power L divided by L factorial the whole square so it's not quite an exponential the exponential is not quite the optimal you know it's very so this modified Bessel function has been chosen by strategy alright so then we conclude today's lecture we will see a little more about FIR filter design and some more things about FIR filters in the next lecture thank you


I'm Mark Friedman and I'm sorry if you guys didn't see me earlier, I just landed today in Singapore in this conference. So I'm from Pivotal, we do a databases, prepare them, most of them are open source now. So just give you an insight of how we actually worked around with Postgres and Hadoop. Why we did that, what are the takeaways that we got and what are the takeaways that the community should take a look at, just give you a quick overview. What happened to this guy? Sorry, we just gave him a minute. Oh, it's fine. Oh, it's fine. Did he just start? Yeah, that's fine. I don't know what you're talking about. You're going in and out. I'm just going to have a look. I don't know what you're talking about. I'm going to set me on a hundred percent. Is it okay? Is it okay? You move your slides now? You're not going to be? Yes, I can. I think it should be good. It should be fine now. It should be fine, I guess. Yeah. I think this one, I'm going to lose. It should be good now, I guess. Give me one of those shakes again, we can fix it. Okay, all right. Good? Yep. All right. Okay, cool. This one. Okay, so as I was saying, this combination is not something that has been imaginable long. I was in Madrid in the PGCon in 2014. I got this question. Why isn't Ausgust working with Hadoop yet? Why are we a closed barrier to the whole ASF community? Because if you look at it, it's not only about Hadoop. It's about the whole ASF ecosystem. They have Pashyambari and all at high. There are a lot of things that hang around there and they have myriad manifesto that something is doing something on HDFS and nothing on Hadoop and blah, blah, blah. So we're all logged out of that. Traditionally, we've been a relational database. And this is something we've always been good at, right? A word of caution. The slides suck. So please don't hold that against me. And so having a look, you know, we've always been a great relational database or standalone server. We start off a standalone server, right? And for Postgres, something that really mattered was the quality that we maintained, right? I think most of us here are part of the developers community. So we know how hard it is to get a patch in.  So that's the kind of stuff we have. But it all boils down to the quality of database that we've always maintained. So moving ahead, then over the years, we started moving towards OLAP capabilities, higher analytical functions, you know, making reports. And recently, I'll take the credit for that. So we have a grouping set functionality and all the queue, rollup, stuff like that. So we've always been a feature rich database. SQL standard compliance, we will mostly complain. I think we are competing on time. I'm not sure. We have some things which are not a part of this SQL standard, but they're good for us and for the customers we have. So we've always followed them.  It's present now. So even that complaint is gone. Strong transactional semantics. It's pretty hard to mess up transactions in Postgres, given the, you know, the variety of isolation levels we have now.  Interactive queries, I wouldn't call Postgres a real-time system, but it's a near-real-time system. I know, unless you're in, you know, using Postgres in an aircraft, I don't know if it's as done, you'll be good to go. You know, you'll have a pretty good response and the T-page is pretty high, fairly high enough to keep you happy. Relative to something like Hadoop, where you know, if you, you can't really do interactive queries with MapReduce, right? So yeah, that's a win for us. Now let's have a look, and this is a bit controversial. I don't want you to throw that to me. So what is it that we're not really good at till now? Now, maybe all of this wasn't really applicable ten years back or fifteen years back, but now given the scale of data that we work at, kind of, you know, how many formats of data we have around, the ad hoc queries that our data scientists wanted to do today, and, you know, the massive shifts that we have between our data formats. We don't support a variety of data formats. On this, we have one specific data format, the on-page format that we have in 8K files or whatever size we have. That's it. There's no other way that you can store your data. Now, somebody may argue that we support a lot of, you know, other data formats that we ingest into Postgres. That's a different story altogether. You take some data, you use some converters, you convert them into the native Postgres format. That's a different thing. Then you're following an entire ETL process. So what happens is that, okay, let's suppose that there's a data format called X, and, you know, you have SQL functions that convert from X to the native Postgres format. That's basically relational tables.  You stored it in Postgres. Nice and happy.  I need to do that now, but you don't have those attributes. Your conversion functions will not take care of that. What do you do now?  Yes, that's the only way. Eventually you get bored of it, and then your data scientist will say that you cannot do ad hoc queries or ad hoc experiments, right? That's the need of the R in terms of data science. No way to bypass the SQL, yeah, injection in the sense, okay, I'll come to that point three and one are correlated. No way to bypass the SQL interface. Now, here's the thing, you know, I have 10 gigabytes of data, and I really can't create, go to your data directory. Everybody here knows what a, you know, a Postgres database looks on this. So I can't go to your directory and create a new folder, okay, table one, four, four something, a random YD, dump my data into it and I'm good to go. Postgres knows my table is there. It does not happen, right? So that prevents me from ingesting large amount of data in whatever format I have. I can't really go and do that, even if I, you know, manually munch my X data format into the Postgres format, I can't really go and put physical files and expect Postgres to pick them up. That just does not happen. And other way around, even if I have my own weird C++ or C program, which can understand the Postgres data format, I'd be really stupid to go and touch the data directory, right? So the only way is I can use the SQL interface that Postgres provides. Now on this module. No resource management. That's well known, well complained about, so I won't even argue it here. Everybody says there's no way I can put limitations.  I can't really manage resources here. Scalability, yes. Because we are a process for backend server, we scale to a limit. It's not an infinite scaling system. We don't use threads and, you know, we're not a massive cluster system. There are limits to our scalability. And even for XIDs, for example, there were Thaidubis to recent times, but a colleague of mine wrote a patch, which I think got accepted, so there's 64 bits. Not sure, but yeah, we are getting there, but there's a limit. There's a ceiling which will hit when coming to scaling. And what I mentioned earlier, there's a whole lot of things waiting for us in the Apache Software Foundation ecosystem, which we just cannot touch because, you know, we... Here's the thing, look at it as Hadoop is the entrance to a lot of things, right? It's just not Hadoop. It's a lot of things that we can leverage off. We don't need to reinvent the wheel for a lot of things. We're close to it right now. So, okay, now Postgres moves to more of an MPP style, massively parallel processing systems. There's been a lot of great work done on the FDW sharding, this point data wrappers, and for a sorted data pushdown, I mean, we have sort keys pushed on, and maybe one day we'll even have query plans flowing around in point data wrappers. But then we also have the capabilities that HGFS offers us and Hadoop offers us. So, what do we do? So, pivotal. It's an experimental product which tried to combine the two of them. We won't discuss a lot of it. I'll go over it a bit, but then we'll come to what we learned when building that product. That product is called Hock. You know, it's now Apache Hock. It's been going to the Apache Software Foundation. I'll go over how Hock is related to Postgres. So, some of you might know that Pivotal did an proprietary fork of Postgres many years back. That was somewhere around 8.2, PGA 8.2, which was called Green Plum. Green Plum is an MPP engine, and a lot of code rewrite happened. They have all those parallel query execution and parallel processing. They're even going for, you know, plan distribution among multiple segments and blah, blah, blah. So, Hock was originally a fork of Green Plum internally. And the first objective that we had was making Green Plum work on HGFS. That was the first thing that we did, and Hock. And, okay, I don't know why it's not Hock. Don't ask me that. Just, I don't know. It just sounded cool. Okay, so this is what Hock basically does. And now it's completely open source. You know, we'll go over how you can contribute to it. So it's like, it natively works on HGFS. There's nothing like we're using some weird connectors, or, you know, we're getting data out of HGFS into the local file system and then working on it. Nothing like that. There's a library called libhgfs3, which we use, which directly talks to HGFS. And that's it. There's no middle man in between. Just, you know, just an FII. To write Hock, we need to add the truncate operation to HGFS. Why we need that? I'll go over it. But, yeah, truncate was needed, which was added to HGFS. It's called, it's in pivotal HGFS. I don't think we open source it, or maybe we did it. But that was something we needed to add. So, here's what we've got, right? The data processing in HGFS, scaling with HGFS, full access to write to the Apache code, everything in Apache became open to us suddenly. The ASF community came to our support. You know, we have a dedicated team, which works on the integration of Hock and Apache Ambari. We have some engineers who work on YAN as well. And two of our engineers actually became Ambari due to their work in Ambari for supporting Hock. So that's how ASF grows. And as I said, Ambari installed. We have the resources managed by YAN itself. So, suddenly, we're very happy. So, this is what it looks like. Something that's really cool here is the cost-phase optimizer, because, you know, the pose risk optimizer was developed over many years by Greatmines. That was leveraged off by Green Plum, which enhanced it further. And then it was used by Hock. So now, Hadoop suddenly got a system which had a great query optimizer. You know, the query optimizer actually became one of the salient features of Hock. And people surprised that something on Hadoop can have such a great cost optimizer. We'll go over dynamic resource allocation, which is actually done by the query optimizer itself based on the cost of the plan that is finally been executed. So, that's something great. ANSI SQL, that's directly leveraged off, leveraged directly off pose risk and can continue enhancing it. Madlib is another open-source project, a fashion madlib, which was developed by Fibital, which is basically a library of machine learning operators, which you can use to do any sort of weird KN and clustering and stuff like that. The interesting thing here is that it works with pose risk. So I'm not sure if anybody knows here, but back in 2013, we did a project with madlib in Google Summer of Code, so I was the co-mentor for that, in which a student in the pose risk community actually added some features into madlib. So that's the kind of correlation we always had with madlib and the people who have been using madlib are with pose risk and writing functions. So madlib is totally applicable to pose risk, not something specific to green thumb or Hock. A quick overview of the Hock history. It was originally called green thumb database on HGFS because that was the only goal it had. Then it went alpha in 2012. Then we have subsequent releases, and in 2015 it entered the Apache incubator. And hopefully it will be in the Apache top-level project soon enough. So I'm confused, which part of Hock is pose risk? We'll go with that. Okay, architecture. This is what it looks like. Basically, Hock is a... If you've looked at clustered mpgs assumes from the Xe or Excel, I love Excel. So you have a master save system. It's called the master and segments in this case. The master basically has the yarn resource manager, the catalog service, the master and the HDFS name node. So we already have HDFS up and running in this case, and name node is on the master of the Hock cluster. The data nodes for HDFS run on each segment.  It's a fancy term. Node manager is internal Hock node manager, and then we have segments. Now segments basically are physical boxes. One box is equal to one segment, but then that segment can be used to process multiple queries at the same time. So then it spawns virtual segments. So maybe the term's not correct, but imagine segment as a physical box, and it spawns multiple threads for different parallel queries. They are called segment as well. So that gets a bit confusing, but that's simple enough. So this part, the grayish part was actually inherited from Postgres through Green Plum. So again, the query optimizer is a lot of... The partial analyzer are mostly Postgres. We just added to the syntactical tokenizer and stuff like that. The resource manager is specific to Green Plum, so it's not something Postgres, but yeah. Optimizer, as I said, dispatcher, catalog service, they're all specific to Green Plum, but they are there, right? So this is the main client invoking code, and this is what we do. Interconnect is, again, an open source alternative to the Postgres communication protocol. FEBE, instead we use Interconnect, which is much more stable and you use state machines. You won't go over it much, but it's just there. This is what the execution looks like. Pretty standard, far so a planner. Dispatcher is an additional functionality here because dispatcher basically dispatches your plans to the state. So all the planning right now happens on master, and then the master dispatches your plans to the executors, which are the slaves. Our resource manager here checks if there are enough resources allowed and available to be executing this query if they are, well, no, queues are the slaves. All goes well. Or else, the source manager says that, okay, not able to execute this query, leave it off. Just another plan, so with the recent edition of parallelism in Postgres, we have the gather motion. This is a pretty similar gather motion in Hock as well. Distribute and then gather result. In addition to gather, we have broadcast and redistribution, which we won't go over here because they are pretty standard non-escalating, I think, and not relevant to our discussion. But yeah, these are three types of motions we have. Broadcast is basically sending tuples to all segments. Okay, this is something interesting. Now, what happened here was that catalogs started to become a bottleneck for Hock in the sense that catalogs only exist on the master. And whenever a segment needs to access something in the catalogs, it goes and talks to the master. So that sort of started becoming a problem with the curve because that was never built to scale at this level. So internally, a language called CAQL was invented, which is basically internal to Hock. If you're a client, you want to access the catalog and still need to use SQL. The CAQL proved to have had a lot of pool caching and stuff like that. So it basically optimized your catalog management. So this is part one of what's really different from Postgres and Hock. So imagine, okay, I'm not saying that Postgres needs to first become a trusted system in order to move to Hadoop or HDFS. But if we do want to move to HDFS, that's something we need to look at because catalogs really can't live on HDFS. We need to store them on a normal file system. But then how do we make sure that the normal file system does not become a bottleneck? The catalog access eventually does not prove to be a bottleneck. So this is one part of it.  Virtual segments, which we discussed. There's a physical box that's called a segment. Virtual segments are the processes that are spawned by that physical segment to execute parallel queries. Segment is pretty much stateless. It does not maintain any state. Self-destroyed plans. Self-destroyed plans are pretty interesting. That's something we want to go over. So right now, if you know the Postgres query... Is it the front end you change or is the back end you change from the raw file system to the HDFS? The back end. The front end is mostly the same. In front end, you have enhanced the capability like resource elevation, which is not available in the Postgres. Yes. So you can actually go and set your resource limitations. Don't allow more than 2 GB of this to be or something like that. But from front end, it not much has changed apart from some additional features I mentioned. The core changes went into the back end to support. I know this one, like I can query the whole... Who can file the Postgres query by status? The integration between the status and the... Hadoop TV. Hadoop TV, okay. That probably... I think this is more of a marriage of NPP systems with Hadoop. That's what we call it. We're not saying that they're not comparators. Just to be clear, this is not trying to be an OLTP system. No. Interconnect is the replacement for FEBE. It's more of a state machine and it uses TCP and UDP both. Which provides a much better performance benefit with UDP and provides more stability with TCP. We won't query it too much. It's more of a state machine. This is what a state machine looks like. But don't ask me questions about it. So yeah. Okay. Transaction management is something new again. So something that proved to be a problem in HOP is the presence of 2PC and MVCC. Now how do you actually allow multiple writers to the same table or to the same top? And remember, the biggest problem with HDFS is that it's an appended only system. You can add to it. You can probably truncate it with the new features, but you cannot override it. So how do you manage that? So for that, and that's the problem with wall as well. You just keep on adding wall. And for pages, they just keep growing. How do you figure to what extent is a page valid and what part of your page needs to be truncated? Maybe a transaction did not come in, but the data was already written to the page in HDFS, and you can't delete it. You need to truncate the entire page, which is not a great idea. So what do you do? Okay. So basically something here is called a swimming lane protocol. Swimming lane protocol basically means that, in a nutshell, it allows multiple writers to create multiple files for the same table on HDFS and all of them write to their own files. And there's a separate table, not in HDFS, in the catalogs, which maintains the page, the HDFS files, which are relevant to one table. So because it's an OLAP system, we don't expect a lot of transactions. OLTP would have a million transactions together. This would have gone however. Million files for one single update. No, thank you. OLAP, we don't expect that much for load. So there are lesser transactions writing more data. That's good for us because maybe there are five transactions. Each of them writing 10 gigabytes in one file, which is fine. The catalogs don't grow that much. Catalogs only see five files, and that's it. But data on HDFS is growing exponentially by each writer. So writers aren't really blocking each other. They are writing concurrently, and there's just a bit of a more catalog overhead for maintaining which files are related. Updates is the standard, you know. But if you have multiple versions of the same... You need to update all of them so that they have a catalog, right? So catalog knows which files are relevant to each of the tables. So you need to go and write to all of them. So yeah, yeah, and that's it. Are the extra files covered by the Hadoop kind of thing? Because you have distributed kind of stuff? Yes, that's it. So all the distribution and the... Distribution only? How do you authentify this? So data is on HDFS, right? So data is on HDFS. So it's been handled by Hadoop, that's fine. Hoc only maintains internally not an HDFS, which files on HDFS are related to which tables. So they're catalogs, right, on the master. So there's another new catalog, which basically tracks the file node IDs on HDFS and the table ID, which is known to Hoc. So, you know, we get the ID from PG class and maintain that, okay. These five or six files are for this relational ID. It's kind of a work process for me. Yes. You can develop and... You can definitely call it that. Okay. Does it create a replica asynchronously on the side? Let's say you've got another writer. How does it get the next file? Yeah, I think that they use a standard. So we don't really mess with HDFS ways of getting files. So yeah, whatever method HDFS uses. Not an HDFS code. But we let HDFS handle the entire fine writing. Okay. So that is something that's good. And so that's called a swimming lane transaction. Each writer writes in his own swimming lane and we just maintain which swimming lanes are led to which table. I'm out of time. Okay. Resource management. Another critical area for Postgres, which we don't do, we should do. Now there are two level of resource management that Hoc does. One is Yarn or something external, which basically gives you resources. So Hoc sits on top of Yarn. Whatever resources Yarn gives you are the only resources known to Hoc. That makes Hoc pretty readily available on shared clusters and in clouds. You can use commodity hardware. You can create shared clusters. You can just manage your resources, allocate to Hoc itself through Yarn. And then Hoc's internal source measure, which are basic resource queues, manage available resources internally to each different query. So there's Yarn, which allocates X amount of resources to Hoc. And then Hoc internally manages those X amount of resources for each different query. How that resource allocation is done is again, you can do resource managers. You can set your limitations. No query should use more than two gigabytes of, I don't know, maybe work mem or memory itself or this much of CPU. You can actually set the amount of CPU percentage that is used after the query is done. So if it's at 37%, it should not exceed 50%. So it basically estimates how much, so that's basically an operator CPU usage and you can estimate how much a CPU usage will happen here. And then it's mostly a predictive system for these cases. But yeah, it does work. Hiacal resource queues basically are the pretty standard computer science hiacal resource queues. We have aging of processes. So what happens if five queries cannot be executed at this point of time? They are put into queue and then the age and gradually, when resources get available, they are pushed through. This all supports prioritization. So you can set the priority of a query and there are some cases where you can say, okay, pack off whatever number of queries you need to do, but execute this one. This is business critical. That's another way. So this is what it looks like. This is the Hoc QD. This is basically the master. The fault also is only different. And this is what happens. So Jan and Mizo's interact with the resource broker, which is basically the Hoc resource manager's entry point. And then we have the policy store, what query gets, what kind of resources, the resource allocator and request handler. So then the optimizer said it's a cost per scene, right? The optimizer interacts directly with request handler. At the optimization level, we decide if the resource is available or not. At the execution level, because when we start executing, we're already on these slaves. At that point, it doesn't make a lot of sense to roll back because you already send the plan to your slaves, right? You always spend the resources, the network IO, whatever. It doesn't make sense. So that's what it looks like. Storage, again, there are row-oriented and color-oriented tables, and we can compress them. Basically, it was more about inventing new scan methods, access methods, and that's it, not much. PXF is basically the extension framework. The extension framework allows you to... It's basically a library of access methods. So you can start reading accumulator tables in Hoc. Just plug in the access method into Hoc and then tell it where the accumulator table is available. It's good to go. So that's basically a black box for Hoc. Okay, how to contribute? That's a site. There's a pretty standard Apache process. There's a JIRA. You can go and look at the bugs. We have newbie bugs and there's a whole wiki on how to contribute. So the community is pretty helpful. You can definitely go and have a look. You can actually catch me outside and ask me if you have any specific questions. Okay. You can find it on the wiki. Nothing too special, pretty much like postcard except the JIRA process. These are the things that we really want to have indexes on top of HDFS. That's something that the community is really looking forward to. JIRA replication, there's some work going around it, but yes, the more the merrier. Integrated ecosystem, that's a huge pain point for us right now. We have limited resources and we need to integrate with the entire Apache framework. There are tons of them, accumulo, HPACE. I don't know. I can't even remember the names of them. So if you work with them, if you experience with them, please come to us, talk to us, add some access methods of a lot of integration work is happening. We're really thankful for it. And maybe you can even get to become an Apache Hawk Committer. That's something good. Okay. This is all references. Now, this is something that's interesting for us as opposed to this community. What does it boil down? So suppose I wanted to make Postgres work on HDFS today. These are the two main areas that I need to look at. Why? You know, the point one, the transaction recovery is basically because HDFS is appended. So once you get things working in HDFS, you can get to work with anything because most of the Hadoop ecosystem members use HDFS as their, you know, file system. So once Postgres starts working in HDFS, we're good to go. Now, that's not something very easy right now. You know, off the bat, it's not something like adding five or, you know, 10, 15 methods and getting it to work. It's a lot of rework for transaction recovery. I doubt that it'll ever make it to the Postgres core. Tom would probably shoot the writer. So, okay. Now, what poses someone from Hawk? Eventually, when foreign data wrappers start sending query plans or, you know, start acting as the connection points between shared nothing systems to build them all into one whole cluster, right? Then we will need to figure how we handle metadata, the catalog tables. Right now, it's fine. We just, they're all acting in independent systems. But once they start depending on each other in terms of catalog, we might need to look at how do we scale catalog tables. Self-describing plans are something that Hawk does to avoid that problem. So, if you look at the amount of catalog access we have, when we're executing a plan, that's a time where we do a lot of catalog lookups as well. Not saying we don't do that during parsing or planning, but execution requires a lot of time. So, what you could do is self-describing plans. Basically, decorate your query plan with the needed catalog data. And one aspect of that is that you get to look at the visibility of that specific query plan. So, you know, you don't need to call the heap tuple satisfies MVC methods all the time to check if this tuple is visible to this user or not, or this transaction or, I'm sorry, this transaction or not. Basically, get all of that data decorated in your query plan. So, your catalog accesses and your CLog accesses go down drastically. So, the number of times you actually go back to the master and talk to it and access data, maybe CLog or not CLog, basically visibility functions, goes down massively. And if you need to have some operator data, like, you know, some operator types or if they are multiple, they can use in merge or not. That's pretty regular data. So, that can all be gotten at plan time and added to the plan tree itself. That makes the plan tree pretty heavy. That's one downside of it. But yeah, that is something that can be done. Multiple types of tables. I think there was some work being done to support columnar tables. I'm not sure where it went, but yeah. Then postgres is definitely moving in that direction. Compress tables would be next, I assume. Swimming lane protocol. So, if you don't do 2PC, swimming lane actually allows you to do the multiple writers, the concurrent updates thing. And that is something that's, if it's not impossible, it's pretty bad to do with 2PC. That's something we might have to look at if we want to move to HDFS. Not sure, right now, we only support two isolation levels. I'm not sure how much it will integrate with the variety of isolation levels we have in postgres right now. But that's a star. That is one way of looking at it. But the writing on the wall is that 2PC maybe won't support the needs of HDFS. Or maybe we won't be able to exploit HDFS capabilities completely if we only stick to 2PC. Beyond wall. Now, one thing is that for a file, right, you can just, in HDFS, you can just append to it. So there's a physical length of that file. But now, some records at the end of that couple, or at the starting, are not maybe applicable. Or, you know, they need to be deleted. So what do you do? You define a logical length. Simply say it says that, okay, only maybe 100, first 100 bytes of that file are valuable. Just read it there. And if there's any access beyond those 100 bytes, you know, there's an offset. Ignore it or just error it out or do whatever. But do not depend on the data beyond those 100 bytes. That's the logical length of your files. And that basically is a one-time write into your catalogs or wherever you're storing that data. But that saves you a lot of pain from appending a lot of wall records. That's one thing we should look at. Second part of the resource management, which is, again, something that is discussed all around, virtual cost-based dynamic resource management is secondary. We need a resource manager per se first. And the way HOP does it internally is using resource queues. So if you want to move to Hadoop or, you know, HDFS, Hadoop specifically, we will need to have our own resource manager. And we could probably start off with a source queue. Resource queue is pretty simple. We, you know, we define 10 slots and you say that aggregate of those 10 slots should not exceed 5 GB of data or 70% of CPU utilization. That's not something very, very hard to implement but might be hard to test. But if we get that, we will have a lot of customers happy today. And if we ever decide to move to Hadoop, that will integrate very well with Yarn or resource, you know, the native Hadoop resource managers. Okay, so that's mostly it.  The biggest two things that I feel personally need to be done if we want to move to Hadoop is, I'm sorry, R, the transaction management, the recovery part, the wall part, seeing how we can scale up our metadata access, the catalog, and as a bonus, resource management, right? If we can get resource management in place, I think that would be something really cool to start off with. I hope somebody will come up with a pass for that. So, yeah, thank you, man. If you have any questions, I'll be glad to take them. Tables are regular tables with regular columns. Bank are integrated. Yes, they are. How does that map to unstructured data, JSON, start, and all of that? So, we use a regular JSON passage that we have opposites, right? So, I mean, you have JSON view, right? So, that's the same format. You just take that and put it into HGFS format. So, the only thing that changes is the page layout. That is posters, right? The table headers and the kind of data that we put in through structured, and then we have the footers telling how many tuples are there. That's the only thing that's changed. Rest of it, if you can convert, you know, if posters SQL or internal functions are converting JSON into its own format, it's good. We use the same to write them to HGFS. I'm guessing you guys are not using the shared buffers? No. That's something that I have to do. Have you added a new storage manager for HGFS? Yes, that's right. Storage manager was the biggest part that we had to write. Was it very difficult to understand this? It was. It was very difficult to get right. You know, it was not difficult to write, but to get our HGFS. Any other questions? Sure. So, the integration was possible? Yeah, we definitely can do that. I think there was somebody writing a connector for that. And it should not be too difficult if you're separate. If you treat them as two separate entities, you can probably use the connectors available. I think there's somebody writing a font data for that. So, yeah, this is definitely possible. Any other questions? Nested loops don't make sense for this kind of data. So, you don't support that? We do, but that's only if you're very unlucky, but they don't make us any sort of sense. If we actually say that if you see a nested loop join happening, you should contact us and let us know. We'll see what's going wrong. No, I mean joins in general. Joins in general, yes, that's the same problem as Hadoop. Joins don't make sense in Hadoop, but since Hoc essentially still is a relation system, we need to support joins, so we do them through spawning multiple workers and stuff like that. But yes, they don't make a lot of sense at this stage. So, there are talks of making joins better and using some in-memory systems, but it still didn't work, not something that we have really worked on. Essentially, you took the green-prem parser to continue the storage manager is HDFs. That's what you got. Yes. So, this was originally called green-prem on Hoc and HDFs. So, what are you doing in the back-end scenario? Yes. So, eventually... Source allocation of front-end as in... So, you can set your resource limitations, right? So, you as a user might want... might not want your individual queries to exceed some set of resources. You might have ten queries and you want to ensure that each of them gets a fair slice, even if one of them is very heavy. So, you know, even if there's one guy... there's one elephant in the room, you don't want it to hog all the resources, but you want it to wait. So, you can set your resource limitations, but that does not mean that that affects the amount of resources we get from Yarn. So, Yarn resources are something that are... you know, there are not something that you see as a user. What you see is the per-query resource management that you set at your left. So, if a hog gets X resources from Yarn, that's not something you can do, but you can tell hog how to invest those X resources in your queries. That's a hierarchical point. Okay. Thank you, guys.