In this video, we will try to understand a very important concept in SSE is called as KPI. KPI stands for key performance indicator. KPI helps to define and measure progress of a business goal or an activity. Now, let us say we want to set some targets for our customer sales and we want to measure that will we be able to reach that target or not? So, in other words, we want some indicators, which can tell us that you know our performance is good or bad. Now, in order to do this, we need four things.

First, we need to know that our customer sales is at this moment, so is it hundred 200 300. So we need to know the actual value. Second, we need to know that what is the goal what is the target which you want to achieve, so you If you see that, okay, our sales is x. So probably do you want x plus 10%? Or x plus 20%? Third, you'd like to know the current status of your sales status means is it good?

Is it bad? Is it okay? And the last thing what you would like to know is that at this rate of sales, will we be able to reach our goal or not? In other words, we would like to know the trend, is it moving in a positive direction so that we can reach our goal or not? So in other words, we need four things. Current Value, what you want to achieve, what is the status and the trend?

Is it positive or negative, which will help us to understand that can we reach our goal or not? So we need at least these four indicators to measure our performance or to measure our progress. So KPI helps you to define all of these four things. So you can see that on the SSIS tab we have the KPI tab. So let us click on this KPI tab and over here, you can see this link over here saying new KPI. So let us go and click on new click on this and once you click on the new KPI, let us give a nice name to the KPI.

So you will see here Okay, this KPI is customer sales progress, okay. And you can see over here, this is the value. Remember I said we need four things. So you can see that this is the value, this is what you want to reach the goal. This is the status so you need to write some expression here for the status. And then finally you have the trend so you can see all of these four things you know KPI helps you to define right.

So first thing I can see here, okay, my value which I want to measure is actually Customer amount okay. So, this is my current value and where do you want to reach? So, yes I will say okay. So, whatever is the customer sales amount I want to reach let us say 1000 extra on that. So, in other words if 100 is my target is my current value then I want to reach 1000 plus hundred that is 1100 Okay. So, this is my current value and this is what my goal is which I want to reach.

Now, the next thing is the status now, the status is nothing but it tells that the current value what do you have depending on that value How will you put the status is it good? Is it bad or is it okay. So, in the status indicator, you can actually write an expression like this you can see I have written expression here. I can see that okay case when under certain condition if things are Good the value will be one if things are bad, the value will be minus one. And if things are okay the value will be zero. So KPI in SSIS whenever it talks about the status indicator, it rates them with the value one minus one and zero.

So one indicating good minus one indicating bad and zero indicating okay and you can accordingly choose the colors from here you know saying that Okay, do you want to show a green red you know such kind of color what exactly right. So, now what I can do here I can see that of the case when the customer amount is greater than 1000 that means it is a very good condition when the customer amount is less than thousand then it is a bad condition right. And in remaining condition, I will just say everything is okay. So that means zero. Now the final thing is The trend now if you see the trend right in trend what we should be doing is we should be comparing the previous value with the current value. So if the previous value is more than the current value that means it's in a downtrend if the current value is more than the previous value that means it is an uptrend right?

So over years in order that to set the trend, we need to compare with the previous value and the current value. So in order to get the previous value, we need to put the core something like this. So you can see here what I'm seeing is I'm seeing that okay go and take the time dimension and from the time driven dimension take the previous member okay. So whatever is the current time dimension from that just go to the previous member and from that take the customer amount, okay. So I will say, Okay, if this please vs member which is coming from the customer amount if it is greater than the current member you can see now this is a current member This is the previous customer Amount value okay. So, if the previous value is greater than the current one that means you are in a downtrend minus right if the previous member value is less than this that means you are in an uptrend or else it is just siedel right.

So, for a trend you know we need to actually compare the previous value and the current value to set the trend right. So, now let's go and save this. Now again, you know, this KPI has to be processed. So in order to process the KPI you can see the left button over here process. So let us click on this and let us try to process the KPI so you can see that he's processing the GPI when I say bye Isn't the KPI means he's actually processing the queue. Good.

So everything done nice. So that it is everything deployed everything processed. And he's starting writing everything to the fact partition, you can see that when the process cube is working down below we also get the status saying that what is happening right slow so that the cube is processed. So now let us go and see in the browser the kPa value. So let us go back to the browser and remember, we have made a lot of changes. So you need to reconnect right or hence you won't be able to see the cube data.

So now what I'll do here, let me first go and drag and drop all the KPI. Okay, so you can see that I've dragged and drop all the KPI values, and it has no now go and drag your device. Ok. So, there it is you can see first thing we have the value of the sales and then plus thousand what is the goal which we want to achieve right and now you can see over here you know there is minus one and that is one and also here you can see the progress. So, it is saying that okay if the value is less than thousand right remember, then it is minus one, but if the value is more than thousand, then the sale progress is good so, it is one, okay. And you can see over here now this is the the last field over here is a trend.

So, you can see now, this is 600, the previous value and this value was 400. So, 2011 sales was less than 2010. That's why the trend is minus one. You can see over here the hundred value is less than 400. So, it is minus one but look at 2013 and 2012. We are seeing here that 700 is more than 100.

So, the trend is positive So, you can see even the trend calculations are happening properly good. But now, this view what you see over here is a developer's view means over here seeing the values as minus one one and zero is fine. But when you look at end users you know, you know for them you know, it would be great if we can show something in a color coding, if we can say that okay, like green color is for one and then red color is for minus one. And you know something is for zero right. So, if we can show some kind of a color coding, rather than showing such kind of a thing, that would make more sense right. So now, you know this showing of the color coding depends on the user interface means for example, if I go and see this KPI through Excel, so let me go and connect to excel.

So if you remember we had corrected the queue through Excel previously. So first thing is We need to get the server name so quickly that we go and check this KPI via Excel slow. So let me first copy the server name here. So I'm going to go and copy the analysis of this server server name. So, you go here and some analysis services. Copy this next.

Next finish. So when you go and see the KPI using Excel over here, you know you would see that the color coding comes properly you know, because the user interface of Excel is rich, right? It supports such kind of a color coding. So for example, you can see over here you know, when I click on the value, gold status, the trend you can see that how the color coding is coming over here so you can see this sign here of circle the sign year uptrend. Let me go and also pick up the years. So I will go and become the year on the role level, okay.

Yeah, no, that makes more sense. So you can see now, how are we showing rather than showing minus one and one and zero you can see that it is showing an uptrend, it is showing a red sign it is showing a green sign, right. So when you're seeing the KPIs to the browser view over here, this is a developer view. So in developer view, you actually see the values you know, and end users will not connect to this browser and users will connect to such kind of Excel sheet right? Great. And also, if you want to go and see the KPI you know the KPI color code And the KPI values.

What you can also do is you can see from this KPI table so so you can go to the KPI tab. Now this view, what you see over here is a form view. Form view means you when you're actually filling the values at that time this view comes in. So you can see over here when you move your mouse, it says that you can also go and see the browser view. Okay? So when you click on the browser view, you can now see the KPI with the color coding, right?

So value gold status and trend. Okay, so one is you can go to the Excel and see the view where you can see the color coding. Or you can go, you know, to this browser view over here in the KPI tab, right. But normally end users connect to excel. This is again, a developer's view over here. So this is only for testing purpose, what you see it is not that the end users will log in here and see this view.

So I hope that you enjoyed this video. In this video we were trying to understand you know what is KPI and What is the importance of KPI when it comes to measuring progress when it comes to measuring success. Thank you so much.