POWER BI DASHBOARDS & VISUALIZATIONS

TRANSCRIPT

 

Welcome, everybody, to Power BI Dashboards and Visualizations. Just to quickly let you know who we are, we are Eastern Analytics, a boutique consulting service who helps customers maximize the value of their data. We are analytics people. We specialize in Microsoft Analytics, Power BI, and Databricks. As seasoned experts, we have the functional knowledge that allows us to understand our customers' sources of requirements and the technical expertise to design and build systems that are robust, flexible, and secure. We assist our customers with their analytic needs, taking them through design, development, deployment, and DevOps. We provide technology advice, solution architecture, design engineering, dashboards and visualizations, and staff augmentation.

 

Now, Scott Pietroski is Eastern Analytics Managing Partner and Senior Solution Architect. For more than 25 years, he's been building out analytic platforms for corporations such as Bose, Adidas, Estee Lauder, and many more. He specializes in Microsoft Power BI and Azure and is excited to share with you today's presentation. So, let's dive in.

 

Hey, everybody. Welcome. My name is Scott Pietroski. As Kerrilee said, I'm a Solution Architect. A lot of the solutions that we build actually have Power BI as presentation layer. So, because of that, we thought it would be good to give a webinar on parts of Power BI that a lot of people don't necessarily think about. A typical developer just works inside of a certain area, and they might not work inside of other areas. So, that's the thought behind this webinar.

 

Today's presentation, we're going to talk about the Power BI desktop versus the service. We're going to talk about that just for a brief amount of time, and that's because some people on this webinar may not be familiar with it all. We're going to talk about the difference between reports and dashboards. We're going to talk about wire frames, so what they are and why do it. We'll talk about the use of themes and templates within Power BI. We're going to talk a little bit about the visualization components. We're not going to go too far into the visualization components because we could do like three hours on that. Along with the visualization components, last but not least, we're going to talk about securing your data within the Power BI desktop and then publishing those rolls out to the service.

 

At the end, there will be a Q&A. Because we don't want necessarily, when we do the questions throughout the webinar, lots of times we go down rabbit holes and it takes up the time of everybody who doesn't really want that topic. So, we're going to do the Q&A at the end of this webinar. Please hold your questions and you can submit them to Kerrilee.

 

Now, one important thing that we wanted to note is for this webinar, I put it together and I use the Microsoft Corporation's Alight Reserved sample file. I use their Power BI/PBI X file. That way I could have screenshots and it's not our customer's reports and basically, we didn't want to have any confidential information there. So, let's get started.

 

If we think a little bit about Power BI desktop and the service, what exactly is the Power BI desktop or the service? Now, the Power BI desktop, that's what most of you people have already worked with. Your developers are working with it now. It's a standalone application where you do all your development work. It's stored locally in a PBI X file. It supports the data ingestion, the transformation of your data, some people call it wrangling, the data modeling, and the creation of roles and reports.

 

Here's just a screenshot of the Power BI desktop. Most of you are familiar with this. Basically, we have over on the left, we have Reports. This is a Reports tab. This is where we do our... Basically, it's our canvas for each page we have down below. We can view data, also the data that's saved inside of this data set. And we have the data model.

 

So, the data model is how you tie together all your different tables that you have inside of your data set. We can have all the ability to ingest all the data up above. We have power query that you can transform your data in. It's a complete development environment.

 

If we look at the Power BI service, the Power BI service has a lot of overlapping functionality with the desktop, but it has a lot of its own unique functionality as well. You can build reports out on the service, but we're going to talk about just building reports inside of the desktop. We're not going to touch on data flows or anything like that. But we're going to talk about the service when it comes to basically creating your dashboards and then how you can secure your data outside of the service. For the purpose of this webinar, we're just going to talk about the desktop and the creation of dashboards in the service.

 

What's the difference between a report and a dashboard? A report is what we just saw in the Power BI desktop. Everything inside of a PBI X file is considered a report and you end up having pages down on the bottom. Those are the different tabs. In the power BI desktop, a report can have multiple visualizations - you can have all your slicers, the different pages, the different views of the data. Reports are interactive. As you navigate through your different visualizations here, they're completely interactive. When you select a specific data element on one of your visualizations, the other ones automatically filter. So, it's an interactive experience.

 

