From Data Chaos to Data Clarity: How to Navigate a Successful PowerBI Implementation

[00:00:00.000] All right. Well, it’s the End of the Week podcast. Thank you everyone for joining us. Thank you, Jason Chance, for joining us. Thank you for Kirsten Murgle joining us. Today, we’re going to talk about reporting Power BI and all that exciting stuff. So maybe just to get kicked off, I’d like to ask the question, how do these types of projects, these reporting, these Power BI projects, typically start?

[00:00:26.940] Yeah. So what I’ve seen in the past is working with clients is an individual in an organization is tasked with solving a problem. And it’s usually something pretty specific that says, Hey, we need to report sales by product and by customer, figure it out. And it gets handed to somebody who may or may not know what they’re doing. And Power BI is a relatively easy tool to learn and get started with. And so sometimes that is picked up and people take it and run with it. But what we often see is they maybe solve that one problem well, but don’t think about the future and how they can set themselves up for not just solving that one question, but the next 10 questions that are going to come down the line. So that’s why, Kristin, I’m curious to see if that matches a little bit with your experience.

[00:01:25.750] Yes, it match because a lot A lot of manager want to have control of their productivity, their process, and they ask for KPIs or whatever. And we recently added Power BI to our tools. And in replacement of Tableau. And yeah, there’s a lot of demand for tools to follow the productivity of the processes. And there’s And a lot of teams at my workplace. And a lot of teams start from their little cells. And I think as a business, you need to have guidelines with these tools because it can become a monster. Rapidly, you need to get the data and have consistency in your data. So where does your data come from for the ? We have SAP. We have Astus that create the schedule for the busses. We have a lot of application software where the data is stored. But we have teams that consolidate the data in cubes.

[00:02:57.260] Okay, great.

[00:02:58.220] So lucky for us.

[00:03:00.630] Yeah, that is fantastic. That means that you’ve got a whole team devoted to organizing the data, centralizing the data, and working on the consistency and reliability of it. Because for some organizations, a lot of organizations that go down this road are very small. It’s maybe one person in IT and the CEO says, Hey, IT guy, figure this out, write me some reports, and they just have no idea. And so they don’t have a team of people thinking about data consistency. So you’re thinking about, how do I make this data reusable? And one of the biggest pitfalls we see is, oh, I create a small data set to make this sales report. And if I had just done a little bit of thinking and planning, maybe I could have incorporated a little bit more information. And now this small data set becomes a little bit bigger, but it becomes much more useful. And not just to solve one problem, to solve many problems.

[00:03:57.770] Yeah. And I would say, you said, well, Well, the manager might ask the IT team to do it. And that’s if you’re lucky that you’re actually asking your IT manager, IT director to think through a strategy. Sometimes it’s just going to be asking your solutions analyst or someone working at the firm and say, Hey, I heard that Power BI is pretty easy to work with. Could you just build me this report?

[00:04:19.700] Hey, you’re an accountant. You’re good with computers. Why don’t you build me this report in Power BI? We see that all the time.

[00:04:25.910] Exactly. Which I think is a misconception on a lot of those low code/no code tools where it’s easy to pick up, but it’s difficult to scale, to your point.

[00:04:38.290] I had a class last year at the end of the year, and it was a 20-hour class, which we view Power Query, Power Private, and Power BI. So 6 hours Power Query, Power Private, and the rest for Power BI. I practice a bit during this period, but I had other project to do, and I just get back into it two weeks ago. And it’s not that easy to get into the tool. I rewatch my recorded classes, formation, and it’s okay. I can do simple things, but to do more dig into data things, it will take me a bit more time time. And the modeling of the data in the Power BI, too, you need to… There are some tricks you need to know just to not lose a lot of time.

[00:05:42.240] Right. And make it easier for people to use because you might not be the only person building a report.

[00:05:46.810] No.

[00:05:47.450] So you might build a data set for your report, and then someone else says, Oh, I’m going to take that data set. I’m going to build my own visuals on top of it. And if you don’t structure the data set in a certain way, it can make it very difficult for someone to understand even what’s going on and to navigate the data.

[00:06:01.700] Even yourself, when you’re building it, you’re just like…

[00:06:04.210] Yeah, and you come back six months ago, you think, Wait, what was I thinking?

[00:06:05.710] Why did they call it that? It needs to be really clear when you build a model.

