>> Good morning, everyone. Thank you so much for
joining us today. Today we have the pleasure
of the amazing Ted Pattison, who's going to show us how to write PowerShell
scripts with Power BI. This is the Power BI
Dev Camp series , and I'm super proud to
have you here today again. Ted, thanks so much for putting
this together. How are you today? >> I'm doing very well.
Thank you, Kelly. I'm very excited about talking
about PowerShell for Power BI. What could be more
exciting than that? >> Not much. >> I'm glad I convinced you. Let's go ahead and start off. I'd like to welcome
everyone here and there is a live events Q&A session so we'll try our best to combine and
monitor questions as they come in. There's a couple of breaks during the session while I'll
just see if there are questions and ask them. But in general, creating
some interactivity for us would be good so please feel free as we're going through
to ask some questions. One thing I want to point out
is a couple of resources, let me go over to my browser here. First thing I want to say is if
you go to community.powerbi.com, you can see that the very first
Power BI Dev Camp session is now published there as a video. Then also I have a little
portal where I'm just going to put a lot of
different links together. Here, notice that
for today's session, if you want to download the slides, those are available for download, and there's also going
to be a tutorial. What I wanted to do is go through a bunch of demos over the next hour, and you don't have to
remember anything because if you want to do the exact
same thing I'm going to do, you can see here let me make
a little bit smaller here, you can see that there's
quite a few pages to go through and get this code
under your fingertips. The other thing I want to
point out before we get started is that we do
have this tutorial, and there is either a Word
document or a PDF file. There's a whole GitHub repo for
this tutorial inside there. Now, what you're going
to do is inside the lab, let me go back here, let me go ahead and take this
link that we give you here. If I go to a browser, and now we go ahead
and do a paste and go, notice that there are going
to download a set of files. It's one thing for me to get these
things running on my machine, but it's far more important for you to get these scripts
running on your machine. What I wanted to do here is
at the beginning of the lab, I'm going to have you
take these files, and basically just copy them into a local directory
right here, DevCamp. What I'm going to do, is
I'm going to go through a lot of these demos that are here, and they're just ready for
you to open up and run. If you're going to go
through the tutorial, notice that you're going
to be writing things in the scripts folder and in solution. Once again, I want to do baby step anyone through
who's new to PowerShell. Now that we know the ground rules, let say that I go into
demos and in just a second, I'm going to open up
our first script. Before we do anything with Power BI, what we're going to do,
is we're just going to do a little bit of
calisthenics to warm up. Let's go back to my
slides just for a second, and I'll revisit these demos. But once again, just want
to make sure everyone knows that the tutorial is there. Before we dive in, I assume that most people here know PowerShell and have been using it for somewhere
between 1-20 years. But for those who are
newer, what is PowerShell? It's this automation tool
we've been using for a long time that has both a command shell and
a scripting language. It is an object-based language. The more you know about.NET programming and dealing
with.NET objects, the easier it is to
learn in PowerShell. But we have things like tab
completion, pipelining. Now, we also have to
recognize there are two major versions of
PowerShell I want to point out. The one that I'm
assuming that most of you are familiar with
is Windows PowerShell. It has come as part of
Windows for awhile. It's included with the Windows
Management Framework five, and just like I'm going
to be working with here, typically we're able to use
this thing which is called the PowerShell Integrated
Scripting Environment to be able to write
and test our code. For instance, here
is a simple example. Here is the PowerShell
syntax to create an array, let's go ahead, move through here. Just let's illustrate a
simple for each loop. The other thing about
PowerShell is that it makes it possible to write
code in very different styles. People like to have
for each object and have dollar sign underscore dot. When I try to write code especially
for a session like this, I try to write it so it's
as readable as possible, and I'm going to let you take what
I teach you here if you want to start making it more reusable and putting it
into functions and modules, that's what you can do. But once again, what
I've tried to do here is write code that is
easy to understand. Now there's also a new version
of PowerShell, PowerShell Core. It started as PowerShell six, and now there's PowerShell seven, but the idea is since it
sits on top of.NET Core, it's no longer a Windows only thing. You could basically run PowerShell
seven on a Mac or on Linux. Many people will run
PowerShell seven on Windows just because they need
to work with PowerShell seven. Some of the things that are
different is you can no longer use the PowerShell
Integrated Scripting Environment. Typically we switch over to
something like Visual Studio Code, which has really nice
PowerShell extension. Now for the things I'm going
to be doing in this session, you can use either one of these. I'm going to stick with
PowerShell five and the PowerShell Integrated
Scripting Environment that most people are familiar with. But there's a couple of
things that I'll do with PowerShell seven and
Visual Studio Core. Now, PowerShell 101, the first time you sit down a machine
after you've installed Windows, you cannot execute PowerShell
scripts unless they're signed. Generally, we do a set execution
policy bypass to basically put your machine into developer
mode so you can write scripts and test them
without them being signed. In this opening calisthenics, one of the things I wanted to do is just show a couple simple things such as when we're
dealing with PowerShell, being able to create an array. Another thing that gets to be common as you know,
so here's an array. We have the at sign and open
and close curly braces, but now we're going to use
the at sign open curly brace, and so we create these dictionaries. They're like JavaScript
name value objects, but what's neat about that, let's go back over here and now we're going to go to
programming dictionaries, and what I just want to make sure is everyone is
comfortable with doing things like creating a collection of objects and
enumerating through it. One more opening
calisthenics that will do. The reason I'm showing
you the syntax is that we're going to be
using it later on is, let's say I don't want to just
spit out something to the Console, I want to create a file. Here's an example of using
$PSScrptRoot to basically figure out what is the folder in which
the script is running. Now I create a text
file name inside here. Is there a question? I heard
another voice inside there. But here's just an example of running something but generating
a text file because you're querying data and you want
to record the data either in simple text or CSV or JSON format. Now let's go ahead and move into the topic that we're
here to look at which is basically working with
PowerShell and Power BI. There is a library of PowerShell commandlets and it is
called Microsoft Power BI MGMT. What you're going to have to do
if you've never installed this, is to basically bring up a PowerShell command prompt running as admin and
run Install-Module, and then that's going to
put these on your machine. Note that if you're using both
PowerShell five and PowerShell seven, you'd have to install this
module individually for each one because they don't share
runtimes or installed libraries. Now let's take a quick look
at the different modules. When you install the main Microsoft
Power BI Management Modules, they install several
different modules. You can install these individually. They're not that large. But the one thing to note is that if you install them individually, the one that you always
have to install is profile because that gives you your ability to do things like
connect to the Power BI service. Typically, I'll just go ahead
and install all of them, at least, that's my own
personal preference. Now, let's go ahead and
look at something simple. Now, the other thing is, when you go through the lab, one of the things that you're going to need is your
going to need to have some type of Power
BI test environment. I just created before
we started here, about half an hour before
we started I created the new Office 365 trial where
you get E5 Office 365 licenses, where it gives Power BI Pro, and I'm going to be
running as this user. I created another user for some of the demos that I'm going
to run right here. Then also we have a
Power BI environment. If you're going to do these
labs in your own environment, you need permissions to be
able to create workspaces. You need a Power BI Pro license. If you're going to do the
labs at the very end, you're also going to need to have permissions as a Power
BI administrator. One of the things that
we point out in the lab here is that if you don't have an environment
that you feel good about trashing because you want to just start hacking
PowerShell scripts. We've provided a document here, how to basically within five
minutes, 10 minutes tops, to create a trial environment, we'll have up to 25 users with
Power BI Pro licenses for 30 days. You can do all the trial, and that's what I'm going to be
using as we go through this demo. Let's go ahead, and now, we just want to talk
about logging in. Let's start by logging
in a user interactively. We have the Power BI, the Connect Power BI service account. Now, note that I'm going to go
ahead and run this right here, and as we run the script, it basically asks the user who's running the script to
basically log in right there. Now, we're going to have this
particular interactive login, and things look great. Well, let's say that I'm going to be running the script
200 times over the next hour, and the last thing I
want to do is put in my username and password 200 times. What we can also do is let's go ahead and see login user unattended. Now, what we're able to do
is put a user password. Here's some programming
geek stuff you have to do. Convert to secure string. We create this thing
which is a PS credential. But now, when we call power to
connect Power BI service account, we can pass a credential
that basically has baked into it the
username and password. Note that this can break
down in certain scenarios. Some types of environments don't allow this type
of seamless login. If multi-factor authentication
is turned on, it can break. But once again, you're looking for, typically dev scenarios
where you're looking to got to create the
convenience of being able to hard-code the username and
password inside of your script. We just basically seeing now
how we connect interactively, and then how we can connect with
basically unattended execution. Let me see if we have
any questions here. Could you please paste these links in to the things
you were just talking about? I think I'll probably have to do
that at the end of the session. It would just be a little
bit too distracting, but I'll make sure that everyone gets all the links inside of this chat. Anyway, feel free to put
questions in the Q&A, and each time we hit
one of these breaks, I'll come pick up the questions. Now, the next thing we'd like to do is we'd like to
create new workspaces. It gets to be very
easy because there's a new Power BI group commandlet, and all you have to do
is pass it the name. Let's say we go back here, and what I'm going to do here is let's go ahead and
call create workspace. Another thing is that you can call connect
Power BI service account. But once you call it,
when you run the script, what's neat is in the PowerShell ISE, they cache things
including your connection. If you don't like logging in, log in one time commented out, and then we can just basically
run this right here. What we're going to do is we're
going to create a Dev Camp Labs, as the name of our workspace. Because I know your inner geek
wants to get really technical here. Let's go ahead and bring up Fiddler. We go ahead and run this and what you can see as
a new workspace got created. If I go back here into Fiddler, you can see behind the
scenes what's happening. Let's go ahead and note
that if I just look at raw, there is a request that comes in, and one of the thing here, and I'm sorry, I'm going to
get really geeky with you, is let's go ahead and look at this access token that's being passed in here, and
what I'm going to do, is there's a little,
neat Fiddler trick where if I come back up here, and then I basically have this
in the text way here down below. It takes that token and it shows
me what's inside of that token, and so what I'm trying
to show you here is that there is this thing which is
called user impersonation. The idea is that you have one
permission as far as Azure AD, and that's user impersonation. To make a long story short, if you weren't using this set
of PowerShell commandlets, you basically have to create
an Azure AD application, and you'd have to do things like get access tokens with
specific permissions. What's neat about working with this PowerShell library is you don't have to create
an Azure AD application. There's one that's built-in, and anytime it gets an
access token for you, it's just as an access token
that says user impersonation, that means you automatically get all the permissions of
the current user account. With that, let's go ahead
and close some of these, and move ahead and start looking
at a couple other examples here. What I just did is I should
have created a new workspace, and we go back over
here and we can see, hey, there's the workspace
that just got created. That's great. Maybe
I want to add users, and I don't want to have
to do all that by hand. I want to basically do
it programmatically. Let's go back here. Let's go ahead and now we're going to say
Create Workspace and add users. But now, here's the case where is
this workspace already created? What we're going to do is we're
going to take the workspace name. We're going to then call
get Power BI Workspace, and see if this passes back a result. If this comes back as
a non-empty value, well then we found the workspace. If not, we'll just go ahead
and create the workspace. But now, after the fact, what we want to be able to do is we want to be able to
add this other user. Let's go back here. I need to know that user's e-mail
address inside here. Once again, let's save some time by not having to interactively log in. Typically that caches for a good long time while you're
doing development work, and now the workspace already exists, and now, if we go back here
just to check our work, let's close and reopen, and what we've seen is we've
added that particular user. Now, what's interesting is that, if you're a regular user,
you're not an admin. You can add users, but you can't query to
see what users are there. That takes admin permissions, and we're going to look at that a
little bit later in the session. We've seen create workspace, we've seen add workspace users. Well now, we'd like to
import some content. If you create a workspace, and it stays empty,
it's not very exciting. Now, let's automate this idea
of uploading a PBIX file. Now, the thing that we're
going to use to upload the PBIX file is new Power BI report, and new Power BI report can be used to upload either a PBIX
file or an Excel workbook. It supports both types, and what we're going to do
in this case right here, as you do your lab, also note that I'm just going
to put some PBIX files. There's two that we're
going to see here, COVID-US PBIX, and SalesByState PBIX. Just to keep things simple, I put the PBIX file in the same folder as the
PowerShell scripts themselves. Now, what we're going to do when we want to go ahead
and import something. Now, let's go ahead and
import a PBIX file. Let's go ahead, and our case right
here, New-PowerBI desktop file. We're going to go ahead
and find the path. Basically, we point to
a physical PBIX file. What is the workspace? Then also very important, what is going to be
the conflict action? In almost every case you want create or override or possibly overwrite. The problem is if you don't
have the conflict action, and you upload a report name Bob, and then you do it a second time, it creates a second dataset and report of the same name
with a different width. You have to have conflict action, create or override to get the
push out a report where dataset, and then continually overwrite it to basically update
it with a new version. Let's go ahead and run
this request right here, and I forgot to disable this, so I'm going to have to
log in again, and now, that I have logged in, what we should see is that it
imports the new PBIX file, and when it's all done, there's the PBIX file, and notice that there's the name, there's the web URL, embed URLs, some properties
of the report itself. If we go back here, what we can see is
that operation created a new report and a new dataset, and once again, I can simply keep running this and take the new
version of this PBIX file, and overwrite the old version that
is inside the Power BI service, as long as I make sure I have this
conflict action set correctly. Now, let's go ahead and
look at our next example, and our next example is going to be, let's say that we have a report
with a dataset in workspace one, and we'd like to move it
over to workspace two. For instance, let's say
here we have Dev Camp Labs, and now I'll create a second one, and this one will be called My New W. We'll go ahead and choose "Save". What we'd like to do is, we'd like
to take that report and dataset from one workspace and move it
over here to this other one. What I'm going to do is, I'm
going to open up a new one. So this one is
Copy-Report-To-New-Workspace, Dev camp Labs, what is the
target workspace inside here. Notice that we're going to get
information about both workspaces, and then we have to make a
call to get PowerBI report. Because what we're going to do is we want to call Export-PowerBIReport, but I have to know the
GUID of the report ID. I can go figure out
and copy GUIDs inside, I just like it better if I just have the friendly name and I resolve
into a GUID at runtime. That's what this report is doing. Now also, let me open this up and
let me go into Demos right here, let me go ahead and run this. This time when I run it, I want to demo one of the things
that's going to be happening, and that is that we're
going to be creating a new report inside here. I didn't do a very good job
of showing that. Copy report. But the important part there, and I think that's because
I'm in a totally wrong place. Give me one more chance
to get this demo right. Let's go back here, let me go
ahead and comment that out. Let's go ahead and run
the script one more time. What we should see as
this script is running, it's basically downloading the
PBIX file from Workspace one, it's now going to upload
it to Workspace two, and then delete the temporary file. Unfortunately, there's
not a better way to move a dataset from one
workspace to another. I think a year or two from now with the XMLA endpoint
and what it's going to add, there's going to be much
better ways to do it. But downloading and uploading
the PBIX file is still considered the best way to
move something like that. Now, let's go ahead and
keep moving through here. The last thing we're going
to see in this section is a call to Get-PowerBIDataset. What I want to show here is that, I can call Get-Power BI Workspace and pass the name parameter and
that will find the workspace. For some reason, Get-PowerBIDataset, the name parameter doesn't
work as I would expect it to. So what we're going to do
is, we're going to get all the datasets in my workspace, and then we're just going
to use Where-Object and say where the Name
equals the dataset. But what I want to show here is that, it's simple commandlet to
call Get-PowerBIDataset, but we need it because
we need to figure out in situations that we're
going to look at in a second, how to figure out what
the dataset ID is. Now, let's go ahead and see if
there are any more questions. This may be answered later,
I don't want to forget it. Is it possible to use PS to retrieve a list of users a report
has been shared with. That is not yet available. We at the product team, know very much that that is a gap in the API, so we're working towards getting
some things inside there. Now, let's keep going. What I want to look at next is a special method which is called
Invoke-PowerBIRestMethod. If you start programming the Power BI REST API and you
figure out what's possible, and then you go and look at this list of commandlets that are offered by Microsoft Power BI Management, you'll find that there's lots of operations and there is
no direct methods for it. So Invoke-PowerBIRestMethod
becomes the Swiss Army knife. Now, once again, let's [inaudible]
as a practical example. Let's go to Google and I'll
say Power BI REST API, and I would expect there
to be great documentation. Here is the documentation, maybe something that I like to program against is
going to be datasets, and I go in here and you can see that there's different things
such as Get Dataset. So there's a URL and
then sometimes there's bodies that you have to put
together for an HTTP request. Now, what I'd like
to do is just show, here's a simple example where
you have to create a REST URL, and you know it's going to
be groups and datasets, but then you had to do
things like figure out workspace IDs and dataset IDs, and basically now we've got our
friendly little method down here, which is going to allow us
to invoke the REST API. In this case right here, what's the HTTP method. If you wanted to delete a
dataset, here's the way to do it. You call a Delete action and you have the REST URL that references
that particular dataset. Now, the reason we're going
down here is that there is a lot of really important
things that we're going to end up doing that
require that we know how to use Invoke-PowerBIRestMethod. Let's go ahead and start. Let's go back here. I think what I'll do now
is let's go into Solution. Into the Solution, I think
is when we get to step five is where we're going to
work through an example. Let's go ahead and start
with this right here. Whatever it is, let's go
ahead and run this script. As I run this script, what we can figure out is what
is the workspace ID and what is the dataset ID for this particular
dataset. That's step one. Now, the second thing
that we're going to do is I'm now going to call Get-PowerBIDatasource and figure out what are the data sources
behind this PBIX file. Let's go ahead and
run that right here. Now you can see that there are two different data sources that are behind the dataset of this
Power BI Desktop project. Moving now a little bit further, we don't just want to look at
these particular data sources. Let me point out a problem. Let's say we go back here and now we've got this particular
data source right here, and now I want to go ahead
and run a refresh operation. But what I should see is, you're not going to be able to do that because we've never
set credentials before. This is true even in the case where your data source
use anonymous access. You still have to set credentials
before you can do a refresh. What we'd like to
figure out is how to do this with PowerShell so we
don't have to do it manually. At the end of Exercise five, here is where we're going to get to. What's going to happen
is that we're going to figure out the data
sources and we're going to make the assumption
in this case that all the data sources
use anonymous access. Now, one of the weird things here is that I call Get Power BI data source, and each data source
has a gateway ID. You're saying, wait a second, my data source is
bound directly to SQL. I'm not using the open
premises data gateway. It doesn't matter, all data
sources have a gateway ID, even those that don't involve
a physical data gateway. What you're going to have to
do is for each data source, what's the gateway ID? What's the data source ID? Then we're going to create
this URL, gateways, gateway ID, data
source, data source ID. Next thing we have to
do is put together the body for the requests
that's going in, and here's just a technique
that I like to use to create a PowerShell object and
then say convert to JSON, and I basically I'm able to
take this and serialize it. Now, we're going to basically have
the Invoke-PowerBIRest patch, there's the URL and
there is the body. Let's go ahead and run
this code right here. This time when we run the code, what we should see is it's patching the credentials
for the first data source, for the second, and then
after we have done that, well, now we're good to
start a refresh operation. Once again, there's
no direct commandlet. So here we are back to groups, workspaces, datasets, data set ID, refreshes, and then we're off
to Invoke-PowerBIRestMethod. This one is a post operation, that's the URL, and you don't have to include anything in the post body. If we go back over here
and say, what's going on? What we should be able to see is, here's the patch request
that goes through and you can actually see
what's going over the network. As you're doing this type of work, I find something like Fiddler, or a similar tool like Postman, or Network Monitor
is pretty essential for debugging and figuring
out what you're doing wrong. Now we've updated credentials for anonymous access and we've
started a refresh operation. Great. Well, you're saying most
of my data sources are anonymous. Let's look at typically, how we deal with something
like the one for SQL Server. Now, in this case right
here, once again, I'm going to run through demos
that's going to parallel the steps that you will do in the
lab this time in exercise six. Let's go ahead and open up
this first one right here. In this case right here, we have a second sales
by state, pbix file. This has a SQL Data Source. What we'd like to do, and I
need to baby step through this, is we're going to start out by just having you upload the pbix file. When you upload a pbix file and its data source has never been
configured with credentials, it can't refresh until
you work through that. We're going to go ahead and get
the Power BI dataset information. The second thing that
we're going to do is that now we're going to move
through each of those data sources, and there's only going to be one, but still I had a for each loop just in case there's multiple ones. But now what you can see is, we're doing the exact
same thing before, except this time, the body of the request is
going to look a lot different. Let's go ahead and
run this right here. Now that I've run that, something is not working and I realized that I have a
small typo right here. Let me go ahead and run
that one more time, and could not find
that particular file. I don't understand why
I'm in that problem. Give me one more second. I'm sorry. I'm going a little bit
off the rails here, but the problem is
I'm running this out of the solutions folder and it doesn't have the
pbix file inside there. Sorry for being distracting. One more time, let's go ahead
and run this right here. But what we're going
to see is that now we have the code that not only
uploads the pbix file, but now is also going to
go through the process of patching the SQL credentials. If we go back here and we look one
more time at what have we done, you can see that here is the raw
requests that's going through. You had to build the URL, you had to put together the JSON to patch those
particular credentials. Now, you see how to patch those, I've showed you two examples, patching anonymous credentials
and patching SQL credentials. Now, the next thing
we're going to look at is importing a dataflow. Now, I wanted to show import dataflow because it's the hardest
example I can show. The problem with import
dataflow is you have to take a model.json file and upload it, and you cannot use
new Power BI report. The other thing is that
you have to put together a body which is based on something which is
called multipart/form-data, and long and short of it is you cannot use
Invoke-PowerBIRestMethod. You have to use the
generic Invoke-RestMethod. But up here, what I want to point
out is for situations like that, when I connect to Power BI
through Power BI service account, I can get the access token. So while I can't call through
the Power BI mechanism, Power BI still gives me
the access token that has all the permissions I need so
that I can go ahead and succeed. Once again, let's look
at the before and after. Let's go back here, and so
in our case right here, if I go to demos and now we're
going to go to import dataflow. If we go back to my site, and inside of my
particular Camp Labs, let's go back here. Let's go ahead and
run this right here, and what we should see is that this is going
to import the definition. I'm not sure why this
was running twice. What we should see is that we're able to take this
one particular file. If I go down here and just give you a quick look at the model.json file. When you export a dataflow, here is the JSON-based
text file that comes out. The one issue I want to point out is that if you do the
export of a workflow, it contains a section
called partitions. Then when you try to
import that, it fails. If you're going to do
the export-import, you basically have to
get code that strips out the partitions to basically
avoid that particular error. Now, the next thing
I want to show you, I think you should really appreciate. This is going to be
at the end of Lab 6. That is, we're going to start using these things that are
dataset parameters. Now, let's say I go back, and let me quickly open up
this Power BI Desktop project. I'm sure some of you have used this, but there's this idea that in
Power BI Desktop projects, if I go up into the query window, I can then create parameters. In this case, we have a
parameter called state. By default, I set it to California. If we basically filtered
rows right here. This is Dev Camps, so go
into Advanced view here. But one of the things I want you to see is that when we
want to filter rows, we can just say "State = State,"
and that basically directs the refresh operation to only
bring back a filtered set of rows. Now, what's neat is, if I have this
particular project and I change the state and run
a refresh operation, well, that's great because
I can basically just change what's in the
underlying dataset. Now let's go and look at what
I'll have you do in the lab. Notice that SalesByState, the default parameter value
for State is California. Now what we're going to do is
we're going to use an importName, and the idea of the importName
is when I say New-PowerBIReport, I didn't include the name
in my previous example. If you don't include a name, it just uses the name of the
PBIX file that you're importing. But now I want to include
a name because I want to upload the same PBIX
file multiple times, each with a different name and
basically set different parameters. Going back here, notice that we have State of California.
That's great. I can open it up and I can see that there is the
State of California. You can see I just now created this new environment because it's asking me these obnoxious questions. Now, let's run this script
a second time right here. This time, what we're going to do is we're going to give
it a different name, but use the same PBIX file. But now we're going to
update dataset parameters. Once again, this gets to be one of these low-level things where
you have to put together a URL with group datasets
and then default parameters. Then for the body, I'm
going to have updateDetail. Then here for each
parameter that I have, I need to have this
collection with the name of the parameter State and the newValue, which is put inside there. Then we're basically going
to invoke the rest method, and then at the very end, we're going to trigger a refresh. If I go back here and we take
a look at Power BI Dev Camp. There's the state of Florida, there's the state of California. Let's open the one for Florida. That's looking good. Once again, one of the things that I'm
trying to demonstrate is we only had to create one PBIX file, but now let's go ahead
and switch this to Texas, and switch the parameterValue to TXT. Now we'll go ahead and run this, and it uploads it with a unique name, changes the parameterValue,
and then triggers a refresh. What we can see is,
right after it's done, there is the sales report for Texas. Let's go ahead and open it
up and you see that it's not the only way to go about
doing a templating technique, but I've seen lots of
companies use this and give them a lot of
deployment flexibility. Here, I changed a parameter that affects the
filtering for a refresh. There's lots of other things
that you could do as well, such as changing connection strings, and things like that. [inaudible] to the slides. Let's see, maybe I'll take a
couple of questions right here. Does it need Power BI admin rights
to run PowerShell commandlets? That's the question and it leads into this next session: Executing
Administrative Commandlets. We'll get to that in just a second. Can we use spare pull list of users
internal/external from an app. Yeah, unfortunately, all
we can really do is get the users who are members of a workspace if we have
admin level permissions. Unfortunately, there's no APIs
or PowerShell support to figure out who has been added as a member to an app or who
has been shared a report. Like I said, we've been getting
a lot of feedback on that, so it's pretty high on the priority list to basically
close those gaps in our API. Now, next thing we want to look at is running commands at
organization scope. There are some commandlets, like get Power BI Workspace and
add Power BI Workspace user, which allow you to put a
scope parameter in place. Now, once again, let's go back here, and I'm going to take you through, what you're going to do in Lab seven. I'll go in Solutions.
Let's start Part one. In this case right here, what I'm going to do is I am
going to call Power BI Workspace, but this time say Scope
equals Organization. The idea is that once you do that, you've flipped this admin switch, without that, you're
only going to see workspaces in which you
are added as a member. Either as an admin or a contributor, or a member, or a visitor. But once you say Scope
equals Organization, you can basically see everything. If you have deleted workspaces, you'll be able to see
them with that here. You can probably see some
of these ones I deleted. Yeah, so this is just
basically a way to say, "Let's put a filter
in and only show me those workspaces that
are still active." There is a type property right here, and for v2 Workspaces, the type comes back as Workspace. For V1, the type comes back as Group, and for personal workspaces, it comes back as
personalGroup. That's neat. But now what I want to do is let's try stuff played
a little bit different. This is what you're going to do
as you work through the lab. When you call get Power BI Workspace, and you say Scope
equals Organization, we can also say, "I would like to include
a couple of collections." I can say I want to see a list
of Dashboards or Dataflows, or Datasets, or
Reports, or Workbooks. I'm going to say all here. As you work through this, what we're going to have
you do is basically running this admin basically figured out a way to come up with
an inventory report. Here's our starting point where we've said basically
get Power BI Workspace, Scope Organization and include all. Now, just so you can see this. Let's go up here, and here
is my workspace object, and if I say "/users," I
can see who the users are. Likewise, if I want to see the
reports or datasets or dashboard, I'll be able to see
those here as well. Now, remember that you have to be a Power BI administrator
to be able to run these. There two ways to become
a Power BI administrator. One, to be the global tenant admin; or two, let's say I go
into our buddy James. What we're able to
do is basically say, we'd like to put this
user into a role, and we'd like to put this user into the role and
show all by category, and somewhere down here, and I'm not finding it very quickly, it's changed, Power
BI admin, and change. Then so in some companies, they don't want to give you
tenant admin permissions, but they can give you power BI
administrative permissions. That's what you need to run this. For someone to be able to get
a listing of all the assets, inside of a particular workspace, and here's the solution
for Lab seven. We're going to have you go to a particular workspace
and basically now, you can see that you can
just force each your way through and whether you want
to put this into a text file for your report or write it to a database or what have
you just gives you an easy way to see what assets
are in what particular datasets. Now that we've seen that, there's one more thing that
we're going to look at which basically is going to involve you requiring
administrator permissions, and that is, we want
to be able to export activity in our Power BI
workspace right here. Now, what I'm going to do
is I'm going to go back and I'm going to look at your
exercise 8 right here. Let's start with this. Old tricky PowerShell code days back, how far back you want to
go to get the history? I want to go back three days. So there's this cute little PowerShell
syntax where I can now say, let's create an array
between 3 and 0. So it's going to be 3, 2, 1, 0, and now let's go through that
and let's figure out how to go back three days then two days and would just basically e-mail
my function with the date. Now, for each day, this function is going
to run right here. Here you can see that
we have a call to get Power BI activity event. So I'm going to go
ahead and run this, and as I run this, I
realize I can't run it on my new environment because
there's no activity yet. I'm going to pick an
environment that is a little bit older that I've
been doing some things in. The idea is that now, as you run this right here, and as this thing begins running, what we should see is that
if there's no activity, it doesn't want to basically
create any particular file. But if it finds activity, it's going to go ahead and
create one of these CSV files. Now this is just going
to walk through, and each day, it's calling in and getting the
activities inside here. If I open one of these up, just to give you a look at what
is inside the activity log, if you want to see what user did, what type of operation. Once again, typically
companies are exporting this type of data because they
want to see usage patterns. What users are looking
at what resources, what users haven't even logged
on to Power BI in a week. That's the type of information
that can be surfaced by using the logs. Great stuff. I see a question. Can we pass a
security group instead of a user? I think that was way back
applying the membership, and the answer is yes, you're able to be able to do that. Now, there's two more
sections we want to look at, and one is going to be running
scripts as a service principle. So quite often, companies come
to us and say, we have users, but we don't want user
identities running our scripts, we want to run them as a more
generic application identity. So we can do that. But unlike user-based access if you want to use a
service principal, you do have to create an
Azure AD application, we're going to use the exact
same PowerShell commandlets, but only user-based
access allows you to do it without creating
an application. There's also a tenant level
setting that you have to enable, and I'm going to show
you that real quick. Now, the important service
principle limitations. Unfortunately, service principles
cannot run Admin API operations. That's another gap that we
are trying our best to close. We're going to make
some advancements over the next six months
in our first wave. But today, it's
important to understand that if you're a user and you have
admin permissions in Power BI, you can do more than
service principles can. In general, service principles can create new workspaces then
they're the owner, the admin, and they can access existing workspaces where
they've been added as a member. Now, just to show some things here. Let's say that I want to get
the service principle running. Let's see if I can do this
fire drill real quickly. Step 1 would be to go into Azure AD, and what we have to do is
we have to create a group, and I'm just going to
create a group, which is called Power BI apps. After you've done that,
step number 2 is, back here in Power BI if
I can remember how to get to the Admin Portal and
then the Tenant Settings, and then way down here
in Tenant Settings, there's going to be
Developer Settings, which is initially turned off. So you have to enable, allow service principles
to use Power BI APIs, and then we're going
to add that Active Directory group inside there. Now, once we've done that, our next step is to create
an Azure AD application, and once you create the
Azure AD application, it's going to have an application ID, and then you basically create either a certificate
or an app secret. But the idea is, I've
already created that, and I've created the group. I've configured it at the
tenant level to allow service principals who are in that group to talk to the
Power BI service APIs. Note that that doesn't really
give them any permission, except they can create new
workspaces and they can get access to workspaces that
you give them access to. But the service principle
cannot fish around the tenant and figure out
what workspaces are there. You know, only a user running with admin permissions is able to do that. Then we create the application. When you create the application, you configure it as TYPE equals Web, so it's a confidential app. There's not going to
be a redirect URL, but you need to add a client secret. Then the other thing that confuses
people is you're going to take the security principal and
add it to a workspace. You're not going to
need any permissions, and if you add permissions into
that Azure AD application, they're not going to have any effect because service principles today, never benefit from
having permissions. They benefit by being a
member of a workspace, and they're working on adding them as an admin
member to things like capacities and to things
like, a data gateway. Once we've done that and we've
got our app in that group, then we add it as a member. One thing that we might want to do, for instance if I
come back here and I go to my particular workspace, and then in this
particular workspace, let's go to "Access". It can find my app ID and then
it could add that inside here. That works just fine. However, there's one thing
that's a little confusing, and that is, let's look
at the PowerShell. If I want to add my service
principle as a user, let's go back here where I
got some service principles. So here I've got this Dev Camp labs. Now I can say add Power
BI Workspace user. We're going to say scope equals
organization, workspace ID. But now we're going to
say principal type equals app and Service Principal ID. That is not the app ID. If I go back here, and we go back and we find our app, you don't use this. You use this to log in, but you don't use it to
configure permissions. Instead, the Service Principal ID, one easy way to find it is go over to the right where it says Manage
application in this instance, drill inside here, and
that is the object ID. So it's the tenant specific, and the idea is that you're
going to add this inside here. Now that we have done that, let's go ahead and run this and
return sampling forbidden somehow. Let's try this one more time. I think I was putting it
into that other workspace, so now let's go back
into my other tenant. I have tenant schizophrenia. I apologize for that. But now that we've run the operation, let's go back here one more time. What we should see
is if I close and I reopen that one more
time, let's go to Access. You can see that now
programmatically, I added the service
principal inside there. Then I will run one more
piece of code here, and that's log in as
service principal. When I say log in as service
principal inside here, what you're going to see is
that there's the Tenant ID. This is the application ID, not the service Principal ID. Once again, when you're logging in or authenticating the Service
Principal, Application ID. When you're setting permissions on sampling so service
Principal can use it, it's Service Principals Object ID. But now if I go ahead
and run this right here, what we can see is that now
the Service Principal can see any workspace that
I've added him into. I'm not sure what pronoun the Service Principal
uses, I guess I said he. But now let's go ahead and final thing I want to show is let's go back to one of
my workspaces right here. Let's go to the Access tab, and let's go ahead and remove that. Once again, let's go back
here and run this one more. Now you can see that this guy
is only going to get access to the workspaces that I
have given him access to. >> Hey, Ted I just wanted
to do a quick time check. We've got about three
minutes left and we have about 20 or 30 questions, so I wanted to do a time check here. >> Tell you what, I
have one more section, but I can talk through
it in about one minute. >> Okay. >> Here, this line right here, one of the things I wanted
to talk through is, if you run a Service Principal, one of the things we
see with a lot of our customers is that they want
the Service Principal available, upload a PBX file or go to an
existing dataset and refresh it. The issue is, if I as a user, upload a PBX file, I am the dataset owner, what's called configured by
property of the dataset and only the configured by
owner can refresh it. In order for the
Service Principal to be able to perform a refresh operation, one of the things it has to do
is to do a take over operation. But now the problem is, once
you've taken over the dataset, the other user's credentials
don't have any effect. After taking over the dataset, the first thing you typically have to do is refresh the credentials so now that dataset has credentials that are owned
by the Service Principal, then you can trigger
a refresh operation. I thought that was the last section, but one more slide here. That is that, in addition to the command list that
we looked at now, one of the things that's come
out into public preview over the last couple of months is a new command at library
for On-premises Gateway. What you can see here is that
this allows you to do things. If I just point to the real
high-value one Install Data Gateway. There is PowerShell that
allows for completely silent, unattended installations
of a Data Gateway on a VM or on one of your machines. One other thing I have to
point out unfortunately, is that this only supports
PowerShell seven. You can not use this commandlet library with PowerShell five or
the PowerShell ISE. You have to use the PowerShell
seven and working with basically Visual Studio code with the PowerShell extension
is what I would recommend. Finally Kelly, I am now done
with all the materials. How do you want to handle questions? Do you want to ask
me the questions or? >> Yes, it's your show, so let's go ahead and scroll through. >> You can stop me. >> I don't think I can do
that, that's for sure. We've got a few questions
about is it possible to run user activity within
dataflow and have an incremental refresh
sit up for one day. Getting bearer token full my admin account is
not possible as we use Okta third party and I'm having to copy and paste
the bearer token everyday. Can this be automated. >> That was a lot there. Is it possible to run user
activity within dataflow? >> There is another thing, we've
only got about a minute left and we've got about
20 or 30 questions. I'm thinking, should we direct
folks to the community as well? Maybe just post on the community blog and then you'll
be able to answer them there. What are your thoughts on that? >> Let's say I go to
community.powerbi.com inside here. Now we go and we find those wonderful forums
that we have inside here. I think I saw developer. Where would you put your PowerShell questions? >> Scroll down and let's go to your blog. Let's keep it altogether. If you go to the webinars, scroll down a little bit more, there we are, webinars
and video gallery. Once you select that, and
then do recently posted, it's on the left-hand
side, there we are. Then you'll see that this
your first series there. If you select that
there, it'll have links. It'll take you number
one to the first one. Then we can either ask questions
here or in your upper blog post, which you just recently
posted, I believe. It's up to you. Let's have a look. We're doing this on the fly folks. Let me just go here,
I'll go to your blog. I think where you posted
the recent instructions. I'm just going to that
now slowly, I apologize. That's the whole music. >> I'm not sure if I will put
out a new blog this week. >> Yeah, you could do that. >> I could supply the
links to everything. Then that would be a place that
people could hang questions off. I can work with you
to get something up. I can certainly put
something together easily. I think we just ask them to
look at the community blog for the post that comes over
the next couple of days. >> We've got a couple here. We've got the DevCamp session in
this DevCamp session, which was on 9/14. I've got your blog post here. Let me just give you that
link and then you can keep all the questions within that
if you'd like to do that. I've just put that in the
webinar one. Here we are. That is the developing
for Power BI using that [inaudible] we want the
PowerShell one, sorry. I'm just looking for Ted's one here. Here we are. Was that
your latest update Ted? >> I think it's the latest blog
post I put out, that's correct. >> You can either start a thread
or you can go to this one. What would you prefer Ted? >> I think may be
just go to that one, seems we could put something
in and give them something now just in case people have
questions this afternoon. >> Yeah, I think that would be great. Then what I can do is, I can copy
and paste the questions from this event and put those into
that blog post or folks, if you'd like to let us know. Just respond and say yes, no whether you'd like to repost your questions or
whether you'd like us. Thanks for the compliment
on the accent. We'll copy them all just in case and then we'll save everyone the trouble, and we can answer them there. But I think that's
all the time we have today Ted sorry, and sorry everybody. I know his content is fantastic. We learn so much every time. >> But I take the whole hour, I hog the whole hour doing that. >> No, it's fantastic. You're
teaching us. It's the best way. >> Well, thank you everyone. Certainly appreciate everyone coming and I certainly hope people
go through that lab, we get some hands-on and become very productive with the Power BI, PowerShell
infrastructure. >> Absolutely. Give us
about an hour to gather all those questions
because it's not as easy to do that, it has to be manual. So give us about an hour and
then we can go from there. What do you say about that Ted? >> That sounds great. >> Awesome. Well, have a great day, a great day evening, great
morning everyone and great week. Looking forward to our
third DevCamp session. What is the title of that one Ted? >> That is programming with
the Power BI JavaScript API. It's very specific to
Power BI embedding. We'll be working a lot
with JavaScript and TypeScript over on the client side, doing all kinds of tricky things with reports that are embedded
in our custom applications. >> Excellent, can't wait. All right, take care everyone. >> Thank you.
