Welcome to The Dashboard Effect Podcast. I'm Brick Thompson.
Caleb Ochs:I'm Caleb Ochs.
Brick Thompson:Hey, Caleb. So in this episode, we wanted to talk about something that you've been doing/getting some experience with that we think might be useful to other people. And that is generating synthetic or sample data using ChatGPT in order to populate data in a data model for a Power BI report.
Caleb Ochs:Yeah. I think I think this has... Well, first of all, it works well. And then, second, I think that there's a lot of different uses for this that people could leverage. For us, what we're doing is taking models and stuff and putting fake data in models that we already have built so that we can have some sample reports.
Brick Thompson:Yeah. And I think actually it'd be interesting to talk about that use case, because there are a lot of solo practitioners out there - Power BI experts - who get hired by companies to build Power BI reports. And one of the challenges I would imagine, as one of those guys, I mean,(we've had it too as a company) is to be able to show samples of your work, but not show customer reports or data. And so the work you've been doing is to take a few really choice dashboards and completely anonymize them. Different logo, different company name, completely different data (customer names and all that stuff). And that would have been really difficult to do. There have been data-generating tools in the past, but it's amazing. It seems like the results you're getting with ChatGPT are really good.
Caleb Ochs:Yeah, I mean, prior to, it seems so silly that you can't find fake data, but it was really difficult. Like, you might be able to find certain things here or there, like some fake names or something, or a fake address. But finding a list of 1000 of them is really hard to do, number one. And then you always have these other things that you need to change or populate. And it's not like, there's just massive, massive amounts of fake data out there for you to just go download - that just was not there. So that's the hardest part when you're doing that is finding fake data, right? It's not the, you know, putting the data into the model, or, obviously, you've already got visuals and a model for that matter, but it's actually getting the fake values to put in there, it's hard.
Brick Thompson:Well you could generate a column of fake numbers, or random numbers, even within a range or whatever, in Excel, but getting data that actually is interesting and looks like maybe "real life" without using real life data, I think that becomes the hard part.
Caleb Ochs:It's really difficult. I think, you know, one of the other things that we used to do was, you could just hash things, you know, using like, like cryptography, you could just hash a value. Obviously, that would look really bad in a report, right. But that was the fastest, easiest way to do it. But with now with chatGPT, you can ask for exactly what you want, give it some samples of what you're after (the types of fake data, like the types of values), and it'll generate a list for you. And you say, "I need 100 of these. I need 1000 of these," whatever you want. And it gives you a file.
Brick Thompson:So are you using the ChatGPT interface? Or are using the GPT-4 API?
Caleb Ochs:I'm not using the API. I'm just using the interface now, and if you do this, one thing you don't want to do, (which may be why you asked the question) is put in samples that are actually real. Don't put in a person's name as a sample for the model, or for ChatGPT.
Brick Thompson:So you have to produce some fake data just manually?
Caleb Ochs:Yeah, or just ask it. Like, a lot of times, so only a specific things. So if you needed something to replace, or to load into your model, let's say you have like a location code or something, right? A lot of times those codes are somewhat descriptive. So you don't want those in the data anyway, right? But you still want to keep the code because you've probably pulled built a report that's has location code in there, and it's formatted to a certain width. So what I would do is say, "Okay, give me a list of 5000 codes, and they need to be four characters," and give it a little bit of description for what it's going to produce for you, and then we'll bring it back. I don't have to give it any real data for any of that. And then for other really easy things like email addresses, it's as simple as, "Give me a list of 500 fake email addresses," and it just gives them to you.
Brick Thompson:Yeah, that's great. I hope they're fake.
Caleb Ochs:They sure look fake. It's lika @...oh, the @s are really funny.
Brick Thompson:Ridiculous names. Okay, so that's pretty cool. Have you run into any problems where you wanted more data than it was able to give you because of the size limitations of how much it could return?
Caleb Ochs:No, not really. But that's kind of where the second part of doing this comes in, like ChatGPT is not going to be able to generate, you know, millions of rows for you. But it will give you some values that then you can write some other code to expand it. So if you get 500 unique values, and you need to create a fact table, for example, you just have each of those values repeat 1000 times like on a different day, right? So you're using that different tool to do that, to blow it up.
Brick Thompson:Are you just running some Python code, or what are you doing?
Caleb Ochs:Yeah.
Brick Thompson:Alright. Yeah, that sounds pretty straightforward. Yeah. And so if you're, let's say, you're taking an existing report, and you want to completely anonymize it. Obviously, on the UI portion of the report, you're gonna have a different company name and different logo and things like that. Maybe change the colors. But how do you know what data you need from the data model? And then how do you get that into the Power BI report?
Caleb Ochs:What data you need from the model?
Brick Thompson:So, I know that you look at the .pbix file and actually look at the model itself? How do you look at that? What tool are you using? And then how do you populate it?
Caleb Ochs:Gotcha. So first of all, I just looked through Power BI just in its interface, and I'm looking for columns that do have sensitive data in it, and those are the things that you need to start figuring out that you need to create values for. Yeah, company names. That's step number one. And then what you want to do is, you take your fake data, I put it into a data lake, I write whatever sort of Python on it, as we just mentioned, to ultimately get it to either generate the rows that I need, or do whatever else I want to do with it at that point. And then you just connect, I'm using Azure, so I use an Azure Synapse serverless SQL, and just connect to the parquet files that are coming out of my Python that I've loaded into that lake. And then you have your model that was there in Power BI now just has completely anonymous data. So you're not going into the guts of the .pbix the, the I know, it's not an XML-type file, but... Yeah, no, you can't really do that. It's interesting, like Power BI compresses data really well. For example, it might be 200MB. And when you open that file, it might consume a gig of memory. Okay, so it's decompressed stuff for when it's running queries and stuff. So if you look, you can go look at like the raw, compressed files, it's just going to be hard to make sense what's going on in there. But one thing you can do is if you have the model open, you can use like a tool like DAX studio, even SSMS, to connect, run some DAX queries against those tables, and then you can view the data that's in there and do things with it.
Brick Thompson:When you say DAX queries, you mean SQL queries? Or actual DAX queries?
Caleb Ochs:Legit DAX queries. Like summarize a table.
Brick Thompson:Okay, so that sounded pretty technical. If someone is not sort of a data lake aficionado, but they're a good Power BI person, what method might you advise them to take?
Caleb Ochs:Yeah, yeah, I mean, it is very technical. Like, that's tough. And that's for a pretty specific use case, right? Yeah, you've got a report but you want to put some fake data in it. So that it's anonymous, and nobody knows who it was for. So if you don't really have those skills, honestly, right now, you're kind of SOL. But what I would think about doing instead is describing the tables that you have in your model and seeing if ChatGPT can give you like a CSV that looks exactly like those with fake data in it. Because then you can just connect directly to those files instead of whatever your Power BI file was originally connected to. I would think you could use that also to work different types of models. Like one thing that is tricky that we've had,(it's been many years since we came up with this method), but one of the hardest things to do in a tool like Power BI is create a accounts receivable report like an aging report. That's a dynamic that you can select like, okay, yesterday, what was the aging? How about the day before? You know that you can select that.
Brick Thompson:So the time logic, it's just hard.
Caleb Ochs:It's very difficult. So there's a specific way you need to model your data to be able to make that work. And you could use ChatGPT to create you some files so you can get some practice on some of these more difficult model types.
Brick Thompson:Oh, yeah. Right. So it could so this actually, this technique could be a good way to do training.
Caleb Ochs:Oh, for sure.
Brick Thompson:Yeah. What, how else? What other use cases would this have? So there's training, there's maybe testing edge cases to see how your report looks and performs with it or something like that?
Caleb Ochs:Yeah, yes. Actually, one of the problems I just ran into with a client last week was a problem with row level security where there are some blanks in some ID fields in our dimensional model, which wasn't a problem until we created a specific type of report. And then the row level security actually was removing some of those because we had, we were basically getting rid of rows that were other employees that were not who you should see as the report-reviewer and your security. And those rows were just blank. So anyway, we just had to make some tweaks to the underlying data. But that's a really good use case of where you could see where a problem might occur if you don't have really good referential integrity in your model all the way through. So yeah, like dirty data, things like that. It could generate you some of those things and see how it works in Power BI and how you can fix it. That'd be great idea.
Brick Thompson:Would you say that being able to use ChatGPT to do this made it so this is something that people can do easily and commonly, as compared to before? You were saying before the tools were just not great for doing this. Is it easy now? Or is it still kind of a slog?
Caleb Ochs:I wouldn't say it's totally seamless yet. But it's enabled you to actually do it without pulling your hair out. So that's a big win, big step forward. And yeah, I would actually be really interested to see if you asked ChatGPT to describe a model, like a dimensional model for something like AR Aging, how would it do it?
Brick Thompson:Sales transactions, or whatever. Oh! That's interesting, actually. Your example is way better, because that's a hard one. And then start throwing all the hard things you know about it, and see what it comes out with. That's pretty cool. I should have put a disclaimer at the front of this one "technical material ahead". But that was interesting. Alright. Well, good discussion. Thanks.
Caleb Ochs:Yeah. Thank you.