In this video, we will try to understand how to write multi dimension expression or MDX queries. In the past videos, you know, I have been talking about MDX queries and we saw some one or two examples of MDX queries, but this lecture is dedicated for MDX queries. Now, what are MDX queries? MDX queries are nothing but you know, they are queries which you fire on the cube. Remember, you fire SQL queries on relational tables, that is your simple tables in SQL Server. But on cubes in order to fetch data, you file MDX query.

So if you remember, we had the analysis database. So let me connect to the analysis database. And we have our relational database, right. So, when you want to fetch data from relational database, right, like simple tables, what you have here, you actually write Select star from table name you know you write the SQL query, but when you want to go and get data from a cube or SSIS cube, you need to go and write MDX query. So, MDX is nothing but it is a it is a query language for OLAP databases or for SSIS database. Now, the time I start demonstrating these MDX queries, one of the things you know, which will come to your mind is you will start comparing these MDX queries with a select queries of SQL because the syntax is looks very similar.

But I would like to make you aware, the way both of these queries work internally remember, MDX queries fires on multi dimension databases, or it fires on a cube it fires on a you know, on an OLAP database actually, and OLAP databases are normally multi dimension, right? So you have dimensions at the side and you have fact at the center. Why? If you look at RDBMS tables or databases, you really have just one table and it has rows and columns. So when you file MDX queries, you are actually fetching data from a cube. And when you fire SQL queries, you are fetching data from a table, which is not multi dimension.

So this is a very important point to remember, you know, while you're comparing both of these queries, So, go ahead, you know, compare them in your mind, but remember that MDX queries fetch data from a cube which is multi dimensional, while the SQL queries fetch data from a simple table, which has just columns and rows not for example, let us say I want to write a simple query on the customer data warehouse. So this is the same data warehouse on which we are doing the project. So let us go to the fact customer. Let us say I want to go and write a query you know where I want to get, you know the some of the customer count right so what do you do? You go here you say select Sum of customer amount right. So, this is the way you do it right.

In other in simple words putting you actually specify the column name you say this column name, and then it picks up that data from that column name. Now look at MDX query. So, in MDX you know, you can see that this is the cube inside the cube we have measures we have dimensions right. Remember cube is three multi dimension it is a multi dimension thing. So, if I now go and write a query So, let us say I say right click on this, and I want to write a query MDX query, right. So, you can see that this is how the window of MDX queries it is bit different from the SQL query, right.

So, over here at the top you can see is your cube, and then you have measures and dimensions and KPI. Now In a queue, we fetch the fact data, the data what we see on the grid is the only the fact data and nothing else, right. And the other things, you know, the columns are multi dimension. So here Now remember, this thing, what we are doing summation here, is already done and saved into the SSIS cube, so we don't have really write some, right. And by default, the column what will be selected is nothing but the fact. So by default, only facts will get selected.

So if I now go here, so I don't have to say select some or select average, because this customer amount here is a sum right? If you remember, you know, the project is not open now of SSIS cube, but if you remember this fact, here is a sum it does summation, right. So when I want to go and select data here, I will say select from just a cute name. So if I do this, and if Execute, you can see that it is giving me a total here right. So, this is the same total what I have in my table, the same total is displayed here remember, this cube is created from this database structure right. So, when you are writing query on a cube, remember you're only selecting facts for example, here I can do something like this I can say select sum and then I can again specify something called discount customer amount right.

So, basically I select columns here right but here I select you know, what has been readymade saved, that is the fact. And one more thing in SQL you know, what you do is you know, when you say, select customer amount, select customer name right when you write like this and you do execute. What it does is, you know, it does not multi dimension, right, it just takes those columns and take the rows, the appropriate rows for those columns, right. But now because this is a cube You know I cannot just say select and column name I have to say okay select the columns definitely when I say columns means the dimensions here there are no columns that are dimensions right? So select the dimensions and put it on the columns I'm on the rows so I have to say here for example I'll say select dimension country look at this okay select this dimension country.

