Category Archives: Dashboards

Power BI Data Security – Sharing

Power BI Security LogoMicrosoft recently added more sharing capabilities that may change my view on sharing within the enterprise. As with all things Power BI, change is inevitable.

Up to this point, I recommended that customers did not use sharing as an enterprise solution due to the inability to manage it and the potential to share data within the organization that violates compliance or internal rules.

Sharing Within Your Organization

When you share a dashboard or a report within your organization, you share the data with it. Here is the issue from my perspective. If you allow users to share content, they are responsible to share responsibly. That is correct. The content creators are now responsible to manage security as well. So, let’s walk through the basics of using sharing effectively and securely within your organization.

Why Share?

The primary reason to use share is to distribute content outside the context of a Power BI App. Power BI Apps should be your first mechanism for sharing content within your organization. It requires more thought and planning which is typically a good idea with your companies data. However, there are times when sharing makes sense. With the ability to share reports, you can limit sharing to specific areas. Also, you may want to create a “one-off” report for use in decision making but not something to be deployed in the long term.

Sharing is very different from deploying Apps. App deployment is not that difficult to do, but prevents sharing and is much easier to manage access.

The Process of Sharing

Sharing capabilities are readily available on any content that you create.

At this point, there is no way to prevent sharing within your organization. Content can be shared from My Workspace as well.

The first step to sharing is to click the Share button on the report or dashboard you want to share.

PBI Share Button

This will launch a dialog for sharing the report or dashboard as shown here:

PBI Share Dialog

I have highlighted a couple of key parts to the dialog. The first is that you can share with individuals, distribution lists, and security groups. This is similar to the permissions you can apply to an App during deployment. As a content creator, I can distribute in this fashion. Typically power users who create content will use individual names or distribution lists as they are the most common methods of working with teams.

The next part to understand is the Allow recipients to share your report option. I have a couple of issues with this option. First, it is on by default. This means if someone shares with a peer in their department that individual can then share outside their department. The original content creator no longer has control of who this is shared to when this option is turned on which is my second issue. While the content creator will be able to see everyone they share with in the Access panel of the dialog when they review it later, they have potentially released data “into the wild” without controls if they do not set this up properly.

Click Share. You have successfully shared your report. Next, let’s have a look at the Access panel after the share is done. This panel is used view and manage sharing within the workspace.

PBI Share - Access Dialog

When in this dialog you can see who has what level of access to the report or dashboard you are currently in. You will see all reshares here as well. This will allow the content creator to remove access if needed.

The Manage permissions link opens up a dialog that lets you view and manage permissions for the entire workspace.

PBI Share - Manage Access

As you can see, sharing is managed by content creators. It will be important for them to understand the process.

Monitoring Sharing

Your Power BI environment should have auditing turned on. This will allow you to run reports to understand who has shared reports and dashboards across the tenant. This will be required to manage auditing and compliance within your organization.

Sharing and Security Thoughts

As I worked through this capability, there are a couple of closing thoughts on security to keep in mind.

  1. You cannot prevent sharing. You must monitor it, so be sure you have auditing turned on in your subscription.
  2. This has a place when sharing on a smaller scale. I would not recommend it as the standard process, but it allows you to share content in smaller chunks.
  3. You must have a process and policy for sharing. This has to be understood by content creators.
  4. If you implement row-level security in Power BI or SSAS, it is honored in sharing. This will prevent unauthorized access to sensitive data. Use this when you have particularly sensitive data in use.

One other thought. If this is a significant concern, you should evaluate Power BI Premium as it will allow to manage which users have the capability to create and share content. Free users are effectively read only within the organization. This will be cost-prohibitive for smaller organizations unless security is the primary concern.

Properly planned for you will be able to share effectively with Apps as a deployment model.

Advertisements

Excel Tip #30: Excel Services Visual Limitations – Displaying Images

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Introducing Brian Wright – Guest Blogger

Brian Wright

Today, I am happy to announce that Brian will be joining DataOnWheels as a guest blogger. I have worked with Brian over the past couple of years and his Excel visualization skills are great. I look forward to his contributions to the Excel Tips series and other BI related topics. Thanks Brian.

Hello Data on Wheels Readers! Let’s start this blog post by letting you know a few things about myself. First, I am not a professional writer, blogger, or ever social guru, but I am passionate about what I do. I love data visualization. Watching boring data come to life in a visual report or dashboard is my “thing”. Secondly, when things don’t work the way I think they should, I become obsessed in finding out a way around it.

Images Are Not Displayed in Excel Services

That is what leads us to this blog post today. In the limited environment I work within, Excel Services is used quite often in our BI suite of tools. When I realized that the ever so important images I was adding in my Excel workbooks would not show on Excel Services, my obsession kicked in.

Here is the trick or hack. (Using the word hack makes me look much cooler in my kid’s eyes). Wherever you want your picture within your workbook, simply add a chart. Yes, you read correct, simply add a chart.

clip_image002

Using Charts to Display Images

The trick here is not to link the chart to any type of data at all. Just leave it blank. Right Click on the blank chart and navigate to “Format Chart Area”. Navigate to the fill area and select “Pattern or Texture Fill”.

Next, click on the File Button and select your image. Your image will now show as a background image in your chart. Save and then voila!

clip_image004

Once Excel Services displays your workbook, you will be pleasantly surprised to see your image right where you want it!

Boston BI User Group Meeting–Dashboard Design with Microsoft: Power BI vs Datazen (10/13/15)

Boston BI User Group

Thanks for joining Anthony Martin (@SQLMartini) and I at the Boston BI User Group Meeting in October. During the session, we demo’d and built dashboards in Power BI Desktop and Datazen Publisher.