Importantly, a report is built on one data set. A data set basically is what you pull into that Power BI desktop PBI X file. It can be data from a bunch of different sources, or it may just be data that's related to what you're trying to do. What I mean by that is, you may have a PBI X file where you're just trying to report on inventory data. That may just have data in it related to inventory. It may be your material master and things like that. You may have another data set in another report which has to do with your marketing data. But whatever you pull into that model, and whatever you can model together inside of that one report and inside of that one data set, is what you have visibility into.

 

For granularity, reports can display data at a high level. If we look at just a simple display here, these different visualizations, you can display data at a high level here. But you could also have on other pages, you could have the details for visualization. You could have grids or matrixes that actually just show you almost down to a transaction level. You can go as low as you want. But Power BI automatically summarizes. You create your reports inside of the desktop up in this tab here, and then on each page, you can then add different visualizations to it.

 

If we think about...One of the things you'll hear people talking about paginated reports. Paginated reports, we're not going to go too far into here. But basically, think about any reports. If people have been around a while prior to dashboards, think about classic data warehousing or just standard corporate reporting where they print out reports page over page, over page. That's what paginated reporting is. It gives you the ability to push your data out and to see it with control breaks by page, basically.

 

This is a picture of a data set and a data model. As I was talking about before, we have all of our tables and things over here. These are all different tables that we pulled in as sources. This is a visualization of those tables. Then inside, we've drawn the associations between our tables, and Power BI uses that to navigate. Basically, these relationships we're drawing between the different tables, it uses the connection to see which way filters should go. If you filter on this table, does it automatically apply to this one? Does it propagate through the rest of them? So, you do that inside your Power BI data model, and it's part of your report.

 

If you think about dashboards, dashboards are different. Dashboards are something that are out in the Power BI service. Basically, you can't navigate on them in the same way that you can report. There are a collection of visualizations from existing reports. If I go into reports out in the service, if I open a report and I open a visualization, I can go into that report visualization and say, pin this to a dashboard.

 

When I pin it to a dashboard, it will show up as just one visualization on the dashboard. Why is that important? Well, because your dashboard should be just a high-level view of what the consumer of the dashboard needs to be able to make quick decisions and look to determine where they should focus your attention.

 

This is an example of a Microsoft dashboard. And we can see here, this was actually the report that I was in in the desktop, and with it, it has dashboards. It also has the individual reports that were in there. If you select this, you'll the see the pages in here. But this gives you a high-level view of your organization or whatever the purpose of that dashboard is. If I select this, it's going to drill through to the report. It's not actually going to change the rest of these visuals like a report would.

 

For sources, one of the nice things about dashboards is you can combine data from different reports. You can pin any visual to a dashboard as long as it's inside the same workspace. That way it keeps the data contained to who should be able to see it and have exposure to that particular data.

 

But you can create dashboards that combine different sources. For example, like sales and marketing. You may have sales reports, you may have marketing reports, but you might decide that your executives want to see sales and marketing in the same view to be able to analyze what's going on.

 

They're also useful to combine different sources of data, maybe cloud data. So, it might be something that you're taking your cloud data and pulling that into Power BI into one report. And then if you're advertising or wherever the source comes from, and then you want to combine that with your ERP data on the back end. An example of that is just a different dashboard where this one happens to be marketing data, basically cost per click, number of conversions, the budget, that data we can see here, and they can analyze that quickly as to what their trend lines are. We also have conversion rates by campaign over on the right. But in the same view, they can also see their fulfillment. They could see the quantity shipped and what they're shipping compared to their conversions that are occurring over here. It's just another example of a dashboard. They basically give high-level insight to people.

 

If you think about dashboards versus reports, what's the difference just as an overview? When it comes to pages, dashboards are always just one page. It can be a big canvas. You can scroll through it, but it's just one page. Reports, they support multiple pages. They have different tablets down the bottom. You can get into whatever level of detail you want with different visualizations. For data sources, they support multiple data sets for dashboards, and that's because you can pin visuals from multiple data sets to it. For a report, it has a single data set, so that's what's pulled into it.

 

