In this video, we will discuss about different kinds of measures in SSIS. If you understand these different kinds of measures in SSIS, you can plan your measure much better Okay. Now, first thing for a measure when I say measure means this the customer amount is the measure right first thing for a measure, you need some kind of a formula for example, you can see that this by default which measure has been created, if you go to the properties, you will see that it is actually using a formula that is sum. And you can see there are lots of other types of formulas which you can apply on this measure like count min max, etc. Alright. So, first thing is that whatever measure you create in SSIS is attached with a formula a mathematical formula like sum or count or average or something.

Now, these four formulas which are attached to the measure they get executed in an additive manner across all the dimensions. Let me again repeat this sentence The second point is very important though formulas on the measures that is some count etc They get applied in an additive manner across all the dimensions what do I mean by that? So, let us try to understand this attitude behavior what exactly it is now, if you see at this moment you know we have this customer amount. So, let us say I go and drag and drop the customer amount here and let us see I go and drag and drop the product name your analyse I drag and drop the country name okay. It means what? Now let's look at the actual data this is the actual data here if you see here at this moment product name with toys in India, right so it is 500 plus 1240 plus hundred.

So, if I go here you can see toys for India is 1840. So 1840 is nothing but your 500 plus 1240 plus hundred that means, you selected product name and the country name it added across all the dimensions and has given you this total over here. So, additive means, it goes across all the dimensions takes that formula at this moment the formula what is applied on the measure is sum. So, it goes to all the dimensions to all the values of the dimensions and totals it up and displays it here. So, this is termed as added to nature, right. So, if you if you go back to our cube structure, so, if you look at the formulas like sum, count, all of these are additive in nature.

But now, there are certain kind of scenarios where victims not want the measure formula to run in a full additive behavior, we want it to run in a partial additive behavior or I can say rather in a semi additive behavior, what do I mean by that? For example, you can see over here you know, we have this India invoice means we have the country name and product name and we have the amount. Now, let us say I want to say that for a particular product for a particular country, can you tell me the least amount of sales done for example, over here now, we have toys in India. So, we have 500 we have 1240 we have 100. So, I would like to display the hundred values saying that from India and toys, you know, for this combination, the least amount of sales was hundred. So, in this case what has happened is for this formula for this kind of formula for min and max.

The addition is done only on toys and India and that also On the minimum value, please note we are not trying to add all the values of toys in India only for that minimum values of toys and India combination we want to do the totaling up that means, we want to apply the Formula One only for certain dimension values and not for all dimension values. In other words, this addition of the least of the amount is only on the least values and not on all the values this is termed as semi additive semi additive means, when your formula is applied not to all the dimension values but only to some dimension values is termed as semi additive. You can also visualize that this additive behavior and semi additive behavior is more from the formula perspective means what? For example, now let me go and add a new measure here.

Now, when I say sum and count These kind of formulas have to be applied to all the dimension values because when you say sum sum means the total of all right, but when you say that you want the minimum value or you want the maximum value, it will not run across all the values right it will only run across certain parts of dimension values. So, this additive nature and semi additive nature is more from the formula aspect. So, when you take the requirement you know for for msbi or for SOC s you know you should be able to understand from the requirement that that measure will be semi additive or it will be additive right. So, at this moment let us go and select minimum. So, minimum is semi additive count is fully additive some is fully additive maximum is semi additive, okay. So, we can look at the formula and from the formula nature you can guess that this is semi additive behavior or a fully additive behavior.

So, at this moment let us see the demonstration of semi additive. So, I will say okay I want to get the minimum sales of the customer amount right. So, let us rename this and say minimum minimum value right and now, let us go and build this cube up. So, let me rebuild it because remember you have added a new measure. So, if you have added a new measure you need to ensure that you build once the whole cube So, that it is everything done. So, let us go back to our browser let us reconnect because we have seen so many things.

So, now, let me go and put the customer amount let me go and put the product and the country. So, Product Name and let us put the country name also let us put the minimum value and now specifically let us see toys in India. So, you can see four toys and India hundred is the minimum value so, for from toys and for the sale of India this combination has the minimum value. So, now this combination this minimum value is applied to only certain rows of the product name and country name and not to all of them right. So, example if I go back here and that is right you know for toys in India This is the least amount of sales Now, look at Nepal and shirts in this Nepal and shirts there there is a minimum value because both of the values are same. So, I expect him to show 700 and that is right okay.

