In this video, we will try to understand what exactly are dimension hierarchies. Now, if you remember, in our customer cube, we had the ability to go and see the sales amount as for the salesperson name, so you can see here, I've dragged and dropped the customer amount and from the dimension I've dragged and dropped the salesperson and I was able to see that you know which sales person has made, how much six. Now, let us assume that there is a full blown marketing department with a proper hierarchy. In other words, you know, there is a sales head and the sales head has sales person below. Now, you'd like to get a dimension report in a hierarchical manner. So we would like to display at the top that we have a sales head and then down below, you'd like to see the respect to sales.

Person with the sales the sales head to display the total of the sales done by the salesperson below. So, in simple words I want to report something like this. So, we can have let us say let us say we have the main boss right and then we can have sales had one right we can have sales had two right. So, inside sales we had one we have let us say salesperson one okay and we can have salesperson too Right. So, we can have salesperson too. In the same way the sales had to also has some other salesperson.

So it he has let us see since person three and since person four, right so in other words There is a hierarchical structure in the company. So, now let us say the salesperson was one does sales of 90 he does a sales of hundred he does a sales of 20 he does a sales of hundred, what we would like to do is we would like to display the individual values of every salesperson what sales they have made, and second, the sales head one will have the grand total of both the salesperson So, since head two will have a grand total of you know, both these, you know, the both the salesperson three and salesperson four and the main boss will have the grand total of 190 plus 120 that is a grand total of everyone, right. So in other words, you know, we have a hierarchical dimension internally viewed like to actually exploit this hierarchical dimension and get a report into our cube accordingly.

So, in other words, how do we go and define this hierarchical data mentioned inside our cube and how do we get a report like this you know based on the fact now, at the end of the day the cube is created from the RDBMS. So, the first thing is that our RDBMS table should define that hierarchical relationship. In other words, our salesperson should have that structure of hierarchy. So, if you see at this moment, our salesperson is a plane sales table, it does not define any kind of hierarchy. So, the first thing is that this table over here, you know, should define a hierarchy. So, in order to accommodate the d3 hierarchy, let us go and change the design of the salesperson table.

So, let us go and open the dim salesperson in a design mode. So, over here in order to have such kind of a hierarchy, where we have a main person and then it has some subordinate and the subordinate further subordinates below, we can use the self reference primary key design means what I can do here is, so this is the sales person. And I will create one more key over here saying this is a self sales person, Boss ID. And the way we will go and fill the data is as follows. So, this field here will have a reference of the ID of the box. In other words, let me go and reopen the salesperson now.

So, view Object Explorer So, let us go and edit the salesperson again. So, in other words, the data would be as follows now, let us assume we have a boss here So, I'm going to go and make boss entries. This is let us say jignesh right he is the boss. And listen there's one more boss here saying this is the Raj is one more boss right? So let us say Shiv and Raj you belong to jignesh so I'll put your five and I'll put your five and let us see the rest of the people that is correct Sham belongs to the village so it is six and six right and both of them do not have any top boss at this moment. So I've kept the value as null.

Okay, so you can see now I can go and define any directory hierarchy over here. Okay, so at this moment what I've done internally jignesh and themes of the boss and in you know below jignesh shivan Raja work and below dirige Cora can Sham works. So now let us go and see that how we can go And you know, put the data in such a way that we can see this kind of hierarchical report. Now, this data over here, what we have created has to be provided to the cube. And to the cube, we provide data by using the data source view. If you remember, in our SSIS project, you know, we had data source we had QPR dimensions, we have lots of these folders, right?

The data source view actually provides data to the cube. So in this you can see that at this moment, if you look at the dim salesperson, right, so here, here is a dim salesperson, this dim salesperson table is only providing the salesperson ID and the name we also want two more fields here, Boss ID and boss name, right. So in other words, you know this cannot be the direct table, we need to actually use SQL for it. So in other words, we need to First go and write SQL, where we would get the salesperson ID where we would get a salesperson name. And where we should also get the salesperson boss ID and the salesperson boss name. So, in order to achieve the same, we need to go and write a query which uses a right join.

And in this query, we need to go and self reference this table, right. So in case you are new to SQL, and you do not have an understanding of left join, right join and inner join, I would suggest you to go and see the SQL Server videos you know where we have explained, inner join, left join and right join. So in this video, I will not be talking about it. So in case you're new to SQL, please go and see that. So over here, I'm going to go and do a left join. And I'm going to go and self reference the table, right.

So rather than going and writing the SQL with your hand, you know what you can do is you can use this diagram Over here. So I'm going to go and click on this diagram pane. And I'm going to go and add the table. So remember, it is a self reference. So I'm going to go and add the DIMM salesperson again over here. And let me delete this.

So the join is between the salesperson boss ID and the salesperson ID of the same table. Right. So from here I will get the salesperson boss ID. And from here I will get the salesperson name. And remember, this is a left join left join means I want to get all the records from the salesperson, right so I'm gonna go and say select all the rows from Tim sales person. So if you go and see the SQL It is something like this.

