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?
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.
Unknown: And do you remember how you first got into the area of data management?
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.
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?
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?
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.
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.
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.
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.
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?
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.
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?
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.
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?
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.
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.
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.
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?
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.
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?
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.
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?
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.
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?
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.
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.
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.
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.