Category: Data Governance

Roche’s Maxim of Data Transformation

On By Matthew RocheIn Azure Data Factory, Data Governance, Dataflows, Power BI, Power Query3 Comments

According to the internet, a maxim is a succinct formulation of a fundamental principle, general truth, or rule of conduct.[1] Maxims tend to relate to common situations and topics that are understandable by a broad range of people.

Topics like data transformation.

Update June 16 2021: The video from my June 11 DataMinutes presentation is now available, so if you prefer visual content, the video might be a good place to start.

Roche’s Maxim of Data Transformation[2] states:

Data should be transformed as far upstream as possible, and as far downstream as necessary.

In this context “upstream” means closer to where the data is originally produced, and “downstream” means closer to where the data is consumed.

By transforming data closer to its ultimate source costs can be reduced, and the value added through data transformation can be applied to a greater range of uses. The farther downstream a given transformation is applied, the more expensive it tends to be – often because operations are performed more frequently – and the smaller the scope of potential value through reuse.

I’ve been using this guideline for many years, but I only started recognizing it as a maxim in the past year or so. The more I work with enterprise Power BI customers the more I realize how true and how important it is – and how many common problems could be avoided if more people thought about it when building data solutions.

Please note that this maxim is generalizable to data solutions implementing using any tools or technology. The examples below focus on Power BI because that’s where I spend my days, but these principles apply to every data platform I have used or seen used.

In day-to-day Power BI conversations, perhaps the most common question to which Roche’s Maxim applies is about where to implement a given unit of logic: “Should I do this in DAX or in Power Query?”

Short answer: Do it in Power Query.

If you’re ever faced with this question, always default to Power Query if Power Query is capable of doing what you need – Power Query is farther upstream. Performing data transformation in Power Query ensures that when your dataset is refreshed the data is loaded into the data model in the shape it needs to be in. Your report logic will be simplified and thus easier to maintain, and will likely perform better[3] because the Vertipaq engine will need to do less work as users interact with the report.[4]

But what if you need data transformation logic that depends on the context of the current user interacting with the report – things like slicers and cross-filtering? This is the perfect job for a DAX measure, because Power Query doesn’t have access to the report context. Implementing this logic farther downstream in DAX makes sense because it’s necessary.

Another common question to which Roche’s Maxim applies is also about where to implement a given unit of logic: “Should I do this in Power BI or in the data warehouse?”

Short answer: Do it in the data warehouse.

If you’re ever faced with this question, always default to transforming the data into its desired shape when loading it into the data warehouse – the data warehouse is farther upstream. Performing data transformation when loading the data warehouse ensures that any analytics solution that uses the data has ready access to what it needs – and that every solution downstream of the warehouse is using a consistent version of the data.

From a performance perspective, it is always better to perform a given data transformation as few times as possible, and it is best to not need to transform data at all.[5] Data transformation is a costly operation – transforming data once when loading into a common location like a data warehouse, data mart, or data lake, is inherently less costly than transforming it once for every report, app, or solution that uses that common location.

A much less common question to which Roche’s Maxim applies might be “What about that whole ‘not transforming at all’ pattern you mentioned a few paragraphs back – how exactly does that dark magic work?”

Short answer: Have the data already available in the format you need it to be in.

That short answer isn’t particularly useful, so here are two brief stories to illustrate what I mean.

Many years ago I was working with an oil & gas company in an engagement related to master data management. This company had a fundamental data problem: the equipment on their drilling platforms around the world was not standardized, and different meters reported the same production data differently. These differences in measurement meant that all downstream reporting and data processing could only take place using the least common denominator across their global set of meters… and this was no longer good enough. To solve the problem, they were standardizing on new meters everywhere, and updating their data estate to take advantage of the new hardware. My jaw dropped when I learned that the cost of upgrading was upwards of one hundred million dollars… which was a lot of money at the time.

Much more recently I was working with a retail company with over 5,000 locations across North America. They had similar challenges with similar root causes: their stores did not have consistent point of sale (POS) hardware[6], which meant that different stores produced different data and produced some common data at different grain, and analytics could only take place using the least common denominator data from all stores. Their solution was also similar: they upgraded all POS systems in all stores. I don’t have a dollar amount to put with this investment, but it was certainly significant – especially in an industry where margins are traditionally small and budgets traditionally very conservative.

Both of these stories illustrate organizations taking Roche’s Maxim to the extreme: they transformed their key data literally as far upstream as possible, by making the necessary changes to produce the data in its desired form.[7]

