The number one reason organizations choose Power BI is the ability to visualize data; however, even with all the immediate choices, many report developers desire to create custom visualizations. In this episode, we chat about custom visualizations for Power BI: how to get them, how to pick them, and when (if ever) you would consider building your own. Have a favorite custom visualization you have used? Let us know!
“My rule of thumb, if it’s circular, avoid it. Half-circles, half avoid.”
“Most visuals ought to be easily apparent for somebody who has never seen the visual before.”
“I think for anything that’s a generic need, I definitely wouldn’t do it unless you’re prepared to now have some sort of open source project or some similar level of effort.”
“You’re just going to have a better time if you try to stay in the lane and use whatever functionality’s built in and only start to go outside of that whenever you have a clear and present need.”
Listen to Learn
00:41 Intro to the team & topic
01:15 Compañero Shout-Outs
02:28 SQL Server in the News
03:33 Announcement of future Season 3 of the podcast
04:12 Where you get your visuals and a touch on the security
06:28 Why you might want to use custom visualizations
08:07 What makes a good visual – Kevin says no circles
11:24 Thoughts on good KPI matrixes or layouts
13:55 The line between needing training and understanding a visual out of the box
15:31 Think about your end user before you settle on a visual
17:43 Customizing your own visuals is different from putting your own theme on a report
19:21 You can deploy custom visuals throughout your organization – check out Charticulator
21:06 Under what circumstances would you create your own custom visual?
24:30 What should you do about security with custom visuals?
25:59 Last pieces of advice from the team
28:04 Team contact information
Music for SQL Server in the News by Mansardian
Carlos: Compañeros! Welcome to another edition of the SQL Data Partners Podcast. I am Carlos L Chacon, your host, and this is Episode 166. I am joined on this episode by Angela Henry.
Carlos: Kevin Feasel.
Carlos: And Eugene Meidinger.
Carlos: Okay, so our topic today is going to be Custom Visuals in Power BI and we’re going to be talking a little bit more about the ‘how to use them’, rather than the ‘how to build them’, but we may touch on that as well. Before we do that, I have a couple of shout-outs I want to give, and as always, compañeros, if I get a couple of these accents wrong, you’ll please forgive me. Andrey Karpov, Kris Simeon, he was actually looking to break in as a DBA and found the podcast helpful, so thanks Kris for reaching out. Ajit Chaudhary, Antoni Nowak, Tony Hoechabuer, Fortunato Couto, Rafael A Colon-Garcia, so long time listener. He says he’s been listening for 3 years, so that’s right at the beginning, Rafael, so thanks for finally reaching out. We’ve missed you all this time. And last but not least, Darrent Goonawardana. So again, thanks Darrent, for reaching out. Also want to give a shout-out to David Yakobovitch, who recently started a podcast on artificial intelligence. Now you’ll remember that this was our episode topic in Episode 165 we had with Hope Foley. He started a podcast specifically on AI called HumAIn and I’ll invite you to check that out. It’s available on iTunes as well.
Okay, so for a little SQL Server in the News. So this got a little traction, I just saw Brent was talking about this and then I thought it was interesting that Microsoft came out with something as well. And this is the max log rate in Azure Managed Instance. And I think there’s been some other discussions about Azure SQL Database and just some throttling and whatnot that may go on. But in Azure SQL Managed Instance, there’s actually a new wait type called INSTANCE_LOG_RATE_GOVERNOR, meaning that you can only write so much data to the log. They’re going to traffic that a little bit, and what they do is, now they’ve given you the ability to figure out if you’re hitting that threshold and if that’s an issue for you. And so I know, particularly as we get closer and closer to 2008 coming to End of Life, that more and more folks are going to want to start looking at the SQL Managed Instance and so just being aware of the limitations, I think, is helpful as you plan your migration. Okay, so in other news, we’ve been going on Season 2 technically, for about a year now, and at some point we’re going to start Season 3. I’m not quite sure what that means just yet, but we’ll figure it out.
Kevin: It means we start killing off main characters.
Eugene: Yeah, that’s Chekov’s gun, right? It’s going to start firing.
Carlos: There we go. Oh man, that’s a little more dark than I envisioned, but you know, I guess I’m open to ideas. Okay, so the show notes for today’s episode will be found at sqldatapartners.com/powerbi or at sqldatapartners.com/166. Okay, so we said, our topic today is Custom Visuals. Now, again, for those of us who need some direction now and then, generally when I think of a custom visual, I’m thinking of anything that I’m going to the store where I’m downloading from some external area to import into my Power BI, and I’m going to end up with a custom visual or a pbiviz file. Is that too broad, or is that a good starting place to kind of define what we’re talking about when we mean custom visuals?
Kevin: So, you mention a marketplace. Where is this marketplace? How do I get to it?
Eugene: There’s two places, so nowadays you can go to appsource.microsoft.com. They used to have more of a separate space, but more often you’re just going to be clicking from Marketplace in Power BI Desktop. So at the very top of the ribbon, you’ll just go to Home, Custom Visuals, and you’ll just say “From Marketplace”. You can still import the file directly, but it’s going to be rare that you’re doing that. Usually you’re just going to go to the Marketplace and just search for your visual, hit Add and now it’s part of your report.
Carlos: So do we have the same– gosh, I think of this a little bit like downloading apps on your phone, and in theory, the stores are supposed to vet these visualizations. I know we’re going to talk a little bit about security a little bit later, but do we need to do a Trust But Verify when it comes to any of these visuals or any heartburn you have by downloading and trying them out?
Eugene: If you’re going through the Marketplace, it’s weird because Microsoft tries to cover their butts and says, “hey, we make no guarantees,” but from what I’ve heard through the grapevine, they still have a process for kind of vetting things. Something that’s relevant, though, is there’s certified custom visuals and these have been kind of rigorously checked and as part of that, as well, there’s that guarantee that they’re not talking to the outside world. Because that’s the biggest worry that I have, out of anything, is I don’t want my bar chart to be sending all of my data to some sort of marketing company or something crazy.
Carlos: Sure, sure, or we talked kind of before was that the idea of a map. So what data do I need to send to a map API to get the visual that I need?
Eugene: Yeah, exactly.
Carlos: Okay, so now I have a visual, I’ve downloaded them. Let’s talk about why I might want to start using Custom Visualizations.
Kevin: Yeah, there are a pretty good number. At the moment as I look at this, there are 198 custom visuals available, at least 7 of which are good.
Eugene: Also, I think the enlightened aquarium visual is one of them. You have fish swimming around based on the size of your data. And oh my goodness, it’s Power BI Certified.
Kevin: Yes, this was one of the very first custom visuals that came out. It was a way of showing off the system and giving developers a chance to see, here’s what building a custom visual looks like. It is absolutely useless in business contexts, but it’s a lot of fun to watch the fish swimming around on your dashboard.
Angela: And it is the most popular one that is downloaded.
Eugene: It’s also good for trolling your boss. I have a friend who works at Microsoft and she jokingly put this in some dashboards for a customer and her boss got pranked. So, various uses.
Carlos: So I think that visual ended up being the winner when they had the contest, when they first opened it up to the community, if you will, of having visuals.
Eugene: That’s what it looks like, yeah.
Kevin: It was.
Carlos: I think that’s why that one has kind of permeated as–
Eugene: Yeah, the description says, “starting back in September 2016 when Microsoft revealed the Power BI Best Visual contest, and it won a People’s Choice Award, so this is the populist visual.
Kevin: Right, it’s the one for the regular Joe.
Angela: Yeah, not the one from the electoral college.
Eugene: That’s probably like the bullet chart visual or something like that.
Carlos: Okay, so I guess let’s talk about what makes a good visual. Kevin’s kind of said, “eh, maybe not all the visuals in the Marketplace are what you’re maybe after”, so Power BI being a very visual tool, what makes a good visual?
Kevin: My rule of thumb, if it’s circular, avoid it.
Carlos: Okay. So what about half circles? So, for example, we’ve done some work with some manufacturing and whatnot and they want to know how busy their machines are, right, so the idea of like RPM. Does that visual cut mustard? It’s circular, but it’s like a half-circle, right?
Kevin: Half-circles, half avoid.
Carlos: Half avoid, yeah.
Kevin: No, but seriously, I say that as a joking rule of thumb, but there are problems with circles, and the biggest problem with dealing with circles is that the human eye is awful at gauging angles. So, if you’re trying to slice up a circle, this is one of the reasons why pie charts are such a bad, bad visual, is because it’s hard to tell relative sizes of slices just by looking at them, because your eye is so terrible at gauging approximate angles.
Angela: Well, that is, of course, unless you only have one data point.
Kevin: Then it’s a circle chart, not a pie chart.
Angela: But then there are no angles.
Kevin: It’s just a circle.
Eugene: The human eye is also bad at judging area, as well. A customer has forced me to read Stephen Few and that’s one of the things that he definitely talks about is it’s very hard to compare the sizes of circles with your circle chart.
Kevin: Right, speaking of area, something that we think of, the eye, it’s like, “oh, that’s about twice as big. It turns out that’s actually four times as big because it’s twice as big along both dimensions.
Kevin: So now, if you’re trying to represent a datapoint 10 versus a datapoint 5 and you have an area of 10 units on the screen versus 5 units on the screen, it doesn’t look twice as big to us.
Eugene: Yeah, definitely. I think one of the reasons why a lot of the visuals aren’t necessarily great is there’s kind of a perverse incentive going on, because a lot of the people who are making these custom visuals are treating them as a marketing tool. And so, whenever you’re doing a marketing tool, whether you’re doing it for Microsoft or you’re doing it for the people who made the aquarium visual or whatever, it’s in your benefit to make something that looks as cool as possible. And unfortunately, the Venn diagram between things that look cool and represent data in a clear, concise, accurate manner, that overlap is surprisingly small.
Kevin: Right, and a lot of the core blocking and tackling visuals are already built in. Your bar charts, your column charts, your line charts. These things are already available to you, so people who are enhancing the set of products available, they either have to go a little bit more obscure, which means, eh, sometimes it’ll work, sometimes it won’t. They have to go a little wacky, the aquarium, we’re going to keep picking on it. I love the aquarium.
Eugene: It’s so good.
Kevin: Or radar charts, where radar charts have about three good uses ever, but there are several radar charts available to you.
Carlos: Sure, well, let’s talk about one that even I, so from my distant lens, have seen quite a bit of folks talking about, or at least wanting, and that is KPI charts or key performance indicator charts. Thoughts around good KPI matrixes or layouts?
Eugene: Yeah, I’ve been working with the Power KPI Visual and the Power KPI Matrix Visual and I think they’re really good and they fill a key gap with Power BI today. I mean, it had some KPI capabilities, but it’s really nice to be able to show, “okay, here’s current, here’s previous year, here’s some KPIs related to that we’re showing,” so I think that’s definitely an area where Microsoft can improve upon. And it fits in well with kind of the key strength and all of that. Interestingly, with the Power KPI Visual, I saw an alert that was saying that it was into the old API, so I don’t know if that means, okay, we need to update the visual version with our reporting or it means that it’s no longer being supported or what. So, that’s something that I worry about, but I think that that’s an area where Custom visuals can add a lot of value, for sure.
Kevin: Yeah, and I’m going to repeat something that I’m pretty sure Eugene had said either on Twitter or on the podcast at one point, which is Power BI has never been that great at putting a lot of information in a compact space. Where people like Edward Tufte or Stephen Few are big on “what is the maximum amount of information that I can put into a small area that people can easily discern?” And tools like custom visuals on KPIs are going to help you in that regard, because otherwise, your classic Power BI visuals are going to be relatively space expansive. They’re not going to be dense collections of information that would really make the visualization people extremely happy. Which has its tradeoffs. There are reasons why you want to have a relatively more expansive visual, including, “well, if I’m on a dashboard, it needs to be glanceable.” You can give me a huge amount of context in a dense dashboard, but if I cannot separate the elements, if I can’t separate the components, I’m going to have trouble as I’m walking from meeting to meeting figuring out if my systems are currently down.
Carlos: Sure, sure. Well now, and maybe this is a tangenting a little bit, but, how much of that is training versus out of the box, you should just understand it?
Kevin: The way that I would put it is that most visuals ought to be easily apparent for somebody who has never seen the visual before. There are some cases where you have an information-dense visual that you will want to walk someone through the first time, but then after the first time that person sees it, it now all becomes clear. I think that I would lean toward simplicity, lean toward things that are immediately intuitive over things that are more complex and information-dense, but if we don’t always need that extra information. So for example, a box and whiskers plot versus a violin chart. The violin chart is more information dense. It does provide an added measure of density, but it is also a little bit harder for a person to follow, and so–
Angela: The first time I saw a violin plot, I was like, “what the heck am I looking at?”
Kevin: Exactly, and there are takes on the violin plot that take it even further and they add additional levels of density, they add additional levels of information, well, that has its tradeoffs. If you make a visual that is too information-rich, and a person comes into it without that training, they’re not going to understand what’s going on and will look at this and say, “I don’t know how to make heads nor tails of it.”
Carlos: True. Okay, so it almost seems like it’s a little bit organizational, you need to think about your users and maybe the first experience with it and just because you’ve been working with the visuals. But at the same time, making it straightforward enough that you don’t have to have an hour-long tutorial on how to consume the data and the visual.
Kevin: I would think about games or websites where they have the “here’s two or three screens of information, click next and I’ll show you a little bit here. I’ll show you a little bit there.” And that’s about the extent of where ideally, I would see it. But I think Eugene has something important he wants to say.
Carlos: There you go. Now, so I guess I’m curious. So is the organizational visuals or customizing your own visuals different from kind of putting your own theme on a report or do those two things go hand in hand?
Carlos: Yeah, so no need to jump in for color changes. That’s just a modification of a current visual. You don’t need to build one from scratch.
Kevin: Right. Now, there are custom visuals which are outstanding and fill a huge gap in what Power BI has available. One of my favorites, a little surprisingly, is the Synoptic Panel by OKViz. The Synoptic Panel basically lets you build an image map, so I’ve used this before by taking seating charts and I can click on the seating chart and get information on that office room or be able to slice and filter like which conference rooms have this equipment in it? Or where are the people who work on this team and be able to see that highlighted on sections of the visual.
Eugene: So something I just want to clarify, when you say organizational visuals, are you talking about the functionality where you can specify, okay, these are kind of the specified custom visuals for the whole organization, or are, Carlos, you talking more about actually making a custom visual?
Carlos: Perhaps I got confused between the two, but I guess I was thinking about, yeah, deploying custom visuals throughout the organization.
Eugene: Yeah, so that’s pretty straightforward, at least to my understanding, and you can set it up so that whenever someone says, “okay, I want to add a Power BI visual,” they can select from the marketplace or they can select from My Organization, and so that’s a good way to kind of vet specific visuals, or even like Kevin said, “okay, maybe we’ve got a warehouse for something and we’re going to take the Synoptic Panel and make our specified custom visual based off of that.” One other thing I wanted to add that people may want to look into is a kind of interestingly named project called Charticulator by Microsoft Research where you don’t have to write any of that code. It’s still a little intimidating, but it’s kind of a drag and drop solution for bespoke visualizations and you can export them as a pbiviz file to just drag into your report.
Kevin: Yeah, Charticulator is really cool.
Carlos: Okay, so if I’m understanding what you just said correctly, Charticulator is a way to help me build my own visualizations?
Eugene: Yes, correct. It’s as close as you’re going to get right now to a drag and drop, create your own custom visualizations. So if you want some spiral chart or a funnel chart or something and you want to have a lot of control over that, that’s a way to do that and it can export to that format that you can, then, import into your Power BI reports.
Carlos: Very good, we’ll make sure we put that link up on the show notes. So then, as we’ve talked a little bit about visuals from the Marketplace, under what circumstances would you then endeavor to create your own custom visual, or have we pretty much ruled that out at this point?
Kevin: No, I think there are reasons to create your own custom visuals. The biggest thing is you have a need that needs filled. We see some really useful custom visuals in the Marketplace like the Synoptic Panel or continuing to go with OKViz, I like a lot of the stuff they do. Sparklines where you don’t have really good sparkline capabilities in Power BI built in, there’s line charts that you can futz with, but just having a real sparkline, drop that in, is useful. There are other visuals that aren’t available in Power BI, but could be useful and even within the realm of visuals, I kind of mocked the idea of creating your own column chart, but if you want to go full-Tufte and I don’t recommend people ever go full-Tufte, but you could have the Tufte-style visuals where, okay, your column charts are now a single line with a little circle somewhere and your box and whisker plot is, again, a single line with like a couple circles. And that’s not something that you could recreate within Power BI natively, but I could see creating some custom visuals if you are full-on dedicated to that art.
Eugene: It’s worth saying that the amount of effort is similar to starting an open source project or something like that. So, I think for anything that’s a generic need, I definitely wouldn’t do it unless you’re prepared to now have some sort of open source project or some similar level of effort. I think for a business where it makes the most sense is you’ve got some kind of visualization that’s core and essential to your business. So the best example that comes to mind is I remember when I was reading through, it’s Information Dashboard Design by Stephen Few, something like that. And he had some examples and one of them was a dashboard from the late 90’s of this big machine on a warehouse floor, and it had all of these pieces of information of like the temperature and stuff going in and all of that kind of stuff. And that’s something that I could see where if that’s critical to your business, being able to succinctly represent that in a single visual could be worth the effort.
Carlos: Sure, particularly if it’s something, yeah, unique to that trade or something that would be easily understood by the people who work there, yeah.
Eugene: Exactly, then you’d throw that up on a monitor or something like that and people have a very manageable way of viewing that. But unless you have a core business need and a unique data display, I just don’t think it’s worth the effort unless now you’re willing to take on the equivalent of an open source kind of project.
Kevin: Yeah, and once you throw it up on the Marketplace here, now people are going to review it and if it doesn’t fit what they want, you start getting bad reviews. But you could also build a company, if your focus is on data visualization, or you are Power BI experts or something, having a good component in the Marketplace is also a way of getting some advertising where people can then go to website and say, “oh, well, these people can do some development skills, obviously, and create a component which is useful. What else can they do?”
Carlos: Right. Interesting. Okay, so I know we touched on security as far as like what’s going out there. Would there be, I guess, a need to check if the visual is phoning home, so to speak and how do we do that?
Carlos: Awesome. Okay, so last thoughts?
Eugene: I think my experience in general with Power BI is you’re just going to have a better time if you try to stay in the lane and use whatever functionality’s built in and only start to go outside of that whenever you have a clear and present need. I’ve run into a lot of heartburn where a customer says, “oh, we need to do it like Stephen Few” and you try to start with that design point and then work your way back to, “okay, does Power BI support sparklines? No. Is there a custom visual? No.” So my advice is start with the way Power BI is designed and then only when necessary, branch out.
Kevin: Yeah, yeah, I would agree. I would not get too locked in to “this is what visualization expert X says” and instead focus more on trying to create a coherent, useful story for your end consumer. To that extent, that means that, “oh, it’s not going to look exactly like Tufte or Few or Nussbaumer Knaflic’s ideas” would perfectly be in reality. But this is a practical implementation of a product and you kind of have to live within the realm of what the product offers. There are some really useful extensions to the product and ways to give people better ways to slice or filter your information. Bullet charts came up earlier in a joking manner, but bullet charts are useful. I think they’re informative, detailed charts that when deployed in the right way are going to be a bit more powerful and getting that marginal benefit in power could be worth it in the right cases, and it could be the difference between a dashboard which is okay and a dashboard which is good.
Carlos: Right, and that’s what it always comes back down to. As fancy as we’d like to make these look, our users need to be able to understand the data and if we’re not helping them do that, then they’ll just go back and start asking for data dumps. And they’ll figure it out, right?
Kevin: Exactly, just give it to me in Excel.
Carlos: Yeah, exactly. Okay, well, compañeros, that’s going to do it for today’s episode. Thanks for tagging along with us today. If you want to reach out to us on social media, you can do so in the following ways. Angela?
Angela: On Twitter, I am sqlswimmer, or on LinkedIn you can get me at AngelaHenryDBA.
Eugene: Yeah, I’m on Twitter at sqlgene. I blog at sqlgene.com and if you go to my blog you can sign up for my weekly newsletters, so you can keep up with all of the changes with Power BI.
Kevin: You can find me in the AppSource Marketplace.
Carlos: Very nice. I want to see that. And compañeros, you can reach out to us. We are always interested in what you have to say and if there are topics you think we should be talking about, please let us know. And we’ll see you on the SQL Trail.