Data Engineering Podcast

Share on social media:

Support Data Engineering Podcast

[00:00:12] Unknown:

Hello, and welcome to the Data Engineering Podcast, the show about modern data management. Are you bogged down by having to manually manage data access controls,   repeatedly move and copy data, and create audit reports to prove compliance?   How much time could you save if those tasks were automated across your cloud platforms?   Immuta is an automated data governance solution that enables safe and easy data analytics in the cloud.   Their comprehensive data level security, auditing, and de identification features eliminate the need for time consuming manual processes. And their focus on data and compliance team collaboration empowers you to deliver quick and valuable data analytics on the most sensitive data to unlock the full potential of your cloud data platforms.   Learn how they streamline and accelerate manual processes to help you derive real results your data at dataengineeringpodcast.com/immuta.  

That's immu   t a, and get a 14 day free trial.   And when you're ready to build your next pipeline or want to test out the projects you hear about on the show, you'll need somewhere to deploy it. So check out our friends over at Linode. With their managed Kubernetes platform, it's now even easier to deploy and scale your workflow, so try out the latest Helm charts from tools like Pulsar, Pachyderm, and Dagster.   With simple pricing, fast networking, object storage, and worldwide data centers, you've got everything you need to run a bulletproof data platform.   Go to data engineering podcast.com/linode,   that's linode,   today and get a $60 credit to try out a Kubernetes cluster of your own. And don't forget to thank them for their continued support of this show.  

Your host is Tobias Macy. And today, I'm interviewing Rob Cawley about Microsoft's Power BI platform and his work Power Pivot Pro to help users employ it effectively. So, Rob, can you start by introducing yourself?  

[00:01:53] Unknown:

Hi. I'm Rob Colley, and I sort of started my career at Microsoft,   13 years there working on various data technologies   like Excel and BI.   For the last 10 years, I've been outside of Microsoft running a consulting practice dedicated to the Microsoft data platform.   Yeah, just a sort of all around data enthusiast.  

[00:02:14] Unknown:

And do you remember how you first got into the area of data management?  

[00:02:18] Unknown:

Mostly by accident. There's always a funny story here, like I never really set out to do this. So it wasn't like a career goal or anything like that. I was just a, you know, a foot soldier recruited by Microsoft, you know, and put where they wanted me in 1996.   But via reorg   and all kinds of other accidents,   I ended up being   sitting next to the Excel team. And then when the product I was working on at that time got canceled,   it was like the most obvious move was just to be absorbed,   absorbed by the Excel team.   And on a personal front, I had, in the late nineties at Microsoft, I had developed the fantasy football   hobby slash addiction   back before football analytics was a thing. It's back before   there were even websites dedicated to the to fantasy football. It was all, like, done all the scoring, even we were doing it in Excel.  

And so in the beginning,   Excel and, you know, what I started to later think of as analytics and business intelligence,   my personal interest in it started mostly from like using Excel to dominate my friends who weren't doing anything like this, you know, people who,   you know, fashioned themselves as big football fans but didn't actually care about the data And, you know, that competitive advantage was pretty,   was pretty intoxicating.   That was sort of like my gateway drug into the world.  

[00:03:39] Unknown:

Yeah. It's pretty amazing how ubiquitous   Excel is and the capabilities   that it provides for people to be able to answer questions and   context because it's just another portion of the Microsoft Office suite, and it's   fairly   natural to start using it for, you know, adding in formulas. And you can go quite far, far, and there are some pretty amazing things people have done with Excel. So it's interesting that that's where you got your start is with that team. So I'm wondering if you can give a bit of an overview about the Power BI product and some of the ways that your experience with Excel   has helped you in being able to   work on guiding that product and with helping users   now and in your consultancy to be able to leverage it effectively?  



[00:04:32] Unknown:

Let's start with something that I do. I very commonly will use this as an introduction if I'm speaking   like at a conference, for instance,   back when we did those in person. If you ever end up seeing me and speaking at a conference, well, I will have ruined this joke ahead of time, but   the audience, I asked them, you know, so what is the most popular programming language? The obvious answers come out pretty quickly.   C, Java, someone inevitably suggests SQL, which I think is clever and it's a correct answer. But the real answer to the question, and it's not even close,   is that Excel formulas   are the number 1 most popular programming language in the world.  

They pass every single   definition, every single criteria of what constitutes   a programming language.   It's just a functional programming language, which, you know, there are other things like LISP, for instance, that are also functional programming languages.   Excel is a programming language.   It's a programming platform. It's got its own, it's essentially like almost like its own   IDE.   Excel is the runtime   library as well for this programming language.   And all the other programming languages in the world, in in terms of the number of people who are using them, add them all together, they still don't come close   to Excel.  

So it's a really interesting way to view the world. Once you start to understand that the people who are slinging spreadsheets in business   are actually these like under the radar business programmers   and they're developers,   it changes the way that you look at the ecosystem   of what's going on. And this is building towards your specific question, which is sort of how does Power BI fit in? The other thing is that when I got into BI, when I was introduced to business intelligence,   and like in the early 2000s,   I was tapped to   run the Excel's investment   in business intelligence. Like business intelligence was still a very hot term back then. It was fresh.  

We did. We put a lot of effort in Excel 2007 into BI. And so I had to get a crash course on BI and what all that meant. Fast forward a number of years and   a lot of this experience   gained outside of Microsoft.   It doesn't matter   the traditional BI investments. It doesn't matter how much money or time   a company has spent on traditional   BI software   and projects.   When you go and you see how it's actually being used in the end,   it's all just being exported back to Excel.   These notions that we produce these reports and then business users will look at the reports and then make a decision based off of that report   is a fiction   that I believe   I was part of it. These things are really just query interfaces in the end for the Excel users.  

And   once you try to make your peace with this, you make your peace with the fact that the Excel user   and really the advanced Excel users, not like everyone that uses Excel is doing this sort of thing, but the Excel user is the crucial demographic   in every business in terms of data.   And Power BI,   when we were developing it, Power BI was built from the ground up with this assumption in the foreground of our mind, which is that for something to truly revolutionize, for something to truly change the world,   it has to be an upgrade   and a major upgrade   over Excel,   and it has to be learnable   by this particular   flavor of Excel user, who, by the way, you can identify them very quickly.  

