Measuring success and satisfaction

Back in 2020 I wrote a post titled “Successfully measuring / measuring success” that talked about a problem without meaningfully describing it, because at the time I didn’t believe that the problem itself was particularly relevant. I was more interested in the pattern, which at the time I thought was “measuring the wrong thing.”

In recent weeks I’ve found myself sharing this post more frequently, and have come to believe that the underlying pattern is actually something else. Let’s look at my beautiful  2010-era chart one more time.

Before Power BI we had PowerPoint, but data is still Power

In this chart, both series represent NSAT[1] scores – basically they’re saying “this is how happy our customers are with a thing.” This sounds pretty simple, but the tricky part is in the details. Who are “our customers” and what is the “thing” they’re happy or unhappy with?

In the context of the chart above, the yellow series was “customer satisfaction with what they got.” The blue series was “customer satisfaction with what we shipped.” Implied in these descriptions and the difference in the two data series is that something happened between the time we shipped something and the time the customers got something, and that the thing that happened was key to the customers’ satisfaction.

Without going into too many details[3], we were shipping a product that was used by partners, and those partners used our product to deliver an experience to their customers. In the FY06 timeframe we started to change the product that we shipped, and the partners used the updated product to deliver an experience that met their customers’ needs. Now they just had to do a little more work to keep their customers happy. As the product changes continued, the partner load increased. They had to do more and more to fix what we gave them and to keep their customers happy. You can see the story play out in the blue series in the chart above.

We were looking at the yellow series, and falsely conflating “customer satisfaction” with “customer satisfaction in what we have shipped to partners.” We didn’t truly appreciate the importance of the party in the middle and their impact, and it ended up causing no end of problems. We were measuring customer satisfaction, but failing to appreciate what it was that customers were satisfied with – and how little that satisfaction related to what we had created.

And this is the pattern I’ve been seeing more often lately.

In my recent post on the role of the Power BI CAT team and how that role has parallels with Power BI centers of excellence, I described a success pattern where a central team serves as a value-add bridge between people with problems and people with solutions.

This pattern is one that I’ve seen provide significant value in multiple contexts… but it also introduces risk of measuring the wrong things, and overlooking real problems. This is a side-effect of the value that the central team provides. Customers are interacting with the work of the central team in addition to[4] the work of the solution team, and it may be difficult for them to understand what part of their experience is dependent on what factors.

In this situation there is a risk of the solution team overlooking or failing to appreciate and prioritize the problems their customers are experiencing. This is  a problem that the “curate” function in the diagram above is designed to mitigate, but the risk is real, and the mitigation takes ongoing effort.

When a member of a solution team works with customers directly, it’s hard to overlook their challenges and pain. When that solution team member hears about customer challenges from an interim party, the immediacy and impact can be lost. This effect is human nature, and the ongoing effort of the central team to curate customer feedback is vital to counteract it.[5]

As mentioned at the top of the article, I’ve seen this pattern more often recently, where a solution team is failing to recognize problems or opportunities because they’re looking at the wrong thing. They’re focused on what their end customer is saying, instead of looking at the bigger picture and the downstream value chain that includes them and their customers, but isn’t limited to these two parties. It’s easy to mistake “customer being happy” for “customer being happy with what we produce” if you’re not keeping an eye on the big picture.

It’s easy to mistake “customer being happy” for “customer being happy with what we produce” if you’re not keeping an eye on the big picture.

If you’re in a situation like this, whether you’re a member of a solution team, a member of a central team, or a member of a customer/consumer team, you’ll do well to keep an eye open for this problem behavior playing out.


[1] You can read this article if you’re curious about NSAT as a metric and what the numbers mean and were too lazy[2] to read the 2020 blog post I linked to above.

[2] I’m judging you, but not too harshly.

[3] I’m being deliberately vague here, trying to find a balance between establishing enough context to make a point and not sharing any decade-plus-old confidential information.

[4] Or in some circumstances, instead of.

[5] Please keep in mind that in most circumstances the central team is introduced when direct ongoing engagement between the solution team and customers can’t effectively scale. If you’re wondering why you’d want a central team in the first place, it may be because your current scenario doesn’t need it. If this is the case, please keep reading so you’ll be better prepared when your scenario gets larger or more complex and you need to start thinking about different solutions.

Join me in London for SQLBits – March 8 through March 12

In less than two months I’ll be making my first work trip in over two years, flying to London to present, meet with customers, and learn exciting things at the 2022 SQLBits conference. If you can, you should register today and join me.

Here’s what I expect my week to look like:

  • Wednesday March 9, 09:00 to 17:00: I’ll be back on stage for The Day After Dashboard in a Day pre-conference learning day, co-presenting with Patrick LeBlanc, Alex Powers, and Lars Andersen.
  • Thursday March 10, 14:10 to 15:00: I’ll be joining SQLBits organizer and MVP Simon Sabin for the Maximising everyone’s super powers panel discussion on mental health.
  • Thursday March 10, 18:15 to 19:05: Prathy K from the London Power BI User Group has organized an evening “ask me anything” open Q&A session with a bunch of folks from the Power BI CAT team, which sounds like a perfect way to end the day. You can register for this evening meetup here.
  • Friday March 11, 13:40 to 14:00: I finally get to present on Roche’s Maxim of Data Transformation for a live, in-person audience, and I get 20 minutes to do it!
  • Friday March 11, 14:10 to 15:00: The BI Power Hour returns after a two-year pandemic hiatus, guaranteeing laughs and excitement[1] in a demo- and joke-filled exploration of how not to use Microsoft data technologies in the workplace. I’ll be joined by an international star-studded cast from the Power BI CAT team and the Microsoft data community, and I expect this session to be the can’t miss event of the decade.[2]
  • Saturday March 12, 08:00 to 08:50: I kick off the final day of the conference with Unleashing your personal superpower, an honest and sometimes-painful look at how to succeed in tech, despite your brain’s best efforts to stop you. I’m very excited to have this important session scheduled on the free-to-the-public day of the conference.