[00:06:09.650] Exactly. I’m here a lot of build the data set, different reports, all that stuff. Maybe for audience, it’s not as versed in these different topics. When you say when you’re building a data set, what does that mean? Yeah, I think so.

[00:06:22.030] We can put up a good visual on the screen about the Power BI architecture. And the nice thing about Power BI is you can use many different sources. So as you look at this visual on the left-hand side, you’ll see it can connect to hundreds of data sources. And you also see on the left-hand side that you’re using Power BI desktop, and that’s the main end user tool to create data sets and create reports. And then everything gets pushed into the Power BI service, which is a cloud-based service. And that’s where the report lives. That’s where the report runs. And that’s where the report can also refresh. And that’s another thing that small companies else forget or don’t really understand. And they think that they have to refresh the report in Power BI desktop and publish it every day to the Power BI service and just republish it every day. And that’s how they refresh the report, which would actually work. But it’s much better if you can just put the report in Power BI service and schedule the data set to refresh. But that means that that data has to be accessible to the Power BI service.

[00:07:26.710] So it has to be an online source or in SharePoint, in a web page or wherever it comes from, but it has to be accessible online. Or also, if you look at the graphic, if you’re going to use on-premise data sources, so in your own data center in your company, you have to set up a Power BI gateway so that the Power BI service in the cloud can talk to your databases on premise and pull data from there and load it into data sets. So that’s a lot for an accountant, right? Yes. To handle to set up if it’s not very simple and straightforward. And on top of that, so once you get the Power BI service, you get the data and the initial report set up. Now that’s how everyone accesses those reports, either through a mobile device, tablet, computer, whatever it is. And folks can just hop on the Power BI service and generate their own visuals if they have the right access to develop their own dashboard, to develop their own reports based on maybe the data that you set up and provided.

[00:08:27.120] And as you said, you need to have a license because if not, you need to have a license to access the Power BI application. If not, and for us, it’s the case, not every user has access. So you need to generate a PDF, PowerPoint, whatever, other documents to share. To distribute it. Yeah, to distribute the information. And you cannot dig into the…

[00:09:02.440] Pdf document. Yeah, that’s it.

[00:09:04.030] It’s just a report. So until every user has the license, they cannot view the report.

[00:09:14.720] Interact with it. If I take a bit of a step back and I try to recap what you said, is the idea is that Microsoft and these other companies that push these reporting tools out in a way that they’re saying, Hey, this is very easy to adapt and this is very easy to deploy, is true to a certain extent, but you can’t necessarily skip the step of defining the strategy in which you want to deploy this in your organization and understand what your data structure is. Once that is done, then that is true that someone could easily add a field, change a visual, do these different elements. But to me, one thing that sometimes people don’t necessarily understand when they don’t have the knowledge with these reporting tools is that there’s really the data sets, the data structure, the data layer, and then there’s all the individual reports. The data sets, the data structure is something that still remains an IT function to this day to a certain extent. Our developers function or someone that’s quite technical versus then what you can give to your accountant, what you can give to the other team members is creating their reporting and doing those different types of elements.

[00:10:27.240] If you were to If you had the chance or the opportunity to speak to anyone saying, Hey, if you want to get invested in these technologies or you want to think about starting reporting, we talked about the pitfall. What are the things that you should do or how should you plan to go about it? And how should you even put governance on these type of technologies?

[00:10:49.570] So the best thing to do is to usually start out how people start out, where they’re trying to solve one problem, or they’re focused on one set of data. For a lot of organizations that we work with, that’s typically sales. That tends to be some of the most important data that they want to crack open first. Great. Let’s get the sales data into the Power BI so we can analyze it. But then the question What I have to ask is, what granularity do we need the data? Do we need to go down to the day? If I’m at a retail location, do I need the time of day of each sale? Am I going to that level? What about product level detail? Do I go down the product itself? Or do I only analyze it at the product group? What about the customer? Do I analyze each individual customer or just maybe groups of customers or customer tiers? But you have to answer all those questions of how am I going to use the data to solve the question that’s in front of me, but also how am I going to solve the next question that comes in front of me?