If we think about visualizations, dashboards you want to build around quick visualizations. You want people to be able to look at a dashboard and quickly view and make decisions about the state of their organization or the state of whatever they oversee. They can say, 'Oh, this is where I need to focus my attention.' For reports, you can have them at whatever level you want. It can be quick visualizations on your front pages, and then you drill through to another page on the back, which is much lower level of detail.

 

For what's available in the Power BI desktop, you can't use dashboards in the desktop. Of course, you can use reports in the desktop. There's no filters or slicers on the front end. It's not interactive. For the portals, you can just drill through. For the reports, they're fully interactive. So, all of your visualizations are coordinated by Power BI.

 

When it comes to user interactions, it's limited to drill through in the dashboards, but it's fully functional on the reports. It's very common to have a lot of reports of all the different functional areas and just a few dashboards. It really depends on who your audience is, who your consumer is, whether they just want a quick look or whether they want to analyze their data down the details.

 

For favorites, you can add dashboards to your favorites and also your reports to your favorites out of the portal. Alerts, you can set alerts inside of your dashboards. You can't set alerts inside your reports. When it comes to subscribing, you can subscribe to either a dashboard or a report. When it comes to exporting the data in a visualization, you can export the data for both of them in dashboards and reports.

 

I'd like to talk a little bit about wireframing. What is a wireframe and why would we do it? A wireframe is a mockup of a dashboard or a report. That's how it really is with a bunch of details in it. It's a mockup of it. You can create it in Vizio, Excel, Word, PowerPoint, whatever you're comfortable with.

 

Importantly, it should be based on the user's data journey. What does that mean? That means that you want to look at it and you want to basically work with your user and say, 'how do you analyze your data?' Think of it as if you're designing a dashboard. You want to know the different data elements or panel of visualizations they should see and what level of detail they should be at. If it's reports, you want to know, well, how are they trying to make their decisions out of this report? Are they just a detailed person with a matrix in there or are they looking at bar charts and things?

 

So, your wireframe should include the layout and the types of visuals you're going to use. It should identify your data sources. The details in your wireframe should go down to your field or filter level. Then you'll also want to identify the theme or template you're going to use if you use themes or templates.

 

Now, why do I want to create a wireframe? Basically, what it does is it documents the requirements, especially with dashboards. Because you're working with the user to put it together, it basically supports consensus between IT or whoever's building the dashboards and the consumer of it. It can provide...you can use this as a spec, and then once it's built, you can get it signed off on and say, 'Okay, test it and sign off on it. This is exactly what the spec is.'

 

Then one of the other things that usually you hit later is who should access this? What access controls do we need in place? Meeting with the business and going through your wireframing process will pull those things out of them.

 

Last but not least is it reduces the time to delivery. So, if you spec it out, which is really what your wireframe is, it's working with the business to make sure your spec is right, it will reduce the time to delivery because then you'll build it correct the first time and then you just go through minor tweaks. It's not something where you build it and then it's not even close and it's much bigger steps in the development process.

 

This is just an example of something. A wireframe doesn't have to be complex. It basically just shows the layout of how you think that they would like to see their data. It lays out maybe the cards or the key figures or KPIs you're going to use to see as your single cards or multi-line cards on the left. Your different visualizations. You're going to have a bar chart, you're going to have line charts, what exact data points are on there. Whatever level of detail you want as a second view, how you're going to present it. It might be a grid. It's up to the business to decide. Then how do you work your way through the data? As a person, as the analysis path, do they start out at the region and then go to the state? After they get to a state, they're concerned with the top 10 customers. Is it a regional manager that's looking at regions and sales reps and maybe states within it or zip codes? If you get that out of them, that should all be inside of your wireframe, and that way you know how to build it, how they're going to navigate on it, and how many pages it's going to be.

 

This is another one that I put together, which is basically something I threw together in Vizio. It's not something I would really use, but I threw out here just because in Vizio, they do have shapes and objects you can drag into your wireframe. I usually particularly use Excel, and then something like this would end up with all sorts of comments all over it, and it might even be in the pen, of the comments.

 

