Can You Run Your BI on AI?

Everyone's asking the same question: why can't I just run all my business intelligence through AI? In this episode of The Dashboard Effect, Brick and Landon break down the real answer. You probably can, but only if your data foundation is built for it.

They cover where AI analytics fit alongside traditional Power BI, why a strong medallion architecture is the difference between reliable answers and confident wrong ones, and the practical engineering that makes natural language query trustworthy. They also dig into rising token costs, vendor lock-in, and how to decide what belongs in a dashboard versus a chat window.

If you run a data-driven business and you're weighing how far to push AI into your reporting stack, this one is for you.

Subscribe for more conversations on data, dashboards, and getting real value from your BI.

About Blue Margin - 

We build and manage the data foundation that turns scattered systems into reliable, AI-ready reporting, so your team gets trustworthy answers without the overhead of an internal data team.

Learn more: https://www.bluemargin.com/

Transcript

SPEAKER_00 0:05

Welcome to the Dashboard Effect Podcast. I'm Brick Thompson. And I'm Landon Oaks. Hey Landon. So today I thought we would talk about using AI for your BI. We're hearing from customers and uh prospects at Blue Margin. Why can't I just do all of my BI with AI? And the fact is, you probably could if you set it up right. Um AI is getting really good at doing analysis, and we're getting some really good results. But there's some easy traps to fall into too. If you don't set it up right, um some things can come out suboptimally.

SPEAKER_01 0:40

Yeah. Yeah. I mean, we've been seeing that quite a bit, um, especially with our early dabbles into AI, you know, where we just pointed it at data and it was not thrilling at all, the results I came out with. But but recently we've been we've been seeing some really, really cool um things where it's accurate. And so Yeah.

SPEAKER_00 0:59

Well, I think we've been we've been doing natural language query for at least a couple years. And really the last six months, it's started to get a lot better.

SPEAKER_01 1:07

Yeah.

SPEAKER_00 1:07

Um and I think it's uh I think it's worthwhile talking through what do you what do you need to do with your data, your data foundation to make it go really well. And I think one of the places to start is um if you don't consolidate your silos of data from your different transactional systems, it's really hard for the AIs to give good responses. You can have MCP servers connecting to multiple systems and it will pull data in from those. Or you can download CSVs and upload those to the to the AI, let's say you're using Claude or something to the LLM. And it will give answers that seem right, and sometimes they are, often they are, uh, but it also has a lot of problems where it can't match up the data source as well. So it might not have the exact same customer number in each system or or or uh employee numbers or those types of things. So if you're mixing an ERP and a CRM and a timekeeping system, uh maybe a dispatch system, something like that, it can be really hard to get good answers from those unless you've prepared the data right.

SPEAKER_01 2:11

Yeah, exactly. I mean, it's not a new problem either. Um, AI is just kind of has the same issue where it is a little bit better, whereas if you have a customer name that's similar, like very similar, you know, uh a good example is like incorporated ink with a period, and sometimes it doesn't have a period, you know. Back in traditional reporting, honestly, that period would make it show two different lines, right? AI is a little smart enough to be like, okay, this is probably the same customer. Um but you know, usually they're not that simple, the the differences, right? And that's that's where it'll get you if you're not you didn't prepare it beforehand.

SPEAKER_00 2:48

It's true. And I've even seen uh, say, Claude, be really smart about saying, I think this customer might be the same as this one, even though it's pretty different. Um, but I've also seen it completely miss. Yeah. And so I think the the question comes down to how are you using the AI on your data? Um, do you really want it to be your BI solution, your business intelligence solution? And I would say you actually need both. You need a good BI foundation to have really good AI analytics. You know, you've got your BI, if if you've built a good data lake house, um, you've got a good medallion architecture, you've got your bronze, silver, uh gold layers, so you're reporting off of gold, you've done good validation, you can be pretty sure that that uh that report or that dashboard is right every time, even though it's being updated every night or every few hours, something like that. Once you've done all that foundational work, it can be right. Um but if you're pulling data straight from a transactional system and asking an LLM to interpret it, uh, you may not have done some of the things that need to be done to make it really good. And we've been doing a lot of work with that for some of our clients, helping them make their AI answers come out not quite deterministic, because an LLM isn't deterministic. You're gonna get a little bit of a different answer usually every time. Hopefully you get the right number every time if you build your foundation correctly. But one time you might get a visualization that you love, you try to get that again, you don't quite get the same thing. It can be a little challenging.