Each of these stories included both technical and non-technical factors. The technical factors revolve around data. The non-technical factors revolve around money. Each company looked at the cost and the benefit and decided that the benefit was greater. They implemented an upstream change that will benefit every downstream system and application, which will simplify their overall data estate, and which corrects a fundamental structural problem in their data supply chain that could only be mitigated, not corrected, by any downstream change.

There’s one additional part of Roche’s Maxim that’s worth elaborating on – what does “necessary” mean? This post has looked at multiple scenarios that emphasize the “as far upstream as possible” part of the maxim – what about the “as far downstream as necessary” part?

Some factors for pushing transformations downstream are technical, like the DAX context example above. Other technical factors might be the availability of data – you can’t produce a given output unless you have all necessary inputs. Others may be organizational – if data is produced by a 3rd party, your ability to apply transformations before a given point may be constrained more by a contract than by technology.

Still other factors may be situational and pragmatic – if team priorities and available resources prevent you from implementing a unit of data transformation logic in the data warehouse, it may be necessary to implement it in your Power BI solution in order to meet project deadlines and commitments.

These are probably the most frustrating types of “necessary” factors, but they’re also some of the most common. Sometimes you need to deliver a less-than-ideal solution and incur technical debt that you would prefer to avoid. The next time you find yourself in such a situation, keep this maxim in mind, and remember that even though it may be necessary to move that data transformation logic downstream today, tomorrow is another day, with different constraints and new opportunities.

Callout: This may be my maxim, but this isn’t the first blog post on the topic. Stuart Box from the UK data consultancy BurningSuit blogged back in March and was in first with this excellent article.

[1] Or a men’s magazine. I really really wanted to use this more pop-culture meaning to make a “DQ” joke playing on the men’s magazine “GQ” but after watching this post languish in my drafts for many months and this joke not even beginning to cohere, I decided I should probably just let it go and move on.

But I did not let it go. Not really.

[2] If you think that sounds pretentious when you read it, imagine how it feels typing it in.

[3] The performance benefit here is not always obvious when working with smaller data volumes, but will become increasingly obvious as the data volume increases. And since the last thing you want to do in this situation is to retrofit your growing Power BI solution because you made poor decisions early on, why not refer to that maxim the next time you’re thinking about adding a calculated column?

[4] This post only spent a week or so in draft form, but during this week I watched an interesting work email conversation unfold. A Power BI customer was experiencing unexpected performance issues related to incremental refresh of a large dataset, and a DAX calculated column on a table with hundreds of millions of records was part of the scenario. The email thread was between members of the engineering and CAT teams, and a few points jumped out at me, including one CAT member observing “in my experience, calculated columns on large tables [can] increase processing times and also can greatly increase the time of doing a process recalc… it also depends on the complexity of the calculated column.”

I don’t have enough knowledge of the Veripaq engine’s inner workings to jump into the conversation myself, but I did sip my coffee and smile to myself before moving on with my morning. I checked back in on the conversation later on, and saw that a Power BI  group engineering manager (GEM) had shared this guidance, presented here with his approval:

“From a pure perf standpoint, its true that we can say:

  • The most efficient approach for a large fact table is to have all the columns be present in the source table (materialized views also might work), so that no extra processing is necessary during the import operation (either in Mashup or in DAX)
  • The next most efficient approach for a large fact table is usually going to be to have the computation be part of the M expression, so that it only needs to be evaluated for the rows in the partitions being processed
  • DAX calculated columns are a great option for flexibility and are particularly useful for dimension tables, but will be the least efficient compared to the above two options for large fact tables”

That sounds pretty familiar, doesn’t it? The GEM effectively summarized Roche’s Maxim, including specific guidance for the specific customer scenario. The details will differ from context to context, but I have never found a scenario to which the maxim did not apply.

Yes, this is a challenge for you to tell me where and how I’m wrong.

[5] Just as Sun Tzu said “To fight and conquer in all our battles is not supreme excellence; supreme excellence consists in breaking the enemy’s resistance without fighting,” supreme excellence in data transformation is not needing to transform the data at all.

[6] That’s “cash registers” to the less retail inclined readers.

[7] If you feel inclined to point out that in each of these stories there is additional data transformation taking place farther downstream, I won’t argue. You are almost certainly correct… but the Maxim still holds, as the key common transformations have been offloaded into the most upstream possible component in the data supply chain. Like a boss.[8]

[8] Like a supremely excellent[5] boss.

Data Governance and Self-Service Business Intelligence

On By Matthew RocheIn Data Culture, Data Governance, Power BI2 Comments

When you hear someone say that governance and self-service BI don’t go together, or some variation on the tired old “Power BI doesn’t do data governance” trope, you should immediately be skeptical.