After that, let's talk a little bit about, I mentioned before about themes and templates. Now, what is a theme? A theme is basically the esthetics. It's what you see when you go into your reports. It's the colors that are chosen. It's the fonts on your screen. For anybody who's worked with web development in the past, it's similar to a CSS, which is a cascading style sheet. It has all your different color definitions and basically your fonts, sizes, whether they're bold, italics, the rest of it. But in your theme, it really would point out something like you could go in and say, 'Okay, do my headings have a blue background with white letters? Or are they all just bold with black ink?' That's all defined inside of your theme.

 

Now, there's predefined themes. If we look at predefined themes, here's a picture of Power BI desktop. If you go to 'View', immediately you're met with this ribbon. This ribbon here is basically a theme. You get to choose which theme you want, and when you select it, all of the different colors in the different locations on your visuals will change. You'll notice in here, it's not a coincidence that they actually have eight different little bars in each one of these. That's because it uses eight different primary colors throughout the theme to highlight data and alerts and things like that. These are all different themes. Each theme had a graphic artist basically create a palette, which are different colors that go together and line up. They created these themes, these are pre-delivered.

 

You can create your own. If you have colors that match your corporate colors, maybe they match your logos or whatever the different colors are that you use in your banners and those kinds of things, you can get those colors in there either rbg or hex codes.

 

A theme file is nothing but a JSON file. It's a JSON file with the different colors and things assigned to the right levels, the right tags, and you can upload or download them. So, you can go and browse themes. There's a Microsoft Theme Gallery where you can actually people have uploaded themes and you can just download them, because it's simply just a JSON file. You can save what you have and create a theme. Lots of companies come up with a theme that they use across their dashboards and across their reports.

 

Now, if we look at what a template is, a template is actually the report itself. So, the theme controls your colors and fonts and all those kinds of things. A template is actually the content. So, it's a way of saving an existing PBIX file as a template file instead.

 

So, what's the difference between a report file and a template file? Well, what's in the template file is, it dumps out everything you see, everything you could see. If you just go to File, Export, Power BI template, it saves everything you see here, all the pages, all the tables, everything. It's just there's no data and there's no connections for them. So, when you open up a Power BI template file, when you open it up, the first thing it does is it tries to refresh the data set.

 

This is an image of trying to open up that template. First thing it does is, it tries to do all of the data connections. It cannot load any of the data. So, there's no data in here that you have to worry about exposing to the wrong people. But you can then use this and click on a visualization and see over on the right how it was set up and now bring in different tables for a different data set into your model and add those to the visuals. You can basically migrate your form and layout from one report to another. Now, one of the important things about that is you might have come out, you might have created this template here. The finance department might have created it. And that's great, but the finance department doesn't want anybody to see all the finance data except the finance department. And that's all saved inside of this PBIX file. They imported the data, they did the modeling, and they built their visualizations. So, a template gives you the ability to use the finances reports and create another one for, let's say, marketing that looks very similar to finance. It's just the finance didn't have to expose the finance data to another group. So, it controls data access. It provides a starting point for some other report, but it controls the access.

 

A little bit about visualizations. Visualizations, we're not going to go too far into them. Once again, we could go into them for hours. There's all different kinds of charts. There's all different kinds of standard charts. It's very similar to Tableau, except for the fact that Tableau's visualization seem to be crisper, in my opinion. But they have all different kinds of charts, bar charts, line charts, area charts, the rest of it. They also have maps. They have the ArcGIS, which has been around a long time. They also have the Azure Maps inside of here in different shapes and different fills. In the category of others is what a lot of people use. Actually, something that we use all the time is matrix. We're using either a matrix or a table a lot of times, which is the grid that you think of. If you're doing something that's really more of a reporting rather than a visualization, lots of times you're showing data inside your table.

 

If we look here, in this case, I just happened to highlight a visualization that I selected. I highlighted in yellow. All I did was select it and it automatically shows me which visualization is being used over on the right. Then it automatically also shows me which tables and fields have data elements in this visualization. It's very user-friendly.

 

If I wanted to experiment, I can simply click on a different visualization, and it will change the data and display it in that different visualization. As long as your data elements and stuff make sense to the new visualization, it'll switch, and it will show it to you.

 