SPEAKER_01 4:16

Yeah. Yeah, that's what we've seen, the the biggest difference. When you have that data foundation set up right, you know, we've actually been seeing some pretty good numbers. Um more recently, you know, sometimes the misses are actually issues in the data itself, you know, that it's okay, it's not the AI's fault. It's under you know, foundational way, way back in the source system that's the issue. Um but the biggest thing too is yeah, to your point, like if I don't if you don't specify exactly what you want, it'll kind of create whatever it feels like at the time. Sometimes it's a table, sometimes it's a visual. And then even when you do specify it, you know, you don't always get the same visuals. You know, sometimes it creates really cool dashboards um that you just can't get again. It's a one-time thing, which is cool, but yeah, nothing.

SPEAKER_00 5:03

I bet that'll improve. I'm sure it will as we go forward. And maybe you can use a skill to try to get the same thing. But let's say you've got a hundred users or some, you know, some of our clients have, you know, hundreds of users, and you're they're running the business off of their dashboards. You want them to come in and get the same reports every every morning. And I think there's sort of two different use cases there. One is true BI. Let's say you've got your factory floor running on your dashboards there. You want that to be deterministic. You want when the user opens that up or looks up on the screen, it looks the same every time. But then you also have business analysts and you have executives that want to do exploration or uh get a quick answer to something that in the past you would have had to ask an analyst to pull some data with a SQL query and put together a spreadsheet. Now you can just ask a question if you've set your data up right and get a quick answer. And if it's something you're gonna put in front of your board, you might still give it to an analyst to really confirm that what you've seen is right, um, to be sure, although we're getting closer and closer to really getting the right answer every time. It's more for exploration and analytics. I sort of see the traditional BI, we we produce our reports in Power BI. That's sort of like your operational layer, if that's how that's how you run your business, your operating system. And then your AI analytics is more your um exploration and business analyst work and checking a theory. Um and if you get something that's super useful there, you might quickly turn it into a Power BI dashboard too, so that you are getting that same view every time you ask the question.

SPEAKER_01 6:36

Yeah, exactly. And that's always been a bit of a gap, you know, for a while there where it's just getting those quick answers without having somebody go off, you know, explore the data, write a bunch of SQL. Like it does a phenomenal job doing that. However, to your point, you know, you should not take it as gospel truth. Um, it's gonna be directionally accurate, but you know, most in most scenarios, unless you've really worked on that data in that specific area, um, you know, you'll want to have people kind of do some betting on it, et cetera, if you're gonna make important decisions off of it. But it's really cool how you can get an answer back in in a minute.

SPEAKER_00 7:13

Yeah. It's amazing. So uh well, let's go technical for a minute. Why don't you describe how you take a traditional data lake house, medallion architecture, and get it so that the AI um really reliably gives you the right answer, at least the numbers the same every time. What do you need to do?

SPEAKER_01 7:32

Yeah, good question. So there's I'll kind of go start with the approach we took initially, um, where we weren't getting the same answer every time. Um so you know, in this case, we already had a gold layer, right? That reporting layer that that we had reports built off of. Um so in your traditional kind of gold reporting model, you have a star schema. You have a bunch of dimensions, you have fact tables. Um it works great for Power BI visualization tools, et cetera. But what we found is we basically took that exact model. We took some of the extra stuff out because you know, when you do a Power BI report, sometimes you need to add columns for this specific slicer on this specific page. Otherwise, that column serves no purpose at all, right? Like you want to try to limit that.

