Dataflows and API data sources

These days more and more data lives behind an API, rather than in a more traditional data source like databases or files. These APIs are often designed and optimized for small, chatty[1] interactions that don’t lend themselves well to use as a source for business intelligence.

beehive-337695_1280
I’d explain the choice of image for APIs, but it doesn’t take a genus to figure it out

These APIs are often slower[3] than a database, which can increase load/refresh times. Sometimes the load time is so great that a refresh may not fit within the minimum window based on an application’s functional requirements.

These APIs may also be throttled. SaaS application vendors often have a billing model that doesn’t directly support frequent bulk operations, so to avoid customer behaviors that affect their COGS and their bottom line, their APIs may be limited to a certain number of calls[4] for a given period.

The bottom line is that when you’re using APIs as a data source in Power BI, you need to take the APIs’ limitations into consideration, and often dataflows can help deliver a solution that accommodates those limitations while delivering the functionality your application needs.

I’ve covered some of the fundamental concepts of this approach in past blog posts , specifically this one from 2018 on Power BI Dataflows and Slow Data Sources, and this one from 2019 on Creating “data workspaces” for dataflows and shared datasets.  I hadn’t planned on having a dedicated new blog post, but after having this pattern come up in 4 or 5 different contexts in the past week or so, I thought maybe a post was warranted.

In late July I met with a customer to discuss their Power BI dataflows architecture. They showed me a “before” picture that looked something like this:

2020-08-01-13-40-14-887--POWERPNT

One of their core data sources was the API for a commercial software application. Almost every Power BI application used some data from this API, because the application supports almost every part of their business. This introduced a bunch of familiar challenges:

  • Training requirements and a steeper-then-necessary learning curve for SSBI authors due to the complex and unintuitive API design
  • Long refresh times for large data extracts
  • Complex, redundant queries in many applications
  • Technical debt and maintenance due to the duplicate logic

Then they showed me an “after” picture that looked something like this:

2020-08-01-13-40-32-810--POWERPNT

They had implemented a set of dataflows in a dedicated workspace. These dataflows have entities that pull data from the source APIs, and make them available for consumption by IT and business Power BI authors. All data transformation logic is implemented exactly once, and each author can easily connect to trusted tabular data without needing to worry about technical details like connection strings, API parameters, authentication, or paging. The dataflows are organized by the functional areas represented in the data, mapping roughly to the APIs in the source system as viewed through the lens of their audience.

The diagram above simplifies things a bit. For their actual implementation they used linked and computed entities to stage, prepare, and present the dataflows, but for the general pattern the diagram above is probably the right level of abstraction. Each IT-developed or business-developed application uses the subset of the dataflows that it needs, and the owners of the dataflows keep them up to date and current.

Life is good[5].


[1]There’s a lot of information available online covering chatty and chunky patterns in API design, so if you’re not sure what I’m talking about here, you might want to poke around and take a peek[2] at what’s out there.

[2] Please let me know if you found the joke in footnote[1].

[3] Possibly by multiple orders of magnitude.

[4] Or a given data volume, etc. There are probably as many variations in licensing as there are SaaS vendors with APIs.

[5] If you’re wondering about the beehive image and the obtuse joke it represents, the genus for honey bees is “apis.” I get all of my stock photography from the wonderful web site Pixabay. I will typically search on a random term related to my blog post to find a thumbnail image, and when the context is completely different like it was today I will pick one that I like. For this post I searched on “API” and got a page full of bees, which sounds like something Bob Ross would do…

Data Culture: Roles and Responsibilities

In last week’s post and video we looked at how business intelligence tools have evolved, with each evolution solving one set of problems and introducing another set. The video described how self-service BI tools have enabled business users to work with data in ways that used to require specialized technical skills, freeing up IT to focus on other tasks – but at the same time introducing challenges around oversight, consistency, and control.

And that brings us to today’s video.

The video includes a few different graphics, but for this post I want to highlight this one, which I’ve taken from the Power BI Adoption Framework[1].

2020-07-31-20-44-46-355--POWERPNT

Successful cultures balance freedom and restriction in ways that benefit the culture as a whole. It’s a compromise – no one gets everything they want, but everyone gets the things they need the most.

For a data culture, this typically involves letting IT do the things they do best, and letting business get down to business.

When an organization adopts a managed self-service model for business intelligence[2], the central BI team in IT[3] does the heavy lifting. This means they prepare, cleanse, warehouse, and deliver the biggest and most important[4] data. They deliver the data that would be hard for a business user to create using a self-service tool, and which will be used by a large audience. They do the things that have broad reach, strategic impact, and strategic importance. They create things that need to be correct, consistent, and supported.