[00:11:49.680] Because a lot of times you’ll say, I just need a sales report for product group and customer tier. That’s all I need. And if you just build the data at that level, then that’s all that’s there. That’s all the level of detail that you have. And as soon as someone says, Oh, that number is weird on that product group, let’s dive into that and figure out why that number is weird. You’re like, Well, the data… I can’t dive into that because the data stops right there. But if you’ve built it to have that level of detail and you’re just showing it rolled up at the product group level, great. Now you can just drill in, look at the details, and be able to solve that next question that comes in. Yeah, that’s the approach I would take. But then also you have to think about, All right, Now I’ve set this up. What is now dependent on this data that I’ve created? Maybe I’m the first one to write a report, but now maybe there’s other salespeople who are using a report. Maybe there’s other visuals that I don’t know someone else created because they have a Power BI license.

[00:12:45.610] And now we’ve got this little ecosystem that’s now dependent on the data that I created. What’s the maintenance involved in this? And who’s monitoring this? If this is a critical function, that salesperson goes out to a customer and they don’t have up to date data, can they do their job? Does it impact them directly? That means that you really need to monitor this. It has to be someone’s responsibility to know that the data refreshed, it was successful, and it’s been validated. And that’s something that’s often skipped is the maintenance involved and who’s taking that on.

[00:13:19.420] And also, if you are building a set of data for cells, and maybe another department use the same data source but manipulated differently, it can give two different results for the same desire to show the same results.

[00:13:44.960] Yeah. We had this, and this is one of the worst things that you can do is to have two sources of the truth. And then you say, All right, what was my sales in July? Oh, it was this number. Oh, I got this number. And as soon as you have two different numbers for the sales in July, why people don’t trust it. Yeah, that’s it. Right. And so as soon as they don’t trust it, you’ve just lost their trust. And it takes a while to get that back and be like, no, the data is good now. We validated it. Yeah. And we’ve definitely had that happen before. So you have to be very careful about having multiple sources of the truth.

[00:14:17.560] And I think that rolls into an interesting point when we always talk about change management when deploying technology. And to your point, if people start looking at reports and they’re getting different Different sources of truth, different numbers for a source of truth, you lose confidence in the technology you’re deploying. So by trying to undermine the effort, the strategy to deploy these reports, you might actually be losing your opportunity of doing a proper deployment and having people adopt it. What I’ve seen, and Jason, please talk to me about that, but what I’ve seen is people tend to just use Power BI report, extract the data, and then just run their Excel spreadsheets it’s next to it because they’re going to try to see how they could get that number or how they’re able to get the additional information they want. And then you’re not getting user adoption, which you want to do by having this tool deployed. So as we think through that, what would you say is a way to solve for that? Or what is something that people should just think about whenever ensuring one source of truth?

[00:15:25.760] Yes. The main thing is to have someone take ownership of it. And So if it’s a sales data set, someone from sales needs to own that data and own that it’s correct, and they have to take that on. And that means that as you’re building it, they have to test it, and you have to test it different ways and slice it and dice it different ways and make sure those slices match exactly what you expect. And then does it need to tie out to something in accounting? So we might be pulling sales data from a different source than our accounting data that hits our general ledger. Those two have got tie out because as soon as you put a number up there in front of the CFO that’s different than the number that’s in the general ledger, the CFO is going to be like, That’s garbage. It doesn’t match. My number is correct. And that’s the end of discussion, I Would you suggest to think of a big picture structure of like, workspace and layers of workspace?

[00:16:25.730] I don’t know. I watched a couple of videos about that. And You can have layers of workspace, one that is more back-end, like test, development, everything, or data set that is shared after that to another layer, more for the user.

[00:16:47.010] Yeah, absolutely. So what you’re referring to is specifically is Power BI workspaces, which you can think of them as containers or folders or areas where you put reports and data sets, and then you can control access to Each workspace individually. And so a great example you just gave is for developers, when we’re developing these, we create a development workspace. And we’re the only ones that get in there because we’re just trying stuff out and testing it out. And we don’t want an end user to stumble upon a development report and the number is not good, and the data is not good. So that’s a great example. And then creating a test workspace is a fantastic practice. Very few people do it, but it’s a great idea because as you’re rolling out changes, you need to do that validation and do that ownership and say that, yes, this report is good. The next version of this report is good. We’re going to test it fully, and then we’re going to move it in production. We’re going to inform everyone who’s involved. This change just went into production. So let us know if anything looks strange or whatever, and have that rigor.

[00:17:49.400] But that’s a great use of workspaces in Power BI is to divide up dev, test, and production. For sure.