SPEAKER_00 8:16

So you don't want to pollute the you don't want the AI to have to figure out what the heck that column has.

SPEAKER_01 8:20

Exactly. Unless it's actually useful. Yeah, yeah. Because it's, you know, sometimes your model has to be very built for that exact place it's gonna show up on a report. And if you don't use it in that exact place, what I mean by that is like, okay, this has to have the week grain on my visual. Like this is a trend line by the week. If I take the week off, it breaks, it won't work. You know, so those types of things you'd definitely want to try to avoid. But essentially we did that. We took trimmed some of the fat, some of the things that you don't necessarily have to have if you're writing like a SQL query to hit it, and then set the AI loose on that, and it was good. You know, it wasn't bad, but one thing we noticed is that there's so many different joins and different like we had a couple different columns for sales, one with tax, one without tax, for instance. And it kind of just picked one at random almost. You know, we didn't see a real pattern unless the person asked specifically, I want no tax involved in this.

SPEAKER_00 9:21

Yeah.

SPEAKER_01 9:22

Um, and so that's where it got a little bit, you know, just didn't quite hit the right area because you have one person asking, show me the sales from last week, and they're expecting tax, but then the uh uses the column without tax and vice versa, right? So it had a little bit of a misalignment there.

SPEAKER_00 9:38

So how do you solve those kinds of problems?

SPEAKER_01 9:40

Yeah, so for that, what we ended up doing um is we created a very specialized view um of that data. So instead of having kind of your star schema, we almost completely um denormalized it.

SPEAKER_00 9:55

So pulled all the different tables into one big table.

SPEAKER_01 9:58

Yeah.

SPEAKER_00 9:58

Yeah.

SPEAKER_01 9:59

And then gave them a single amount column, right? And so the business had to decide are we gonna use tax or are we not gonna use tax for this view? In this case, it was a profit and loss statement.

SPEAKER_00 10:09

Yeah.

SPEAKER_01 10:10

Um once they decided that, you know, there's no ambiguity for the AI. It just has one column it's gonna use, so you'll get the same answer every time.

SPEAKER_00 10:18

Aaron Powell And you could have both if you wanted to. You'd have to be clever about how you do it.

SPEAKER_01 10:23

Yeah.

SPEAKER_00 10:23

Um and and well, there's probably other stuff you're gonna talk about in terms of uh context. So you give the AI some context so it knows that there's two different two different uh types of sales or something like that. So anyway, so you do that, and then you probably have a bunch of other things to make the AI, make the job easier for the AI.

SPEAKER_01 10:44

Yeah, for sure. You know, the big thing is like the markdown files, for instance. Um we use I think we've talked about it on this podcast before, but for those of you who might not have seen it, we do use MCP servers, so it's a custom server that will um go out and connect to a data source.

SPEAKER_00 10:59

Yeah.

SPEAKER_01 11:00

And the nice thing about that is you can code all kinds of instructions on the right server. Exactly, which we we're using currently. Um and so yeah, you can tell it like you have to go read this all these different documents. And they're markdown files that tell you about the business, tell you about common metrics, any you know, anything in the data that's of important to know. So it essentially gives it all of the context that an analyst that's been working on it for a year would have, right? Yeah.

SPEAKER_00 11:32

And it needs to read that every time because it needs to be additional context from the markdown files and we store those in the data lakehouse as well. Back to the uh the data model itself. So you've denormalized, you've taken all the tables, don't fully denormalize, but but a lot, and created a wide table that has just the columns you want. You also rename those columns so they're really easy to infer names of of what's going on there. Um rename tables if you have multiple tables. Um we also do uh materialization. Can you explain what materialization is of the table?

SPEAKER_01 12:08

