DA-100 Microsoft Power BI – Level 6: Mapping Part 2

5. Creating hierarchies

Now, in this video, I want to talk to you about hierarchies. So we’ve had an example of hierarchies in the past when we were looking at dates. For instance, we have a date hierarchy, and this hierarchy goes from the widest to the narrowest. So it starts off a year and then go quarter month and day. And that is a computer generated hierarchy, also known as an employee explicit hierarchy. Now, we have used hierarchies already. For instance, when we had a look at the maps, Afghanistan, we have got a hierarchy here of country and then name, and we can go up and drill down the hierarchy as we wish. Now, you’ll notice that these hierarchies aren’t actually hierarchies over here. They don’t have the same indentation as what this data hierarchy does.

Why would you wish to create a hierarchy? Well, it’s largely as a guide to the end user. Somebody else is going to use your model. For instance, all of these fields, and you want to say actually what we have in our data, we have country, then we have state. Maybe we’re looking at a series of products. So when we go from the widest and an arrest, so we have categories, and then we have subcategories, and then we have the product, and then maybe we have the product for a particular color. So narrowing down each time. So it’s just a way to indicate what a particular hierarchy should be. For instance, if I went said, well, the hierarchy is state, and then it goes into country. Okay, that’s the wrong way round. It goes from country to state, so you can state the order of the hierarchy. So let’s do this for this Afghanistan model. So I’ll get rid of country and name two.

And in the Afghanistan source, I’m going to create a hierarchy, and I’m going to do this in two different ways just so you can see the different ways that it can be done. The first way is to drag from the more specific to the widest. So country will be the first, the top level of the hierarchy. If you had continent, that could be higher sports, and then maybe planet could be higher than that. But for the data, we’ve got country and then name.

So I’m going to drag name into country and release. And you can see we have a hierarchy created, which we can then rename. I’m going to just delete that and show you a second way of creating a hierarchy, and that is to right and click on your initial idea, which is country, and click on new hierarchy. So this creates a hierarchy with just one thing, and then when I want to add a second or third or fourth thing, I just drag it in to the hierarchy.

Now, it might be that it’s in the wrong order. If that’s the case, then you can always move up or move down the hierarchy. So it’s a good idea to name this as a particular hierarchy. So this could be like the location hierarchy. Now this can lead to a problem. You have got a country hierarchy here and a country hierarchy here. What do you do? Do you really want to have the end user have two different countries to choose from?

Most of the time you’ll probably say no, that will just confuse them. And so what you should do is hide all of those measures, all of those dimensions descriptors that aren’t part, that are now part of the hierarchy, that you don’t want to be part of separate items. So now I can drag the hierarchy into the location and it works just as we’ve done before. So previously we had to drag country and then the name in. Now I just need to drag it the hierarchy. So I can now drill in, expand all levels, drill out as before. Now there is one particular little thing you do have to bear in mind. Let’s say I go to this u. S. Country and you’ll notice that we’ve got country and states not currently using country, but if I were to drag that in, then it would function as a hierarchy. So we got country and state. But notice that in Afghanistan we’ve got these little globe symbols and we haven’t for the country in the state. Now you may remember that we got these little globe symbols by clicking on a particular item and then going to modeling in the older versions or column tools in the later versions of power bi data category and saying what sort of category it is, whether it is city, consonant, country, county of that sort.

And you can see that at the moment I could change the data categorization. What I’m going to do now is I’m going to create a hierarchy. So here is my hierarchy with country and state. It’s called country hierarchy. Obviously it’s not. It’s again a location. So I’m going to rename that as location hierarchy again. Again, probably a bad idea to have two hierarchies exactly the same name. So I’ll call that us location hierarchy. So what I’m going to do, I’m going to hide country. I’m going to hide state. And now I want to have these little globes. I want to do the categorization.

So I’ll click on country and I’ll go up to modeling or column tools in data category and I’ll click here and it’s not working. You cannot categorize items which are part of a hierarchy or in the hierarchy. So what I’ve got to do is we’ve got to unhide all. So that gets me the country in the state and from the country in the state there I can categorize country. And then you notice that country is now categorized in both locations and state I can categorize and it’s only then that you can hide it. An alternative to unhide all is view hidden and you can see the hidden ones there.

Now you can afterwards deselect view hidden. So I’ll hide this and hide this. So hierarchies don’t just have to be used for dates. They can be used for anything which goes from a big, wide, categorization like country which contains, in the case of the United States, 50 states down to the state, down to even closer, like for instance, a city or maybe a county. And it can go further down, postal code and so forth. But it can also be used for objects and anything else that you can have. Various Categorizations.

