1. Data Visualization
So the last topic of this exam and of this course is data visualization. Now, as the name implies, this is how you view the data within your databases. This is effectively a reporting function. The data visualization tool that will be used in this exam is generally Power Bi. And so we’ll be talking a lot about PowerBI in this video and in the video that follows. One tip: I’m going to talk to you in this video about the different components of Power Bi. It is worthwhile to pay attention to which Power by the Numbers components may or may not be on the exam.
So keep an eye out for that. Now there are three types of reports that we’re going to talk about today. The first type of report is called a paginated report. I’m not a big fan of this name; I find it a bit confusing. But the concept of a paginated report is that it is designed to live on a page. It is designed to be printed. The report itself has a column width that’s perfect to print on a piece of paper. And if the data is going to go on too long, you’ll end up with page two, page three, page four, et cetera. This could be your customer invoices, sales details, a profit-and-loss statement, or anything else that is designed to live on a page but could also live on the screen, but it is designed to live on the page. Now there are a couple of tools that are used for Power Bi-Page Generator reports. The one that you need to know is called Power Bi Report Builder. So that’s a separate piece of software that you install—that’s how you are going to create your page and report on which columns, which rows, and which query pulls it in.
You build that report, you save it, and when you think you’re good, you’ve tested it and it’s looking good. The report is made available through the Power Bi service. So Report Builder is needed for pigeon-aid reports, and the Power Bi service is where the reports can live. And then your users can come and run those reports, print them, do whatever they need with them, or email them. The second type of report is called an interactive report. Now this is a better name. just as the name implies. It is designed to be interacted with. It is designed to be viewed on a screen. It responds to mouse clicks. You can click on it to see more details. If you want more information, drill down and see your data in almost a living form. Now, these types of reports tend to be more visual. So instead of just showing you an Excel spreadsheet of rows and columns of data, really what you want to do is end up building charts—line graphs, bar charts, pie charts—all the different charts we talked about at the beginning of this course—and putting them together into an interactive report. The other thing you can do is hide information. So you can put stuff behind a “more info” link, or you can just use a mouseover or a hover to show information that you don’t want to display on screen. So you can have a line chart or a bar chart that does not show actual numbers.
But when the person puts their mouse on top of the relevant data point, a little pop-up happens that shows the actual number that the report represents. And finally, one of the important things about interactive reports is that the report can change based on the user’s action. So you can add columns, remove columns, sort, filter, and, more importantly, say you’re looking at a national report and you want to focus on a single region. By clicking on that region, more information could actually be added to the report. So now you’re seeing the subregions within that region—the states, the counties, the cities—whereas at the national level you don’t have that much information. So being able to change the report layout based on the user wanting to click, I mean, that’s an interactive report. Looking at Power Bi, the two things you need to know are that you would generally be using something like Power Bi Server. That is going to be what’s served in the interactive report. When your users visit the URL, PowerBI Server interprets it and serves them the report that was promised to them. And then, when they interact with it, that report changes, filters, sorts, and gets more detail. Power Bi Server is the other end of that communication.
The Power BIS server requires the Power Bi Premium in order to use these kinds of interactive reports. The third type of report that we’ll talk about in this video is called a dashboard. Now, dashboards are becoming more and more common. I would say probably ten years ago it was less common, but now you log into a website, even the Azure Portal, and typically the overview screen of any particular resource within the portal is a dashboard. You go into the virtual machine of the Azure Portal. Four charts dealing with memory, CPU, and data, as well as IO types of information, will be displayed. You’ll see the IP address and the length of time that it’s been since it was created, and all that wonderful stuff. So with all this stuff on a single page, you don’t have to click around to five different reports to see this information. It is good for being able to see if something is wrong at a glance by looking over the screen and then being able to click to the individual report if you want to investigate what’s going on. So those are the three main reporting types and how PowerBI tools are used to process them.
2. Power BI Content Workflow
So this is the last topic of the exam, and the requirement says to understand the Power Bicontent workflow as a concept, but I guess we’ll talk about it for the DP 900 exam. So how do you go about creating a report in Power BI? Well, first off, you’re going to need to know where your data is, what database has it, and how you connect to it. You might have to work with your technical teams if the data exists, particularly production that you don’t have direct access to. So now we have to get a copy of the data, perhaps a backup copy, or find a way that the data can live somewhere where you can get access to it and connect to that data source using Power Bi. Now you’re going to want to know how your data is structured. If we’re talking about a relational database, which we usually are, these tables exist. They have relationships with foreigners, especially after normalization. You might have a customer ID that lives in the order table.
And then you have to join the customer table to get the customer’s first and last name or their business name. As a result, you load your data schema into memory. That’s called a data model. So now you know how the data is structured, and you can start playing around with it. So you start to build this query. You’ve got to join certain tables. Maybe you need this and maybe you don’t. You play around with it. You will basically format your report. You use the Power Bi desktop to build this report, and then when you are confident in it, you’re happy with this report. You’re basically going to publish the report. We talked about this in the last video. Share this report with other people. So maybe you want to run the report and then share the output with people, and that is the Power Bi desktop application that can do all that. And that is your typical content workflow.