During a recent Guy in a Cube live stream there was a great discussion about self-service BI and data governance, and about how in most larger organizations Power BI is used for self-service and non-self-service BI workloads. The discussion starts around the 27:46 mark in the recording if you’re interested.

As is often the case, this discussion sparked my writing muse and I decided to follow up with a brief Twitter thread to share a few thoughts that didn’t fit into the stream chat. That brief thread turned out to be much larger and quite different than what I expected… big enough to warrant its own blog post. This post.

Please consider this well-known quote: “No plan survives contact with the enemy.”

Please also feel encouraged to read this fascinating history of the quote and its attributions on the Quote Investigator web site.

In his 1871 essay Helmuth von Moltke called out an obvious truth: battle is inherently unpredictable, and once enemy contact is made a successful commander must respond to actual conditions on the ground –  not follow a plan that is more outdated with every passing minute.

At the same time, that commander must have and must adhere to strategic goals for the engagement. Without these goals, how could they react and respond and plan as the reality of the conflict changes constantly and unpredictably?

Implementing managed self-service business intelligence – self-service BI hand-in-hand with data governance – exhibits many of the same characteristics.

Consider a battlefield, where one force has overwhelming superiority: More soldiers, more artillery, more tanks, and a commanding position of the terrain. The commander of that force knows that any enemy who faces him on this field will fail. The enemy knows this too.

And because the enemy knows this, they will not enter the field to face that superior force. They will fade away, withdraw from direct conflict, and strike unexpectedly, seeking out weaknesses and vulnerabilities. This is the nature of asymmetric warfare.

The commander of the more powerful force probably knows this too, and will act accordingly. The smart commander will present opportunities that their enemies will perceive as easily exploitable weaknesses, to draw them in and thus to bring that overwhelming force to bear.

And this brings us naturally back to the topic of data governance, self-service business intelligence, and dead Prussian field marshals.

Seriously.

In many large organizations, the goal of the data governance group is to ensure that data is never used improperly, and to mitigate (often proactively and aggressively mitigate) the risk of improper use.

In many large organizations, the data governance group has an overwhelming battlefield advantage. They make the rules. They define the processes. They grant or deny access to the data. No one gets in without their say-so, and woe unto any business user who enters that field of battle, and tries to get access to data that is under the protection of this superior force.

Of course, the business users know this. They’re outgunned and outmanned, and they know the dire fate that awaits them if they try to run the gauntlet that the data governance team has established. Everyone they know who has ever tried has failed.

So they go around it. They rely on the tried and true asymmetric tactics of self-service BI. The CSV export. The snapshot. The Excel files and SharePoint lists with manually-entered data.

Rather than facing the data governance group and their overwhelming advantages, they build a shadow BI solution.

These veteran business users choose not to join a battle they’re doomed to lose.

They instead seek and find the weak spots. They achieve their goals despite all of the advantages and resources that the data governance group has at their disposal.

Every time. Business users always find a way.

This is where a savvy data governance leader can learn from the battlefield. Just as a military commander can draw in their opponents and then bring their superior forces to bear, the data governance group can present an attractive and irresistible target to draw in business users seeking data.

This is the path to managed self-service business intelligence… and where the whole military analogy starts to break down. Even though data governance and self-service BI have different priorities and goals, these groups should not and must not be enemies. They need to be partners for either to succeed.

Managed self-service BI succeeds when it is easier for business users to get access to the data they need by working within the processes and systems established by the data governance group, rather than circumventing them.[1]

Managed self-service BI succeeds when the data governance group enables processes and systems to give business users the access they need to the data they need, while still maintaining the oversight and control required for effective governance.

Managed self-service BI succeeds when the data governance group stops saying “no” by default, and instead says “yes, and” by default.

  • Yes you can get access to this data, and these are the prerequisites you must meet.
  • Yes you can get access to this data, and these are the allowed scenarios for proper use.
  • Yes you can get access to this data, and these are the resources to make it easy for you to succeed.

What business user would choose to build their own shadow BI solution that requires manual processes and maintenance just to have an incomplete and outdated copy when they could instead have access to the real data they need – the complete, trusted, authoritative, current data they need – just by following a few simple rules?[2]

Managed self-service BI succeeds when the data governance group provides business users with the access they need to the data they need to do their jobs, while retaining the oversight and control the data governance group needs to keep their jobs.

This is a difficult balancing act, but there are well-known patterns to help organizations of any size succeed.

At this point you may be asking yourself what this has to do with plans not surviving contact with the enemy. Everything. It has everything to do with this pithy quote.

