Welcome to the Dashboard Effect Podcast. I'm Brick Thompson.
Caleb Ochs:And I'm Caleb Ochs.
Brick Thompson:How's it going, Caleb?
Caleb Ochs:Not too bad. How are you?
Brick Thompson:I'm doing good. Thanks. So today, we're gonna talk about a strategy that PE-owned companies can use to help consolidate data as they're doing acquisitions.
Caleb Ochs:And, you know, all companies could use this. But typically with PE owned and companies, at least the ones we're talking about, they're buying and building.
Brick Thompson:Right. So that buy-and-build strategy often has lots of smaller acquisitions happening, and one of the big challenges then is integration of those smaller companies to into the bigger platform firm.
Caleb Ochs:Right.
Brick Thompson:And we see one of the biggest challenges around that is just getting consolidated reporting quickly. So often, the platform firm has a CFO, he's responsible for reporting to the board, and just to get consolidated financial reporting, becomes a huge lift. I mean, we have one customer, the CFO recently told me that he was logging into 14 different virtual machines to look at their QuickBooks instances to get the reporting to put it together.
Caleb Ochs:Right. Yeah, that's an extreme example, but that's not uncommon, right? Either the CFO or someone at the platform company gets stuck with a nice big chunk of work, or people at the platform company gets stuck with a nice big chunk of work of sending this data out to have it consolidated.
Brick Thompson:Yeah, it's true. And one of the challenges is, So those are all good, great points. First of all. Good job. well, you might say, okay, so if you've got a data warehouse, you've got BI, go ahead and just do ETLs, pull all that data into the data warehouse and do your reporting there. But sometimes it's not that easy. Sometimes the data sources are not clean. Sometimes the the business units, the acquired companies, their processes don't quite line up, and so you don't necessarily want to put it right into the data warehouse. Often there's a plan for bringing those acquired companies onto the platform company's ERP system, and so they don't want to spend necessarily the time or the money to do that data work, what's a different approach.
Caleb Ochs:You know, what we're doing is pulling all of that data just into a data lake, like make it accessible. It's kind of the first step, say, so your CFO doesn't have to go to 14 Different QuickBooks. Ideally, then, if he was savvy enough, could just go to the data lake and do some queries and pull the data that way, probably use an analyst or something like that instead, but at least the data is in one spot, right? At least you've got access to it. You can start doing some exploring across all of those different acquisitions, data sets, before you go after any migrations, or do the heavy lift of proper integration into your BI system.
Brick Thompson:Yeah. Okay. And so moving that data into a data lake is much simpler than doing an ETL, and all of the transforms and so on that you need to get it into a dimensional model that you might already have,
Caleb Ochs:Right? It's just raw data, right? You're just pulling it, dumping it in there. And then you can do all kinds of different things with it. You can start exploring, you know, where are the gaps? Where are the processes that need to be improved? How do different customers map to each other? How do the customer tables map to each other? You can start with some of that exploratory data engineering work, if you want to, but it's also there just to pull quick reports off if you need.
Brick Thompson:Yeah, yeah. So it gives your data analysts the ability just to get at the data much more easily. They don't have to go chase it down at the various business units.
Caleb Ochs:Yeah. And you can set up a scheduled refresh so it's up to date, you know, so you're not having to go out and grab the most recent version. It's just there, up to date ready for you to look at.
Brick Thompson:Yeah. Can you build good BI tools, say in Power BI off of the data lake? Would you do that? Or do you want to wait until you get it into the data warehouse proper?
Caleb Ochs:No, you definitely could, I mean, Power BI would be a good way to just explore it, for sure. You know, you could use queries and get at it with Serverless SQL and stuff. But, you know, if you wanted to do some quick hit analysis on something, pulling it into Power BI, just those raw tables, writing some sort of queries to get into somewhat of a dimensional model, that's a great option. We use that all the time just to debug things or do some exploring around data. It's a lot quicker to pull the raw data into Power BI and then just click around in it than it is to continually write new queries to get at stuff.
Brick Thompson:Yeah, that makes sense. You've got that visual interface, and there's all sorts of transforms you can do through Power Query and all of that. I think one of the advantages this gives us is one of the things that I'll hear from PE backed CEOs is that they don't even need, you know, as they're doing this buy-and-build, they don't necessarily need perfect data to start with. They need to know where the problem areas are. And they sometimes feel like they don't even have that. They're finding out sort of 45 days or 60 days or more, post something happening, post period ending, that there's a problem. And by pulling this data in, say you pull it in daily into the data lake, and even doing some rough reports, even if they're not perfect, something that you know, you would necessarily give to your auditors, they can still give you good directional data about where you should go spend your time.
Caleb Ochs:Yeah, yeah, directionally accurate. That can be a huge win, you know, when you go from seeing nothing to actually being able to see something on a somewhat frequent basis, whether it's daily or weekly, ideally daily, but that gives you the idea of where you need to spend your time. And that's ultimately what we're going for, right?
Brick Thompson:Yeah, exactly. So this kind of setup I've heard referred to as a data lake house. Why do people call it that?
Caleb Ochs:Just because it's data, and it's in a lake, and then you have your house... on top of it. (laughing)
Brick Thompson:Your warehouse(laughing), I figured it was just sort of taking data lake and data warehouse and combining them.
Caleb Ochs:Yeah, that's pretty much all it is.
Brick Thompson:Okay. Yeah. So you can also take data out of the data lake and put it into your dimensional data warehouse, if you want to your dimensional reporting model, right?
Caleb Ochs:Yeah. And that's what we would recommend doing as you're going through this is, do that exploring directly out of the data lake. And once you start finding those reports, I like to call them your pillar reports, the reports that are really important for your business, you need to see every day, they need to be really accurate, those go into your data warehouse, and you can start building out your facts and dimensions that are going to support those types of reporting. And then you can just build on to that as you go. But that should be the process. Start in the data lake. Explore it. Figure out the problems. Figure out the logic. Get it into to a report. Test it for a while. Stick it into your data warehouse, once you've got all the kinks worked out?
Brick Thompson:Yeah. Is there some advantage to having pulled that data into the data lake when you do migrate that subsidiary or that business unit, you migrate them into your main ERP system? Does that data lake then allow you to keep historical data in a good way? Or are you going to figure out how to pull that into the data warehouse as well?
Caleb Ochs:No, you might as well just leave it there, you know? Most of the time, the these platform companies don't go through the process of doing a historical migration. So they they just, you know, they'll pull in like customer list, employees, you know, those dimensional master type data, and then the transactions will just start from go-live. So they don't pull the transactions through the history, maybe open transactions only. But then having that in that data lake, then whatever system that was, if you've copied all of your data, you can completely sunset that thing and not have to worry about bringing it back. If you need to do any analysis, you've you've got access to the data and really cheap storage.
Brick Thompson:Yeah, I like that. You know, I'm realizing as we're talking about this, we never really defined data lake. And it's one of those terms, I think that if you're not technical, it can seem sort of mysterious. How would you explain this to a non technical person? How would you explain this to your mom? Did I just offend your mom (laughs)
Caleb Ochs:No, no, that's good. So on your computer, you've got a file, you got files, right?
Brick Thompson:You've got a directory structure...
Caleb Ochs:Could be your C-drive, could be a shared directory. You can think of it as that, just not on your computer. Just up in the up in the Cloud.
Brick Thompson:Yeah. Okay. So you're dumping files. You're dumping database tables. You're dumping JSON files, XML files, Excel files, CSV files. You basically can put anything into it.
Caleb Ochs:Yeah, videos if you wanted to, whatever, this podcast, you could put whatever you want into that data lake for analysis later. But that's essentially all it is, is just a just a directory, just storage.
Brick Thompson:Okay. And so what are the unique attributes to a data lake that's different from just a file store?
Caleb Ochs:So typically, you structure it, I mean, you could you could just build it like a file store. It's probably not the greatest idea. You probably want some structure to it that's going to be intuitive and work and, you know, that type of thing. But you can also store data in certain formats that make it quicker to read with querying tools. So, Parquet is a popular one. That's just an example, so it's a special file format that you wouldn't be able to just connect to. I'm going to say this, but you probably can with Excel, and definitely can with PowerPivot. But you know, it's special. It's meant for something like that.
Brick Thompson:It's got special tools, special special attributes that allow you to get at the data easily and do exploration and extraction. When we were talking about this before the podcast, you brought up a point that using the data lake is a useful step on your way to getting to an ERP migration. How does that work?
Caleb Ochs:So we've gone through a couple of these with our various clients where they're going through a migration, and we've already built some sort of BI for them. And they always end up using BI to get at and format the data that they need to do the migration. So customer list is a good example. Rather than going and having to pull a customer list from QuickBooks or from Sage or wherever, they can just go to one place, pull the data that they need, get it into the right look, like the right shape, rename the columns doing some aliases, just SQL queries, right? You can't write a SQL query against QuickBooks, so if you have that data, you can write a SQL query against it. Alias your columns. Get it perfect for your upload process into your ERP.
Brick Thompson:Okay. Yeah, that makes sense. What are the things... would speak in favor of doing this strategy of pulling your acquisitions data into a data lake on its way to eventually hopefully being integrated into the ERP and then in the data warehouse?
Caleb Ochs:Yeah, I mean, there's there's a lot of advantages, you know. You could start looking at master data. You could do some data quality stuff. Yeah, you can do all kinds of things once you have it
Brick Thompson:Master Data. That's right. That's a good one. available on it's easy to get to, it just makes it easier, right. But I also think we've kind of mentioned this, but I think it's worth mentioning again, you know, as you're, as these companies are coming on, you're trying to get a handle on how do they track their metrics. You probably have some idea, but not at the level as once they're actually operating under your umbrella. So you're starting to get into the details and realizing that, you know, these two acquisitions you just made don't process data the same way. So having it in one spot where you can do some easier cross referencing allows you to identify those gaps, identify the process differences, identify how the companies are just different, and how they could either become more similar, and get better, you know, kind of take the best of both worlds. But you can do all that through data. It makes it a lot easier. Yeah. So I think we can wrap up, but I just want to kind of re-stress the point that I think that using this strategy of pulling the subsidiaries or business units into a data lake could be critical to helping your integration go more smoothly and more quickly, getting a handle on what these companies are doing that's different from what you want them to do and what the platform is doing. And much less expensively than trying to do a full blown ETL and transforms to put it into the data warehouse reporting dimensional model.
Caleb Ochs:Yeah, you get to realize some of that value right away. You know, ultimately, you might get there, right? And you might spend that money, but you can start getting the value much sooner. And that's what we're all about, right? We just want to get where we can see what's happening quickly. And then we can build onto our data warehouse from there, but you're right.
Brick Thompson:That's right. Okay, I like it. Anything else?
Caleb Ochs:That's it.
Brick Thompson:All right. Thanks, Caleb.
Caleb Ochs:Thanks, Brick.