PL-300 – Section 9: Part 1 Level 8: Other Visualization Items Part 5

89. Use the AI Visual Decomposition Tree Visual to Break Down a Measure

Now, in this video, we’re going to break down a measure. So, a measure, we’ve got for instance, is the sales volume. So, I’m going to insert a table, put it over here on the right-hand side. And this is just going to have the sales volume.

Now, we can break this down, for example, in years. So, I’m going to get the years. So, I’m going to put it at the beginning. So that breaks down this one overall measure of 3.795 million into various years or these 22 years. We can also break it down then by month. So now we broke it down into 22 years, 12 months, that’s 264 months. So, this is one way of breaking down a measure. And we can also, if I just get rid of year perhaps, and just have month, have this as a graph as well. So, I’ve just copied it, pasted it. And I’m now going to change this into a small graph.

Now, we can break down the measure, additionally using a Nova Visualisation called the decomposition tree. So, sounds awful scary, don’t worry. I’ll show you what it does. So, here we have our decomposition tree visualisation. And just like the previous one, the key influencers, we have got analyse and explained by. So, I want to analyse, again, the sales volume. So, I’m going to drag that measure into analyse. And you can see, we start off with the sales volume of 3.795 million.

Now, what factors could explain a sales volume? What breaks it down? Well, I suppose in the year. Let’s put in the month, let’s put in the quarter. All of these different dates could explain it, perhaps. I’m going to put in month above. I don’t want it to be part of the hierarchy. I want it to be an individual value by itself. I think also, what might explain it is region name that could explain it. So, here are some factors.

Now, I’m going to break down, we’re going to expand this sales volume and I’m going to click on this plus. And it says, “Well, how do you want it to expand?” Maybe, you want it to expand by year. And now it will show all of the years with this bar being relative to the maximum year. So, highest number of sales. So, you can see 2007, 2006, 2004, 2003, 2002, it roughly the same amount of sales. And you can see real dips there. Okay, I can click on one of these, 2008.

Now, notice what’s happened. It filters is down. So, we had, on these visualisations, 3.795 million. Clicking on this, we now just have 123,000. So, it can filter down the other visualisations. Here you can see it’s being used as a highlight.

Well, let’s say I want it to be a full filter. So, I’ll go to format, edit interactions, click on this decomposition tree. And I’m going to say, “I want this to be a filter on this particular one.” So now, when I click on say 2008, we don’t have a small bit shown. We have the entirety of the graph changing. Okay, how do we want to expand this?

Well, let’s expand this. Just this value, 2010 into quarters. So, here we have 2010 expanded into quarters. And again, you can see how it changes. And now let’s expand this second quarter into region names. I don’t have to do it in any particular order, just because I’ve put explain by as year, quarter, month, region name. I don’t have to expand it like that. This isn’t a hierarchy I’m doing. So, I’m expanding it into just this 2010, quarter two, into region names. And again, I can click on any particular one. Or, maybe, I don’t want quarter anymore. I can remove the level. And now I’ve got 2010 into all of these different region names.

Now, if there is an explainer that you want to remain, you can put the lock symbol next to it. So there’s no X next to it. So, I can’t accidentally delete year. I have to unlock it first and then I can delete it. So, now I’ve got the sales volume just broken down by region name. And that’s pretty much all the decomposition tree is. It allows you to put in a measure and it allows you to drill down and you can put in as many different explainers as you want. And then say, “Actually, I want to not explain about that, but I want to explain by something else.” High value, that then it shows you what of these has the highest value. So, at the moment, quarters which have a higher value than say years, because each year would have a smaller amount of sales. Each month would have a smaller amount of sales. So, none of these would have as high a number of sales as a quarter does. And then equally, I could say low value. So, this chooses the more granular thing. And here, the granular thing is 2009 in the years. That’s your lowest value out of all of these explainers.

So. the decomposition tree, it allows you to get a particular measure, in this case sales volume, and it allows you to explore. It allows you to see, perhaps, why the sales volume is what it is. And it also allows interaction with other visualisation. So, you can see just a bit of a filter but it’s also a tool for you to explore and drill in and then say, “No, I don’t want to drill like that. I want to drill in with something else.”