The successful data governance group will have a plan, and that plan will be informed by well-understood strategic goals. The plan is the plan, but the plan is made to change as the battle ebbs and flows. The strategy does not change moment to moment or day to day.

So as more business users engage, and as the initial governance plan shows its gaps and inadequacies, the data governance group changes the plan, keeping it aligned with the strategy and informed by the reality of the business.

This is a difficult balancing act, but it is being successfully performed by scores of enterprise organizations around the world using Power BI. Each organization finds the approach and the balance that best achieves their goals.

Although this post has used a martial metaphor to help engage the reader, this is not the best mental model to take away. Data governance and self-service business intelligence are not at war, even though they are often in a state of conflict or friction.

The right mental model is of a lasting peace, with shared goals and ongoing tradeoffs and compromises as each side gives and takes, and contributes to those shared goals.

This is what a successful data culture looks like: a lasting peace.

Multiple people replied to the original Twitter thread citing various challenges to succeeding with managed self-service business intelligence, balancing SSBI with effective data governance. Each of those challenges highlights the importance of the effective partnership between parties, and the alignment of business and IT priorities into shared strategic goals and principals that allow everyone to succeed together.

If you want to explore these concepts further and go beyond the highlights in this post, please feel encouraged to check out the full “Building a Data Culture with Power BI” series of posts and videos. Acknowledging the fact that data governance and self-service BI go beautifully together is just the beginning.

[1] This is really important

[2] Yes, yes, we all know that guy. Sometimes the data governance team needs the old stick for people who don’t find the new carrot attractive enough.. but those people tend to be in the minority if you use the right carrot.

 

Problems, not solutions

On By Matthew RocheIn Data Culture, Data Governance, Patterns, Power BI2 Comments

Imagine walking into a restaurant.

No, not that one. Imagine walking a nicer restaurant than the one you thought of at first. A lot nicer.

restaurant-2697945_640
Even nicer than this.

Imagine walking into a 3-star Michelin-rated best-in-the-world restaurant, the kind of place where you plan international travel around reservations, the kind of place where the chef’s name is whispered in a kind of hushed awe by other chefs around the world.

Now imagine being seated and then insisting that the chef cook a specific dish in a specific way, because that’s what you’re used to eating, because you know what you like and what you want.

knife-1088529_640
I’ll just leave this here for no particular reason.

In this situation, one of three things is likely to happen:

  1. The chef will give you what you ask for, and your dining experience will be diminished because your request was granted.
  2. The chef will ask you to leave.
  3. The chef will instruct someone else to ask you to leave.[1]

Let’s step back from the culinary context of this imaginary scenario, and put it into the context of software development and BI.

Imagine a user emailing a developer or software team[2] and insisting that they need a feature developed that works in some specific way. “Just make it do this!” or maybe “It should be exactly like <legacy software feature> but <implemented in new software>!!”

I can’t really imagine the restaurant scene playing out – who would spend all that money on a meal just to get what they could get anywhere? But I don’t need to imagine the software scene playing out, because I’ve seen it day after day, month after month for decades, despite the fact that even trivial software customization can be more expensive than a world-class meal. I’ve also been on both sides of the conversation – and I probably will be again.

When you have a problem, you are the expert on the problem. You know it inside and out, because it’s your problem. You’ve probably tried to solve it – maybe you’ve tried multiple solutions before you asked for help. And while you were trying those ineffective solution approaches, you probably thought of what a “great” solution might look like.

So when you ask for help, you ask for the solution you thought of.

This is bad. Really bad.

“Give me this solution” or “give me this feature” is the worst thing to ask for. Because while you may be the expert on your problem, you’re not an expert on the solution. If you were, you wouldn’t be asking for help in the first place.

And to make matters worse, most of the people on the receiving end aren’t the IT equivalents of 3-star Michelin-rated chefs. They’re line cooks, and they give you what you asked for because they don’t know any better. And because the customer is always right, right?

Yeah, nah.

As a software professional, it’s your job to solve your customers’ problems, and to do so within constraints your customers probably know nothing about, and within an often-complex context your customers do not understand[3]. If you simply deliver what the customer asks for, you’ve missed the point, and missed an opportunity to truly solve the fundamental problem that needs to be solved.

If you’re a BI professional, every project and every feature request brings with it an opportunity. It’s the opportunity to ask questions.

Why do you need this?

When will you use it?

What are you doing today without the thing you’re asking for?

When will this be useful?

Who else will use it?[4]

As a software or BI professional, you’re the expert on the solution, just as your customer is the expert on the problem. You know where logic can be implemented, and the pros and cons of each option. You know where the right data will come from, and how it will need to be transformed. You know what’s a quick fix and what will require a lot of work – and might introduce undesirable side-effects or regressions in other parts of the solution.