An example of something that really didn't make too much sense to it. I took that same visualization that was highlighted here as a bar chart and put in here a key influencers control. When I did that, it really didn't show me much. Some controls or some visuals work better than others. It depends on your data. I'm sure everybody on this call is probably really familiar with that.

 

I do have here a page of all the different visualizations. We can provide this as a handout to you. That's no problem. These are all actually links to the different help locations for each individual thing, each individual visualization. We also have a link to Microsoft Help atop. We can provide this to anybody who wants it and we'll send it out to you.

 

Last but not least, let's talk about securing your data. Securing your data. Basically, for the most part, when you secure your data, you're either securing it in your workspace, out in the service, you're making sure that nobody can have access to a workspace that has data that they're not supposed to see. But there's also something called role level security, which has to do with applying a filter. Basically, the system automatically applies a filter.

 

Think of it as if you had a certain group of business users who were only supposed to see one company code or one sales organization or some type of unit within your organization. What you can do is you can create role level security. You'll create a role which basically has a filter in it. Inside of here, I just went to Modeling and then Manage Roles. I created these three roles just for this webinar. The first example, I said, 'Okay, well, I want this just to be a simple filter on X box data.' So, whoever is assigned to this role, the system is automatically going to apply this filter to their data.

 

So, how I set it up, I created the role. When I create the role, it automatically shows me all the tables in my data set. I selected Product and hit the drop down and it showed me all of the columns inside the product table. I selected the product column, so it's a field you can tell by the brackets like in SQL. I selected the product column, and I could just index into a filter. I want whoever's seeing this role should be filtered to just the X box data.

 

You'll want to get more complex because that's very simple. Whoever's assigned that role can only see X box. If you want to get more complex, we have many cases where you actually have an authorization matrix. Think of it as you may have somebody who's authorized to see certain zip codes, certain regions, and certain products. It's tough to handle with just a bunch of roles. But what I did here, I mean roles just with filters on them, what I did here in this example was I added this table. I called it a user control table, but I just called it that. All it is is the table that I entered data into in Power BI. And in this case, I added just a product ID and I added in the user ID of who could see it.

 

This is a simple example. You might actually have product, you might have zip code combinations of different products, and then the users might be sales reps. You could have this table, might have a thousand rows in it or something. But the purpose of it is, you can then take this table and use it in your data model and automatically filter all the data so that people only see what they're supposed to.

 

And you do that by doing this. I took the control table that I had that I created, which is full of my users and the products they can see, and I took that table, and I just joined it over to my product. In this case, it's a single key, but I joined it over to my product master. Now what's going to happen is when I use this role as consumed, it's going to say, 'What products can Scott Pietroski, which I was the one in the table, see?' And it's going to filter this table to just the products I can see, and that join is automatically going to enforce it and propagate the filters through the rest of the data.

 

In this case, what happens, what I did was I then said, 'Okay, well, for that role, I want to go in and I want to say, Okay, on that control table, I want to filter where the user ID', which is the ID next to the product in that table, 'I want to filter it to whoever is executing the report.' So, this is the function that returns the ID of whoever is using the report. This particular function, user principal name, returns the Office 365 username. This Office 365 username is the one that's basically when I'm logged into Power BI service, it's going to see I'm Scott Pietroski, it's going to automatically filter my product control table and Scott Pietroski records, which will then join to my product master and filter all the data for me. That's how you enforce the role level security in a matrix kind of situation.

 

Last but not least, if we look at securing your data, most people will never need to use this. But there is something called object-based security. What this does is it actually locks down visuals from using data that's in an identified table or field. And the reason it's used is, let's say you had something sensitive, let's say you had data that inside of your data model that was going to be maybe employee data. Maybe you have an employee report, you're doing HR reports and you don't want them to see whatever it is. You're doing analysis of vacation days, but also in the table you have salary range or a pay grade or something.

 

Well, you can go in and make it so that nobody can use that, even though it's in a table in your data model. And what you do for that is you go in and you create a role similar to the other roles. From there, you then go in and inside you need to install an add in. There's something called tabular editor. The tabular editor is something you install, and it shows up under... When you install it on your PC, it shows up under external tools. When you go to external inside of the desktop and click this, it pulls it up and it's automatically basically updating the data set inside your PBI X file. When you open this up and you go to your role, you can go over here and set permissions on an individual table. The default means you can read it, but in this case, you can go in and on the table and say that there's none, and then any visual that has that table in it won't work. This basically turns off access to a table.

 