6. ArcGIS Maps for Power BI

Now there is one other type of mapping visualization and that is this one, the Arc GIS. GI standing for Geographic Information System. ArcGIS is a company which specializes in mapping. Now the visualization that comes as part of Power Bi is a free taster. It has some functionality but there is additional. There is a plus subscription which can be purchased at an individual level or for the entire organization. And there’s also Arc GIS online which allows for more secure organizational GIS data. But in this particular video we’re just going to look at the one that ships with Power Bi. So I click on it and we have got to abide by terms and privacy notice. Okay, so you’ll see it looks fairly similar to what we’ve had previously. So there is something different.

We do have a color well here. So what we’re going to do is do basically what we’ve done previously. I’m going to drop the state in and the computer’s thinking about it and you can see it’s worked out that’s the United States I could also put in the country new account. This one only allows single locations. Whereas previously, if you remembered, we were able to have multiple locations here. For instance, we got country and then the place within the country. So if I go back and we have State there, just click on it again, think of accidentally DeVito. So I’ll just recreate. So I’ve clicked on State in location and then the color could be the population for instance. So you can see things aren’t as instant as they were with the other maps. But there is a lot of hidden flexibility behind all of this. Now there is one problem with all of the additional functionality.

It’s where is it? It’s not in this part, it’s not over here. This just shows very basic formatting. If you want additional formatting functionality, you have to click on the more options and go to Edit. So this gives you the additional functionality. First of all, we’ve got things that we’ve seen previously like the base map. So we can have different types of canvases including Open Street View which is useful if you’re zooming in all streets, similar sort of thing. So I’m just going to stick to Open Street View and zoom out location type. So remember I said you could only put on one location. I can’t put a country well, I can say our locations are in name of country so our locations are all in the United States. So similarly I can add another Arc map and go to the England data and put in region name in location and sum of sales volume in color and size. Edit this and say all of these locations are in the United Kingdom and it would be great if I could do exactly the same thing with my Afghanistan data.

Unfortunately I can’t and I’ll show you why. If we add in the location there again, doesn’t really know where all of these places are and in fact, it says it’s failed to locate a few items. If I put in number of peoples as color and size and edit this put in location type, all of these are in one country, in that country is, and Afghanistan is not one of those countries. So that makes it a bit more difficult. So, going back to our American narrative, our American analysis, we can also change the map theme, so whether we want the color or not. And this is where it starts getting more interesting symbol type. So at the moment, it’s actually classifying all of these in what’s technically known as bins.

I sometimes call them buckets. So there are five different types of buckets with the same color. So that can be useful rather than this long discrete scale that we have got with other areas, other types of mapping. So if I can change the number of buckets, number of bins further down by saying I want them to be in six buckets and seven, and you can see the gradualization of it. And in fact, you can change which scheme, color scheme it’s using and notice it’s using schemes which again are good for colorblind people. So you won’t see any reds going to greens here. Now, I’m going to change this to not represent boundaries, but points for two reasons. First of all, notice what happens if I try and change it back. So we’ve got this spit at the bottom.

If I change it back to boundaries, where’s the OK button gone, it’s here and I can’t click onto it. So, yes, I’m using a very low resolution, but even so, the computer should be able to say click OK. It should have enough height on this. So if I say that these are points and if I go to analytics, the reference layer, I can put a layer underneath all of this. So, for instance, I could have the US average household income size. So we’ve got the amount of people based on the size of these circles and we can cross reference it with maybe the USA Diversity index. Now, one thing you’ll notice about all of these ten is that they are all US centric. So good luck trying to get this to work with your French data or Afghanistan data. However, Arc GIS does allow you to search for additional data.

So I’m hoping you can see the idea behind this. You could have lots of, say, different types of stores and with Pinpoint and then beneath it you can see a certain analysis, or maybe you have a number of stores and how much is sold in that store and be able to correlate that with income, for instance. So if I go back to my British page, my England page, I could add a reference and I’ll just type in the word England. And one thing that will come up, I believe will be the boundary. So here we have police force boundaries and so you can see where all of these various areas are in terms of police forces. If I go to my Afghanistan data and I can add a reference if I zoom into the Afghanistan area. So there we can see Afghanistan if I add in a reference layer. And again, I’ll just type in Afghanistan and you’ll see that you can add in districts so that underlays what’s there, or if you want the higher up version, the county boundaries, so that is higher than districts. Looking at the symbol style, you can see that we can change the circles to diamonds or squares as well. I can add a pin.

