Month: October 2018

2 things you need to know about Power BI Security

/ Mitchell Pearson / Leave a comment

In this blog post, I want to quickly discuss 2 things you need to know about Power BI Security as it relates to the Power BI Service. Many of the clients that I visit and perform training for are just getting into an implementation of Power BI and are not aware of these two items. I will probably come back and update this list as there are a couple more items I want to add, but those will take slightly more explanation! Smile

Publish to Web should be disabled

The publish to web feature provides an Iframe that allows a user to take a report and then embed that anywhere an Iframe can be accepted. The problem with this is that now the report is 100% security free and anyone who has access to the report has access to everything in it.

If you choose not to disable this feature it should, at the very least, be limited to specific people within the organization who have received proper training. To disable or manage this feature you must perform the following steps:

  • You must be a Power BI Administrator
  • Next, select Admin Portal from the settings wheel found in the Power BI Service.

image

  • From the admin portal, select Tenant Settings and then expand Publish to Web.
  • Click on the button to disable Publish to Web feature.

image

Direct Query to a relational database is not a RLS enabled data source

Many users, especially users on later versions of SQL Server, assume incorrectly that a direct query connection to SQL Server will use the security restraints applied at the database level. Unfortunately, this just isn’t the case.

Any user connecting to a report with Direct Query is using the stored credentials in the enterprise data gateway and therefore will have access to whatever the data gateway has access to. So if you have enabled row level security on the tables for your database, this is not going to work with direct query.

However, you can and should add row level security to your power bi data model (pbix) file. This row level security will work and limit access as it is intended!

Not sure how to set up Row Level Security in Power BI? Thanks ok! Click Here

Advertisement

Download and unzip files using R in Power BI

/ Mitchell Pearson / Leave a comment

In a previous blog, I wrote about how you could use R to unzip files. Well, that raises the question, can I download a zip file and then unzip it using R?

You can view the previous blog post on how to unzip files here.

Update: YouTube video can be found at the bottom of this post!

Download files using R

In general, downloading and processing a file can be done in Power BI very easily using “web” as the data source and you never have to worry about using R. However, if you need to do something more complex then R can be very helpful. For example, you may want to download a zip file, unzip it, and then process it.

In this example, I am going to download a zip file that contains names by state, this zip file is stored in the following location:

https://www.ssa.gov/oact/babynames/state/namesbystate.zip

This zip file has data for each of the different states, for this example I only want to look at the data for Florida. So we are going to unzip the file and only extract the data from the single file that we need data from.

Let’s take a look at the code in R Studio and break it down:

image

  1. First, I want to store the destination location and filename in a variable, this is done in lines 1-2.
  2. Next, I want to download the zip file and store it locally, this is done in lines 4-6.
  3. Thirdly, now the file will be unzipped and the file FL.TXT will be stored in the variable FLnames, lines 8-9.
  4. Lastly, we will read the text file and store it in the variable babynames.

The only thing we added in this example that was not in the previous blog post was the ability of downloading the code. If you, missed the previous blog and want to know how to import the data into Power BI, you can check that our here.

 

Thanks, for checking out my blog!

Using R to unzip and process files into Power BI

/ Mitchell Pearson / 4 Comments

There are a lot of blogs and videos out there on how to use R to enhance the visualization layer of Power BI. In this small series, I want to focus on how R can be used to improve the data importing and data cleansing capabilities of Power BI. Previously, I wrote how you could use the R language in Power BI to export data out to a CSV file. You can view that blog by following this link.

Update: Video embedded at bottom of blog post.

Although, I personally am not an expert when it comes to R, I’m learning more each day, it’s one of the topics I enjoy teaching the most in our Advanced Power BI Boot Camp.

Now it’s time for the required Pirate Picture: ARRRRRRRR. By the way, this image is from a tabletop game I like playing called Merchants & Marauders!!

Image result for pirate arrrrr

Getting Started with R in Power BI

To work with R you will need to have R installed. I use Microsoft R open, link provided below. You can optionally download R studio as well. Both of these are free downloads.

Required: Download the Microsoft R Open

Optional: Download R Studio

Discloser: At the time of this blog post, there is no built-in intellisense in Power BI to help write and debug R. Therefore, I use R Studio to write and test my code! R Studio is not a requirement, it is, in my experience, just very helpful.

UNZ and READ.CSV in R

