#SQLChefs: Power BI Datasets, Reports and Dashboards

October 2, 2018VisualizationsEugene Meidinger

This week we’ve got another episode of SQLChefs with Bert Wagner, where we talk about the different between datasets, reports and dashboards in Power BI.

What are datasets?

A Power BI Dataset is a series of Power Query queries that have been shaped in a DAX model. Each dataset can combine different files, database tables and online services all into one tabular model. In our cookie analogy, these are all different “ingredients”.

Unlike SSRS, a dataset in Power BI does not represent a single table or query of data. A dataset should be considered more like a “flavor” of data used to accomplish a specific type of reporting: financial, operational, HR, etc. So in our analogy, the dataset is the “raw dough”.

So in Power Query, you are going to have a set of queries which each combine a data source with a usually linear set of transformations.

Then, in DAX, you are going to take each of those outputs and combine them into a model. This consists of defining relationships between the outputted tables and adding business logic via calculated columns and measures.

For more on the difference between Power Query and DAX, see our previous episode of SQLChefs.

What are reports?

A power BI report is a series of visualizations, filters and static elements on a canvas. Power BI reports are saved as a single PBIX file and connect to a single dataset. Remember, a Power BI dataset can have many data sources.

(Demo file courtesy of Microsoft, MIT License)

Each report can have multiple sheets, just like an Excel workbook. In our analogy, this is us placing our “cookies” on multiple “cookie sheets” making one big batch, all of the same “flavor”.

One report per dataset

A quick aside to something that used to confuse me. In most cases, a report and a dataset are going to have a one to one relationship. A dataset can have one report and a report can have one data set.

Recently this has changed, however. A while back, they added the ability to use an existing dataset as a data source for a report. and at Ignite they announced the ability to share datasets outside of the app workspace they were made in.

That being said, while you are still learning Power BI, it’s easier to remember that in many cases, your dataset and your report are going to have a one-to-one relationship and be tightly linked.

What are dashboards?

In Power BI, dashboards are a way of pulling together visualizations from various reports. When you think dashboard, you are probably thinking something like Microsoft’s definition: “A Power BI dashboard is a single page, often called a canvas, that uses visualizations to tell a story. Because it is limited to one page, a well-designed dashboard contains only the most-important elements of that story.”

However, if you look at the report example above, it probably fits that definition. It is not a Power BI Dashboard. In Power BI, a dashboard is tool for pinning visuals from different reports and other sources of data.

In my opinion, a Power BI Dashboard is as much a tool for organization and navigation, as it is for actual reporting. I think that’s the real value add with Power BI dashboards.

Power BI Error: R cannot be added to gateway

September 25, 2018Power BI ServiceEugene Meidinger

If you are using R as a data source, you may want to be able to refresh the data or run that R script on a schedule. However, if you try to configure a On-premises Data Gateway to refresh the data source, you will see this error.

Extension{"extensionDataSourceKind":"R","extensionDataSourcePath":"R"} Cannot be added to gateway

Back in August 2016, Microsoft added support for refreshing R scripts but only for the personal data gateway. So, in order to schedule a refresh where you are using R, you need to install and configure an on-premises data gateway in personal mode. The Microsoft documentation for r query steps reflects this limitation as well.

TSQL Tuesday: Who is on my server?

September 12, 2018SQL ServerEugene Meidinger

tsqltuesday

Here is my entry for this month’s T-SQL Tuesday.

I once had to some auditing for a customer and it was a complicated, multi-stage process. We had to be able to demonstrate who had admin access and what kind of activity was going on, on the server. But before we could do any of that, we first had to identify who was actually logging on.

Triggers to the rescue

So what are the different options for telling who is logging on to a a SQL server? 5 options come to mind:

Configure login auditing.
Login Trace
Login Extended Event Session
SQL Audit
Server Trigger

So going through each one of them:

Configuring login auditing really isn’t a good solution. What you are doing is changing the base settings to log successful logins in addition to failed logins. The problem is that these events are written to the SQL Server event log, which isn’t convenient to parse.

Well what about using a trace? Well I’ve always been told that traces are expensive in terms of performance so I shied away from using one of those. In retrospect, I doubt it would have been too expensive since it’s only tracing logins. If anyone knows, let me know!