Now I cannot really select just dimension country Remember, a dimension also has members inside it right? So I'll say select the country name. dimension the member of this is the dimension and this is the property right the member. So select this dimension on columns as multi dimension, right? Or you can say okay, select the salesperson name on rows, look at this, you know, so on columns on rows, and what gets by default selected factors. It is the intersection of the columns and rows in the cube, that gives out the fact.

Again, I repeat the statement, we are selecting when we write MDX queries, we are supposed to select from a cube. So if you want to select something from a cube, that means a fact of the cube, you have to define the intersection. And the intersection can only be defined by specifying columns and rows, while in SQL, it is a flat. It's a flat table, there is no dimensions here, right? So that's why I don't have to specify any kind of intersection here, I have to say, okay, on this column, and on this row have this dimension and that we can get me that, that intersection of that columns and rows and and take out the fact chunk, you know, the fact customer amount and display me. So if I go and exclude this, you can see that he's displaying me the data here, right.

One of the problems with this query is that it is actually just showing me all I would like to go and display the Entry names and the salesperson name right. So, for that what you do is you have to say dimension which you already said after that the attribute which you have already said and then the members inside it. So, you'll see our members and also like to go and display all the members members, it is plural excute. So, now you can go and see all the dimension values the salesperson name and as well as the dimension for the countries. Now, in this you can see that there are a lot of non empty data right. So probably you do not want to display this non empty data here right.

You want to only display data which is having values for example, you can see India and the IRS does not have data Nepal and IRS does not have data. So must be the IRS nor the SR right. So what you can do is for non empty you can go and do something like this you can say non empty and curly racket now this non empty is a function of MDX. So I'll say non empty members do not show it also do not show the non empty members of the salesperson name. So whenever there is non empty, don't show it execute. And then you can see that the non empty members have gone off.

Now remember that you can see see, you can still see a null here, but this knowledge scene because, you know, for kulluk we have a data in us, right. So that's why he's showing it. But you know, for India and Nepal, we don't have it. But the other completely you know, the elements or the dimensions for which we had no data at all have been removed. So for color here, you can see that the value is for us. That's what he's showing for India and Nepal as well right the nulls but the full non empty dimension have been removed.

So remember, non empty, to show to not show the dimensions individual Not have value. Now, we can also put a where clause here, but this WHERE clause here is very different from the where clause what you have been writing in SQL. So, for example, you know in SQL you will say where customer name you know is equal to something right. But here you can do like this you can say like, let us say I want to go and select by product name. So, I want to show all the columns the country name on the rows the salesperson name, but I cannot go and say like this product name is equal to twice this is not possible, right. So, here what you do you will say where product name is twice that means, you exactly specify the member value or the member the X the member What do you have, right.

So, if I exclude this, you can show you can see that is now showing me the value as per the Twice. So for the toys he's showing me you know, which are the members at the at the top that means the country name at the top and at the left is showing me the salesperson name. But now a very important word of caution here. You can see like this, you can't say that for example, you can see now you have selected the country name on the columns. And here you are seeing that okay, I want to select all the country names. So now you can go and see in the where clause over here that I want to only select India.

This statement here is contradicting this statement. So if you have such kind of a contradiction, what happens is you get an error like this. You can see that he's saying that the country name already appears in x is zero. And whenever you see this word x is zero. It means the column, this axis zero is the column and the axis one is the row. So here what you're trying to say is show me only the value of India, right?

And here you're actually trying to display all the the country names. So which is contradicting so remember that you know when you see like this right this kind of error it means that there is some contradiction. What do you what do you have selected and what you are filtering out. And also, if you exclude here one of the things probably you don't want to see is this all sometimes you want to just see the members but he's not want to see this all year. So, what you can do is you can say that, just show me, children, children excute. So, you can see now that all has gone away.

