Power BI Governance & Administration - Organizational Visuals

As the Power BI Service and Power Platform Administrator at Van Lanschot Kempen I often run into things related to governance and administration of our Power Platform environments. I plan on doing more of these posts, including, but not limited to, the following topics:

• Workspace upgrade to V2
• Refresh a dataset with Powershell (and a service principal)
• Power BI Premium monitoring
• Workspace and Gateway administration
• Naming conventions
• Tenant settings
• Power Platform environments

If you're interested in any of these or other related topics, please let me know, below in the comments or on Twitter!

Power BI Visuals

Now on to the topic of this post: out-of-the-box, custom & organizational visuals. The main topic of this post will be organizational visuals, but let's start basic and begin with the regular visuals. Because there are quite some out-of-the-box visuals in Power BI, that have a pretty diverse use case.

 

 

Next to all the basic visuals there are also quite some custom visuals in the marketplace, 253 at the time of writing, to be exact.

When you want to use custom visuals in Power BI, please take the following points into consideration:

• Make sure you trust the author and the source of any custom visual, it can contain code with security or privacy risks.
• Do you know what data is send over the internet? And where is your data send to?

When to use what visual?

But how do I choose between all these visuals? When do I prefer a line chart over a table visual or a pie chart? Well, the answer to the last one is simple: don't use pie charts. Like never. Even Wikipedia mentions many experts recommend avoiding them, among which Stephen Few.

 

Now we're clear on that, let's look at a few examples of how to choose the best chart/visual for your data:

• The Chart Chooser diagram on The Extreme Presentation Method by Dr. Andrew Abela
  This uses general chart types, some of which are basic and others custom visuals in Power BI
• Financial Times Visual Vocabulary: Power BI Edition by Jason Thomas (B | SQLJason)
  Jason divided around 75 visuals into 9 categories, he discusses basic, custom and R-visuals
• Choose your Power BI chart by cfunk on the Power BI Community Data Stories Gallery
• Power BI Visuals Reference by SQLBI.com
  Alberto and Marco discuss all the 204 visuals available on September 2018

Organizational visuals

At Van Lanschot Kempen, in the end of last year we introduced the use of Organizational visuals. This allowed us to deploy and manage Power BI visuals inside our organization. The mean reason for us for doing this was to block all, except the certified custom visuals, mainly because:

• The Admin can control which custom visuals are available in the organization
• Better security as those visuals will only be allowed for organizational members that are logged in
• No need to share visual files (when using private visuals)
• Everyone uses the same visual version (controlled by the Admin)

 

Tenant settings

In the Power BI Admin portal there are 2 Tenant settings that you can use to adjust the use of custom visuals to the needs of your organization. Because by default all members of an organization can add and use all Power BI visuals including imported private files.

 

If you think these settings were previously called different, that's correct. I believe they renamed them last week, this were the old names for the settings:

 

The 2 settings are used as follows:

• Allow visuals created using the Power BI SDK

Pretty straight forward: allow or disallow the use of custom visuals, either for everyone or (not) for specific security groups

 

 

• Add and use certified visuals only (block uncertified)

If the first setting is enabled, you can either enable or disable this setting.

 

 

Please be aware that when you enable this (last) setting, all uncertified visuals in current reports will be blocked (in the service). Enabling the visual through the Organizational Store again doesn't help, because in that case the visual uses another ID, as you will see later in this post.

 

In addition to blocking the use of certain visuals in the Service, Power BI Desktop supports using a Group Policy to apply these 2 settings also locally:

• EnableCustomVisuals
• EnableUncertifiedVisuals

You can read how to exactly enable this setting in the PowerBI visuals settings of the Admin Portal documentation.

Organizational visual administration

The organizational visuals can be managed from the Admin Portal.

An important note: organizational visuals are visuals that are handpicked and managed by the admin. None of the Tenant settings above are applied to the organizational store. Disabling the visuals in the organizational store must be done manually by the admin in the Admin Portal.

These are the organizational visuals we currently have deployed in our tenant:

 

 

As you see we also have the Hierarchy Slicer (by Jan Pieter Posthuma), because at the time we implemented that visual into the organizational store, it wasn't certified yet (it was certified on January 7, 2020). We still have to do an impact analysis to see if we can switch back to the regular (certified) visual, because then it will update itself again via the regular visuals store and can also be exported to Power Point for example.

 

