Managing Members in a Power BI Group Workspace

As discussed in my post about Sharing and Security in Power BI, a group workspace in Power BI is our best option currently for organizing content by subject area, and/or by security boundaries. 

Members in a group workspace may be allowed to edit content, or restricted to view content (this applies to *all* members in the group rather than individually). This is easy enough to work around by setting members to view only, and allowing only administrators of the group to edit content. For groups, such as "Sales Staff" for instance, where there could be dozens or even hundreds of salespersons, this type of setup would allow all of the salespersons view only access with a very small number of people allowed to edit the content.

Dozens or hundreds of users in a group is what is prompting me to write this post. Manually managing the members within the Power BI workspace is just fine for groups with a very small number of members - for instance, your team of 8 people can be managed easily. However, there are concerns with managing members of a large group for the following reasons:

  • Manual Maintenance. The additional administrative effort of managing a high number of users is a concern. 
  • Risk of Error. Let's say there is an Active Directory (A/D) group that already exists with all salespersons add to the group. System admins are quite accustomed to centrally managing user permissions via A/D groups. Errors and inconsistencies will undoubtedly result when changes in A/D are coordinated with other applications, but not replicated to the Power BI Group.Depending on how sensitive the data is, your auditors will also be unhappy.

To avoid the above two main concerns, I came up with an idea. It didn't work unfortunately, but I'm sharing what I learned with you anyway to save you some time. 

The Idea For a Way to "Sync" Members from Active Directory to a Power BI Group 

Essentially, the idea was that if synchronization (or federation) from on-premises Active Directory is already taking place to Azure & Office 365, that we could use the sync'd O365 group (security group, distribution group, or mail-enabled security group) as the only member of the O365 unified group that controls the Power BI workspace.

There are multiple types of groups in Office 365. The names shown in the screenshots below are just because I did some various experimentation with Security, Distribution, and Mail-Enabled Security Groups. In the screenshots below:

      MCTesting_DistributionGroup: An O365 Distribution Group (the one to be sync'd from A/D)

      MCTesting_AlignWithDistributionGroup: An O365 Unified Group (created in Power BI)

Unsuccessful Attempt 1: "Invitation Will Be Sent" Message

When I tried to add the O365 distribution group as a member directly in the Power BI group workspace, I got a message "Invitation will be sent" rather than the normal dropdown to set admin or member. I saw this message recently as well when I typed an individual person's e-mail address incorrectly. At this point, I believe the "Invitation Will Be Sent" message is really indicating it's an invalid e-mail address so I take it to mean the Power BI group doesn't actually recognize usage of the O365 distribution group. (A bit of a guess there on my part - either way, it didn't work.)

Unsuccessful Attempt 2: Using the O365 Interface

In order to get around the first issue, I tried instead use the Office 365 group interface:

The good news is the e-mail address for the group was accepted in this interface. However, my happiness didn't last long because what it does is take that group and expand it out to the individual members (Meagan agreed to be my guinea pig in the test group):

Just for grins, I decided to add one more member to the O365 group. Sure enough, it doesn't flow through to the O365 Unified Group for use in Power BI. I didn't expect it to, just wanted to double check.

Ideally we would want the Power BI Group (i.e., the Office 365 Unified Group) to be maintenance-free in terms of membership - once the original A/D group members are updated, we want them to flow through to Office 365 and thus Power BI.

Alternative Solution: PowerShell

You can use PowerShell to automate synchronization of members extracted from Active Directory into the respective Office 365 Unified Group for use in Power BI. There are some ExchangeOnline cmdlets that could be utilized for this purpose. Specifically, the "Add-UnifiedGroupLinks" cmdlet adds one or more members to a unified group. Don't forget to also delete members that no longer exist in Active Directory. Also, you may want to only manage your read-only members in the O365 Unified Group & let the group admins be manually set in the group (presuming that most users are read-only & that there's only a handful of admins who can edit content).

The purpose of this post is to share what I've observed so far with respect to easing the effort of maintaining a large number of users in Power BI groups. At some point I'm certain our options will evolve, so be sure to validate in your environment.

You Might Also Like...

Groups in Power BI - How Sharing and Security Works

Overview of Power BI V2 Features End-to-End

Direct Connect Options in Power BI for Live Querying of a Data Source

Tips for Using Azure Data Catalog

It seems there's a lot of interest in Azure Data Catalog from the customers that I work with. Since I've been discussing it a lot recently during projects, I thought I'd share a few thoughts and suggestions.

Register only Production data sources. Typically you won't want to register Development or UAT data sources. That could lead to users seeing what appears to be duplicates, and it also could lead to inconsistency in metadata between sources if someone adds an annotation to, say, a table in UAT but not in Production. 

 
 

Register only data sources that users interact with. Usually the first priority is to register data sources that the users see-for instance, the reporting database or DW that you want users to go to rather than the original source data. Depending on how you want to use the data catalog, you might also want to register the original source. In that case you probably want to hide it from business users so it's not confusing. Which leads me to the next tip...