When I’m not on stage, I’m hoping to spend as much time as possible at the Microsoft product booth and the community zone. Conferences like SQLBits are an opportunity to have interesting conversations that can be an awkward fit for virtual channels, and I plan to get the most from my week as possible.

Update February 10: I’m planning to be in the community zone on Thursday afternoon immediately following the mental health panel discussion so we can keep that conversation going. I’m also planning to be back on Friday morning at 10:10 to talk about non-traditional career paths. If either of these conversations sounds interesting to you, you should plan on joining me.

Update February 12: My Saturday session has been moved from the end of the day to the beginning of the day. With this change, I can now have more time to hang out in the community zone on Saturday to continue the discussion.

If you’re in the London area – or if you can get there – and if attending an in-person event matches your personal risk profile, I hope you’ll register today and come say hi during the event. I’ll be the purple-haired guy with the mask on.

If you can’t come to London, I hope you’ll still register and attend. Most sessions are hybrid with virtual attendees welcome and included.


[1] Not guaranteeing that you will laugh or be excited. I’m just thinking about me here.

[2] Thinking back on the decade in question, this isn’t as high a bar as it might otherwise seem.

Reporting on your Spotify listening history

I’ve listened to music on Spotify for over 5,000 hours since I subscribed to their service. I listen the most in the early morning when I’m at the gym and before my morning meetings begin. I’ve listened to more Amon Amarth than I’ve listened to my next three top bands combined, but the album I’ve spent the most time listening to is “The Black Parade” by My Chemical Romance.[1] The song I’ve listened to the most is “O Father O Satan O Sun!” by Behemoth – Since March 2017 I’ve listened to it 620 times, for a total time of 69 hours, 15 minutes.

How do I know this? I know because for the past few years I’ve been reporting on my Spotify listening data.

Spotify doesn’t have a viable API for this type of analysis, but you can request a copy of your listening history and other account data by emailing privacy@spotify.com[2]. It takes about a month to get your data, so if you want to report on your own listening history, you should probably email them today.

If you’re interested in using my report as-is or as a starting point for your own analysis, I’ve shared a PBIT file here:

Here’s what you’ll probably need to do:

  1. Request a copy of your Spotify listening history by emailing privacy@spotify.com.
  2. Wait approximately 30 days for Spotify to prepare your data.
  3. Download the zip file prepared by Spotify.
  4. Extract the zip file to a folder on your PC.
  5. Locate the folder that contains JSON files. This report uses only the “endsong*.json” files that contain your listening history. The other files probably contain other interesting data – who knows?
  6. Open the PBIT file in Power BI Desktop.
  7. When prompted, enter the path to the folder containing your Spotify JSON data.
  8. Click “Load”.
  9. Cross your fingers, because I’ve never created a PBIT template before. Even though I’ve tested it, I don’t really know if this will work as hoped for you and I’m probably not going to provide any timely support if it doesn’t work.

Music is a huge part of my life, and I’ve had dozens of music-related pet projects over the past 30 years. When I was in college, my “learning a new programming language” routine involved re-implementing my record collection manager in whatever new language that semester’s classes called for. Being able to play with this familiar and very-meaningful-to-me data in Power BI has been a fun way to spend a few hours her and there over the past few years.

Sadly, the reason I’m using this report today is to figure out what albums I need to add to my music collection when I delete my Spotify account. I’ve discovered so much awesome new music because of Spotify[3], and I am now using this report to prioritize what CDs to purchase. This isn’t why I originally made this report, but this is where we are today.

As you’re probably aware, Spotify has chosen to spread disinformation and hateful propaganda and to prioritize their profits over public health. This is their right to do so, since they’re apparently not breaking any laws, but I won’t give them any more of my money to knowingly cause harm and poison the public discourse.

If you want to call me out for being a snowflake or SJW or whatever, please do so on Twitter, not here. I’ll delete any blog comments on this theme, and no one will get to see how witty you are – wouldn’t that be a shame?

Whether or not you’re planning to delete your own Spotify account, feel free to download this PBIT and check out your own listening history. I found insights that surprised me – maybe you will too. This report is something of a work in progress, so you might also find interesting ways to complete and improve it that I didn’t think of.

As for my post-Spotify music streaming world, I’ll probably end up getting a family subscription to Amazon Music. The price is the same, and it looks like all of the top albums I’ve been listening to on Spotify are there.

I’ll also definitely be spending more time with the CD/MP3 collection I spent decades building before I discovered Spotify. I own thousands of albums by hundreds of artists, and curating this collection was an activity that gave me joy for many years. Now I’m feeling motivated and inspired to rediscover that collection, and the personal satisfaction that comes from mindfully expanding it.

Specifically, I’m adding my personal music collection to Plex Media Server, and streaming it to all of my devices using their excellent PlexAmp app. I’ve been moving in this direction since it became obvious Spotify was going to keep funding and promoting hatred and ignorance, and it’s already reminded me of the wide world of music that’s not included in the big streaming catalogs[4].

Plex and PlexAmp make it incredibly easy to manage and stream your personal media, and they have both a REST API and a SQLite database for tracking and reporting on library contents and listening activity – and it doesn’t take a month to get your data. Maybe there’s a silver lining here after all…


[1] If you’re wondering where Manowar is on this list, they’re #8. Please consider that Spotify only has a limited selection of Manowar’s music, and that when I listen to my personal music collection it isn’t included in this data.

[2] For more information, see the Privacy page at https://www.spotify.com/us/account/privacy or whatever variation on this URL works for your part of the world.

[3] Four of my top 10 artists are bands Spotify recommended to me, and which I probably would not have heard of were it not for their “release radar” and similar playlists.

[4] For example, my personal collection includes every album, EP, single, and DVD Manowar has ever released. I may not literally have an order of magnitude more Manowar than Spotify does, but it’s pretty close.

On joining the product team