90. Creating a Paginated Report

Now, the reports that we’ve done up to now are adaptive reports. And it works if you give them more screen space, they will expand and if you give them less, then they will collapse. And this is the same for the power BI Service which we’ll get on into part four of this course. However, what if you need everything to be exactly the correct position on a page? Well, these are cold, paginated reports and we don’t use power BI desktop for that. Instead, we use quite a different programme and this is called Power BI Report Builder.

Now, this is used specifically for creating paginated reports. So, let’s install this on our computer by going to this install link and then download. Again, this is another free download and you can see it’s a very small programme just 28 megabytes. So, you can see it just takes me a matter of seconds to download it. Let’s click on it and install it. Click next, accept the terms and licence agreement, next and install. It’s as easy as that.

Now, let’s open up the Report Builder. If you have used other programmes in the past, such as SQL Server Reporting Services, SSRS, there was a report builder that you could use with that and you could also use it to wave SharePoint. So, what Microsoft have done is taken this smaller version of SQL Server Reporting Services and then re-package it as the Power BI Report Builder.

Now, these reports are designed to be printed on physical papers or shared and is designed to fit on a page so if you’re in United Kingdom, that would be an A4 page. If you’re in America, that would be a letter page. So, let’s create a new report and I’m going to have the Table or Matrix Wizard help us.

The first thing it does is ask you, what data set are you going to use? An existing data set that you’ve already got loaded into Power BI Report Builder or a new dataset? Well, it’s going to be a new dataset and I need to create a data source connection, so I’ll click on new. I will put in my data source, let’s call it, and we select the connection type and you notice right from the beginning, there is something quite different with Power BI Report Builder. We can’t connect to our Excel data. All I can do is connect to a database, either in premises, on premises, or an online version in Microsoft Azure, including the Detaverse. The Detaverse is the new name for the Common Data Service, CDS. So, what I’m going to do, is connect to an existing Microsoft SQL server table. I don’t expect you to actually have this table in your computer, I don’t expect you to have SQL server.

So, this is just an example for demonstration purposes. I need to build my string. I’m going to select the express edition but you’ll notice there aren’t any server names coming up. So, this is not exactly helpful. So, I’m going to go back to SSMS. I’m going to connect to this and I’ll just copy my server name there. Now, I can select a database name, while I’ll go for an adventure works database. I’ll test the connection, make sure it works, click okay so I’ve got my data source, go to next. Well, I’m going to go into the human resources schema into a table, and I’m going to get the employee table.

Now, there are lots of things you can do here but like group an aggregate so for instance, maybe I wanted to know the number of people at a particular job title or for each job title.

Alternatively, I can edit as text and that gets me to the SQL statement so I could just copy and paste my SQL statement into here if I wanted to. If this is fine, click next, say what I want to use in rows, what I want to use in values and what to want to use in columns. If I just use row groups or just use column groups and this becomes a table, otherwise it becomes a matrix.

So, let’s say I want the job title in the rows, I also want birth date and gender and in the values, I want number of vacation hours, number of sick leave hours. Let’s also add in their login ID as well in the middle. So, click next. So, how do you want this to be shown? Do you want it to have subtotals and grand totals and you can see what it would look like, so there’ll be a subtotal for gender, subtotal for birthdate, subtotal for login ID? That for me is a bit overkill because birthdate, gender is probably going to be one person. So, I might need it to put in those totals separately. So, I don’t want these subtotals or grand totals and then do you want to expand or collapse the groups? I’ll say yes for that. So, click next, this is what it looks like, press finish and there it is added into your Power BI Report Builder.

So, let’s just have a run of this. So, I’ll go to view, run and here we can see what it looks like. So, I’ve got an accountant, there’s their login, there’s their birth date and gender, female.

91. Exploring Power BI Report Builder

So, this allows for a limited interactivity in terms of being able to collapse or expand various levels.