And now it is official as the docs are updated: the HierarchySlicer for #powerbi is certified! pic.twitter.com/sWmYLefm3w

— Jan Pieter Posthuma (@jppp) January 7, 2020

 

Adding organizational visuals

In the Power BI Admin Portal you can Add an organizational visual in the Organizational visuals section.

You can provide the following properties for a visual:

• .pbiviz file (mandatory): you can download this file from AppSource.
• Name: You can give it any name you want, just be aware this name is not used as the name of your visual in Power BI Desktop, only in the My organization tab in Power BI Desktop
• Icon: Again, this is only the icon displayed on the My organization tab in Power BI Desktop, the icon in the  visualizations pane is still managed by the .pbiviz file.
• Description: duh :-)
• Access: you have the possibility to disable a visual currently deployed to the organizational store.

 

 

For more information on adding an organization visual please visit Add a new custom visual.

Using Organizational Visuals

As I mentioned earlier, when you are using organizational visuals they are different from the "regular" visual with the same name. They are managed in the Admin Portal and have a different ID from their regular counterpart. You can check this in Power BI Desktop if you right-click a visual in the visualizations pane.

 

Organizational visual

 

Regular visual, tied to AppSource

 

Now when I publish this report to the service I will see the following error on my report:

Recap and limitations

There are quite some Power BI visuals available for you to use: be it out-of-the-box, custom or organizational visuals. However, you can have good reasons not to open up the world for the Power BI report authors, so you start with taking a look at the Tenant settings.

Don't forget to plan for updating the used organizational visuals and communicating to the organization you're limiting the use of certain visuals for the end users.

 

Also, you might want to take a look at a few important limitations mentioned in the documentation, of which I'd like to highlight the following:

• If a custom visual is deleted from the repository, any existing reports that use the deleted visual will stop rendering. Deletion operation from the repository isn't reversible. To temporarily disable a custom visual, use the "Disable" feature.
• Organizational Power BI visuals are private visuals imported from the organization repository. As any private visual they can't be exported to PowerPoint or displayed in emails received when a user subscribes to report pages. Only certified Power BI visuals imported directly from the marketplace supports these features.

Resources and further reading:

• https://powerbi.microsoft.com/en-us/blog/deep-dive-in-the-organizational-custom-visuals/
• https://powerbi.microsoft.com/en-us/blog/how-to-govern-power-bi-visuals-inside-your-organization/
• https://docs.microsoft.com/en-us/power-bi/admin/organizational-visuals

Installing the PowerShell Power BI Management cmdlets

So I thought this would be a pretty straightforward task. But because I wrote this short post you probably figured out I ran into a couple of issues trying to install the Power BI Management commands for PowerShell.
I should also mention that I think I can say I'm pretty proficient at using the Power Platform products, but I'm still a beginner in the PowerShell world :-)

Prerequisites

For starters, keep in mind the supported environments and PowerShell versions, luckily I met those:

• Windows PowerShell v3.0 and up with .NET 4.7.1 or above
• PowerShell Core (v6) and up on any OS platform supported by PowerShell Core

Older versions of MicrosoftPowerBIMgmt

The first hurdle I encountered was the fact that there was already an older version installed. After some fiddling, trying to uninstall the old modules and going through the fixed described below, I figured the documentation was probably the best start, and it was. :-)

You can update to the latest version of the Power BI Management module with the following command:

Update-Module -Name MicrosoftPowerBIMgmt

If you want to uninstall all the Power BI PowerShell cmdlets, run the following in an elevated PowerShell session:

Get-Module MicrosoftPowerBIMgmt* -ListAvailable | Uninstall-Module -Force

Untrusted Repository

The second thing that hit me was this message that kept coming back about an untrusted repository PSGallery.

After some searching I found this blog that has a nice solution: adding the PSGallery to the trusted repositories will get rid of the necessary annoying pop-up:
Set-PSRepository -Name 'PSGallery' -InstallationPolicy Trusted

Install-Module

I thought I could finally get around to installing the desired module, but unfortunately I ran into another error:

I also tried installing only the Profile cmdlet, but that didn't help and turned up the same warning error.

Going to the link provided for the Source Location actually downloaded the NuGet-package, so the URL seemed valid, just not in PowerShell.

Another Google search got me the solution. Apparently switching to TLS 1.2 solves the problem, so I did:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12


Now I can finally get around to searching for one of my workspaces with PowerShell :-)

@NickyvV

My First Look at Read/Write XMLA Endpoints in Power BI Premium