And business users do the rest. This is a broad bucket, but it’s a reasonable generalization as well. Business users create their own reports using the IT-managed data sources and data models. And they prepare and use their own data where there is no IT-managed data for a given purpose.

Over time a given application or a given data source may become more or less managed, as the culture adopts, adapts, and improves.

Managed self-service BI isn’t the only way to be successful, but in my experience working with many large Power BI customers it’s the best way and most predictable way. By having clearly defined roles and responsibilities – who does what – moving from either extreme can overcome the problems that that extreme creates, without going too far in the other direction.

Does your organization take this approach?

If it doesn’t today, what will it take to make this change a reality?


[1] Which I have shamelessly stolen from the Power BI Adoption Framework, because it is an awesome graphic and because I love to stand on the shoulders of giants.

[2] Which is the approach most likely to drive short-and long term efficiencies and successes.

[3] Please understand that this is a gross simplification. This “central BI team in IT” may be a single central team for the whole company, it may be a single central team for a specific business unit, or it may be one of dozens of BI teams that are designed to support a distributed global enterprise. This is less about the technology and the culture than it is about organizational structure, so I don’t expect to ever try to tackle this diversity of approaches in this series.

[4] Next week’s video will talk a little more about what data is likely to qualify for this distinction. And since the video is done already, I’m pretty confident to say “next week” this week.

Weekend reading: New dataflows docs

In case you didn’t have any plans for the weekend, the Power Query team has released a treasure trove of new documentation for dataflows in Power BI and Power Apps.

You can find the new docs here: https://docs.microsoft.com/en-us/power-query/dataflows/overview-dataflows-across-power-platform-dynamics-365

There are over 20 articles in this new resource, and the content they contain answers many of the questions I hear most frequently. For some reason WordPress isn’t letting me upload the screen shot I took of the content outline, so instead here is a picture of me[1] when I first learned about this new content:

woman-591576_1280

Please check out this new information, and please submit feedback to the documentation team using the feedback form on each page. When you let the docs team know what you like, what you don’t like, or what is missing, they read every comment!


[1] Me in my avatar as a photogenic young woman.

Data Culture: A brief history of business intelligence

Why is a data culture important?

Why does building a data culture in the 2020s involve the challenges and goals that it does?

Why am I publishing in July a video recorded in January?

battleship-389274_1280
BI Application Internals, Circa 1986

The first two questions are probably the easier ones to answer, so I’ll save the third one for last.

For the first question, I’ll refer you to the first post and video in this series, which introduces the key concepts for a data culture.

For the second question, you need to understand how we got to where we are today – how the data and BI technologies we use today evolved over the past decades. And that’s where today’s video[1] comes in. Check out a brief history of business intelligence for context on the problems that a successful data culture needs to solve, and what your starting point might be.

I won’t go into depth here (I spent six months on that video, so darned right I’m not going to spoil it!) but there are basically four phases in this brief history:

  1. The dark ages, also known as “back when I was your age” when there was no BI, and decisions were made based on human knowledge, instinct, and experience
  2. The good old days, when BI solutions were developed by highly skilled BI professionals using complex and difficult tools, and when ETL consultants could charge exorbitant rates[2] without clients blinking an eye
  3. The SSBI era where every business user got up to their elbows in data, and IT wasn’t slowing anyone down any more
  4. Nirvana[3], where IT and business work together as partners, each contributing based on their skills and priorities to common shared goals

And each phase brought (or brings) with it its own challenges:

  1. Dependence on specific individuals with specialized knowledge, slow decisions, and probably giant hair you spent hours sculpting with Aqua Net
  2. Expensive development efforts, long turnaround times for insights, IT becomes a bottleneck
  3. Inconsistent results, untrusted (and untrustworthy) insights, producing misinformed business decisions
  4. Difficulty in aligning business and IT and making the necessary cultural changes… also flannel?

And for that third question, please let me just say that 2020 is kicking my butt in ways I never expected. Between work and family and the global pandemic that’s ravaging the globe, I’ve needed to harshly triage all non-essential work. Back in January I was sure I would publish this video a week late, but still in January. Back on February 24th I made this optimistic comment:

In case you’ve been wondering why my blog and YouTube output has dried up this month, it’s because real life has been kicking my ass. I think I can finally see the light at the end of the tunnel, so hopefully we’ll be back with regular content before too long. Hopefully.

That was about a week before the coronavirus outbreak at the nursing home down the road from my house hit the news, and it’s all been downhill from there.

