Hello everyone. In this video we will talk about calculations in SSIS. calculations is one of the biggest and most important topic in SSIS. It includes many small small sub areas, and covering all of those sub areas in a single project is almost an impossible task. And hence, instead of using our ongoing project, we have created a new project altogether for calculations. The name of the project is sample multi dimensional project.

This project contains a cube called SSA is dw, which contain three dimensions, customer product and time. And this cube also contains a measure group table called facts sales, which have three measures defined inside it, amount, tax and factors count. These three dimensions have a relationship with this fact table. Now let's not waste time. Let's get into calculations. So let's click on this calculations tab.

In this calculations tab, we will get an option to create three things, one, create a new calculated member. Second to create new name set. Third, we can create new script command. In this calculations tab, we have something called as script organizer, which will list down all the existing script command name sets and calculations. We also have one somewhat one thing which we did created, which get automatically created when cube was created and that is this calculate script command. It is nothing but it is the one which controls the aggregation of the leaf cells in the queue.

Confused that simply deploy the cube and let's go to this browser tab and Let's simply say reconnect. And let's browse the queue by putting this amount and customer name in the screen, and what you see is, every time I drag something into this browser browser in this browser designer, the value start populating and this amount is nothing but it's the combination for example Initially, it was the total amount and when I added customer name here, now amount is displaying with customer name. If I drag product, then amount will be displayed with customer name and product both and every time the total value is getting displayed is the total amount of this customer this product. If I remove customer name, there is a total value of jeans, keyboard PC and T shirts. That means something is getting aggregated every time we try to display something or Total is getting displayed.

And that is possible because of this calculate command. If we remove this calculate command, then it will not work. Let's go and deploy the cube. And let's test it. deployment is done. Let's go to browser tab again.

Let's say reconnect. And let's drag amount this time. Now you will see that amount is not coming because it's not possible to create an aggregated value of amount because we simply removed the calculate command. Now try to track customer name still, it's empty. And hence, it's very important to have that calculate command in place. If you remove it, accidentally or purposely, your cube will not work in the expected manner.

So let's go and create it again. Simply click on this new script command and simply write down calculate su deploy the cube. And it's done deploy and processing both is done. Let's go to browser tab again. simply say reconnect. And now if we drag amount again, again we are getting 32,000 as value.

So just now we understood the importance of calculate command. In the next slide, let's go and create a new calculator member. for that. Simply click this button new calculated member, and let's name it as total sales amount or total bill amount that will be the better word and it is nothing but the sum of existing amount and tax. So it's going to be simple. In the expression tab, we will write the syntax or do we will write the logic Simply drag the amount and put plus and then drag the tax.

Now what we write here is MDX syntax. We are going to have a detailed video on MDX query soon, so don't worry about it right now just concentrate towards simple MDX queries and try to understand calculations in a better way. Now, other than this name, and this expression, we have something in between, we have something called as parent properties. Currently, this parent hierarchy is set to measure and parent member is disabled. Just leave it as it is for now. We will talk about it after some time.

Secondly, total bill amount, it's not readable, right? So let's make it readable by putting some space in between. But now we have to make sure that there is a square bracket surrounding it. Simply go and deploy this cube deploying processing. It's done. Let's go to browser tab again, let's say simply reconnect.

Now let's drag amount. But just below this faxes folder, you will see total will amount as well. Let's drag it as well. And let's drag tax as well. And you can see that total bill amount is a sum of amount and tax. If we want we can drill down further by putting customer name and we are getting what we are expecting.

Now let's improve the improve this calculation with the help of some custom properties. Again, I will say forget about this parent properties for now. We'll come to that soon. One of the property which is very important here is associated measure group right now. Our total bill amount calculated member is displayed displayed as an independent measure. If you want, you can make it as a part of faxes measure group.

For that, we have to say associated measure group will be faxes. And if it required, we can also go and create a custom folder I can say custom calculations like and I can keep my new calculations inside it as well. Now, let's go and build us a cube again. Sorry, deploy the cube again, it will deploy and process the cube both. Now let's go to browser tab, and let's say simply reconnect. Now you will see that inside measures, we have faxes and inside faxes, we have amount faxes count count and tax as well as we have one more folder that is custom calculations.

And inside that custom calculations, we will see total will amount this custom calculations folder get created. Because we chose a custom calculations as a display folder here, you can put whatever name you want. And you can select whatever measure group you want. We'll talk about this visible and other properties soon. Just leave it for now. Next feature of calculated member worth to discuss is this format string.