The next option is to use Extended Events, which often have better performance. Unfortunately, this server was SQL Server 2008 R2 and there was no GUI support for extended events. So that wasn’t ideal.

What about SQL Audit? Underneath the hood, SQL Audit is just Extended Events. That being said, there is at least some GUI component to it. For 2008R2, it required Extended Edition. while that wasn’t an issue for us, it seemed like overkill.

So what’s the last option? Creating a server level trigger. This was simple to implement and easy to dump the data into a SQL table for reporting purposes.

Proceed with caution

So, what’s the downside. Wellllllll. What happens if you have an error in your code? If you hit an error, then you can’t login. At all. Anyone.

There are ways to resolve this issue, but it requires shutting down the SQL Server and taking an outage to fix it. Suffice it to say, I spent a looooot of time testing before I pushed this out to production.

Overall, triggers provided a simple solution to a simple problem. But the solution required a good dose of caution.

Doing the bare minimum to stop toxic behavior

August 12, 2018Mental Health & Personal GrowthEugene Meidinger

I recently saw on Twitter some abusive behavior, and I know that Twitter and trolls go together like peanut butter and jelly, but for whatever reason I’m angry. Maybe because I know the person who was abused, maybe because the abuser is a part of our community. I don’t know. But I’m feeling angry.

If you have to ask “Why are there so many tech events only for girls?” you are either:
1) Trolling
2) Severely lacking empathy and imagination
3) or both.

— Eugene M (@SQLGene) August 10, 2018

I want to do something, but there’s not much I can really do at this point. The abuser has deleted his account and I’ve already said my piece online. So, I thought I’d write a blog post about doing the bare minimum. For you, this post is likely to be utterly banal (“Well duh”), somehow offensive (“You are being too politically correct”), or both (“That’s your minimum? You can do better Eugene.”).

Well, damn it all and fire the cannons. I’m angryblogging.

Step 1. Be a safe person

The person you have the most control over is yourself. The person you have the least control over are abusive people, especially narcissists, psychopaths and anyone else who doesn’t feel a healthy sense of shame. So the most effective thing you can do is be a safer person.

Safe people apologize. Learn to apologize, practice apologizing, and understand that apologies are more than saying I’m sorry. In the book Apology Languages, the author breaks and apology down into 5 different parts.

1. Expressing regret. “I’m sorry.”
2. Accepting responsibility. “What I did was wrong.”
3. Making restitution. “How can I make amends?”
4. Genuinely repenting. “I won’t let this happen again.”
5. Requesting forgiveness. “Will you forgive me?”

We all know how to say I’m sorry. It’s a cliché of mothers forcing small children to apologize, but often what’s more effective is putting your money where your mouth is. It’s taken a lot of practice for me to be able to say, “What I did was wrong, full stop.”, without needing to explain my motivation. Being safe takes practice.

Safe people listen, without always trying to solve the problem. Sometimes the other person just needs to be heard. Sometimes by trying to fix a problem you can accidently take away someone’s agency. I often ask my wife, “Do you want to vent or do you want advice?”. Her response is 50/50 each way.

Sometimes it’s not about the nail:

Safe people empathize and validate. I was in a really bad relationship once, and one of the things I understood later was the other person never said, “I can see why you’d feel that way.” They never said, “That’s understandable.” They never said, “I would feel the same way, in your shoes.” Validate the person’s feelings.

Toxic people are often unwilling or unable to empathize. For narcissists in particular, it presents a threat to their sense of self. Even worse, many do something called gaslighting, where you make the person question their own senses. Validation is a antidote to gaslighting. Let people know they aren’t crazy.

Step 2. Be aware of different experiences

I’ve written bad emails, angry emails. I’ve gotten in a feud with a co-worker. I’ve been stressed and blown up on people. And never once has someone told me I’m “too emotional”.

I’ve presented dozens of times and never once has some given the feedback that I should “smile more” or “present in lingerie”.

I’ve walked down many streets and I’ve never been catcalled or sexually harassed. I’ve never had to worry if someone was following me around. I’ve never had to run up to someone and say “help me, I’m being harassed.”

And all of this presents a challenge for me, because it makes it harder for me to empathize with women and their lived experiences. Because some experiences are so incongruous with my entire life that there is this cognitive dissonance. This dissonance can be quite uncomfortable.

