Join Ron Davis for an in-depth discussion in this video Hierarchies, part of Microsoft Business Intelligence Stack in Depth.
- View Offline
All right. Let's look at adding context to our queries by the use of hierarchies. I've cleared out the query window and let's take a look before we add a hierarchy. Add to query, sales amount, and there's my number. If I drop down here, sales territory. Sales Territory Key > Add to Query. So here's the Sales Territories Keys and that makes almost no sense, unless you happen to know what those sales territory keys are. So we're going to go in and we're going to modify this dimension.
And while we're modifying it, we'll add a hierarchy to that dimension. Dim Sales Territory, I'll double-click it to open the Designer. Over here is the underlying table that the dimension uses. So far, all we have is the key. You saw that one through ten, over in the browser. So let's add Region, Country and group. And, real quick, let's look at the, explore the data.
So, what I've added was the region, the country, and the group. And, you see, these labels are going to help us better identify what it is we're looking at. So now I have those. I'm going to save. And we're going to create a hierarchy. So I'm going to take the group, drag that over and put it at the top of the hierarchy. And underneath that, I'm going to take country. And under country, I'm going to take region. And we'll rename this territory. Now, let's look at the attribute relationships.
And, the wizard was able to discover my keys, and it came on over, and it put it into the correct relationship. Let's break one and show it to you. I'll delete that out. I'm going to take Sales Territory with a new attribute relationship, and I'm going to take the territory key and I'm going to relate that into region. Now I'm going to take region and I'm going to relate that into country. Okay? And I'm going to take country and I'm going to relate that into group. And that red should go away. It does.
So now I have the correct relationship between these different attributes. Back over in my structure. Here's my hierarchy. I'll save all. I'm going to process the cube which will process the dimension also. It deployed successfully. This is the real test. Will it process? That is, can we grab the data and understand the relationships and put it in to analysis services? So I'll call run. Succeeded. And this is trace information.
The trace information really doesn't help me much when it succeeded. But if it failed, it helps me tremendously. because I can come down and see everything that was happening and read all the queries etc. So I'm going to close, close, see if it worked. So let's go back over here into the cube and in the cube I'm going to reconnect and I'm also going to refresh, I just tend to do that. So measures. Back to internet sales. Let's again add sales amount. And we expand that out.
Move on down. Sales territory, when I expand that out, here's this hierarchy territory. So let's right click that. Add it to query. And we've got it. So now I see Europe. And within Europe, I've got France, Germany and United Kingdom. Sales Territory, region. And then the sales amount for each region. There is your hierarchy.
- Working with data sources and data views in SQL
- Understanding the star and snowflake database schemas
- Working with the SQL 2012 semantic model (aka tabular mode)
- Using Data Analysis Expressions (DAX)
- Using Excel Web Parts to connect to spreadsheets
- Building dashboards with key performance indicators (KPIs) and reports
- Understanding the Microsoft data-mining process