Here we will specify the format in which we want to display the value in the client. For example, here we can put something like this zero comma, triple zero double zero, it indicates we would like to have a comma in our final amount as well as dot and after.we are supposed to get to two decimal digits. So let's deploy the cube once again. And let's get into browser tab. And let's simply say reconnect. And let's try to drag total bill amount now.

There is no decimal and there is no Thousand separator symbol as as well, right? Why? Because whether to support format string or no, it all depends on the client we use. Let's try it in Excel just click this analyze in Excel button, it will launch an Excel we have to say enable. And let's select total amount now. Now you can see that dot and this comma, that is thousand separator and decimal separator.

Both are coming. And as I said before, support for this format string completely depends on the client we use. So it's not a best practice to put format string here. What I expect is, it's better if you put this kind of format string in the client itself, use the power of utilize the power of client that is Excel SSRS, etc, and try to get this formatting. Secondly, I personally believe that cube is meant for business logic and tools like SSRS QlikView Excel, they are meant for presentation when it comes to cube. So presentation part should be handled there instead of putting it here.

So let me remove this thing again from here. And let me deploy the cube once again. Let's go to browser and let's test the cube sirica Connect. Let's drag the total bill amount it's working. Now let's get into the next level by creating a one more calculated member called contribution. This contribution member will work with this year mandate hierarchy and display the contribution of sales amount with respect to its parent.

For example, when I asked contribution to display with date, that means I am expecting to get sales amount contribution with respect the month when I asked contribution to display with month in that case I'm expecting to get a contribution to sales amount with respect to ear. Still confused? Don't worry. Let's look into an example. Let's say in year 2002. In January, we had total sales of 5000.

In this way, in Jan, one sales amount was 1000. In Jan FY, sales amount is 3000. And in Jan 10, sales amount is 1000. That means together in Jan, we had a sales of 5000. In the same way, let's assume that in February, we had a sales of 2500. And in March also, we had a sold sales of 2500.

In remaining months, we didn't had any sales. That means in an entire year of 2000, we had a total sales of 5000 plus 5000 sorry, 2500 plus 2500. That means 10,000. Now when I asked to display month and contribution In that case, January will display contribution as 50% because from total 10 10,000 January add 5000 that means 50% February add 25% and march also had 25% April to December had zero percent. Now, let's say I asked to display date with contribution in that case, January one will This will display 20% because in January total sales is 5000 out of January one had a sales of 1000 rupees which is 20% 1000 is at 20% of 5000. When I say Jan phi, it is 30 60%.

When I say Jan 10, it's again 20%. For all other January dates, it will be zero percent. So, this is what we are expecting, according to what we want to display the value get changed and every time it is going to be a contribution with respect to the parent So let's see how we can achieve this. So let's get back to Visual Studio. And let's create a new calculated member and let's call it contribution. Again, let's keep parent hierarchy as it is, we will come to that and let's try to understand what will be the logic to get what we are expecting, it will be very simple it will be current amount, current month or current year whatever current time period amount divided by parent amount, parent sales amount.

And if we if we multiply it with hundred, then it will give us the percentage. This is going to be the final formula. Now, when we look at the formula, there is one problem from where we will get this parent sales amount because current amount is easy. We simply have to drag this amount this is going to be the current amount that's absolutely fine. But from where we will get parents is unknown. So the first problem is calculating parents face amount.

So what we will do this time is we'll simply go and delete this one. And we will create another calculated member, which we name it as parent sales amount. And now, let's go and put expression. This time, we will put a very different MDX expression called tuple expression. In tuple expression, we will put parentheses and we will pass two things into it. The second parameter will be what we want to display.

And first parameter will be what we want to display based on vote for example, right now we are expecting to get amount based on parent. So we have we will use parent function in MDX, for that go to functions and in navigation You will find plenty of functions. And right now we are interested with parents. So simply drag parent here. Now parent of the parent of current year month date hierarchy. So let's simply put it here.

And let's remove this parent keyword from here. Now, what is the meaning of this tuple expression? It means we want to display amount of the current members parent. For example, if you are using date, it will return month amount. If you are using month, then it will return year summer. So let's go and test this expression first.

For that, we have to deploy the cube and then simply go to browser tab, simply say reconnect. Expand measures we have parent sales amount and amount both simply drag amount. Simply drag parent sales amount. It's null right now Correct. Now simply go and put month what you see here is January 2008 and February 2008. And it will end up at December 2008.