Almost two years ago I published a blog post and video on Being a Program Manager at Microsoft, in which I shared some of my personal experience and advice based on my decade plus at Microsoft. I’ve received an incredible amount of feedback[1] on that 2020 post… and now it’s time for a follow-up.

My last few posts have focused on topics related to problem domains and solution domains, including how these concepts relate to Power BI and my work on the Power BI CAT team. What about for people who are currently Power BI or data professionals who might be thinking about changing careers to join the product team? These are pretty abstract concepts – what do they mean to you?

This conversation calls for an analogy, and since I don’t know anything about driving race cars or automotive mechanics, I’m going to use an analogy about race car driving and mechanics[2].

As a Power BI or data professional, you’re like a race car driver.

Your job is to win races. You need to respond to the rapidly-changing demands of the moment to deliver victory for your team[3]. You’re in the spotlight,

As a race car driver, there are two main factors that contribute to your wins and losses:

  1. Your skills as a driver
  2. The performance of the car you’re driving

To win a race, you need to know how to get the most from your car, and make it perform for the conditions on the track, and within the rules of the race.

As a program manager on the product team, you’re like a mechanical engineer responsible for designing, refining and tuning the race car engine.

Your job is to deliver an engine that performs the way your drivers need it to perform, as part of the larger system of the race car as a whole. The race car needs to have the capabilities and characteristics that enable drivers to win races, and the engine is a key part of that… as is the steering, suspension, and whatever other subsystems make up a race car.

Your job is not to drive the race car, and you can excel at your job without ever having won (or driven in) a race.

Re-read that last sentence and pause to let it sink in. If you’re thinking about joining the product team that makes your favorite software tool or platform, you’re also thinking about giving up the day to day job of using that software.

Even more than this, the skills that you’ve built as a race car driver – or as a data professional – are no longer directly relevant. Please don’t get me wrong – if you’ve spent years implementing data and BI solutions this will serve you very well as a program manager on a product team building data and BI tools. But that experience will be valuable in the way that having been a race car driver could be valuable in informing the design and optimization of race car engines.

Having an intimate understanding of – and deep personal experience with – the problem domain can help inform your work in building and improving the solution domain… but your job is no longer what it used to be. You need to learn and master new skills to be successful in the new role, and that means no longer being the expert on the problem domain like you used to be.[4]

This can be a difficult realization and wake-up call for people making a career change, because it means giving up – or at least moving away from – the work they know and love. It can also mean discovering new work and new challenges that they may love even more… but change is hard at the best of times, and it doesn’t always come at the best of times.

I hope this blog post and analogy[5] doesn’t scare anyone off. Working on a product team can be incredibly fun and rewarding – it’s just different from working with the product that the team builds and ships. It’s another situation where the problem domain and solution domain differ in ways that aren’t always obvious until you’ve had a chance to work in both worlds.

Joining the product team after working as a data professional changes your relationship with the product. What used to be your solution domain, where you were the expert responsible for understanding the product and delivering solutions based on its capabilities, now becomes your problem domain. You’re responsible for increasing its capabilities to make it more useful for others to build their solutions, but you’re no longer building those solutions yourself.

 


[1] I continue to believe that this video may have been the best thing I did in 2020. I’ve had dozens of conversations with people around the world who want to be program managers or product manager – at Microsoft or other tech companies, and some of them have already achieved their goal. This feels like I’m actually making a difference in the world when the world is doing its best to make me feel powerless, and that’s like a gift that keeps on giving. I may not be the only one who feels this way. While this post was in draft form waiting to be published, my friend Davide created a website to showcase this video – he even transcribed it! You can check it out here: http://aka.ms/being-a-ms-pm.

[2] Because of course I am. This is an analogy that presented itself to me in one of those conversations mentioned just one footnote earlier, and which works as well as any I’ve found so far.

[3] There are teams in racing, right?

[4] This is where it’s useful to have a person or team responsible for bridging these two worlds.

[5] Another possibly less scary analogy could have involved someone who loves eating becoming a chef in a professional kitchen. Since I know enough about these worlds to be dangerous, I didn’t trust myself to use this analogy without ratholing on lots of details no one but me would care about. Maybe I’ll try that one again when I have more time to think about it…

Simplifying the solution domain

My recent posts have focused on the importance of understanding both the problem domain and solution domain in which you’re working, the value of operating in both the problem domain and the solution domain, and some specific resources and techniques for solution-oriented practitioners to increase their fluency in understanding the problem domain. A lot of the message I’ve been trying to get across in these posts can be summarized as “the problem domain is really important, and we need to get better at appreciating and understanding the problems we work to solve.”

This message is predicated on two basic assumptions:

  1. You, the reader, are a technical professional of some sort[1], and you need to deliver technical solutions to people with problems
  2. The people who have problems can’t solve those problems on their own

Let’s look more closely at that second assumption, because it’s becoming increasingly less valid, and less true. And let’s look at it through the lens of… furniture.

Close your eyes and imagine for a moment that you need a table. Let’s say you need a bedside table. What do you do?

As you read through this list you were probably evaluating each option against your own requirements and preferences. Odds are you’ve chosen one or more options at some point in your life. Odds are the criteria you used to decide which options are viable or preferable have changed as your circumstances, skills, and budget have changed.

I couldn’t find Ikea instructions for a bedside table, but honestly I didn’t try very hard

Of course, the decision doesn’t need to be about a bedside table. It could be about a dining room table, or a bed, or a full set of kitchen cabinets. Each option will have different but similar choices, and you’ll likely use different criteria to evaluate and select the choice that’s right for you.

This is also true if the choice is about business intelligence solutions, which may not technically be considered furniture[2]. Close your eyes once more and imagine for a moment that you need a report, which may or may not include a table.  What do you do?

  • You could use an existing report
  • You could start with an existing report and customize it
  • You could start with an existing dataset use it to build a new report
  • You could start with an existing dataset, customize it using a composite model, and use it to build a new report
  • You could work with a center of excellence or BI team to create a dataset, customize a dataset, create a report, or customize a report to meet your needs
  • You could locate or create data sources and build a custom BI solution end-to-end, maybe getting some help along the way if you don’t already have the tools and expertise required for the project