So suppose I wanted to put a pin in Kabul, the capital of Afghanistan. Well, there is my pin, I’m going to edit it so it’s a black pin and then zoom out and you can see where Kabul is compared to the rest of Afghanistan. Now, you might be saying, actually all of this is fine, but this is still way too much data for me to be able to take in. No a problem. If we go to map theme again, we have got, for instance, a heat map. We saw heat maps earlier, so we can see where the majority of the districts are. They congregate around at the capital, for instance. But I think going much further down, all the way to the bottom, we have clustering, and this is quite interesting, this shows the count of the number of items. So in this case, number of districts which are allegedly in or outside of Afghanistan. We’ve got six over here in Europe, for instance.

And if I zoom in, you can see that there are two looks to be in Great Britain and three around the Hungary area. But then let’s go across to Afghanistan where there’s 258 and I can zoom in and we can see 93 around the capital, Kabul, and then I can zoom in more and this can allow you to have a more summary view of the data for certain types of analysis. And I must admit, this won’t come up very often. You can also say drive time. So I want to have all the areas which are within 30 minutes and that’s the maximum drive time you can put in around Kabul, or you could say radius. So you can get to all of these areas in 30 minutes, if that is helpful. I say I don’t really think that’s very useful, but you can see the sort of expansions that you can do with all of this.

Now, the downside is there is a bit of latency. If I’m using one of the native files, you can see visualizations, it allows me to zoom in and out with much greater rapidity. It’s not trying to recalculate on the flyers. This we’ve got this pause for it to load up and then we’ve got further pauses when the zooming in and out isn’t as fluid. So personally, my advice is to use the default visualizations, the really built in ones of map and fold map, unless you have identified a need to use the arc GIS and then you can use the extra functionality that is included within this ArcGIS. And as I say, this is the base version. You can get a plus package that allows you to do a bit more at a cost.

7. Practice Activity Number 6 – The Solution

So how have you got on with this practice activity? In this section we’ve been looking at maps and this practice activity is just so that you can get some practical experience about creating maps. So first of all we need the Power Bi data set and from that we’re going to load the PA practice activity maps. Then we need to create a map with country, region, chord and then state, province chord. So we have this globe symbol, there’s our map. And so we need country, region chord and state province chord. So both those get added into the location. So you can see we have United States, Canada, France, Germany, Britain and Australia. Notice that the words aren’t actually used as in United States, it’s just US, CA and so forth. But Bing is able to correctly interpret these.

Now we’re going to add count of address ID into the size. So currently it says average of address ID. So if that’s all you’ve done, you probably would be thinking that we’re really high in Britain and in France and in Germany because of the size of it. But if you then change it to count, we can see that it is America which takes the lead in terms of the number of customers that we have. Next, we go to the next level of the hierarchy and notice what happens. We get a dot in Argentina. So why do you think that is? Well, that is because we have a state called SL and without the country name being given as a clue, it could legitimately be a state in Argentina. I think that’s a place called San Louis. So we drill back up instead and instead of going to the next level, we expand.

So the computer is able to use the country region country data as well as the state. So now we can see there are no dots in South America. It’s just like when we were looking at Afghanistan previously. The country helps us to get where we need to be now. Next, we need to zoom buttons at the zoom buttons, but to take the auto zoom off. And the reason for taking the auto zoom off, you probably wouldn’t normally do this, but when we go into any of these locations that have asked you to and you change level, then it will zoom right back out, which can be a bit jarring. Next, I asked you to add in the city and the postal cord into the location and to zoom into one of four places. So we’re going to have a look at each in turn. So first of all, we go to British Columbia, Canada, and you can see we’ve got a centralized dot in British Columbia. But when I now expand into the hierarchy, Fervor, you can see that we have got a lot more dots now in Vancouver.

And if I expand on that, you can see for instance, we have got no dots to the north of this inlet, the Barrad Inlet. But when I go further down to give us the postal code, you can see that one of our dots has moved into the North Vancouver section. So it depends how refined you need the data to be. It also depends what zoomed in you’re going to be. So let’s just go back out to Paris, France. We’ll go to London, England, first, so we have a dot in the center of the United Kingdom, and then it becomes a bit more spread. We just have one dot in London. London is this area enclosed by this big circle, the M 25. And as we zoom in, we can see how important London is. But we don’t know more precisely than London until we get the postal cords.