Anyone that uses   the VLOOKUP function in Excel   or any of its cousins, there's a family of lookup functions, anyone who uses lookup functions   and or anyone that uses pivot tables,   that's like the 5%   of the business audience. They are shadow IT. They're the real BI and the real reporting for the world. That was the original mission, was to build something   to bring the robustness of real BI   to the Excel crowd.   As flexible as Excel is, and as widely known as it is, it is not scalable.   Every new question that you get asked   requires you to re engineer your spreadsheet.  

It is not nimble.   Even just pumping the most recent data through it, through your spreadsheet report.   Once you have a sufficient portfolio of these, you spend your whole week   just maintaining   your past work. So you end up almost like disincentivized   from innovation.   Last thing you would need is to build another spreadsheet that people like that then you have to maintain.   So that was the mission. I think that's important background, but your question is, you know, how does it actually work in the world? What does it, you know, mission is 1 thing, but what does it actually ended up as? Is that correct?  

[00:09:10] Unknown:

Yeah, I think that that's definitely useful context, and I think it's interesting to go from there to, yeah, discuss some of the ways that you would   actually use Power BI and maybe do some comparison to the broader business intelligence market and ecosystem,   and   what are some of the factors that will   lead someone to choose Power BI over something like a Looker or   some other business intelligence platform that has fancy dashboards   when, as you said, at the end of the day, it's mostly just gonna all add up back in Excel and somebody doing some further analysis on the reports that are generated.  

[00:09:46] Unknown:

Doctor. Yeah. So there's sort of like 2 distinct generations of BI tools that are worth talking about. There's the previous generation,   and this includes a lot of Microsoft stuff. A lot of Microsoft tools   fall into this, you know, previous generation category.   There's tools like SQL Server Reporting Services and SQL Server Analysis Services.   When I talk about sort of the failures of traditional BI,   I absolutely include those 2 previous products   in that failure. They were part of that industry. You know, there's others like that from that generation, like   Cognos and Business Objects were dominant for a while, and they're very much the same sort of characteristics as those other Microsoft tech. And then there's this new wave. You know, some of the names that you've mentioned, you know, Looker, Tableau, Power BI. First, let's differentiate between   Power BI and the traditional wave. I actually think it's differentiated from the traditional wave even more so than   its competitors in the 2nd wave. The 2nd wave competitors have more in common with the old tools than what they let on. And this is really funny for me because I was a software engineer who helped build a lot of these old tools.  

I helped create the dysfunctional   previous generation.   So it's almost like I'm atoning for the sins these days, and   there's something about the way that software was built, all of that software, all of that traditional software was built in a way that dictated   a very, very, very heavy   waterfall   type of methodology.   Like everything starts with requirements gathering.   Everything starts with writing this gigantic requirements document.   Lots and lots of meetings, lots of time is chewed up just producing   the specification.   And it turns out the specification isn't worth very much.  

It's incomplete.   It contains all kinds of misunderstandings.   It gets reinterpreted   down the line by the developers.   It's a massive dysfunction,   but the thing is you can't work any other way because   the tools move too slowly.   You know, like you can't sit in the room as a business stakeholder. You can't sit in the room with the developer   of the traditional   solution   and watch them work.   Days would go by before you had anything to provide any sort of feedback on. You do this asynchronously. You do it via requirements docs.   And even the creation of those documents sometimes takes weeks,   maybe longer, maybe months.  

The number 1 differentiator right off the bat about Power BI versus the traditional tools   is that it is just so much   faster.   It's not even just faster.   It enables a completely different method of working.   It's now completely   feasible   to sit down with a blank canvas   and a business stakeholder   and load data like in front of them   and begin building the solution   in real time collaboratively   with them,   and you bypass   all of that requirements gathering process, and you also, by the way, bypass the vast majority of the misunderstandings,   And also you fast forward the inevitable   experience when the business stakeholder   sees exactly what they asked for. You've delivered exactly what they asked for. They immediately realize   that that isn't good enough,   and they don't realize that until they've seen it. And so this iterative process that churns,   you know, for weeks months,   sometimes very literally   can be compressed into like hours or a single day,   and the entire project   moves at an order of magnitude,   greater pace. It's a lot faster, and therefore it's a lot more cost effective.  

And the results you get,   even though it's cheaper and faster and all of that, the results you get are also qualitatively   hands down superior   to what you would get   out of the old method. It's a tool that allows us to actually work the way that human beings are meant to work.   And the old tools just failed that test. And I didn't know that until   I started to see how these new tools worked, how Power BI worked in the wild.   And I'll be honest, even I was surprised.   I did not expect it to be   this different. You know, when you work on software projects for, you know, for 13 years,   you become a bit of a hardened cynic   about,   you know, the quality of what you're gonna produce.  

It really kind of blew my hair back when I started to experiment with Power BI in the real world   and experience how different it actually was.  

[00:14:21] Unknown:

In terms of the actual work flows for somebody who is building reports within Power BI, there are a number of different aspects that I think we should dig into, 1 of which being how you handle   iterations of a report where in the sort of Excel world, you might have 15 different copies of the same report stored in a directory with different suffixes of, you know, version 1, version 2. This is the final 1. No. Now this is the final 1.   And so that's where a lot of the sort of business intelligence as code comes in where you can use version control systems to be able to handle that succession and that iteration and be able to go back to different points in time or branch and merge back.   What are the capabilities in Power BI for being able to handle that iterative process and be able to do some compare and contrast between   version 1 of a report version versus version 2 of a report and how well they work or being able to maintain history of a report so that you can have that context of this is how it changed and why.  



[00:15:20] Unknown:

The first thing that it does,   this isn't terribly unique to Power BI, but the richness that it brings to this story, I think, is unique. But the the first thing is, is that, like, most consumption of Power BI reports, you know, the way that you do it is 1 version of the truth style where it's, it's a published version and you have a URL to it, or you have an app, you know, it's server based. And so you don't get this   end   user generated   proliferation of versions, which you absolutely get   in the Excel world. And then the next thing it brings to that is that, like you were saying with the suffixes and everything, like there's a lot of sort of explosion of files in the Excel world   that is merely compensating for the shortcomings   of Excel. You know, like Excel is a programming language and it's learnable by business users and it's incredibly flexible. And so that's why it's so ubiquitous.  

It's really kind of a wonder of the world, but at the same   time, it's kind of a terrible programming language. Like once you view it from a, from a programming language   type of standpoint, there's no encapsulation. There's all kinds of, there's no subroutines, you know.   And so, a single Power BI report   will literally answer like an almost an infinite combination of questions that you can throw at it, and it will contain,   thanks to the in memory compression engine,   they can   contain vast quantities of data   that'll run very, very cleanly. Whereas, you know, Excel would bog down pretty quickly with like, you know, even a few months of history sometimes.  

And so you can have like 1 mothership essentially of a data model, and that's something we're gonna have to talk about is the data model. 1 mothership of a data model powering a handful of reports,   but each 1 of those reports is serving   a role that would traditionally have to have been served by, you know, maybe dozens   of sort of traditional SQL driven reports or Excel reports. So you just, right off the bat, you just knock a couple of zeros   off of the quantities involved. And that makes the whole, the whole versioning problem becomes   just so much more calm and sane   as a result.  

Now, from the development standpoint,   you know, if you have multiple developers   working on a single Power BI file, you know, I'm still in touch with my buddies at Microsoft, and they don't have   good version control. I mean, they have, obviously, we have check-in and check out, but we don't have merge. We don't have change merge yet.   And I think this has a lot to do with the fact that they've been innovating very, very, very quickly in the file format,   and they need to stabilize that.   They know about it. Microsoft knows that   the team driven development of a shared Power BI model is still 1 thing that they have to address. It's on their roadmap.  

I don't know what the timing of it is, but at our company, we have a bunch of consultants that are working in Power BI all the time, and   it's amazing how much 1 person can do on their own. It doesn't actually require a team   most of the time to execute a particular project,   whereas the traditional world absolutely did require a team. So we don't run into this as often   as you would expect, but we still run into it enough that it's like you're sort of having to use like verbal hallway check-in, check out, Like, okay, I'm done making my changes. Now you go. That type of thing.  

[00:18:35] Unknown:

In terms of the access to the data,   from when I was looking at the documentation   for   Power BI, it looks like there are a few different implementations where you've got the desktop app for being able to actually build the reports. You've got the report server if you wanna be able to run it in your own environment or the hosted version where you can publish reports to from the desktop.   How do you handle things like access control and security   for being able to pull in data sources?   And for somebody who's producing the data assets or providing access to those different data sources for the peep person building the report, how do you handle things like master data management or   consistency   of definitions for the ways that you should be thinking about the different data assets in terms of how you're structuring the reports?  



[00:19:25] Unknown:

There are a number of different questions hiding in there. So, you know, first of all, let's start with the really, the only responsible way to share or publish a Power BI report   is to publish it to a server. And like, you know, the cloud product, their cloud service   is the runaway number 1 choice. If you work in an organization   that is   open to using the cloud, which is basically everyone now, the Power BI cloud service is definitely your best bet. They have that other sort of on premises   server product   called Power BI Report Server.  

And that's useful in cases, you know, primarily where you've got someone that's blocking you from using the, you know, a cloud service for some reason.   The cloud service, by the way, so the desktop app that you use is a Windows only desktop app,   and it is the IDE essentially. It's where you develop, it's where you develop your Power BI reports and your data models and all that kind of stuff. And it would work as a runtime.   You don't want to be seduced by that. You really do want to publish what you build   to the server and then point your users to that. And that right off the bat gives you   the ability to allow people to interact with it, sort of like it's like read only. Even though it's interactive, you know, you can change filters, you can drill down, things like that. Yeah. So once it's on the server,   you can give people the interactivity   without giving them the right   to download, you know, essentially the source code. They can't download the thing that you worked on. They can't get ahold of all of the source data. They can't get ahold of the business logic.  

And then that also gives you the opportunity to provide   things like   what's called row level security,   which you don't always need, but when, you know, you very often do so that   when 2 people look at the same report, they're only seeing sort of their own corner of the org chart in terms of the data. You don't have to build a separate, you know, you don't you don't have to clone the report in 2 different for 2 different people,   bake the filter into the report. It can just be a dynamic filter based on who's logging in. Those 2, you know, features, they go a long way towards addressing   the concerns about putting this out amongst the users,   you know, the people who are gonna be consuming it, the business users.  

Then there's the upstream stuff. You were talking about,   you know, things like master data management, which, you know, that happens, you know, much further upstream,   you know, from the publishing.   And,   you know, the thing that Power BI is really, it's really good at is that   doesn't, it doesn't care where your data is.   It has its own internal cache that you can choose to use   once you've connected it to things. And most of the time, it's better to use that cache   even when you wouldn't expect it. Once Power BI has connected to your data,   it sort of becomes like this, all those data sources. Anything useful is almost always built from multiple data sources as opposed to 1.  

And Power BI is amazing   at   quickly   splicing together,   you know, all of your different silos of data in your organization   into an integrated all up view of your business or whatever it is that you're doing.   And this allows you to skip   a lot of the   traditional engineering processes,   like the old tools, even the old tool from Microsoft. It was like step 1,   get all of the data that you care about into a single instance of SQL Server.   What an impractical   and unnecessary thing that was.   And the reality in the world, I don't think this is at all controversial, is that it's always a blend of, of sort of like really   disciplined structured sources   of data with, you know, with sometimes a little bit more of the less, the less closely federated data, like, you know, it's like third party sources,   you know, things that, things like the, you know, the ERP system or whatever that we haven't quite finished integrating into the data warehouse yet. You know, it's never quite done.  

