One report for multiple audiences

Update: Power BI now has native functionality for this scenario, and the workaround approaches described below are no longer needed. For additional information, see this blog post from March 2023.

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.

Diving into the blender

Sometimes things are hard.

Sometimes things are hard, not just hard for you, but just really hard.

And sometimes you can’t always tell which is which, until after the hardship has passed.

This post is something of a personal story, but based on the positive responses I’ve received to my post on being a PM at Microsoft, I suspect that there might be someone out there that might find some lesson or inspiration or something in the story.

In the summer of 2011 I  moved my family from upstate New York to Redmond, WA to join the SQL Server Integration Services[1] PM team. I’d been an SSIS MVP before joining Microsoft in 2008, and this felt like a real dream job. I could help the team finish and ship SSIS in SQL Server 2012, and then make the next version of SSIS better than it could otherwise be.

Except it didn’t quite work out that way.

In reality, I was joining a part of Microsoft that was at the forefront of the massive wave of change that transformed the company into the Microsoft we know today. Everything was in flux, from roles and responsibilities to ownership and processes, to the fundamental questions of what products and services we would build and ship. The 18 months that followed was a series of large and small reorgs, and every time I felt like I was getting my feet under me, everything seemed to change again.

People around me seemed to be rolling with it, and adapting to the new reality. For me, it was chaos, and I floundered. Even though I had worked for Microsoft for three years, this was my first technical PM role. I made it through, but it was one of darkest periods on my adult life. It was hard. It almost broke me, and I thought it was me.

Image by AD_Images from Pixabay
Pixabay didn’t have any pictures of blenders, and this one really captures how it felt.

Later on in 2014 I was working as a program manager on the first generation of Azure Data Catalog, and I was interviewing a new member of the PM team. For the purposes of this story[2] I’ll call this new PM Rick. Rick was a veteran PM who had shipped multiple major features in SQL Server and the first releases of SQL Azure, so he was interviewing the team as much as we were interviewing him.

During the interview, Rick asked me to tell my story – how did I end up where I was, where had I worked, what did I do, that sort of thing. So I told him my story, and when I got to the summer of 2011, he inhaled so sharply that I paused. I looked at him, and saw a look of surprise and respect[3] on his face. He looked at me, and he said:

Damn. You dove into the fucking blender.

He then went on to talk about how in his experience this time and place was the epicenter of unprecedented change and disruption that saw many veterans struggle – and saw many of them decide to look for opportunities elsewhere.

Rick and I worked together for for the next year and a half, and I learned a lot from him over that time. But I think the most important lesson I learned was that it wasn’t just me. I’d thought I was going for a swim in familiar waters, but I had actually dived into a blender, and I managed to get out with most of my extremities still attached.

That’s the story.

I think there’s a lesson in there somewhere about not giving up, about being true to yourself, and about doing what you think is the right thing to do, even when it feels like nothing you do is making any difference on the world of struggle around you. This would be a great lesson, if it’s actually in there.

The lesson that I know is in there is that sometimes things are really hard, and it’s not just you. This isn’t a nice lesson or an easy one, but that doesn’t make it any less real.


[1] SSIS FTW!!

[2] Also because it is his name.

[3] I’m not the best at reading emption, so I honestly don’t know if there was any respect there, but it sounds good so I’m going to roll with it. Rick can correct me if he needs to. He knows where I live.

Foster Kittens and Managed Self-Service BI

My family is a foster family for cats and kittens from the cat shelter where we’ve adopted some of our own cats. Usually a litter will stay with us for a month or two, but it depends on the kittens themselves, and on external factors.

While the kittens are with us, it’s our responsibility to help them grow, both physically and socially. The experts at the shelter are always available if we need help, but for the most part we have the knowledge and tools we need to be successful. In many cases we’re their first real exposure to humans, and we can prepare them to be loving and playful members of a family. Just not our family.

Once the kittens are ready to be adopted, we take them to the shelter, where they will be carefully matched with their forever family. This last part is important – it’s hard enough to let go, and knowing that each of them will find a good home is what makes it possible.

It’s really the best of both worlds – kind of like managed self-service BI with Power BI.[1]

 

Not unlike fostering kittens, managed self-service BI can be the best of both worlds. As an analyst working in Power BI, you can often pick up projects when the scope is still small and manageable, and when you can have fun playing around with the data and seeing what it’s likely to become.

I’m emphasizing the “managed” in managed self-service BI, because it’s best to not be completely on your own. Having someone backing you up, someone with the expertise and resources to get you through challenging spots with a helping hand, is just as important with BI as it is with kittens. An author on his own may make avoidable mistakes with long-term consequences, but a center of excellence or community of practice can provide training up front, and assistance along the way so the finished self-service solution is ready to grow up – and growing up is an important goal.