Now, many times you'd like to go and put a filter you're saying that show only those fact values which are greater than three times All right, so you want to just see 3840 and you want to just see 3700 right? But you can't go and see that were like this, you can't go and save our customer amount is greater than 3000 this is not possible. No, you will, this is not possible because, you know, when you put a filter, you know, you need to apply it either on the columns or either on the rows, you can't put it this way, because this is multi dimension. So, because this is multi dimension, you need to go and slice the cube. So, either you put it on the columns or either you put it on the rows, in other words, actually filter is a function like this. So, you can say a filter.

Let me remove the non empty for now. So, filter, let us say the children were, the second parameter is the range the criteria where customer amount will be created Than 3000 right. So, filter actually gets applied on columns and rows it does not get applied in the where clause in WHERE clause you to exactly put the member name right. So, let me go and check the syntax here we can see it will go and check the syntax You know, it does not give you the message saying successful it gives you parsing complete, okay. So I'll go and execute this. You can see now it is actually showing me the values which are greater than 3000.

Now, very interesting very, very interesting here. You can see here, this is right, no Shams values greater than 3000. perfectly right. But shifts value is greater than 600. This is not not done. Why this value is shown you know, because you know she has one value which is greater than 3000 for Nepal. So that's why he has selected this value as well.

Remember, at this moment, the filter is updated. lied on the person name, right? So it's a pride of the person name, but there is one value which is colliding with the country name. So in case you don't want to see this value and so, then you need to go and put filter also on the column on the columns. So, if you go to apply the filter on this, then you will exactly see the values which you intend right. But at this moment, there is a contradiction here.

You have to select the ship and Nepal, right. And you have to select India and charm. So that's why he selected Shiv and India remember it is a cube. You can't just take out one part and believe the other part it has to either come in one go or either go in all goes right. So that's why you know this 600 is selected. And let us say you want to go and sought this customer amount again.

For that we have the order function remember, in SQL, we write this order by, we write the where clause No after the from right, but in case of MDX order filter these are actually applied on the columns and the rows very important difference you know do not think about that from and then order by. So, all these functions order by WHERE clause you know is actually applied as a function you can see order is a function here. So, I will say order I want to order by customer amount. So, in order there are three inputs, first one is the member name, what do you want to display second one on which column you want to order on LC on which element you want to order I want to order on the amount and third one, you want it ascending or descending. Right So, if I go and exclude this, remember I have removed the the where clause right so, I've removed the filter actually.

Okay. So you can see it is ordered, but you can see that it is Let me go and do members here let us go and see all the data members that can go into those two members here right, let me remove this non empty for now. So, let us see what do you see here? I'm saying that order the data ascending, right all the data as sending on the customer amount, you can see that he's not actually doing it, I'm trying to order the data ascending on the customer amount, but you can see that it is not ascending You know, it is 100 and 3840. So, really it is not ascending you know, let us try descending, you see, the d e sc. Again, you can see that this descending is looking okay.

But when I look at ascending it is not really okay. You know, it is if you look at these values, it is not ascending right, it is going haywire. Right? So, what exactly is happening? Now, when we use the order clause in MDX The first thing what it does? Is it orders on the members of the column, so, it goes and orders on the country name, then it orders on the person name and then afterwards it considers this ORDER BY clause.

So, in other words, it does not go and order directly on the customer amount. So, that's why we are not able to see the proper data. In case you want to go and see the proper data, you need to break this hierarchy of the cube. In other words, you know, you you would like to tell MDX saying that ignore this sorting on columns, ignore the sorting on the rows, just sort on the customer amount. So, for that, we have to say break the hierarchy that means ba SC, right. So, if I execute this, you can see that now this is properly ascending, right.

So basically just a C and D sc. descending, what they do is they actually sort on the member and then the sort of the data right, so by using BSc and as well as we have BD sC right. So, this is what is sending the word D breaks the hierarchy and just concentrates on the actual order field What do you have given, so, at this moment this is customer amount. So, he is not going to consider now sorting on the person name is not going to consider sorting on the country name. So, when you use a C and D SC, it actually first sorts on this person name, then it sorts on the country name and finally, the amount right but when you said we break the hierarchy BD C or BSC, then it just goes and sorts on the actual customer amount what is specified in the order.

