In this episode of the SQL Data Partners podcast I sit with Patrick LeBlanc during SQL Saturday RVA. We chat about Microsoft PowerBI. PowerBI is a business intelligence tool that easily connects disparate data sources to create dashboards. Patrick answers my questions about the application and also explains why he thinks that PowerBI has grown in popularity so quickly.
He reveals what he knows about PowerBI and the Microsoft data stack, such as:
- the latest free features of PowerBI
- how organizations are using the free version to get results
- the security features of PowerBI pro you don’t want to ignore
- what to do BEFORE you download PowerBI Desktop
- when PowerBI isn’t the best solution for your organization
Finally, Patrick unveils the underrated Microsoft tool he swears by and the superpower that drives his kids crazy.
Patrick LeBlanc is a Data Platforms Solution Architect and Technology Solutions Professional at Microsoft. He speaks and trains regularly on data virtualization topics at SQL Saturday events and on Microsoft Virtual Academy. He’s also contributed to several books on business intelligence topics: Applied Microsoft Business Intelligence and Microsoft SQL Server 2012 Bible. He’s a father of two out of Alpharetta, Georgia.
Transcription: Power BI
*Untranscribed introductory portion*
Carlos: Patrick, thanks for being here! Welcome to Richmond.
Patrick: I’m happy. It’s beautiful weather today.
Carlos: Yes! We’re glad to have you. Thanks for coming up. So we want to be talking a little bit about Power BI. It’s very new, it came out fourth quarter of 2015, right? So it’s brand spankin’ new. Take us through just the high level. When we talk about Power BI, what are we talking about?
Patrick: So you know what, Power BI is Microsoft’s end-all-be-all complete business intelligence solution. It’s not just the tools, not just the product, it’s a complete solution.
Carlos: So not just the reporting like dashboard, pre-dashboard requirement?
Patrick: Not at all. You can do everything, right? From soup to nuts.
Carlos: So everything? So I have an Excel document, right, and I have a database. Then I want to start giving my users reports on that data.
Patrick: So think about traditional data warehousing, right? So if I’m a traditional person, if I’m doing a traditional data warehouse, the steps are that I need to transform my data, I need to extract my data, I need to massage it, and I load it somewhere, right? They require dedicated IT resources, right? You need some guys that are really sharp out there to come in. ETL folks. But with Power BI, I can have these disparate data sources, I can use the desktop, just a single desktop application to bring that data in and massage it. I can become intimate with the data, right? I can create these relationships across these disparate data sources and then I can start reporting and creating dashboards directly on top of it without IT guys. I don’t need those guys anymore.
Carlos: Interesting. So, I was in Portland last year and heard from James Phillip. It was the VP of Business Intelligence offerings at Microsoft and he said a couple of interesting things. He said, one, that the move to the cloud is inevitable, and of course PowerBI is kind of based in the cloud, right? They have that service. Although there’s a desktop version which we can talk about. And he also suggested that, as you mentioned, that PowerBI would be the glue of sorts that would help these different reporting needs. His example was that people are already using the cloud for different things. We talked a little bit about it. Like Google analytics, it was a perfect example. You’re not using Google analytics. It’s a cloud solution, lots of people are using that, but then you wanna take Google analytics and compare that with Twitter data, right? So you have two cloud applications, disparate data sources, right? Are you saying is that how people are primarily using PowerBI?
Patrick: So I’m working the education space and so the home page of an institution is critical, right? Especially when you think about a college. People are clicking about on that web page. And so if I just enable Google analytics, I can go to the PowerBI and go to PowerBI.com, put in my credentials for Google Analytics, and guess what you get? A dataset, reports, and dashboards automatically. You don’t have to anything. You don’t have to know anything about reporting, dashboarding. It just dumps it out there for you. That’s one means. But if you want to mingle those two data sources, use the desktop. And you can actually connect to it, it gives you a dataset. You can pull in JSON files from Twitter, because that’s what Twitter exposes, right? Connect them up and start correlating that data. Even better, I can take some of my own data. And so imagine I got people clicking around on that site. Maybe I want to see if admissions correlates to how many people actually are clicking and submitting applications. I can almost do that in real time using PowerBI. So I’m definitely seeing, you know, adoption, the glue. But it’s much much more than that. Much more than that.
Carlos: Okay, so now that kinda almost sounds like, “Hey, IT folks. You’re gonna start having shadow IT!” More of that, right? The marketing folks are using Google analytics and whatever. So then how, I’m an IT person responsible, maybe I’m not a C-level but I’m a small team and I want to take care of my people but I also don’t want them, you know, just going off without me necessarily so what do I need to do to prepare my environment so I can say, “You know what, we’re going to support you with PowerBI.” What do we need to do?
Patrick: So the biggest thing, and I think we were talking about this earlier, you guys, the IT staff, have to be a little more agile. I don’t think they’re going to want you to know the answer, because you don’t set up Google analytics, you don’t know anything about Google analytics. You have to be a little more antsy and have a listening ear. You have to be a little more agile and go, “Let’s take a look and see what this is.” So in regards to those type of data sources, it’s up to you on how your IT staff is going to handle that. But if we’re talking about your data sources that you hang on to, this is where I think the disconnect happens. I can’t tell you, I was at one university. They separated the business people from the IT people, because the business people want access to the data because they have Power BI now, so I can connect and I can pull it in. And so the solution, and this is my Patrick solution is, that so if I’m the IT staff and I have a guy who says, “Hey, Patrick, I want to have access to all the enrollment data of the university.” I don’t want to give that guy access to it. I’m gonna go, “What are you trying to do?” Before you download PowerBI desktop, go, “What are you trying to do?” He says, “I’m trying to do this.” So I say, “I’m going to create a view for you, and I’m going to give you access to that view.” IT peole are going to have to be a little bit supportive of what these gusy are gonna do, because this is a new paradigm that we’re in. PowerBI is not the only desktop application that we have out there that allows the end users to connect.
Carlos: So that’s interesting. That idea as data professionals is that we generally will have the role of being the data stewards, right? So we mentioned so like the marketing team has the Google analytics, right? But they’re setting that up. I was in a meeting today, as a matter of fact, where I get with the marketing folks and they’re like, “Our Google analytics numbers are off. Will you tell me that? I can’t correlate that with my data internally.” And I’m like, “What do you want me to do about it,” right?
Patrick: I can’t give you any pixie dust to sprinkle over that, but what I would tell you is that you gotta just listen and take a look, because they’re probably right in something in the semantic layer. So you can create this abstracted view in PowerBI. And maybe they messed up the calculation, because when you pull that data in from Google analytics to the desktop, you can mess it up a little bit, right? Maybe just take a look and go, “well, maybe look at your calculation and maybe this is not quite right.” I can’t tell you that you’re going to have the answers for those guys, because you don’t know that data. You’re not intimate with that data. So just a listening ear.
Carlos: So I’m a database guy, right? I feel that PowerBI is in the reporting space, while a lot of jobs now have this like, “We want you to be the DBA and the data architect for our warehouse.” They’re really two different camps. Even from a PASS perspective, right? They have PASS Summit for kind of the engine and they have the PASS Analytics conference. So who’s going to be taking care of that?
Patrick: If you’re in a big enough shop, you’ll have those separation of duties. If you have a big enough shop, there’s going to be a reporting team and there are going to be the DBAs. But unfortunately, most shops these days there’s a DBA. The DBA is the data warehouse guy and the reporting guy or girl and so you have to assume multiple hats. It’s another hat that you’re going to have to wear. Unfortunately, it’s the truth. So even at Microsoft…so my role at Microsoft is as a Data Platform Solution Architect, I cover the engine and I cover the analytics. I cover the entire gamut of the data platform. And so I think it’s just the new role, a new part of the IT department.
Carlos: One thing I think is interesting, as listeners of this show know, that if you work in IT you’ve made the commitment to learn new things. If you don’t have the initiative of learning, we’ve said find another profession. So it sounds like this is among that. So let’s talk about the pieces there, so there’s a web component. Now, I thought if I use the web it’s $9 a month per user. But no, you’re telling me there’s a different configuration.
Patrick: No, you can actually use this for free. So desktop? Zero dollars. You can download the desktop for free. Nobody pays for the desktop ever, that’s what I was told. I don’t know if that’s going to change, but at least for now. So download it now. For the service, there is a paid component to the service. But the paid component comes in when you want to live query or direct access data that is on premises. When I’m not moving any data to the cloud. If I just want to issue a query, go down on premises and query that data. That’s like the true, that’s when you go from free to paid. Now there’s other aspects of the pro license that will require pay. If you want collaboration, if you want to create what you call a content pack, where if I give you this content pack all this pre-built collateral it automatically goes into the workspace. So there’s a lot of different aspects but when we’re talking about the pro version, think about, “I’m live querying.” If you’re OK with importing your data to the cloud, you can absolutely do all this for free.
Carlos: In our example, I have Google analytics, I have Twitter data…
Patrick: All free. If you go to the service, sign up, sign in today, and you can go click Google Analytics in the service, put your credentials in, and zero dollars. It’s called freemium.
Patrick: That’s a very common model. It’s freemium. It’s absolutely free.
Carlos: So is that like, from an adoption perspective, how is –
Carlos: I think he quoted James again and I’m quoting James from last year: “one hundred thousand new sign-ups a day” or such thing?
Patrick: Insane. I go talk to so many customers and they’re like, “You have to see what we’re doing with PowerBI.” And I’m like, “Are you guys paying for it?” No, no, no, no. If you import, and you deploy, you can do it for free. Let me show you something else, right? The best part of the precon that I’m doing tomorrow. Let me show you an enterprise BI solution.
Carlos: So take us through that. If I don’t want my stuff in the cloud or maybe let’s compare that to a Tableau or an Oracle, so what are the components that I would need?
Patrick: Take any in-memory product. Any desktop product like even Excel. Even Excel. Access, I’m going back. Think about Access. These are data silos That’s what I call them, because I’ve literally imported that data into that model and then what else can I connect to it, right? How many other things connect to PowerBI desktop and Tableau if I import that data and leverage the model that I build there. Not many things, okay? Because it’s in the silo of Tableau, right? So I can’t get a connection from reporting services to Tableau. I can’t create that connection from the PowerBI desktop to Tableau. If I create a semantic layer on the server level using SQL Server analysis service or if I let it lay in the database, I can use any product to connect to it. But if I want a true enterprise solution where let’s say you’re the dean of a college and I’m a professor. Okay? When I sign in and look at the dashboard, should we see the same thing?
Carlos: I would think not.
Patrick: I would think not. The dean has the whole view. I just see my courses that I teach. If I use the Desktop for that or I use Tableau for that, I can’t control that security at that granular level. If I use analysis services I can dynamically control that so when I sign in from the services and say, “Hey, this is Patrick”, it passes my security context down, it runs that query, and filters out the data based on that login user. It’s a Pro feature.
Carlos: That’s a pro feature. To do the filtering that’s a pro feature.
Patrick: [laughing] That’s a pro feature.
Carlos: [laughing] Patrick wants you to pay the nine dollars a month, ladies and gentlemen.
Patrick: It’s a pro feature. It’s an enterprise solution, though, right? Because or you create a dashboard for the dean, a dashboard for professor one, professor two, professor three, and you maintain them all. Who’s going to do that? Is that worth the $9 a month?
Carlos: Yeah, totally. At that point there’s enough money to do that.
Patrick: Our solution, PowerBI, it’s more than just a desktop application. And it’s not just PowerBI. It’s an entire BI stack, right? They complement each other so well and so that’s why it eclipses anything out there.
Carlos: So we talked about how most people are using the free version, right? People are adopting the cloud and having their data stay out at the Microsoft base and they’re ok with it?
Patrick: We have the most secure cloud in the world. I wake up every morning and Microsoft sends me these kool-aid packets and I make it and I drink it. [laughing]
Carlos: [laughing] Azure…yummy….it’s so secure…
Carlos: So that brings up another point, right? You work for Microsoft and we talked a little bit about this, right? You have internal documentation, you have access to the people making this stuff, right? I don’t work for Microsoft. I am trying to support my team. I am a good IT soldier. What skills or where am I going to get the training or the knowledge that I need to support them?
Patrick: That’s a good question. [laughing] You know, you are a SQL Server guy. The first place I would start, honestly, I think this is one of the best user communities in the world.
Carlos: Oh no question.
Patrick: Open up a web browser, go to Bing. Not Google, Bing. [laughing]
Carlos: [laughing] Yes, this Kool-Aid is very good, Patrick, thank you.
Patrick: [laughing] Type your question in and I guarantee you’ll find the answer. Just kidding, alright? You’ll find great blog posts. So, one place you can go is Microsoft Virtual Academy. I kid, but it has tons and tons of free content out there. Actually I have a video out there myself on Analysis Services. Eight hours, free. Sign in, log in, use it. I’m not kidding you, phenomenal. Or TechNet. One place that people don’t read, and we were talking about this earlier, there are a couple of stations that a friend of mine said that they said we shouldn’t put books online. But for SQL Server, Books Online has a ton, a ton, of information for you to learn and that you can disseminate, you know, and share.
Carlos: So another question I have is, so PowerBI is the new hot and shiny, right? Came out in the 4th quarter. We’re sitting here in early February. When I look at the MSDN document diagrams, and we’ll put this link on the website, there are a number of reporting options in the Microsoft space. Excel, PowerPivot, data mining for Excel, SharePoint, the BI center site, SQL Server Analysis Services with Tabular and Multidimensional models, right? Then there’s In-Memory Tabular. Then there’s performance point which at one point was going to save the world with dashboards and things right? So PowerBI is so new, is just released, what is the adoption? If I’m the IT leader, why am I going to jump on the PowerBI bandwagon when there’s this trail of reporting capabilities in the Microsoft history that I’m going to have to spend money to get people trained to get people using even if they’re not trained, I now have an organization that I have to support. And if google analytics goes down, it becomes my problem.
Patrick: So, I’m going to add a few more to your trail. You forgot about Reporting Services. And in 2016, last April, we bought another product called DataZen and we integrated DataZen into Reporting Services in 2016. So not only in Reporting Services do you have the Paginator Reports, but you can create KPIs and create mobile reports that can be consumed in the PowerBI native application. Alright? So not only do we have all that over stuff. We have more stuff. But the reason you would take PowerBI? Performance point. It’s a great product. Wow, for it’s day, for it’s time, but for it to truly compete, for Microsoft to compete, for your organization to compete, to stay on the edge, you gotta go with PowerBI. And the reason is, think about you’ve got two landscapes right now. You’ve got the cloud and you’ve have on-premises, okay? Microsoft’s goal is to give you parity between the cloud and on-premises. And so whatever you can do in the cloud, you’re going to be able to do on premises. Whatever you can do on premises you’ll be able to do in the cloud. Okay, so I take a PowerBI Desktop, not only will I be able to publish it to the web, but I’ll be able to publish it to on premises. It’s on the roadmap. Go look, it’s out there on the site.
Carlos: I think I missed that.
Patrick: I can take a Reporting Services report and pin it to a PowerBI dashboard. Fully integrated capabilities across the cloud and on premises. So, if you want to stay archaic, I’m going to be honest with you.
Carlos: Kind of like upgrading to XP, right, or Server 2003?
Patrick: Like going from Vista to 2010. [laughing] Or Windows ME? Remember Windows ME? So, Performance Point is a great product. But at some point you’re going to reach a brick wall where you’re not going to be able to do certain things. So there’s not a lot of development going on around Performance Point these days. So why do I go to PowerBI? I think that’s the question you initially asked, right?
Carlos: So am I going to be able to hang my hat and say, “In two years, PowerBI is going to be around.”
Patrick: It’s going to be around.
Carlos: There’s not going to be like PowerSuperBI?
Patrick: I’m not going to say that. I obviously can’t say that. But if you think about two years ago when we released PowerBI version 1. It was horrible. And I’m sure that’s a strong word but it just wasn’t up to par with what we expected it to be. And now with this version of PowerBI, the adoption is mass. It’s everywhere. Everywhere you go, people are talking about PowerBI, you know? So if you want to use a Microsoft BI landscape and you want to stay up to date on it, you have to go to PowerBI. It’s just not a choice.
Carlos: Very good. So as we wrap up the PowerBI discussion, anything else that people should be mindful of or be considering of?
Patrick: Before starting PowerBI?
Carlos: Before starting PowerBI.
Patrick: Know what you’re going to do first. Know what you’re going to do.
Carlos: Start with the end in mind.
Patrick: Don’t download the Desktop until you know what you’re going to do. And I say that not as a bad thing, don’t download the desktop. The desktop is so phenomenal then… and I’m taking my Microsoft hat off and putting the Kool-aid away…. The first time I downloaded it, I had a project that I was going to work on and I ended up doing three million things before I got done. Because there were so many things and capabilities in it.
Carlos: Shiny new object, you want to play around with it.
Patrick: It was like someone gave me a shiny nickel and I was ready to go. So kind of know what you’re gonna do before you download it, because once you get it downloaded and start playing with it you’re going to get lost in all the capabilities and features. So kind of come up with a game plan. That’s my recommendation.
Carlos: Areas where PowerBI might not be the best solution?
Patrick: Operational reporting. True operational reporting. PowerBI is high level, aggregated data. Now it can show very detailed data, right? But if you need traditional pixel-perfect operational reports that are going to be delivered in somebody’s email box as a PDF? You need some other solution. Reporting Services. PowerBI is not meant for doing things like that. PowerBI is ad-hoc reporting, on-demand reporting. Not operational tables and columns. It can do that capability, but that’s not truly what it’s meant for.
Carlos: That’s interesting, because that kind of goes back to our point as data stewards is that we need to steer that in the sense that yes, you can use your PowerBI, but you’re using that for reporting. Like, I’m not going to be responsible for keeping that up. It’s yours, I’ll give you that data or you’ve download it from the web, but it’s yours. If you want a report from me that tells you how many website visitors we have? Ok, I’ll use Reporting Services and I’ll email that to you on a subscription or whatever and you can do that.
Patrick: That’s right.
Carlos: Well Patrick, let’s do SQL Family. So we have a couple of questions where I’m going to get to know you a little bit better. So we’ve been talking about tools. I like to know how other people work. A very successful person like yourself, you’ve been doing lots of different things. I want to know what your favorite tool is.
Patrick: From Microsoft?
Carlos: To get things done in your work. What is your favorite tool, why do you use it, and what does it do for you?
Patrick: What’s my favorite tool for getting work done? Hmm. A computer.
Carlos: [laughing] Okay, an application maybe. Let’s narrow that down just a little bit.
Patrick: [laughing] You know, I don’t know. I use so many different tools to get work done. Honestly, it’s Outlook.
Carlos: There you go. Okay, so any special tips or do you have any add-ins that you use?
Patrick: Nope. I get a lot of junk mail. So it’s funny, we just had a new hire and he’s like, “Man…”
Carlos: You like Clutter? Is that what you’re telling me?
Patrick: Nope, not Clutter. I don’t use Clutter. But we get inundated. We just get all these DLLs and everything and basically the sort. Sort and you can see, “Hey, this person is not… “ delete, delete. And then have a rule. If I’m on the CC line, it does not show up in my inbox. It goes to a folder that’s a lot of junk. If my name is not in the To line, it goes to a CC. So I get a lot of work done then with Outlook, I really do.
Carlos: An Outlook hack. Very good. Now I will say that I started using a program called Boomerang. And that’s been very nice. Now I’m a little bit more needy than other people, I guess, because I’m always like requesting podcast interviews, right? And if they don’t respond to me at a certain time Boomerang can set that up so that it will simply let me know. It will come back to the top of my inbox and say, “They never got back with you.”
Patrick: Nice, I’ll have to look at that. And you know what else? SQL Server data tools.
Carlos: So, as a Microsoft person can I complain a little bit?
Patrick: [laughing] Not that I can answer, but yeah.
Carlos: So it seems that it’s kind of the same product but they made SQL Server data tools, right? So that there’s one for the database for like the installs and SQL Server Management Studio and then you’ve got the Visual Studio implementation.
Patrick: Not anymore. It’s all one. 2015, you install it, and everything’s there.
Carlos: See I’m using 2013 I guess.
Patrick: In 2015 all of them are there now and that’s why it makes me so productive. So I can even do Management Studio type stuff in SQL Server Data Tools now. So I don’t even have to open up Management Studio anymore.
Carlos: Okay, you just inherited a million bucks. What are you going to do with it?
Patrick: [laughing] Put it in the bank.
Carlos: Put it in the bank.
Patrick: I have two kids that gotta go to college.
Carlos: You know, it’s interesting. So when I came up with this question, I thought it would be interesting just to hear different people’s scenarios. And the most common? Pay off my house and save it.
Patrick: I have two kids that gotta go to college.
Carlos: We have our professionals here. We’re not for playing the lotto much.
Carlos: So we haven’t talked about it, that you’ve come from Pragmatic Works, you’re working at Microsoft, you’ve been an MVP, right? You’ve had a plethora of different experiences. What’s the best career advice that you’ve received?
Patrick: Listen more and talk less.
Patrick: Yep. Steve Jones told me that. He said, “You’re a great presenter but you need to talk less and listen more when people talk to you.”
Carlos: Steve’s a great guy. Good advice. One last question before you go. Okay, if you could have one superhero power, what would it be and why do you want it?
Patrick: I wanna be smart. My kids and I have this conversation. They’re go, “why wouldn’t you want to fly or something?” I say that I don’t want to fly. I want to be the smartest person ever.
Carlos: Like a Doctor Xavier or a Doctor X?
Patrick: Doctor X. There you go, that’s me. But I want to be able to walk though.
Carlos: [laughing] There is that one side effect. Patrick Leblanc, thank you so much for being on the program.
Patrick: Well thank you for having me.