I’m tempted to say I again see the light at the end of the tunnel, but I’m not going to do that. The last time the light turned out to be a train. I will instead say that I’ll do my best to get more blog and video content created and published as consistently as possible and that I appreciate your patience and your support[4].


[1] Today’s video was recorded on January 19th as “take two” that delayed its publishing. Basically I was filming the video at the end of the day when I had planned on publishing it, so I figured it would end up being a week late. Yeah.

[2] This may not be how everyone remembers those times, but this is my blog and my story and I’m sticking with it.

[3] Or Pearl Jam. Maybe Soundgarden. I never really did keep up with grunge, so I’m honestly not sure.

[4] I don’t want to get anyone’s hopes up, but between Friday evening when I wrote this post and finished editing the video, I have recorded the next two videos in the data culture series, and edited the first of them. Only time will tell if August is kinder to me than recent months have been, but barring unforeseen circumstances you’ll see the next two videos on the next two Monday.

Thoughts on certification

My professional career was enabled by professional certifications. I would not be where I am today – however you want to parse that statement – without my Microsoft certifications[1].

graduation-1449488_640
Certification rocks!

Before you read too much into that bold opening statement, please allow me to elaborate by telling my personal certification story[2]. This post was prompted by an online conversation, and responses on Twitter that made me remember that this is a nuanced topic, and that my experience is part of a bigger discussion.  Understanding my story will help put my opinions in context.

tl;dr: certifications are a great way to build and validate knowledge and skills, and can help meet career development goals while complementing real world experience.

I started my IT career in 1996 when I got a job as a software applications trainer. I spent around six months teaching end users how to use Word, Excel, PowerPoint, Access, Windows 3.11, and Windows 95. Despite being terrified of public speaking[3], it turned out I was pretty good at it. After those six months I was offered the opportunity to become a Microsoft Certified Trainer and to teach MCSE[4] courses at the training center where I worked. The risks were high[5], but the potential gain was enough to offset the risk. I said yes.

At this point you might be saying something along the lines of “hey wait, you didn’t have any real world experience!” You’re exactly right. And I wasn’t about to get any. I was passing exams so I could teach people with real world experience how to do their jobs that I had never done. I’m as aghast as you are. Please bear with me.

After I passed my first few exams[6] I developed a study routine that looked something like this:

  1. Read the courseware cover to cover
  2. Make a list of topics that I didn’t understand well enough to explain
  3. Investigate these topics in secondary sources like Microsoft TechNet CDs and technical books[7]
  4. Read the courseware again, crossing items off the list as appropriate
  5. Repeat steps 3 and 4 as necessary
  6. Take and pass the exam
  7. Teach the class
  8. Get awesome ratings from my students[8]

Fast forward a few years and I was in a consulting role[9] where training was a much smaller part of my responsibilities. Instead, I was working with clients to design and implement their network infrastructures, and later to design and implement custom software solutions using SQL Server, Visual Basic, and ASP.

This transition had a few bumps, but it was all about the tasks, not the knowledge. The skills and knowledge I had gained by preparing for certification exams and had reinforced through training enabled me to hit the ground running.

I no longer needed to pass exams to teach classes, but I did have another motivation: my company was a Microsoft Partner, and the benefits we received depended in part on the certifications held by our employees. I kept taking exams, but because I had different goals and a different starting point of skills and experience, I took a different approach.

My new exam prep routine looked something like this:

  1. Read the “skills measured” list published by Microsoft on the exam web page
  2. For any skill on the list where I don’t have meaningful knowledge and experience, study and practice the skill
  3. Take and pass the exam

This new routine is shorter and simpler because I now had more experience and a richer personal knowledge base to rely on, and because I wasn’t preparing to teach a class – I just needed to pass the exam and use the product.

I adopted this second exam prep routine over 20 years ago, but I’ve never seen the need to change it, even as my role and motivations have evolved.

Since 2001, I have used certification exams mainly as a skill-building tool. When there is a new tool or technology I want to learn, I will use the exam skill list as my prep guide, and the exam as validation that I have learned it well enough. (More later on what “well enough” means here.) The list of skills measured by an exam is informed by a lot of industry research, and it covers a broader range of topics and features than you’re likely to encounter solely through hands-on project work.

One standout example of this process is BizTalk Server. Using this exam prep approach I was able to quickly build my BizTalk knowledge well enough to use it on a few projects. I wasn’t proficient on day one, but I went into my first BizTalk project with a clear understanding of what features it had, where to use each one, and how they worked together. This meant that as I was figuring out how to use a specific feature to solve a specific project requirement, I knew from the beginning what feature to use, and where to begin. Because I was certified.

But that’s just me. What about the other guy?

