Raw Data By P3 Adaptive
Small Biz, Big Impact w/ Fran ReedListen Now:
On today’s episode, self-described accountant by trade and a problem solver by passion, Fran Reed of Freedup Data Optics shares how she uses her data bending skills to remove the frustration of working with an on-premises accounting software build. Fran is one of the rare go-betweens of the Power Platform and Intuit’s Quickbooks. She makes easy work of bridging the gap between numbers and decision-makers to design the best solution for both her large and small business clientele. With the ability of Power Query to cleanse and transform data to the perfect format for the transition to QuickBooks, the low/free entry cost and Fran’s recommendation may just tip the scale to bringing more small businesses to the fold.
Fran also shares her data origin story. While she has always been a numbers person, Fran developed an early appreciation of software and took the first opportunity to transition away from the antiquated accounting technology of the past like Micro Fiche machines and Exacto knife/scotch tape corrections to computational files and automated calculations.
Beware, however, that today’s episode may also result in a few laughs as host, Rob Collie drops the mother of all Dad Jokes. You do not want to miss this episode.
Also on this episode:
Rob Collie (00:00:00): Hello friends. Today's guest is Fran Reed. I think we're all aware that for decades, Excel has been the everything glue, it makes business work. Well, in this episode, we essentially pay Power BI the highest of compliments, which is that it is starting to take on a similar role, in addition to all of the environments and scenarios in which we're used to seeing Power BI used.
Rob Collie (00:00:26): Did you know that Power Query can be used to help transfer data from one line of business system to another? Did you know that in some small business environments, Power BI Desktop alone is enough to be a game changer? It's truly extraordinary, isn't it?
Rob Collie (00:00:42): That the same tool is being used at the enterprise level of multi, multi, multi billion dollar corporations. And at the same time scales down and delivers amazing impact even all the way down to the sole proprietorship level of business and all points in between. In a professional sense, Fran lives metaphorically at the intersection of Power BI and QuickBooks.
Rob Collie (00:01:07): And we have mentioned many times on this show before, how Power BI's pricing model actually scales incredibly well to the mid-market. And whether you consider QuickBooks customers to be mid-market or small business, I think is really just a matter of semantics. We P3, also use QuickBooks as our accounting line of business system.
Rob Collie (00:01:26): And frankly, it's kind of hard to imagine what it would take to outgrow it. So we talk all about that and a lot of other fun things for instance, the world's original compression algorithm was an optical compression algorithm and it was microfiche. We talk about Dropbox versus OneDrive, and how that's an evolving story over time.
Rob Collie (00:01:44): And we make a bunch of Mr. Mom jokes about 64, 65 bits, whatever it takes, that kind of thing. It was really great to catch up with her. She's been part of this whole Power Pivot, Power BI, Dax and M revolution for a very long time. She's practical, experienced and funny. So let's get into it.
Speaker 2 (00:02:04): Ladies and gentlemen, may I have your attention please.
Speaker 3 (00:02:08): This is the Raw Data by P3 Adaptive podcast, with your host Rob Collie, and your co-host Thomas LaRock. Find out what the experts at P3 Adaptive can do for your business, just go to P3adaptive.com. Raw Data by P3 Adaptive is data with the human element.
Rob Collie (00:02:33): Welcome to the show. Fran Reed, how are you this morning?
Fran Reed (00:02:36): Fine, thank you. Thank you for having me on here and really appreciate it.
Rob Collie (00:02:39): Thanks for being here. And we've of course navigated the usual technical difficulty. We do those backstage. Imagine if we included in the show all of the, can you hear me? That should be part of every episode. We're past that now. So Fran, why don't you tell the good people a little bit about yourself. What do you do for a living, we'll go from there.
Fran Reed (00:03:00): I work within the QuickBooks community and I do custom reports. So the larger businesses within QuickBooks that need specialized reports or integration, I'm the gal. I'm a data nerd. I'm Excel nerd. I love all that stuff. Basically that's what I do. And there's a small handful of us, maybe six to 12 of us that do the kind of reporting that I do, it's kind of a nice little niche.
Rob Collie (00:03:25): You said the larger QuickBooks customers, can you sort of give me a feel for how large do you have to be.
Fran Reed (00:03:32): Well, the ones that have inventory or job costing where there're more complex than just a regular business. So it could be any size, but usually a desktop of QuickBooks because QuickBooks Online, hasn't caught up to that complexity for those guys yet.
Rob Collie (00:03:45): Interesting. So the online product lacks the features.
Fran Reed (00:03:48): It's not as robust.
Thomas LaRock (00:03:50): That's a familiar topic.
Rob Collie (00:03:53): It's such an inversion, right? Now the cool thing is that your cloud version of your software has features that your on-prem software doesn't have. QuickBooks is like, we're going to buck that trend.
Fran Reed (00:04:03): Yeah. The online version is getting there, but it's not quite there yet. They're trying.
Rob Collie (00:04:08): I thought you were going to say that, well, these people have been in business for so long, they've been on the on-prem version and migrating to the cloud it might be a little bit of that, but it does sound like it's a feature parody issue primarily.
Fran Reed (00:04:18): Yeah, it is. And what I run into is a lot of the on-prem guys will find hosters so they can be more portable. So they're working with hosting. So then I have to deal with if the cloud hoster can deal with the reporting tools I use or not.
Rob Collie (00:04:32): Just continuing down that question thread, if they have particular complexities in their business that opens them up to needing the sorts of services that you provide, just give me an idea revenue wise, number of employee wise, what's the smallest type of organization that you'll end up working with.
Fran Reed (00:04:48): Oh man, that's really hard. It's all over the place.
Rob Collie (00:04:51): That's what we do here. We ask the hard unscripted questions. It's the Raw Data.
Fran Reed (00:04:58): Usually there's 10 to 20 employee or something of that nature. Because I'm in the QuickBooks world I end up getting requests for financials and stuff like that. But my real passion is trying to get analytics for the owners and get them more into Power BI. And financials frankly are boring. I want the company to be able to pull out the data quickly and easily. What's important for them to run their business.
Rob Collie (00:05:23): Can you differentiate for me and for other naive people, where you draw the line between financials and analytics.
Fran Reed (00:05:31): Financials would just be a profit and loss statement, a balance sheet, cash flow. Those are the financials.
Rob Collie (00:05:37): The standard financials?
Fran Reed (00:05:38): The standard reports that you need for taxes and accountants live and die with them. I have a real push to try to get business owners involved in more visuals of analytics.
Rob Collie (00:05:48): Let's keep pulling on that thread. Those sorts of reports that you're talking about, those standard financial reports, those tend to be the sorts of things that are run monthly or quarterly.
Rob Collie (00:05:57): If you took the frequency of just those reports and you ran them more frequently on a weekly basis, or sort of a daily basis and with an eye towards predicting where the month will close based on this pace, would those standard financial reports start to blur a little bit over into the analytics space for you? Would that be higher value?
Fran Reed (00:06:18): Yeah. If you did a little bit of forecasting in that vein, but usually the transactions week to week depends on the accountant that inputs the data, right? That they input it correctly. And that's another thing I do is I build templates for clients that need to get data into QuickBooks from third party software.
Rob Collie (00:06:36): What does that look like, templates?
Fran Reed (00:06:37): Lots of times third party software doesn't have a way to get data into QuickBooks so I use a tool called Transaction Pro. So I set up an Excel spreadsheet that Transaction Pro can read easily and then import into QuickBooks. Oh my gosh when Power Query came on the scene, that was awesome because the data coming out of third party system is not always clean.
Rob Collie (00:07:01): No.
Fran Reed (00:07:05): It's jumbled up so you have to transform it or it's in all sorts of columns and you need it to be in row so you have to do some pivots around pivots with it.
Rob Collie (00:07:13): We're going to rename the podcast, the shocking truth.
Fran Reed (00:07:19): So Power Query was a dream to be able to clean up spreadsheets.
Rob Collie (00:07:24): I believe it. How does Power Query work in tandem with something like Transaction Pro, which I have never seen. How do you use the two of them combined to get what you need?
Fran Reed (00:07:32): In Excel I set up a template for them to take their data, put it into Power Query and then goes into Excel, obviously in a format, right? And Transaction Pro will read that Excel sheet and you map it. There's a mapping.
Rob Collie (00:07:46): Okay. And then Transaction Pro will then inject it into QuickBooks?
Fran Reed (00:07:50): Yes. It feeds into QuickBooks. Yeah.
Rob Collie (00:07:53): You do just the usual export to spreadsheet export to CSV from the other system, the non QuickBook system, Power Query it to reshape it and maybe even line up the mapping. So then Transaction Pro is just a really seamless into QuickBooks.
Fran Reed (00:08:10): Yes. And I set it up so the client can obviously run it by themselves. I mean, I don't want to be in that business.
Rob Collie (00:08:15): Yeah. Which is funny. Right. We talked of this in the show before. You write that Power Query, the traditional consulting mindset would be that you hoard that Power Query, you hoard that macro and every time someone needs to do something, you've got a billable opportunity, right?
Rob Collie (00:08:31): That's the parasitic consulting model that the handbooks teach you. First of all, it feels gross. Doesn't feel particularly honest. And secondly, it's boring AF. You don't do that. You give it to them and let them run it. It's awesome.
Fran Reed (00:08:45): And they're importing this stuff daily. So I don't want to be. I don't do bookkeeping.
Rob Collie (00:08:51): Yeah.
Fran Reed (00:08:51): Just is-
Rob Collie (00:08:52): You could have hired an offshore team to run these macros. I'm just kidding.
Fran Reed (00:08:55): Nah. And even in the reports I run for clients, I try to have them filters and slicers so they can pick the dates that they want to look at and make them flexible.
Rob Collie (00:09:10): That's smart. I'm completely 100% in agreement with the way that you've gone. The very first time I faced you many years ago, I faced exactly that question with a client. I felt the elders of consulting sitting on my shoulders going, no, do not give them this. I'm like, "Are you kidding? I'm giving them this macro today."
Fran Reed (00:09:25): There is that fine line about, well, maybe you can develop support agreement or something on keeping it up or they just call me if they breaks.
Rob Collie (00:09:36): Fran, you haven't lived until you've managed a team of 1099s over Fiverr. Or hired some local neighborhood teenagers, that is not the way. We need a new meme for the Mandalorian, this is not the way.
Rob Collie (00:09:52): That kind of I think starts to answer my next question which was integrations. You said reporting and integrations. I was like, okay, what are the integrations like? This is an example of integrations or is there something else there?
Fran Reed (00:10:03): No, that's really it. For a time I was one of the few people doing integrations from clients that you were using Concur. Concur did not have a good integration.
Rob Collie (00:10:15): I don't know what Concur is.
Fran Reed (00:10:18): Concur is a accounts payable type of system.
Rob Collie (00:10:20): Okay. It seems like the sort of thing that QuickBooks should have built in.
Fran Reed (00:10:23): It's just better than what QuickBooks has. Again, the larger clients use it.
Rob Collie (00:10:27): It's a new band name. You remember, Better Than Ezra with their one song. We're going to be better than QuickBooks.
Fran Reed (00:10:34): Concur since has learned how to build their own integration so I don't have a lot of template work on there. But lots of times, I have one client that does cruises around the Florida Bay, their program that manages the cruises, the data of the money he gets needs to go into QuickBooks so I build a template for that.
Fran Reed (00:10:52): So it's that type of stuff. It's third party stuff. Sometimes where they have a third party inventory system, doesn't play nice with QuickBooks and I do something with that.
Rob Collie (00:11:00): So I discovered this morning to my absolute surprise. You and I were not directly connected on LinkedIn.
Fran Reed (00:11:06): I saw that. Yeah. I saw that.
Rob Collie (00:11:08): We fixed that this morning. I forget the exact wording of your headline, but I believe it used the term, small business, didn't it?
Fran Reed (00:11:16): I haven't looked at my LinkedIn profile in so long. It probably does, but mainly my tagline is your data in focus.
Rob Collie (00:11:24): So the word small business lead me to the following tongue-in-cheek reaction. Hey, wait a second, I'm on the internet. I read about Power BI. I see content out there. It always uses the word enterprise.
Rob Collie (00:11:35): Enterprise governance and got to have the Azure and the Synapse, all of this stuff and getting user buy-in and adoption. And clearly this Microsoft Power platform is only useful for multi-billion dollar annual organizations. I don't understand.
Fran Reed (00:11:52): Power BI when it came out was just a dream because it allowed small business to have the tools that large businesses have and especially being free or low cost.
Rob Collie (00:12:05): Rounds to free.
Fran Reed (00:12:06): Yeah. Oh man, it's just an awesome tool.
Thomas LaRock (00:12:09): I just want to point out that we had a CVP that did not describe Power BI when it came out as a dream.
Rob Collie (00:12:17): The first version of Power BI, the false start was all Excel and SharePoint based. Okay. We try not to talk about that era because it cellys the name Power BI, doesn't it?
Thomas LaRock (00:12:28): We don't want to remember that, but everybody's has to start somewhere.
Fran Reed (00:12:30): Yeah. It was just a great tool. And once you learn Power Pivot in Excel, the translation was very easy into Power BI.
Rob Collie (00:12:39): Heck of an on-ramp.
Fran Reed (00:12:41): I mean, my Power Pivot knowledge is all from you. I think you know that, right?
Rob Collie (00:12:45): I think that's very generous of you. All of yours prepackaged knowledge that was transferred. Yes. And I know you know this, right? I'm not telling you something you don't know. I'd still say that the majority of your Power Pivot knowledge came from hands on experience even after you'd learned some things from me.
Fran Reed (00:13:01): Yeah. When you first came out with your book, it's dogeared. It was my Bible because I learned Dax.
Rob Collie (00:13:06): And like any good religious text, it contains some things that first version that were not actually true.
Fran Reed (00:13:13): Yeah. I got the second version too.
Rob Collie (00:13:16): The second version, the new Testament much more factually accurate.
Fran Reed (00:13:21): So once I had that, then Ken Puls came along with Power Query, devoured his stuff. That's sort of my journey.
Rob Collie (00:13:28): I'm going to ask you a question, but I'm going to admit in advance that I personally hands on have not really done anything with this. Our company does, do this all the time now.
Rob Collie (00:13:36): There's so many things that our consultants have learned and get to play with on a daily basis that I, A don't have any need to most of the time. B, don't have any time and C, I've never liked technology. Have you scratched the surface of dataflows?
Fran Reed (00:13:49): No.
Rob Collie (00:13:49): So again, I'm sympathetic. I've seen it. We use dataflows all the time as a company, but it's like Power Query in the cloud. It might even get easier in a way to build these integration pipelines that you're talking about. It's worth poking around there.
Rob Collie (00:14:02): I don't know whether the endpoint, the output of the data flow could be fed straight through something like Transaction Pro or straight into QuickBooks. There's probably still an air gap there.
Rob Collie (00:14:13): Rather than delivering a Power Query file to the client if you ever wanted to improve it or fix it, you'd be able to go and edit the code directly in the cloud. You'd be running essentially your own cloud software service at that point.
Fran Reed (00:14:26): I'll have to look at that.
Rob Collie (00:14:27): You could even get notified when it failed and things like that.
Fran Reed (00:14:31): Okay.
Rob Collie (00:14:31): Another thing that you can go out and get ahead of me on.
Fran Reed (00:14:36): Yeah. I love learning the new technology. My clients are very comfortable in Excel. Obviously it's the tool of the day, but I am so surprised at, well, I can tell where a client is if I can hear the cringe, if I mention pivot tables, because there's still a lot of people afraid of pivot tables, I just go, no, you guys are kidding. It's sad.
Rob Collie (00:14:55): I was one of many generations of Excel engineering team members that have tried to make pivot tables as mainstream as charts. We made a dent. We certainly didn't get to chart level. Talking to Microsoft today, some of the same things that we were talking about 20 years ago, back there, they're still talking about it. Like, they can pull it off this time.
Fran Reed (00:15:13): I mean, I use pivot tables more than I do charts. I don't even do charts then because they were sort of hard to set up and deal with. And pivots with Power Pivot is just amazing what you can do as Excel has evolved into a data modeling tool.
Rob Collie (00:15:28): Agreed. We've been talking now so jumping back and forth quite a bit already even between using the phrase Power BI and then talking about Excel specifically with Power Pivot and Power Query built into it. What is the usage profile with you and your clients of actual Power BI versus the Excel based tools?
Fran Reed (00:15:46): Power BI not as much as I'd like, I want to do more in it. Power BI is not really great for true financial statements just because they go over a page. So I tend to stick in Excel and I'm trying to see if I can, as I get jobs, do some templates with Power BI to show them what's out there, what's possible in more visual terms.
Rob Collie (00:16:05): There are a number of third party vendors right now who are doing at the demo level, doing an amazing job at things like financial statements in Power BI. I think the market at large recognizes this shortfall. And even if Microsoft isn't directly going at it, a lot of the partner ecosystem of software vendors, they're playing heads up.
Rob Collie (00:16:26): Even I hesitate to mention the actual firms that I'm aware of, not because I'm afraid of promoting them or anything, I'm actually afraid that I will get the names wrong. I see a bunch of demos of software all the time. And I think I probably of the five things I have in my head, I've got at least a couple of their names transposed in memory.
Rob Collie (00:16:45): I'm going to save myself the foot in mouth moment. If you shoot me a note, I'll make sure we get you the right names. I thought that you were going to say that the reason why Power BI isn't as widely used between you and your clients yet, I thought that your answer was going to be, well it's too foreign. They're familiar with Excel.
Fran Reed (00:17:02): That's one issue. It's new.
Rob Collie (00:17:04): Yeah. It is. In the places that you are using it with your clients, how is that even deployed? Do they have their own Power BI subscription? Are they just using Power BI Desktop? Are you sort of running a subscription for everybody or how's that work?
Fran Reed (00:17:16): No, they're using Power BI Desktop. The data comes through a data model. We export the data out of QuickBooks using QQube, but I don't know if you're familiar with that tool.
Rob Collie (00:17:25): I am familiar with QQube.
Fran Reed (00:17:27): It's one of the best tools to get data out of QuickBooks. It's more complete. I've been around the owner for many years. He's mentored me.
Rob Collie (00:17:34): Chuck.
Fran Reed (00:17:34): Yep. Chuck Vigeant.
Rob Collie (00:17:35): Vigeant. See, this is the thing with internet names, right? I would pronounce it Chuck Vigeant. Tom does not familiar with QQube. Give us the rundown.
Fran Reed (00:17:46): QQube is a tool that extracts the data out of QuickBooks Desktop. He takes every piece of data out of there. It's all a data model. He presents it in Excel with an add-in. I tend to roll my own and I'll bring in my own tables and do the joins, put it in Power BI or even Excel.
Rob Collie (00:18:04): As far as I understand it and I have seen it a few times. It's basically data MAR in a box off the shelf. It translates the incredibly complicated obscure QuickBooks storage schema into a human readable star schema type of thing, which is exactly what Power BI and Power Pivot would like to consume.
Fran Reed (00:18:22): That's exactly what it is.
Rob Collie (00:18:23): It's amazing how this product exists at every level of ERP. The fact that it's "Desktop QuickBooks," even if it's running in some sort of cloud data center ish type of thing, right. Means that your kind of already one step off the cloud when it comes to data refresh and all that kind of stuff.
Rob Collie (00:18:41): If you got a Power BI gateway installed and that same infrastructure, and then you'd be closer to an auto refresh type of thing.
Fran Reed (00:18:49): You have to refresh and then run your reports. So you can schedule the refresh for daily refresh.
Rob Collie (00:18:54): Have you ever run across Mary Fealty, that name ring a bell?
Fran Reed (00:18:57): No.
Rob Collie (00:18:58): So she was on the show, I don't know, six months ago. It's worth looking her up. Either that episode or just looking her up online. First of all, she's in Ireland. And her business isn't exactly the same as yours, but it's amazing how far she has casually pushed the envelope.
Rob Collie (00:19:16): She is running her own Power BI embedded infrastructure within her own website. And all of her clients are consuming their reports through her Power BI tenant, running embedded.
Fran Reed (00:19:29): Wow.
Rob Collie (00:19:29): That's the other end of the spectrum in terms of automation and fully leaning into the platform. Not that anything that you're doing is broken or wrong. When we were talking to her I was expecting sort of the same sorts of answers, right?
Rob Collie (00:19:41): How do you get client organizations with relatively small IT footprints to embrace something so automated? It's a bit of a stretch, right? They're used to desktop tools. I don't think QuickBooks is part of her business so much, but it's worth looking at.
Fran Reed (00:19:55): Yeah. The biggest challenge is when I'm trying to set up a report, I have to get a copy in the QuickBooks file or files and then work on it on my own. That can get to be challenging. Just setting up to do the reporting can be challenging.
Rob Collie (00:20:10): I believe it. Once you get it built then you give them the Power BI, the pivots file or files and you teach them how to press the refresh button.
Fran Reed (00:20:18): Yes. And then sometimes I get into, well, my Cube isn't working how do I get, then you end up into support issues.
Rob Collie (00:20:26): You got to love the error messages that come out of Power Query at refresh time, they have a 0.0% chance of helping you fix the problem.
Fran Reed (00:20:34): Yeah.
Rob Collie (00:20:36): What was it the one I ran into the other day? I mean, this is a very, very, very standard Power BI refresh error, but it was the collection was empty or something like that. It was like, what? That was the front headline of the error message. You need decoder ring that says it's not finding the file.
Fran Reed (00:20:53): Yeah. And we get that with Cube and you can't read the SDK properly. There's something wrong with your QuickBooks file and blah, blah, blah. So you get all of that in there. And the thing I am running into now is people not having a 65 bit Excel. Did I say 65?
Rob Collie (00:21:10): You said 65. It's Mr. Mom, what did you use? A 38? 38, 39, whatever it took. Do you use 64 bit?
Fran Reed (00:21:19): Yeah. So one issue we run into is that a lot of clients may not be using 64 bit Excel or they're not on Office 365. So we get to power out a lot of these tools they need to be up there.
Thomas LaRock (00:21:31): Is that still a thing these days? People aren't running 64 bit?
Fran Reed (00:21:35): One of my larger clients the other day I had to have them upgrade.
Thomas LaRock (00:21:38): So where are they on? Windows 7?
Fran Reed (00:21:40): No, it's the Excel.
Thomas LaRock (00:21:41): I guess I don't understand. So they're running 32 bit Excel on a modern day OS, I don't know, Windows 10 or something.
Fran Reed (00:21:49): Yeah. And sometimes it's a hoster that is the problem.
Thomas LaRock (00:21:54): Okay. Well there's that.
Fran Reed (00:21:54): And there's actually one hoster that can't use Cube because they don't update their data. So if a client's using that, they're dead.
Thomas LaRock (00:22:02): But this is where we can yell at Rob because you're the installer guy. There's no reason that a 32 bit version should ever be allowed to be installed on the 64 bit platform.
Thomas LaRock (00:22:12): Windows should give you a message that says, hey, you may have chosen the wrong version of Excel to install. Do you really want to install the 32 bit version on this 64 bit platform?
Rob Collie (00:22:24): I'm not going to be involved in anything that tramples people's individual liberties to install substandard versions of the product. I think we do need a new Sally Struthers commercial.
Rob Collie (00:22:36): Look at this. This is Jimmy, he's still running 32 bit infrastructure for just pennies a day. The price of a cup of coffee we could get him a 64 or even 65 bit infrastructure.
Thomas LaRock (00:22:53): Whatever it takes. We need to start doing video promotions for this podcast now. Now we got to start making commercials. I'm sure Sally Struthers is available.
Rob Collie (00:23:04): I cameo.
Thomas LaRock (00:23:05): Yeah. Just read this.
Rob Collie (00:23:07): I've tried that a couple times already with celebrities like that with mixed results. I hired one of the Letterkenny actors to do a reading of something and he just ignored all instruction and did what he wanted and then took the money. We called Amex and said, we're going to claw that back.
Rob Collie (00:23:23): I don't know if we got the money back or not, but we didn't use it for a thing. And then we went and made the Letterkenny video that we wanted to make with our own people. I've also been trying to get ahold of the most interesting man in the world, the old Dos Equis.
Thomas LaRock (00:23:36): Is he on cameo?
Rob Collie (00:23:37): He is on cameo for a while there, especially in the '80s and maybe also in the '90s sitcoms had these outro one second clips that were like, sit Ubu, sit. Good dog.
Thomas LaRock (00:23:49): Yeah. The production company. You're right.
Rob Collie (00:23:50): I want him to be the recorded version of our production company, Outro for everything. Stay curious my friends. And I've reached out. They looked at us and said, P3, we only work with P4 and above.
Fran Reed (00:24:04): Well, that's not very nice. They don't know who they're missing.
Rob Collie (00:24:07): Okay. Let's get in the way back machine. We've been talking a bit about the now, how did you find your way into this line of business? What's the origin story of Fran Reed?
Fran Reed (00:24:17): Well, I started out in the mid '70s working at Hughes Aircraft and that was when we had a big ledger, 13 column yellow paper ledgers. And IT department in the background that you never talked to.
Rob Collie (00:24:32): We had IT in the '70s really?
Fran Reed (00:24:34): Yeah.
Rob Collie (00:24:35): What were they doing?
Fran Reed (00:24:36): Yeah. And I've always been interested in the computer and data and all that jazz. So Lotus 1-2-3 Excel and that whole journey. We had a project, the corporate would microfiche the daily transactions and then we would take those daily transactions in the accounting department and manually book them into this big ledger, go figure.
Rob Collie (00:24:56): Holy cow. Okay. Let's unpack that slowly. First of all, for the younger members of the audience, microfiche has nothing to do with hacking or trying to steal people's bank accounts or their personal information. It's not spelled the same. This is back when you would go into a physical library.
Rob Collie (00:25:13): Does it make sense for them to store approximately 500,000 tons of old magazines? No. So they would take pictures of every page and these are old newspapers as well. And then they would zoom them way down into microscopic format and you'd load this.
Rob Collie (00:25:29): It looked like a piece of film, right? A photo negative, which again, many people listening to this have never seen a physical photo negative. And then you zoomed in on this giant screen in front of you. It was crazy. Anyway, go look it up. Microfiche.
Fran Reed (00:25:41): Microfiche. Yeah. F-I-C-H-E.
Rob Collie (00:25:45): I had no idea that microfiche, I was taught microfiche. If you use micro fiche as you're manipulating the machine you have to leave your pinky extended. Microfiche you could grip the handle with all your fingers, but microfiche was a four finger thing. I did not know that microfiche was used outside of libraries.
Fran Reed (00:26:06): Yeah.
Thomas LaRock (00:26:07): It was the '70s.
Fran Reed (00:26:07): Yeah. It was the '70s. Yeah.
Rob Collie (00:26:08): What in the wide world of sports was going on.
Fran Reed (00:26:10): They were getting the data from somewhere and micro fiching and we would put it in a reader and then translate it into our ledger. It was crazy. And then the debits would be in black ink and the credits would be in red ink and then they would microfiche our ledger in case there was a fire. Well guess what? Red ink doesn't show up on microfiche, it's only black and white.
Rob Collie (00:26:29): What?
Fran Reed (00:26:30): No one looked at it. So I discovered that. We changed our ways. We started putting brackets around the credits. But anyway, there was a big job that needed to be done that was going to take three of us three days to do on detailing transactions.
Fran Reed (00:26:44): I called the IT department and asked them if they could do that report for us, they were thrilled to have someone even call them and ask for a report. And they said, yes. And I got a big award for that.
Thomas LaRock (00:26:55): I can just see them. The phone rings and they stare at it. What are we supposed to do?
Rob Collie (00:27:00): Just the first call that they get in Ghostbusters, they were about to go out of business. No, one's been, right. We got one. IT people are sliding down poles.
Thomas LaRock (00:27:13): So this whole process, the data goes on to microfiche then you to read from the film and then enter into a system, right?
Fran Reed (00:27:20): Isn't that crazy? Into a manual ledger.
Thomas LaRock (00:27:23): Yeah. But it sounds like data quality must have been through the roof. There's no chance for errors.
Rob Collie (00:27:29): Yeah. Way to take a bunch of people who don't like doing repetitive work and make them do repetitive work.
Thomas LaRock (00:27:34): Now that is a real data pipeline if I've ever heard one, that is.
Fran Reed (00:27:40): It was a start.
Thomas LaRock (00:27:41): You should have just written it on stone tablets.
Fran Reed (00:27:44): Yeah. Just about.
Rob Collie (00:27:45): It occurs to me that microfiche in this situation was the original data compression.
Thomas LaRock (00:27:50): Yeah.
Rob Collie (00:27:53): Look out Vertepac.
Fran Reed (00:27:57): Well, I'm sure as far as corporate went, they probably had reports they used, this is just for our divisional ledger. So who knows.
Rob Collie (00:28:07): I can't stop picking at this copse. You would get microfiche, would read it on a reader and then hand write it onto the yellow ledger. Tom was saying, you'd enter it into a system. Yeah. Enter it into the pen and paper system or pencil and paper. This one that Bill Jelen told me is that you'd use pencil, right?
Fran Reed (00:28:24): Yeah.
Rob Collie (00:28:24): But eventually even if you were using pencil and eraser, you'd erase too many times, you would Exacto knife out a rectangle and scotch tape a new piece of paper with the real number in.
Fran Reed (00:28:37): I can hear Bill Jelen saying that. Yeah.
Rob Collie (00:28:40): Exacto knives and scotch tape were important tools in the accounting world.
Fran Reed (00:28:44): He was one of my gurus back in the day. Mr. Excel.
Rob Collie (00:28:49): Mr. Excel, indeed. We retitled all of our episodes at one point recently. We'd retitled him, the OG internet tech celebrity.
Fran Reed (00:28:57): Yeah. He was. Him and [Chandoo 00:28:59].
Rob Collie (00:28:59): To me Chandoo is very much wave two or three and still was really early.
Fran Reed (00:29:04): He might have been. Yeah.
Rob Collie (00:29:05): I mean, for someone who had been using the Exacto knives and white out and scotch tape at one point in his career to be on that absolute bleeding edge of internet publishing is actually one of the most amazing things you'll ever see. This world was painful. It sucked.
Rob Collie (00:29:22): And this was back when IT was actually really excited to build a report for you if you asked them. That was the one time, and then the second time you asked them, they were like, no, we're too busy, right?
Fran Reed (00:29:33): No, no, no. Anyway. So after working at Hughes Aircraft, the next stop was at Stanford and I worked there for about 20 years. And that's where I learned a lot of my chops on Excel. And as things were evolving I always had a foot with the IT department, how to do this, that or the other thing. I got pretty good with VBA.
Fran Reed (00:29:54): I learned some SQL. My SQL's pretty bad though. When I was at Stanford, one of the big things was we went through a conversion to Oracle. Stanford has own homegrown system for many years. And that process, which I was a part of was really good learning for me in the future when I'm dealing with my clients and them going through different conversions and understanding what people go through.
Fran Reed (00:30:16): Because the first time Stanford wanted it to be just like it's always been, I want the same way. And they found that Oracle couldn't quite do it. We did another round of conversion. There were three conversions. And you can imagine the expense of that.
Rob Collie (00:30:29): I'm sure it was exactly the original price tag. The price never went up once, did it?
Fran Reed (00:30:33): That was a big deal. And that when I left Stanford I decided to strike out on my own. And I was going to do Oracle consulting and realized that as an individual person, it really wasn't going to work because Oracle it was too big a product. I went into the QuickBooks world from there and that was in 2005.
Rob Collie (00:30:52): So how did you so quickly pivot into the idea of QuickBooks? Had you had exposure to QuickBooks before that?
Fran Reed (00:30:58): A little bit because of my counting background, my community came up with that. I can't even remember who suggested it, but somebody did and that turned my career path around. So I became a QuickBooks ProAdvisor, again put my shingle out for doing reports right away.
Fran Reed (00:31:13): I never did day to day bookkeeping. And at that time, Excel, Microsoft Query, these tools to get data out of QuickBooks and dealing with them in Excel. Did a lot of VBA back in those days.
Rob Collie (00:31:27): Oh yes.
Fran Reed (00:31:30): It was a fun journey and wow, it's quite a bit.
Rob Collie (00:31:34): We also had Tim Rodman on the show relatively recently. And he has a similar relationship with Acumatica that you have with QuickBooks. He's positioned himself as a reporting and analytics professional specifically for companies that use Acumatica.
Rob Collie (00:31:51): And if I were in this world doing the same sorts of job career that you and Tim are doing, I think I would probably end up more resembling the way that you approach things, then the way that Tim does. Tim doesn't do bookkeeping either, but I would definitely not.
Rob Collie (00:32:07): Tim cannot resist getting involved well upstream from the reporting because the simplest most glib way to state it is that's where the data quality is decided, is the upstream. But the other reason is it's the business processes upstream that are both generating the data and are also the things that need to be optimized.
Rob Collie (00:32:29): And so I think he finds himself creeping upstream in his relationships with his clients, even though he really only wants to do the reporting and analytics. He's not trying to be their general purpose, operational, IT, even for their Acumatica system.
Rob Collie (00:32:44): However, he ends up I think his footprint goes a little further upstream than what it sounds like you're a keen on doing. Maybe I'm wrong about that. Even though I admire the way that Tim operates. And I think it's probably "The right way," I would probably want a little cleaner separation. Go, no, no, no, the systems are yours. How do you react to that? Do you feel the same way or?
Fran Reed (00:33:07): No, I love partnering with my clients and kind of learning about their businesses and getting into their businesses. And I do get calls to do clean up in QuickBooks or splitting a file or something like that. So it's a not purely just reports. I enjoy getting involved in the business process.
Fran Reed (00:33:23): If we can set up QuickBooks in a way the owners can get better data, I will try to work with the finance. I'm mainly dealing with the finance guys in QuickBooks. So I'm not always dealing with the owners, but I do love getting into the business process. And that is up stream, getting the owners and also the accountants to realize I'm not just a report writer, I can do some of these other things for them and add value.
Rob Collie (00:33:49): An organizations of the size that you deal with, somewhere between the finance people and the owner. That's the BI department to the extent that they would have one, it's going to be some Venn diagram overlap of ownership and finance. In fairness to myself, I think since I have stopped being a frontline consultant, which and actually been a number of years now, I really haven't.
Rob Collie (00:34:09): I don't think I've done any hourly work directly billable work for a client since I don't know, maybe even 2016, my attitudes towards all this stuff have changed. When I first hung out the P3 shingle, it was very clear, take your data, crunch it. And of course I'd be working with them around their goals and all that kind of stuff.
Rob Collie (00:34:27): But I had this artificial wall I drew between myself and the client that I thought was for the client's benefit. You hear me say things like being better informed is useless. It's only if you can translate that into improved action, doesn't make a difference.
Rob Collie (00:34:42): So it's all about the process. It's all about the improvement loop. I was sort of transporting myself back to 2013, imagining myself being back as an individual, wanting like Jack Nicholson style, wanting that wall, needing that wall, but it's a bad wall.
Fran Reed (00:34:59): Were you doing a lot of training back in those days too?
Rob Collie (00:35:02): That was a big part of the business back then less so today. Okay. So you got into QuickBooks and you'd learned some serious Excel chops at Stanford, but of course continued to develop that once you went solo, how'd you find your first clients in that game?
Fran Reed (00:35:15): Yeah. I just don't recall that, but I started out getting reporting clients right away. So as I became a ProAdvisor, you're part of a community within QuickBooks. So I probably got referrals from that.
Fran Reed (00:35:27): And then I'm very active in the community. I make sure I'm really active in it and get referrals that way. Now people actually, they know who I am and I get clients all over the country.
Rob Collie (00:35:39): You're a maid member of the mob now. Here's the thing. If you had struggled to find clients early, you would remember much better where they came from when they did come in. Trauma has a way of cementing memories.
Fran Reed (00:35:53): And I never did hourly billing. I always tried to do project based billing. You mentioned hourly a little earlier. It's very difficult sometimes.
Rob Collie (00:36:04): So how do you do that? The way I described this years ago, after doing some project based work in this space, basically four times out of five going in, I could estimate roughly what a project was going to entail and be reasonably accurate. But the fifth time I would underestimate how much would need to be done by a factor of at least five, therefore wiping out all of the advantage of being accurate the other four times.
Rob Collie (00:36:26): The times you get right were just making up for the time you got wrong. And so it's just such a difficult model. You would think that in the data space, the reporting space, the things would be more predictable than that. And it's sort of the cousin of that other thing I say all the time, which is people don't know what they need until they've seen what they asked for.
Fran Reed (00:36:45): That's totally true.
Rob Collie (00:36:46): So there's a little bit of that, that's part of being off by a factor of five, but there's also part of you as the consultant. Your early understanding of what they're after and what their systems look like. You almost have to be deliberately simplifying your view of what's going on in order to make sense of it at that point in the project.
Rob Collie (00:37:03): And again, usually those circuits four times out of five, 80% is pretty good. Those circuits are doing a good job, but then there's that iceberg that you just thought was a piece of styrofoam floating in the water.
Fran Reed (00:37:17): Yeah. And those are hard to price, and clients for reporting they're not as sensitive as they are with maybe conversion projects and stuff like that, but it's still hard pricing out. And you're right. That fifth project where you run into the iceberg, do you charge them more, do you just let it go? Do you work it through? There's that.
Rob Collie (00:37:36): It's brutal. And you're speaking from a perspective. Your ecosystem is one of the more predictable and constrained ecosystems because you know that QuickBooks is going to be at the center of it. That brings an element of predictability compared to our business.
Rob Collie (00:37:52): It's a different set of systems. Every single time. I feel sympathy for you, but it also is kind of misery loves company. I'm excited to hear that even in your, by comparison, more predictable environment, things are still not predictable, that warms my heart.
Fran Reed (00:38:06): People talk about, well, you could set up a template to do these kinds of reports. You can to a certain degree, but everyone sets up their systems differently. There's always a uniqueness.
Rob Collie (00:38:16): And the business logic of how they calculate sort of their existing reports. Even if QuickBooks isn't heavily customized, the number of years that they might have been iterating and evolving their home grown reporting solution, the complexity they can build up over time like geologic layers being deposited in the early, early going.
Rob Collie (00:38:38): One of the times that I got burned the worst was by that. And this reporting system was written in Fortran. It was completely procedural. It was completely custom built. And as we eventually worked through it, being five X over the original estimate, we discovered that there even places where that Fortran had been buggy forever.
Rob Collie (00:38:57): Imagine all the time that you waste trying to figure out why your Power BI number, your Dax number is wrong, when the original number was actually wrong and your Dax is the first time it's ever been right, you can lose weeks on that. I don't know Fortran. I'm not going to go debugging.
Fran Reed (00:39:10): Oh my gosh. Yeah. That'd be crazy. No, we run into some of that with the QuickBooks files, it's pretty nuts sometimes. When you run into multicurrency or they have five or six QuickBooks files, you want to connect together for consolidated reporting and some people have 100 files, QuickBooks files you go, "How do you manage all that?"
Rob Collie (00:39:31): We don't.
Thomas LaRock (00:39:32): That's right. We have you.
Rob Collie (00:39:33): In situations like that look as long as there's still money coming in, there's no detected problem. If suddenly 10% of those income streams went to zero, we would probably notice.
Fran Reed (00:39:44): One challenge I have is that if the client has a third party system where a lot of their day to day is kept, they want everything from that third party system into QuickBooks and going, there's no real need for that in QuickBooks. So trying to educate them on that and how much easier it is when it's not an exact duplicate of the third party system.
Rob Collie (00:40:06): Yeah. Intrinsically, if you're running a business or you own a business or whatever, and you know that you've got four or five different line of business systems, and you're frustrated that you can't see a cross all of them simultaneously, which is a 100% legitimate.
Rob Collie (00:40:21): In fact, table stakes type of need. Of course you need to be able to see you don't need five separate reporting systems, you need one that tells you what's going on. That integrates from all of them. Okay. So that's good. But then the next instinct very often for sort of the uninitiated is, okay, I need to get all of that data together in the same place.
Rob Collie (00:40:40): I need to make these systems talk to each other so that I can get a report across all of them. And no, that's the wrong instinct. It turns out the data model of Power BI is a great place for that data to meet. It's the middleware, how Power BI talk to all of them.
Rob Collie (00:40:56): I'm sure that in the earlier stages of my career that would've been my instinct as well. Well, look, QuickBooks has got all these customizable tables. Why don't we start jamming all that data? Oh God, no.
Fran Reed (00:41:06): Well, and then you have folks that want to put everything into one table instead of using the star schema type of setting.
Rob Collie (00:41:13): I mean, one table is the simplest thing, right?
Fran Reed (00:41:15): Yeah. Well, and that's the way it used to be. You had to do it that way.
Thomas LaRock (00:41:20): Sometimes you just would need the one table is the best way.
Rob Collie (00:41:23): Just keep adding columns.
Fran Reed (00:41:25): Yeah. No problem.
Thomas LaRock (00:41:26): It's a feature, not a column. The features.
Rob Collie (00:41:29): I mean, I like to make my table so wide that they end up with sparsity where you go scrolling, left and right up and down through them and you never even see any numbers. Unless you're on the right rows and the right columns at the same time, you could do that way, that situation for those tables, you could microfiche them.
Fran Reed (00:41:49): There you go. Sometimes there will break them up. If I'm not in Cube or just another reporting system. The QuickBooks world Intuit, and a lot of vendors are out there trying to develop reports.
Fran Reed (00:42:01): Nice third party reports with bells and whistles on them, but they never go deep enough for the kind of clients I work with. They usually just do a chart of accounts, which is a top level. And if you want to do anything more you have to do custom reporting.
Rob Collie (00:42:16): A universal truth, it should be added to the laws of physics that even in a standardized system, where even if you aren't even allowed to customize the schema, you almost always are. But even if you weren't one size fits all reporting never works, ever.
Fran Reed (00:42:29): No.
Rob Collie (00:42:29): Ever.
Fran Reed (00:42:29): Doesn't-
Rob Collie (00:42:30): Just forget it.
Fran Reed (00:42:30): I totally agree. Yeah.
Rob Collie (00:42:32): Now you can still sell that product, that looks good. The difference between the capabilities of a product that you have to build in order to sell it and the capabilities you have to build into a product in order for it to be useful, they overlap, but they are not the same thing.
Rob Collie (00:42:47): You can build all one or all the other and have a failure of a product. Those built in reporting systems are a perfect example of the ones that are built to be sold, but not built to be useful and valuable.
Fran Reed (00:42:58): Yep. And that was a lesson I learned when I was working with the Oracle project, is that the reports in the software aren't useful for the most part for the individual business, because everybody's different. The same thing with Intuit and QuickBooks, their reports are there, but they're not useful.
Rob Collie (00:43:14): Someone ask me, either one of you, whichever one of you wants to step up and toss this softball across the plate for me and get this out of my head and we can move on. Would one of you asked me, have you ever dabbled directly with QuickBooks? Ask me something like that.
Fran Reed (00:43:31): Rob, have you ever dabbled into QuickBooks yourself?
Rob Collie (00:43:33): Yeah. I messed around in there for a little while, but it's turns out I wasn't that intuit.
Thomas LaRock (00:43:38): That's one of those things that we just cut.
Rob Collie (00:43:40): A dad joke from me is generally expected.
Fran Reed (00:43:45): What do you use?
Rob Collie (00:43:46): We actually do use QuickBooks.
Fran Reed (00:43:48): Do you? I was going to say you're about the right size for that. Do you use Online or Desktop?
Rob Collie (00:43:54): Online.
Fran Reed (00:43:55): Online. Yeah.
Rob Collie (00:43:56): We don't have inventory.
Fran Reed (00:43:57): For services it works great. And it's been evolving. I mean there's one year when Intuit was just totally pushing online and it wasn't ready for prime time at that point, it's like, no.
Rob Collie (00:44:08): They built the sellable features. They hadn't built the usable features.
Fran Reed (00:44:12): Absolutely. The bank rec is one of the best things out there. The ability to hook up to the banks and have the bank connections.
Rob Collie (00:44:22): In terms of complexity, our business is high complexity. So we have some of them are halfway home grown even. The complexes we have are the types of things that if they were more standard, it would be the sort of stuff that you would be like, okay, that's only in the on-prem version of the QuickBooks, but it turns out it's not an either version of QuickBooks. We're also Salesforce.
Fran Reed (00:44:43): Yeah.
Rob Collie (00:44:44): Oh boy, Salesforce. And heck even the add-ons from Salesforce like Pardot are the alien parasite in the original Alien movie, right? If you try to remove that thing, the host dies. There's no way we could survive a Salesforce conversion. We have enough complexity and enough size to use all of these systems, but unlike a fortune 500, we would never have the budget for the nine figure conversion cost.
Rob Collie (00:45:11): It is interesting. The economies of scale with all these tools, a theme on this show over and over again has been that for small and midsize business, Power BI is the power platform in general is a deal akin to stealing. It's so good.
Rob Collie (00:45:26): You've got the cloud subscriptions. How many users do you need? 10. Our Dropbox subscription at our company costs a hell of a lot more than Power BI would if we even had to pay for Power BI, which we don't as a partner.
Thomas LaRock (00:45:40): Why are you using Dropbox?
Rob Collie (00:45:43): [foreign language 00:45:43]. So back in the day before the earth cooled, there was Dropbox and there was OneDrive.
Fran Reed (00:45:52): Dropbox was better.
Rob Collie (00:45:53): Dropbox had this quality to it that's hard to describe, but I think the word is good. Dropbox was good and OneDrive was not. I mean, I think it was as basic long time ago as Dropbox would be doing diffs of files and uploading just the diff and you could change one character in a 10 megabyte file and it seemed like OneDrive just needed to upload the whole file every time. There were many, many, many things about OneDrive that made it unusable in my opinion, circa 2012.
Thomas LaRock (00:46:29): Yeah. So 10 years ago. Yeah. I agree.
Rob Collie (00:46:29): Exactly.
Thomas LaRock (00:46:30): Okay. 10 years ago. Okay.
Rob Collie (00:46:32): Well, the old story worry about whether it's true or not, don't dispute it. The reason why the railroad tracks today are a certain width is because they trace all the way back to the width of the Roman chariot wheels that were wearing holes in the dirt paths and everything.
Thomas LaRock (00:46:45): Which is a bullshit story, but go on.
Rob Collie (00:46:47): Do not allow your version of the truth.
Thomas LaRock (00:46:50): No, this show is nothing to do with truth or facts.
Rob Collie (00:46:53): Come on. But we don't even know if you calling bullshit on that story is true. We don't even know.
Thomas LaRock (00:46:59): It is bullshit because you can find that there were many different types of railroad gauges in the 19th century. And the reason the railroad is what it is because that worn out for some reason, but there were different sizes because it was proprietary. It's like, no, no, this rail. My rail's ole.
Rob Collie (00:47:18): This is the OLE DB standard for records.
Thomas LaRock (00:47:21): Right.
Rob Collie (00:47:21): Not that we don't run on the ODBC.
Thomas LaRock (00:47:24): So who are they? Vanderbilts? Who are the railroad barons, right? Yeah. They were the ones like, no bullshit. The railroad then this is the gauge and this is what we use.
Rob Collie (00:47:35): It's not like they could be varying the gauge of that. That thing was laid down one time with a particular gauge. Are you saying that there was a competition going on at that point in time?
Thomas LaRock (00:47:45): No. I said in the 19th century, there were many different railroad companies. The transcontinental was built by two railroad companies that had standardized on the gauge that they would use.
Rob Collie (00:47:53): I sure hope so.
Thomas LaRock (00:47:54): Yeah. Because if they met, well, actually if they met a promontory point, maybe that's where they would just switch trains. All right, everybody get off, get on this other one because the track is different.
Fran Reed (00:48:07): That's funny.
Thomas LaRock (00:48:10): It's kind of getting your data in the format all the way to say into QuickBooks, but then it can't go anywhere else, now you got to stop at promontory point and now you got to use this tool to get the data out so you can get it into Power BI. So that stuff, it still goes on today Rob.
Rob Collie (00:48:26): I think we could probably pull off the Dropbox to OneDrive switch over.
Thomas LaRock (00:48:29): At this point in time, yes. When people talk about Dropbox today I'm like, you're a Microsoft shop, if you're using Azure Active Directory, all that stuff, just keep it all together. There's no reason for you to share your data with a third party like Dropbox.
Fran Reed (00:48:43): I've switched over to OneDrive and I still have a Dropbox subscription for some of the old stuff.
Rob Collie (00:48:48): It's not sharing my data with Dropbox that's bothering me so much at the moment, it's sharing our money with Dropbox.
Thomas LaRock (00:48:55): Yeah. There's that too. Yeah.
Rob Collie (00:48:57): I've started lately. Here's where things start to leak in for me is that the other day I updated Office in general on my laptop. Life is a box of chocolates. You never know what's going to come at you in that Office update.
Thomas LaRock (00:49:10): And you installed the 32 bit version. Is that what you're going to say?
Rob Collie (00:49:14): I insist on at least 33, 34 bits. So I ran the update whatever, it seemed like no big deal. And the next time I clicked the word shortcut on my start menu, it launched a browser, and now I've got Word Online and I'm like. I think I'd launch Word and walked away and I came back and I'm like, that's funny.
Rob Collie (00:49:34): So I closed the browser because that's not what I wanted. I want my desktop win Word. So I clicked the word shortcut again and it launches the browser again. I'm like, [foreign language 00:49:43]. I've recently started, this is unbelievable, using the web versions of PowerPoint and Word. And if I'm using the desktop version of PowerPoint, I'm saving the file to OneDrive.
Fran Reed (00:49:59): Yeah.
Rob Collie (00:50:00): And now I don't have to save the file anymore on my laptop before I can go edit it on my desktop upstairs and I can share it with other people. This online mode you can use Excel desktop in the online mode. It is so cool.
Fran Reed (00:50:17): Yeah. I haven't quite gone there yet. OneNote was like that for me, because OneNote, all of a sudden there's two versions. There's a desktop version, which one am I in? Because I really didn't tell you.
Rob Collie (00:50:29): I still can't tell. I think I've got multiple versions of the OneNote icon, it's the worst. But the OneNote experience is pretty awesome. Even the traditional OneNote experience when the OneNote notebook was saved in OneDrive, they did a pretty good job of steering me into that at some point many years ago. I've experienced multi device seamless sync for my OneNote notebook for a very long time.
Thomas LaRock (00:50:51): So Rob, you guys aren't using Teams yet, are you?
Rob Collie (00:50:54): We are using Teams. That's where all of our meetings are, is Teams.
Thomas LaRock (00:50:58): But I tell you, when you start using Teams and you start sharing files there and then you see it's in the OneDrive space or it's in the SharePoint, it's actually, it's pretty good. It really is good.
Rob Collie (00:51:10): I bet there's a lot of benefit there. I just can't get over how non-consumer fun feeling Teams is compared to Slack. For an internal chat discussion feature, Microsoft just isn't so good at consumer, they're really good at enterprise.
Thomas LaRock (00:51:26): And that's fine. But if you are Microsoft 365 customers you're paying for all of this stuff, you should use it.
Rob Collie (00:51:33): Well, we are. We're just also using other things that we're paying for on the side.
Thomas LaRock (00:51:40): That's right. Yeah.
Fran Reed (00:51:41): I've been mainly Google, but I do like Teams. It's nice. I haven't quite gotten used to totally how it works. I've got some clients on it. I'm not a Slack, these chats like that, I'm just not totally into that stuff. It's noise.
Rob Collie (00:51:55): I wasn't either when it was first introduced at our company, in fact it was actively abrasive to me. I got so angry about it. I almost pulled the plug and said, let's shut this down, but I've come around. I love Slack.
Rob Collie (00:52:07): I think Slack is really, really, really good. Teams has all these arbitrary circumstances in which it launches another window. It's the classic 5% of a product that Microsoft can't get right that really just, it just gets under my skin and it irritates it.
Fran Reed (00:52:22): With Windows 11 you get desktop one two and three and try and figure out all that.
Rob Collie (00:52:27): The times that Teams the icon on the task bar lights up orange, there's something I'm supposed to see, no, there isn't.
Thomas LaRock (00:52:38): My Teams isn't opening up new windows. I don't have colors coming at me.
Rob Collie (00:52:42): We must be on the monthly, the really annoy Rob build. The hardest things about software for me are the things that are off putting, but are actually difficult to pin down. They're actually difficult to catalog. What is it that's driving me nuts. There's certain categories of things like this.
Rob Collie (00:53:00): It takes a lot of energy to distill out and write down. So if someone from the Teams or Microsoft are going to ask me, what is it that annoys you? I would turn around, I'd first just go, I don't even know, but it's just I'm constantly annoyed.
Rob Collie (00:53:14): And so it would take me a couple of weeks of documenting the instances where this happens and why to even give a good answer. But Fran, Slack seems pretty humane. The benefit of Slack is that now I can basically ignore email.
Fran Reed (00:53:30): Yeah. I can't quite do that yet. And I'll take Slack over Messenger. I'm not a big Messenger person. My brother lives and dies with it.
Rob Collie (00:53:38): What I mean specifically is there's nothing internally important happening in email, the vast of the day. Client email, absolutely. It's just I don't have to worry about inbox zero anymore.
Rob Collie (00:53:51): I never really did, but if I can keep my Slack notifications at zero and I find that to be relatively low energy commitment type of thing. Whereas dealing with internal emails after a certain number of years just became way too heavy.
Fran Reed (00:54:05): Yeah. I don't have a staff or anybody around me. It's just client emails or Slack is used for some of my community stuff so it's not as important to read the community chats.
Rob Collie (00:54:14): Yeah. I agree. Slack is internal and it's very, very, very effective at internal. If you're having long running collaborative relationships with other organizations, am I allowed to say this on air? We've dragged Tom into our Slack for one channel, the podcast channel.
Thomas LaRock (00:54:29): Yeah. I publicly quit Slack and wrote all about it. I forget how long ago. So here's the thing for me, Slack was unusable because every external company and community, they're all like, we just have a Slack channel. So I had a dozen or more of these entities and then inside there hundreds of channels and the noise was just, I'm like I'm done.
Thomas LaRock (00:54:53): I had spent all day in Slack looking and trying to find something of value. All these conversations, is there something I should be paying attention to? And I'm like, you know what? I could just turn Slack off. I don't have to be a slave to this app anymore. I can break free. And that's what I did.
Thomas LaRock (00:55:16): Nelson Rob's like, I'm going to add you to our Slack channel. I'm like, motherfucker. Just you brought me back in. I'm actually in two or three and that's it. One's for work, I'll call you for work as well. And then the other one is AWS community. So I'm a member there. I just lurk. I just hang out.
Rob Collie (00:55:36): We keep it clean. We're not super spammy for you.
Thomas LaRock (00:55:39): No, I'm only in a couple of channels there.
Rob Collie (00:55:41): That's the key, right?
Thomas LaRock (00:55:41): That's the key. Yeah.
Rob Collie (00:55:42): We've now solved the world's problems with collaborative team software.
Thomas LaRock (00:55:47): We've collaborated on collaboration tools.
Rob Collie (00:55:50): And Fran just says, pass, which I respect. Lower software footprint. Okay. I'm in.
Fran Reed (00:55:58): Well, and then some people use WhatsApp and there's other ones out there which just don't push me into that.
Rob Collie (00:56:04): Yeah. The number of just basic text messaging apps I have on my phone now. Yeah. I mean, you'd think one would be sufficient, but no, no, no, no, no.
Fran Reed (00:56:13): One app to rule them all.
Rob Collie (00:56:16): Not going to happen. So we've covered the usual ground here. This is going to be a really good episode. I think this going to be a really good conversation. One of those I'm going to really enjoy re-listening to.
Fran Reed (00:56:26): Where do you think about within the Excel ecosystem, you have Excel itself and you have VBA and then you have Cube formulas. Do you ever run into that using Cube formulas with client? I do occasionally use them, it's almost hard coding as far as I'm concerned.
Rob Collie (00:56:44): Yeah. It kind of is. It's been a while since I've used Cube formulas, but I would think that there's probably some future innovation that could happen there that make things a lot better. When we first built Cube formulas, that was on my watch at Microsoft.
Fran Reed (00:56:58): Was it? Okay.
Rob Collie (00:57:00): Grumpy old man voice. We didn't have these fancy dynamic arrays back then, there could be something that they ultimately choose to do. And I don't have any knowledge of them working on or not working on something like this. I will neither confirm nor deny rumors.
Rob Collie (00:57:14): It seems like there'd be something that could be done there to kind of take some of the hard code ness out of it. But it is fundamentally trying to be a financial report type of shape, but because it lacks a repeatable structure, you need headers and then rows and then headers and then rows.
Rob Collie (00:57:30): And those blocks need to be able to repeat and be variable size based on what the data is telling you. So you can't really use it that way. Back when the Excel canvas was our only reporting and visualization option for the DAX engine. In Power BI, we call them Cards.
Fran Reed (00:57:47): Yeah.
Rob Collie (00:57:48): The number one equivalent of Cube formulas in Power BI is the Card.
Fran Reed (00:57:54): I never thought of it that way.
Rob Collie (00:57:55): And that's what Cube formulas did for us in many cases is okay, I've got a pivot table or a pivot chart or maybe a couple of those sorts of things, but then I need five individual cells that tell me just five metrics that are not broken out in this grand structure.
Rob Collie (00:58:09): So it sort of the side dish that went with the main course. And Cube formulas really played a solid role there. So every time you look at a Power BI report, whenever you see Cards being used, because I have Power BI and it's got all of these different visuals, far more sophisticated visual language. And frankly, even though it's complicated at times, I still find Power BI charts easier to use than Excel charts.
Fran Reed (00:58:33): I mean, that's what Power BI is made for. It's a visualization tool. So if I have clients that are just, most clients still are tabular and getting them even in a pivot tables, there's a big deal, but with Power BI it's visual and that's a lot of business owners are more visual than the accountants.
Rob Collie (00:58:50): If we were still stuck and Excel was the only canvas we had, we'd be using Cube formulas quite a bit, the same rate that we were using Cards in Power BI.
Fran Reed (00:58:58): And that's what I use them for. Or if someone doesn't like the way the financial statement comes out looking in a pivot table type of format, which is, the Cube formulas have a place.
Rob Collie (00:59:10): Back when I taught Power Pivot classes specifically, the way that I would describe Cube formulas to people was you use these for reports where the axis of the report are known and the axis of the report do not change based on a refresh. It's for example, you know that you have seven departments in your company and these are the 12 metrics you like to see for each of the seven departments.
Rob Collie (00:59:34): And you like them laid out a certain way, Cube formulas are great for that. You're not expecting to run a refresh and get an eighth department. When you added eighth department to your company, then that's an okay time to go and modify manually the structure of your report to bring in the eighth.
Rob Collie (00:59:48): But a pivot table, you would never do that with customers as the entities, right. Because hopefully you're going to acquire a new customer tomorrow and you don't want to change your report structure on a refresh there.
Fran Reed (00:59:58): Yeah. So the Cube formulas are kind of a tool I keep in my back pocket and use it when I need them. And I try not to use them very much.
Rob Collie (01:00:05): The Card visual of Excel.
Fran Reed (01:00:08): Yeah. So you can use them for headers and stuff like that. But I also discovered when I'm doing a power pivot table, that I can move some of my values over to the row columns and then get sort of a dash 40 type of view of your data if you want to.
Rob Collie (01:00:24): If you looked into the, this is a crazy old one, this is a real trip down memory lane. You can do graphically defined sets in a power pivot table. Have you done that before?
Fran Reed (01:00:36): I tried it, I never really kept up with it, but I am interested in the Power BI groupings. Calculation groups, that sounds like a real cool tool. I have to learn more about that.
Rob Collie (01:00:47): Some of those things that's these new fangled things that Rob doesn't really know much about. I used to say that our company, I was the worst at the tools, but I could still do the job. I don't think that anymore. I think am the worst at the tools. And what I know about Power BI is now so stale relative to what our team knows and what the product is capable of.
Rob Collie (01:01:12): I actually would really struggle to do the job now. I mean, I would eventually pick up all of these things that I've missed, but if you dropped me into the mix today, I would find a number of hard roadblocks almost immediately that I'd have to go and skill up.
Fran Reed (01:01:24): I would think it'd be very easy for you to pick it up though. Because you understand the language and how it's, wouldn't be like riding a bike?
Rob Collie (01:01:31): Kind of. There are some things that sort of speak to me with systemic beauty and I can lean into those. And I think Dax is an example of that. Excel formulas, are an example of that. It's sort of learning physics in high school or something like that. I love that. I loved it. Right.
Rob Collie (01:01:50): And then there's biology, completely arbitrary. Why is the heart kind of on the left side of the body? I don't know. There's no reason for it. It could have been on the right side. That's not how it worked.
Rob Collie (01:02:00): There's so many things about biology that are just, that's just the way it is, it just happened that way and you better remember it. And I feel like there's a lot of new stuff that's coming to Power BI that had to be designed a certain way and it just kind of feels like biology to me. And I'm like, I don't want to learn that stuff.
Fran Reed (01:02:16): No, there are some things you just going, that's nice, but my clients I wouldn't use, I don't have a need for it. So you just have to pick and choose. Otherwise, I would just dive into it and that'd be a distraction. Be like, Squirrel moment. I can do Squirrel really well.
Rob Collie (01:02:29): Your clients probably aren't beating your door down for Row-level security.
Fran Reed (01:02:33): No.
Rob Collie (01:02:33): Now if they understood what it would do for them, if you translated it into real English, some number of them would say, that'd be kind of cool. Not all of them. In some sense, it was easier back in the day when someone would ask a question and be like, yeah, there's no way to do that.
Fran Reed (01:02:47): Yeah.
Rob Collie (01:02:50): Now there's ways to do everything. Calculation groups, the idea of rewriting the same formula 100 times, which is a different base measure, revenue dollars. Okay. Now you've got revenue dollars last year and percent growth versus last year revenue dollars. Oh damn. Now we've got to do that for margin. You go and you duplicate all of these, right?
Fran Reed (01:03:10): I'm working on a project right now doing this other thing.
Rob Collie (01:03:13): We used to have this hack that [Sekaresti 01:03:16] worked on with me, where you could hack the Power Pivot file to bulk insert gobs and gobs of formulas that you've generated programmatically or formulaically. And that was the equivalent of calculation groups. Now we got the real way to do it.
Fran Reed (01:03:34): There's a couple guys I listen to on podcasts and get some of those ideas. One of is Matt Allenton, the other is Sam McKay. Do you know him?
Rob Collie (01:03:44): I do not.
Fran Reed (01:03:45): I think he's New Zealand. New Zealand or Australia. And he does a lot of Power BI. Enterprise DNA is his thing. He does some nice podcasts.
Rob Collie (01:03:54): What is it with New Zealand in Australia?
Fran Reed (01:03:56): I don't know.
Rob Collie (01:03:57): Per capita you just kind of feel like you walk a street in New Zealand and it's all you're doing is running into Power BI and Excel internet celebrities, but it's whole neighborhoods.
Fran Reed (01:04:10): I was at the Excel summit there was tons of them in there from that part of the world.
Rob Collie (01:04:16): We need to do that on a per capita basis, how many MVP? I'm sure Microsoft has these stats.
Fran Reed (01:04:21): Sure.
Rob Collie (01:04:22): Microsoft, we need you to tell us.
Thomas LaRock (01:04:24): That's right. We know people at Microsoft, we can ask this question.
Rob Collie (01:04:27): I don't think it's a sensitive topic. I don't want to know the individuals. I just want to know the aggregates.
Fran Reed (01:04:31): And that's one thing I would like to do as a goal is to cert either in Power BI or Excel, it's just always been something I've wanted to accomplish.
Rob Collie (01:04:42): Are you in the MVP program?
Fran Reed (01:04:44): No, I'm not.
Rob Collie (01:04:45): Do you do a lot of content production in general or just client work?
Fran Reed (01:04:49): Just client work. Yeah.
Rob Collie (01:04:51): I think you would be. From the perspective of product feedback, I think you'd be a very, very valuable member of those MVP programs because you would be such a sharp contrast to the people that they usually recruit. Right? It's all enterprise stuff. Right? The highest end stuff.
Rob Collie (01:05:07): The lower end of complexity has its own challenges, doesn't mean that it's easier. It's different. And I think it would be a really good contrast. But yeah. The criteria for getting in is how much content you churn out essentially.
Fran Reed (01:05:20): What you published. Yeah.
Thomas LaRock (01:05:21): The MVP website. So now this is only people that have chosen to have a public profile. You don't have to have your profile be public, but you do need a profile for Microsoft to review. So anyway, if I go to the MVP website and I filter for data platform, which is what includes Power BI, and Australia, for example, 17 people, now you ready? Let's do New Zealand.
Rob Collie (01:05:46): 17 for Australia. Okay.
Thomas LaRock (01:05:48): So here's the question. New Zealand going to have more or less than Australia?
Rob Collie (01:05:51): Feels like more.
Fran Reed (01:05:53): I think more. Yeah.
Thomas LaRock (01:05:54): Nine.
Fran Reed (01:05:55): Nine. That still a good chunk though.
Rob Collie (01:05:57): Get to USA and then we're going to do population numbers and get sort of back of the envelope per capita.
Thomas LaRock (01:06:05): United States, 79.
Fran Reed (01:06:07): That's it? I would expect a bigger number.
Rob Collie (01:06:10): Are you pulling up population figures for us Tom or should I?
Thomas LaRock (01:06:13): Am I supposed to do that? I have to do everything for you, Rob. Hold on.
Fran Reed (01:06:16): Absolutely, Tom.
Rob Collie (01:06:17): Hey Jamie, you look that up.
Thomas LaRock (01:06:22): All right. Country populations.
Rob Collie (01:06:24): Per capita, is the U.S.A. going to keep up with Oceania?
Thomas LaRock (01:06:27): Well, no, it's more rare for you to be a data platform MVP if you're in the U.S.
Rob Collie (01:06:34): That's what it seems. Yeah.
Thomas LaRock (01:06:34): It is.
Rob Collie (01:06:35): So what's the U.S.A. population roughly?
Thomas LaRock (01:06:39): This says 331 million.
Rob Collie (01:06:40): 331. Okay.
Thomas LaRock (01:06:42): Australia has 25 million. So let's than 10.
Rob Collie (01:06:45): Holy cow.
Thomas LaRock (01:06:46): So multiply Australia by 10 would be 170.
Rob Collie (01:06:48): Actually. We'd be more than 100. So the U.S.A. should be 200 instead of 79 if they were at Australia.
Fran Reed (01:06:54): Yeah. Should be a lot more.
Thomas LaRock (01:06:56): New Zealand has 4 million people.
Rob Collie (01:06:58): Holy cow.
Fran Reed (01:06:59): Wow. That's a huge number.
Rob Collie (01:07:01): Yeah.
Thomas LaRock (01:07:02): If New Zealand was the size of Australia, they would've 45. So you're right. So New Zealand-
Rob Collie (01:07:07): Is a monster.
Thomas LaRock (01:07:08): ... per capita. You're right. So you walk into a neighborhood and you're like, hey, aren't you on some podcast. Hey, I know you.
Rob Collie (01:07:14): Which MVP program are you in? Is what you say. That's the opener at the neighborhood barbecue.
Thomas LaRock (01:07:21): Actually now that you say that, remember I only filtered by data platform.
Rob Collie (01:07:26): So my impression of New Zealand as being a hotbed is based solely on the data platform community.
Thomas LaRock (01:07:31): Yeah. But now I'm curious, how many MVPs do they have overall? 40.
Fran Reed (01:07:35): That's good chunk.
Thomas LaRock (01:07:36): Out of 4 million people, 40 MVPs with public profiles.
Rob Collie (01:07:39): One out of every 100,000.
Thomas LaRock (01:07:41): It's pretty common.
Rob Collie (01:07:42): You can't walk through an airport without bumping into one.
Thomas LaRock (01:07:45): I bet. If you go to an airport, you're going to see almost all of them because they're all heading to some event together.
Rob Collie (01:07:51): No one goes to events anymore, right?
Fran Reed (01:07:54): I still do. I got one coming up, a QuickBooks event in June, which is live. In fact, this producer in the event actually did it during COVID. He set the event up in Florida so he could do it.
Rob Collie (01:08:06): I have really enjoyed this.
Fran Reed (01:08:07): Me too. Thank you.
Rob Collie (01:08:08): Great to speak with you again.
Fran Reed (01:08:09): More than welcome. It's good to see you again.
Thomas LaRock (01:08:11): Nice to meet you Fran.
Fran Reed (01:08:12): Take care.
Speaker 3 (01:08:13): Thanks for listening to the Raw Data by P3 Adaptive podcast. Let the experts at P3 Adaptive, help your business, just go to p3adaptive.com. Have a data day.
Sign up to receive email updates
Enter your name and email address below and I'll send you periodic updates about the podcast.