So, remember, order filter these are functions, these are functions and they need to be applied on the rows or they need to be applied on the columns. They are they do not come after the where clause. So, this is again one more big difference, which you need to consider when you are comparing MDX and SQL in SQL we write this ORDER BY clause this There are clauses after the from, but while here because this is multi dimension, these are functions. Now, one of the things you know, which probably is coming to your mind is that we are writing MDX queries, we are writing multi dimension queries, but still till now we have just selected to access in other words, we are selecting on columns, and we are selecting on rows. But when you say multi dimension, you know, it is very much possible that you would like to select five dimensions and six dimensions.

For example, you can see if you see our cube, our cube at this moment has so many dimensions. So, at this moment, I'm just self selecting on the rows and then just selecting on the common columns. You know, this is like a school isn't it? Just on the rows and on the columns. But if this is if MDX is really multi dimension, then I should be able to select The country name, then the person name and then product name and time and area and so on. So I should actually be able to select a lot of dimensions in one single query.

And yes, MDX allows you actually, MDX can select 125 access. So, it can select 125 dimensions. In other words over here, this one thing what you see on columns is nothing but it is access. The one thing which you see over here on rows is nothing but it is access. So, basically, you can select such in such a way, you can select 125 axis. So, basically, this columns here is nothing but it is actually axis zero you If you wish, you can go and write this as x zero, you can go and write this as access one.

And then you can go and select one more dimension here. Let me go and remove the order clause for now, so that you can see things in a more clear way. So let me remove this. So we can see now, on the first axis, this is nothing but the column, you know, this you can say x is zero or you can give a user friendly name, we'll call this column. So you can say x is zero, you can say x is one. And in the same way, I can go and also select product name.

So I can see that I want to go and select product description here. On access to dot members on access to right, so we can see that I've selected on three axes. And in this way, I can go and select till 125. When I say 125 minutes, because you start from zero, it will be 124. Now there are a couple of rules for the axis. The first thing is that you cannot skip axes in between means for example, over here, I said zero and then one Let us say if I see your three for example, so, if I go and check the syntax, it will give you an error saying that you know, the access numbers specified in those in the query must be sequentially specified and it can it cannot contain gap.

So, I cannot have you know 01 and three, so, the access should be continuous like 012. So, you can see now, when I did a check syntax for this is actually giving me saying that parsing the query and parsing complete remember that in MDX You know, it does not show like you know, check successfully, it shows you this kind of a message that passing the query is complete. Okay. So, that's the first rule that the access should be continuous. If there is some kind of a gap in between, you will get an exception. The second thing to remember about these accesses is that access from zero to four or the first way access have names So, they can either be called by numbers for example, you can call this backslash zero or you can also write your columns.

The second axis that is the second means, access one, zero is the first one it is zero index remember, access one you can get this from this rules access to yours there is a third access, you can call it by pages be just and then you can go and call by content. So, so you have taken sales personally and we have taken country name, let us take vendor name here. So I can go and put your vendor name dot members, all I can say section sections. And the last axis which is named is chapters. So, again you can see chapters here so rather than saying access of four, four means access of five actually the fifth axis you can see chapters. So you can see zero access one access to access three access four, right.

So you can either call them by number or you can either call them by name like columns rows pages and sections. And if you need further access for example, if you say okay I want to further use one more access you can go till 125 I said right, the fourth for further access we don't have names you have to then say access of. So this is 01234. You can say access of five the number zero index again I'm reminding it is zero index. So zero index means this five is actually six. Okay.

So yes, you know, because this is MDX you know, you have the full freedom to go and select as much as dimensions you want, and show the data and centrally What do you have centrally you have is your fact remember, I The end of the day in MDX facts gets elected. So, let us go and check the syntax here. And you can see that it gives an error which is a very very logical error it says that the area name has already appeared on access for that means you're and it is again appearing on access five over here, right? So we need to actually go and remove it right. So for that, so let's go and remove this area from here. And that is put your state's you know, states has not been used at the top.