It’s means that by default, certain experiences feel less credible because I’m never lived them. I don’t want to believe the crap people endure. Some of it seems too horrible to be true.

So that means I have work at it. I have to listen to the stories of other people and have a willingness to feel uncomfortable. The default is me minimizing and invalidating the experiences of others because they don’t match up with mine. So I have to do better than the default.

Step 3. Be alert

An embarrassing story: in the last year, I had the opportunity to stop some harassment in person and I didn’t. It wasn’t because I was scared or unwilling. No, it’s because I wasn’t paying attention.

I take martial arts and if someone was beating another person, I’d like to think I’d intervene. I haven’t been tested on that and hopefully never will, but I’m pretty confidant I’d jump in. But harassment can be subtle, almost invisible. And so I didn’t jump in.

In this case, nothing in the conversation was harassing. Nothing offensive was said. But something felt off. The non-verbals were screaming at me. And I didn’t hear them because I wasn’t listening.

You know when you cook some food and it’s 2 days expired and it smells off? Not moldy or anything, it looks perfectly fine, but you eat it and feel sick an hour later? Harassment and abuse can be like that. Nothing blatantly wrong but in your gut you know that something isn’t right.

Being able to stop harassment requires being alert and being aware. If you are someone like me who doesn’t worry about getting harassed personally, doesn’t get harassed regularly, this can take work. I never want to miss the signs ever again.

Step 4. Speak up. Step in. Intervene.

I hate conflict. I am a people pleaser. I have poor boundaries. So the idea of stepping in the middle of something gives me shivers.

I don’t like getting involved in Twitter fights, I don’t think they accomplish much. I don’t like the mob mentality on Twitter and online. When I think about the dog-pile culture on Twitter, I worry someday I’m going to say something tone deaf and lose my job over it. I say stupid things a lot.

But you know what? Say something. Do something. Step in.

I’m not encouraging people to put themselves in danger or incur abuse themselves. But for many of us that’s not a serious risk. I’m 6’ 2’’ and practice self-defense. I can afford to intervene in a conversation. My safety is not at risk.

Stepping in might mean just being physically present and making knowing eye contact. It might involve saying “Sir, that behavior is inappropriate.” It might involve entering the conversation and asking pointed questions that belies the true intentions of the abuser.

Online it might mean calling out bad behavior. Saying, “This is unacceptable.” or “This is harassment.” It doesn’t require being some internet crusader or dog-piling. You have a line personally, and you know in your gut if something crosses that line. You know in your gut something is wrong. If something is wrong, then say something.

I’ll say it again. I hate conflict. I’m a people pleaser. I have poor boundaries. But I’m working on speaking up more when I see something that I feel is harassment or abuse.

And remember, calling out bad behavior is not just about shaming the abuser. They aren’t likely to listen to you anyway. It’s about letting the victim know that they are seen, they are heard and they are not crazy. It’s about setting a standard for everyone else. There is a saying that “locks keep honest people honest”. Healthy accountability keeps everyone honest.

On doing more than the bare minimum

I’m not saying that we should all aim for just minimum. If your bare minimum is more than this, awesome. I’m not trying to encourage doing less. But for people like myself, the minimum is not the default. The minimum is a destination, not the starting point. Let’s change that.

What I’m trying to say is there are small, simple things we can all do without making a big leap out of our comfort zone. And that minimum bar is getting higher every year and we should all be aware of that. Times, they are a changin’.

Angryblogging, out.

SQL Saturday Philadelphia: Power BI Precon

April 17, 2018Deployment & AdministrationEugene Meidinger

This Friday at SQL Saturday Philadelphia, I will be presenting a precon on implementing Power BI. I’m excited about it because it’s the kind of presentation I would have attended two years ago.

One of the big challenges of learning Power BI is that everyone wants to sell the sizzle (great visuals) and not the steak (Infrastructure). And because of how it is to get started with Power BI, you can get a nice looking dashboard together in a few hours. But the hard part is answering “What’s next?”.

In my precon I break it up into two pieces Data Wrangling and Administration. Power BI works great when you can just drag and drop, but most of the time the data we have to work with is just plain ugly. Power BI gives you two languages for cleanup and modeling and both require a new mindset to understand.