So let me go and copy this SQL here Ctrl C and let me just open it in a new window. So let me paste it here. Ctrl V and let us watch you know what SQL Boo created so we don't need to stop we need to get all the records right so the top just comes from the designer right so you can see I want to get the salesperson ID yes I want to get the salesperson name yes I want to get the salesperson boss ID yes and this last one has come because of the left joins right so this will be named as sales person boss name right? And it is a left join with the same table and the join is on this okay so it's on the person boss ID with the other salesperson ID right. So if you go and finally see the output of this SQL It is something like this and which is what we want.

So you can see here it says okay, Shiv is a salesperson his boss is jignesh reduce boss's jignesh kolak Shan belongs to the Raj and jignesh and direct our bosses. So they don't have any one at the top. So it is null. Okay, so that's why I have made a left join. So this query will now become an input to our cube. So I'm going to go and copy this Ctrl A Ctrl C. And let us go back to our cube.

So over here, now, this dim salesperson cannot be a table, it has to be a query, right? So I'm gonna go and delete this table from here. Yes, a lot of big changes now to the cube. And here, I will add a new named query, right? So not a table, a named query. Okay.

So a new named query. So I named this as dim sales person. And I'm going to go and paste the query down below, Control V. There, let's just go and execute this query to ensure that we are seeing the proper records. So I'm gonna go and execute it and you You can see that I do see my records properly. Yes. So I'll just say okay.

Right. So you can see now, this is a query, can you see this icon over here? This signifies that this is a query and this is a table, right? So yes, it is a query, it is nice. And also what we need to do is we need to make this as a primary key because it is coming from a query you can see that he has not made it a primary key, and this will be referenced over here. Right?

So it's opposite arrow, I'm sorry. So like, this is great. And then received this. Good. So we have got all the necessary data into our cube now we have the salesperson ID, we have the salesperson name, and we have the respect to salespersons boss ID and the boss name. So now we have enough fields over here.

To go and create a hierarchical dimension. Now, because we had deleted the dim salesperson, right from the dimensions, or I'll say from the data source view here, what has happened is you know, our dim salesperson dimension what we had previously has completely become invalid. So, you can see over here there are lots of red signs, you know, indicating that the stem salesperson dimension has to be again recreated. So, what we will do is, let us go here and let us create this dimension manually. Now, remember, you know, if you have any kind of modification, add the dimension level, then try to do the changes over here. If you have any modification at the fact level, then try to do it over here.

So, do not try to delete all the cube and recreate again that's a very bad idea. So, remember that, you know, when you have the changes when they apply those changes to the cube, you know, rather than going and recreating the cube from the scratch, okay. So I'm going to go and just delete this dim sales person over here. So I would go and say delete. And I'll be recreating this dim sales person, again from scratch. So only the dimension.

Okay, so I'll say add a new dimension. And the result starts. And I will say, use an existing table. Yes. So we don't have a table actually, you know, this is actually a query, right? So I'm gonna go and take this and say next.

And the salesperson ID is that is the attribute name. And we'll just finish. So you can see that things are created over here. Now we want all the fields. So I'm going to go and take all these three fields and move it to the to the team salesperson thing, right. And also this dimension has to be linked to this cube now, right?

This is not the cube it is the DSV. So let us open the cube. So I'm going to go and click on the cube right. So on this cube because we have deleted the dim salesperson dimension you can see it is not available. So, let us go and right click and say add q add dimension and let us add the dim salesperson, right. And also this dimension has to be now linked with the fact remember that the time you say that you're going to go and manually create the dimension it is a two step process first is you will create the dimension second is you will add the dimension over here and link it with the fact table.

Right? And to link it with the fact we have to use the dimension usage remember, dimension usage links, the fact and the dimension. You can see that's good actually it has automatically linked so let me just quickly check if this is right. Okay. So it is good that he has done it automatically. Because the field names are same in dimension table we have that salesperson ID right and also in the measure table we had it.

So, that's why it has linked it, but in case if this linkage is not available then it is your duty to go and do the linkages right. So, this is good everything is happening. But now, where is the hierarchy we need to go and define the hierarchy right and the hierarchy is defined here you can see this hierarchies right. So, over here now, we need to go and define the dimension hierarchy because at this moment what is happening is the cube is getting all the data from the DIMM salesperson dimension, but he has no idea how these attributes are linked with each other, okay. So, he has no idea that who is the parent in this and who is the child. So in order to create a hierarchy, we need to go and drag and drop the attributes over here.

So the at the top the hierarchy Is the salesperson salesperson boss name, right? And below the salesperson boss name, you have the salesperson name, okay? And the salesperson boss name is linked. So we also need to go and define with which ID it is linked. So we're here I'll say the source attribute is the boss ID. And over here the attribute is the salesperson ID.

Okay, good. So I want to save this. And also let us go and quickly check you know if this hierarchy is proper or not. So go to attribute relationship. And over here, he gives you a very nice visual view saying that Okay, so here is a salesperson boss ID and his total will come from the salesperson ID. Remember, we create hierarchy because not only we want to see a visual structure, like a parent and a child, but also if there are any calculative fields like the sales amount We want to total it up.