So I'll just say states dot members, right. And I will just say check syntax and everything is fine. Great. So again, this is one more rule. Once you have selected a member, you cannot repeat it. So this is all fine.

If I do a check syntax. It says that passing the query, you know obtaining the compete ad MD that is the object model and all is fine. But when I try to go and execute this, it throws me exception. So you can see that it says that I'm able to obtain the complete cell site and object type, but I cannot display the result. Why is it so, at this moment, you know, the software in which you are trying to execute this query is the SQL Server Management Studio. And this SQL Server Management Studio was specifically meant to display SQL data.

And normally, SQL data is two dimension that is rows and columns not more than that. And now you are talking about displaying multi Dimension Data. So, what it is saying is that this SQL is fine. This MDX SQL is fine. But I will not use that word SQL this MDX query is fine. But, you know, I do not have the capability to display this multi Dimension Data.

So as a user interface Now this is the user interface by which we are displaying data, right. So he's saying as a user interface, I can only demand only display multi Dimension Data. In other words, if you just delete this, and if you say execute, I can do this, but the time you say that you will go and select five or six axes, in my UI has or does not have the capability to display such kind of multi Dimension Data. So, in other words, you know, now, we need a UI, which will help us to display this kind of multi Dimension Data, it cannot be displayed inside this query analyzer here. So one such graphical interface, which will help us to display such kind of a multi Dimension Data is nothing but your near and dear friend SSRS. That's what the whole point of SSRS is, that SSRS is meant specifically for bi and it should be able to display such kind of multi Dimension Data right.

But again, there are some limitations in SSRS Because again then SSRS was created previously it was created thinking SQL in mind, but let us see with whatever multi whatever limited capability, how it can explain this multi Dimension Data. So, let us go to our SSIS project and let us add a new report so, I have copied the SQL, let us add a new report. And in this report I'm going to go and display the SSIS data. So, I'm going to go and connect to Analysis Services. Let us get the SQL Server Name that is this, copy it. And just one next and let us copy this SQL or I'll say MDX.

I'll just keep on seeing SQL. Let us copy MDX over here slow Right okay. So, let us go and use this query builder. And let us try to specify him this complex MDX query. And let us see that what kind of capability does SSRS have. So you can see a one thing is we can go and create the query from here, but we will go to the design mode here.

And we will copy paste this query there. So, I'm gonna go and delete this default query and copy paste it here. And I will try to press OK. So, as I've said that, you know, there are limitations of SSRS you can see the first thing he says that the first access of the query, you know, cannot have dimensions it should have measures. So, basically again, you know, SSRS does put some restriction fine. So, the first access means columns zero, right.

So, here he's expecting measures, so I'm going to go and drag and drop this measure right away here. Right. And I'll see Okay, so this is a small restriction by SSRS, that the first column should be measures. And then afterwards, yes, you can have, you can have, you know, whatever, whatever number of dimensions you want, you can go and put 125 dimensions, not a problem. I'll do a next. Now, when you're displaying such kind of multi Dimension Data, definitely, the best way to look at it is by using this metrics report rather than the tabular report, right?

So I'm going to go and select the metrics report. And there it is. So you can see now, it at this moment, SSRS supports three axes. It supports a row, it supports a column, it supports a page, not more than that. So again, as I've said that it depends that how much level of access support is given by the tool and it is completely on the tool. So here I will say, Okay, I want to display the customer amount, right as the center But in the row I would like to go and display person name must be that does not select person name.

Let us select I want to select the let us select person name in the column, I would like to select the vendors name. And in the states I would like to select the page now you can see here it was well select the area I'll tell you why. Because area has data which is as per page. So, you can see now again one of the problem here is that you know again there are some dimensions left out and I do not have the capability to display this dimension. So, it completely depends on the tool you know, what kind of support is giving, so, let us go and do a next next and let us preview this report. So I'm going to say a finish.