In the late 90s and early 00s the phrase “paper MCSE” got kicked around a lot. The idea was that some people would pass the exams and gain the certification without actually earning it, maybe by going to an illegal brain dump site or otherwise cheating. They’d have the letters but not the skills to back them up. What about those guys?

In my experience the paper MCSE was more often heard than encountered, but it was a real problem, as evidenced by articles like this one. I hired a few paper MCSEs, and I fired a few as well.

Based on my personal positive experience with certification, I updated my interviewing and hiring practices to go deeper in other areas – and to pay attention to how the candidate represented the importance of their certifications. If someone acted like their certifications made them God’s Gift To IT, that was a red flag – their certification had become a reason to not hire them. If someone acted like their certifications provided a foundation on which they could build with experience, they were more likely to get hired.

I’m hesitant to compare certifications to college degrees, because I have never completed a degree program – but I’m going to make the comparison anyway. I don’t know of any technical jobs where simply having letters after your name – be it MCP, MCSE, OCP[10], BA, or BS – makes you qualified. If you’ve earned those letters, they contribute to your qualification to one degree[11] or another. The rest of who you are, what you know, and what you do, is what completes the qualification.

I haven’t passed a certification exam since 2012. On my birthday in 2012 – four years into my career at Microsoft, and having just helped ship SQL Server 2012 – I decided I would take the SQL Server 2012 exams on my birthday as a present to myself[12]. All seven of them. And I passed them all. I knew my stuff, and it showed.

But I have failed an exam since then. A year or two later I was at a conference where a testing center was set up. A friend was taking an exam, and the folks at the testing center had an opening (also, as a Microsoft employee, I didn’t need to pay anything) so I said “why not?” I picked an exam on some technology I’d dabbled with, and went for it. I figured my general platform knowledge and experience and my having passed over 75 different Microsoft exams should compensate for my lack of knowledge on the specific topics being tested.

I failed, and not by a small margin. And this was what should have happened. I was not a qualified candidate for the skills being tested on this exam. I should have known better, but tried it anyway. The exam knew better.

Anyway, that’s enough about me. Let’s talk about you.

This whole post started this morning when someone[13] on a Guy in a Cube live stream asked if he should take the DA-100 Analyzing Data with Microsoft Power BI exam.

Rather than simply saying yes, please take a look at the skills measured by this exam and let me ask you if you would like to have those skills validated. Or, ask yourself if these are the skills that hiring managers would want a Power BI data analyst to have.

Then I’ll say yes. You should take the exam.

If it aligns with your goals well enough for you to be asking in the first place, yes you should take and pass the exam. Use the skills measured by the exam to guide your study and preparation, and then use the exam to validate that you know the topics well enough.

If you’re using Power BI every day, odds are you can spend a few weekends studying the things you don’t do very often, and you’ll be ready to pass the exam. If you’ve never used Power BI you’ll probably need to spend a few months studying and practicing before you can pass. But either way, the skills measured by the exam align well with what you need to succeed in the real world – and how could that be a bad thing?

Remember how I said earlier that we’d get back to what “well enough” means in the context of certification validating skills? Think about when you got your drivers license and the test you had to take. Think about the times you failed the test, or the friends who failed it.

Did passing the test and getting your license make you a Formula One driver? No, it validated that you were a minimally qualified driver, and that you were now allowed to legally operate a specific class of motor vehicle because you’d met a predefined bar. This is what certification tests to – they show that you are minimally qualified for the skills or role tested by the exam.

As I’m publishing this post, my question on Twitter has gotten 31 replies, and spawned some interesting discussion. I’m not going to try to reply to all of those topics here, but I’d love to have you join the discussion, and to hear what you think.

Also, when you take and pass the DA-100 Power BI exam, let me know what you think, and if you agree with me.


[1] If you’re interested, you can see my certifications by clicking on this link, and entering the transcript ID 678145 and transcript sharing code MatthewMCPCode1 when prompted.

[2] Part of it, anyway. The whole thing would likely take far too long to tell, and I would be as bored as you.

[3] My previous presentation experience was a college public speaking class where I needed to deliver 5 or 6 short speeches to the rest of the class. I spent 20-30 minutes before each speech being physically ill in the restroom down the hall from the classroom. But the trainer job’s starting pay was double what I had ben making at my retail job, and it was working with computers – combined, that was some serious motivation.

[4] If you think back to 1996, you’ll remember what a big deal the MCSE was. I remember seeing people charging over $1000 per day to deliver the official training courses. If that doesn’t sound like a lot of money to you, please consider that very recently I’d been making under $7.50 per hour. This was the opportunity of a lifetime.