The parallels are easy to see, and they hold up surprisingly well to closer inspection. Just as ready-to-build options make it possible for someone with no woodworking or cabinetry skills[3] to self-serve their own furniture, DIY BI tools[4] like Power BI make it increasingly easy for someone with no data preparation or data modeling skills to build their own BI solutions.

To step away from the furniture analogy, tools like Power BI simplify the solution domain and make it easy for problem domain experts to solve their own problems without needing to become true experts in the solution domain.

Any finance or supply chain professional can use Power BI Desktop to build reports that do what they need, without needing to ask for help. Their reports may not be the most beautiful or best optimized[5],  but maybe they don’t need to be. Maybe “good enough” is actually good enough. Just as it might make more sense to buy a $40 Ikea bedside table instead of spending hundreds or thousands of dollars on something fancier, it might make sense to stack a few milk cartons on top of each other and call it good, at least until you can save up for something better[6]. There is no one size that fits all.

If you are a Power BI professional, it may be difficult to think of the “boards and cinderblocks bookshelf” approach as acceptable, but in most circumstances it’s not up to you to decide. The tools for DIY BI are widely and freely available, and people will use them if they present the most attractive and accessible option to solve their problems. You can’t stop them from building DIY solutions, but you can help them build better ones.

This is where having an effective and engaged center of excellence comes in. Whether you think in terms of problem and solution domains, or in terms of enabling DIY through readily available tools and guidance[7], you can help make “good enough” better by meeting your problem domain experts where they are. They have the tools they need to get started building their own solutions, but they probably need your expertise and assistance to achieve their full potential.

You should help them.


[1] Probably a Power BI practitioner, but who knows?

[2] I have repeatedly seen Power BI referred to as a “platform” which is basically the same thing as a table, so I’m going to withhold judgment on this one.

[3] Someone like me! I married a carpenter and have never found the motivation to develop these skills myself.

[4] DIY BI has a ring to it that SSBI doesn’t have. Why don’t we start using DIY BI instead. DBIY? D(B)IY? D(BI)Y? Hmmm…

[5] They may indeed be the Power BI versions of my DIY home improvement projects.

[6] Please raise your hand if you too had bookshelves made of random boards and cinderblocks when you were in college, and were also happy with the results.

[7] This is the second place in this post where I’ve shared a link to this Marketplace story. If you run a Power BI COE for your organization, do yourself a favor and listen to the story – it will inspire you to think about your COE in different ways.

Understanding the problem domain

If you’re reading a Power BI-focused blog[1] you’re probably someone who builds solutions. As a BI and data practitioner, you may build reports, datasets, dataflows, databases, or any number of technical artifacts that together represent a solution domain. You have a toolset at your disposal, and you invest your valuable time and energy into refining the skills needed to use those tools.

But what if you’re holding the right tools, while using them to implement the wrong solution? This could happen if you’re holding a hammer and building a chair when what’s really needed is a table, but it could also happen if you’re using Power BI to build a solution that deliver what its users need to make their jobs easier.

Action doesn’t equate with value, and time spent solving a problem you don’t meaningfully understand is often time wasted. So how do you, as a Power BI author, meaningfully understand problems in domains like finance, supply chain, HR, or patient care if you lack personal experience in those domains?

Asking good questions is a great start.

If you want to deliver a fix for a problem someone has reported, asking targeted closed-ended questions to narrow down the scope of the problem can be valuable. What is the error message, when did it start happening, what are the steps to reproduce the problem, does it happen all the time, does it happen on other computers, does it happen for other users… that sort of thing.

This type of question is often great if you need to deliver a fix, but what about when you need to deliver a product? What if you need to deliver a Power BI app that will help a group of people be more productive completing a complex set busines processes?[2]

Asking good questions is still a great start, but you need to ask different questions.

While focused, detailed, targeted questions can be very effective for scoping down a tactical problem to deliver a fix, they tend to not be great for understanding a broader problem domain. Ask closed-ended questions when you want to deliver a fix; ask open-ended questions when you want to understand the problem.

What does your current process look like, where are the places where you struggle, what are the triggers that would cause you to begin the process, why do you do it that way, who works with the output you produce, and what do they do with it… that sort of thing.

If you’re used to asking the first sort of question, the second sort may not be obviously useful. What would you do with these answers? How can you act on them?

The short answer is “it’s complicated” and the longer answer is “read The Customer-Driven Playbook by Travis Lowdermilk and Jessica Rich.”

This amazing book presents a “hypothesis progression framework” that includes tools for learning about customers and their problems before building a solution to those problems. This framework is broken down into four phases:

At the risk of oversimplifying 250 pages of expert guidance, the framework looks something like this:

  • Who is my customer, what does she do, what motivates her, and what problems does she have?
  • What is the scope of my customer’s problem, how does it affect her, and what is its impact?
  • How well the concept you’ve come up with to solve to solve the problem actually fit with your customer’s needs, does its value proposition motivate her to learn more, and how will the limitations of the concept reduce its value?
  • How well does the product or feature you’ve built to implement the validated concept actually solve the customer’s problem it was built to solve?

Each of these phases is presented with best practices for asking the right types of questions of the right people in the right way, with guidance for formulating the right questions, asking them in structured experiments so you can better trust the answers you get, and then making sense of what you’ve heard. That sensemaking process is where your deep expertise in the solution domain will help you the most, as you look closely and mindfully at what you you’ve heard and decide how to act… but most of the process is about learning the details and nuances of the problem, not about solving it.

Albert Einstein once said “If I had an hour to solve a problem I’d spend 55 minutes thinking about the problem and 5 minutes thinking about solutions.” Think about that.

