Tag Archives: Power BI

Using Power BI To Track My Activities

As a MS MVP one of the things you have to do is keep track of all the “things” you do for community, whether it be volunteering, organizing, speaking, etc.  It can be a bit daunting trying to keep track of all of it.  But hey, I’m a Data Platform MVP, how hard can it be to keep track of data?!  Queue music from one of my favorite Blake Edwards movie .. Pink Panther.

At first I was just keeping track of everything in a text file via Notepad.  That got very unmanageable very quickly with all the different kinds of things I was doing.  I migrated all my data to a spreadsheet, because we all know that Excel is the most popular database in the world, right?

I knew that I had been busy in 2018, but I had no idea until I used Power BI to look at my data.  Yes, I was significantly busier in 2018 than I ever had been and 2019 is shaping up to be just the same if not busier.

Take a look at what I created.  It was a fun project to work on and allowed me to explore some things in Power BI that I don’t work with on a regular basis.  Let me know what you think.

How To Use Power BI Embedded For Your Customers

Recently I had a need to build a Power BI report for a client.  This client has a multi-tenant database and their own custom web app.  They want to show each client their data without showing any other clients’ data. They also don’t want to require their customers have a Power BI license.  Easy enough, just use Power BI Embedded with the App Owns Data model and set up Roles (row-level security) in the Power BI Desktop file, should take less than five minutes to set up.  Well, we all know it took longer than five minutes or this blog post wouldn’t exist.

There are some great tutorials from Microsoft out there, they even provide a sample app on Github that you can download if you are not a web developer (I am so NOT a web developer!) to help you get started.  There are some great blog posts about it too, one from Reza Rad and one from Kasper de Jonge.  So why I am writing yet another blog post about it?  Because the devil is in the details, and I completely missed the details which means someone else must have as well.

I don’t want to repeat what others have already written, so go ahead, go read their posts, it’s okay I’ll wait here.

Now that you familiar with Row Level Security in Power BI, how do you make it work when you want to pass in your customer’s identifier because your customers don’t have Power BI accounts?  It seems like the only way to make dynamic row level security is to use the Username() DAX function?  But wait, doesn’t that require the user to have a Power BI account?  Sigh, it seems we are going in circles.

The one thing these articles don’t talk about is that when you are using Power BI Embedded, you can pass in whatever you like for the EffectiveIdentity via the Power BI API and it will “overwrite” the Username() function.  What?!  That’s right, it will completely ignore the Username() function and use whatever you give it.  WooHoo!

Let’s see an example, so it’s crystal clear.  I have a small sample of data that has some sales.

Embedded-Sample-Relationships

Let’s say I want to limit the view of the data by Region.  Here are my available regions.

Embedded-Sample-Regions

I would need to create a role based on Region that looks like this

Embedded-Sample-ManageRoles

Notice that I am using the Username() Dax function for Region.  I know, I know, my customers aren’t Power BI users, so this makes no sense, why would I use this?  Like I said earlier, the value that you pass via the Web API as the EffectiveIdentity will overwrite the Username() function.

Now that I have my Roles set in my Power BI Desktop file, I can publish my file to the Power BI service.  All that’s left to do is to add members to my role.  Now when using Power BI Embedded in this scenario, you only need to add one account to the role that was just created.  Just keep in mind that this account must be have a Power BI Pro license.

Navigate to the Security for the dataset.

Embedded-Sample-Navigate-to-Security

Add your user to the role

Embedded-Sample-Add-Account-To-Role

It should look like this now

Embedded-Sample-Final-Role

Now, let’s look at the sample web app and how this will work.  In the homecontroller.cs there is a call to EmbedReport which contains the creation of the EffectiveIdentity, this is where the magic happens.

Embedded-Sample-Effective-Identity

Let’s take a look at how this works from the front end.  Here’s my report with no security.

Embedded-Sample-No-Security

Now I just click the checkbox View as a different user, enter my values then click Reload.

Embedded-Sample-Filtered-Result

Presto change-o, the data is filtered for just the Midwest Region.  But wait, Midwest isn’t a Power BI user, so how does that work?  Again, the EffectiveIdentity overwrites the Username() function and applies whatever you have entered.  It’s magic, I’m telling you.

