Welcome to The Dashboard Effect podcast. I'm Brick Thompson.
Caleb Ochs:I'm Caleb Ochs.
Brick Thompson:Caleb, today, we wanted to talk about some Fabric stuff. Direct Lake mode, Pipelines. They're kind of technical topics, but I think they're relevant to business people who care about their data. So maybe we could start with, let's actually start with Direct Lake mode. What is it? Why should you care about it? What pain does it solve?
Caleb Ochs:Yeah. So just kind of high level Fabric. As far as I understand it, really one of the key concepts behind it is one copy of your data. That's kind of one of its selling points, which I think is really great. Because, you know, how many times have you copied data into different places and just got multiple versions of the truth. And you don't use don't want that. So that's really helpful. In order to do that... Well, let me explain real quick, high-level how Power BI the options you have to pull data into Power BI. Before Fabric, there was "import mode". So you would load the data physically into Power BI, and the Power BI VertiPaq engine would store it, it would store it and compress it. And that's how it could do queries really fast on the fly. And that would all be saved into memory. So it was a copy of data.
Brick Thompson:Yeah. So, if you had a report that was using import mode, and you wanted to have a slightly different report, you do a "save as" as a different report. Now you've got two copies of that data sitting there.
Caleb Ochs:Yeah, yeah. And you can even say, just physically putting it into import mode, you've now got a copy and from wherever you got the data from. Right. So that was that was one mode, and most common, by far, we still use it a lot. The other mode was "direct query", and this was - just like the name implies - it would send SQL queries, for example, back to your SQL database, to compile the result that it was after and bring it back to Power BI. So it didn't have another copy of data. But slow, right?
Brick Thompson:Right. Because it had to figure out the query, had to send it, had to wait for the result, and then display it.
Caleb Ochs:Yeah, your database, like a SQL database is not optimized for running many queries like that on the fly very quickly. Like it can do it fast, but you're not gonna get anywhere near as fast as an in-memory query, right against the VertiPaq. And so anyway, so those are really the two main modes. Now with Fabric Direct Lake came out. And went Direct Lake does is it allows you to do a direct query back to your lake, or One Lake (which is in Fabric), but you get the same speeds as if you imported the data into Power BI, that "import mode"? I'll say at first, I didn't believe it.
Brick Thompson:Right, I know.
Caleb Ochs:I was skeptical, and then I tried it. And sure enough, it is just as fast. It's really impressive. So essentially, it solves the problem of having another copy of data, right? So you've got, let's say if we just run down a quick, hypothetical example, if you have data coming into your One Lake, (which is in Fabric, which we're not going to get into), and something changes in One Lake, you update some data or new rows come in. Then the previous, actually in the current really the mainstream architecture now, you would have to refresh your Power BI report.
Brick Thompson:Which re-imports.
Caleb Ochs:So pull all the new data. So you can have a scenario where your source or your lake is out of sync with Power BI, right?
Brick Thompson:Yeah, it is until that next refresh, which runs on a schedule.
Caleb Ochs:Right. Now with Direct Lake when that change happens, and someone comes into Power BI report, it does not need to load it again. It's just querying those files, so it's 100% in sync at all times.
Brick Thompson:So direct query also gives you that? How, how do you think they solved that problem? I mean, I know there were all sorts of tricks they were doing with direct query, like query folding and stuff to make them as fast as possible. But somehow they've gotten some completely different place to be able to do the Direct Lake query. Yeah,
Caleb Ochs:Yeah, it's all with you know, the Delta parquet file formats. And then from what I understand they've, Microsoft has put a proprietary indexing on those files. And so that's what happens to them when you put them into One Lake, and into your Fabric environment, they get that indexing on them. So then Power BI is optimized to just read that really fast. Yeah. And same with the other tools are in Fabric, you know, like your warehouse and some of the other queries that you can and data transformation type things that you can do against those files, right? They all get to use that.
Brick Thompson:Alright, so that's all interesting. As a user, it sounds like the main pain it solves is that your data between, say, Power BI reports is unlikely to be inconsistent. You're not doing a refresh.
Caleb Ochs:Yeah, yeah. Basically, you get you to say, yeah, that. It's consistent.
Brick Thompson:And it's really fast. Yeah. Okay. All right. That was good. So at the
Caleb Ochs:And you just don't have to schedule. Right now this is the way it works. You schedule your ETL, and that loads data to wherever you're storing it, right? Say in this case, it's a data lake. And then from there, you have to also schedule or part of your ETL, you have to kick off a Power BI refresh. So it's an extra step. It eliminates that step. Yeah. So it's just you just have to build your ETL. You just have to land data there. Then everything that's consuming it gets the latest version. beginning of this episode, I said we wanted to talk about pipelines, data pipelines and Fabric as well. And speaking of ETLs, so what's a data pipeline in Fabric? Yeah, so the data pipeline, if you're familiar, really the they've taken Synapse Pipelines and Data Factory and just put it into Fabric.
Brick Thompson:With no changes? Basically exactly the same?
Caleb Ochs:No, there are some changes, and there's actually some features that are missing right now, which is why we haven't adopted it widely. So they're still working on it, I fully expect all the features from Synapse Pipelines and from Data Factory to ultimately end up in Fabric Pipelines. However, it's just not there yet. So you can do some basic things like moving data from a SQL database into One Lake, or push data out or you know, things like that. But for more of, I guess, real world scenarios, we stick with Synapse Pipelines right now. Okay. And, you know, there's not really a problem with doing that, but I guess all this is to say that those Fabric Pipelines still have a little catching up to do. The good news about them, though, when they do get to that place, is that Fabric, you're going to be able to have everything in one spot in Fabric. I think that's the most exciting part. There's a lot of exciting pieces about it, but being able to have your development team go to one place, switch between Power BI/pipelines/data flows/your lakehouse/writing spark notebooks, like it's all in there. And it's collaborative. And like, it's almost like a Word experience. You know, when you got two people collaborating on a Word document, you can do that same thing in a lot of Fabric tools. Like Jupiter notebooks? You can do Spark, whatever.
Brick Thompson:Okay, yeah. Okay, right, right. Yeah, that's cool. Do you know when it's going to be at parity with functionality with Synapse?
Caleb Ochs:I don't totally, yet. The biggest thing that's missing right now is the ability to attach a gateway to, or "an integration runtime" is what they call it in Azure, to the pipeline so they can pull data from on-prem sources. I'd have to check myself, maybe that's changed since I last looked, but I haven't heard that it has. There are other ways that you can get data from on-prem, but pipelines are the best way to go. So that's why we are sticking with with Azure for now.
Brick Thompson:Okay, if you're working in the Azure environments, Synapse or Fabric, is there ever a reason you'd go to some older technology like SSIS or something like that? Or have they covered everything at this point?
Caleb Ochs:I mean, you can do everything you would need to do in there. So you wouldn't need anything like that. I think. There are scenarios, like if you have a bunch of SSIS packages running, and you just want to run them in the cloud and like shut off your server or something. There are options to do that. It's not in Fabric, it's in Azure. But if you're ready to rebuild some stuff, go in and do it in Fabric.
Brick Thompson:All right. So the main advantage to the Fabric data pipelines in your mind once it's at parity with Synapse Azure right now, is that your teams, your development teams, really are working in a unified platform. They're not sort of all over the place. It's, uh, actually, I don't know quite what the analogy would be. But it maybe Word is the right analogy. You're both in this, not just a document, but you're in this place where you're seeing changes happen together on the fly. Is there a way to manage like, so you've got your PySpark notebooks? How do you do source control? How do you make sure you're checking stuff in? It sounds like It could get a little loose.
Caleb Ochs:So it gets built in, which is awesome. So you just connect your DevOps repository or your GitHub repo, and then you get to do it just like you're using Git, so you have commits and that type of thing.
Brick Thompson:Okay.
Caleb Ochs:Yeah, it's pretty slick.
Brick Thompson:Cool. All right. Anything else we should talk about there?
Caleb Ochs:I don't think so. I think that pretty well covers directly.
Brick Thompson:Appreciate the education. Okay. Talk to you soon.