Yeah, definitely. Yeah, yeah. So I'll try to keep this a little higher level. Um so essentially, you know, a lot of times a lot of what people do is it's called schema on read, right? And what that means is like when I go and I query this invoice, this fact invoice view, um, instead of having the data stored in the way we want it, like where we have some calculations on there, we ping on a customer ID, et cetera, um, it applies that as it's reading the data. Um the beautiful thing about that when analytics is that you can be super agile. You know, I want to make a change to a column, I just change one line of code and I'm done. Yeah, right. I'm off and running, right? So it's really, really nice to be quick.

SPEAKER_00 12:53

So you have a view that's then going back through often through multiple layers of views till it gets to the raw data.

SPEAKER_01 13:00

Exactly. Yeah. And so again, for analytics that looks great because you know you're refreshing things like once a night the most frequently, sometimes every couple hours throughout the day, you know, that's fine. When you're so it might take, you know, five minutes to return data. Right. When you're talking to an AI, five minutes feels like an eternity.

SPEAKER_00 13:18

Right. If you're in a chat. Yeah.

SPEAKER_01 13:20

Yeah. Oh my gosh, people, yeah, you just kind of give up almost.

SPEAKER_00 13:23

Yeah.

SPEAKER_01 13:24

Um so what we basically do in that case is we will take all that logic and we will actually save it off to another table. Um so it's a physical. Now you're now you're doing um schema on right is what it's called there. And it just speeds up the performance like crazy.

SPEAKER_00 13:40

So the yeah, so the LLM is not pointing at a view, it's actually just pointing at a table. Exactly. So we can get the data back quickly. It doesn't have to wait for those views to calculate your way through.

SPEAKER_01 13:49

And that's in a separate area, right? That's in our platinum layer, we call it. Um because in the gold layer, you know, being fast and agile to make changes really quickly is still very important. Yes. And massive for the business. But in the platinum layer, you know, it's kind of different priorities. Right. Yeah.

SPEAKER_00 14:05

So in the gold layer for your Power BI reports, you know, you're you're pulling in data in the middle of the night, you update the report. As you said, if it takes five minutes, even if it took an hour to do a full update, then it's set until the next update runs the next night. So it looks instantaneous on the screen. It's just pulling pulling that set data that you've already done. Yeah. But for the LLM, if you if it was taking five minutes or longer to do an update, you're sitting there in a chat window wondering if it broke.

SPEAKER_01 14:32

Yeah, exactly.

SPEAKER_00 14:33

And so you can materialize it into materialize that view into a table so that you get instant instant response virtually.

SPEAKER_01 14:41

Virtually, yeah.

SPEAKER_00 14:42

Yeah, okay, cool. I think one of the other problems that we see using AI with BI is that uh the AIs, I'm thinking mostly ChatGPT and uh and um uh Claude will give you very confident answers. They just look right, even when they're wrong. And so it's really important that you have that platinum layer and that custom MCP and that additional context so that you can be certain that you're getting good responses. I guess you can't be quite as certain as with Power BI report. Although even in a Power BI report, someone could introduce an error and have have a number on a table that looks right but isn't. So, you know, you have that same problem. But you can get it darn close to that, even though the AI is non-deterministic and has a certain amount of variability, you can get it so it's reporting the right numbers virtually every time. And that's taken some work to get to that.

SPEAKER_01 15:38

Yeah.

SPEAKER_00 15:38

And I think that um one of the disservices that vendors that I see out there talking about their solution for connecting, you know, uh their agent to your data, um, the disservices they make it sound like you can really just connect it right to whatever the raw data source is and it'll give you a right answer every time. If they've done really good engineering of their MCP server and they're connecting to a single data source, and um they thought really hard about what data can come back and taught it well. And if the company using that system hasn't used it in some non-standard way, then you can get it really good. And I've seen that. But if you've customized how you use it, maybe you use a field differently than maybe you know the the manufacturer uh intended it intended for you to use it, you can end up uh getting completely wrong answers that look right just because of the way the AI says it.

SPEAKER_01 16:33