And so it's very common for us to be working with our clients on solutions that,   that do. They span all of these, you know, multiple different silos   of operational systems, line of business systems, ERP systems,   and cobbling together,   although cobbling makes it sound so weak, it's actually, you know, splicing together   for the first time   an all up view.   We were working recently with a client, you know, with a publicly traded company,   you know, built by acquisition, lots and lots of acquisitions that have built them up, and they literally didn't have,   for the CEO of this, this major organization, they did not have   a single integrated   accounts receivable report.  

Just no dashboard about how much money they're owed and when   by their customers.   They've never needed it enough apparently,   but also they've   never been able to do it because   every single subsidiary had their own AR   report.   But what does that mean for the overall company? You know, we have no idea.   With COVID,   all the rules changed and suddenly became incredibly crucial that the CEO know, like   up to the day level,   what their cash flow is going to look like. And, you know, they just had never had it before. And we were able   to pull that together for them in, I think in about 36 hours.  

So, you know, Power BI itself isn't an answer to the data quality problem. It can integrate with anything that you've got, and it does have some things that help. So like the Power Query Language,   the M language   that Power BI has, it's, you know, it's a, you know, language developed by Microsoft for, for data prepping and cleansing and things like that. They've done a really good job with that, with that tool set, really good job with that language,   and they've also set it up with this thing called Dataflows   that allows you to essentially build   cloud based ETL   through a GUI even, you know, there's a language that you can hand edit if you want. And, you know, and oftentimes you need to, but, and then you can share that, that data source,   you know, across multiple different data models, multiple different reports.  

They're very much aware of, you know, kind of the the centralization   and   discoverability   problems with these sorts of things. But, you know, Power BI doesn't directly address something like master data management.   The last thing I will say though on the topic is basically like that's upstream from Power BI. You know, you still need it, but it's not something that Power BI directly does for you. You need other tools for that. However, though,   bad data quality   cannot hide from Power BI for more than a couple of minutes.   The places where your data is deficient   and might have been deficient for years,   you know, undetected   tends to just jump off the page   when you're working with it in Power BI. And it's, it's almost annoying.  

Like when we're working on a project,   how quickly sometimes we can become stymied by a data quality issue   that has existed forever. We had nothing to do with it. You know, the people we're working with it at the company had nothing to do with it. Like, it it's just been there forever, but there's a saying that I have, which is that at a company,   the only sins that ever get noticed and addressed   are ones which   significantly interrupt the flow of money.   You know, if we stop paying someone,   they're gonna complain. If we stop getting paid, we're gonna complain.   Everything   else just waits until a good BI tool comes along and people trying to actually see what's going on, and that's where the buck stops, you know, and we have a client right now that, you know, what they're doing right now is implementing   via Power BI. We discovered that   the people in their warehouse weren't and still aren't doing anything remotely resembling a competent job   taking inventory. They've, you know, they're just miscoding products left and right. You know, they're   mislabeling,   you know, unit size, you know, pallets versus cases.  

And   so, Power BI has sort of become the reason   why there's now a data quality effort afoot. These problems aren't new.   They've never been visible before, and they've never actually caused a problem because   they weren't monitoring inventory close enough anyway. Now that they're doing it, and they're keeping an eye on inventory very closely,   now data quality is the problem. And so, now we're into the data quality phase on that project, which wasn't anticipated,   but it's necessary.  

[00:27:49] Unknown:

And in terms of being able to monitor the data quality, as you do start to adopt   principles   to ensure that there is an adequate amount of freshness in the data, that it's getting updated on the right schedule,   and understanding   what the lineages of the data flows at the point that you're connecting to them from Power BI.   What capabilities does it have for being able to expose that information whether by pulling from a metadata repository   or pulling in the various   health signals that you might embed into the records as they're delivered to the destination?  

[00:28:27] Unknown:

So it's sort of a 2 part answer. Like, Power BI is very transparent   about where it's getting its data. It's not something that, you know, that it hoards and it's actually, it's actually really, it's very centralized in terms of how you, you know, you, you would audit   a data model. You find a report and   you're curious about the what's feeding that report.   You can trace that to the data model that powers it.   And then within the data model, you can then very quickly trace where all of its different,   different data sources come from. And it's   very easy to pick up that trail of breadcrumbs   at all times.  

1 of the things that we preach   is that   the expression language   that is used to power the reports,   which is distinct from, you know, I mentioned M and Power Query, which is basically an ETL   language.   DAX is a formula expression language to produce numerical results and aggregated results   that are then, you know, visualized as charts and various visual types on the canvas. This language called DAX   is   gorgeous.   I had nothing to do with it. I didn't have anything to do with building DAX when I was at Microsoft.   I had a ringside seat while others were doing it. When I brag about DAX, extol its, its virtues.  

It's not self serving.   Wish it was. I wish I had something to do with it, but I didn't. DAX is a real strength   of the Power BI product. There's really no 1 has anything like DAX. There's nothing out there like it. Nothing that can compete with it. Nothing that can touch it. And when you combine that with PowerBI's ability to splice across data silos, These are really subtle distinctions that the layperson doesn't really, isn't able to readily appreciate,   but the ability to build multi,   multi fact table data models   with just quick drag drop without tremendous amounts   of infrastructural   prep, and then layer over the top of that   an expression language as rich and as capable as DAX.  

You mentioned earlier, like the Lookers and the Tableaus of the world.   These are the things that those products don't have. And they also don't have something that measures up to M and Power Query either.   Tableau recently   started to introduce their own data model feature.   Power BI has been a data model   powered, you know, multi silo   powered product from the very beginning   and they have a huge, huge, huge lead there. So even though those 2 products look very similar on the surface, what's under the hood is very different. And I thought that was an important distinction to make, but at the same time, it's relevant to your question as well, which is this DAX expression language, I think even people who are, who get good at it, they tend to under use it. And 1 of the things we encourage   is to use the DAX expression language to essentially to become like a big part of your automation suite for detecting problems.  