Once you have your data cleaned up, you have to deploy and administer the thing. And boy are there a lot of ways to deploy it. And there are gotchas too. Like the fact that you need a pro license to deploy that content, even if you have Power BI Premium or Power BI On-Prem. Nobody get’s excited about data governance, but if you want a production solution you’ll need to learn the ins and outs.

If you are interested, there is still time to sign up.

M vs DAX: Chopping Broccoli vs Planning a Menu

April 3, 2018VisualizationsEugene Meidinger

Last week, I had the pleasure of recording some video with Bert Wagner about Power BI. In the video, I got to use one of my favorite analogies for M versus DAX: Are you chopping broccoli or planning a menu?

One of the challenges with learning Power BI, is that you have to learn not 1, but 2 new data manipulations languages. And it’s not always clear what they are good for, especially if you come from the SQL world.

Is M a general purpose knife, or one of those weird egg slicers?

Head Chefs versus Sous Chefs

I have never worked in the restaurant business, but I’m going to make some gross generalizations anyway.

Sous chefs, as far as I can tell, do a lot of the prep work. They are cutting vegetables, cleaning food, making sauces, etc. While this is all important work, much of it doesn’t inform the final outcome. If you are making beef teriyaki or if you are making broccoli salad, you still need to chop the broccoli.

The head chef however, gets paid for her brains just as much as her hands. The head chef is figuring out the menu and how to combine all of the ingredients. She is involved very heavily with what the final result is going to be. A head chef has to think of the broader goals and strategy of the restaurant, not just how to get the immediate task done.

M is the Sous Chef; DAX is the Head Chef

Again this is all a gross generalization, but in the restaurant called Casa De Meidinger this is actually the case! I do a lot of the grunt work when we cook a meal. My wife says, “zest this lemon” and I mindlessly do it. I could probably be replaced with a robot some day, and that would be fine by me.

Annie, however, actually enjoys planning a meal, deciding what to cook, and thinking about how to make the final product. To me, cooking is just a necessary evil for eating. I don’t necessarily get any joy from the process itself.

Working with M

I like to think of M as this sous chef. It does all the grunt work that we’l like to automate. Let’s say that my boss asks for a utilization report for all of the technicians. What steps am I doing to do in M?

Extract the data from the line of business system
Remove extraneous columns
Rename columns
Enrich the services table with a Billable / NonBillable column
Generate a date table

This is all important work, but I would have to do the same work for a variety of reports. Many of the steps tell me nothing about the final product. I would generate a date table for most of my reports, for example.

Working with DAX

Now, if I’m working DAX, what am I going to do?

Ask what the heck “utilization” really means

This was a real-life example that happened to me. What is utilization as a key metric? Well it turns out it depends what you are trying to report on. A simple definition is usage divided by availability. If a technician billed 20 hours and clocked in 40, his utilization would be 50%. Or so you would think.

How do we handle internal projects? Let’s say we have a technician who billed 2 hours to a customer, but spent 38 hours on an internal database migrations. What was his utilization?Well, if we are looking for billable utilization, it’s 5%. If we are looking for total utilization, it is 100%. These are questions that you are going to encapsulate in your DAX formulas.

The whole idea of a BI semantic layer is to hide away the meaning from the end users. When someone orders a cobb salad, they don’t want to have to articulate the ingredient list. They just want a darn salad.

Are you paid for your hands or your brain?

In the SQL Data Partners podcast, episode 114, there was a question: what’s the difference between a contractor and a consultant. One of the answers was this: a contractor is a set of hands, and a consultant is a set of brains.

I think this answer relates to M versus DAX. M is an automated set of hands, able to do work you’d normally do by hand in Excel. DAX let’s you take your domain knowledge and encode it into a data model. It’s an externalized representation for your brain.

And if you think about it, which do you want to be paid for? Do you want to get paid to unpivot data by hand every week? Or do you want to get paid for thinking, for understanding the business and for working at a higher level.

M allows you to automate the first step, so you can do more of the latter with DAX.

Are local credentials or passwords stored in the Power BI Desktop file?

March 22, 2018Deployment & AdministrationEugene Meidinger

When I presented on Power BI at Cleveland, I wrote up a blog post with all the questions I didn’t have an immediate answer to. I presented last week at Cincinatti and wanted to do the same thing.

