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.

You are not the customer!

Unless you’re building a solution for your exclusive personal use, you are not the customer. If you are not the customer, you need to invest in understanding the customer’s motivations, problems, and goals. The right solution for you may not be the right solution for someone else – even someone you may think of as being “like you” – for reasons that may not be apparent until you look more closely.[1]

This is another post looking at problem domains and solution domains, and it’s inspired by a recent non-technical conversation over brunch[2] with some foodie friends. My wife and I had gotten together with friends who love good food – people who we’ve had over for dinner many times over the years, and from whom we’re always delighted to receive dinner invitations. These are people who know how to cook, and who have invested in their kitchens over the years, so they’re well-equipped to cook what they want.

Imagine my surprise when a friend who has a fabulous combi oven[4] in her kitchen kept talking about how much she loved her new toaster oven that was also an air fryer. Or when another friend who is more than capable of making wonderful meals from scratch waxed poetic about how Thermomix now allows you to use community recipes from any region so she can now make that dumpling recipe she’s been looking at… and about how easy it is to make toast in her June smart oven because it recognizes that she’s put a slice of bread into it and knows how dark she likes her toast…

My initial reaction was to point out how all of these toys were unnecessary, because they already had the real tools they needed to do all these things and more[5], but as the conversation went on I realized I was in a tastier version of a very familiar conversation. This was a conversion about pro and self-service tools, and  about persona-appropriate experiences.

Have you ever been in a conversation when someone suggests that “all you need to do” to solve a problem is to use some code-first tool or service, when all you really want is something lightweight and simple? You probably don’t have an environment set up already, and even if you did the proposed tool don’t solve the problem in the way you want to solve it… even though the tool is capable of solving the problem.

Have you ever built a report that gives its users all the information they need to answer their questions, only to find that your target audience doesn’t like or use the report, and instead keep building their own workarounds?[6]

Have you ever found yourself on the other side of that scenario, where someone has given you a report with all of the numbers, but none of the answers?

These are all variations on the same theme: the importance of persona-appropriate experiences. Delivering capabilities is never enough – you need to deliver capabilities via an experience that works for the people who will use it.

Building an experience people want to use requires a meaningful understanding of those people. What are their backgrounds, their skills, their motivations, their priorities, their preferences… as well as the problems they need to solve. There are lots of ways to build this understanding, and they all start with your acknowledging that you are not the customer, and your willingness to spend time understanding the actual customer’s problems before you start trying to solve them.

I’m confident that a commercial restaurant kitchen would have all of the capabilities you would need to prepare breakfast – and more. I’m similarly confident that you would prefer something smaller, less complex, and more familiar – like your own home kitchen – and that if you were only given a commercial kitchen you would soon start looking for alternatives or workarounds that work better for you.

The next time you’re working to solve someone else’s problems – whether you’re building a BI solution or a software product or service – pause to ask yourself if you’re giving a commercial kitchen to a home cook. While you’re paused, also take a minute to remind yourself that you’re not the customer.


[1] The admonition “you are not the customer” was included in some of the first training materials I read when I became a program manager at Microsoft. It took me a few years to understand why this is so important, but now it’s something I think about almost every day.

[2] This was December 2021, our first restaurant meal with friends since December 2019, right before the omicron wave arrived in the Seattle area. Who knows when we’ll be able to dine out again…

[3] Kind of like me. I’ve been cooking and baking as a primary hobby for over 30 years, and have a wonderfully-equipped home kitchen. It doesn’t have every tool or toy I might want, but it has everything I need, and quite a few specialized tools I might not technically need but which make common-enough tasks easier and less time consuming.

[4] A combi oven is an oven that combines convection and steam, and which allows you to precisely control both the temperature and humidity while you’re baking.  This simplifies baking lots of things, from bread to cheesecake, but they tend to be very expensive. They’ve just recently moved from high-end professional kitchens into high-end home kitchens. I don’t own one, but I definitely want one.

[5] Thankfully I had my mask on as we were waiting for our food to arrive, which made it easier for me to keep my damned mouth shut for just one minute, which is not my forte.

[6] Yes, in Excel.

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!