With this expertise, you’re in the perfect position to ask the right questions to help you understand the problem that needs to be solved. You’re in the perfect position to take the answers to your questions and to turn them into what your customer really needs… which is often very different from what they’re asking for.

You don’t need to ask these questions every time. You may not even need to ask questions of your customers most of the time[5]. But if you’re asking these questions of yourself each time you’re beginning new work – and asking questions of your customers as necessary – the solutions you deliver will be better for it.

And when you find yourself on the requesting side (for example, when you find yourself typing into ideas.powerbi.com) you’re in the perfect position to provide information about the problem you need solved – not just the solution you think you need. Why not give it a try?

This is a complex topic. I started writing this post almost 100 years ago, way back in February 2020[6]. I have a lot more that I want to say, but instead of waiting another hundred years I’ll wrap up now and save more thoughts for another post or two.

If you’ve made it this far and you’re interested in more actual best practices, please read Lean Customer Development by Cindy Alvarez. This book is very accessible, and although it is targeted more at startups and commercial software teams it contains guidance and practices that can be invaluable for anyone who needs to deliver solutions to someone else’s problems.

 

[1] This seems like the most likely outcome to me.

[2] This could be a commercial software team or “the report guy” in your IT department. Imagine what works for you.

[3] If you’re interested in a fun and accessible look at how the Power BI team decides what features to build, check out this 2019 presentation from Power BI PM Will Thompson. It’s only indirectly related to this post, but it’s a candid look at some of the “often-complex context” in which Power BI is developed.

[4] Please don’t focus too much on these specific questions. They might be a good starting point, but they’re just what leaped to mind as I was typing, not a well-researched list of best practice questions or anything of the sort.

[5] If you’re a BI developer maintaining a Power BI application for your organization, you may have already realized that asking a ton of questions all the time may not be appreciated by the people paying your salary, so please use your own best judgment here.

[6] This probably explains why I so casually mentioned the idea of walking into a restaurant. I literally can’t remember the last time I was in a restaurant. Do restaurants actually exist? Did they ever?

Data Culture: Strategy is more important than tactics

On By Matthew RocheIn Data Culture, Data Governance, VideoLeave a comment

Even though he lived 2,000 years ago, you’ve probably heard of the Chinese military strategist and general Sun Tzu. He’s known for a lot of things, but these days he’s best known for his work The Art of War[1], which captures military wisdom that is still studied and applied today

Even though Sun Tzu didn’t write about building a data culture[2], there’s still a lot we can learn from his writings. Perhaps the most relevant advice is this:

Building a data culture is hard. Keeping it going, and thriving, as the world and the organization change around you is harder. Perhaps the single most important thing[3] you can do to ensure long-term success is to define the strategic goals for your efforts.

Rather than doing all the other important and valuable tactical things, pause and think about why you’re doing them, and where you want to be once they’re done. This strategic reflection will prove invaluable, as it will help you prioritize, scope, and tune those tactical efforts.

Having a shared strategic vision makes everything else easier. At every step of the journey, any contributor can evaluate their actions against that strategic vision. When conflicts arise – as they inevitably will – your pre-defined strategic north star can help resolve them and to keep your efforts on track.

[1] Or possibly for the Sabaton album of the same name, which has a catchier bass line. And since Sabaton is a metal band led by history geeks, they also have this video that was released just a few weeks ago that looks at some of the history behind the album and song.

[2] Any more than Fiore wrote about business intelligence.

[3] I say “perhaps” because having an engaged executive sponsor is the other side of the strategy coin. Your executive sponsor will play a major role in defining your strategy, and in getting all necessary stakeholders on board with the strategy. Although I didn’t plan it this way, I’m quite pleased with the parallelism of having executive sponsorship be the first non-introductory video in the series, and having this one be the last non-summary video. It feels neat, and right, and satisfying.

Session resources: Patterns for adopting dataflows in Power BI

On By Matthew RocheIn Azure Data Factory, Data Culture, Data Governance, Dataflows, Patterns, Power BI, Presentations, Video6 Comments

This morning I presented a new webinar for the Istanbul Power BI user group, covering one of my favorite subjects: common patterns for successfully using and adopting dataflows in Power BI.

This session represents an intersection of my data culture series in that it presents lessons learned from successful enterprise customers, and my dataflows series in that… in that it’s about dataflows. I probably didn’t need to point out that part.

The session slides can be downloaded here: 2020-09-23 – Power BI Istanbul – Patterns for adopting dataflows in Power BI