[00:17:56.500] And I feel like we’re going through the same motions with these no code, local tools as we went through 15, 20 years ago whenever we started building and developing business software more at scale, right? Where you look back 15, 20 years ago and people weren’t testing warehouse management systems before deploying to prod. They were just building them, deploying them, and figuring them out. You tell that to someone today that’s getting into deploying warehouse management systems or ERPs or anything like that, they would say, Well, that’s unconceivable. Yeah, that’s insane to do But we learned. We said, Well, actually, no, this is the framework. You go from dev to test production and even to QA whenever there’s updates and that stuff. Now it’s a given. But because you have integration partners that know this and are able to easily inform end users and end clients that that’s how it should be done, then this isn’t a hurdle or not as much anymore. Versus these low-code/no-code tools whenever you start playing around with them, like Power BI, Power automate, the different suite, because it’s so easy to have access to those tools and play around with it, sometimes we try to take shortcuts.

[00:19:04.490] When we try to take shortcuts, well, that’s when we can, unfortunately, have these challenges. It brings me to a point where sometimes managers don’t understand the effort that’s required to even get these up and running and maintaining them and all that stuff. I think it’s important for end users that are building these reports, and maybe you could talk to that a bit, but to really provide the feedback to your team and provide the feedback to your managers of what is the investment of time that it takes to actually get that done.

[00:19:37.520] Yeah, that’s a lot of effort, I would say, because, yes, it’s a low code, but there’s a lot of technical and specificity that you need to know. By example, if you want to connect, I don’t know if you had the… For sure, maybe you forgot about this because you’re so used to it. But just an For example, when you want to connect to a folder, if you connect to an Excel files on your server on premise, it always add your name in the beginning. So if after that you share it, other people will not have access because of this little specificity, it adds your a name before. So you need to do a web link and put it on SharePoint, by example. And when you do it, you need to erase the last few characters. It’s small things like that that you can lose time in the beginning.

[00:20:50.810] Well, they do those things to make it easy for you to implement it, right? Yeah. And make it seamless. But then when you want to- You need to know it. Yeah, exactly. But then when you want to actually put it out there refresh it on the schedule, if that-christine file is not available, it’s going to fail. That’s it.

[00:21:07.130] So there’s small things like that that you can find in Excel, too. There’s the format, this thing. But yeah.

[00:21:17.530] Yeah. And it’s interesting because when you look at the different tools, we’re using Microsoft as an example here, but you look at different tools for Microsoft and you look at the skill set that’s required to start leveraging those tools, these low-code/no-code tools, have the greatest bandwidth of types of people that we could use it. So someone that has very little knowledge of technology or just programming can actually start using them, but you could go all the way to a deep level of programming in those tools. So the subset of people that could and should interact with that tool is very, very large. If you compare, let’s say, to PowerPoint, to Excel, or to these other tools of Microsoft, which is just less… The audience is much more restrained. Exactly.

[00:22:12.110] Yeah, that can lead to all sorts of challenges. Yeah.

[00:22:15.600] And to be efficient and other things that because I had a class, and it’s to use the DAX language, by example. And it’s not that easy to No, it’s not super intuitive. In the beginning, at least, to get used to these languages, to make calculation, the measure. So, yes, but I will get used to it.

[00:22:44.920] Yeah. And that’s the thing. Dax is the query language you can use within Power BI data sets. It’s super powerful. It’s super powerful. Yeah. But the syntax of it is like nothing else. It’s its own beast. And you have to learn it and just have to understand it.

[00:23:04.320] I would say I think these tools are fantastic. If you think Power BI, if you think Tableau, if you think of all these other reporting tools that we use, they’re great tools. I think sometimes the only problem is that there’s a misperception of how these should be implemented as business and who should take ownership of what step of it. So I think the goal today was really just talk through some of these things, some of these challenges, and just give some insight of what we learned of what Kirstin learned at her job implementing this from an end user point of view. Do you have any last closing remarks?

[00:23:38.470] Well, I would just say that if you are listening from home and your manager has tasked you with getting a report to him by Friday because it’s so easy, and I saw this video online, and why can’t we just have this report by Friday? Maybe just send him a link to this podcast and give him a little dash of reality about what’s really involved and how to do it well. All right.

[00:24:00.600] Well, thank you very much for your time. Thank you. And thanks, everyone, for listening. Have a great week. Have a great week.