Exactly 1 year ago Microsoft announced the public preview of PowerBI open-platform connectivity with XMLA endpoints. That allowed us to connect to Power BI Premium datasets with external tools, like SSDT, SSMS, SQL Server Profiler and DAX Studio.

On March 26, they took the next step and announced the public preview of read/write XMLA endpoints in Power BI Premium.

You can go to the link of the webinar to re-view the session given by Christian Wade last week.

Theory

If you just want to see read/write XMLA endpoints in action, skip to the Practice section of this post.

Otherwise, if you never heard of it or just wanna read up on the specifics, keep reading here and we'll get there eventually :-)

Preview Feature

To start with a side-note disclaimer:

XMLA Endpoint

If you're not familiar with XMLA, you can read all about it at the XML for Analysis (XMLA) Reference. Basically, it's the protocol for communicating between client applications and the engine that manages the workspaces and datasets.

These communications are through what are commonly referred to as XMLA endpoints. XMLA is the same communication protocol used by the Microsoft Analysis Services engine, which under the hood, runs Power BI's semantic modeling, governance, lifecycle, and data management.

Additional Functionality

Through these endpoints, Visual Studio and open source community tools like Tabular Editor are enabled to provide additional data modeling and deployment capabilities (supported by the Analysis Services engine, but not yet in Power BI Desktop), like:

• Calculation groups for calculation reusability and simplified consumption of complex models
• Metadata translations to support multi-language reports and datasets
• Perspectives to define focused, business-domain specific views of dataset metadata

Enable Read Write

Follow the steps outlined here to enable XMLA Read Write in the Power BI Admin Portal for your Premium capacity.

In Practice

To test the new read-write functionality I started with an existing Tabular project in Visual Studio (SSDT) and see if I can deploy this to a Power BI Premium workspace. This project currently has a compatibility level of SQL Server 2014 (1103).

One thing to remember is the current SSAS-version of my localhost, this is 1400. We'll come back to this later, as it seemed rather important…

Upgrade Existing Model

So I started with opening the model (.bim-file) to change it to SQL Server 2019 / Azure Analysis Services (1500).

That leaves me with the following error:

A bit strange at first, because I don't know why this wouldn't be possible. I tried upgrading one compat-level at a time. 1200 worked fine, as did 1400. But 1500 kept failing. Then it struck me that I set up the Workspace Server to my localhost, which is level 1400.

So to fix this, in the properties of the model, I changed the setting Integrated Workspace Mode to True. And after that I could change the compatibilitiy level to 1500.

If you haven't disabled the pop-up, you can also set this at the startup of your project:

Deploy to Premium Workspace

If you don't know yet how to connect to a workspace on a dedicated capacity read up on it here and then come back. In the properties of the project file you can set the Deployment Server to the Power BI URL of your workspace.

Security

In addition to the XMLA Endpoint property being enabled to read-write by the capacity admin, the tenant-level Export data setting in the Power BI Admin Portal, also required for Analyze in Excel, must be enabled.

Furthermore, it's good to know that when accessing workspaces through the XMLA endpoint, group memberships (workspace and app access rights) are still honored.

The highest access rights that are currently possible are equivalent to Analysis Services database admins. So anything that requires server admin permissions (server-level traces, certain DMVs) is not yet supported through XMLA endpoints.

Model Roles

When I started my deployment I ran the following error:

As stated in the docs, the following limitations apply when working with dataset roles through the XMLA endpoint:

• During the public preview, you cannot specify role membership for a dataset by using the XMLA endpoint. Instead, specify role members on the Row-Level Security page for a dataset in the Power BI service.
• The only permission for a role that can be set for Power BI datasets is the Read permission. Build permission for a dataset is required for read access through the XMLA endpoint, regardless of the existence of dataset roles. Use the Power BI security model to control permissions beyond RLS.
• Object-level security (OLS) rules are not currently supported in Power BI.

Obviously, I ran into the first point. And it turns out I had 3 XMLA-scripts for role assignments in my project, which are not supported yet in preview mode, as also explained here. I had to delete the defined roles in my model itself, as shown here:

Success (sort of)

After fixing the roles I tried to deploy again and I got myself a little cheer, because the metadata was successfully deployed! However, I was seeing some errors at the table level of the deployment.

Looking at the error details quickly showed me what is was, because I read that, again, in the docs:

When deployed the first time, a dataset is created in the workspace by using metadata from the model.bim. As part of the deployment operation, after the dataset has been created in the workspace from model metadata, processing to load data into the dataset from data sources will fail.

So looking at the Power BI workspace, we can already see the dataset appearing there!

The error is now with the credentials of the dataset. So configuring the dataset settings in the Power BI Service will solve this. Depending on the data source you can either:

• Set data source credentials
• Configure the dataset to use the data gateway

Downloading the PBIX file

There's one more thing I'd like to mention, which is also noted in the docs, but very important to know:

At this time, a write operation on a dataset authored in Power BI Desktop will prevent it from being downloaded back as a PBIX file. This will result in the following error in the Power BI Service:

Closing

I also did some tests with another project and got into some other errors. But this post is already getting very long, so I'm first going to investigate these errors and see if I can find an explanation for them to pop up.

Also remember once again, this feature is still in public preview, so it should not be used in a production environment. It is scheduled to be in GA in September 2020 according to the current release plan.

All-in all I think this is an awesome feature that will give us many more capabilities, especially regarding metadata of a dataset. And I only used SSDT and SSMS to connect to and deploy my dataset, but there are a lot of other options available that you can use.
The error messages I received could have been a bit more descriptive, but I heard (from a very good source) that the Power BI team is already working on that!

So to recapitulate, a few important points to notice in the end:

• Go read the documentation first 
• Enable Read Write for XMLA Endpoints at your Premium Capacity
• Enable the Export data setting in the Power BI Admin Portal
• Make sure your localhost is compat level 1500 or use the Integrated Workspace Mode
• Delete any role memberships in your model during the public preview
• A write operation on a dataset will prevent it from being downloaded back as a PBIX file
• Did I already mention it is still a preview feature? :-)

Did you already have a start with XMLA endpoints, being the recently released read and write feature, or the read-only feature? Please let me know what you think and also what things could be improved even further?

@NickyvV

Update: My Favorite (Data Platform) Podcasts

A tweet on the #SQLFamily hashtag triggered me last week to update an old blog post I wrote a few years back on My favorite (SQL server) podcasts.

Good news- I got a new job. Bad news- now I have a 45 minute commute. #SQLFamily I need some recommendations on podcasts I can listen to in the car to make my time productive!

— Jonathan Avellaneda (@SQLFutbol) February 5, 2020

After I switched jobs 2,5 years ago I have been doing a lot less commuting, which is good actually, but that left me also with less time to listen to podcasts. Therefore I chose to not listen to some episodes that are not that interesting to me.

Old podcasts

Here's the list I was already following since my last post:

• .NET Rocks!
• This is still a great podcast. I particularly like the Geek-outs.
• Dear SQL DBA
• The last episode is from the beginning of 2019, I always liked this very much because of the way Kendra explains things.
• Developer On Fire
• This was still a thing in 2019, but kinda dried up at the end of last year. The list of book recommendations is still there and huge, so do have a look there.
• Hanselminutes
• Still very much a thing. Good, short and fun episodes.
• no dogma podcast
• Still a regular flow of good episodes.
• Office Hours Podcast: SQL Server Pain Relief
• This one had its last episode on Jan 2 2019.
• RunAs Radio
• Still a constant, weekly flow of information and knowledge!
• Simple Programmer Podcast
• The frequency was turned down to weekly in 2019, but still regularly producing content.
• SQL Down Under
• The URL changed, and after 9 months of silence there was a new episode in December of last year.
• SQL Server Radio
• After a few months of silence there was a (re)new(ed) episode with a great announcement.
• voiceofthedba’s podcast
• Steve is actually on a sabbatical right now, but his short bursts of knowledge will certainly continue as he goes back to work next week.

New podcasts

These are the podcasts that are new:

• Analytics on Fire hosted by the amazing Mico Yuk (B | @micoyuk)
• Great content, focussed on adoption and implementation of BI projects.
• DataDriven.tv hosted by Frank La Vigne (B | @Tableteer) and Andy Leonard (B | @AndyLeonard)
• Alternately doing short "DataPoints" and longer in-depth episodes.
• Freakonomics Radio hosted by a whole team
• I recently added this to my list after a recommendation of the #SQLFamily.
• GroupBy.org
• A free database training conference by the community, for the community.
• Knee-Deep in Tech by Alexander Arvidsson (@arcticdba), Simon Binder (@bindertech) and Toni Holopainen (@MrBlackSwe)
• Fun and informative podcast about tech, Power BI and meta-topics.
• Linear Digressions by Ben Jaffe (@benjaffe) and Katie Malone (@multiarmbandit)
• Weekly Data Science podcast.
• Ask SQLFamily (ASF) by Kamil Nowiński (@NowinskiK) and Michał Sadowski (@SadowskiMichal)
• All interview-based podcast, but with well-known names from the industry so well worth listening.
• SQL Data Partners by Carlos L Chacon (@carloslchacon) and guests
• Focused mainly at Power BI and DWH-ing.
• The World of Presentations by Boris Hristov (B | @brshristov)
• Boris is/was a well-known name in the #SQLFamily, and now made his way to PowerPoint MVP.