[5] The deal was basically this: My employer would pay for the certification exams for each new course I was asked to teach, and I would pass them. They would also pay for me to attend each course – this was a Microsoft-mandated prerequisite to teaching at that time. For each course day, I would get a paid study/prep day. By the end of the prep days I needed to pass the exam(s) required to teach the course. If I did, I got a raise for each new course I taught. If I didn’t, I would need to pay my employer back my salary for the prep time, and the full retail cost for the course I attended. It’s also worth noting that at this point I had 3 peers who had collectively taken and failed 4 or 5 exams, and I knew zero people who had passed one. So yeah, high stakes, high pressure.

[6] The first exam I took was Implementing and Supporting Microsoft Windows 95. I was so nervous that when I sat down at the testing center computer to take the exam, I looked around trying to figure out where the loud pounding noise was coming from, because I was hoping for a quiet environment for this stressful activity. After a minute I realized that the loud pounding noise was my own racing heart. I am not exaggerating at all – I was terrified, but I prevailed.

[7] This was before “searching online” was a thing. Kids these days and their Googles don’t know how good they have it. <<shakes fist at cloud>>

[8] I don’t mean to brag, but despite my lack of real world experience, I was really good at what I did. Really, really good. I earned “instructor of the month” every month, and because this was disheartening to the applications instructors the training center split this recognition into “applications instructor of the month” and “networking instructor of the month” so someone else would have a chance. Every time I’m in Albuquerque, my ego tells me I should visit to see if the wall of fame is still there. I tell my ego to shut up, because it’s gotten me into enough trouble already.

[9] I need to shout out here to my amazing mentor Jeff Lunsford, without whom I may never have taken this leap. Of all the people in my life to whom I owe an eternal debt of gratitude, you are the one named Jeff. Thank you for believing in me, for opening doors for me, for pushing me when I needed pushing, and for supporting me when I needed support.

[10] Did you know I was Oracle certified too? Around 20 years ago I got my Oracle Certified Professional Developer certification as a way to learn the basics of Oracle database. I fell in love with P/L SQL, but I hated Oracle’s tools so much I never did anything with the certification or the knowledge.

[11] Pun not intended, I swear.

[12] If you look at my transcript, only five of these seven show up on my birthday, but there are other weird date discrepancies throughout the transcript too. I’ve learned to let it go. Also, if you try to schedule 7 exams on the same day please be prepared for the exam people to tell you “no” more than once.

[13] YouTube isn’t letting me see the chat for some reason, so I can’t say who it was.

[14] I’m still laughing at the “certification rocks” caption on that photo. I get all of my random pictures from https://pixabay.com/, just so there’s something to show in the preview when the link is shared. This one works on so many levels… if like me you have the sense of humor of a 12 year old…

5’ve got all the swords

Power BI turns five years old this week. On July 24, 2015 Microsoft announced the general availability of Power BI, a new cloud service for self-service business intelligence.

In the five years since then Power BI has evolved to be much more than a self-service tool. Power BI today includes capabilities for self-service and enterprise BI, adding data preparation, information management, machine learning and more, while still including powerful tools for data visualization and reporting.

Today[1] the Power BI team at Microsoft is commemorating this milestone by saying thank you.

PowerBI_5YearAnni_1584x396-04-1024x256

Even though I’m on the Power BI team at Microsoft, this is my personal blog, and I wanted to say thank you as well. So I put together a short video that includes five of my favorite things:

  1. Power BI
  2. Swords
  3. Bad jokes
  4. My awesome new “5’ve got the Power BI” t-shirt
  5. Worse jokes[2]

The one thing I couldn’t include is all of you.

Thanks for the comments and feedback on the blog and the videos, and thanks even more for helping make Power BI what it is today.

 


[1] Yes, I know today is July 23rd here in North America, but by commemorating this milestone today we can be more inclusive for folks in parts of the world where it’s already the weekend.

[2] There is one joke in the video that I suspect I am the only person who will understand enough to laugh at it. If you get it please tell me so I don’t feel so alone.

If you cannot change, you cannot grow

Back in 2013 I presented for the very first time at the MVP Global Summit. I was terrified.

At this point I had been working for Microsoft for five years. I’d been an MVP before joining Microsoft as an employee and I had been a regular public speaker for many years – but this presentation felt different. MVPs are some of the most technical, most vocal, and most skilled people I know, and as a general rule they are talented presenters and communicators. I was very nervous, and I wanted to break the ice, both to help myself relax and to set an open and conversational tone for the session.

So I started my presentation like this:

2020-07-01-15-36-56-716--POWERPNT

