Category Archives: Pragmatic Works

Power BI and Data Security – App Workspaces and Power BI Apps

Power BI Security LogoShortly after I published the Power BI Security Sharing Data post in April, Power BI Premium, Power BI Apps, and Power BI App Workspaces were released. These changes impacted that post in many ways. As part of the follow up, I also did an updated webcast with Pragmatic Works. This is a follow up post with some of the changes called out.

We are in the process of restructuring our organization around Apps and App Workspaces. Here are some of the highlights and changes related to sharing data using these new features.

Information Architecture and the Importance of Planning

My company, Pragmatic Works, uses a number of collaborative features in Office 365 including Planner, Teams, SharePoint, and Power BI. With this level of usage, a number of Office 365 groups get created. As we begin the process of updating our reporting structure, we will be using the App Workspace model to manage content creation and the Power App model to deploy content to users.

Before we began, we had to understand who the content creators are and who the consumers would be. App Workspaces are currently managed as Office 365 groups. We have a lot groups that match to our consumers, however, they really don’t work for content creators. Why? As we began the research, consumers exist in the current groups and are excellent targets. App Workspaces already exist for these users and groups due to our use with Teams and SharePoint. But due to the current limitations within Power BI and Office 365 with regards to group management, we need to create new App Workspaces, which also create new Office 365 groups to manage content creators. Typically these groups will be small and easy to manage. By limiting the users in these workspaces, we are also able to keep the additional clutter that is required.

In our process, we treated the end result as the guidance for the required workspaces. Each workspace creates and app that we want to target a specific set of consumers. By starting there, we created the list of workspaces we need to create. Because the apps and workspaces have a 1:1 relationship with each other, the apps (collection of Power BI content with the same permissions) are the determining factor for whether a workspace will be required. Our goal was to have the appropriate level of security while still minimizing management of the additional workspaces.

App Workspaces

We created the App Workspaces based on our Information Architecture Plan. The workspaces were created with two admins and set with members who would be content creators. Part of our exercise was to understand the impact of changing roles in Office 365 and related products such as Teams. What we learned is that Admin and Owner roles are shared throughout and managed by the Office 365 group. If you make a user an Owner in Teams they become an Admin in the matching Power BI Workspace. THIS IS IMPORTANT! While creating additional workspaces for report creation adds complexity by creating Office 365 groups, we have different security and content management rules for Power BI groups.

The Admins have the ability to add users to the group. Members do not. Also a Team, for instance, may have 100s of members who are essentially consumers. We are using the same role, Member, to assign to content creators. Consumers will use the Power BI Apps to view and consume the data made available. Because of this distinction, we created new Power BI App Workspaces.

When creating Members in Power BI Workspaces, you have the option to make those members View Only. However, doing so means all content creators will need to be Workspace Admins. This may work well for your organization, but remember Admins have elevated permissions as they are also Owners in Office 365 groups.

Preventing App Workspace Creation

Currently the only way to prevent App Workspace creation in a Power BI subscription is to disable the ability to create Office 365 groups or limit that capability to a small group of people. (NOTE: This affects all Office 365 applications which use Groups to segment the app such as Teams.) This is done using PowerShell. You can find details here including what applications are affected by this change.

Power BI Apps

In order to use Power BI Apps, all users need to have a Power BI Pro license or the apps need to be deployed to Power BI Premium. Whether you choose to use Pro or Premium should be evaluated for your organization. With current retail pricing, around 500 consumers is the “break even point” when only considering licensing. I will be discussing non-license related reasons to choose Premium in a later post.

When publishing or updating an app as noted in the images below. You have the ability to assign permissions to the app. Unlike Workspaces, you are able to assign distribution lists, individuals, and security groups to an App. This allows you to manage consumers using Active Directory (AAS). PBI Workspace and App

PBI App Permissions

Once Apps are deployed, a link can be sent to the users or they can find it in their available apps from Microsoft Appsource and their organizations deployed apps. They should only see apps they have been given permissions to. Once they get the app and open it, they have read only access but full Power BI interactive capabilities.

Power BI Apps will honor Role Level Security (RLS). However, unlike content packs, users cannot modify or change any content in the app including dashboards. When using apps, you are essentially creating the entire experience for the user and it cannot be “personalized” with their own dashboard or other updates. For enterprise reporting scenarios, this makes sense. For self-service or configurable solutions, you should still consider Content Packs.

To finish our story, we will be deploying Apps based on reporting groups within our Active Directory structure. This will allow us to control access to reporting through standard processes. At the moment, a group of us will manage the Workspace groups to prevent unwanted exposure to data and to manage report “creep”. In some ways, this is contrary to the original purpose of Power BI as a self service product. We are not limiting our teams capability to do their own report creation, app deployment, or self service analytics. We are making a point that the Enterprise Reporting will be managed which most organizations need on some level. If you have a great report you want to include, the Power BI Desktop allows us portability. The only not portable portion is the Dashboard itself. Hopefully we will be able to transfer that between workspaces in the future.

Advertisements

SQL Saturday #492 Follow Up – A Window into Your Data

sqlsat492_web

Thanks for attending my session on window functions in TSQL. I hope you learned something you can take back and use in your projects or at your work. You will find an link to the session and code I used below. If you have any questions about the session post them in comments and I will try to get you the answers.

Questions and Comments

  1. Does RATIO_TO_REPORT exist in SQL Server? It is in Oracle.
    • Currently this function is not available in SQL Server
    • Here is the equivalent functionality using existing functions in SQL Server:
      • OrderAmt / SUM(OrderAmt) OVER (PARTITION BY OrderDate)
      • This example can use the source code I have referenced below. It uses the current value as the numerator and the sum by partition as the denominator. While not a simple function, the equivalent is still fairly simple using window functions to help.
  2. Demo issues with Azure SQL Database
    • During the session I ran into an issue with Azure SQL Database. It turns out that the following two functions are not supported there.
      • PERCENTILE_CONT
      • PERCENTILE_DISC

Slides, Code, and Follow Up Posts

The presentation can be found here: A Window into Your Data

The code was put into a Word document that you can get here: TSQL Window Function Code

This session is also backed by an existing blog series I have written.

T-SQL Window Functions – Part 1- The OVER() Clause

T-SQL Window Functions – Part 2- Ranking Functions

T-SQL Window Functions – Part 3: Aggregate Functions

T-SQL Window Functions – Part 4- Analytic Functions

Microsoft Resources:

Check Out Josh’s Blog on Creating Percentile in DAX

I would like to introduce a “new” blogger to you. Joshuha Owen has restarted his blog and will be covering topics on business intelligence and data. I have worked with Josh for years at a Magenic and now Pragmatic Works. I look forward to seeing what he will be writing about in the future as well on Bits, Bytes, and Words.

Here is his most current post. Enjoy!

Replicating Excel Percentile in DAX

Currently, DAX has no native percentile function so if you want to replicate a version that matches what the Excel Percentile.INC (inclusive) function does you have to jump through a few hoops. This will involve having to create several measures to hold some intermediate values to apply a final formula. In theory you could do it all in one DAX expression but it would very difficult to read and test.

Continue reading…