And we don't even need to distinguish necessarily between data quality problems and real world problems,   because sometimes 1 masquerades as the other, and you need to know about it either way. And so   when you build a Power BI solution in contrast to like, if you build something like in R, for instance,   everything about Power BI is essentially declarative.   You're setting up a structure, a data model structure,   and you're   developing expressions that represent certain calculations that can then be deployed against that structure, you know, with a, basically with a click. And so 1 of the things we can do with DAX, for instance, is to, is to write essentially like robots in DAX that will go and scan   and scan your data at whatever granularity you think is appropriate   and looking for outliers.  

Outliers can be as simple as   just outside of their historical norm   by, you know, a certain number of standard deviations or something like that.   And it is amazing what this turns up.   When a very tiny corner   of the data pipeline   experiences an outage, and let's say it just starts returning all zeros or something,   when you get to the aggregate final dashboard,   sometimes that's not apparent. Sometimes that's gonna manifest as like a 1% difference   in your your top level metrics. Other times, you know, everything flat lines and you just know something's wrong, but the subtle ones are the scary ones.   You know, DAX,   you set up alerts, essentially DAX powered alerts.  

You can spot things that you would never have looked for. You would never have looked for it. And there are even some horror stories where this is not a data quality problem, where the data quality pipeline   was tip   top, but the operational   system,   like entire   segments of revenue   went to 0 because, you know, a system stopped issuing invoices for a particular kind of product.   And again, at the top level dashboard for the company, this manifest is maybe like a 2% difference. And, you know, that might just be within normal variation   month to month, right? So you don't, you don't necessarily   suspect anything's wrong,   but if you've got these formulas that are out there just like churning through and scanning, like at, you know, at hyper speed, you know, through all kinds of different combinations, even of customer demographics and products. It doesn't even have to be single tables of entities. It can be   sort of combinations of entities across different dimensions.  

And then just reporting the things that are outside normal variation,   it's astounding what you'll find. You'll even find fraud. You'll find people stealing.   And so data quality ends up being, you know,   like deliberately   overlapped   with operational quality. And it's amazing what it'll find.  

[00:34:07] Unknown:

Equilum's end to end data ingestion platform is relied upon by enterprises across industries to seamlessly stream data to operational,   real time analytics, and machine learning environments.   Equilum combines streaming change data capture,   replication,   complex transformations,   batch processing, and full data management using a no code UI.   Equilum also leverages open source data frameworks by orchestrating Apache Spark, Kafka, and others under the hood.   Tool consolidation and linear scalability without the legacy platform price tag.   Go to data engineering podcast.com/equilum   today, that's e qualum   to start a free 2 week test run of their platform, and don't forget to tell them that we sent you.  

And then another aspect of business intelligence   that is frequently a problem is the discoverability   of existing reports   where, again, with the Excel situation, you might have multiple people working in their own silos   performing the same calculations and analyses. And even if they're coming up with the same results, they don't know about it, or they might be coming up with slightly different results that ends up causing issues with the decision making.   So I'm wondering what capabilities exist within Power BI for being able   to surface useful reports and answer questions   in terms of making   a particular metric visible or   being able to help surface something that is   adjacent to or relevant to a particular question that somebody is trying to answer?  



[00:35:42] Unknown:

Here's where my software cynicism   will shine through. You know, in my time at Microsoft,   I ended up being   party to multiple different   features like this that were meant to help people discover   and reuse stuff.   And   it turns out that almost anything that an engineer or software engineering team comes up with, even if they've got, you know, customer research people on the team,   whatever we come up with almost never seems to work. The human beings just don't ever seem to cooperate. It's a very sticky problem.   And of course, you know, so the Power BI platform has those same features. It has the features you'd expect, you know, search for a report, search for this, search for that.  

But I mean, like,   unless someone, like, specifically,   you know, made you aware of it, it's even hard to get people to use those search features, it turns out. So that sounds really gloom and doom and, you know,   we should all give up type of thing. But but I actually don't think it's that way because,   first of all, like,   finding reports,   the reason that this problem existed in the first place   is that there were so many reports.   And I still think to this day, I talk about this all the   time, that the world understands that it needs storage. Of course, we need storage, right? We have to store the data. And then, of course, we need to be able to retrieve it. And you need to even be able to do that just to build operational systems that, you know, that make the business run.   Even if you were never gonna do any analysis whatsoever, you need storage and retrieval.  

And so   when you start doing BI   or analytics or whatever you wanna call it, you know, the first team that everyone turns to is the storage and retrieval team. You know, the people who've been doing that kind of work, like, so like the vast majority of business intelligence professionals, even to this day,   are people whose primary language is SQL. And I know this is changing, you know, even storage is changing   rapidly every day, but when you have a storage   background and a storage, not even the background, it's more the mindset.   Reporting services for Microsoft is the most popular product in their history in terms of BI. And it's just a way to basically visualize, you take a SQL query,   the results of a SQL query and render it as HTML. You know, that's essentially   its reason for existence.  

Basically what happens is, is that every single business question, every interesting business question almost always requires a new report to be built. No report   can be built to, you know, like in reporting services, you, you can't build reports to be, to be self-service.   And you also can't build reports to integrate across data sources,   like, which is something that I've been talking about sort of a lot in this discussion.   When you think about these reports in terms of how they're actually used,   what they're actually used in the end is as export sources for Excel. So if you think of them instead of as reports, if you think of them as queries,   now you understand why you have thousands and thousands and thousands of reports,   and most of them haven't been used   in years because a lot of them were commissioned for 1 particular purpose, used that 1 time and then put to bed.  

So in a Power BI environment, by contrast,   a single report is capable of doing   literally thousands of times the work as a traditional report can do. And so you might end up still with a lot of reports in your world, a lot of Power BI reports, but a lot is measured with many fewer zeros than in the old world. And because they can splice across data sources,   they're not bound to be used purely as export sources for Excel. Like they actually can't answer the question without additional follow on, you know, cobbling together in Excel.   So you end up with a much smaller number,   and then you have this thing called usage.  