A warning before we proceed: This post is likely to have more coarse language than I usually use. If you’re offended by swearing you may want to consider skipping this one, but I hope you’ll read on. I’m writing the warning before I write any swears, so maybe I’ll find another approach by the time I get there.

As the initial icebreaker I used the cover of Madonna’s 1984 album “Like a Virgin.” This was my first MVP Summit presentation, and I wanted to remind everyone that even though I might be a familiar face, this was my first time… or close enough.

The next slide represented the transition from the intro into the technical content of the session. I wanted to emphasize how we were talking about internal Microsoft processes and planning[1], and that the session would share these details that are typically not shared with anyone not on the product team… so I used the title “opening the kimono[2]” and as a family-safe but slightly risqué visual joke I used a “no image found” placeholder for the slide body.

The session went better than I could have hoped. People laughed where I wanted them to laugh, and shared deep technical feedback and constructive criticism were I wanted them to share deep technical feedback and constructive criticism. I was very happy.

Until later in the day, when someone came up to me to let me know that there were complaints. That someone had been offended, that someone hadn’t liked my choice of slides and sexually charged metaphors and images.

I was shocked.

The MVP who was reporting the news let me know that he wasn’t at all offended, and that the complaint came from someone who was always complaining anyway. “You know who,” he said to me, knowingly. I didn’t know who, and I never tried to find out who[3].

Instead, I thought about how my actions had a negative impact on someone in a way I didn’t plan, intend, or desire, but which was real nonetheless. That process was painful, and even now – almost seven years later – it is still uncomfortable to write about. I’m worried that someone might comment “you should have known better, you were an adult and 2013 wasn’t 1984” because criticism is difficult, and maybe they were right. Maybe I should have known better, but I didn’t, and because I didn’t I caused problems for someone who didn’t need me causing problems for her.

And I didn’t do it again. I messed up, I learned about my error, and I did my best to correct it. I made a choice, and I chose to change and grow.

That was 2013. Now let’s fast-forward to 2020.

Last week one of my data platform community heroes, Jen Stirrup, stepped down as a Microsoft MVP because the MVP program has not chosen to change and grow.

I want to unpack that last sentence before I proceed. Please bear with me, because this is important.

Jen is one of my heroes. She’s deeply technical – she’s one of those people who knows more things about more things than anyone could ever be expected to know. She works with some of the biggest companies in the world, helping them deliver global scale data solutions using a huge range of technologies. She can hold her own standing with the best of the best because she’s that good. She’s also an effective communicator who freely and selflessly shares what she knows. She’s soft-spoken, but when she’s speaking it’s worth leaning in and listening to what she has to say.

But that’s not the only reason she’s a hero. Jen is compassionate and tireless, and speaks the hard truths where so many others would chose the easy path and remain silent. Jen consistently chooses the right thing even though she knows it is also the hard thing.

And Jen chose to give up her status as an MVP. If you know anything about the program, you’ll know that this is a big deal. The MVP program comes with significant benefits and access to information that is invaluable to someone in her profession. I remember being an MVP and I know that I could not have done what she did. I am in awe.

The final thing I want to unpack is community – I called Jen a data platform community hero. The context of a community carries some baggage that may not be obvious, and I want to look at it a little bit here. Every community is defined by the behaviors it tolerates. If a community tolerates abusive behavior, that abusive behavior will thrive and will become the norm. People who can’t tolerate the abusive behavior will leave, and they won’t return. People who are made uncomfortable by the abusive behavior will engage less, and contribute less. And people who appreciate the abusive behavior will recognize that it is allowed, and they will repeat and amplify it. This is how communities work – not just technical communities.

Weeding out the “bad apples” isn’t enough. Successful communities need to define what they want to be, and to do it deliberately. I don’t know of any technical communities that do this[4], but I know one in another part of my life: Valkyrie Western Martial Arts Assembly. Valkyrie is a martial arts school in Vancouver, BC that saw the abusive behavior in the North American HEMA[5] community and decided to be something different. In this awesome blog post, Valkyrie co-owner Kaja Sadowski describes how she did what she did, and why. Please read the whole post (it’s much shorter than this one) but for now, please read this bit:

If a female student is worried about whether the guy she’s partnered with is going to hit on her again, or “accidentally” grab her breast, or refuse to hit her in drills, she can’t focus on her training. If an Asian student feels like he has to prove he’s not a bookish stereotype, or has to put up with constant shitty Kung Fu jokes, he can’t focus on his training. If a queer student is anxious about how their fellow students will react when their partner comes to pick them up after class, or a transgender student is stuck worrying they’ll be called out for using the “wrong” bathroom or misgendered by their training partner, they can’t focus on their training. All of these students end up in a position where their learning suffers, because they can’t put 100% of their energy and effort into their training and instead have to deal with the background noise of harassment and discrimination. If I put them in that position, I have failed them as an instructor.