Use security capabilities to hide unnecessary sources. The Standard (paid) version will allow you to have some sources registered but only discoverable by certain users & hidden from other users (i.e., asset level authorization). This is great for sensitive data like HR. It's also useful for situations when, say, IT wants to document certain data sources that business users don't access directly.

Use the business glossary to ensure consistency of tags. The business glossary is a capability of the Standard (paid) version and is absolutely worth looking into. By creating standardized tags in the business glossary, you'll minimize issues with tag inconsistency that would be annoying. For example, the business glossary would contain just one of these variations: "Sales & Marketing", "Sales and Marketing", or "Sales + Marketing".

Check the sources in the "Create Manual Entry" area of Publish if you're not seeing what you're looking for. There's a few more options available in Manual Entry than the click-once application.

Use the pinning & save search functionality to save time. For data sources you refer to often, you can pin the asset or save search criteria. This will display them on the home page at AzureDataCatalog.com so they're quicker to access the next time.

Use the Preview & Profile when registering data when possible. The preview of data (i.e., first X rows) and profile of data (ex: a particular column has 720 unique values that range from A110 to M270) are both extremely useful when evaluating if a data source contains what the user really wants. So, unless the data is highly sensitive, go ahead and use this functionality whenever you can.

 
 

Be a little careful with friendly names for tables. When you set a friendly name, that becomes the primary thing a user sees. If it's very different from the original name, it could be more confusing than helpful because users will be working with the primary name over in tools such as Power BI.

 
 

Define use of "expert" so expectations are clear. A subject matter expert can be assigned to data sources and/or individual objects. In some organizations it might imply owner of the data; however, in the Standard (paid) version there is a separate option to take over ownership. Usually the expert(s) assigned indicates who knows the most about the data & who should be contacted with questions. 

Be prepared for this to potentially be a culture change. First, it's a culture change for DBAs who are responsible for securing data. The data catalog may absolutely expose the existence of a data source that a user didn't know about--however, remember that it only exposes metadata and the original data security is still in force. The other culture change affects the subject matter experts who know the data inside and out. These folks may not be used to documenting and sharing what they know about the data. 

You Might Also Like...

Overview of Azure Data Catalog in the Cortana Intelligence Suite <--Check the "Things to Know about Azure Data Catalog" towards the bottom this post for more tips

How to Create a Demo/Test Environment for Azure Data Catalog 

Setting Up a PC for Azure Cortana Intelligence Suite Development

Some development aspects of the Cortana Intelligence Suite can occur in the Azure Portal. There are also some additional client tools which are helpful, or potentially required, to fully create solutions. This is a quick checklist of tools you probably want to install on a development machine for purposes of working on the analytics, BI, and/or data warehousing elements of Azure.

1. SQL Server Management Studio (SSMS)

The latest version of SSMS is recommended for compatibility with Azure services, as well as backwards compatible with all SQL Server versions back to 2008.

Download SSMS:  https://msdn.microsoft.com/en-us/library/mt238290.aspx


2. Visual Studio 2015