So because it's server published   and you've got usage data,   the admin team,   and maybe multiple levels of admin team, can absolutely be watching   and monitoring   what sorts of reports and data models are being built, who's using them.   And it becomes a bit more of an oversight function   to say to teams   A and B, Hey, do you know that you're both using something very similar?   You're both reinventing the wheel.   Let's talk about consolidating those things.   Whereas   reuse   just doesn't come natural. Like people don't go out of their way to search for something that's already there. I think   that the world that we're landing in now is actually   much more realistic   in terms of, because that's what you really want in the end. You don't want wasted effort, right?  

And so in the Power BI environment,   the centralization   in particular models and their flexibility   combined with the oversight component through usage statistics that   the admins can monitor. I think that that's really the way to go. That's how we do it.  

[00:40:23] Unknown:

As far as your work with clients in your consultancy,   for people who are looking to take on a business intelligence effort or improve their visibility into their operations   through building these reporting dashboards and analytics.   What are some of the common points of confusion   or frustration that they encounter in being able to   represent things   effectively   and provide the information in a way that is constructive   to their end goals.  

[00:40:54] Unknown:

We could do a whole hour on that 1 question. Let me pick 1 thing. So, 1 of the things   that I recommend to people is that we not talk about our reports even as reports.   We stop thinking about our jobs as being   our mission. We tend to think of our mission in BI as it's right there in the name, intelligence, right? It's information.   And no, we're not, we're not in that business. None of us are in that business. We're in the business improvement.   You know, that's our real discipline is business improvement.   And   no report   is worth anything   until or unless   it translates   into   improved behavior.  

And, you know, okay, that sounds, that sounds obvious, you know, like it's, like it's a meaningless   statement, but it's kind of everything   when you make that your focus. So instead of starting with the data and working forward into what kinds of reports you can build from it,   if you start instead with thinking about the stakeholders, the people who are actually going to be using this thing when you're done with it,   and you work backwards   from what is their daily workflow?   What are the decisions that they have to make or can make   on a daily basis?  

You know, what knobs do they have? Do they have a knob that says increase production, decrease production, you know,   open new stores, close stores, whatever? It turns out that especially in larger organizations,   every role has a finite number of controls that they've got. I like to imagine them sitting   at something like a bat computer with these gigantic   1960s pop art buttons and levers on it. And like, what are the labels on those things? You know, like I said, like increased production, decreased production is a really simple example.   And if you work backwards from that,   with that mindset, you actually end up building something completely different.  

You don't end up building   reports.   You end up building sort of action loop advisors.   You end up building,   you know, things that   directly influence or assist them in making decisions and improvements.   And like a report   oftentimes   tries to do everything,   tries to do everything by being your source of information on everything.   And in the process, it ends up being good at nothing.   And when you think backwards from the action, this is really just acknowledging the fact that even reports, reports and data models   are software.  

You're using a Power BI software platform to build it, but they are software in their own right. And you'd never build software if you think about it as just like a software engineering practice. You wouldn't build software   without getting to know your users.   You wouldn't build software without getting to know what their pain points are. You wouldn't build software without   understanding what your users are, your audience, what they're leaving on the table.   It's a hard thing to do justice without   some anecdotes, but we have a lot of stories   about how dramatically   things have shifted with our clients   when they   replace the report mindset with the improvement mindset.  



[00:44:02] Unknown:

Jonathan Also, in terms of your experience consulting with various companies who might be across different business domains or verticals, what have you found to be the necessity for particular domain expertise   with the problem space that they're working in? And how much of your experience   in data modeling and visualization   and report generation   have you found to be   broadly applicable across those different domains?  

[00:44:29] Unknown:

Yeah. Yeah. So really great question. First of all, I mentioned earlier, you know, near the beginning that you can sit down with the stakeholder   and develop   in real time.   1 of the   key advantages of that is that you're now executing this project at distance 0   from the subject matter expert, from the domain expertise.   You know, like you're not the domain expert. I'm certainly not the domain expert. None of our consultants are in any particular,   you know, company.   The people we're working with at that client, those people are the subject matter experts there. And to have them,   you know, in the room in real time as this thing evolves   is critical.  

This is where the improved quality comes from. It's just, it's amazing. So   the old world, we had the technical skills to execute   and the subject matter expertise of the business   at great distance from each other,   communicating through a very, very low bandwidth, you know, like 2, 400 valve modem, right? And now, we're like this, like, you know, gigabit connection between the 2 because we're sitting in the same room and it's just night and day different.   It's not just faster. It's very different. Just like today's internet is incredibly different for 2, 400 baud internet. The other question is how, from our team's perspective,   you know, how important is   particular expertise in an industry? It's really funny, like the most important thing is,   you know, 1 of the things that we explicitly hire for   is   the ability to   very quickly understand   a particular business and its needs   rapidly.  

And our consultants, the ones that, you know, you could think of them as the developers and they're the ones developed this, you know, these solutions.   They are also these communicators.   We don't split those 2 roles   at our   company for good reason, because   as you split those roles, you're introducing more   communication cost and   more inertia into the system and you can't move at the pace that you would if it was just all in 1 brain.   But here's the funny thing. Once you have that plasticity,   it turns out that   let's say we have a manufacturing client   and they insist on someone from our team that's worked with a number of manufacturing clients.  

Then we go along with that. We send someone that's manufacturing expertise.   What it'll turn out is, is that the particular problem that we're dealing with at that moment   might not be 1 that our consultant has seen before. You know, just because it's in manufacturing, it just, that covers a lot of ground. It doesn't mean that that it's going to be   the same thing over and over again. In fact,   1 time I was working for a large, you know, 1 of the big 4 accounting firms and did some work for them many years ago in Power BI.   We're building a tax model   on first in, first out, FIFO   accounting.  

And we don't need to go into the details of it. You know, some people will know what that means. Some people won't.   Obviously, FIFO is probably familiar,   but with respect to accounting, it might not be something you've heard before. But anyway, it's pretty interesting. It's probably what you'd expect.   Years later, teaching a class,   someone took me aside during a break and was asking me some questions about   food. They were in a food distribution business and they were asking me questions about modeling spoilage   in their warehouses.  