Kaja decided that inclusion and diversity was important, so she decided to be aggressively inclusive.

Aggressively. Inclusive.

And the results are fantastic. I’m a middle-aged straight white guy. I’m tall and reasonably fit and reasonably good and I expect I would be welcome in any club. I have never felt more welcome than I am at Valkyrie. I visit Valkyrie for events, and am delighted when they come down to my club in Seattle for our events. They built a community that is aggressively inclusive for the people who might feel unwelcome in a traditional martial arts school, and along the way they built a community that includes everyone.

That’s how inclusion works.[6]

I’m not done. There’s one more thing I need to call out here – both because it’s important and because it is central to Jen’s decision – and that is the #MeToo movement.

Some of you might be saying “MeToo is so 2017, is that still a thing?” I hope not, because that is likely to get the swearing started, and I’ve done so well so far.

MeToo has the name that it does because women have put up with sexual harassment and sexual abuse that men don’t experience, and often men don’t see. Women are literally saying “me too – I have also suffered the thing that you are talking about” because for far too long they’ve been told everything but this.  Most[7] of the women you work with have been harassed and abused at every step of their careers, and they’ve tolerated it as best they could.

This is important, and this goes back to my personal story from 2013.

My choice of slides may not have been a big deal. On their own, they may not have been worth mentioning, even for someone who would rather I chose different images and phrases. But they weren’t on their own – they were part of a lifetime of challenges and indignities large and small. Despite my lack of malice, they were another straw on the back of someone who didn’t need one more person telling her she wasn’t welcome, and I was that person. Casual sexism doesn’t need to be intentional to be hurtful.

In 2013 the only person who mentioned that complaint was the guy telling me it was no big deal.

In 2016 a small martial arts school decided to build a welcoming and inclusive place for everyone, and their results speak for themselves.

In 2020 one of my heroes left the MVP program.

I suspect that in 2020 there are young women and brilliant people from other underrepresented groups who are questioning if the MVP program has a place for them.

Communities are defined by the behaviors they tolerate.

One community in this story chose to make diversion and inclusivity a true priority, and one apparently did not. One invested time and money and careful thought in “actively and aggressively building… a safe space for all,” and the other one apparently did not.

I hope the MVP program will recognize this moment for the wake-up call it is. I hope the MVP program will choose to change and grow.

But it needs to be a choice.

Hey, look – no swearing after all. Good on me[8].


[1] The MVP Summit is an NDA event, which is one of the things that makes is to much fun.

[2] If this phrase feels immediately inappropriate as you read this today, I would like to share this MacMillan Dictionary post from the general timeframe of this event that calls this phrase “a vividly effective metaphor for conveying transparency and frankness” which nicely sums up my intent.

[3] The same brain that made it hard for me to know who might have been offended by my presentation also makes it hard for me to remember the male MVP who let me know about the complaint. Faces and names have always been hard for me.

[4] Not to say that they don’t exist – there’s a universe of stuff out there I don’t know about. If you know of a vibrant and inclusive technical community, please tell me.

[5] HEMA stands for Historical European Martial Arts, which is a blanket term for the modern swordplay practice I love so much. The HEMA community has enough “bad apples” to drive away many people who would love it as much as I do, including sexism, racism, and more. That community is starting to change and grow, and Valkyrie is part of the reason. There are some other delightful folks in this community who take no bullshit, and I love them too. If you want to imagine what the abusive behavior looks like, watch Karate Kid, but give the Cobra Kai dudes swords.

[6] If you don’t believe me, check out this great presentation from Jasmine Orange on “Designing for the ten percent.”

[7] Look it up yourself – do a little legwork on this one, bro.

[8] This was a rough post to write – it’s difficult to talk about hurtful mistakes even if they’re years in the past. I like to think I am a better person than I was a year or a decade ago, but I still make mistakes. I still act thoughtlessly and carelessly, and I still fail to live up to the moderately lazy standards I set for myself. But I’m trying, and the people in my life are forgiving.

One report for multiple audiences

Have you ever needed to build a Power BI application that make insights and data available for use used by multiple groups of users, where those user groups have some overlapping needs, but also some exclusive needs?

Maybe requirements like these?[1]

  • A dataset with sales data that needs to be used by store managers and district managers
  • A sales report that contains both store-level and district-level pages
  • Each district manager should see only data from stores in their districts
  • Each store manager should see only data from their store
  • District managers should see all pages
  • Store managers should see only store-level pages