So, when I say total it up means basically quickly. So, if you have let us say this is a sales Boss Boss right and he has salesperson one. So, since person one inside him and sales person what happened okay salesperson two. So total total it up means you know I want to see this visual hierarchy definitely also you know if this is 10 and this is 20 I want to see that the sales POS total also comes 30 So, even the fields calculation should total up So, we need two things from this dimension hierarchy one is yes we want to see this kind of a visual structure and also we want to ensure that these calculations are roll up properly right. So, over here you know this this says that okay the sales bus sales person boss is actually Actually the parent of salesperson and any kind of calculations that happened down below will total up here right?

Good. So, let us go and process this cube. So I'm going to go now to the cube here and let us process the cube Remember to process the cube, we need to click on this thing here, process cube right. So, let us click on this let us say yes, lots of things have changed and I want to go and process the cube. So it starts up that small Process window so that you can see the processing is happening. So let us wait for some time and then we will go to the browser and see the view.

There it is. Yes. So you can see the processes is in progress, and I hope that everything is green and everything becomes green. And we also see the the hierarchy right. And you can see the process has succeeded very nice. Now let us go back to our browser.

Remember that whenever you do a process, you know you need to go and reload the browser, it is very important to reload the browser. So in order to reload, remember that we have this reconnect button over here. So if you click on this reconnect button, any changes that you made to the cube will come inside the browser. So now let us go and start seeing that if we can see the hierarchical dimension in action. So you can see that I have put the customer amount. So let us go to the salesperson dimension and you can see the first thing what you will see in the dim salesperson is that there is a hierarchy icon over here.

This hierarchy icon clearly says that you know that this is now hierarchically structured. Okay. So first let us go and see the individual salesperson amount. So, you can see that it is showing me kuduk it is showing me sharm it is showing me shift, but you can see that he is not showing the boss names here. Okay. Now let us go and drag and drop the boss names and now you can see it is telling me Okay, correct belongs to the rods, Sham works under the Raj, she works under jignesh and it also shows the respective customer amount here if I go and delete the salesperson so let us say I go and delete the salesperson you can see that he's also showing me the individual totals of the sales boss right.

So you can see for example over here if you see correct and Sham are working under the bridge, so the total should be two to four zero plus 800 right. So when I go and delete this sales person name, you can see 2240 plus 800 is this much and that is right. Okay. So you can see the individual salesperson amount as well as the totaling up is also happening. Ok. And you can also see that there is a unknown entry here, very strange. Let us go to SQL to check that why is this thing unknown?

And you can see that there is one entry here of a salesperson which is null. Right? So that's why you know, it is showing you know, showing an unknown entry or that right. So that is right, you know, if the salesperson is not known, so you have to show it unknown, right, good. Now remember that this browser tab over here is for developers it is for us. So that's why you know, he does not really show a tree view it only shows a grid right.

But if you try to go and open this cube in Excel right, if you try to go and see it in Excel, so let me go to my Excel here. And let us see that how this thing looks so if our end user sees it, through Excel or some other you Right, you know, which really shows a tree view, you will really get a good idea of how the hierarchical dimensions will look like. Right. So let me open my Excel and let us go and connect to this cube. So there are Excel has opened. And if you remember, in one of the prior videos I had shown you know how to connect to analysis services using Excel, right.

So, we need to click on this from other sources, and we need to give the server name, the server name, you can get it from here, click on File, and copy. So this is the server name for our DBMS. We want the server name for analysis services. Now remember that over here, the database engine, the analysis services, and the reporting services are on the same server, right? But it is very much possible that you know when you are working in real time scenarios, these databases can be different. So remember, always click on the drop down and then take the respective server or server name.

So at this moment, I want the Analysis Services server name. So I've taken that Ctrl V Yes I want to use the Windows Authentication next and yes that is the cube and finish and what he does internally he uses the pivot table to to display the view of the cube because a cube a cube view can be seen better by a pivot right. So you can see now I can see fact customer I'm going to go and load it sorry customer amount. So, let me load that and now let me take up the salesperson let us see how it looks over here. So, I will go to my hierarchy here and let us select first the salesperson name. So individual values, let us take the sales person boss name and now you can see okay, let us do like this.

This is this and this light by now that is good. Because I selected for the salesperson name so he was showing it at the top review right now. You can see now this makes a lot of sense, you can see how he's showing the hierarchy over here. Because Excel has the capability of showing hierarchical grades, you know, I'm able to see it over here, you know, but this browser view here is for developers right for us. So this will not be seen by end users. So over here, you know, we do not have those flashy grids.

So you can see now over here, because of the hierarchical dimensions, you know, we can see the, the tree view kind of a view here. Right. So that brings us to the end of 30 minutes of the video, and the next video is on SSIS. And I'm very glad to introduce one more train or into this video series, Mr. sukesh. Marla sir, who is a very experienced person in msbi, as well as he is a trainer, and as well as a Microsoft MVP. So the next video is going to be Mr. Mukesh Kumar lessor and the video will talk about two components of SSIS.

One is the web service task and the other one is the XML task. So the next video by Mr. Su cash Marla sir, where he will show demonstrate that how to consume data from web services and how to read that data of the output of the web service. xml by using XML task.