The session recording is available for on-demand viewing. The presentation is around 50 minutes, with about 30 minutes of dataflows-centric Q&A at the end. Please check it out, and share it with your friends!

 

Data Culture: Now you’re thinking with portal

On By Matthew RocheIn Data Culture, Data Governance, Power BI, Video2 Comments

In an ideal world, everyone knows where to find the resources and tools they need to be successful.

We don’t live in that world.

I’m not even sure we can see that world from here. But if we could see it, we’d be seeing it through a portal[1].

One of the most common themes from my conversations with enterprise Power BI customers is that organizations that are successfully building and growing their data cultures have implemented portals where they share the resources, tools, and information that their users need. These mature companies also treat their portal as a true priority – the portal is a key part of their strategy, not an afterthought.

This is why:

In every organization of non-trivial size there are obstacles that keep people from finding and using the resources, information, and data they need.

Much of the time people don’t know what they need, nor do they know what’s available. They don’t know what questions to ask[2], much less know where to go to get the answers. This isn’t their fault – it’s a natural consequence of working in a complex environment that changes over time on many different dimensions.

As I try to do in these accompanying-the-video blog posts I will let the video speak for itself, but there are a few key points I want to emphasize here as well.

  1. You need a place where people can go for all of the resources created and curated by your center of excellence
  2. You need to engage with your community of practice to ensure that you’re providing the resources they need, and not just the resources you think they need
  3. You need to keep directing users to the portal, again and again and again, until it becomes habit and they start to refer their peers

The last point is worth emphasizing and explaining. If community members don’t use the portal, it won’t do what you need it to do, and you won’t get the return you need on your investments.

Users will continue to use traditional “known good” channels to get information – such as sending you an email or IM – if you let them. You need to not let them.

[1] See what I did there?

[2] Even though they will often argue vehemently against this fact.

Migrating to Power BI

On By Matthew RocheIn Data Culture, Data Governance, Power BI2 Comments

One aspect of building a data culture is selecting the right tools for the job. If you want more people working with more data, giving the tools they need to do that work is an obvious[1] requirement. But how many tools do you need, and which tools are the right tools?

Migrating to the cloud

It should be equally obvious that the answer is “it depends.” This is the answer to practically every interesting question. The right tools for an organization depend on the data sources it uses, the people who work with that data, the history that has gotten the organization to the current decision point, and the goals the organization needs to achieve or enable with the tools it selects.

With that said, it’s increasingly common[2] to see large organizations actively working to reduce the number of BI tools they support[3]. The reasons for this move to standardization are often the same:

  • Reduce licensing costs
  • Reduce support costs
  • Reduce training costs
  • Reduce friction involved in driving the behaviors needed to build and grow a data culture

Other than reducing the licensing costs[4], most of these motivations revolve around simplification. Having fewer tools means learning and using fewer tools. It means everyone learning and using fewer tools, which often results in less time and money spent to get more value from the use of those tools.

One of the challenges in eliminating a BI tool is ensuring that the purpose that tool fulfilled is now effectively fulfilled by the tool that replaces it. This is where migration comes in.

The Power BI team at Microsoft has published a focused set of guidance articles focused specifically on migrating from other BI tools to Power BI.

This documentation was written by the inestimable Melissa Coates of Coates Data Strategies, with input and technical review by the Power BI customer advisory team. If you’re preparing to retire another BI tool and move its workload to Power BI – or if you’re wondering where to start – I can’t recommend it highly enough.

[1] If this isn’t obvious to a given organization or individual, I’m reasonably confident that they’re not actively trying to build a data culture, and not reading this blog.

[2] I’m not a market analyst but I do get to talk to BI, data, and analytics leaders at large companies around the world, and I suspect that my sample size is large and diverse enough to be meaningful.

[3] I’m using the word “support” here – and not “use” – deliberately. It’s also quite common to see companies remove internal IT support from deprecated BI tools, but also let individual business units continue to use them – but also to pay for the tools and support out of their own budgets. This is typically a way to allow reluctant “laggard” internal customer groups to align with the strategic direction, but to do it on their own schedules.

[4] I’m pretty consistent in saying I don’t know anything about licensing, but even I understand that paying for two things costs more than paying for one of those things.

Data Culture: Picking Your Battles

On By Matthew RocheIn Data Culture, Data Governance, Patterns, Power BI, Video3 Comments

Not all data is created equal.

One size does not fit all.

In addition to collaboration and partnership between business and IT, successful data cultures have something  else in common: they recognize the need for both discipline and flexibility, and have clear, consistent criteria and responsibilities that let all stakeholders know what controls apply to what data and applications.

2020-08-01-19-55-59-794--POWERPNT