And it turned out to be exactly the same problem as the FIFO accounting problem   for selling securities and for tax purposes and things like that.   So it turns out that the places where experience matter,   matter the most,   is number 1, you've got to have the subject matter expert   from that business   in the room with you.   And then secondly,   it's the patterns. It's the data patterns that you get exposed to over time   that make the difference.  

[00:48:05] Unknown:

In terms of your experience   working on the Power BI product and building it at Microsoft,   and now running a business to help support its end users, what are some of the most interesting or unexpected or challenging lessons that you've learned in that process?  

[00:48:20] Unknown:

First of all, is that none of it, none of it works the way that I thought it did. I went to college   and then went straight to Microsoft.   And so, you know, it was only like in 2010   that I first ventured out into the real world. You know, I kind of left the bubble, the controlled environment.   The good news, bad news, the most shocking thing for me,   just how far we have to go.   Because we work at such a fast pace, we burn through projects.   We get through them as fast as possible   for our clients.   That means by necessity to stay in business, we have to see a lot in a year. We work with a lot of clients.   You know, the velocity of our business is just   orders of magnitude different from the traditional firm that operates in our space. And   that's our big differentiator.  

So we get to survey. 1 of the side effects of that is that we get to survey   the world   at a pace that most people don't get to see. We get to see a lot. We get to see a tremendous amount of breadth.   And, you know,   everyone we work with, not everyone, but close to everyone,   they feel like this, you can just see it in them and   they'll even say it explicitly sometimes as a sort of like,   they'll say like,   Oh, yeah, we're really primitive here. We're really primitive at this company. You know,   we're way behind the curve.   But everyone's saying that,   and they wouldn't know that because, you know, everyone generally is, you know, you work on average for like 5 years at a company.  

You just don't get the exposure that we do.   Everything   is in the dark ages still.   When you read, you know, magazines and things like that from the industry and you listen to, you know, the analysts from Gartner, etcetera, talk like,   you know, there's a sexiness to talking about the next big thing. You know, the next big thing is this, the next big thing is that.   And, you know, all those things that are talked about, they're valuable and they have their place. But what I have found to my utter surprise   is that the next big thing   in analytics, in business intelligence, the next big thing is doing the basics right   for the first time ever.  

And we are still in the earliest, earliest, earliest phases of that.   We have some clients that we've worked with for a number of years,   and   for them,   you know, we've watched them mature. We've watched them mature from, you know, not having the basics.   The things that everyone assumes. You know, when I work at company X, I assume every other company in the world has the basics done right, and they don't. When you start to get the basics done right for the first time ever, that's when you start to realize that BI is really just part of a bigger ecosystem.   It's part of the, like I was talking about earlier, it's part of the, it's a crucial component in improvement,   and that means action.  

And so it's   really rewarding and gratifying to see   our clients we work with sort of after   getting a hold of the BI problem and actually making, you know, significant strides against it and getting it into a place where they actually, they actually turn around after a little while and they say, Okay, what next?   And that what next question is just absolutely,   it's just, What a good feeling.   Because the world's never gotten,   gotten to next.   BI has never gotten out of,   never gotten caught up what it was always meant to be in the first place. So I think that's the most surprising thing to me is just like,   just how   much work   and, you know, the good news people listen to this, this podcast is that how much opportunity   remains. It's just, it's like a gold rush   that's out there and you don't necessarily have to be doing the absolute most, you know, advanced machine learning, this, that kind of thing to be part of that gold rush. And, you know, we do that kind of stuff too,   but, you know, majority of our business is doing the things that are just really fundamental. When I say basics, it's not really basics. It's like   the things that we should take for granted, the things that we should take for granted in terms of what we can see through our business data, we've never gotten there as a society, but we're getting there now.  



[00:52:20] Unknown:

For people who are considering what business intelligence solution to go with and they're interested in Power BI, what are the cases where it's the wrong choice?  

[00:52:29] Unknown:

Now, the cases where it's the wrong choice end up being   more like   when you do view it like as a programming language. I'll give you an example. As powerful as DAX is,   DAX doesn't have recursion.   There's no concept of recursion   in DAX, which, you know, we take for granted in, you know, in procedural programming languages.   Right off the bat, you think of all the different places where you use recursion if you're a programmer.   You go, oh, well, if you can't do recursion, then it can't do what I want. Well, it turns out that even like 90% of those cases, the features of DAX allow you to do those things without,   without requiring recursion.  

For example, you never have to worry about sort. You know, sort is just built into the engine and ranking and all that kind of stuff. It's all just built into the engine. So you don't have to worry about any of those kinds of things where you might, you know, program your own recursion.   But there are occasional problems   where   you have to calculate 1 result   to feed forward into the next result, into the next result, into the next result, into the next result. And the way I would describe it is, and this is also a contrast with Excel because Excel can actually do this   pretty well in general. If you have a small amount of input data   and then a set of rules,   and then from that small amount of input data instead of rules, you produce a larger amount of data,   Power BI is not a good tool for that.  

Simple example of this would be like an Excel based income statement where you sort of like   are simulating   what the business might look like in 12 years or not in 12 years, in 24 months.   If you add 2 customers a month for the 1st 6 months and then 3 customers a month, and then you lose some 20% of them each month, you know, attrition rates and things like that. That is you start with a small amount of real data or assumptions,   and then you use rules to generate,   you know, 24 months of data. You end up with more, you know,   more square footage of data than you started with. That's not a good Power BI scenario.  

Power BI is the other thing. Power BI goes the other way. You start from larger amount of data and you crunch it down to a smaller amount of data, like a set of metrics   that then might be, you know, visualized as charts, but like you end up with a smaller set of numbers than what you originally had. And a lot of recursive solutions   are the kind that need to produce   a lot of data as intermediate steps   and then feed that forward into the subsequent steps. You know, there is some sort of mathematical proof that all recursion can be turned into iteration,   transformed into iteration.  