This time there were some more difficult questions so I’m going to have to split it up into multiple blog posts.

Are local credentials stored in the Power BI Desktop file?

With SSIS, you have to be careful to export the SSIS files without any sensitive information included. But what about Power BI? If you save the .PBIX files on OneDrive, can you be exposing yourself to a security risk?

Looking at things, it looks like credentials for data sources are stored globally, so one wouldn’t expect them to be in the .pbix files.

So, first I turned the PBIX file into a zip file and poked around. I didn’t see anything suspicious.

Next, I ran

If we open user.zip we find a folder called Credentials, with a single encrypted file inside. I’m willing to bet this is where the passwords are being stored.

Come see me present!

If you are interested in attending a future precon, I’ll be presenting at the following locations for 2018:

TSQL Tuesday #100: Industry changes and Meidinger’s Law

March 14, 2018SQL ServerEugene Meidinger

Meidinger’s law

To celebrate the 100th TSQL Tuesday, this month’s

Maybe a bit

And what about

We are all imposters

If you are going to change jobs every two years, then that’s how long you have to learn before it’s on to the next thing.

I’ve had my freakout about the data platform constantly broadening. Mindy Curnutt has a great podcast episode about imposter syndrome. We are all worried about the pace of change.

And I think Meidinger’s law is our saving grace in some sort of way. Things keep changing, we have to keep learning. But how much we actually have to learn is constrained by that 22 year old drinking red-bull with no family obligations.

Throwing Darts

Oh yeah, we were supposed to be predicting the future. Well I think the fact that we are all going to be replaced by that 22 year old some day gives us a hint at the future.

I think more and more things are going to be abstracted away. We’ve seen it with virtualization. We’ve seen it with the cloud. These abstractions mean the new guy can learn new, more important things. He doesn’t have to be intimately familiar with RAID 5. He doesn’t have to have the OSI model memorized.

I think we are seeing it now with data science and machine learning. So much of those areas require a Phd and years and years of study. But things like Azure machine learning and Azure cognitive services are going to get easier and easier. So easy that even the new guy can do it.

T-SQL Tuesday #99: I’m secretly a LARPer

February 13, 2018SQL ServerEugene Meidinger

For the 99th T-SQL Tuesday, I’m going to talk about something I do that’s completely unrelated to work. Something I’ve never told any of you about.

What I don’t want you to know

There are 3 things in my life that could cost me a job, things that I fear an employer ever finding out about. I suffer from 3 big problems in life: depression, diabetes, and LARP. That’s right, I’m… a LARPer.

I’m not kidding about the fear. People get weirded out easily, and it’s easier to say no to an applicant than to say yes. I really worry about someone finding this post and turning me down.

I once heard a story about a potential staffed employee. Everything was looking good until the client looked up the employee’s Facebook and found out that they were a furry. After that point, the client was no longer interested in staffing that employee.

It’s unfortunate that harmless personal hobbies present such a risk, but here we are.

How it started

When I first heard about it, I was skeptical. In my head, all I knew was the stereotype of the uber-nerd shouting MAGIC MISSILE!
missile GIF

My then fiancée and a couple friends of ours had started going, and she wanted me to come along. I told her quite clearly, “Listen, LARP is so dorky that if I go with YOU, you’ll end up breaking up with ME. No way.”

Well it turned out that my excuse had a limited shelf-life. A couple days after we are married, my new wife reminds me that I’m very much against us getting divorced. So now I have no excuse not to go, because we aren’t breaking up any time soon. We go to LARP a week after we are married.

What is it?

I could tell you it’s Dungeons and Dragons in the woods, but that would be doing it a disservice. Instead, let me tell you a couple stories from Memorial Day weekend, 2016. It was 8 months later, and the start of our honeymoon.

That time I tried to commandeer a flying ship

So my character is a diplomat / minstrel / healer. Which means, if possible, I’d prefer to solve things with words instead of violence. And one of my abilities is to use my charisma to persuade people, convince them we are friends, etc.

So, me and my group are informed by a Non-Player Character that some bandits have taken over his grounded airship, and he needs us to get it back.

We approach the ship, and as we get on the ramp the bandits warn us to stay back. I tell my group, “Let me handle this.”