The problem I want to solve in this blog post is how to unzip a file with Power BI and then process that file. Because I come from an enterprise BI background my first thought is to approach this problem with a tool like SSIS or PowerShell. However, there is an easier way, why else would we be here! Smile

I want to show you how you can use R to unzip and then extract data from a file all in one step. By the way, it’s really, really easy!

Let’s start off by looking at the code, this is all of it!:

image

  1. On line one, we are declaring a variable called data, this variable will store the results of our expression.
  2. On line two we are using read.csv to extract the results from the csv file, Customer Sales, and these results are stored in the variable from step one, please note that this process can not occur until after the file has been unzipped.
  3. On lines three and four we are using UNZ to unzip the file Customer Sale.zip and then return the file Customer Sales.csv.

And that’s it!

Wait, wait, wait…. What is line seven? Well, line seven allows me to test the results in R Studio. This is not needed for the importing data into Power BI Desktop. Oh, that reminds me, we need to import the data into Power BI Desktop!

Importing data using R in Power BI Desktop

As you are already aware, there are a lot of data sources available in Power BI, it shouldn’t surprise you that one of those data sources is R. Now let’s walk through how we can quickly import data from an R script into Power BI Desktop.

From the home ribbon in Power BI, click Get Data –> Other –> R Script. See screenshot below:

image

This will launch the R Script editor. As I previously mentioned, this script editor is simply a text box. Now we simply paste in our code from R studio, or if you happen to be an R aficionado you can just type your code in this box, as seen below:

image

Once we click ok, the Navigator window will open. Since we only declared one variable in our script, only one option will be provided. From here, everything is exactly the same as any other data source.

4_Navigator Window

Ok, now we’re done! I hope you enjoyed this blog post and I look forward to writing more, until next time.

Using Composite model with Dynamic Security in Power BI

/ Mitchell Pearson / 2 Comments

Like many others, I have not been an advocate of using Direct Query, it has long had many concerns diminishing its’ overall usefulness with one of the major key issues being performance. The Microsoft Power BI team has made major strides to mitigate the concerns around performance by adding two new key features: Composite Model and Aggregations. This blog will not discuss aggregations. However, we will briefly discuss the composite model and how we can use this to make Dynamic Security better.

The composite model simply allows users to import data and use Direct Query in the same data model. This has long been a point of contention for Power BI users because combining these two features into one model adds a lot of value. In this blog, I am going to briefly explain how this model helps to improve dynamic row level security.

I was sure I had previously blogged about dynamic row level security but I don’t see a post so I am going to re-direct you elsewhere. If you are interested in learning exactly how to implement dynamic security you can check it out here: Dynamic Security implementation

I am a huge advocate for Dynamic Security. Dynamic security allows you to create a single role in Power BI and then you can manage all your user security in a single table. Take a look at the following table which I am now storing in SQL Server:

image

So why would the composite model make dynamic security better?

Great question. Historically, I would always import data into Power BI, including the dynamic security table. The problem with this method is what happens with the security of the data if the model is not being refreshed? For example, I may reassign which users have access to which sales territories in my security table but if data models in the service are not being refreshed then the security is not being refreshed and thus users will have access to data they should not have.

Now, with the composite model I can still import all my reporting data but then I can set up a direct query connection to my dynamic security table. This means that all of Power BI models using this table will always be up to date!

image

Hopefully you can find many more useful scenarios for the composite model! Thanks for reading my blog!

Power BI Resources

/ Mitchell Pearson / 1 Comment

This blog post is a quick post to outline some of the resources around Power BI that I have found most useful, I will update this list accordingly.

Power BI Learning Tour

Brand new to Power BI? If so, this Microsoft led training is a great place to start:

https://docs.microsoft.com/en-us/power-bi/guided-learning/

Custom Visuals – Series by Devin Knight

Devin Knight has, at the time of this blog, recorded 117 videos on his blog dedicated to demonstrating different custom visuals created by the community. If you are not already aware of this blog series, take a look at the link below!:

https://devinknightsql.com/category/power-bi-custom-visuals/

PowerBI.tips – Custom Theme Generator

My personal favorite from powerbi.tips is the custom theme generator. They also have pre-built layouts, tutorials and many other valuable resources.

https://powerbi.tips/

DAX Guide

The DAX Guide is a great place to browse and explore available DAX functions.

https://dax.guide/

Have something that just has to be on this list? Let me know!