One thing that tripped me up was that I wanted to filter by RegionID, not region name.  When we work with data, we want to be as precise as possible so we typically use a primary key (or other unique identifier) to filter our data.  However I have discovered through trial and error that you cannot pass a number, you have to pass character data in order for the filter to work.  This means for your data you may need to ensure that there is some unique character data field to identify your customers other than a numeric ID field.

Oh, and if you want to play around with this you can use the sample app from Github and this Power BI Desktop file.

I hope this was helpful.

Update: 2-Feb-2019

I was asked an interesting question via Twitter by Dave Ruijter about converting data to string so you could essentially still use the numeric ID field.

Instead I added a computed column to my dataset that uses the FORMAT() function to convert the existing numeric ID field to a string.

Embedded-Sample-New-Computed-Column

Then I based my role on that column, still using the Username() function.

Embedded-Sample-Using-RegionID-Role

Now I can filter based on the ID field, WooHoo!

Embedded-Sample-Final-Using-RegionID

Thanks Dave Ruijter for making me think about this a little bit more.

Speaking at SQL Saturday Atlanta (#652)

I am so excited and honored to have been selected to speak at SQL Saturday Atlanta (#652) this year.  This is huge event where I’ve been a volunteer and attendee in the past, but this will be my first time as a speaker.

I will be presenting my session What is Power BI?  I’ve presented this session a couple of times in the past but will be updating it to contain information regarding the changes that go into effect June 1, 2017.

If you are close to Atlanta on July 15, 2017, please stop by and say “Hello”, I’d love to see you.

#SQLSatATL

SQL Saturday Richmond (#610)

On Saturday, March 18, 2017, I spoke at my very first SQL Saturday.  I have been an attendee, involved in organizing and volunteered at many over the years, but this was the very first time I was a speaker.  My session, What is Power BI?

I have presented this session twice before, once to my local user group and once at the Triad Developers Conference, so I was fairly comfortable with my content.  Richmond had 245 attendees registered with 5 different session tracks.  I had about 25 people in my session.  Of those 25, I only saw one nod off, but it was the first session of the day (8:30am), so I’m going to chalk that one up to not enough caffeine prior to the session.  There were some great questions and I had several people approach after the session with more detailed questions and to tell me how much they enjoyed my session.  Some were so excited they would be able to take action when they got back to work on Monday based on my session.  It really doesn’t get any better than that.

With my session in the rear view, I was excited to attend other sessions.  I was able to make it into two other sessions, both of which were fabulous.  The organizing team for SQL Saturday Richmond did a fabulous job.  The event appeared to run like clockwork, I think maybe they’ve done this before Winking smile

One thing I really liked about this event is that they did not have a typical speaker dinner and gift, they did a speaker event.  It was at G-Force Karts which was so exciting for me because I’d never driven a go kart before.  I’ve always fancied myself a race car driver in another life (much to Martin’s dismay) so this was my opportunity to see if it was true.  All I can say is, “Yes, it’s true.”  I had so much fun.  I wish more SQL Saturday organizers would consider doing something like this.  A nice dinner is always appreciated and a gift is a thoughtful gesture, but the memories I made with my #SQLFamily at G-Force Karts are something I will NEVER forget.

I just want to thank the organizers of SQL Saturday Richmond, all the volunteers, sponsors and spouses who made this event happen.  It was truly amazing and something I will remember my entire life.  Well done.

SQLSatRichmondGoKart

Photo courtesy of Doug Purnell (Blog |Twitter)

What is Power BI?

One of the “benefits” of being a chapter leader is that sometimes it means doing a presentation yourself when you can’t get a speaker.  I fell into this exact scenario for February’s meeting of Triad SQL.  I was trying to figure out what to present when the planets aligned. After reading the #EntryLevel post in this month’s PASS Connector News and my boss asking me about Power BI.  He wanted to know more about it and if it was something we could use.

I decided to put a presentation together to answer those questions.  This post is basically the flattening out of my PowerPoint presentation.

The What/Who/Why/Flavors of Power BI

What is Power BI?

When I Googled (yes, I used that as a verb!) “What is Power BI”, this is what I got, “Power BI is an amazing business analytics service that enables anyone to visualize and analyze data.”  This sounds cool, but isn’t all that helpful.  After further research, I found this definition, courtesy of powerbi.microsoft.com

Power BI is a cloud-based business analytics service that enables anyone to visualize and analyze data with greater speed, efficiency, and understanding. It connects users to a broad range of data through easy-to-use dashboards, interactive reports, and compelling visualizations that bring data to life.

Why use Power BI?

There are lots of reasons to use Power BI, other than, it’s so cool.  For instance, Power BI makes it easy to see, in one glance, all the information needed to make decisions.  It also allows you to monitor the most important information about your business.  Power BI makes collaboration easy and when I say easy I mean EZ!  You can also create customized Dashboards tailored to those C-Suite folks or make a completely different dashboard based on the same data for those that actually do the work.

Who can use Power BI?

Anyone who has a work or school email address can use Power BI.  Sorry, no personal email addresses.  Also no government (.gov) or military addresses (.mil).

Flavors of Power BI

There are two flavors of Power BI, Free and Pro.  You can do everything with Pro that you can do with Free plus a few other things.  Here’s a little comparison of the two, there are more differences, but these are the big ones.

Free

Pro

Data refresh frequency: Daily

Data capacity Limit: 1GB/user

Streaming rate: 10K rows/hour

Data sources are limited to content packs for services and importing files

Data refresh frequency: Hourly

Data Capacity Limit: 10GB/user

Streaming rate: 10M rows/hour

Data Sources include free ones plus direct query dataset and on-premises data

Collaboration with content packs

As of January 21, 2016, the Pro flavor goes for $9.99 USD per month per user.

Also, there is the previous version/flavor of Power BI referred to as Power BI for Office 365, which will be deprecated on March 31, 2016, so I am not including this version/flavor in this post.

The How of Power BI

The building blocks of Power BI are Dashboards, Reports & Datasets.

Dashboards

Dashboards are made of Tiles that contain a single visualization created from the data of one or more underlying Datasets.  When I first read this all I heard was “blah blah blah Datasets”.  What this means is simply this, it’s a collection of reports that are all displayed together for a specific reason.  It could be that you want all your sales guys to see different views of how they are doing compared to budget/forecast or it could be that you want to give your C-Suite people a quick overview of how the company is doing as a whole.  You can tailor these dashboards to whatever suits your purpose.  Now the only reference to the limit on the number of dashboards I could find was on the Office 365 site and it was listed as 100 per user or group.  I’m thinking of the old adage “just because you can doesn’t mean you should” would apply here though.

Reports

A report is one or more pages of visualizations.  Reports can be created from scratch within Power BI or Power BI Desktop.  They are very easy to create, you simply click on the type of visualization you want to display then select the data to be used.  One caveat that I will mention here is be sure your data is formatted so that is can be more easily consumed by Power BI.  See this link for tips and tricks on how to build a “proper dataset” for Power BI.  Just as with Dashboards, you have a limit as to the maximum number of reports, which is the same as Dashboards, 100 per user or group.

Datasets

A Dataset is something that you import or connect to.  It contains the actual data you want to translate into visualizations.  Right now you are limited as to the types of files you can import in to Power BI to Excel, Comma Separated Values (.csv)  and Power BI Desktop files (.pbix).  As far as connecting to data sources you can choose from many of the content packs that are available via the Power BI site like Google Analytics, Bing, Mail Chimp, Sales Force and GitHub, just to name a few or you can connect to a database.  As with anything that sounds too good to be true, you are limited to the databases you can connect to.  Right the now current list is limited as well, to Azure SQL Database, Azure SQL Data Warehouse and SQL Server Analysis Services (tabular model only).  There is a 250MB limit to the size of the dataset that you can import in to Power BI and a limit of 100 Datasets.

References

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-get-started/

https://powerbi.microsoft.com/en-us/documentation/powerbi-videos/

https://powerbi.microsoft.com/en-us/documentation/powerbi-webinars/

https://technet.microsoft.com/library/mt282164.aspx

That’s it.  I hope this post provided a little bit of insight into Power BI and whether it’s something that can be useful to you and/or your company.  Check out the following links if you want a deeper dive into Power BI.

Power BI Blog

Melissa Coates Blog

Reza Rad Blog

Chris Webb Blog