And now we can see we’ve got some in southwest 19, which is roughly where Wimbledon is, and we’ve got some in E 17. That’s roughly where King’s Cross Station is from Harry Potter platform nine and three quarters. So similarly, if we go into Paris, first of all, we have a dot in France zoom in. But again, Paris is this big city, largely within this large motorway, the A 86, I think that is. We also have closer in the Boulevard Peripitique, I think it’s pronounced. But we just have one dot for Paris. So zoom in and can see how important Paris is. It’s only when we really go to the Postal Court that we can get differentiation between all of the different arrangements onto the areas of Paris. And then finally, we’re going to Australia and we’re going to Victoria.

And you can see around the Melbourne area, we’ve got lots of dots, but in reality, it’s only when you get really closer that the dots start to move, for instance, into more specific locations. We’ve got a dot near Frankston, for instance, which gets more refined, and similarly, this other one, near Cranborn, gets more refined. The more details you can give it. Now, we can put in the category labels, so if I go to category labels over here in the format, we can see at this stage it could be useful, especially if we’ve got a lot of more countries in Africa or South America.

But when we start drilling in on the hierarchy, you can see instantly that it really overlaps the data. And if we kept going down and down, we don’t need this level of detail for these data labels, but we can’t really adjust what they show. If we were to zoom into a particular place, like, for instance, if we go back to Victoria in Australia and imagine that these were all stores like Melbourne in Victoria, Australia, this could be useful as a screenshot. But whether it’s useful or not, I’ll leave that up to you. So now we’re going to go right back out and we’re going to go to a country view, and we change this from a map to a field map. So we just have all of North America, united Kingdom, Northern Ireland, France, Germany and Australia. Vote. But it doesn’t tell us how important any of these are. So we need to change the color to give us a clue.

Now we’re going to change the color from a standard color to a conditional formatting color. And we’re going to use diversion colors. And it’s going to start from a blue. And I’m starting it fairly dark so we can see it to a dark yellow and ending up with a green. I’ve got no greens here in these 56 colors. So I’m going to go into custom color and click on a green. And now we can see quite highly highlighted how important the United States is. But secondly, Australia is our next biggest country. Now let’s zoom in. We expand in terms of the hierarchy. We expand all down to one level and notice what happens in America. We can see how important California is. We can also see how important Washington state and British Columbia is in Canada. We can see the United Kingdom. We’ve got these various areas shaded in as we have in France and Germany.

And we got the states in Australia. Again, we’ve got a very important state here for New South Wales. But then when we expand further going down to the city level, everything disappears. So why is that? It’s because the computer knows where the outline of say, Florida is. It doesn’t know where the outline of Orlando is. So it can’t help us going get there. Right next. We’re going to change it back to a map so you can see it works. Again though, our color scheme is now quite different. But that’s okay, we’re going to change it later. And next we’re going to use the data category to categorize the country, state, city and portal code. So I click on them over here in the field and I go to modeling data category and I say that this is a country or region.

Notice it’s country as opposed to county. Quite different. This is a city, this is a postal cord and this is a state or province. So now you can see all four of these have got globe symbols next to them. So it would be nice to put all of these in a hierarchy. So what we’re going to do is delete all of these individual locations and I’m going to create a hierarchy right at the beginning. We have country that’s right at the top. So I’m going to drag state on top of that. So here’s our hierarchy. So I’m going to call that location. And then we have city. So drag into location and postal code. Now we don’t need all of these individual ones. So I’m going to hide these fields that we don’t need. It helps tidy up the data. So we now have fewer fields. It looks much better. So we drag location out into our location. And there we go. So we can still get rid of any of these like we do with a date hierarchy, but I’m going to keep them all. Now we’re going to switch heat map on, and we’re going to expand to the next level, expand all down one level and then expand all down another.

So in the field map, we weren’t able to get down to the city level, whereas here we are able to do so. And finally, I want to change the colors because they’re okay colors, but they’re not great for me. So I think I’m going to use red. So we go into the heat map and we change them for various shades of red. Well, I hope you’ve enjoyed this map activity. Hope you’re really getting hang of where all of these hidden sort of areas areas are, all of these dots that are just hidden out of the way that you’ve got to hover over. And you’ve now got the difference between maps and field maps. And we’ve had a look at creating your own hierarchies. In the next section, we will be continuing level six and measuring performance by using KPIs gauges and more.