Yeah. Yeah, exactly. And I mean, at least in terms of what we've seen, non-standard uses of systems is like that's the that's the norm.

SPEAKER_00 16:41

That's the standard. That's the norm.

SPEAKER_01 16:44

Exactly. Yeah. Yeah. So which, you know, it's it's it could be a good good way to um actually get some answers back. But I I look at it a lot like even uh back when you know companies would have their standard set of reporting, right? A lot of times people be like, well, this just isn't it's it works, but I really wish it could do this thing as well, right? Yeah. And I have a feeling that'll be very similar.

SPEAKER_00 17:09

I think that's right. I think one of the other things people uh maybe need to think about more is just the cost. So we're seeing the cost, token costs for using LLMs continues to go up for for the frontier. I mean, there's some good open source and you know, there's some Chinese models that are a lot less expensive, but for doing analytics, you definitely want to be using the frontier models, at least at this point. And it's getting quite expensive. We've seen like our company uh account with Claude, where we used to have plenty of usage for what we were paying. All of a sudden it feels like we have a fraction of that. Yeah. You know, employees are saying, hey, it says I'm out of capacity for three hours. You need to put more, you know, put another coin in the machine to make it go. And pretty soon you're spending instead of you know $25 or $50 or whatever, uh, I can't remember what the monthly is. I think it's $25. Unless you're doing the pro. $25. It's $110 or $200. But but yeah, pretty soon you're having to put extra usage, you know, token uh uh budgets on there, and you can easily spend $100 or $200 in a day. And it can get quite expensive. So as you're thinking about um using AI for your BI, if you do have lots of users, you have $50, 100, you know, several hundred, some of our clients have hundreds of users, if they're all just pinging, let's say, Claude all day for their answers, as opposed to just pulling up a dashboard, you're going from not quite free because you've got to pay the monthly for the Power BI, but you know, whatever that is, ten or fifteen dollars a month. But now you're paying sort of this variable cost to use the LLM. And if they ask the question a certain way, it can get even more expensive. Um you really want to offload the regular BI to something like a Power BI or Tableau, something like that, where you're not going to have that variable cost, where you can have hundreds of people hitting it multiple times a day, and the cost stays the same virtually. I mean, there's a little bit of compute maybe, but but not much. Whereas on the AI, if you all of a sudden have hundreds of people pinging and trying to get the data they need to do you know their their role in the business, you could send up spending literally hundreds of dollars per person or thousands. I mean, it's it's amazing how quickly it goes up.

SPEAKER_01 19:22

Mm-hmm. Exactly. Yeah, it's it's gotten very expensive recently. So I think I think I mean with like fabric, for instance, you do have that fixed cost, you know, it's not going to change unless you go in and change it.

SPEAKER_00 19:35

Yeah.

SPEAKER_01 19:35

So yeah, that's that's the nice part is people can go get that answer that they need sometimes every day, multiple times a day for for the same cost every single month. Um, which is huge there because I couldn't imagine if you had 100, 200 users just using AI solely for their BI platform. Um, yeah, I don't even I couldn't even throw out a ballpark number there. It'd be a lot.

SPEAKER_00 20:00

That said, having the ability to do ad hoc natural language query for executives, um, for analysts, for you know, your CFO, CEO, COO, uh, that type of thing, your your VPs, super valuable. Even though it's going to be more expensive, so valuable to free those people to be creative and be thinking about specific things they'd like to know the answer to and be able to get that quickly. Yeah. Even if it costs a few dollars more, uh, that's worth it. And it's usually they're doing that you know, one time. They're doing research on it maybe a few times. If they're doing it a lot, they probably give it to their Power BI team to go ahead and build it into one of their dashboards. Yeah. Because you want to be able to pull it up the same every time.

SPEAKER_01 20:42

Exactly.

SPEAKER_00 20:42

Especially things that you're taking to the board or that you're making big decisions on. You really want to know that it's more deterministic than than non-deterministic.

SPEAKER_01 20:50

