[MUSIC].
>> Module 6, Part 3, Configure Data Permissions.
Hello, I'm Peter Myers.
In this demonstration, I will
explore two data permission scenarios.
First, by creating a dynamic role,
and then by developing a data-driven design.
Here I am in Power BI Desktop
ready to create a dynamic role.
I go ahead and on the left,
I switch to the model view.
This gives me an understanding of
the model diagram in terms of tables and relationships.
I notice specifically
the relationships between the Region,
State, and Sales tables.
Notice the direction of filter propagation,
the arrow pointing from Region to
State and from State to Sales.
That means that filters applied to the Region table
propagate to filter the State table and in turn,
the State table filters filter to propagate to Sales.
When a filter is applied on
the Region table, therefore,
it filters to the states of the region and in
turn to the sales of the states.
Now it's important to understand at Tailspin Toys
that they have six regions
that are defined geographically,
grouping the 51 US states,
which includes Washington DC.
The objective is to create
a role that filters the Region table.
To do that, on the Home ribbon tab
I click "Manage roles".
Then in the manage roles window,
I click "Create" to create a new role.
I'll name the role "Region".
Then in the tables list,
I click the ellipis (...) and I apply
a filter on the Region column of the Region table.
Now the DAX expression that's automatically generated is
filtering by a static value of "value".
But I'm going to replace that to use
the DAX function, which is USERNAME.
This function returns a string value
representing the value of the authenticated user.
However, in Power BI embedding situations,
the function actually returns
a value determined by the app,
as you'll soon see.
I go ahead and click "Save",
and now I'm ready to test the role.
I switch on the left to report view,
and then on the Modeling ribbon tab,
I then click "View as".
Now, this allows me to check "Other user".
Now what that does is it lets me enter
the value that the USERNAME function will return.
I'm going to enter the value of "Midwest".
Then I check the Region role.
What I'm asking is to apply
effective permissions that will enforce
the Region role with the username function
returning "Midwest". I click OK.
Now notice the yellow banner
across the top of the report page
indicating that the report is being
filtered by the Region role for Midwest.
I don't see any filter change and that's
because region isn't shown on the page yet.
If I do open the dropdown list, the slicer,
I will see there is only one region available.
If I then select the Sales Map tab,
I can see data only for the states of the Midwest.
I'm satisfied that the role is doing its job.
I click "Stop viewing" to
remove those effective permissions.
I'm now ready to save
the Power BI Desktop file
and republish it to the workspace.
On the Home ribbon tab,
I click Publish and
then I select the Sales Reports workspace.
Then I confirm replacing to
overwrite the existing report and dataset.
I now switch to Visual Studio Code.
Then I open the appSettings.json file.
There's a third section added called "Current user",
and it allows testing data permissions.
In the region setting, then I'm
going to enter the Midwest.
I save and close the app settings file.
Next, I expand the Services folder
and open the Power BI API services code.
I now add some new logic to
the "get reports embedding" data method.
Specifically here, when it enumerates the datasets.
This new logic creates
an effective identity for the very first dataset.
We can see that the username of
the effective identity will use the region appSetting,
and it will map to the role named Region.
Then I scroll down and where
the generate token request method is used,
I go in, and I add some additional code that is
going to pass in that effective identity object.
Now I'm ready to test the app.
I save and close the file,
and press F5 to run.
Then in the app, I open up the Sales Analysis report.
Here in the map visual, it can be seen that
only the states of the Midwest have data.
I close the web browser session and I
returned to Visual Studio Code and I stop running.
In preparation for the next data permission scenario,
I return to Power BI Desktop.
Then on the Modeling ribbon tab,
I open up "Manage roles" and I
delete the Region role. Then I click Save.
In this second data permission scenario,
I'm now going to develop a data-driven design.
Let me switch back to the model diagram.
Then on the Home ribbon tab,
I'm going to get data from a text or CSV source.
I have the CSV file named "app user".
I go ahead and open it.
Then I see a preview of the data.
The file contains two columns
describing region and username.
In fact, it's a mapping of
users and the regions to which they're assigned.
I go ahead and click "Transform
data" because there's work to be done.
Here in the Power Query Editor window then,
the transformation that I will apply is to promote
the row one values to become the column headers.
I do that on the Home ribbon tab by
clicking "Use First Row as Headers".
Now let's take a moment to look
at this data more closely.
Notice rows 1 and 2 for Midwest.
That tells us that there are
two salespeople assigned to that one region.
Then in the username column,
notice that Ty Johnston on row 3 and also on row 8;
this individual salesperson is assigned to two regions,
specifically New England and Southwest.
The results of this query will be loaded to
become what we call a bridging table in the model.
A bridging table, as you will see,
defines a many-to-many relationship,
in this case between regions and app users.
On the Home ribbon tab, I click "Close and apply",
and that adds that query as a new model table.
Now that the table is added,
I notice also that a relationship
was automatically created,
but I notice that the filter propagation isn't correct.
See how the relationship from Tegion to "App User"
is pointing towards "App User".
We need it to go in the other direction so that
a filter applied to "App User" will propagate to Region,
which will propagate to State then to Sales.
To edit the relationship,
I simply double-click that relationship
between "App User" and Region.
There are two changes that I'll make
in the cross-filter direction dropdown.
I switch to both directions.
Then to ensure that the filter will be applied
during row-level security scenarios,
I check the checkbox to apply
the security filter in both directions.
I then click OK.
Then notice in the relationship diagram
that the arrow is pointing in both directions.
What matters is that
"App User" filters will propagate to the Region table.
Lastly, I hide this table.
In the table header, I click
the eye icon and I ensure that that table is not visible.
The purpose of this table is
for security design purposes,
so it shouldn't be made visible to report users,
including the Q&A experience.
I'm now ready to create a dynamic role.
On the Home ribbon tab,
I click "Manage roles",
and then I click "Create" to create a new role.
This time the name of the role will be "App User".
I will apply a filter on
the "App User" table and
specifically to the Username column.
What will be passed to filter
the Username column will be the USERNAME function.
Remember this function will return
a value that our app determines.
I then click Save.
Switching back to report view,
let's test this role.
Clicking "View as",
and then I'm going to enter
ted.baker@tailspintoys.com and enforce the "App User" role.
Let's see what the data looks like.
In this scenario, Ted has
permission only to see the states in Midwest.
I'll "View as" again,
and this time I will try Ty Johnston.
I can see that Ty,
due to that many-to-many relationship, has
the ability to see Southwest and New England.
It seems to be working, so I click "Stop viewing".
I set that the decomposition tree
is in fact the default view.
I save the Power BI Desktop file.
Then on the Home ribbon tab, I publish,
and I overwrite the existing version
in my "Sales Reports" workspace.
Switching back to Visual Studio Code,
I go ahead and open up the app settings.
It's now because we're not using region as a setting,
we're actually using the "App User".
You can think of this as
being the authenticated user of your app.
Let's test it for Ty Johnston.
I save and close the app settings.
Remember this is just for testing purposes.
Then I go ahead and open up the Power BI service.
Just a couple of changes need to be made here:
For the effective identities object,
it now uses the "App User" setting in the config,
and it now uses a role named "App User".
I make those changes and save and then close.
Now I'm ready to go ahead and test,
so I press F5.
Now viewing the app as Ty Johnston,
who has permission to see two sales regions.
When I open up Sales Analysis
and then switch to the Sales Map,
we can see that the app is correctly
enforcing the permissions for this particular user.