So I walk up the ramp, alone and in mild danger.
I tell the bandits, “Hey, I’m here to help. That guy you stole this ship from, he’s trying to find adventurers to kill you.”
“We didn’t steal it!”, they reply.
“Fine, the former owner is looking for you.”, I say as I keep walking up the ramp.
”Stay back!” they shout. Then I throw two spell packets at them. Spell packets are basically birdseed wrapped in cloth. This allows for ranged attacks without hurting us or the wild life.

Now the two bandits at the front are charmed and best friends with me. They are trying to convince the rest of the bandits that I’m legit. That they know me from…somewhere?

“Now, here’s how I’m going to help you.” I say as I lean forward, at the top of the ramp. As my foot hits the ground, I hear a loud squeak.

Crap.

“Hoooooold!”, we shout. We have to pause the game because something important just happened. I stepped on a trap. An obvious trap. A trap so obvious that the person setting them up expected everyone to see it. Nope, not me. I’m too busy with my silver tongue.

“10 fire damage in a 10 foot radius. That counts as an aggressive action and breaks charisma.”

Crap. I guess we aren’t BFFs anymore.

I try to re-charisma the one bandit and convince her that she set off the trap. She apologizes profusely, but the rest of the bandits are not convinced and a melee ensues.

Well, at least I tried. Now we have to kill them all. This is what happens when you travel with murderhobos.

Thankfully, all of the dead bandits are very forgiving.

That time my hat was a cauldron, and it saved the swamp

“Keshan, do you know how to featherfall?” I’m asked.
“Sure, I just learned it.” I reply.
“Okay, we need you to climb this tree [metaphorically] with these magic flowers, and then jump out of the tree.”, Angus says.
“No problem.”

I jump and do a twirl and pretend I’m falling out of a tree. Now they can make some magic potion that’s going to cure the swamp of some ancient curse.

Only there is a problem. “Where is the cauldron?” says the game marshal, “You can’t make a potion without a cauldron.”

Mind you, we are preparing for a town fight, a battle between all of the players and all of the NPCs. As far as our characters are concerned, we could be attacked at any moment.

So I shout, “There’s no time! Zanrick, turn my hat into a cauldron.” Zanrick here is a gnome, and they are a crafty folk. And they have an ability called improvise, which means they can make something into something else if they B.S. well enough.

More shouts go out, “We need an amour patch!”. We end up with 3 of them. Technically they are mason jar lids, but in game they are used to repair your armor after a fight. And suddenly, my hat is a cauldron brewing a magic potion to save the swamp. Pretty cool, right?

The best part of all of it? The game marshal was so delighted by my silliness, that my hat now counts as plate armor in-game. She was just expecting one of us to go to the kitchen and grab a pot. Well, that simply wouldn’t do.

To hell with what everyone thinks

There was a recent conversation on Twitter about how when you are 40, you learn to worry less about what other people think, and you learn to do what you love. I’m not quite 30, and I worry what people will think of me, but damnit I love LARP.

I mentioned depression at the beginning of this post. I usually don’t like to talk about it, because I don’t want to seem like I’m fishing for sympathy. But it’s worth mentioning that one of the causes of depression is ruminating or obsessing over negative thoughts.

In my life there have been only 4 things that have truly gotten me out of my own head and allowed me to stop worrying about things:

Video game programming competitions
Board games
First-person shooters
LARP

That’s it. Those are the only things that allow me to escape for a little while.

But even more than that, LARP is an excuse to turn off my cell phone. It’s an excuse to go outside and walk around. It’s a situation where I’m forced to be hyper-social and meet new friends. It’s this beautiful mathematical dual to everything I do in IT.

I do it to get away. I do it get a break. I do it to make friends.

As I get older, all of those things get harder and harder to do. I may take my work home with me, but there’s at least one place in this world where my work can’t follow me.

One last thing

One last thing, that I should probably mention. So, I might have published a book, in-game. Because why the hell not.

Deploying Power BI: Scaling from 5 to 5000

February 9, 2018Deployment & AdministrationEugene Meidinger

Today, I had the honor of speaking at the here are the slides.

I want to be clear that this talk isn’t so much about scalability in the performance sense, but more in the IT Governance sense.

Why deployment can be a challenge

Deployments are pretty boring, just like most administration. You just hit publish, right? Figuring out the right solution for you is actually pretty difficult. So why is that?