The latest 2015 version of Visual Studio is recommended for full functionality for the newest components. If you choose to do a customized VS installation, be sure to select the option to install Microsoft Web Developer Tools. (If you don't, when you try to install the Azure SDK later it won't install properly because prerequisites are missing. Yeah, yeah, I've been there.)

If you don't have a license available, look into using the VS Community edition.

Download VS 2015: https://www.visualstudio.com/downloads/download-visual-studio-vs 

Note: in addition to "Visual Studio 2015," there's also a "Visual Studio 15 Preview." The 15 Preview is *not* the same thing as Visual Studio 2015, even though 15 is in its name. So, just watch out for that in terms of naming.



3. SQL Server Data Tools (SSDT) for Visual Studio 2015

Here's is where you gain the ability to create BI projects: SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS). These SQL Server BI projects aren't considered part of Cortana Intelligence Suite, but if you're creating an analytics, BI, and/or data warehousing solution you may need at least one of types of BI projects as part of the overall solution.

With the latest version of SSDT for VS 2015, you'll also be able to interact with all versions of SQL Server, as well as Azure SQL Database and Azure SQL Data Warehouse.

Example of what an Azure SQL Data Warehouse cloud resource looks like from within Visual Studio (SSDT):


4. Azure SDK

The Azure SDK sets up lots of libraries; the main features we are looking for from the Azure SDK right away are (a) the ability to use the Cloud Explorer within Visual Studio, and (b) the ability to create ARM template projects for automated deployment purposes. In addition to the Server Explorer we get from Visual Studio, the Cloud Explorer from the SDK gives us another way to interact with our resources in Azure. 

Note that Visual Studio 2017 will have the latest Azure SDK built in, so this separate installation step won't be necessary. At that time of this writing, Visual Studio 2017 is not fully released yet.

Example of what the Cloud Explorer pane looks like in Visual Studio (by Resource Group, and by Resource Type):

Example of what you'll see related to ARM template projects after the Azure SDK is installed:

The above Azure Resource Group project is useful for source-controlling your Azure infrastructure. This can hold your ARM templates (JSON files), as well as PowerShell scripts (see #8 below).

Download the Azure SDK for Visual Studio 2015: https://azure.microsoft.com/en-us/downloads/ 


5.  Relevant Visual Studio Extensions

These are important extensions for working with Cortana Intelligence Suite at this time:
-Microsoft Azure Data Lake Tools for Visual Studio 2015 <--Automatically installed as part of the Azure SDK
-Microsoft Azure HDInsight Tools for Visual Studio 2015 <--Automatically installed as part of the Azure SDK
-Microsoft Azure Data Factory Tools for Visual Studio 2015

At the time of this writing (June 2016), Azure Data Factory Tools are not automatically installed with the Azure SDK. That will probably change at some point I would guess.

Example of the Cortana Intelligence Suite projects you'll see after the Azure extensions are installed:

Depending on the particular service, there may be links within the Azure portal as well:

Download: https://azure.microsoft.com/en-us/downloads/ <--The file called "VS 2015" is the Azure Pack

Ongoing updates for Azure Data Lake and Stream Analytics Tools for Visual Studio: http://aka.ms/adltoolsvs

The latest extension for Azure Data Factory: https://marketplace.visualstudio.com/items?itemName=AzureDataFactory.MicrosoftAzureDataFactoryToolsforVisualStudio2015


6.  Microsoft Azure Storage Explorer and/or AzCopy

I really like the new standalone Azure Storage Explorer for uploading and downloading files to Azure Storage. AzCopy is another alternative - AzCopy is a command line utility instead of a graphical UI, so it's better suited for automation and scripting purposes. 

Example of what Azure Storage Explorer looks like:

Download Microsoft Azure Storage Explorer: http://storageexplorer.com/ 
Download AzCopy: https://azure.microsoft.com/en-us/documentation/articles/storage-use-azcopy/

Note: There is a different Azure Storage Explorer on CodePlex with an almost identical name to the one I'm referring to.


7. Azure PowerShell

Even if you don't tend to utilize scripting and automation tremendously, chances are you'll need PowerShell for something sooner rather than later. Installing Azure PowerShell adds the Azure management cmdlets to Windows PowerShell.

Download: https://docs.microsoft.com/en-us/powershell/azure/install-azurerm-ps?view=azurermps-4.0.0

There's also an Integrated Scripting Environment (ISE) you may want to look into using. The ISE also has an Azure Automation Toolkit which makes local authoring of Azure Automation runbooks easier.

 

8. PowerShell Tools for Visual Studio

You may want to store and source control various PowerShell scripts:


9. R Tools and/or Python Tools for Visual Studio

R Tools for Visual Studio (RTVS) and Python Tools for Visual Studio (PTVS) are optional alternatives to other design environments (such as R Studio).

Download RTVS: https://www.visualstudio.com/vs/rtvs/

Download PTVS: https://microsoft.github.io/PTVS/


10. Azure Feature Pack for SQL Server Integration Services

In step 3 above we installed the BI projects including SSIS. There's also a feature pack for SSIS which adds connection managers, tasks, and components for Azure services such as Blob Storage, Azure HDInsight, Azure Data Lake Store and Azure SQL Data Warehouse.

Download for SSIS 2016: https://www.microsoft.com/en-us/download/details.aspx?id=49492

Download for SSIS 2014: https://www.microsoft.com/en-us/download/details.aspx?id=47366

Depending on what you need to do, there might be additional items to install (such as the Azure CLI), but the above list should be a good start for tools related to development of analytics, DW, and/or BI solutions. 


11. Team Foundation Version Control or GitHub

Usage of Team Foundation Version Control (TFVC), Git, or another form of source control is not just a good practice, it'll save your bacon one of these days.

TFVC: https://www.visualstudio.com/team-services/tfvc/

GitHub: https://github.com/


Building Blocks of Cortana Intelligence Suite in Azure

I've been really enjoying learning more and talking with customers about hybrid analytics and data warehousing solutions. The services which are evolving as part of the Cortana Intelligence Suite are really amazing. I've put together a new presentation called the Building Blocks of Cortana Intelligence Suite.

I'll be presenting it for the first time at SQL Saturday Atlanta in May, followed up by the Charlotte BI Group and Carolina IT Pro Group in June. This will be a fast-moving introductory session wherein I'll introduce each component of the suite and discuss its purpose and use cases. I'll also call out important requirements for expertise, such as key tools and languages.

Each section will wrap up with an example of the 'building blocks' to formulate a solution. Although these 'building blocks' examples are greatly simplified, my hope is it will generate ideas for how the different Azure components can fit together for formulating hybrid solutions. 

The materials will changing over time as I refine the presentation and as new capabilities in Azure evolve and mature, so check back later for updates.

You Might Also Like...

What is the Cortana Intelligence Suite?