PL-300 – Section 20: Part 2 Level 8 – Get Other Types of Data Part 2

160. Expanding Multiple Tables in SQL Server

What I’m going to do is say actually, what I want is the name. I want the category, the subcategory and the category.

So, let’s move across and say I want the category and subcategory.

Remember, we’ve only imported one table. Subcategory is in another table. There was a reference to subcategory. So, let’s see if we can find it. There we are, products subcategory ID. So, there’s lots of nulls. So, I’m just going to take out the nulls so we can actually see some data. However, 14, 21, 31 doesn’t actually tell us what the actual subcategory is. Let’s move along. And you can see that we have got links to related tables. You remember when we looked at the source, one of the Advanced Options was include relationship columns, so the computer knows that one particular table is connected to another table using primary keys or phone keys, it’s basically set up well. So, what we’re going to do is go back to our data and I’m going to scroll across all the way to Production. Product subcategory.

Now, you can see that each of these particular cells contain multiple items, including a table within a table, essentially a table within a list. So, what I’m going to do is expand Production.Product category, subcategory, and have all of these items. I’ll decide later what I actually want to keep. So, let’s click OK. And so we can now see that subcategory 14 is road frames 31 is helmets, 21 is jerseys, and so on.

Now, I also want the category which is related to the subcategory, but it’s a different table. And again, we have a link to it. We also have a link back to product, because the subcategory stuff that we’ve just got is related to the product, but also the product is related to the subcategory. So, it’s a bit of a loop if we go down that route, but what I want to go is go further down the chain and get the product category. So, I’ll expand that as well. So, now we can see that product category two is components, four accessories, and three clothing.
Now, we currently got 53 columns, an awful lot. We don’t want that many columns. And if we have a look at the native query, it’s fairly complex. It’s telling us all the columns that we’ve got. So, what we’re going to do is say, well, I want the name, I want the list price. So, I’m holding down Ctrl to select these individually. I’m scrolling across. I want the subcategory name and I want the category name. So right and click and Remove Other Columns. So, if we now have a look at the native query, you might imagine it’s going to be a lot simpler. Actually, no, it’s building and building and building. But in computing terms, this is actually a lot simpler. Yes, we have got references to all of these columns that we don’t want. But SQL Server is intelligent enough to go. Actually, these aren’t part of the final output, so I’m not going to bother processing them. So, you can let SQL Server take the strain of deciding what’s important.

Now, notice we’ve got Production.ProductSubcategory.name, and Production.ProductsSubcategory. Production.ProductCategory.Name, very long. So, whenever you’re dealing with nested tables, it’s probably good towards the end to just rename what you’ve got. So, let’s make a visualisation based on this. We will Close and Apply. I’ll make a graph, a stacked column graph. And within that, we will drag the list price to the value, the category to the axis, and the subcategory also to the axis. So, we can drill down.

So, we have got data from SQL Server. And we can see that we can manipulate it as any other sort of data. However, the simpler the data, the better. So, the fewer columns you need the better because the computer is then not going to have to retrieve all of that data. So, the process that the computer uses is called Query Folding. I’m just going to remove this filtered roles because I don’t actually need it now. So, I know the query could break it’s not actually going to, and here is our final, it looks hideous, SQL query. It could be re-formatted quite easily. But this is actually fine in computing terms, you can make it a lot more elegant, but the end result will just operate just as quick.

Now, I said I would let you see the difference between an SQL Server while we are enabling full hierarchy. So, just to show you that, as I say, I don’t recommend that you do this. We then go through the various schemas like person, like production, and then we get into the various tables. I personally find it a lot easier to just go for it, in order of whatever order it gives. It seemed to give a bit of a random order where we looked at it, but this is an alternate way of looking at things.

But as I said before, just select one table, and as long as you have got include relationship columns, you can use up one table to draw down to other tables as appropriate. And you can import the data which is the default. Or you can use direct query which will always ensure you have the most recent data. But of course it takes longer to actually get the most recent data. It will be refreshing quite often.

161. Importing Data from SQL Analysis Services (SSAS)

Now, in this video we’re going to be looking at importing into Power BI from SSAS, which is SQL Server Analysis Services.

The difference between SSAS and SSMS is that for a standard relational database, it is more geared to getting data in. Getting data out is a bit slower. With SSMS a cube, which is the technical name, is created of pre-aggregated data. So, what happens is, you identify when creating the cube, these are the important things and let’s add up things that relate to it. So, aggregation are your counts, your sums, your averages and so forth. So, it is the designer of the cube that restricts the huge amount of data that’s available within the original database and says, this is a set of data which is much quicker to query, which has all of the things that you really need. So, what I’ve done, in Visual Studio 2019, I’ve just created a quick cube based on the Adventure Works.

So, there are two sets of things, dimensions and measures. Measures are things that you can literally measure. So, there are things that you can sum, that you can average, that you can min, you can max.

Dimensions are things that can’t necessarily be measured. So, they might be things you can count, but that’s about it. So, I’ve got a dimension here for Subcategory. So, I got Subcategory’s name. I cannot add together two or 10 Subcategory’s name and come up with something that actually makes sense. Similarly, I’ve got Product Category ID, So, 2, 3, 4, yes, they’re numbers, but in reality I don’t add them together, because there is no Category ID 9, for instance. It just relates to a particular category.

So, what I have done here in Visual Studio, is created a cube full of analyses that have already been created. So, what I can do, is I can drag out, say, the Subcategory name, and I can go into Measures and get the total List Price. So, I’ll execute the query. So, what I’m going to do is put the Subcategory name into the List Price, it’s currently in the filter. So, here we have the Subcategory’s name, then I’m going to add the category Name, and so on. So, it is already processed analyses that the computer can create other analyses from. So, let’s see how you can import that into Power BI. So, Get Data, and Analysis Services. So, you need to connect to a particular server. So again, I’m going to connect to dot, and you got Import or Connect Live. So, Connect Live is the equivalent of Direct Query, for Analysis Services. You can also write your own query. If you do, be careful, it’s not using T-SQL, it’s’ not using any sort of SQL language. It’s using MDX or DAX. And we’ll be using DAX in the next part of this course. But it depends what sort of cube you’ve got.

There are two versions of SSAS. So, let’s connect. And you can see that I have my cube, Adventure Works 2014, which contains the following dimensions and measures. So, click OK, and notice that we have all of these, measures and products, in categories, exactly as we’ve got in Visual Studio. So, here we have all the measures, and here we have all of the dimensions. Also notice, I can’t get data. So, once you have imported something from SSAS, you can’t combine that with other pieces of data. It is the one dataset. In Edit Queries, you can just go in and edit which database you’re looking at.

So, once you have imported the data, then again, creating the information is fairly straightforward, creating your visualisations. So, get what visualisation you’re talking about. And again, I’m going into product measures, that’s something you measure, and put in the value. And I’m going into Category and getting the category names, and the Subcategory names. Then I can drill down, just like I did in the previous example. So, SSAS is pre-aggregated, pre-calculated analyses that a computer can then build up into your own analysis. So, it’s generally a lot faster and a lot easier to use in its native form. But, when you get to something like Power BI, it’s the same user interface. So, once you’ve imported the data, then you just drag and drop as before.

Now, some of the more advanced features within SSAS might not be available in Power BI. So, for instance, named sets and actions. They might not be available, but the majority of the stuff that you’ll be using on a day to day basis, you will be able to use in Power BI. So, that’s how we import data from SSAS.

• Category: Uncategorized