Raw Data By P3 Adaptive
Falling Iguanas and Fantastic Intelligence, w/ Priscilla CampListen Now:
Today, on Raw Data by P3 Adaptive, our special guest is Priscilla Camp, tech blogger, Power BI enthusiast, user group leader, and data diva. Joining us from sunny Orlando Florida, Priscilla shares her Accidental DBA story. Like so many others, she began her evolutionary process in IT where she learned there are better, frictionless methods to fine tune data. Now, her skills are shared via her I.T. Data Diva blog to help others navigate the process. Also on this episode, we get her take on mixed use of SQL and M to cleanse and transform data. Her advice to SQL users: Always use Power Query for dates. You can find Priscilla on Twitter @ITDataDiva.
References in this episode:
Power Query Date Handling Expertise: Read our blog on consecutive dates in Power Query.
Rob Collie (00:00:00): Hello friends. Today's guest is Priscilla Camp. Now, many of you know her online as the person who runs the Power BI user group based in Orlando. And it was in fact a cold day in Florida as we recorded this. So we went Wild Kingdom for a bit and talked about the impact of cold weather on various distinctly Floridian reptiles. But of course, we also talked about more relevant and serious things, because we do that too. One of the things we talked about was that in the era of COVID and remote work, which we're still very much in, when you say something like the Orlando Power BI user group, Orlando in that case is more the name of the user group, like the T-Birds, than it is its location. Because of course, a very significant percentage of attendees of these user groups now do not live anywhere near the original geographic location.
Rob Collie (00:00:50): We also of course talked about Priscilla's path into the data world. And like so many of us, her path starts with an accident. She was hired to write technical documentation years ago, and about five minutes later, was working on the database. So maybe accident is the wrong word. There are so many of these out-of-necessity battlefield promotions that happen when people are forced to wear multiple hats. We've seen it so many times, the finance people or the accounting people pressed into being defacto IT.
Rob Collie (00:01:21): Well, here's another example, you were writing technical docs, hey, that has the word technical in it. That's basically database management, isn't it? So off she went into SQL, and she devoured it. Couldn't get enough of it, always learning new things. And when Power BI came along, she embraced that with the same level of gusto. Along those lines, we talked a little bit about what it's like to transition from SQL to DAX, or from, broadly speaking, from databases to Power BI in general, does the database background make it easier? Does it make it harder in some ways? Do you continue to translate into SQL in your head?
Rob Collie (00:01:59): That naturally led into another discussion about SQL and Power Query and how Tom should really get in gear, get after it. The world needs Power Query rockstar. It was a great conversation with someone who really knows her stuff and also does a tremendous amount for the community. I hope you enjoy it. So let's get into it.
Announcer (00:02:19): Ladies and gentlemen, may I have your attention please.
Announcer (00:02:23): 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:47): Welcome to the show, Priscilla Camp. How are you down there in warm... Is it warm today in Florida?
Priscilla Camp (00:02:55): No, it is not. It is actually chilly out, and we have a cold front tomorrow. It should be here. It'll be down to a low, I think, of 25 at nighttime.
Rob Collie (00:03:05): 25, 25. Wow.
Thomas LaRock (00:03:10): 25. Wow.
Rob Collie (00:03:10): Yeah. I don't understand how the cold weather gets to Florida because I think it's 20 here in Indianapolis today, and for it to get to 25 in Florida, don't you think it ought to be like a lot colder here? How does the cold air get to Orlando?
Priscilla Camp (00:03:25): Arctic blast. They say there's an Arctic blast. That's what they said a few years ago, and it comes in at nighttime and it's cold. We don't have the big iguanas over here, but in Miami, they do. And they're like, if they drop from the trees, they're not dead, they're just, I guess frozen, maybe.
Rob Collie (00:03:43): It rains iguanas in Florida.
Priscilla Camp (00:03:46): We don't have big ones in Orlando, I haven's seen any-
Rob Collie (00:03:48): That's weird.
Priscilla Camp (00:03:49): ... just the little lizards we have.
Rob Collie (00:03:50): I actually grew up in Orlando. Oh. I went to Dr. Phelps High School. Part of your base camp is UCF. You work there a little bit, you're a professor there a little bit. I've been to UCF many, many, many times. The science fair type stuff and all kinds of nerdy things. A lot of those would end up at UCF in high school.
Thomas LaRock (00:04:07): So I don't understand, when it gets cold, they just go unconscious and then fall from trees?
Rob Collie (00:04:13): Yeah.
Thomas LaRock (00:04:14): That's weird.
Rob Collie (00:04:15): Isn't it? Yeah. Now, of course in Boston or in the Northeast with Utah, when they follow out the tree, they are dead.
Thomas LaRock (00:04:22): So there's no iguanas in trees up here?
Rob Collie (00:04:24): No.
Thomas LaRock (00:04:25): We don't have that.
Rob Collie (00:04:26): Have you seen the videos of alligators sticking their noses out of frozen lakes? It's crazy.
Thomas LaRock (00:04:32): I have not.
Priscilla Camp (00:04:32): Oh, I have not.
Rob Collie (00:04:34): It's a technique for surviving, when the water freezes. the water underneath the ice is actually warmer than the air, so it's actually better off for them to be in the water in the winter than on the ground. But then they have a problem with breathing. You can go out on these lakes and see all these alligator noses sticking out of the-
Thomas LaRock (00:04:51): No, thank you.
Rob Collie (00:04:52): And they're so low energy at that point because they're so cold, you can just walk up and just like grab the snout and nothing's going to happen to you.
Thomas LaRock (00:04:58): No, no, no. Okay, you can, but you shouldn't.
Rob Collie (00:05:02): Well, here's the thing, alligators are not nearly as dangerous as crocodiles. People like Tom who live up north, they don't know any better, they just think that it's the same thing. An alligator is a people-eating machine, and they're not, they're scared to death of you with rare exceptions.
Thomas LaRock (00:05:17): don't want to get close to find out or identify the... No, I'm good. I'm good.
Rob Collie (00:05:21): Yeah, there you have it. But alligator popsicles, you haven't lived until you've seen those videos. So Priscilla, how long have you been in Orlando?
Priscilla Camp (00:05:30): I've been in Orlando since 2003. I'm actually from New York.
Rob Collie (00:05:35): So about 20 years, huh? Are you still deathly afraid of alligators or have you naturalized to the idea that these things are just in your environment?
Priscilla Camp (00:05:44): I'm used to it now. However, my husband and I were hiking last year, and we're just walking along a path. In Florida, there's like little paths of water, and there's the alligator crossing from one side to the other, a big one. We're like, "All right, let's turn around and go. Nevermind." It was a big one over in Lake Jesup, you're probably familiar with that.
Rob Collie (00:06:05): Oh yes.
Priscilla Camp (00:06:05): They have some big ones over there.
Rob Collie (00:06:06): They grow them large.
Priscilla Camp (00:06:07): They're intimidating.
Rob Collie (00:06:08): Yeah. It's another one of those things, if this animal knew its capabilities relative to people, it would be a lot more dangerous than it is. Hey, we're just lucky that alligators aren't nearly as interested in people. Basically, crocodiles, they're water's edge predators that have been eating mammals for their entire existence, so why not eat a human. But alligators are different, they like fish and duck and all that kind of stuff. People are scary, fortunately. You can usually run them off, usually. All right. So let's start today. I know that you run the Orlando Power BI user group. I think there's also some other adjectives for that user group, is that correct?
Priscilla Camp (00:06:45): It's pretty much the Orlando Power BI user group. I have another user group that I've started.
Rob Collie (00:06:49): Oh yeah. What's that one?
Priscilla Camp (00:06:51): It's Data and AI Central Florida. And that one's more towards the cloud and data and the cloud and AI too. And I wanted to start that one up because there's not too much out there. And to me, Orlando is not as ahead in the tech industry, as I would say, maybe you agree with me, as Seattle. So I'm really trying hard to build it up over here and it's becoming well known. I've actually had a colleague write me and say, "My nephew is graduating from UCF. How can he get started in the data industry?" And I said, "Well, I have some great user groups, you can join so you can try to make some networks and learn some things." So it's given a bunch of opportunities to people."
Thomas LaRock (00:07:36): In those two user groups, do you see a lot of the same people show up for both the Power BI and the data and AI?
Priscilla Camp (00:07:42): Yes. When I first started the Orlando Power BI user group, it was before COVID, so we were actually meeting in-person. And it was neat because I did see the same people over and over. They enjoyed it. And the nice thing is, I did have a leader, he's no longer in here, but he worked for construction company so he was able to host it at his location downtown and provide food. So we actually got some really good, I think there was 20, 30 people at our first meetup.
Rob Collie (00:08:09): Wow. First one?
Priscilla Camp (00:08:10): So it was great. But then once COVID happened, things changed and we have more of, I'd say, audience all over the world. I had one lady come in from England virtually and was so excited to learn more. So being virtual has given so much opportunities. However, I'd like to have maybe, when things calm down, I'd like to have a hybrid, maybe tape it as we're in a location with some other people. So I think some people are missing that face-to-face contact.
Rob Collie (00:08:41): Oh yeah. We keep thinking, "Oh, we're just around the corner from being able to do those things again."
Thomas LaRock (00:08:43): And we're not.
Rob Collie (00:08:45): That pesky, pesky virus.
Priscilla Camp (00:08:48): I did bring up a lunch and lean I put at Miler's Ale House, but some people were asking me, "Can we meet up in person?" Nobody signed up though, I think I've only got one person. So the virtual meetings are still going. We have Christopher Webb coming in February when we've got 25, 30 people, but I put the lunch that everyone's been asking about and there's only one person responded. So I just don't think the demands there.
Rob Collie (00:09:13): One person drinking beer, that's never going to go out of style. It's almost funny the way that things have gone with these user groups now, Orlando just is no longer the location, it's like its name, they're just like any other symbol that you would attach because the attendance is open across all geographies. So do you have a sense of percentage wise when you hold a virtual event these days? What percentage of the attendees would live in Orlando versus elsewhere?
Priscilla Camp (00:09:44): I haven't done the analytics on it, but I'd say it's been a lot of different people coming in. My first few meetings that were virtual, we still had a lot of people from Orlando, but now when people introduce themselves, it's been changing. I have a co-organizer that leads it with me, Lacretia, and she just led the last one. And I know we had someone from Microsoft, he presented, and we had Kelly K. there. So we've definitely had more people from around the world come in. And lately, I've actually scheduled a lot of the meetings in the afternoon because it gives more flexibility because I tried 5:00, but it seemed like for the people in Europe, it works better around the afternoon.
Rob Collie (00:10:24): Does it feel like maybe like 50-50 local and remote? It's hard to do that analysis because it's not like necessarily when people sign in for the event, they don't tag their geo, right?
Priscilla Camp (00:10:33): Yeah.
Rob Collie (00:10:33): But does it feel like even splintered? I'm just fishing for a rough order of magnitude out of just my own curiosity.
Priscilla Camp (00:10:40): I would say now it's becoming more of a split, definitely 50-50. I'm definitely seeing a lot more people.
Rob Collie (00:10:45): Ah, that's so neat.
Priscilla Camp (00:10:46): But I'm trying real hard to keep the Orlando people at the same time.
Rob Collie (00:10:50): Yeah. There's that mythical El Dorado city at the end of the road that maybe we can actually go back to doing in person events at the ale house, for instance, that'd be good. I've actually been not to a Power BI, I spoke at a SQLSaturday in Orlando, probably in like 2011 or '12.
Thomas LaRock (00:11:11): Yeah. I was going to mention that I think there are two, I'll say, SQL Server user groups, but they're not that anymore, they're called, I think, Azure data community now. I think they did too, because traffic was so bad in Orlando. The first one was on the north side of town and then there were people on the south side, they're like, "I'm not driving an hour." And so they formed their own group. So they were two user groups, I think, or maybe they just discussed it in Orlando. I know there's a lot of Azure data groups in Florida in general.
Rob Collie (00:11:44): The north side, south side rivalry in Orlando is just tearing the community apart.
Thomas LaRock (00:11:48): It's just so funny. Right? It's like, "I'm not going to drive an hour to get across town." I'm like, "An hour?" And then I go to Orlando for an event or whatever and going for, I don't know, like three miles to get to downtown Disney or something and it just takes forever, it's just awful.
Rob Collie (00:12:05): Yeah. I'm for a lot of fun. But when I lived there... See, I haven't really lived in Orlando in any full-time capacity city since 1992.
Priscilla Camp (00:12:14): Oh, okay. It's been a while.
Rob Collie (00:12:15): Traffic's gotten much, much, much worse. I remember not taking that long to get places, but you wouldn't think Orlando would have such bad traffic problem. The geography seems pretty conducive to roads, it's not like Seattle where you got huge lakes that cut it off and everything.
Priscilla Camp (00:12:31): And it's not that bad right now because of COVID, I feel. Some people say yes. I think it's slowed down a little bit because everyone's virtual now, so everyone's not going into work anymore, a lot of people are just doing it online. Even at UCF, the majority of us in IT, we wanted to work from home, so we're all at home. So none of us are creating any traffic. I think that goes for a lot of our decisions too.
Rob Collie (00:12:53): THat's great. So by day you work in IT at UCF? What's the path that led you there. What did you do in school? Were you always on this IT-leaning path or was there a twist and a turn that led you that direction?
Priscilla Camp (00:13:08): For me, it was a twist and a turn. So I graduated with a bachelor's degree in business administration, but then I had nothing to do, so I was like, "I guess I go back to school for my master's degree." And I earned it in actually interpersonal communication, that was more focused on interpersonal communication in the workplace. So once I graduated, I got married, and then I had a child, and then I was like, "Okay, I need to go back to work now." And this was during the great recession, the housing market. I started looking online for a job, and I was applying for a few things.
Priscilla Camp (00:13:39): I got a call and they said, "Why don't you come in and interview?" And it was going to be writing technical guides since I was a communication major. And I was like, "Okay, well, my dad introduced me to computers at a very early age, so I'm pretty good with them, I'm decent." So I went in there and he's like, "Well, I don't only want you to write technical guys, I want you to work with the database and I want you to do project management." And this is what I do with It. There was a small company, it was called Assist-Rx, and they were growing and they needed people, so I was like, "All right, I'll do it all."
Priscilla Camp (00:14:09): So I was working on it, and for some reason, the database, they opened up SQL Server and they're like, "Here, I want you to do this, this, and that." And I knew nothing. I didn't know what SQL was. And this was 2010, so I just started to figure out myself. And for some reason, I was really excelling in the data side. Project management side, I did okay, but the data side, I was really just flying through it and getting everything they needed. So I took a class online of SQL, introduction to SQL. I learned it. And at the time, they were making deals with pharmaceutical companies, so I started running prairies for the pharmaceutical companies.
Priscilla Camp (00:14:49): Then I got really good at that, and I saw a job open up at UCF, and it was closer to home. I was a teacher there. So I was like, "Oh, okay, let me see if I can get back." So I applied and they hired me as a data warehouse analyst, and there, I got more experience. And at the time, I didn't know about SQLSaturday, any of that stuff. And I had software engineers that worked in the department with me, and they're like, "Why don't you come to this free conference? It's over in Seminole State College. And I said, "Sure." So I went there and then I overheard some of the gentleman talking about SQLSaturday.
Priscilla Camp (00:15:23): And I was like, "Oh, that's cool. I didn't know they had that." So I actually slowly just started getting involved with it. And then I went to a few Power BI conferences when I moved to another department. Back then, Power BI wasn't out, so this was like 2012, 2016. So my first experience was working with SQL Server and SSRS. I don't know if you remember that.
Rob Collie (00:15:46): Oh yeah.
Priscilla Camp (00:15:47): I remember trying to do some stuff, and I was like, "Oh, I'm just driving me crazy. I want to use those visualizations." So when I started my new role in 2016 at a different department, we were going to use Tableau first, so that was our plan. Tableau. And they didn't know anything about our data warehouse. So we did that for two years and then they said, "Well, let's try Power BI. it's better cost wise because we were paying a lot and we're having some issues with them." So we started doing that. I then went to the Power BI conference and started meeting a lot of people. I met Matt Allington and he invited us, believe it or not, a year after the event, it was people were playing pool hoping it was right over there.
Rob Collie (00:16:25): Oh yeah. That sounds like us.
Priscilla Camp (00:16:29): Yeah, that was 18. I remember talking to Matt. Matt was showing me how to play pool. I was on the team with him. We had a good time. We actually won. I think it's because he helped me more, but he was a really great teacher. I really like Matt. And I started to make more contacts and networks. And when I got back, I was like, "You know what, someone tried starting up the Orlando Power BI user group, it didn't go well." I said, "Let me kick it off. So that's how I grew and started to learn a lot.
Rob Collie (00:16:54): You're like, "Yeah, I've got a masters in interpersonal communications, I can do that. That's my music that they're playing." There were two really interesting transitions in that story. So the first one that jumped out at me was how quickly after being recruited to write technical documentation, they were like "Oh, a and take care of the database." Like these things are at all related to each other.
Priscilla Camp (00:17:19): Yeah. They're like, "Here's PowerShell, write the code. Here's the code, copy and paste it in there."
Rob Collie (00:17:25): Yeah. That's technical doc writing, it's just these technical docs happened to be code.
Priscilla Camp (00:17:30): Yeah. And I do a little bit of that.
Rob Collie (00:17:33): It always blows me a way the things that get grouped together with data. We've had so many people we've met over the years and some of them been on this show even, and they were in charge of accounting and someone says, "Well, accounting, it's in the numbers and it's in the spreadsheets and stuff, so you are also in charge of IT now." CFOs that are also the BI director for their company. It's just so much by default. So your first realistic live fire exposure to the world of data was SQL?
Priscilla Camp (00:18:07): Yes.
Rob Collie (00:18:07): Ah, that's a nominal.
Priscilla Camp (00:18:09): It was an SSMS.
Rob Collie (00:18:12): Yeah. So a lot of people's first exposure is heavyweight Excel, and then later some of them bounced into SQL. You started in the deep SQL end of the pool from the get go. What was that like? Did it seem weird to you that the job description had said X and now you were DBA or it's like, "Nope, no big deal, it's just the road's bent and so I turn the wheel."
Priscilla Camp (00:18:35): I just figured it was a job to start and I'd just do whatever they wanted me to do. The first six months was a bit stressful, there was a lot of learning. They told me at the end I did great, but I was also doing Q&A, quality insurance as well. They were showing me there as well how the database would interact with the webpage. They say make a change in the webpage and I would watch the database and go back and forth. So I would say it was like free training and getting paid for it, so it was great.
Rob Collie (00:19:03): I don't know, Tom, does this story sounded all familiar to you?
Thomas LaRock (00:19:05): Well, yeah, I think I agree with you, Rob, that it's tad unusual that their starting point or their diving endpoint would be SQL Server itself. However, it really isn't that unusual and it's certainly not unusual for somebody to show up thinking their job is one thing and then they get told, "Oh yeah, and by the way, you're in charge of that database thing over there." That's very common. It's what we call the accidental DBA, everybody's heard of that before. But I think it's common with a lot of things in IT in general. It's like, "There's some tech thing, you're going to do these two things, but also, somebody's got to do that third thing." And you're new, so you just got to do the thing that nobody else is doing right now that we all know has to be taken care of. And this is a very familiar story.
Rob Collie (00:19:51): I was under the impression that most people who at some point in their lives ended up identifying as a DBA, that databases were their first data exposure.
Thomas LaRock (00:20:02): I don't know about that. There's data, but I think it's data and code more often than the databases. I think you interact with the database, but not as an administrator. These are two different things. I think you might be a database developer or a programmer and you're building an application and you've got to store some data and we're using this database thing over there. For my example, I was a PowerBuilder developer. My first job was, I got hired onto a project with a consulting firm, and the application was built in PowerBuilder. And the guy that hired me just said, "You're smart enough that I can teach you PowerBuilder. That's what we need."
Thomas LaRock (00:20:40): The back end was Sybase. When I left to get a new job as a PowerBuilder developer, the backend was Oracle. It didn't matter, Sybase Oracle didn't matter to me. It was just the data stored over there in some tables and stuff. But then I said, "Hey, DBA sounds like an interesting job. Data doesn't change, I've just administer it." That's easy." And that's what led me into being a role that fell into being more of an administrative thing, which was then Sybase. And then SQL Server was where I ended up. That development role, that functional role, that functional analyst, business analyst, it doesn't matter what tool you're using. You could be just in Excel all day, and Excel might lead you to Access, back in the day, and then Access, well, that's a database.
Thomas LaRock (00:21:25): And maybe we need have that in SQL Server now instead of Access, because that only allows 10 people at a time for whatever reason. And so I think there's different journeys to help people end up working primarily with a database. This is really what happens, is, there's a problem with the database, are you smart enough to go fix it? Do you have the time to go fix it? Can you go figure it out for us? And then, all of a sudden you fix the problem in the database and now you're the DBA. There you go. That's a new job for you.
Rob Collie (00:21:54): Soundsl Priscilla didn't have much choice from the get go, "Here you are."
Thomas LaRock (00:21:57): No, some people don't.
Priscilla Camp (00:22:00): I was like, "Here's all these charts, we'll see what you're good at. All these tasks, we'll see what you're good at and put you in that." And for some reason, what I was really good at, SQL, for some reason came very natural to me, and so did SSMS. And to me, I'm not the best at Excel, that's not my strength. Everyone's like, VLOOKUP, and I'm like, "No, I'll just do some SQL stuff."
Rob Collie (00:22:18): Even the Excel crowd, a lot of the Power BI crowd grew out of the Excel crowd, and still going on to this day. And when you make that transition, you also leave VLOOKUP largely behind, as well as all the new ones. I'm contractually obligated as a former software engineer on the Excel team, I'm supposed to also mention that they have XLOOKUP, which is far better than VLOOKUP, but I still haven't bothered to learn it because I don't do that, I do Power BI relationships 99% of the time when I have a need like that. The other transition that jumped out at me there was the transition from SQL and SSRS into Power BI.
Rob Collie (00:22:59): And I think as a percentage, the SQL funnel, the SQL factory where people are becoming good at SQL, the SQL factory funnels people into Power BI less efficiently than the Excel factory does, other than just some anecdotal, real world experience, that's where I'm really going on when I say that. Having taught a number of people who are coming from a SQL background, having taught them DAX, I found a lot of times, not all the time, but a lot of times, the people who knew SQL really well struggled with DAX because they kept trying to translate it back into their SQL world rather than embracing DAX and data modeling as its own thing.
Rob Collie (00:23:40): These are the ones that ask me questions like, so what kind of Join is that relationship? And I'd be like, "Hmm, no, don't think of it that way. It's not that thing, it's a filter path." To the point where I've even seen some of those people who struggled and struggled and struggled, and they finally relented and stopped trying to translate it into SQL in their head. All of a sudden, they just took off vertically in terms of their skills. Do you have any experience with that? How did you bounce from the SQL world to the DAX world? Because on the surface, it seems like it's easier than it actually is.
Priscilla Camp (00:24:10): Yes, you're correct. When I first started, I had a majority of my data wrangling in SQL Server. And that was because, as I was telling you before, we had actually used Tableau. So when we switched over to Power BI, I was just, "Oh, I can actually do some data wrangling in M and then jump into Power BI and then use DAX." And I do have to say, when I'm doing the data modeling, I see the filters, I'm like, "Okay, so I do know that it's a one-to-many relationship, and then it does a left Join. And it does a left Join, with the From clause, it does this table and that table."
Priscilla Camp (00:24:43): And in my mind I keep one to go back to SQL, so I'll actually open SQL Server, take my tables and do a query and then try to go back and do it. So I have struggled with DAX before, and they say to do a lot of your data wrangling, especially if you have performance issues in SQL Server so you can load into Power BI and it's faster. So I save a lot of my DAX for a lot of the aggregations, because that's where I find that it's very easy. When you're doing the SQL Server, you're more of looking at that row by row, but it's so easy in Power BI for me just to take the DAX and to do the aggregation. So that's what I use it for a lot, but I still do struggle because in my mind I still go back, I call it my first language, I just automatically go back to SQL.
Rob Collie (00:25:25): Again, in my experience, it's a journey. And at some point, you will, just like Neo at the end of The Matrix, you'll see 100% in DAX, and you'll also be able to see 100% in SQL. You'll be able to just completely change goggles. But I think it's completely natural to still retain some of that language of origin. And I never really learned SQL, so I was never burdened by any of that. And I'll tell you, before Power Query existed, because we had Power Pivot with DAX and data model in VertiPaq. And we had all of that for several years before we had Power Query. So the only way to get data reshaped to build the data model, the only way to do it was to get it into a database and then sweet talk a DBA into doing those views for you.
Rob Collie (00:26:15): To this day, I still think that when you have a database as a data source, as you said, I think it's most efficient, it's practically feasible in the social plane if you have access to that database as an admin, it's still very, very, very effective. It's most effective to do a lot of your data shaping there. The great gift that is Power Query, the majority of the gift of Power Query, in my opinion, is that it allows us to chew up things that weren't in a database in the first place, exports and text files and all that kind of goodness. Or when it's from a database where you do not have the cooperation of the administration team of that database, which is also a very, very, very common scenario, unfortunately. But when you have control of both, it's great.
Priscilla Camp (00:26:58): Yeah. And what I really like about Power Query is just the date option. Some SQL Server, you have cache and you have to change it from time to date. It's so much easier in Power Query that sometimes I'll go in there and I'll just do a simple click and change the date and it's so much easier or make this the weekend or the weekday, and I'm like, "Oh, this is great. I love it." So once in a while, I'll go in there and I'll do a little bit of data wrangling in there just because even if I make a mistake, you can click the X button on the right side and just your mistake goes away so easily.
Rob Collie (00:27:27): Yeah. Just peel that layer off.
Priscilla Camp (00:27:28): Yes. So I really found Power Query, especially handle for dates, because I'm off with dates too. I always have to Google, I'm like, "Okay, you want me to do this with the date? All right. Hold on one second."
Rob Collie (00:27:39): Tom has long appreciated the role that DAX plays relative to SQL, and it's a completely different beast and it does completely different things, even though a lot of times, if you squint at it, the two look similar. But what Tom hasn't necessarily appreciated historically is what Power Query can do for him. We've been on a, we're going to call it a multi-year campaign because it's been happening in different calendar years. We're going to cheat and call it a multi-year to get Tom deep into Power Query. So Priscilla, here's your chance, sell Tom on getting deep into Power Query. Tell him why he should vector some of his time over there.
Priscilla Camp (00:28:16): Well, if you're not great with dates and all the syntax of dates from SQL Server, in Power Query, it's an easy click.
Rob Collie (00:28:24): There's there's the killer app, Tom.
Priscilla Camp (00:28:27): You don't have to sit there and Google things and say, "Was it month, year, day, or what's this one? And how do I subtract a day from this?" It's very easy in Power Query. And another thing I love about it is, can you easily do a step, and if you don't like it, you can easily remove it. And another thing too is if you forget what you did, it's always in Power Query and you can just follow the steps of what you've done.
Thomas LaRock (00:28:49): Now, I heard recently you can name those steps, you can manually rename them?
Rob Collie (00:28:53): Yeah.
Thomas LaRock (00:28:54): Yeah. That came up today. I think it was somebody from Microsoft, somebody who should have known better, you would think who knows everything. But they were like, "I just learned this." I'm like, "Yeah, there's always something new to learn." I didn't even think of that, when you were talking to me the other day, Rob, about it, I didn't even think to myself the idea of renaming it, because to me, the name that shows up by default logically explains what happened. But I'm like, "Whoa, whoa, whoa, I could rename this."
Rob Collie (00:29:17): You don't want to learn from me. You don't want to look over my shoulder when I'm doing Power Query, you're going to get the ugliest thing ever. You're going to end up with renamed column seven in your-
Thomas LaRock (00:29:28): Seven. Seven?
Rob Collie (00:29:31): I will rename some columns earlier, rename some columns late, and I end up with just... I can make those default names useless. Trust me.
Thomas LaRock (00:29:38): See, I figured you were talking about the movie, Seven, so that'd be Se7en. Another would be Fight Club, and the names wouldn't make any sense except to you.
Rob Collie (00:29:47): You should see some of the Power Query that's behind Cover Hawk for the football stuff. Because I never got any actual help from people at the company that are really good at it. And it is so ugly. There are places where I renamed certain steps to have the first five characters of the name of the step are like right angle brackets, so it looks like a header. Here's the part where things really go off the rails. It's gross. Every time we add a column to the original data set in Cover Hawk, I have to go into five different UNPIVOT steps and name that column in the UNPIVOT or everything dies.
Rob Collie (00:30:26): I think it's an UNPIVOT other. It's shameful, is what it is. Tom, you would do a much better job.
Thomas LaRock (00:30:32): I'm not sure about that.
Rob Collie (00:30:33): I'm sure Priscilla already does. Just keep me away from the Power Query. I make a mess. Did you hear that, Priscilla? Tom's been getting a little bit of Power Query out of band. He's running into it elsewhere.
Priscilla Camp (00:30:44): Oh, interesting.
Thomas LaRock (00:30:46): Rob's making a statement, but let's fact check it. It's not like I'm just happening to run into it. Rob calls me one day and says, "Hey, let me show you something." It wasn't like I was on my way to Starbucks and somebody had a laptop open and they say, "Can you help me with this Power Query?" I'm like, "Actually, maybe I can." No, it's really been shoved in my face between Rob and Alex and all these other people who are like, "Oh, you've got to learn this Power Query stuff." "Yeah, I'll get to it."
Rob Collie (00:31:13): So Priscilla, I would like you to schedule a session of the Orlando Power BI user group entitled From SQL to M, with Tom LaRock. Let's just put it on the calendar.
Priscilla Camp (00:31:26): Sure. Yeah.
Thomas LaRock (00:31:26): Wait, am I presenting this?
Rob Collie (00:31:29): Oh yeah, you're presenting. It's like the scene in Red October where Alec Baldwin asked him, "Who's giving the briefing?" And he goes, "You are."
Priscilla Camp (00:31:38): We could always do something in the summer and you'll have time to prepare.
Rob Collie (00:31:41): That's right. Unite the clans, Tom, commit.
Thomas LaRock (00:31:46): From SQL to M?
Rob Collie (00:31:48): Yeah.
Thomas LaRock (00:31:49): I guess.
Rob Collie (00:31:49): Well, or whatever, we don't have to imply that you've left SQL.
Thomas LaRock (00:31:53): No, I was just going to say, can we also say with Tom and special guest star, Rob Collie?
Rob Collie (00:31:59): You don't want me there, not on Power Query. If you need just like a grumpy foil, you're going to be there to advocate for it, and I'm going to be like, "Hey, let me show you how to really muck things up." Anyway, it's a good pitch.
Thomas LaRock (00:32:10): Well, it was a pitch.
Rob Collie (00:32:11): Yeah, that's the forcing function we need. That's actually funny, because of COVID, I've been deprived of an experience that I really, really enjoy, which is running into people in coffee shops or whatever, where you look over their shoulder and you see them doing sometimes that's like, "Oh, my people." I haven't run into anyone using Power BI in the wild like that. But I think the reason for that is, we haven't been out and about for two years. If I've been on airplanes at the same rate or in coffee shops at the same rate, by now, I would've seen Power BI over someone's shoulder on a laptop. It's gone mainstream enough in these last couple of years. I have yet to do that. Priscilla, have you seen anybody in the wild with Power BI?
Priscilla Camp (00:32:51): I have not seen anybody in the wild, but I can tell you that I get many contacts on LinkedIn saying, "I just started Power BI, can you help me?" Or, "I'm going to make this go live, can you help me with it go live." I get so many messages, it's unbelievable. Either to work for them or for a job, just anything. That's where I see it. And it feels like the wild somewhat because these people just find me and they contact me.
Rob Collie (00:33:16): The internet connection. I'm really looking forward to that first time like walking through somewhere and like seeing the unmistakable look of Power BI reports or table view or something like that, and then immediately accosting that person and scaring the hell out of them. People are not expecting that.
Priscilla Camp (00:33:38): We did have in-person actual SQLSaturday Orlando back in October. So I was able to meet some people there and they were really interested in artificial intelligence and Power BI. That was a strong interest. When I spoke about it, I had pretty much a full room. So I was really shocked.
Thomas LaRock (00:33:58): I'm interested. Tell me more.
Priscilla Camp (00:33:59): What I focused on was natural language processing. My supervisors came up to me one day, and what I'm responsible for is IT services, how quick we're servicing faculty, staff and students. Once we service them, we usually throw over a survey and say, "How well did we do?" And you can select a face or not. And usually, if you want to go further than a survey, you can leave comments. And what they wanted to do was pretty much see what these people had to say and they wanted me to give them some feedback on it. So we did the natural language processing, but I only had the pro license. So we had to look over at trying to figure out how to do it, not using the premium.
Priscilla Camp (00:34:40): They came up with, it was a premium per user, but at the time, it was only like a month. So I didn't have enough time to do a side project. So I did run it and I actually used it through Azure. And what I did was, I followed something online where you would actually grab a function they wrote, like an advanced editor M, paste it in, and then you'd put your endpoint from Azure and your key, and you'd get it to run. So I made a presentation showing people if they didn't have premium, how they can actually use pro to get the same information, just a little bit harder, but you could still get it.
Priscilla Camp (00:35:16): And what I did was I took data from, I think it was Kaggle and it was about Universal Studios, because I live in Orlando, I had to do something. I actually had the machine process the NLP for the reviews that people gave, and people really enjoyed that. It seems natural language processing is very, very popular and it's a great option, I think, in Power BI. But not only that, but what people love too is that word cloud, those key phrases, they love that. So if you know to leave a comment, they would just pick out the most important words, and it was very well received by people. It's an upcoming thing, I think.
Priscilla Camp (00:35:51): I got my certification in AI Fundamentals. It's hilarious, on that section of NLP, I got 100% correct. It's said I had studied it so much.
Rob Collie (00:36:01): By the way. My first job ever was at Universal Studios.
Priscilla Camp (00:36:04): Oh, okay.
Rob Collie (00:36:06): Dr. Phelps High School is literally in its backyard. It's like a couple hundred feet from the fence. Do you remember from that presentation, any themes that jumped out about Universal Studios? I'm deeply curious.
Priscilla Camp (00:36:18): Yes. Harry Potter came out a lot on the positive side. Everybody loved Power BI, that was the big thing. What I found interesting was, when there were negative comments, you would see Disney, and it would be like, "This is horrible. Learn from Disney or go to Disney."
Rob Collie (00:36:33): Interesting. By comparison to Disney.
Priscilla Camp (00:36:36): Yeah. It was very interesting.
Rob Collie (00:36:38): No one was complaining about, say, the cleanliness of soft-baked pretzels being sold on the carts in the park or-
Thomas LaRock (00:36:46): Not since you left.
Rob Collie (00:36:47): Not since I...
Priscilla Camp (00:36:50): They complained about lines, the lines were long. That was one thing they did not like.
Rob Collie (00:36:55): Yeah. I had nothing to do with that.
Thomas LaRock (00:36:57): I have a question about the Power BI ML thing though. Let's say I get all this sentiment loaded into Power BI. So I would imagine the data, everything's encapsulated inside Power BI, but the hook in, when you mentioned to Azure, where's the model? Is the model something I've built and deployed to Azure that I then just point to? I think is what you described. Or can I actually do it all inside Power BI? Does Power BI have certain algorithms baked in that I can just use?
Priscilla Camp (00:37:26): It's in Power Query, actually. When you go into Power Query on the toolbar, they have three buttons. I don't have it memorized, I'm sorry, so I can't give it to you specifically, but it's-
Thomas LaRock (00:37:37): You're certified. What do you mean?
Priscilla Camp (00:37:39): It's ML, machine learning. I want to say computer vision, and maybe it's text analytics. But what happens is you put your data in there in Power Query and you can put an Excel file and upload it. And once you put it in there, what I do is I clean it, I like to only keep a few columns. And I actually call it from there because I run that function, and that function has the Endpoint and API in it to access Azure, and then it calls it and it brings back the data and that column will come in.
Thomas LaRock (00:38:09): Okay. So it calls something in Azure, but it doesn't necessarily have to be a model that you've built, it's just something that Microsoft makes available that Power BI can call into. So they've already built something that just does regular sentiment analysis, I call it regular. So I think I understand how it works.
Priscilla Camp (00:38:27): In Azure, you have to go to cognitive services, which is like pre-train machine learning and can set up the text analytics there. And I think it's changed recently because I just did a presentation on it and I was looking up something, and I think they changed it from text analytics to language. I saw it real quick. I just got my certification in Azure Data Fundamentals, I've been doing a lot of stuff. So my focus has been more of that right now, but I went back and recently checked and look like something had changed. But you would go there anyway and put text analytics, set that up, get your key in Endpoint and then you would run it that way.
Priscilla Camp (00:38:58): And I believe you can get a free 100 credit hours maybe a month, I'm not in entirely sure. You could even test it out for yourself.
Rob Collie (00:39:07): Where do I call this from in Power BI again? What's the name of the environment that I use to call out to this? Is it power... Oh, Power Query. Tom, you should really take a look at that. That sounds like it's right up your alley.
Thomas LaRock (00:39:20): I know, but I'm spending so much time trying to learn M that I just don't have time for the Power Query stuff.
Rob Collie (00:39:27): Don't tell them.
Priscilla Camp (00:39:28): Well, there's the advanced editor part where you can actually write the M yourself instead of the computer doing it in the steps. It's pretty cool.
Rob Collie (00:39:38): Even I used advanced editor, but I don't write from scratch in there. That's the place where I copy paste or if I need to delete seven steps all at once and I don't want to keep clicking the X and the okay, the X and the okay, I'll go in there and delete them in bulk and then stitch the two lines back together so they refer to each other that have lines that are gone. So even terrible Rob uses advanced editor view. Tom, I'm sure, loves him some advanced editor. That's his place.
Thomas LaRock (00:40:06): Yeah, that's right.
Rob Collie (00:40:07): He just says yes. He's learned.
Thomas LaRock (00:40:09): Not just advanced, I want pro advanced.
Rob Collie (00:40:12): Oh, well, you have to pay an extra $10 a month for that.
Thomas LaRock (00:40:15): Yeah, right.
Priscilla Camp (00:40:17): Premium per user. And it's nice, 10 bucks is better than the whole premium plan. It should be expensive, so it's a nice workaround that came out with.
Rob Collie (00:40:27): A lot of organizations will only have a small number of people that would need such a thing. It's a nice pricing model, very humane. I like things that are humane. So Priscilla, you also have, I think, you're on website ITDataDiva, is that right?
Priscilla Camp (00:40:39): Yes. Yes, that's correct.
Thomas LaRock (00:40:39): Data Diva, that's nice.
Rob Collie (00:40:40): ITDataDiva.com?
Priscilla Camp (00:40:45): .com.
Rob Collie (00:40:46): What do you do on IT Data Diva?
Priscilla Camp (00:40:48): I've done a lot of blogging of what I've done at work, a lot of problems I solve. So what I started to do was actually show, I think, I do on my own and just share it with people, tips and tricks. And I have also put a lot of this on the Power BI community, I've been blogging for there. I've slowed down a little bit just because I've been working a lot of cloud stuff lately with the Azure, tips and tricks as well. I've came out with one specifically about how to use the relative date filter to filter stuff. So I've done a few things. I've also wrote little bit about DAX. There was an issue with the sort by column and you would actually have to write in the DAX. It drove me crazy for a few days and you actually have to write in the DAX certain type of keywords in DAX all selected.
Priscilla Camp (00:41:31): You would have to write that in there and include it because otherwise you would be like, "Why is the numbers not coming out?" So I've focused a lot on just problems I ran into, things I thought about, little tricks. I've also wrote about how the past Power BI, I think they're going to change it to Power BI Associate, right now I think it's Data Analyst Associate. I've also parsed that, I wrote a bit blog about how to parse it. I have a few things in there that's different. And I also came out with something when I first started blogging, it was about how to get started in Power BI because I'm a person that I ask a lot of questions, I'm like why? And I always like to know all the steps and things.
Priscilla Camp (00:42:10): So I came out with this documentation, or this little spreadsheet I created in PowerPoint with a table and it would show you how to get started Power BI, all the Microsoft links. So I put it there. And believe it or not, I had that on Twitter and I leave it on there, it's gotten over, I want to say 22,000 views. So popular that was.
Rob Collie (00:42:28): Awesome.
Priscilla Camp (00:42:29): Yeah. I never had such great feedback. I guess people don't know how it started because on LinkedIn, people were sharing it like crazy. It was a really big hit. I could say that's probably the most successful thing I've ever written. When I go look at the statistic, especially in the Power BI community, I see that's the number one thing that people are interested in.
Rob Collie (00:42:48): That's cool.
Priscilla Camp (00:42:50): Yeah. It's funny because I've had a few people try to mimic the same thing or somebody once tried to take credit, and I knew it was so well liked because people were trying to redo it.
Rob Collie (00:43:00): When we first published the first version of my book, Bill Jelen our publisher MrExcel, he said, "Now, look, we're going to put an electronic version like an e-reader version of your book out on the market and it's going to get pirated. Don't sweat that, it's a mark of honor." For a little while there, it wasn't showing up on any of the torrent sites or anything like that, all the freeware stuff, it wasn't being distributed that way. But then one day it did and we're like, "Ah, wow, now we've arrived." Mostly we just left that kind of thing alone, but there is something different about having a blog post or something, you put a lot of effort into just completely stolen and relabeled as if someone else wrote it. Those happened a few times as well. Ugh, the blog post stealers are the worst.
Priscilla Camp (00:43:43): Yeah. I guess you've done a good job if someone's wanting to take it, but it's also like "That's mine."
Rob Collie (00:43:49): Yeah. It's very personal.
Priscilla Camp (00:43:51): Yeah. It's happened with that one specifically about two or three times. I don't know why. And I've got a few people who gave comment, "Oh, I thought about that."
Rob Collie (00:43:58): Well, people can see what's getting traffic. They have to do a Google search and see what ranks highly and then say, "Oh, that must be very popular." So then that's the thing to steal. It's not hard to find the things that are worth stealing. What's even funnier is when people steal things and they end up resharing things, they don't even really understand what they're resharing because sometimes there's little Easter eggs in there that very clearly identifies them as not the author or famously, I used to make fun of in a good natured way of Donald Farmer from Microsoft is looking a lot like Tommy Chong. And I would post pictures next to him before and after like, which one is it? Is it Tommy or Donald?
Rob Collie (00:44:37): And so someone made a post about Donald one time and used the picture of Tommy Chong because the Donald Farmer image search had led them to our blog. They were just like, "Well, that one looks good." And people steal, they aren't as thoughtful as they normally would be maybe. One of the things we've talked about a few times on the show lately is that now that the community has really taken off around Power BI, somewhat paradoxically, it's more intimidating to get into it because the level of expertise that's on display everywhere is so far down the road now. There's very, very well-read articles on, for instance, I'm not picking on these articles, I think they're good articles, it's just for a beginner, the first thing you see is why you should be using ad columns instead of summarize in your DAX or some subtle benefit to going this way.
Rob Collie (00:45:27): It's like, "Folks, I don't use either of those. I'm some number of years into this." For the beginner, I think it's actually the wall now that you have to scale to get in, the barrier to entry. Now, paradoxically, looks higher than it used to even though adoption has gotten a much greater sense of traction in the world. And so we need those how to get started entry points, like the really gentle, humane onboarding ramps, even more now than we might have five years ago. So definitely, keep at it. Maybe steal your own blog post.
Priscilla Camp (00:46:06): And I agree. Presentations that do get a lot of hits, even before COVID, when I was presenting at SQLSaturday, the most interest is how to start. I created one presentation, Are You Power BI Newbie? My son used to love to say the word newbie. So I just used Power BI Newbie, and the class was full. I had people running out of chairs, we had to get them seat. It was popular. People really want to know how to start. And I think like you said, they feel intimidated when they see these posts or people are using the fancy words. I've been told a lot, people like to work with me because I just talk normally and I don't use anything fancy and I explain concept slowly.
Priscilla Camp (00:46:43): And I understand, when you're new, it's hard at first, you really have to focus and think, I always try to be very patient with the new people even when I'm teaching, even when I've train other people, I always say, "Hey, I won't look over your shoulder." Because for me, I hate when people look over my shoulder when I'm trying to code or something. So I try to be very easy with people because I know how they feel, I've been in their shoes.
Rob Collie (00:47:07): Yeah. I can't write difficult DAX when someone's watching me. I remember when I used to do lot of the consulting at the company myself, I'd find myself in situations working with someone remotely or even in-person. Let's say I was visiting a client, most of the work would just flow, it was really straightforward, easy stuff, but then every now and then we'd run into some challenge where I had to really stretch my brain a little bit and I would invariably end up looking at them and saying, "Hey, why don't you just go get like a cup of coffee or something? Just give me 10 minutes, 15 minutes because if you sit here looking at me, watching me try to type this and struggle through it, we'll be here an hour from now, but if you go away, I'll be done in 10 minutes."
Rob Collie (00:47:46): And it was real. They go get their coffee or whatever and I'd be done when they got back and off we go. But yeah, there's something about thinking about the audience while at the same time trying to crunch something difficult.
Thomas LaRock (00:47:58): I've certainly experienced that, I don't think I was smart enough to ask somebody like, "Go get a cup of coffee." For me, the worst was just as things were getting, let's say more distributed remote, for example, let's say we have an office that's 800 miles away, they can't just walk over to your cube. So they call you and they're like, "Where are we at with the problem?" You're like, "Well, it's not fixed yet. I have no idea what's wrong and I'm working on it." "Okay." And then 10 minutes later, phone rings again and you're like, "Okay." And by the third time, they're like, "I need to know where we're at." And I go, "Where we're at is if you stop calling me every 10 minutes, it might get fixed faster."
Thomas LaRock (00:48:38): And then there's the email to my manager, "We need to talk about Tom." My manager's like, "What do I need to know?" "You need to tell him to leave me the fuck alone so I can try to figure out what's wrong. Because every time he interrupts me, I go back to square one trying to figure out where I was at in my train of thought." And I'm like, how do you not get this? Certainly as humans, we have all been interrupted by another human constantly and we say, "It'd be really great if you just get the fuck out. But yeah, this person as a human will then do it to me and I'm like, "But you know you're making in this last longer, yet you still do it. I don't get it."
Thomas LaRock (00:49:14): So in the distributed world, I find that the people picking up the phone or they send you an instant message where we at, where we at, where we're at is, maybe it's easier if I didn't answer the phone, I guess, but anything, I'm not at my desk, I don't know
Rob Collie (00:49:28): The mean time it takes to get into the zone, writing something difficult is like 12 minutes, and you have colleagues calling you at a meantime of 11 minutes.
Priscilla Camp (00:49:39): I've been before when I was transitioning from different positions of asked questions in interviews and they want you to answer them right on the spot and you're just like, "Wait, are you saying this? Are you saying... " And they just stare at you. And once or twice I've done a rookie mistake just because I just couldn't take the pressure anymore, like, "Okay. Just leave me alone."
Rob Collie (00:50:00): It's difficult. Our Power BI interview at P3 is a real challenge like that. You have to solve a series of pretty challenging problems, but it's open book, it's offline. We don't do this to you face to face on a Zoom call. We make it realistic. You wouldn't be working that way in the real world, why interview that way? On the other side of that challenge, if you get through it, we do ask you some questions to explain it. So if you just wholesale stole the solution from somewhere and didn't understand it, that'll become pretty clear pretty quickly. If it's not your solution, that's going to jump out at us.
Rob Collie (00:50:37): So it's just a really unrealistic test of whether you can be successful. Can you do it while someone is breathing down your neck? No, the interviewer probably couldn't either.
Priscilla Camp (00:50:49): It's definitely a test.
Rob Collie (00:50:51): Even with that question, if they didn't already know the answer, they probably flip the tables and they wouldn't succeed.
Priscilla Camp (00:50:58): So how much pressure can we put you under and still make you think rationally?
Rob Collie (00:51:03): I don't know, that's not a great test. So working in IT at a relatively large organization, how many people do you think are impacted by things that you and/or your department work on? Are students ultimately touched by some of these things? Is it faculty and staff? If you think of the non-IT people that you ultimately end up servicing, think of them as like your constituents, how large is the constituency?
Priscilla Camp (00:51:29): It's pretty much the whole crowd. So it's mostly staff and faculty, of course, but students, because our IT services branch over to infrastructure, telecommunications, all the different departments, we think what else, strategy and planning we have. So it's hitting everything. And one thing that's especially important that they're trying to focus on now is the service deck and telecommunications, how fast are we servicing the students? For instance, if they need their password changed, how fast are they doing it? Or if another issue arises, are we fulfilling that incident in our SLA, our service level agreement time. So it's affecting the whole campus.
Priscilla Camp (00:52:05): And the hardest part to me about is it's just the culture, trying to get people used to looking at these Power BI reports and trying to get them to understand them so that they can service the community better. Even when I read a lot of the survey responses, now we don't get a lot of feedback because they say people get survey fatigue. So our responses are very low, but we do do well, but I do see complaints about our network or service there. I also see some complaints about different areas. So we do cover some areas that are not under UCFIT with transcripts and I see people upset about that. So we're covering pretty much the whole campus. And we just had leadership change too.
Rob Collie (00:52:46): Tens of thousands of people ultimately. Now let's focus in for a moment very specifically on the Power BI reports. We talk about getting people to custom to using them, using them as opposed to what? Were they doing something else to get data before, or is this the first time that they've been given any data, ongoing data feedback or information? There's both sorts of scenarios, right? I've been doing it this way, I've been looking at this system and pressing export and doing it in Excel or whatever and now you're telling me that I should use something different. There's a change in system essentially. And there's the other situation where they weren't doing anything data driven and now you're trying to encourage data driven.
Rob Collie (00:53:28): I'm sure you're probably running into both, but let's dive into that world. That's a really interesting, I think, dynamic, getting people accustomed to these things. Can you tell us anything about that?
Priscilla Camp (00:53:36): Yes. When I first started, we had disparate data, they were all in Excel spreadsheets it wasn't uniformed, there was people in telecommunications, then there were people in another department using it. So no one was aligned with each other. When we came in, my supervisor and I, we had to align it. So he had the data and we have a service that we use, ServiceNow, and people were running the reports on ServiceNow to get the data. However, it was hard because ServiceNow didn't have all the ETL and everything. So I started creating these reports, but then we were running into other things where people would do reporting from ServiceNow and not properly use it the right way.
Priscilla Camp (00:54:16): So they would take the data and show their own story instead of really showing the overall story. So we were facing that issue. We had multiple issues going on. What we did was, when we first actually started using Tableau, it was really hard to get people interested because it didn't really work with the other Microsoft products and we were a Microsoft shop. So we switched over to Power BI. We were able to put the reports in Teams and then that's when it really started to kick off because people were really happy that they can just go to their channel, do a click and there's their report. And that really helped with people adapting to it and looking at their reports.
Priscilla Camp (00:54:53): People sometimes run into issues and I always go to the Service, I'm like, "Okay, let me see the workspace and where your issue is." And they go, "No, no, no I'm in Teams." So one big thing I think that really helps with the culture is just being able to embed those reports in other products such as Teams, and that's really been taking that off big time. And now we also have leadership with the crazy about data, actually enforcing that more. But I would say the number one major change was just being able to put that into Teams for people. They don't even know the Service exists. They're like, "Oh, that's the Service?" I'm like, "Yeah." They don't even know what it is. You say workspace, no idea. They don't care. They're just happy it's in Teams.
Rob Collie (00:55:34): That's a cheat code, if they're already there, put it there.
Priscilla Camp (00:55:37): Yep. So it's been highly adaptive because of that. And we still have some people who don't know how to do things. We're slowly trying to mature our data culture at UCS.
Rob Collie (00:55:48): So a couple things there, first of all, it's come up many times on this show, but it always bears repeating, any line of business system like ServiceNow, or whatever, name any piece of like business software that performs a particular purpose, it's going to have built-in reporting. And at least 99 times out of 100, that reporting is going to be inadequate. It's just going to be completely inadequate, either you're exporting it to Excel to add calculations that weren't there or also to enrich it with data from other silos, these line of business systems are uni-silo reports. Well, the world that you live in runs across many systems and like the big picture turns out to be important.
Rob Collie (00:56:29): And so Power BI's ability to splice between worlds with the data model is just amazing. It's just jaw dropping magic. Anyway, so this theme of Power BI replacing these siloed exported spreadsheeted things, it's a sea change coming for the entire world, and we're still very, very, very early in that transition. Now, those departments that you're talking about that were getting by poorly, not because they're bad at it, but because they were playing a very difficult game, relying on line of business, exported reporting is really, really, really primitive, it's just hard to get anywhere with it, but you knuckle down and you make good, you do your best.
Rob Collie (00:57:13): That means that they were involved in the creation of their own analytics. It's primitive and it's clumsy, but that department had least one person, usually an Excel person, not always, but usually, that was serving as shadow IT of a sort. Now, with the centralized Power BI model, there's certainly some tremendous improvement there, but there's also this like loss of satellite power. They're no longer directly involved in the creation of it. If you run into any of that where they're like, "Hey, we gained something, but we lost something." Do you run into that at all?
Priscilla Camp (00:57:53): We do have people, I would say that still want to create their own reports. I try my best to be humble here. I just know how to do the stuff and they're like, "Oh, you're so awesome." And we had a few people that wanted to create that on their own, but once I start doing it and they give me a mock up, I can usually do it really fast. And then they're shocked, they're like, "Oh wow, that's so fast." So we haven't run that too much, but we also do have a lot of governance processes behind our reports, which make our reports a lot more trustworthy. And you're probably going to get a kick out of this, but I have my reports and I run them.
Priscilla Camp (00:58:28): And my supervisor, he has his reports from before I was there in Excel, he has all this code and we actually run our reports. And every month we look at our reports, we compare them and we make sure that they're 100%.
Rob Collie (00:58:41): Yeah. It's good to have that completely separate system validating it, at least some of the top level results. If either one of them drifts, you're going to notice pretty quickly. I was fishing for a number of things with my question without going right after it. There's something really important that you said, there are people in those individual departments that are now receiving Power BI reports who were probably were involved in the creation of the original Excel reports and the exports and running the line business reports and all of that from before.
Rob Collie (00:59:09): Essentially, what they're saying when they come back to you and ask if they can create reports, what they're really saying to you is that these reports that you've made for us are great and either they're missing something that I used to do, like a column that I used to really rely on that I never told you about and so you never included it in the report or something. Or just as good, even better really, is seeing what you've done has woken up a new curiosity, a new ambition that I never had before. It'd be really great if I could address that ambition. So they've got a new need that isn't addressed by your existing set of reports, or maybe not even addressed by your existing data model.
Rob Collie (00:59:47): So they come to you and something really important happens which is, you listen to them and you help them get what they need. And the shortest path for you is very often, it sounds like, to just go build it, to just would do it for them. And so listen up everybody out there who's in a similar role, if you are able to be responsive and helpful like that when they come to you, they will keep using your reports. They will stay bought in to this culture that you're trying to create. If however you stonewall them and you say, "Nope, that's not how it works. The reports don't do that, sorry," or more innocently, it just takes you a long time to get back to them. There's a take a number dynamic, they have to wait a long time.
Rob Collie (01:00:34): You will invisibly recreate the export to Excel culture and it'd just be that your Power BI reports are now being used as the export point, as opposed the original line of business reports, you will lose traction over time like that, but also you will have missed the opportunity for a far more efficient organization. Very often this happens through no fault of anyone, it's just that the Priscilla in the story, the person playing your role has too many departments that they're responsible for. And just in terms of human hours, they couldn't possibly keep up with the pace of innovation and the demand. And Power BI only wakes people's hunger up. It's not going to slow down.
Rob Collie (01:01:15): The pace of innovation should increase in this environment if you're doing it right. And so if you can't keep up, that's when you to start empowering a small number of people out in the departments to act as you with a certain set of sandbox, and rules, and restrictions, and all of that. But one way or another, that business need out there at that next level out, it's going to get met. The question is, is your central system going to be an integral part of it or is it going to go off into the shadows again? It's going to get met either way. Priscilla, definitely sounds like you're meeting it the right way.
Priscilla Camp (01:01:52): We are. And we do have different processes, governance that we've been setting up. So we do have some people that do create reports, not just me in our project management office. And what we've done is we created, it's there a governance, but I more like to say it's BI governance, because I feel like after you've created the report, you've done all the measures, calculated the columns, I just feel like that's more BI not data, but we do have our governance team though, actually look at these reports. Now, we're not really worried about people who just want to make their own reports and share it within their team, that doesn't worry us.
Priscilla Camp (01:02:23): What more worries us is when they want to share throughout campus, like with faculty, staff, because then that's our numbers. So what we started was this governance program and we wrote some policies and procedures to follow and usually I will have people come and we have like a little committee and someone will say, "Here's this report I created," and they'll show their visualizations they created and maybe some of their measures or calculated columns, anything. And we actually review that together. And as a committee, we'll actually approve the reports or not. So that's been another way that we've been giving people more hands on experience.
Priscilla Camp (01:02:57): And I've had some create some really nice reports. And the nice thing about my blogs is I'll tell my coworkers, "Just go look at my blog that I wrote." And it's helped them a lot. So we've really gotten forward with that that our reports now, since the committee looks at them, even then they're more trustworthy. And we have a little stamp that we put at the bottom of Power BI reports and it'll say UCFIT Business Intelligence Committee. And it will actually show that it's been proven and went through this process.
Rob Collie (01:03:22): Is it a digital blockchain signature?
Thomas LaRock (01:03:28): It's an NFT.
Priscilla Camp (01:03:28): It's a UCF logo that we have, a nice little logo and it's UCFIT Business Intelligence. And it just helps make the report when they know that it's out there, that it's actually been validated and went through our committee. And that's helped a lot, people trusting your data and saying, "Okay, we can trust this, we know it's been through this process.
Rob Collie (01:03:46): I'm going to start a black market site, a dark website that sells that logo.
Priscilla Camp (01:03:51): The hard part is, I can tell you this much, the hard part is, it's getting people to check their data monthly. I've noticed this in a lot of different places, just a data culture. People understand, whatever you're putting into a website, whatever values you want to put in, especially if there's no control, the programmer doesn't have any control on that stray or that column, you put anything in there. And then your code goes to garbage because you expected it only to accept this type of day format and now you got this day format in there.
Priscilla Camp (01:04:18): That's been more of a difficult thing for me to help people understand because some people aren't technical, so they're just like, oh, "Hey, I'm just going to pull whatever in the website." And they don't understand the whole thing that's going on underneath.
Rob Collie (01:04:27): Generally speaking, how many people are you aware of within your universe at UCF who are writing DAX? Some people are writing their own reports, but there's a different level when you're writing DAX or even data modeling. That's the real hub thing. You certainly are doing that. Are there colleagues within your department who are also doing this? And then out in those satellite groups that we were talking about, are there any people out there that are doing DAX?
Priscilla Camp (01:04:55): In my department, I would say they do basic DAX when you sell them measure and then you go and you put it on a field well, and you try to make an AX software and then it does it automatically for you. They've done that. Now, the other departments, since we're so big, I don't communicate with people frequently. Now, I do know they try to create a Teams channel with all the Power BI users, but I really don't think we have any serious DAX writers. I can tell you, I've had so many people from different departments come to me and ask me for help, but I can't even do it for them because my workload gets so high, and then it comes to, who's going to pay for me to do that. So I don't really think we have any serious expert DAX writers.
Rob Collie (01:05:36): Oh, that's coming. And the thing is, they're probably out there in a couple places you wouldn't expect, in places that you wouldn't even have any direct exposure to, but I guarantee you, there's some research labs at the university who have stumbled into it. Many, many years ago, I wrote a series of blog posts about my neighbor at Case Western University who was running a neuroscience lab, running all kinds of experiments, and turned out that Power Pivot, even before we had Power BI, Power BI would've blown his mind with the different visualizations we could have done. But I was like doing Pivot Tables with conditional formatting that were showing like breathing patterns, respiration of animals and stuff.
Rob Collie (01:06:13): And his papers that he published on this topic were very popular, it even made some national news at the time. And he couldn't use Power Pivot, Power BI, he couldn't use it to prove to the scientific standard that he needed to, that margin of error was sufficiently small and all that kind of stuff. He had to do that in separate statistical packages or whatever. He probably could do some of that today in Power BI, we're talking about 2013, 2012 Power Pivot here, but he did use it extensively for inspection of all of his data and to formulate hypotheses and also to catch places where his graduate assistant had done a bad job keying in the data. So it really sped his workflow up. And he actually mentioned of technology in the paper. He threw it a bone, a little credit, the acknowledgement section or something like that.
Rob Collie (01:07:03): So it shows you the leverage. You get one person writing DAX, and so far that's been sufficient to keep a lot of things running. There will come a second phase, I believe, where organizationally, you're going to a handful of other people in different departments who are standing up like as mini-Priscilla's.
Priscilla Camp (01:07:26): It's so funny you said that, my boss calls me, he always says, "I can't get another Priscilla right now. There's no funding."
Rob Collie (01:07:31): Yeah. Yeah. See, there you go. When you become a noun, you know you've arrived.
Priscilla Camp (01:07:37): The thing about Power BI is, and I think people don't realize this, to me, there's a lot involved with it, with report writing. If you're just using Power BI, you're writing an M. So you've got to be familiar with M. You can use the [inaudible 01:07:51], but if you want to start doing other stuff, you got to hit that advanced editor, but only that, but then once you transform your data, you go into Power BI and then you got to data model it. So you've got to know your relationships one to one, one to many, and then you write DAX, and then there's the visualization part. And that's a lot too. There's really a lot.
Priscilla Camp (01:08:11): And I almost feel like sometimes I'm a Jack of trade, but a master of none because I can do it all, but I don't have the time to master anything. I don't sit there and do DAX every day. I know it. That's the thing why my boss is like, "Oh, we can't find Priscilla because I can do the SQL side and then I can put my head in there and then I can put my head into Power BI, and now I'm putting my head into Azure. So now I'm learning about Azure and big and non-relational data stores. So my mind is just going from thing to thing to thing.
Rob Collie (01:08:40): We've talked a lot on this podcast in the past about how life and career more closely resembles the decathlon than any one particular sporting event. The Jack of all trades master of none used to be like a sideways back candid insult. I don't think so. I think that reality is that if you're 80th percentile or 90th percentile at 10 different things that all relate in a single role, you are now 99th percentile at that role. So the Jack of all trades thing is important because life is usually a spectrum of activities that inter relate as opposed to... just like the valuable business reports need to span silos, valuable human activities span domains.
Rob Collie (01:09:26): You having self-identified years ago as someone interested in interpersonal communication in the workplace, for instance, that's part of the decathlon for you. There might be someone else out there who can really throw that shot put, then they go to the human communication event in the decathlon and just face plant. And the decathlon specialist, I think most people who've been on the show fit that mold. And it's really striking, decathlon is the future. It's really boring Olympic event, but in real life, it's crucial.
Priscilla Camp (01:09:58): Yeah, you're doing a little bit of everything. And another thing that I had wanted to mention that we were discussing earlier, I think it's mentioned Power Pivot. For some reason, when I was teaching another department about Power BI, we had used back in the past. So this was before Power BI Access, Microsoft Access. And I think someone mentioned it too when we were talking, but for some reason, and I had mentioned this, Power BI somewhat reminds me of Microsoft Access, in more in the fact that with Access, I remember you can make copies and paste your report, your creation.
Priscilla Camp (01:10:29): And with Access, I remember you can copy and paste. Now, would you think of the similarity there? Or would you think it's still completely different? Because ID Excel. I see a lot of mixture in it, but for some reason, just a little bit reminds me of Access.
Rob Collie (01:10:45): I think on the ground Power BI is slash was the last nail in Access's coffin. And Tom's reaction to Access is that it's fake database compared to SQL.
Thomas LaRock (01:10:55): It's not a database.
Rob Collie (01:10:57): See, there you go. See, fake database.
Thomas LaRock (01:10:59): It's not a database. It's not.
Rob Collie (01:11:00): But for the business world, Access used to play two very, very important roles, and it's not the only roles, but in terms of business analytics, it played two roles for shadow IT in tandem with Excel that were really crucial. First of all, it was a way to get around, Excel used to have a 64,000 row limit on a sheet. So Access was used to get around that limit because Excel's Pivot Tables could hold more than a million rows, but they could hold a tremendous volume of data behind the scenes, but you just couldn't land it in a sheet. You couldn't land the data in an Excel worksheet, but the Pivot cache could eat more than 64,000 rows directly from a database.
Rob Collie (01:11:37): So if you had more than 64,000 rows, you would load them into access rather than into Excel. And then point Excel Pivot Tables at the Access database rather than a sheet full of data. Getting around the row limit was one Access killer app. The other one was getting rid of all your VLOOKUPs. You have five different tables, you do the joins and produce the huge wide Franken Table, you do that in Access. It actually is much, much more time effective to do that with joins in Access and then load that big wide table into Excel from Access, and then to your Pivot Table on top of that.
Rob Collie (01:12:12): So the Pivot Table always serve the aggregation like the DAXs role. Access was a way to get around the VLOOKUP problem and way to get around the row limit problem. Wow, Power BI, there's no longer really any practical row limit in that system. You don't need VLOOKUP and you also don't need to create Franken Tables. The data model, the star schema is the answer to that. And then if there was any database type of manipulation that you needed to perform on the data to cleanse it or whatever, or reshape it, Access was doing that as well. When I have M, you have Power Query to do that.
Rob Collie (01:12:48): So the reasons why a business unit would organically end up deepen up to its a eyeballs in Access, Power BI offers a much, much lower path of resistance for all of those reasons. Copy, paste, you mean like copy, paste of like report tabs or like visuals from within the canvas, that kind of thing?
Priscilla Camp (01:13:06): The file itself. When we used to create Access reports because at UCF, it's not like other industries where you're moving fast and you have a whole pipeline. We did our own development. So we would actually make copies on our computer and then open it up and do new stuff and then just rename the file. So in Power BI, since I work by myself, I take my file and my PBX file and I just open it up, make a dev copy and then just rename it just like I did in Access. I was seeing a little similarity there. It's better, it's 100% better, but I thought maybe perhaps in the development why they came out with Power BI was to remove those tools, but also just have some similarities in it.
Rob Collie (01:13:49): I think that when you start building desktop software for the citizen developer mindset, if you're doing a good job of it on the design side, you don't have to explicitly be mimicking each other to arrive in similar places. There's just certain ways that desktop software for that demographic needs to work. Again, if you're doing a good job, you both arrive in very similar locations. I don't think there was a whole lot of Access mimicry going into when I was there. Now, the one exception was when Power Pivot first added its relationships view, with the scheme of view, with the tables and the arrows, and all about the relationship arrows, in Power Pivot, the early versions of Power Pivot, that diagram view was so closely designed to mimic Access's diagram view that the arrows on the relationships pointed in the same direction that Access's arrows did, which is in the opposite of the direction of filter flow.
Rob Collie (01:14:49): So it pointed from the many to the one, instead of from the one to the... So every time I would teach a class, I'd say, "Hey, you see these handy little arrows that point this direction, they point the wrong way. So just imagine... " And then the earlier versions of my book, I'd show the diagram view, and then using my screen grabbing software, I'd put these gigantic orange arrows over the top of those arrow, they were pointing the other way. And so the people involved in designing that feature for Power Pivot, and I wasn't around when they did that, I'd already left Microsoft at the time because it was the second release, but they either had a database background, so the arrow direction seemed perfectly natural to them or they didn't, but they didn't really understand how DAX and the data model interact.
Rob Collie (01:15:33): But what's really funny is that that arrow direction still got through everybody, everyone on that software team had to see that arrow play in that direction and not one person spoke up. And that's why the arrows now, believe it or not, in Power BI, that's why the arrows are not on the ends of the arrow, they're in the middle of the line, because if they had just flipped the direction of the arrows from the original, it would've confused the hell out of everybody. They had to come up with a new way to represent the arrows that people would recognize as not the same. So there's a little bit of inside baseball on why the arrows are on the middle of the lines.
Priscilla Camp (01:16:13): Well, I guess it did have an effect on things, impact past technologies. I can tell you, we were converting a lot of those Microsoft Access reports to SSRS. This was of course before Power BI. A lot of those Microsoft Access reports had VBA in it if I remember. So you would write the VBA, you would hit the script and it would go to Excel. And they were creating pictures, graph in Excel. And I used to always think, "Man, it'd be awesome if they just had something where you can just drag and drop the visualization. I have to write all this VBA or go to SSRS." So when I saw Power BI, I was like, "Oh my God, this is great."
Priscilla Camp (01:17:18): Oh, Okay. Well, there you go. I'm going to remember to put you on the calendar for the summer. We can put Power BI as a code.
Rob Collie (01:17:30): Yeah. The Tom and Rob challenge.
Priscilla Camp (01:17:35): I will hold you to that because people love when we get speakers who have been up Microsoft or at Microsoft, they all love to join on those calls. So I guess they feel like they're getting an inside view and they can speak their opinions about things that they want to be added or whatnot.
Priscilla Camp (01:18:31): They can go to meetup.com, and we are there. And we are also on the Power BI Community site. So they just created user groups there and we are there as well. So you can find us in both places.
Rob Collie (01:18:44): When's your next scheduled meeting?
Priscilla Camp (01:18:46): It's in February with Christopher Webb.
Rob Collie (01:18:48): Chris Webb.
Thomas LaRock (01:18:49): Chris Webb, former guest of the Raw Data Podcast.
Priscilla Camp (01:18:52): Yes. And he will be explaining performance tuning, Power Query and data flows in Power BI.
Thomas LaRock (01:18:58): Power Query session? Huh? From Chris Webb, huh?
Priscilla Camp (01:19:03): Mm-hmm (affirmative).
Rob Collie (01:19:03): And he probably knows what he is doing, Tom.
Thomas LaRock (01:19:04): I know the man.
Rob Collie (01:19:04): He does what he's doing.
Priscilla Camp (01:19:12): I also recommend our Data and AI Central Florida user group. We have April 5th, Jason Serra coming. And he's going to talk about data warehousing trends, best practices and future outlook.
Thomas LaRock (01:19:24): Data warehouse has no future. Data warehouse is per se, it's done.
Priscilla Camp (01:19:29): If you pay attention with business intelligence, I find it so interesting that they have Azure data factory and then data bricks, and then Azure synapse. But what's really interesting is that at the very end, they show how to put all this data into Power BI. So it's like they're keeping this business intelligence suite altogether. So it's really interesting to see how it all plays.
Thomas LaRock (01:19:49): Data warehouse is dead, it's now a data lakehouse. That's the hipster thing right now, is data lakehouse.
Priscilla Camp (01:19:58): I think I looked at the white paper a year ago by Aaron and I believe he was saying how they were working on getting these data out of Azure using Power BI. I think you can actually go in the Service and pick data up from as Azure from the machine learning in AI. So I know it's getting really advanced and they're keeping Power BI in it, which is really cool for those people who are just Excel users and they don't want to jump into Power BI.
Rob Collie (01:20:21): There's a gold rush or a war going on in the noun space right now. It was warehouse for a while and everyone decided on that. We'd reached a peaceful equilibrium around that. But now, now that the warehouse has gotten per se. Oh boy, wide open game, data lake, Uh-uh (negative). Data lakehouse. How about data lakehouse compound?
Thomas LaRock (01:20:41): No, no, it's not compound, it's a data estate. Look, I wrote this whole thing. I live on a data estate, it's by data lake, and now I have a data lakehouse. I work at the data factory, it's made out of data bricks. I shop at the data mart, gets all that stuff from the data warehouse.
Rob Collie (01:20:56): Down the street from the query store.
Thomas LaRock (01:20:59): Down the street from the query store. Yes, that's right. I had all the terms and I mapped out like a day in the life of the data professional.
Rob Collie (01:21:09): I got the data fire pit. We're going to roast some data marshmallows. I saw data fabric the other day.
Thomas LaRock (01:21:15): Yes, data fabric.
Rob Collie (01:21:16): I saw data fabric the other day.
Thomas LaRock (01:21:18): I heard data lakehouse recently, really within the past year or so. And I'm like, "What are you trying to get at?" Well, it's not quite data warehouse, it's not quite a data lake, it's a data lake house. It's like smaller version of a warehouse, but it's this other... I'm looking at it, I'm going-
Rob Collie (01:21:32): Were you, I forget Tom, if you were available to co-host the Denny Lee episode or not?
Thomas LaRock (01:21:37): I was, yeah. And we had this talk, I think-
Rob Collie (01:21:41): We did. We did. Very high energy conversation about the data lakehouse. But if we brought him back on now, he'd be like, "Oh, that's 15 minutes ago." I was kidding.
Thomas LaRock (01:21:49): Exactly. Oh yeah.
Rob Collie (01:21:49): He'd still be on the data lakehouse, but you feel like by now he's like, "Oh man, we're talking about fabrics now." All right. Hey, listen, this has been a pleasure. I really appreciate you taking the time.
Priscilla Camp (01:22:01): Oh, thank you for having me. I appreciate it.
Rob Collie (01:22:03): It sounds like we might have actually met at that event that Matt invited you to where you joined us, but now we've really met.
Announcer (01:22: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.