Dutch podcasts

Next to the Data Platform related ones above, I also have a few Dutch podcasts that I like to listen to:

• NOS op 3 Tech podcast
• Tech news explained
• Een Podcast over Media by Alexander Klöpping (Blendle) & Ernst-Jan Pfauth (The Correspondent)
• Anything wildly related to media or tech is discussed
• POEN by NPO Radio 1 / NOS
• A podcast about economic news

Closing

I still use the app Podcast Addict forAndroid, now the paid version without ads.

And one more pro tip before I finish: I use a podcast app that can set the speed of the podcast. I listen everything at 2 times the speed now :-) You can start at 1.5 and work your way up to what feels comfortable for you.

Are you also listening to podcasts, on your way to work, during work or somewhere else? Let me know in the comments which ones you like and if you have others not in my list!

@NickyvV

My Upcoming Speaking Engagements: Belgium and Iceland!

Before talking about my upcoming speaking engagements I would like to tell you a little story. And it goes like this.

How it all started

Back in 2018 I started my journey of getting more involved in the SQL and Power BI community. It actually all started with a Power BI User Group (the Dutch Data Dude Farewell Tour with Jeroen ter Heerdt (B | @jeroenterheerdt)) organized by PBIG (and me) at the Van Lanschot Kempen office in Den Bosch, on which I wrote about earlier last year. Jeroen Schalken (B | @JeroenSchalken) and me did a little introduction talk (around 15 minutes) about our implementation of Power BI at Van Lanschot Kempen. It was frightening and exciting at the same time. During the break of the evening Edgar Walther (B | @edgarwalther) of PBIG came to say it was a very interesting presentation and if we would like to extend that and come talk at the Power BI User Day in March 2019. It was right there that I (and Jeroen) made the decision to say YES. We still had to make a one hour presentation, but also had plenty of time left.

After that presentation I really got the feel of it and started looking for other opportunities to speak. Eventually this turned out in speaking at Power BI Days Belgium with a session and a workshop, followed by SQL Saturday Prague together with Jeroen, dataMinds Connect in Belgium, and even the Power Platform World Tour in Dublin!

It brought me so much more than I could have imagined.

The first thing is that I get the chance to share my knowledge with like-minded people. Next, it was not only amazing to travel to these venues with great speakers and people I look up to. There were also awesome social events during every trip. And that's where the real magic happens. You make contacts, chat a bit, have a beer (or drink of your choice) and make new friends. And at another event you meet even more people to connect with, but sometimes also catch up with the newly made friends from the previous conference. It's been an amazing experience so far.

It was amazing being part of this #PowerPlatformWT! Thanks @pbiusergroup @benrebooted @DublinPUG for the opportunity!
My slides are uploaded to the PPWT site and can also be found at my blog: https://t.co/qnuW3ALWFu https://t.co/FLVSH5rLz0

— Nicky van Vroenhoven (@NickyvV) October 31, 2019

Upcoming events

But now back to the title of this blog post: it was time again to update my Speaking page, because I already have a few very nice events in my calendar.

This year will start with SQL Saturday Belgium (#956) on March 7, where I will deliver (an updated version of) the presentation that started it all.

Next up is SQL Saturday Iceland (#937) on March 28, where I will deliver my new session A Form, a Flow and a Power BI Streaming Dataset Walk Into a Bar. This session is based on the lightning session I gave at the Dublin Power BI UG during the Power Platform World Tour in Dublin.

Next to those two SQL Saturday events there's also a UG meeting planned, but not officially released yet. So more info on that one later.

Teaser

And last but not least: I will have some very exciting news to share in the coming weeks hopefully. I'm currently finalizing everything, so I'll let you know when everything is signed, sealed and delivered :-)

@NickyvV