Until we got to the last two requirements, you were probably thinking about row-level security, but then you may have started to have doubts. Hold that thought.

Image by Tayeb MEZAHDIA from Pixabay

First and foremost, protecting data at the source is the most reliable way to prevent unauthorized users from accessing what they are not entitled to see. Hiding elements in the UI layer is not a security mechanism and should never be treated as one.

Let’s make that stand out a little more with some fancy formatting.

Protecting data at the source is the most reliable way to prevent unauthorized users from accessing what they are not entitled to see. Hiding elements in the UI layer is not a security mechanism and should never be treated as one.

Sometimes security isn’t everything you need – sometimes you need a targeted or adaptive user experience as well. This just got a little easier with Power BI and some recently released capabilities for conditional formatting.

Here’s the short version:

Using some of the same techniques you use for row-level security, you define roles in your data model that say what users can see what pages. You hide the pages in your report, and you implement navigation controls in your report using buttons on report page surface. When you publish the report, you define row-level security rules in the report dataset to identify what users and groups belong to which roles, which in turn are used for the conditional formatting used to control report navigation.

Here’s the long version: Read these blog posts from Gilbert Quevauvilliers and Marc Lelijveld.

Marc and Gilbert both go through step-by-step detail and each one explains the approach slightly differently, so I recommend reading each of their posts. They’ve saved me the trouble of writing up something detailed myself.

This approach gives you another tool in your Power BI tool belt, and may be useful if you’re trying to reduce the number of reports you manage and want to customize a single common report for multiple audiences.


[1] This is a generic example with generic data and roles, but the actual scenario details seem to be common across industries and data domains. I’ve heard variations on this theme from enough customers to believe that this is a common use case.

You can do it – don’t give up!

After my recent post where I dug into hidden difficulties, I feel like it’s important to share the other side of the story. If you really want to do something, you can do it[1].

It’s easy to look around – especially on social media – and see people making complex and difficult tasks look simple and effortless. It’s also easy to judge your own learning processes, struggles, and failures, against the final edited results of people who have been learning and practicing for years – and to feel disheartened by the comparison.

Please do not fall into this trap – everyone makes mistakes. Take a look at this outtake from my recent video on being a PM at Microsoft:

The final video ended up including around 20 minutes of “talking head” footage, but I had closer to 40 minutes of raw footage before I edited out all most of the mistakes and false starts.

It’s not just me. One of my data community heroes, Power BI MVP Ida Bergum, recently shared a similar example:

If you’re ever feeling like an impostor[2], like there’s no way your efforts could ever measure up to all the “experts” you see online, don’t give up. You can do it.

Every successful example that you see online or at a conference or on a bookstore shelf has gone through the same journey. They may be further down the path – that’s usually the easy thing to see. They also had a different starting point and they probably have a different destination in mind as well, even if it’s close to your goals.

Even if you see someone and think “I wish I…” it’s important to remember that they struggled along the way as well. Odds are they struggled in different ways or in different places, but no one’s journey is free of bumps and detours – even if people usually prefer to share the highlights from the easy parts.

One of my favorite quotes on this subject is from a cartoon alien-dog-thing, and you can find it all over Pinterest:

Picture from https://quotesgram.com/adventure-time-jake-the-dog-quotes/

It never feels great to suck at something you want to not suck at, but everyone sucks at the beginning. Most people just don’t like to admit it – at least not until they’ve moved past the “sorta good” phase.

One overlooked benefit of being earlier on your learning journey is that you can still remember what it was like to suck at something, and what was valuable to you then. If you’ve been doing something every day for 20 years, odds are you may not remember what it was like to just be starting off. But if you are becoming sorta good at something, you have a perspective that you can share with everyone out there who sucks at that thing, and would really like to be sorta good.

Anyway… I didn’t quite figure out how to get the narrative for this post into written form. The story was there in my head, but didn’t want to take shape once I started typing. But I won’t let that stop me, because it doesn’t need to be perfect to be good, and useful.[3]


[1] I’ve included the “if you really want” qualifier deliberately. Motivation and desire are critical for success, because it’s that want that will push you to keep getting back up after you fall down, and will keep you looking for more resources and opportunities to learn and improve.

[2] I still remember the day I first read this blog post by Scott Hanselman. My friend Cheryl had shared it on Facebook, with a comment about how she had thought “it was just me.” I’d known and admired Cheryl for years, and had no idea she felt this way, because I’d only ever seen her kicking butt. This is just another example of why it’s important to talk about mental health.

[3] I swear I didn’t plan this ending, but once it presented itself you’re darned right I was going to run with it.