You can also take it further. Instead of just a table, if you have your table here in your role that we added, when you select it, you can go in and on the field level inside of there, you can select an individual field and turn those off. That way, if you did have something in a table, you can turn off most of the table but make some of it available for reporting.

 

I know I covered a lot of stuff here. I tried not to go too fast, but just over the 30 minutes. Now, for anybody who has any questions, now I do have, it looks like there's some questions in here.

 

Why should a company hire a consultant to build it in-house as Power BI's self-service tool. Well, yeah, if you know what you're doing. You don't need to hire a consultant for something if you know how to do it. It's just if you have something complex or if you don't have the amount of staff that you need to deliver what you need, that's why you would hire one.

 

How long does an implementation take to build 10 dashboards and 100 reports in a Power BI project. And what are the costs? Well, we would have to talk to you about that because it really depends on the complexity of your dashboards, the complexity of your reports. And typically, the complexity of your reports, a lot of the time has to do with harmonizing your data and pulling in your different data sources and stuff like that. So, a lot of our projects, we do the power BI piece, we have power BI people, but we also do a lot of the data engineering in the Azure space. So, we deal with the data factory or Databricks, if you're using Databricks, actually, usually they're in conjunction. We deal with sourcing the data, transforming the data, building out, in some cases, data warehouses, or enhancing the data warehouse, so that it has all the data that you need to support these hundred reports or ten dashboards. So, it really is a case by case. We'd have to talk to you on it. There's no way for me to estimate that for you.

 

Is Row level security linked to field-level content? Yes, it is. Row level security, what you're actually doing is you're putting a filter on a field. It's called Row level security, but what it's doing is it's only returning the rows you're authorized to see based upon the field's content. So, yes, it does. Row level security is linked to field-level content.

 

So, this is a similar stuff - Example: A sales director has access to all stores in a region. We did do a project for somebody where they were dealing with zip threes. The first three codes of a zip code were their territories. We did do a project for somebody where they had sales reps that were assigned to a whole bunch of different territories, which was zip threes. Then we also had another column which was the manager, which would oversee a whole bunch of reps. We did use the row level security for that. Similar to the matrix table I was showing as an example. Basically, that matrix table, we did the same thing. We built it out, we joined it to their transaction data. It was actually cell-through data. We basically could join and make sure that the sales reps could only see their own data and the managers could see the data for all of their reps.

 

I think I just answered the next one already, which was the sales manager only has access to his stores within the region. So, yes, you can build the matrix out for that without a problem.

 

Then does the data go to the cloud or stay on prem? How is ETL done? Which tool is used for ETL? In the Power BI desktop, basically, you can do your transformations there in the desktop. Whether you move it out to the cloud or keep it local is whether or not you publish your desktop files out to the service. When you use the service...I guess I answered the question. They all disappeared. I don't know if it's Kerri just did that or whether they just disappeared.

 

Okay. I think it looks like it's the same as the questions. If anybody has any more additional questions, we'd love to answer them. I'm glad some people enjoyed this. Hopefully, it was helpful. I tried not to go too far into the weeds because it can get very weedy. We could have spent a day just on the visuals. I thought it's more important to show things related to that people wouldn't usually run into.

 

Thank you very much. Oh, and Kerri said she erased the ETL question.

 

So, for the ETL question, it all depends on what you're trying to build out. We've built out entire... What people would think of as a data warehouse. In Databricks would be a Lakehouse, but it's not necessarily a Lakehouse. We were using Blob Storage in Azure. We're using the data factory to pull the data in into orchestrate. We're using something like an SSIS, which is a Microsoft tool for ETL, which basically you're using in this case for transform as data flows through your data warehouse. There's standard concepts that we use to source, transform, and then deliver that to Power BI as a presentation tool.

 

Okay. Well, thank you very much for coming. I thank everybody for coming. Hopefully, I was able to answer the questions. I'm glad that there were a lot of questions. Thank you.