Today’s video looks at this key fact, and emphasizes this important point: you need to pick your battles[1].

If you try to lock everything down and manage all data and applications rigorously, business users who need more agility will not be able to do their jobs – or more likely they will simply work around your controls. This approach puts you back into the bad old days before there were robust and flexible self-service BI tools – you don’t want this.

If you try to let every user do whatever they want with any data, you’ll quickly find yourself in the “wild west” days – you don’t want that either.

Instead, work with your executive sponsor and key stakeholders from business and IT to understand what requires discipline and control, and what supports flexibility and agility.

One approach will never work for all data – don’t try to make it fit.

[1] The original title of this post and video was “discipline and flexibility” but when the phrase “pick your battles” came out unscripted[2] as I was recording the video, I realized that no other title would be so on-brand for me. And here we are.

[2] In case you were wondering, it’s all unscripted. Every time I edit and watch a recording, I’m surprised. True story.

When does memory die?

On By Matthew RocheIn Data Culture, Data Governance, Metadata, MetaphorsLeave a comment

My recent post on metadata and Indian food has gotten more traffic than most posts on this blog, and it has also received more comments and discussion as well. One comment in particular, from Jessica Jolly, really resonated with me:

Love the analogy. My personal favorite analogy regards old family photos. If no one takes the time to write on the back of the photo the who/what/where/when/why (i.e. the metadata), that photo will get thrown away.

Not everyone agreed that this was a great analogy. Khürt Williams in particular called out the inherent value of some data independent of any metadata to give it context.

No one throws away old family photos because they lack who/what/where/when/why. In fact, I would argue that with family photos the metadata lives in the minds of the people in the photograph or some family member you haven’t yet spoken to.

Somethings have value way beyond their metadata.

These comments got me thinking, and made me ask myself: when does memory die?

I’ve seen many variations on this quote[1], but I don’t know who said it first:

You only live as long as the last person who remembers you.

This may be a Russian proverb or it may be a quote from Westworld, but I believe the principle applies as much to business data as it does to family photos, despite the obvious differences between the two.

Looking at the family photos context first I can clearly recall times in my life, in those dark days following a funeral or a divorce, when family photos were discarded and the lack of metadata was a contributing factor. The photos of close relatives were kept, but those of more distant relatives were at risk.

When you’re asking “should I keep this photo?” and the next question is “who are these people?” the answer to the second question is going to influence the answer to the first.

As a specific example, I’d like to share a photo that hangs above the one-handed swords[2] in my hallway.

1586442320506-2d7b50e2-5f49-4739-aa05-0c7308bb64a6
I don’t know who this is.

This photo was in the home of my wife’s grandmother, who passed away almost 20 years ago. We found it when we were cleaning out her house after her funeral; it was in the attic, not on display, and no one knew who this young man might be. A few relatives thought that he was a cousin or second cousin of my wife’s late grandmother who went to the great war and never returned – but no one was certain. There was writing on the paper backing the frame, but it was faded and smudged by the years, and by the time we discovered the photo the words were illegible.

By the time we discovered the data, the metadata was no longer usable, and any subject matter expert who could have shared the deeper context of the data had long since moved on.

And once you phrase it like that, it starts to sound familiar again.

In far too many business contexts the metadata lives only in the minds of the people who create and work with the data. It’s tribal knowledge – just like unlabeled family photographs. But as people move on to new jobs and the business changes over time, that tribal knowledge is lost. Even though the data may still be the same, and may still be valuable, when the people move on the tribal knowledge leaves with them. At this point it will either be organically rediscovered and recreated, or the data will stagnate because no one remembers anymore why it was important. Or, as is the case with the photo above, the data may be used and applied to a different purpose.

Tribal knowledge is a lousy metadata solution, no matter the context. Because tribal knowledge is inherently transitory and lossy, we should strive to capture metadata in a more systematic way, and to keep the metadata as close to the data as possible.

Because eventually memory will die. And some things are too important to forget.

[1] My favorite variation may be from Manowar, who remind us that only courage and heroism linger after death… but it would be a stretch even for me to incorporate this into the body of the post. This is why we have footnotes.

[2] I call out the fact that these are the one-handed swords because the two-handed swords hang in a different hallway, and there isn’t enough room for a photo above them.

Metadata is not a “nice to have”

On By Matthew RocheIn Cooking and Baking, Data Culture, Data Governance, Metadata, Metaphors, Power BI15 Comments

I love to cook, and over the past few days I’ve made a few of my favorite Indian recipes that I haven’t made in a while. So, of course, this has me thinking about metadata.