Now, what I can do is look at the page set up because these are going to be paginated reports. So, I’ll click on the “Page Setup”. So, it’s selected for letter, I’ll go for letter landscape. Let’s have a look at this in print layout model. Does it fit all on the one page? And I can go to the next page and yes, it does. So maybe I’ll change that again. Going back to portray, does it fit on the homepage? Yes, it does. And then I can print this.

So, let’s print this to Adobe PDF. You might also have Microsoft print to PDF. So, I’ve just navigated to the folder I want it to be. so I’ll call this a PaginatedReport and now it’s printed. I can open up this PDF and there it is. So, this allows me to have pixel perfect representation of my data.

So, let’s go back into the design view and have a quick look at all of these tabs. So, first of all, we have the home tab. So, this is why we can change the font, the paragraph, the border, the number so whatever. For instance, you saw those dates that came up, had the date and time. I don’t think we particularly need date and time for birth date. So, let’s just change that to date. Go back into the print layout so I can play with it. And now we can see the birth date. Yes, that works much better.

Let’s have a look at the insert tab and we can see here, we can insert tables, matrix and list. Very few data visualisations compared to where we were in the Power BI Desktop. We’ve only got charts and if I go to insert chart, we can see the various types of charts. We’ve got available fairly similar to Excel maybe a slightly different styling. We’ve got gauges, maps, data bars, like conditional formatting and Excel spark line and indicators. We can also add in text boxes such as this at the bottom, which is in our report footer. It shows when we are running this report, we can add images, lines and rectangles. We can add sub reports so we can have maybe a report which just lists all of the job titles from one particular table. And then with any sub report, we have a sub report about that job title maybe all of the employees who work in that job title and then we can add or remove head and footers.

And then finally, we’ve got a few things in the view section: report data grouping, parameters, properties and ruler. So, the report data over here that shows us the boot in fields, like number of pages and page number. We have our data sources and then the fields in each data set. The properties over here on the right-hand side. This allows us to fine tune things like alignment and border font and self-off not just for a particular element, it could be for the entire report or maybe for an individual cell. And by the way, when you click on an individual cell, you get this little data icon. So, it allows you to overwrite what’s there with different data field. So, this is the Power BI report builder. There’s not too much in it as you can see, compared to where we were with Power BI Desktop. If you have used SSRS, SQL Server Reporting Services in the past then you’ll probably be very familiar with this. If not, this is just one item in the DA-100 exam. And I think all you need to know apart from a look around or what are they home insert with very few data visualisations and view is how to start creating a paginated report. And then what sort of things it can do, why would you create a PaginatedReport as opposed to an adaptive report? You need everything to be pixel-perfect.

Now, finally, let’s publish our report. It’s probably worth saving it first. So, let’s save it to this PC. So, I’ll call this paginated report and I’m going to publish this. So, I need to have my Power BI Service login. Don’t worry too much about this. I know we’ve created a login earlier near the beginning of Part 1 of this calls, but we’ll really we’ll be looking at Power BI Service in Part 4 of this course.

Now, the login, why are we going to use? If you’re going to upload paginated reports then you need to have premium capability. So, this is the most expensive version of Power BI Service. It’s around $5,000 per month for an organisation. So, if you’ve got Power BI free or Power BI pro, then you won’t be able to load paginated reports up onto the Power BI Service. It’s only for premium users.

So, that’s something else you probably need to know for the exam. So, let’s sign in. And then after a minute of waiting, where if you just click on the programme, you just have a sound effect. I eventually get this and I can now type in my password and click on sign in. So, now I can save my paginated report to the Power BI Service. So, I click where I want to save it. It’s got a file name, click “Save” and the find out that I’m unable to save it to the workspace because I require a premium capacity workspace. And all I’ve got is a premium per user workspace. So, you can see that even though I will be paying $20 a month for this, I’m unable to applaud paginated reports. I need the $5,000 premium capacity workspace.

So, this is how you can create paginated reports. You need the Power BI report builder. You can’t do this in Power BI Desktop. As you can see, it’s fairly limited in terms of what you can do but if you are used to SSRS, then you’ll be used to this. If not, you’ll only need to have Power BI report builder when you need to create a paginated report. And remember the source data must be from a database.