So you can see over here now, you know the report is getting displayed in SSRS and it is a cubicle data. So you can see the first at the left hand side is the name so this is the is the first access. You can see the second access vendor right the the amount in between In the fact and you can see the third axis over here, Asia pages, can you see that area, non Asia. So you can see the third axis is now as a page name at the page level as a page header. Right. So remember, again, as I've said, again, I repeat, that display tool should have the capability to display that much access.

So yes, in in MDX, you can go and write as much as access as you want, you can have 125 axes. But at the end of the day, the display unit or the display software should have the capability to display this many accesses. Now tillow whatever MDX queries we have executed, we are only selecting one fact. And for example, here we are just selecting the customer amount factor. But now let us say I want to go and see two fact values in one goal. For example, I want to see that as for the salespeople.

All right, I will see both customer amount as well as the minimum value, you can see that we have two fact values here. So, by default you know whichever is the first fact value we just selected that I want to see the second fact almost be more facts, the dimensions. So, for that, what you can do is we can go and see your start off curly brackets and end of curly brackets and we can say, please select the customer amount as well as go and select the minimum value. So, if I execute this, you can see now I can see customer amount as well as minimum value. And I can see the dimension values here as well the salesperson name. Now, these facts here, what you see are pre calculated in other words, you know, they come from the cube, right?

But a lot of times we want to calculate inside the MDX we want to create a new field No, no Which is, you know, having some kind of a calculation which is done on the fly. So we want to create extra column here, for example, I want to go and create extra column here saying premium cost. So the premium cost actually, you know, we'll be taking the customer amount and adding hundred to it. So at this moment, I can go and do something like this, I can't just go and say, Okay, this plus hundred if I tried to do this, it actually throws me exception saying that you can't do this right. So I would like to go and add an extra column on runtime means the MDX will evaluate it on runtime and display the value. So, for that, what we can do is we can say your width member means create a new member and where do you want to create this member?

I want to create this member inside the measure. So I will say that this member is inside the measure and I will name it as premium the meal Okay, and how is this member calculated? This number is calculated by using this customer amount. And I'm going to go and add 100 to it. So, now that we have a new fact added here, dynamically call this premium, I can go and take this and I can now put it over here on the columns. So, this premium fact what you see here is calculated on runtime while the MDX gets executed and the calculation is this right.

So, if I go and execute this, we can see that one more field of field has been added premium, and it is calculated on the basis of customer amount. So, this is 9952, it is adding plus hundred to it. This is 3840 it is adding plus to it. So this premium here is a dynamic fact, which will get calculated and the MDX gets executed now, this is In a runtime measure which we have calculated is not so useful. Let me go and calculate some more practical runtime measure. For example, now let's say we want to go and calculate something like this.

So let us remove this premium. So I want to go and calculate you know the difference, the difference between the previous year's customer amount and this year's customer amount. So in other words, I want to do something like this. So rather than taking the sales person name on the members, let me take up the years. So let's go and do some more practical calculations here. So you can see over here in 2010, we have 602,011, we have on 400 hundred 700, and so on.

Let me take up just the children's so that You don't want to see the all so there it is. So now over here you can see that we have 600 and in 2010 we have four in 2011. But I want to go and just have one more value at saying that How did I perform as compared to the previous year? Means over here I want to go and create one more runtime measure. So I will say with member so then we'll put that the member name as many measure dot compare race or I'd say yeah, compare performance okay. So performance as compared to the previous year.

So your I would like to get you know, saying that okay, in two Thousand 10 it was nothing but in 2011, it was 600 minus 400. And then 400 minus hundred, right. So for that, what we need to do is we need to use something called as the current member and previous members. So now in order to go and get the difference from the previous value add the next value, we need to somehow get hold of the previous value means, for example, if I'm in 2011, and I want to say 600 minus 400, then I need to somehow get the previous value, right. So what we'll do is now in order to get the previous value, first we should get the previous year because if I don't get the previous year, then it's very difficult to get the previous value right. So let me do like this.