IMG_20200319_193323
Homemade naan and chicken tikka masala

Going from Indian cooking to metadata isn’t as big a leap as you might think. The bridge is one of my favorite cookbooks: Julie Sahni’s Classic Indian Vegetarian and Grain Cooking.

If you’re just here for the food, you should immediately make this spectacularly delicious Bengal red lentil recipe taken from this book, because it is absolutely phenomenal. If you’re here for the metadata, remember the link but don’t click on it yet.

IMG_20200321_080342

Every recipe I’ve made from this cookbook has produced fantastic results. It’s one of those go-to cookbooks where I know that anything I try will be good. And yet, I almost never seek it out when I want to cook, except for the recipes I already know. The reason is metadata.

It doesn’t matter how good your data is – without effective and available metadata, your investment in quality data will be undermined.

Let’s look at the recipe for saag paneer. Say those words out loud (“saag paneer”) and images of that rich, vibrant green sauce will start running through your mind.

IMG_20200321_080455

I found this recipe easily because I have a bookmark. But let’s say I didn’t – it should still be easy to find, because cookbooks have indexes, and indexes are the perfect tool for finding recipes. Let’s find the recipe for saag paneer.

IMG_20200321_080549
Oh, there’s no entry for saag paneer, or for saag?
IMG_20200321_080628
There’s no entry for paneer? There are so many paneer recipes in this book!
IMG_20200321_080656
Ok, we’re making progress… I think.
IMG_20200321_080727
There it is. Maybe? It still doesn’t say saag paneer anywhere.

Literally the only place the phrase “saag paneer” exists in this book is below the recipe header. This means that the only way to find the saag paneer recipe is to flip through the book page by page, or to know the specific and arbitrary phrase the author uses to describe the recipe for Western readers. This is why my copy of the book looks like this[1]:

IMG_20200321_083450

This systemic problem is exacerbated by the book’s complete lack of photos; there’s also no way to skim through the book and quickly visually identify recipes of relevant interest. The reader is forced to carefully evaluate each recipe in turn, looking at ingredients and processes to decide if the recipe is worth making.

At this point you may be asking what this has to do with metadata[2] or you may see the connection already.

The reason I immediately thought of metadata may be related to a BI effort I’m working on. Without going into too much detail, I have built a small Power BI app that presents information from a program I run and makes that information available to other members of my extended team.

I’m currently at the point where my app needs to include data from other sources in order to increase its value. Fortunately, that data already exists, and to make it even easier to work with, it is available as a set of Power BI dataflows. I was able to email the owner to get access[3] and to learn which dataflows to look in, and I was off. But not for very far, or for very long.

Very quickly I was back where this post started: I was faced with the high-quality data I needed, and I lacked the metadata to efficiently use it. I needed to manually evaluate each dataflow and each entity to understand its contents and context and to decide if it was right for me. I made some early progress, but because of the lack of metadata the effort will likely take days not hours, and this means it probably won’t get done this month or next.

Let that sink in: because of a lack of effective metadata, quality curated data is going unused, and business insights are being delayed by weeks or months[4].

Just like these fantastic recipes sitting on my shelf, largely unused and unmade because a fantastic cookbook lacks a usable index, these fantastic dataflows are going largely unused, at least by me. All because metadata was treated as a “nice to have” rather than as a fundamental high-priority requirement.

Does your data have the metadata it needs, in a format and location that serves the needs of your users? How do you know? Remember that last picture of all the bookmarks[5]?

These bookmarks are a symptom of the underlying metadata problem. Bookmarks aren’t a problem themselves, but if you’re paying attention you can see that they’ve been implemented as a workaround to a problem that might not otherwise be apparent. If you’re familiar with the concept of “code smells”, you probably see where I’m going.

When your data lacks useful metadata to enable its effective use, people will start to take actions because of this lack. Things like emailing you to ask questions. Things like building their own ad hoc data dictionaries. Things like using alternate or derivative sources instead of using your authoritative data source – like the recipe link I shared above.

The more of these actions you identify, the more urgency you should feel about closing the metadata gap. Not every data source is a werewolf, but every data source requires metadata to be effectively and efficiently used.

Requires.

[1] Remember this picture. There will be a quiz later.

[2] You may also be asking if there’s anything in life that doesn’t make me think about metadata. This is a fair question.

[3] I knew the owner’s email because I had bookmarked it earlier.

[4] To be fair, my full schedule is also contributing to this delay – I’m not trying to say that the lack of metadata is independently costing months. But it is a key factor: my schedule could accommodate two or three hours for this work, but it doesn’t have room for two or three days until the end of April.

[5] I told you there would be a quiz.