And, you know, DAX has a lot of iteration in it. There's a lot of iterative functions in DAX. It's, it's really good at that. But I mean, sometimes I've tried some of these sometimes where, like, you start to feel like you're doing, like, differential equations, you know, like Laplace transforms. You're trying to, like, restate your recursive problem as an iterative problem, and it just gets really hairy. So   in all the years that we've been working with this, we've only run into this probably 3 or 4 times.   And I think it's because   those sorts of problems tend to be solved with other tools by default anyway.  

We kinda always have our our antenna deployed   to try to sense when something like that's going to happen. Triple exponential smoothing.   Yeah, you're not going to do that in DAX.   It's just not that just not that kind of thing.  

[00:55:38] Unknown:

Are there any other aspects of the Power BI platform or your experience helping people adopt it and utilize it effectively or the overall business intelligence space that we didn't discuss yet that you'd like to cover before we close out the show?  

[00:55:52] Unknown:

I think it's just maybe it's worth mentioning that it's just, like, almost criminally   inexpensive.   The desktop tool for developing and testing is, you know, is completely free.   The cloud service product   is $10 a month per user. You know, Microsoft prices for the enterprise.   Microsoft   is always looking, you know, for the the 10, 000 seat   account, the 10, 000 seat customer.   They have to price at that level.   And even if you work at a huge company,   the departmental cost for Power BI,   you know, it's just so   dirt cheap.   It's an amazing tool. I say this all the time. I don't work for Microsoft anymore.  

I'm as much of a Microsoft cynic as I am a patriot. They don't pay me anymore.   If there was a better tool out there, my company would be using   it, and we're not. We're using this 1 because it is the best. We believe it.   For that thing that we believe to be the best,   for that thing to be the most affordable   product   on the planet,   It's just so counterintuitive. It's almost like you'd be more interested in it if it was 10 times as expensive.   Some of the things going on in the innards of Power BI, the only things in my entire Microsoft career that felt like science fiction,   couldn't believe some of the things that they were pulling off, like the speed, the performance, the data capacity that they were able to pull off. This thing,   you know, Microsoft has a reputation as being, you know, kind of like, almost like the IBM of old, right? Like it's, you know, it's corporate and it's, you know, vanilla and boring   and they name their products terribly boring names, you know?  

I'll tell you, Power BI is   incredibly   sophisticated,   incredibly cutting edge under the hood. It's still not matched.   And on top of that, it costs next to nothing. So   the cost of entry is really low,   but please, please, please, if you start messing around with it, you start messing around with it,   you've got to understand   that it's not like SQL. You don't want to feed it 1 big wide table.   You want to feed it separate tables   representing   each particular data source, each type of transaction, each type of inventory,   and each type of entity, like, you know, customer or product line or things like that. And then relating these tables together   to produce a   dynamic data model.  

Because when you start doing all of your work in SQL or whatever storage language you like   to feed it into Power BI, you tend to follow some of the old practices of reporting languages.   And when you do that, you end up throwing out a tremendous amount of the value   of Power BI. So, you know, you've got to understand, it's not hard, but you've got to understand what it is to build a data model, and you need to be building basically a star schema   under the hood   and not big, wide, flat views.  

[00:58:40] Unknown:

For anybody who does want to get in touch with you and follow along with the work that you're doing, I'll have you add your preferred contact information to the show notes. And as a final question, I would just like to get your perspective on what you see as being the biggest gap in the tooling or technology that's available for data management today.  

[00:58:56] Unknown:

I think that the biggest gap is that, and Microsoft   is somewhat early to this party, even though they still have a lot of work to do. The biggest gap is that, is that thing I was talking about earlier, which is that the recognition that BI   is really just part of the improvement cycle   and so it needs to translate into action.   And, you know, we tend to think of our reports   as standalone applications.   I don't even think of them that way, but we think of them as islands in a way. You go in there and you ask some questions and you get some answers   and then you leave   and you go do something.  

And   I think that,   you know, let's say 5 years from now, maybe less,   we will   see organizations like Gartner and Forrester.   They will be   including in their evaluation   criteria   as a top level criteria,   how   closely   taking action   is integrated   or can be integrated into the dashboards.   If I recognize   that particular product line   is running low on inventory, for instance, that's what I find out on a report.   Why do I have to pick up the phone or go look somebody up or leave that environment to go to a different system   and navigate to the right spot in that system?   Like the report knows in a sense, it knows   the identifying information   and like,   why don't we have instead,   you know, a button or a link or some sort of verb   right there on the report that says, you know, increase production level, you know, on this date, we wanna make X more or at bare minimum,   navigate me to the right place. Like go log me in   to the other system   and take me to the right page and set the filters appropriately so that I'm looking at the right product line and the right production day so that I can take that action.  

And I think we're gonna, we're gonna start to think of, of BI and reports and dashboards   as much more of   a crossroads, as opposed to just like   a 1 way flow of information.   And this is, again, it's 1 of the things that we see with our clients after they've had, you know, a year or 2 of maturing   in sort of this new form of BI, the 1 that actually works.   This is where their gaze inevitably turns next   as they start saying,   Ah, how do we integrate more action into this?   And then, then you circle back to the technology and you say, Okay, what does the technology offer Microsoft   kind of already is, but yeah Microsoft kind of already is. But yeah, that's the, I think that's sort of the next frontier   is just,   again,   it's 1 of those fundamental things that we just never have gotten around to, But it's crucial. It's   absolutely crucial.  



[01:01:44] Unknown:

Thank you very much for taking the time today to join me and discuss your work with Power BI, both at Microsoft and as a consultant. It's definitely a very interesting platform, and I appreciate your insights and enthusiasm. So thank you again for all of your time and effort, and I hope you enjoy the rest of your day. Likewise. It's been my pleasure.   People listening. Don't forget to check out our other show, podcast.init@pythonpodcast.com   to learn about the Python language, its community, and the innovative ways it is being used.   And visit the site at dataengineeringpodcast.com   to subscribe to the show, sign up for the mailing list, and read the show notes. If you've learned something or tried out a project from the show, then tell us about it. Email hosts at data engineering podcast.com   with your story. And to help other people find the show, please leave a review on Itunes and tell your friends and coworkers.