Too many options

There are at least 9 different ways that you can deploy your Power BI reports:

Sharing Dashboards / Reports
Sharing Workspaces
Organizational content Packs
“Apps”
SharePoint Embedding
Power BI Premium
Publish to Web
Power BI Report Server
Power BI Embedded

So you have all of these different options to choose from and at time it can be confusing. Which method makes sense for your organization?

It keeps changing

Even worse, Power BI is rapidly being iterated on. This is great for users, but a challenge for people trying to keep up with the technology. One year ago the following deployment options modes didn’t exist.

Sharing individual reports (Jan 2018)
“Apps” (May 2017)
SharePoint Embedding (Feb 2017)
Power BI Premium (May 2017)
Power BI Report Server (June 2017)
Power BI Embedded V2 (May 2017)

It can be a real challenge to keep up. I think that a lot of the dust has settled when it comes to deployment options. I don’t see them adding a lot of new methods. But I expect there to be many small tweaks as time goes on. In fact I had to make two changes to my slides this morning because they announced changes yesterday!

Organizing by scale

So, how can we get our arms around all of these different options. How can we organize it mentally?

One way of approaching this is who do you want to share with? Do you need to reach 5 users, 50 users, 500 users, or 5000 users?

This is the framework that I use in the presentation and the rest of the blog post.

Before we jump into the different ways to deploy your reports, we need to talk briefly about the dirty little secret of self-service BI:

Self-service is code for “undermining IT authority”

Any time you make it easier for Chris in accounting to create and share reports without having to talk to Susan in IT, you chip away bit-by-bit at IT authority This isn’t always a bad thing. Sometimes the process governing your IT strategy is a bureaucracy.

The reason I bring it up is that you’ll find that the more users we need to reach, the more of a centralized structure we need to support it. Dashboard sharing is great for 5 users but is horrific for 5000 users. It’s just like building a tower or skyscraper. The requirements for a 10 foot building are drastically different than a 100 foot building.

Sharing with your Team

So let’s say you want to share with your team, just a handful of people. Well the good news is it’s pretty easy. You hit publish and you click share.

First you have to publish

Whenever you make a report in Power BI Desktop you have to hit the Publish button to push it out to the Power BI Service, a.k.a PowerBI.com.

Whenever you do that, you are going to be asked what workspace you want to push it to.

A workspace is basically a container for all of your report artifacts: dashboards, reports and data sets.

Dashboard sharing

The quickest and easiest way to deploy reports is direct sharing. Once you’ve published a report, you can create a dashboard by pinning visualizations to it.

One it’s created, then you can hit the share button:

From that point you will be asked who you want to add. When you add users to a dashboard you can either given them read-only permissions or the ability to read and share.

Report sharing

Last month, they added the ability to share individual reports as well. The overall process is the same. Upload the report, hit share. The difference is now we can finally do that without creating a dashboard.

Workspace Sharing

So let’s say that you actually want to collaborate with other people on reports, or at the very least keep them all organized in a central location. The quickest and easiest way to do that is to share the whole workspace.

When you share a workspace you can make people either admins or members. You can also decide if you want those members to be read only, or able to edit the contents of that workspace.

This is ideal for collaboration or sharing with small groups. But if you have to support 100 users, it can start to break down, especially if all the members have edit privileges. Let’s take a look at the next level of scale.

Sharing with Power BI Users

Okay, you’ve been able to share with a handful of users. But now, you need to deploy “production” reports. This means having some sort of QA processes and a way to centrally manage things. We need to step up our game.

Organizational content packs

Organizational content packs were the original way of wrapping Power BI content in a nice bow and sharing it with the whole organization. Unfortunately they are now deprecated and have been mostly replaced by apps. Mostly.

The one use case for content packs is for user customizations. Whenever you share an app, the user gets the latest version of that app. With content packs, a user can download the pack and make personalization’s to their copy.

Business Intelligist has a good post breaking down some of the differences.

Power BI Apps

Power BI Apps are the definitive way to share content within your organization. A Power BI app is essentially a shared workspace with a publish button and some nice wrapping around it.

Apps provide a number of benefits:

QA and staging. Review your reports before deploying.
Selective staging. Work on reports without having to publish them.
Professional wrapping. Add a logo, description and landing page to your content.
Canonical Versioning. By using vehicles like Apps, you can have company endorsed reports.

To Share an App, you hit publish and are given a URL to distribute. Users can also search for your app. In the future, you will be able to push content out to your users directly.

Sharing with your whole organization

So let’s say that you want to expand your reach and share reports with everyone in the entire organization. In that case you will either need to a) change your licensing approach, b)move away from powerbi.com, or c) both.

Power BI Premium

Power BI Premium is ideal if you have lots of users and lots of money. With Premium, instead of licensing users you license capacity. You are essentially paying for the VMs behind power bi service instead of the individual users viewing the content.

Power BI Premium is a licensing strategy, not a deployment strategy. The deployment is secondary.

Remember what I said about lots of money? The full Power BI Premium SKUs start at $5000 per month. If you are paying $10 per user per month, the break-even starts around 500 users. That’s a lot of users.

From a user experience perspective standpoint, absolutely nothing changes with changes. you mark a workspace as premium, and now it’s isolated and free to users.

Image source: Microsoft

Power BI Premium also offers scalability benefits. Larger data sets, better performance, more frequent refreshes. If you are bumping up against the limits of the Power BI Service, Power BI Premium might make sense for you. The whitepaper goes into much more detail.

SharePoint Online Embedding

If your organization has made heavy investments in SharePoint, it may make sense to use SharePoint as the front-end instead of powerbi.com

To deploy a report to SharePoint Online, crate a new page and then add the Power BI Web Part.

Image Source: Microsoft

Once you add the web part, you have to specify the URL of your report and you are done.

From a licensing perspective, users with need to have Power BI Pro, or you can use the EM SKUs of Power BI Premium. The EM levels with cost you $625-$2495 per month.

Power BI Report Server

EDIT: This section is incorrect and will be updated. Please see David’s comment at the bottom.

For a long time, the #1 requested feature was Power BI on-premises. Power BI Report Server is basically SSRS with support for rendering Power BI reports. The deployment story is very similar to SSRS reports. Users would go to the web portal and open up reports from there.

Unless you have data sovereignty regulations or highly confidential data, you shouldn’t use Power BI Report Server. The first reason is that it is very expensive. There are two ways to get Power BI Report Server:

Licensing is included with Power BI Premium
SQL Server Enterprise Edition + Software Assurance

The other issue is that Power BI Report Server is that it is still limited:

No support for Dashboards
No support for Scheduled Refresh
No Q/A or Cortana support

I expect that they are going to continue to improve upon PBI Report Server, but as with an on-prem solution, it’s always going to be lagging behind the SaaS model.

Sharing with everyone

So let’s say that you want to go a little bit broader, what if you want to share with people outside of your organization. What if you want to share with everyone?

Publish to Web

The simplest and easiest way to share with people is to use Publish to Web. When you publish a report you will be given a public URL and an iframe for embedding.

If you use publish to web, it’s completely free to anyone to view. However, your data is publicly available. Anyone with access to the URL can view the underlying data. If this sounds bad, be aware that you can disable publish to web at the tenant level or for specific security groups.

Power BI Embedded

To use Power BI Embedded, you are going to need a web developer. There are no two ways about it. And web developers are expeeeensive.

Power BI Embedded allows you to use Javascript to control and embed Power BI reports in your web application. One of the consequences of using Power BI Embedded id you are going to have to roll your own security. You aren’t going to be giving users access like normal.

The other thing to know about Power BI embedded is that it depends on Power BI Premium to back it. So you are paying for capacity, not users. In this case you are using the A SKUs, which cost $725-$23,000 per month. That will get you 300-9600 render per hour.

If you want to start playing around with it, there are samples available.

External Sharing

While this isn’t a way to share with thousands of external users, it bears mentioning that you can share with external users. This is ideal if you have a handful of external clients. The overall user experience is largely the same. The big difference is that their account can live in a different Azure Active Directory tenant.

I won’t go into detail about it here, but check this link out if you want to learn more. There is also a whitepaper (AAD B2B) that goes into even more detail.

What now?

If you head isn’t spinning from all the information, definitely check out the deployment whitepaper. Chris Webb and Melissa Coates go into excruciating detail into all of your options and all the different details to consider.