You will see that January 2008 had a value of 1500. February had a value of 6000. And all other other months add a value of zero, it means total value of 2008 year is 7500, which you will see in parent sales amount column 7500 for every month it's correct. Similarly, if you get into 2009 in 2009, we had only one entry that is 6000. For me, that means for an entire month, IRS contribution is 6000. And that's what we see here.

Finally, for 2010 it's something like 9000. So we see 9000 year and 411 it's coming something different. So this is what We are expecting and we got it. Now what we will do is we will use this parent sales amount, calculated column and we will get the one we are expecting that is contribution back to the calculations and let's create a new calculated member and let's call it as contributions. contribution. And let's put expression as simply amount multiplied divided by parent sales amount.

Unfortunately, we can't see parent sales amount here. Because every time we deploy the view, we have to say reconnect. So let's say reconnect and currencies are more easier. Now. Let's simply put it here. We put a law wrong divided by symbol, it's this one.

And then we have to say, multiplied by 100. Now simply, say deploy the queue, deploy and processing It's done. Let's go to browser tab. Let's say reconnect. And let's drag amount column and contribution. member.

It says empty it says infinity, right? Don't worry about that. First let's go and drag month year and now is coming correct. You can see that January's contribution is 20% February's contribution is 80% means contribution is hundred, because there is only one entry in 2009 in 2010 February had some contribution March had some contribution. This is what we are expecting and everything is correct, except one thing. When we don't put this month column, we are getting this infinity keyword Why?

Now, it's very simple. When we don't put one keyword then there is no parent right? That parent was applicable for that time, year month date hierarchy if you don't put any, any of those the either neither month neither date Noria That means there is no parent. In that case, parent sales amount will be zero. And as per our formula, we are dividing it with parent sales amount. So, when we divide something with zero that means the outcome is in finite and that is why we are getting this error as infinite infinity.

So, the solution is we can put if case here, case statement here and we can solve the problem. So, let's get into it. So, to start with let's put a case statement case when now we are going to have some condition here. Let's get into that condition later. I will say some condition, I will say when some condition satisfies it means it's neither here nor month nor a date. So, in that case, we are expecting to get not at the applicable but in case it satisfied that means it's either your or month or date.

So in that case, We are expecting to get the same output. And so, we are done with our logic now it's time to put some condition. Now, this condition will let us know whether we are using any of the year month date hierarchy or we are using nothing. So, for that, we will use a special function in navigation section called the current member. Now, you will see that current member expects a hierarchy and it will return the current member of our hierarchy getting used for example, when we use year it will return here when we use month it will return month and when we use date it will return date and in order to check exactly what we are using, we will use another function from metadata section that is level which will return the current level of the member which will be followed with ordinal we will say dot ordinal which will return a number Which indicates that which indicates the actual position.

Now this will be one if we are using ear, it will be two if we are using month it will be three if we are using date, and it will be zero when we are using nothing, when we won't use either ear, neither month, neither date, this ordinal will return zero. Now let's go and deploy the cube and let's check the output say deploy the solution. It's deploying and done. Let's go to browser tab. Let's say reconnect once again. Now let's drag contributions simply.

And as you can see, it's displaying not applicable. Now let's drag month and let's make sure that it's working. As you can see, we are getting the one output we are expecting. Still, there is one issue and they show his parents his amount. Our end user is not Not interested in this parent sales amount. So we have to make it invisible from the end user.

So what we'll do is we'll simply come back to our calculation tab, click parents is amount and simply set visible to false. That means this is a kind of a calculated member, which is going to be used by only other members. It will never be used by end user for display purpose directly. So let's go and deploy the cube again, deploy and processing. Now let's go to browser tab again, simply say reconnect once again. And simply drag contribution and month, values are still coming.

And at the same time, you can't see parent sales amount any more here. So we got it what we want. So far, whatever calculator members we have created, they are with the parent hierarchy as measures, hence, they become measures. You see them As a part of measures, now, we will get into the next level, where we will go and create a calculated member, which will become a dimension member. For a scenario, let's go to the browser tab. Let's clear the grid.

And let's drag amount with ear. Now, as you can see, for every year, we are getting some value. Now end user is interested to see the total amount as well for 2008 and 2010. Together, that means he's expecting to get a fifth row, which will display the total value of eight and nine together. And that can be achieved by creating a new calculated member of type dimension member. So let's do that.