Just as my family includes our adult cats, that analyst working in Power BI has a day job. If we kept each litter of kittens we foster, things would soon become messy and unmanageable. If an analyst retained ownership of every Power BI solution he developed, he would struggle to stay on top of his core priorities.

Being able to hand off a self-service solution to a central BI team is what gives this story a happy ending. The BI team can give the analyst’s work the long-term home it deserves, and the analyst can get back to his job… while also keeping an eye open for the next self-service BI challenge to come along and steal his heart.

Of all the kittens I have loved, I miss Tiny the most.
Your head. I will bite it now.[2]
If you’re interested in learning more about the shelter where we volunteer, please visit the Meow Cat Rescue web site. Please also consider donating while you’re there – the global pandemic is making it harder for their awesome staff and volunteers to do what they do, and kitten season is upon us. If you appreciate the BI Polar blog and its companion YouTube channel, there’s no better way to say thank you than to donate to Meow. Even a small donation will help.


[1] I hope you saw that one coming

[2] This footage of Tiny attacking my head didn’t fit into the Power Kittens video, but I shared it on Twitter because it was just too cute to not share.

Don’t tell me it’s easy

“This will be really easy for you to do.”

“This is super simple.”

“This shouldn’t take you long at all.”

bulb-1994881_640

Every time you say this to someone, you’re diminishing them.

Every time you tell someone that completing a task or solving a problem will be easy, you’re telling them that you don’t know them and that you don’t care to know.

You’re saying loud and clear “you’re just like me, and if you’re not just like me then tough luck.”

Don’t do this.

Instead, take the time to realize that your experience and perspective may not be universal. Other people may have different perspectives, different strengths and weaknesses, different skills and different knowledge.

Someone with dyslexia may struggle with some common tasks that involve consuming or processing written information, despite reading every day.

Someone on the autism spectrum may struggle with tasks that involve social interaction, understanding the motivations and goals of others, or understanding how their actions will be perceived by others, despite interacting with people every day.

Someone who is short may struggle reaching items on high shelves, despite taking things off and putting things back onto shelves every day.[1]

Despite any hidden difficulties, these struggling people may still show every sign of effortless success. The things that are constantly difficult for them may be part of their day-to-day work. The tasks that challenge and batter and exhaust them may be part of the successes that define their public persona and reputation. But this doesn’t make the struggle any less real. This doesn’t make it easy.

When someone completes a task that would be easy for you, it could be because it is also easy for them. But it could also be because they spent extra hours or days preparing, or that they have special tools and practices that enable their success.

Speaking of tools and practices, let’s get back to those short people. I’m choosing this example because it’s a simple one that everyone will be able to relate to, or at least to understand. Mental and emotional challenges are no less real than physical ones, but they’re often more difficult to communicate and appreciate.

My wife is a foot (30.5 cm) shorter than I am. We both cook a lot, and we share kitchen tasks like cleaning and putting away dishes.

In our kitchen we we use the top of the cupboards as extra storage for large bowls and canisters and such. I can reach these items with ease, although I may need to stand on my toes to get a better grip. Most of the time I don’t even think about it. For me the top of the cupboards is just another handy shelf.

My wife cannot begin to reach the top of the cupboards – what is simple and effortless for me is difficult or impossible for her without additional help or tools. If she needs to get down something from the shelf she needs to walk into the dining room, get a chair, bring the chair into the kitchen, stand on the chair, get down the item she needs, step down from the chair, take the chair back into the dining room, and return to the kitchen to use whatever item she took down.

Think about that: what might take me half a second and a single motion might take her a minute or more, including some potentially dangerous steps along the way.

Of course, the solutions here are as trivial as the problem itself: I get down and put away the things my wife needs, and we are selective in what we store above the cupboards so this is an edge case rather than a common occurrence. But the only reasons we’re able to find this shared path to success is because we recognize the challenge and collaborate to apply our own individual strengths, while acknowledging and minimizing the impact of our individual weaknesses.

When someone’s work appears effortless, it could be because it comes naturally to them, and they may move on easily to their next chore, and the next. But it could also be that the effort is hidden, and that when the work is done they will need a few hours or a few days just to recover from the herculean exertion that you will never see.

You don’t know, and you probably never will.

So don’t tell me it’s easy, even if it is easy for you. Instead, work with me to discover how it can be easier for us, working together.


[1] Hold that thought. I’ll come back to this one.