I'm going to go and first display if I'm able to see the previous year or not. So for that, to get the previous year, you will see your time dot year and current member and that current members I want the previous member. So, whatever is the current members member over at this moment the cube is browsing I want just the previous member of that current member I will see name. So let us first go and are we able to see at least the year properly Okay, let's check this excute There it is. So we can see now by using this previous member and current member, you can see that I am seeing calendar 2010, which is a current one previous we did not have any entry for the year. Afterwards we are in 2011 I'm able to see 2010 for 2012 I'm able to see the previous member.

So, by using this now, you know it is good that I'm able to get the previous member. But now what I want is I don't want the previous member, I want the previous members amount, right. So for that what we can do is we can say Okay, give me the previous members amount. So this Customer amounts I want the previous member. So we can see that I'm using the time and year. And I'm saying that I want the customer amount of that previous member right.

So, that is going to exclude let us see if we can see the value. And yes, we can. So, you can see for 2010 we did not have any entries, so nothing but for 2011 you know, you can see 600 is an entry of 2010. And seen over year 2011, you can see again the entry. So we can see this, in this compare performance, you know, I'm able to see the, the previous amount entry. Now what I need to do, here, we need to do the subtraction.

So we will say here, whatever is the current amount customer amount minus the previous one. So, you can see I'm selecting the customer amount, and then I'm selecting the minus of the previous one. So it is going to exclude this so that it is so you can see Customer amount first entry, comparing to the previous 106 hundred a year I'm seeing okay the next year I made 400. So I made minus 200 less. Next year I made 100 I made I made minus 300 less than the previous member. Next year I made 700 I made plus $600 in profit.

And last year I made 7452. Right? And then again for the 2015 nothing. So you can see that basically by using the current member and previous member, I'm able to browse back and forth in the cube. Now, let us go ahead and explore some more important functions like top some top count except union intersect and so on, you know, because I think that when you when you talk about joining data between two sets, you know, how do we do it? So let me write a very simple MDX query here, you know, I would like to go and display ads for the year.

So take the year On the columns, now, you should get acquainted with writing MDX query. Take the vendors. So I'm going to go and take all the vendors and let me just take all the members at this moment on rows, right rows from customers very, very simple MDX query here and it is displaying me the vendor name and it is displaying me the total sales done Let me also display this as member so that we can see the full report right here it is. Now, what I would like to do is I would like to go and find out that who is the vendor, you know, who has done the maximum sales across all the years, right. So for that, we can go ahead, we can see where it is clearly went to right. So I can go and apply top, some so I'll say top some So go and find the top some between the members of between the vendors but once we find those subs which are more than thousand, so anything less than thousand exclude those some in also in case if you want to just start from everything says zero, right but at this moment I'm saying that any value which is about thousand should be considered for the top some function and I will say that use this time in here in order to make the sum.

So, he has to use the time in year when he makes a sum he has to use this 2010 2011 and so on right and i will see on the columns go and display the total measure right. So, I would like to see measure and who is the top guy who has earned money right so excute and you can see it is vendor two, right. I can see that there are Let me see children, because I'm seeing all of that. All right, so that all is not good, right? So I've just said children so with the children I'm getting exactly when to because if you say all then actually it just takes the total sum of everyone right? I don't want a total sum of everyone, I wanted to find exactly which vendor made the maximum selling.

So, here it is vendor to good. So, that is a top sum function. So, top sum function actually takes first you know, which you want to sum by the dimension Second, the value of which you want to want to consider while you do the sum. So any value about this will be considered any value below this will not be considered and then on which you want to do the aggregation, like year or time or whatever. Now what I would like to do is, this report is good, but I would like to now go and say that, okay, so if he's a top vendor, right, who has made the maximum I would like to compare this with let us say vendor for. So I want to report now that I want to display the vendor data, as well as I want to display the total sum the top sum made by vendor for.