So we will start with creating a new calculated member and we will name it as 2008 hyphen 2010. But this time, we'll change the parent hierarchy to the customers And sorry, the time and here. Now we are selecting a dimension member instead of selecting measure because we are interested to create a new dimension member. Now, you may ask me a question why I'm selecting ear when I can go and select date month or any of other fields as well. Don't worry, you will understand that point soon. Right now just select year and say, Okay, now as soon as I select Change parent hierarchy to year from measures this parent member got enabled, simply click on change, and you can see the error that error retrieving the children.

This is because we have to press reconnect. Now click on change, you will see all simply click all say OK, now it's time for expression. Now let's try to understand what we want. We want a new member with the name as 2008 2010 and with the value as some of the value of 2008 and value of 2010. Now, that will be easy, we have to use this statistical section. And here we will find a function called as sum, which will expect a set and set means collection with more than one member, and each member will be specified inside this brace bracket as a comma separated value.

Right now, we are expecting to provide two values. So, that's why one comma is required, the first member will be will go to metadata. Simply I will expand the year and I'm interested in 2008 so let me drag 2008 and the second member I'm interested is 2007. So let me simply track 2010. So now, this 2008 2010 calculate member will display the sum of 2008 value and 2010 value. So let's go and test it for that.

Let this deploy the cube. It's deploying. Done. Let's go to browser, let's say reconnect. Now let's simply drag amount. And let's simply drag here, you can see that one more row is coming 2008 2010, which is displaying value as 16,500, which is a total of 9000 and 7500.

So this is how we can create a new dimension member. It's only purpose is making end users life easy to give him what you want. But do you really believe that we made end users life easy? I think we made it confused. Let me explain you with an example. Let's move to cue structure and let's create a new measure by saying right click and new measure And usage will be maximum value.

Source column will be amount, say okay, now we have one more measure here called maximum amount. I will simply say build and deploy solution. It's done. Let's go to browser tab. Let's simply say reconnect. And now what I will do is I will first drag amount and then I will drag maximum amount and then I will drag here.

Now, what you see in the maximum amount column is the maximum amount in that year 7500 is a total value in 2008. The sum of all the amounts in in that year, January plus February plus March, whereas maximum amount represents the maximum value in the entire year. In January some sales happened in January 20. Some sales happened in February 15. Some sales happen Out of that, what is the maximum value in that year is displayed here. So what you expect to be there in 2008 and 2010 in amount, you definitely expect the sum of 9000 and 7500.

But from an end user perspective, just think from an end user perspective, he will expect here the maximum value between 10 and eight, he don't expect some right but here we are getting some Can you see it here 5000 plus 3500 and we are getting 1500 a year, but we are expecting to get a maximum value here a maximum here maximum value combining eight and 10. And we are getting this kind of behavior, because when when we define the calculation, in our expression, we use some function. Now solution is instead of using some function, we will use a special function called as aggregate function. In statistical section, we'll get this function called aggregate. Simply drag this file And let me correct the syntax. Now what this aggregate function will do aggregate function will calculate the aggregated value based on the measure we are using.

For example, when we say amount it will do the sum, when we say maximum amount, it will use a max query. So, ultimately based on the measure it will calculate the final value. So, now let's say deploy the cube Let's wait it to deploy it. And it's done. Now in browser simply say reconnect. And now simply drag amount and maximum amount and then drag here.

Now you are getting the correct output 2008 2010. It's showing 5000 which is nothing but a maximum value between 2010 value and 2008 value. So this is how it will work. Now you may have a question in mind that what is the difference between calculation and measures simple word measures will be calculated and stored inside the cube when we deploy and process the cube, whereas calculations will be stored inside the cube just as the instructions their values will not be stored. Hence, they will not occupy a space and hence, performance will not be better in case of calculations, because every time and user try to access this individual calculated elements are calculated members at that time the expressions will be evaluated and the values will be displayed. So, in simple words, just ask yourself or ask your business a question, what is more important in this moment?

Is it space or is it performance if performance is a concern, definitely measure is a good choice. If you believe that size is a more concern here, then you should go with calculations. So, that's it about calculated members. We are going to have an independent video in series explaining MDX queries named sets and script command. If you have any questions with regards to calculated columns, you can always drop to drop a mail to correspond at rate questpond.com. Thank you very much.