So, this is termed as semi additive behavior. Now, there is one more kind of scenario, which a lot of times we come across project in those kinds of scenarios, the measure is neither fully additive, the measure is neither semi additive or I will make a statement here the measure is not additive at all. For example, if you see at this moment. I have four countries registered in my database at this moment, but I have made sales only to three countries at this moment I have made concealed to us, India and Nepal, but I have made no sales at this moment for Sri Lanka. Now, I would like to know that for how many countries are made sales, is it three is it four. So, from the four countries how many countries have made sales.

Now this value is constant, irrespective of anything, it is one single value. So three countries is what the sales you are made. And then we'll remain three across any dimension across any dimension values. So these kinds of measures are termed as non additive. So one is we have fully additive or you can say additive in short. The second one is we have semi additive, and the last one we have non additive.

So let's see a simple demonstration of non additive Nature. So we'll go here again remember, additive semi additive or non additive depends on the formula. So apply your logic first look at the formula. And you will understand that it is an additive nature of or of a non additive nature, right. So I'm going to go and add a new measure here. Now, I will say, I want to know the Distinct Count the Distinct Count of sales made to every country.

So you can see here I've selected country ID, right. So for every country ID, what is the kind of measure selected, sales done right? So I'm going to select country ID here. I will say okay, you can see now at the left hand side, because this is not going to do any aggregation because it is not going to do any kind of additive or non additive nature. He has created a separate measure group for it. You can see this fact customer has the additive and semi additive measures But he has created a separate kind of group saying that this guy cannot be clubbed into this right.

So, here I will say let us go and name this as country number of number of country made sales okay means in how many countries are made sales from the total four countries right. Now, let us go and process this cube yes slow so that the cube is processing. And now let us go to the browser tab and let us see the results. So, let's reconnect. So, here I go So, if I say okay let me show me the customer amount. Show me the minimum value and if I say show me the number of countries I've made till now sales he says three Countries you have made sales great.

Let us see I went drag and drop a product okay. So let us say again drag and drop a product and you can see that it is still three actually okay. So don't total it up if you total it up it is wrong, but you can see that he says okay let me put up country okay. Actually this report does not make sense, but you can see here he say he says okay for us is the only one type of country of Seoul for India is only one type of country have sold in Nepal is the one country of Seoul In short, this is non additive, it does not keep adding to the dimension values right. And for such kind of a non additive kind of a field. It is more at the total level.

So this report makes sense. Saying that, okay, the total sales is 4652. And the number of country you made sales till now is three out of the four countries, right? So remember three guys two measures additive semi additive, and this kind of a single value which is non additive. Now, there are approximately 11 formulas if you go here to the cube structure. And if you go to the properties here, these aggregate functions or formulas are of 11 types, and depending on the formula it is additive semi additive or non additive.

So, what I'm going to do is I will not be demonstrating all the 11 formulas because you can see that I demonstrated distinct a demonstrated mean some was already done, okay. So, the formulas are pretty easy, you can experiment it yourself. What I will do is I will go and I will flash up a small screen here, which I will hold it for 20 to 30 seconds. And in that you know what I've done is I have given the name of the formula. Then I've said that it is semi additive or additive and a small description of what that formula does and you can see from the formula itself, it is very easy to know. For example, Max's max So max of the total sales count, total amount of sales done right.

So the formula itself is self self explanatory, but I would like to go and flash the screen here for 2030 seconds, have a look at it, and then that will make you understand, you know what kind of formulas are additive and semi additive. Now from that 11 formulas, one formula I'm excluding that is by account for by account, you know, it is a different kind of a kind of measure, you know, where it is more related to accounting. So we will be having a separate video on this you know, where I will talk about Chart of Accounts and balance sheets and etc. So this I will not be explaining in this video, but I will have a separate video for this in the coming times. So all the other formulas are self explanatory. So let me flash this small table here.

Read It For 2030 seconds, and then we can conclude this video. So that brings us to the end of this 15 minutes of video and why this video I just wanted to send you a message you know what are the different kinds of measures we have, so that when you go and plan your measure, you just don't think additive, but you also look at other aspects of the measure. Again, a small request here, you know, if you are really liking this msba series, please go and put your feedback on facebook.com slash questpond and let us know that if we are moving in the right direction or not. Now the next video is again an awesome video. I think this that would be my best video that I've created in this whole series. The next video is on SSIS performance tuning.

So the Don't miss the next video is going to be approximately one hour of video where we will talk about how to fine tune SSI as performance. So we have more 17 hours still pending approximately, don't give up. I'm not giving up and I'm sure that he will also not give up and let us try to become a true msba professional. And let us do all the three aspects of msba Let us not just concentrate on the SOC s, or just on SSIS or just on SSRS let us try to do all of these three things so that we become a true msba professional. Thank you so much.