So I can see okay, here is a vendor to data. And here is a vendor for data. In other words over here, I need to do two selects one is, I've already done the first select to get the maximum, top some of the vendor which is vendor two. The second one is I need to do a select for getting the vendor for some, the top some of vendor four, and then combine both of them. And this is where we need union. What union does, it actually takes two sets of data right and combines them.

This union You can also compare with your union and union all in SQL. In case you're new to Union and union all you can go and see our videos in SQL Server section, where we have explained this SQL in more detail. So union means take two sets of data and combine them so I have I already have the top some. So yeah, we say union, right. So you can see set one set two. So I also cannot take the second set, I want to give you control V. But in this set, you know, I do not want all the vendors I'm looking at vendor name, which is when the fourth so I can drag and drop went a full year.

And I'm not worried about you know, thousand, I just want to start from zero, right. So just take any value, which is there for vendor for and exclude this. So that it is you can see now it says that vendor to a six to four, zero and vendor four is 3712. So basically union has taken both of those members and combine them. But now remember one point, you know, when you do a union, the data types, the number of rows, everything should match. So you can't Go and take your like you can see be union with vendor and union with salesperson name this is not allowed because you cannot select multiple members on multiple dimensions on the same axis okay.

So, if you try to exclude this it will say that it should be should use the same hierarchies you know it cannot have a different hierarchy. So, basically, you know the data types, the field names you know everything should be same when it comes to Union. So, Union helps you to combine two sets of data and show them as one. So, this is nice, but now I want something, some kind of a different report I want to show the vendor for right but I want to compare when the force top sum with the sum of all the people means all the vendors. So, for that again, what you will do is I will go and copy this paste it here Ctrl V so that we can compare them so I'll say okay go and make some of all the vendors you can see I've removed members have just said all vendors when you remove the dot members it does not dot dot children and not members he does not actually take the members into account you just show me the total of all right.

So if I exclude this you can see that I will get here the following so you can see I'm getting here 6240 plus 3712. So you can see he says okay, this is total nine and five two, and from that you have 3712 is vendor four. So you can see the in the second Report, I'm getting the total and the vendor four, right? Good. But I want to go one step ahead. In this all It also includes vendor for data.

Right in this all It also includes went for data. So how about Can I do like this? I also could display me all the total. Except, except for vendor for, right? So there is that's where we have the except function. So what we can do here we can say that okay union, right?

So I'll say okay, take all the total, but except look at this Ctrl C Ctrl V. Right? So I'll say your, but take the sum, right? Except, so, take the total sum, but except this guy, except the vendor for you know, take all of them. So you can see over here this is the Accept function. So it is it says that I will take all the data but except for the stop song. So except takes two things.

One is the total sales of the vendor and second one is you know which one exclude right? So let's do a check syntax here first right parsing complete very good. I'll say execute. There it is, we can see now let us go and see. So, there it is. Yeah, that is right.

Is it right or something went wrong, except except for when the four right. Yeah, so that looks right data right. So except means it is excluding union means joining. And then we have one more thing here called as intersect. intersect means basically the wherever the intersection happens, right? If the members are whichever are equal to it's like an inner join into segments like the inner join, so it's like an inner join saying that okay.

In both records matches, then only I will send the data out. So remember, when you Talking about combining two sets of numbers you have union you have accept and you have intersect in Union you just combine those sets in intersect only the matching records are combined while in except, you know, something, something is excluded. So, that brings us to the end of this one hour MDX session. I hope that, you know, in this one hour I covered, you know, some of the very important syntaxes I hope that, you know, I have communicated to you basically, you know, what is the mindset, you know, that goes into writing MDX queries. So, after this video, I'm going to go and flash two slides one is, which compares MDX with SQL right. So, whatever points have discussed, will will will see this summary slide.

And the second one is you know, some important skills what we have covered here I will show a summary sheet summary sheet. So, two summary sheets will come in one which we saw the difference between MDX was a school and second one important points and finally assignments. Thank you very Much and keep MD axing. Okay. Thank you