Yeah. That's that's the guidance that we try to give people is you know, if you're starting to ask the same question, you know, every day, multiple times a week, et cetera, it might be time to look for a Power BI report for that one there. Um But I mean the big thing too is like just getting that quickly. There uh another kind of area that has helped us and we've heard people where it helps is if you're on a meeting, you're strategizing with someone, you know, this might be in the C-suite level, et cetera, and a question comes up that you don't have a report for, um, you know, if you don't have natural language query, you're talking, okay, now we gotta go go get with this team, give them the requirements, wait a couple weeks, a couple months maybe. Get an award to do it. Yeah, yeah, or get an analyst to go get it, and I'll wait a few days. But they can just type it real quick in the meeting, get back an answer. Sure, it might be might not be 100% accurate, but it's directionally accurate. And now you're just continuing down that thought process and it's it's big.

SPEAKER_00 21:47

It is big. And I think more and more it's not just directionally accurate, it's accurate if you lay that foundation.

SPEAKER_01 21:52

Yeah, yeah.

SPEAKER_00 21:54

If you don't lay the foundation, if you're just using the um SaaS vendor's MCP server, um, you know, a lot of times it is right. I mean, I use those a lot for quick exploration. I'm amazed at how often it's useful but not right. Yeah. It's it's close. It doesn't have all the data or it misinterpreted something, something like that. I think the other thing people need to look out for when they're um trying to do BI with AI is some vendors, like SaaS vendors, will say some some version of, hey, you can put all of your data onto our cloud, and we have an agent that will then allow you to do natural language query. Um and the problem is it might be true, like maybe their cloud is Oracle or something. Um, but the problem is you still have to do all that foundational work. You need to get that medallion architecture to get you to um what's really a deterministic set of data. You know it's right. And then you need to do that extra prep work that you were talking about earlier to make it really easy for the agent, the LLM, to be able to interpret correctly. I think the other problem with that is you've sort of got some vendor lock-in now. So let's say your CRM says, hey, we have a new agent, does great natural language query, you can put all of your transactional data in our cloud and it'll do it for you. Um let's say you even build a foundation there if they have that capability. Well, now you're really locked into that vendor, as opposed to being on a more agnostic system, I guess, like a fabric or an AWS or BigQuery, something like that, where you really own your tenant there. You can move it to another system without some kind of egress cost. Um, you can make the decisions around all the work you do on it. The foundational work you do is yours, it stays there. You can take the data from that one system that wanted you to load all the stuff to their cloud. You can put theirs in your tenant as well on one of those systems, and you have the best of all worlds there. Maybe you can't use their agent on it, but the chances that their agent is better than what you can get from OpenAI or Anthropic is probably low, at least at this point. Maybe someone will crack that and come up with something so good that you just have to do it. But I would really watch out for that vendor lock-in piece.

SPEAKER_01 24:08

Yeah, agreed. I mean the other the other nice thing too is putting it on those big cloud providers that are very open, um is you also can control your model now, too, right? Yeah. That's one of the things I like about the MCP servers that we've been using is it's really easy to change it from anthropic to open AI to Gemini. You know, so you can be really agile there.

SPEAKER_00 24:30

Trevor Burrus, Jr. Yeah, you're not tied into someone's uh funny uh named agent. Yeah to do your stuff.

SPEAKER_01 24:36

Who knows what model they're using in the background? No clue.

SPEAKER_00 24:40

Well a lot of times they're using sort of we'll use our own I hear a lot uh variations like we use our own model. We've trained our own. I think they're using some kind of uh open weights models, and they're usually not as good. Not yet. Yeah, they may get there, but they're not yet.

SPEAKER_01 24:55

Yeah, exactly. It's changing every day at this point.

SPEAKER_00 24:58

Yeah. All right. Anything else you'd like to add? I don't think so. Okay. Well, good discussion. Thank you.

SPEAKER_01 25:04

Yeah, thank you. All right.