Power BI

image

www.powerbi.com

Couple of thoughts from our demo:

  • Power BI is a way to get data, model data, and visualize it
  • Power BI Desktop allows you to work with data on your PC
  • Power BI is experiencing a lot of change – for example over 40 changes were applied in September 2015
  • Power BI has an open API that allows you to customize the experience

Datazen

image

www.datazen.com

Couple of thoughts from our demo:

  • Design first scenario – make it look good, then shape data to match
  • Datazen publisher allows us to create dashboard for many different profiles
  • Datazen handles custom shapes

Additional Training from Pragmatic Works

Questions from the Session

Can you use links in Datazen to support drillthrough?

Yes. You can find more information here: Drill-throughs to Other Dashboards or Custom URLs.

Power BI API Development

You have the ability customize Power BI. Check out the contest winners to get some ideas of what is possible.

image

Details about the solutions can be found on the Power BI blog: http://community.powerbi.com/t5/Best-Visual-Contest/con-p/best_visual_contest/tab/entries.

You can find more about custom visuals here: https://powerbi.microsoft.com/en-us/custom-visuals.

Thanks again for joining us.

Excel Tip #29: Forcing Slicers to Filter Each Other when Using CUBE Functions

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Scenario

You have went to all the trouble to build out a good set of slicers which allow you to “drill” down to details based on selections. In my example, I have created a revenue distribution table using cube formulas such as:

=CUBEVALUE(“ThisWorkbookDataModel”,$B6, Slicer_Date, Slicer_RestaurantName, Slicer_Seat_Number, Slicer_TableNumber)

image

Each cell with data references all the slicers. When working with pivot tables or pivot charts, the slicers will hide values that have no matching reference. However, since we are using cube formulas the slicers have no ability to cross reference. For example, when I select a date and a table, I expect to see my seat list reduce in size, but it does not. All of my slicers are set up to hide options when data is available. There are two examples below. In the first, you can see that the seats are not filtered. However, this may be expected. In the second example, we filter a seat which should cause the tables to hide values and it does not work as expected either.

image

image

As you can see in the second example, we are able to select a seat that is either not related to the selected table or has no data on that date. Neither of these scenarios is user friendly and does not direct our users to see where the data matches.

Solving the Problem with a “Hidden” Pivot Table

To solve this issue, we are going to use a hidden pivot table. In most cases we would add this to a separate worksheet and then hide the sheet from the users. For sake of our example, I am going to put the pivot table in plain sight for the examples.

Step 1: Add a Pivot Table with the Same Connection as the Slicers

In order for this to work, you need to add a pivot table using the same connection you used with the slicers. The value you use in the pivot table, should only be “empty” or have no matches when that is the expected result. You want to make sure that you do not unintentionally filter out slicers when data exists. In my example, I will use the Total Ticket Amount as the value. That will cover my scenario. In most cases, I recommend looking for a count type value that will always have data if there is a potential match of any kind.

image

Step 2: Connect the Slicers to the Pivot Table

Using the Apply Filters button on the Pivot Table ribbon, you need to select all the slicers you want to interact with each other.

image

Once these changes are applied, you will see how my data changed.

image

Now, let’s test this for real. We will keep the date and table, but now we will see that the other slicers are now filtered to match the data that is available.

image

As you can see, the solution is fairly simple, but not intuitive. You will be able to create more creative dashboards with this technique. Keep in mind this issue is primarily a problem when using cube formulas in your Excel dashboard.

Until next time…

Excel BI Tip #28: Using member expressions in the CUBEVALUE Function

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

CUBEVALUE and CUBEMEMBER

If you have not looked at the cube functions in Excel check out two of my previous posts: Excel BI Tip #18: Using Cube Functions to Break Out of Pivot Tables and Excel BI Tip #27: CUBESET and CUBESETCOUNT Functions. Cube functions allow us to apply MDX functionality in Excel beyond pivot tables and charts. In this post, I will show how you can expand the CUBEVALUE function by directly using member expressions or CUBEMEMBER functions in the syntax. Tip #18 demonstrates how to use cells in Excel.

Typically, you would use these functions separately. For example, CUBEMEMBER is used in column and row headers in a typical solution. Because of this CUBEVALUE usually refers to a series of cells and slicers.

CUBEMEMBER used as a column header:

=CUBEMEMBER(“AdvWorks”,”[Measures].[Customer Count]”)

CUBEVALUE referring to this member:

=CUBEVALUE(“AdvWorks”,$D8,F$7,Slicer_Commute_Distance)

The snapshot below shows how it looks in Excel when the cell with the CUBEVALUE function is selected:

image

Using Member Expressions and CUBEMEMBER

The method illustrated above is how the cells are made when a pivot table has been converted to formulas. However, if you look closely the CUBEVALUE function accepts CUBEMEMBER values. The next few examples show how you can use members directly in the name to get more functionality.

=CUBEVALUE(“AdvWorks”, “[Measures].[Customer Count]”,CUBEMEMBER(“AdvWorks”,”[Customer].[Gender].[Female]”))

 

=CUBEVALUE(“AdvWorks”, “[Measures].[Customer Count]”,”[Customer].[Gender].[Female]”)

 

You can use any MDX expression that returns a member as one of the expressions in the function. This includes functions like STRTOMEMBER which allows you to create dynamic functions to get current date. Using member expressions allow for even more customization without dependencies on reference columns.

Adding a Slicer

Finally, you can add a slicer reference to the CUBEVALUE function.

=CUBEVALUE(“AdvWorks”, “[Measures].[Customer Count]”,”[Customer].[Gender].[Female]”, Slicer_State_Province)