In my experience most technical professionals don’t value and appreciate the importance of the problem domain, and instead focus their energies on solutions to problems they don’t fully understand. We can do better, as individuals and as teams and as an industry, and The Customer-Driven Playbook is an excellent place to start. I hope you’ll read it, and I hope you’ll let me know how it changes the way you approach problems and solutions.


[1] Does BI Polar count as a Power BI-focused blog? These days it feels like I’m posting more about ancillary topics and less about Power BI itself.

[2] For that matter, what if you need to deliver a complex commercial software product like Power BI?

Into the Date Dimension!

Late last year I was spending some vacation days playing with one of my favorite personal data sets[1], and I realized I needed a better date dimension. So I asked for one.

A bunch of folks from the Power BI community shared their favorite Power Query date dimension queries[2] – you can look at the Twitter thread for links if you’re interested. With their inspiration (and code!) I picked what I liked, discarded what I didn’t, and came up with this:

let
// To be turned into parameter
startDate = #date(2017,01,01) as date, 
// To be turned into parameter
endDate = #date(2021,12,31) as date, 
// To be turned into parameter
culture = "en-US" as text,

// Beginning of actual query 👇 
Source = List.Dates(startDate, Duration.Days(Duration.From(endDate - startDate)) + 1, #duration(1, 0, 0, 0)),

TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns , "Year", each Date.Year([Date]),type text),
InsertQuarterNumber = Table.AddColumn(InsertYear, "Quarter Number", each Date.QuarterOfYear([Date])),
InsertQuarterName = Table.AddColumn(InsertQuarterNumber, "Quarter Name", each "Q" & Number.ToText([Quarter Number])),
InsertMonth = Table.AddColumn(InsertQuarterName, "Month Number", each Date.Month([Date]), type text),
InsertStartOfMonth = Table.AddColumn(InsertMonth, "Start Of Month", each Date.StartOfMonth([Date]), type date),
InsertEndOfMonth = Table.AddColumn(InsertStartOfMonth, "End Of Month", each Date.EndOfMonth([Date]), type date),
InsertDayOfMonth = Table.AddColumn(InsertEndOfMonth, "Day Of Month", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDayOfMonth, "DateInt", each [Year]*10000 + [Month Number]*100 + [Day Of Month]),
InsertMonthName = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM", culture), type text),
InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month Name Short", each Date.ToText([Date], "MMM", culture), type text),
InsertShortMonthNameYear = Table.AddColumn(InsertShortMonthName, "Month Short and Year", each [Month Name Short] & " " & Number.ToText([Year]), type text),
InsertCalendarMonth = Table.AddColumn(InsertShortMonthNameYear, "Month and Year", each [Month Name]& " " & Number.ToText([Year]),type text),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter and Year", each "Q" & Number.ToText([Quarter Number]) & " " & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Day of Week", each Date.DayOfWeek([Date]) + 1),
InsertDayName = Table.AddColumn(InsertDayWeek, "Day Name", each Date.ToText([Date], "dddd", culture), type text),
InsertShortDayName = Table.AddColumn(InsertDayName, "Day Name Short", each Date.ToText([Date], "ddd", culture), type text),
InsertEndOfWeek = Table.AddColumn(InsertShortDayName , "End Of Week", each Date.EndOfWeek([Date]), type date),
InsertedStartOfWeek = Table.AddColumn(InsertEndOfWeek, "Start of Week", each Date.StartOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertedStartOfWeek, "Week of Year Number", each Date.WeekOfYear([Date])),
InsertMonthWeekNumber= Table.AddColumn(InsertWeekNumber, "Week of Month Number", each Date.WeekOfMonth([Date])),
InsertYearMonthNumber = Table.AddColumn(InsertMonthWeekNumber,"Year and Month", each [Year] * 100 + [Month Number]),
InsertYearAndQuarterNumber = Table.AddColumn(InsertYearMonthNumber, "Year and Quarter", each [Year] * 100 + [Quarter Number]),
InsertYearAndWeekNumber = Table.AddColumn(InsertYearAndQuarterNumber, "Year and Week", each [Year] * 100 + [Week of Year Number]),
InsertDecadeName = Table.AddColumn(InsertYearAndWeekNumber, "Decade Name", each Text.Range(Text.From([Year]), 0, 3) & "0s"),
InsertDecadeNumber = Table.AddColumn(InsertDecadeName, "Decade Number", each Text.Range(Text.From([Year]), 0, 3) & "0"),
InsertStartOfQuarter = Table.AddColumn(InsertDecadeNumber, "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
InsertEndOfQuarter = Table.AddColumn(InsertStartOfQuarter, "End of Quarter", each Date.EndOfQuarter([Date]), type date),
InsertDayOfYear = Table.AddColumn(InsertEndOfQuarter, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
InsertIsWeekday = Table.AddColumn(InsertDayOfYear, "Is Weekday", each if [Day of Week] = 1 or [Day of Week] = 7 then 0 else 1),
InsertIsWeekend = Table.AddColumn(InsertIsWeekday, "Is Weekend", each if [Day of Week] = 1 or [Day of Week] = 7 then 1 else 0),
#"Reordered Columns" = Table.ReorderColumns(InsertIsWeekend,{"DateInt", "Date", "Year", "Year and Quarter", "Year and Month", "Year and Week", "Quarter Name", "Quarter Number", "Quarter and Year", "Start of Quarter", "End of Quarter", "Month Name", "Month Name Short", "Month Number", "Month and Year", "Month Short and Year", "Start Of Month", "End Of Month", "Week of Year Number", "Week of Month Number", "Start of Week", "End Of Week", "Day of Year", "Day Of Month", "Day of Week", "Day Name", "Day Name Short", "Decade Name", "Decade Number", "Is Weekday", "Is Weekend"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"DateInt", Int64.Type}, {"Year", Int64.Type}, {"Year and Quarter", Int64.Type}, {"Year and Month", Int64.Type}, {"Year and Week", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Number", Int64.Type}, {"Week of Year Number", Int64.Type}, {"Week of Month Number", Int64.Type}, {"Day of Year", Int64.Type}, {"Day Of Month", Int64.Type}, {"Day of Week", Int64.Type}, {"Decade Number", Int64.Type}, {"Is Weekday", Int64.Type}, {"Is Weekend", Int64.Type}, {"Quarter Name", type text}, {"Decade Name", type text}})
in
#"Changed Type"

In case you can’t visually parse that unfortunate wall of text, here’s what it looks like in my data model:

As I keep playing with my pet project I’ll probably keep tweaking the date dimension query to add, remove, or refine it as needed. When I’m done I may turn it into a dataflow, or I may make it into a function that takes my project’s min and max dates as input parameters. Who knows?

What I know for sure is that the next time I’m looking for a date dimension query, I’ll look here.

Update: I’ve published the pet project where this date dimension ended up – you can find the details and download the PBIT file if you’re interested.

This PBIT also includes a tweak to the query shared above to dynamically set the start and end dates included in the data source. If you’re interested in this pattern and don’t want to bother downloading the PBIT, here’s what’s different.

// Update as necessary to reference the appropriate date column in your data
startDate = Date.StartOfYear(Date.From(List.Min(#"Spotify Extended History"[ts]))) as date,
// Update as necessary to reference the appropriate date column in your data
endDate = Date.EndOfYear(Date.From(List.Max(#"Spotify Extended History"[ts]))) as date,
// To be turned into parameter as needed
culture = "en-US" as text,

 


[1] I made a GDPR request of Spotify, asking for all of the data they had related to my account. It took them about a month to prepare that data and make it available for me to download, but it was worth the wait. You can request your own data too: https://www.spotify.com/us/account/privacy/

[2] And a bunch of other folks shared DAX and SQL solutions.

Between the problem domain and the solution domain

In a recent post I mused about problem domains and solution domains, how they’re context-dependent, and how interesting things tend to happen at the intersection of a problem domain and a solution domain.

In this post I’m going to talk more about that intersection, and I’m going to start by putting it into a personal context: my job. For the past[1] few years I’ve been a member of the Power BI customer advisory team (Power BI CAT) at Microsoft. After spending almost a decade as a PM shipping data products and services, moving out of a feature role was strange, but I quickly realized this was the perfect place for me to work, and to shine, mainly because I was literally livin’ on the edge[2]… between the problem domain and the solution domain.

Early in 2021, we realized that the CAT team had grown significantly enough during the pandemic that we should organize a virtual “offsite” meeting for the team. There were more and more team members who had never met each other[4], and we wanted to invest in a sense of shared purpose and team identity.

To this end, some of the more tenured members of the team put together an agenda to cover the most important topics. I built a single slide to show “this is who we are, and this is what we do” to share my perspective and spark conversation. This is that slide:

The Power BI CAT team recognizes that the customer is the authoritative expert on the problem domain. They live it day in and day out, and their reality working with Power BI is their lived truth. We can work to understand it, and we can work to make their experience better, but they’re the experts. They get the final say.

We also recognize that the Power BI product team is the authoritative expert on the solution domain. The product team ultimately decides what features and capabilities to ship, how to scope them, and how to prioritize them[5]. They also decide the implementation of each feature – the product team writes the code, which is about as authoritative a perspective as you can get.

The CAT team stands in the middle, with one foot planted firmly in the problem domain and another firmly in the solution domain. This is a key reason why we’re able to be so impactful with such a relatively small number of team members. Although we are not the authoritative experts on either side[6], we are well-informed participants with a deep understanding of both sides. Customers often have a meaningful understanding of the solution domain and product team members typically have a meaningful understanding of the problem domain; the CAT team’s position gives us a unique perspective and advantage.

One of our team’s core function is to “build better customers.” Please keep in mind this is my personal phrases, not anything official, but I believe it effectively describes what we do. The CAT team works with some of the biggest customers in the world – enterprise customers who are pushing the limits of what Power BI and the Azure data platform do. Sometimes these customers need help, because no one has previously done what they’re trying to do. With our team’s expertise and our deep connections with the product team we can help them achieve their goals to be successful today – and as part of the same engagements we help the product team improve Power BI so that the next time a big customer is trying to do that same difficult thing, they don’t need to ask for help.

A key aspect of this function is scale. If we work with one customer to help one customer, we’ve missed a vital opportunity. To increase our team’s impact we’re continually looking for opportunities to work with one customer and help every customer. This scaling might take the form of community engagement via blogs or YouTube, or Microsoft Learn courses on commonly-needed topics like DAX and effective report design, or the Power BI guidance documentation or the Power BI Adoption Roadmap…. the list goes on and on. The team culture encourages members to ask themselves “does it scale” and to look for ways to make the answer be “yes” as often as possible.

The team’s other core function is to “build better product.” As we help customers in our ongoing engagements we’re learning from them, and we take what we learn back to the product team in a variety of structured and unstructured ways. One of my favorite books, The Customer-Driven Playbook[7]describes this process as “sensemaking”:

Sensemaking is a process that ensures we organize the data we collect, identify patterns and meaningful insights, and refine them into a story that compels others to action.

I absolutely love this quote, because for me it elegantly captures the core of what I try to do every day. Members of the CAT team are typically technical professional with significant personal experience, and we bring that to our jobs. What we don’t bring is an agenda. We’re careful in our work with the product team to curate what we learn from customers, and to represent both the “what” and the “why” of their goals and challenges, with the goal to compel the product team to take action to address the customers’ needs[8].

(yawn)

If you’ve made it this far, you may be asking what this has to do with you. Why should you care what’s going on behind the scenes at Microsoft, even if you use Power BI?

You should care because the role of the Power BI CAT team – standing with one foot planted firmly in the problem domain and another firmly in the solution domain – is a success pattern that you can and should generalize in your efforts to build a data culture. Your center of excellence fulfils role similar to that of the CAT team, sitting between the internal customers who are trying to use data to solve business problems and the teams responsible for building and maintaining data systems and tools in your organization.

The details will be different, because the details are always different – but the pattern can be applied wherever there is a group of people with solutions that are relevant for different groups of people with problems.

Last month I had a chance to work with MVP Chris Wagner, an analytics architect who leads a data enablement and community team at Rockwell Automation. As part of a presentation that he and I delivered for Rockwell’s BI community, Chris presented this slide:

He stole my slide! And I could not be happier to see it.

Chris recognized that my mental model for the Power BI CAT team aligned closely with his mental model for how technical community works at Rockwell… and as they say, plagiarism is the highest form of flattery.  If your job or your team has you standing on the border between the problem domain and the solution domain, you should feel free to steal my slide too.


[1] Checks watch… oh my goodness it’s been almost four years. Anyway…

[2] Eat your heart out[3], Aerosmith.

[3] Also apparently Dio.

[4] I’m pretty sure the team has nearly doubled in size since then, so maybe it’s time to have another offsite. I wish we could do this in person again. Please get vaccinated if you’re able to get vaccinated, so you will be part of the solution instead of being part of the problem.

[5] If you’re interested in somewhat dated but still wonderful and relevant insights into how the Power BI product team makes these decisions, please watch this 2019 presentation from Will Thompson.

[6] I’m always skeptical when someone describes himself as an “expert” on anything other than their own lived experiences, because the more I learn about a subject the more I realize how little my knowledge encompasses the subject as a whole.

[7] You should read this book. If you’ve already read it, you should probably read it again because you’ll learning something new each time you do.

[8] Although no modern cloud service or commercial software product will every be “done” I believe there is strong evidence to say that we’ve been pretty successful so far. Over the past few years I’ve had scores of customers tell me that “we know that if something we need isn’t in Power BI today, it will be there next month, or six months from now.” The real credit for this earned trust goes to the product team, but it feels pretty nice to be part of that story.

Problem Domain, Solution Domain

I’ve been thinking about problem domains and solution domains a lot lately. I’ve posted on this topic before, but the more I think about it, the more I think I should explore it more. Starting now.

Image of interlocking puzzle piece hearts from https://pixabay.com/illustrations/puzzle-heart-love-two-hearts-1721619/
When the right problems meet the right solutions, it can be magical

Let’s begin by defining our terms.[1]

A problem domain is a subject area where people work.

If you’re a business intelligence or data professional, the problem domains of interest are often a business function like finance, supply chain or HR. The problem domain experts[2] are typically people who are work in one of these fields, and who might come to you looking for solutions, or for help building solutions.

A solution domain is also a subject area where people work.

If you’re a business intelligence or data professional, the solution domains of interest are often some combination of data visualization, data modeling, data transformation, and so on. They may also be DAX, Power Query, Power BI, or another specific set of tools and technologies.  The solution domain experts[3] are typically people who build data and BI applications and systems to solve problems in other problem domains.

On the other hand, if you’re a member of the Power BI product team[4], the solution domain you’re working in is commercial software development – and the problem domain of interest is building, deploying, monitoring, and/or administering Power BI solutions. Everything is relative, and whether a given subject area is a problem domain or a solution domain is a function of the context in which it is being evaluated.

Let’s pause to let that sink in for a minute. None of the information above is particularly new, and it may not seem profound at first glance, but these two terms are some of the most foundational concepts of building a data culture.

A successful and mature data culture is the product of the right people doing the right things with the right data as part of the right processes.[5] This means that a successful and mature data culture involves solution domain experts and problem domain experts having healthy partnerships and mutual respect… which is also a foundational concept that sounds simple until you look at it more closely.

If you think about the traditional relationship between business and IT, “partnership” probably isn’t the first word that leaps to mind. All too often this relationship is characterized by conflict and a lack of mutual respect that is in part a function of misaligned priorities. Like many entrenched conflicts it is also partly a function of history and  the mistrust produced by historical wrongs – actual or perceived.

Most interesting things – interesting conversations, interesting projects, interesting jobs and careers – exist at the intersection of the problem domain and the solution domain. Interesting things happen at the edges where one thing ends and another thing begins. This is where complexity is found, because multiple domains are involved and making sense requires experts in each domain.

Unfortunately this is also where things tend to go wrong. Too often things fall into the cracks between the problem domain and the solution domain. Experts in one domain don’t value the other part of the picture, or they don’t see it as their job, or they assume that someone else will figure that part out… or maybe it’s none of these things, and they just lack the vocabulary and the context to close that gap.

Please take a moment to think honestly and critically about the problem domains and solution domains in which you operate, and your relationships with the folks in other domains with whom you interact. This is all I have for today, and although I don’t know exactly where I’m going down this path, I know I’m not done – and I know a little introspection can go a long way.


[1] These are my personal definitions that I’m making up as I write this post. You’ll find other definitions if you’re willing to go looking, and although those definitions will align broadly with these, they will have different emphasis and context and nuance because they’re not mine.

[2] The subject matter experts in a given problem domain.

[3] The subject matter experts in a given solution domain.

[4] Or another team building commercial tools for data and analytics.

[5] And the state in which these things are the norm and the exception.

Tabular Editor and “Desktop Hardening”

Back in June[1] the guys in cubes at Guy in a Cube were joined by Daniel Otykier and Marco Russo for a live stream on YouTube. Daniel is the creator of Tabular Editor, a powerful tool for developing Analysis Services Tabular data models and Power BI datasets. Marco is one of the Italian Data Models[2] behind SQLBI.com, and is a recognized expert on tabular development and DAX.

This special live stream was a Q&A session with Daniel and Marco about the then-new announcement of Tabular Editor 3, an improved and expanded paid version of what had previously been a free, community-supported too. I watched the stream to learn what the excitement was all about, and to see if there were any off-topic questions[3] I could answer in the chat.

Quite a few questions came up where the term “desktop hardening” was part of answer. Questions like this one:

I strongly encourage you to watch the live stream replay for the questions and discussion. Daniel does an excellent job of describing how Tabular Editor uses Microsoft’s Tabular Object Model library to modify tabular data models, and some of the complexities of what’s allowed or not allowed depending on where a given tabular model is located – and I’m not going to attempt to replicate his wisdom here.

What I am going to do here is talk a little about “desktop hardening,” which is a term I’ve heard hundreds of times in meetings and work conversations[5], but which I discovered is practically undefined and unmentioned on the public internet. The only place where I could find this term used in context was in this reply from Power BI PM Christian Wade… and if you don’t already know what “desktop hardening” is, seeing it used in context here isn’t going to help too much.

Here is my definition of desktop hardening:

Desktop hardening is the internal shorthand used by members of the Power BI product team to describe a broad set of ongoing investments to expose the full contents of the Power BI Desktop PBIX file format as a supported public API.

There are a few things worth picking apart in this definition. Let’s start with “public API,” and let’s start by clarifying I don’t mean “REST API” or “.NET SDK” or anything of the sort. In this post I’m using the broadest possible definition of the term API, which to me is “a set of functionality that can be accessed programmatically.” More on this below.

As this excellent post from API Academy highlights, a public or “open” API “is an interface that has been designed to be easily accessible by the wider population of… developers” and that “opening an interface to external developers can significantly add to the management and security challenges associated with publishing APIs. For example, with many third-party client apps active in the field, it can be very challenging to ensure that interface updates do not break application functionality.”

Conversely, “Private APIs can significantly reduce the development time and resources needed to… build new systems that maximize productivity and create customer-facing apps that extend market reach and add value to existing offerings.”

In my experience, a private API can be updated with minimal or moderate effort. By definition it’s only being called by developers in your organization, so if you need to make a change you let them know and they update their client code[6]. On the other hand, updating a public API is very complicated and very costly, especially when there are legal and/or regulatory requirements that need to be met. When updating a public API you need to worry about a thousand different things, from documentation to backward compatibility. These considerations make every aspect of maintaining the API slower, more complicated, and more costly. Forever.

Now that we’ve looked at the “public API” part of the definition, let’s also look at the “Power BI Desktop PBIX file format” part.

The Power BI Desktop authoring tool uses the PBIX file format, which is implemented similar to the Open XML format introduced in Office 2007. Each PBIX file is basically a Zip archive that contains a set of folders and XML and JSON files. Each PBIX file contains the tabular data model, reports, Power Query mashups, and related metadata for the a Power BI solution. MVP Gilbert Quevauvilliers of FourMoo explored the contents of the PBIX format if you’re interested in taking a closer look.

Now let’s look at the “ongoing investments to expose the full contents” part of the definition.

As of today, only a limited number of operations on the tabular data model in a PBIX file are supported for write operations. These operations were exposed as part of the Power BI team’s addition of external tools support to Power BI Desktop, and are documented as part of the docs for external tools.

Today if a given operation isn’t on that list, it’s not supported for use by any client other than Power BI Desktop. If you edit another part of a PBIX, directly it might be OK, but it might not – your edit might make the PBIX unable to be opened by Power BI desktop. And even if a given operation might work today, it might not work tomorrow. That’s how private APIs work – the API owner can make changes at any time, and any client application or process that relies on the unsupported private API may be broken.

I should also point out that the scope of desktop hardening isn’t limited to external tools like tabular Editor. It also includes:

  • The ability for Power BI Desktop to accept dataset definitions that include features that currently cannot be built in Desktop, such as Partitions and DetailRows.
  • The ability to download a dataset defined or modified using the XML endpoint in the Power BI service as a PBIX file or PBIT template.
  • The ability for Power BI Desktop to accept dataset definitions that are produced as part of a git workflow or similar process where a developer might programmatically “merge” changes from multiple branches.[7]

The Power BI team has a goal to provide a public API for everything in the PBIX file (that’s what “desktop hardening” means, remember?) but it’s a long-term goal. You can keep an eye on the public roadmap at https://aka.ms/PBIReleasePlan, but I expect the full vision of desktop hardening to be delivered incrementally over time, rather than in one big release. Desktop hardening isn’t a feature – it’s a bunch of related features and non-feature investments. Because of the ongoing implications of having a public API, it’s important to do things right, and the complexity of this effort cannot be overstated.

That brings us to the final part of the definition I want to explore: the term “desktop hardening” is an “internal shorthand” used by the product team. This isn’t a term that is ever likely to show up in official Microsoft documentation, but it’s become part of the team vocabulary, and has unfortunately leaked into public community conversations as well… sort of like an unsupported API.

I hope that the next time you hear someone talking about desktop hardening you’ll have a better idea of what this term means, and I hope that the next time you hear someone asking about it you can direct them to this post.


[1] Back in June when I started writing this post, which might give you an idea of how my 2021 has been going.

[2] Think about it. “Marco” and “Alberto” have become mononyms not unlike “Fabio”. Also, they’re all Italian, they’re all absolutely beautiful people, and their names always come up when you’re talking about modeling. Seriously, I’m right and you know that I’m right.

[3] Guy in a Cube live streams are usually more “ask me anything” Q&A sessions rather than focused on a specific topic, and there are always more questions than there’s time for answers. Adam and Patrick were kind enough[4] to make me a moderator of their channel, so I do what I can to help out.

[4] Foolish enough.

[5] This is where I add the standard disclaimer that although I am a Microsoft employee and a member of the Power BI CAT team, this is my personal blog and all information here is my opinion and does not necessarily reflect the views of my employer or any of my cats, etc., etc.

[6] Somewhere an engineering manager is screaming and/or crying as they read this. Updating private APIs can indeed be complicated and costly – just not when compared with updating a public API of similar scope and complexity. When you’re building, shipping, and maintaining software that is used by millions of people around the world, nothing is actually simple.

[7] Thanks a million to the engineering manager who took time out of his busy day to provide constructive criticism on an earlier draft version of this post, and to let me know that he felt my definition was too constrained, and that my examples were too sparse. This list of additional examples is based on his feedback – thank you, Akshai!