Wednesday, April 15, 2020

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 :-)


Posted by No comments: Links to this post
Labels: ,

Tuesday, March 31, 2020

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:

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?


Posted by 9 comments: Links to this post
Labels: , , ,

Friday, February 21, 2020

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.




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:

Dutch podcasts

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

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
Posted by No comments: Links to this post
Labels: ,
Subscribe to: Posts (Atom)