Power BI Row Level Security 3 Dynamic with full access for certain users

We have already looked at a very simple dynamic level solution example using two tables. Now We move to dynamic security where other users still have full access

At this point it should be noted that I am the Admin of the App Workspace

This means that I can see everything at dynamic level.

Create a New report

for this example, a brand new App workspace Area and report is created

2 tables are created in Power BI to test

Employee Table

Sales table

Add a Manager Role

Notice the isManager column within the Employee table. It is set to 1 for the manager and 0 for everyone else

Manage Relationships

We now need to amend the Employee role slightly that we set up previously within Manage Roles.. This is our previous Logic

the following code can be added to Table filter DAX expression. We will look at this in more detail.

IF(
    MAXX(
       Filter(
      'Employee',
      'Employee'[Email]=USERPRINCIPALNAME())
      ,'Employee'[IsManager]) = 0,
   'Employee'[Email]=USERPRINCIPALNAME(),
1=1
)

In order to get an understanding of the DAX lets go to the inner DAX and move outwards.

Filter: Allows you to manipulate data context to create dynamic calculations. Which means that it returns a table that has already been filtered.

In this case the <table> to be filtered is the Iterator. the <filter> expression is a true or false boolean that gets evaluated for each row of the table. this is the Row Context . in this case is it true or false that the email in the data is equal to the email currently set within UserName()

FILTER is not used independently, but as a function that is embedded in other functions that require a table as an argument.

MaXX: Evaluates an Expression for each row and returns the largest numeric value. So it takes the result of the filter (the whole row from the table object) and Brings back the Max number.

So in this case, we can create a quick measure to understand what it is doing using my email address hardcoded into the measure

Maxx Measure check = MAXX(FILTER(DimEmployee,DimEmployee[EmailAddress]=”Debbie.Edwards@emailaddress.com”),DimEmployee[isManager])

I am not the manager, so in this instance our Maxx Iterator comes back with 0. It is not true

If: the results of the MAXX is 0 (Not true) then we just show the data related to that person

ELSE: If the User is a manager then show everything. This DAX expression is a simple way of dispaying everything

You could also use this DAX expression

if(
     LOOKUPVALUE(‘Employee'[IsManager],’Sales Rep'[Email],USERPRINCIPALNAME())=1,
      true(),
    ‘Employee'[Email] = USERNAME()
   )

LOOKUPVALUE  

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…[, <alternateResult>])

  • result_columnName is an existing column you want to return. [IsManager]
  • search_columnName is the column where the lookup gets performed [Email]
  • search_value is related to the actual value you are searching for. Not a column. USERPRINCIPALNAME()

Brings back IsManager where the userprincipalname is the current Email address

Go back to Modelling and Manage roles. This is currently set to [EmailAddress] = USERNAME() but we are going to replace with the above IF

Republish and go back to Service

Back in Datasets > Security Add your emails as a role and save

Again you can test as role from here.

Test one

In this case:

User 1 is Admin and can see all the rows

User 2

Just the one row of data is displayed for user 2

User 3

User 3 sees all the records because of the IsManager Flag

Conclusion

If you publish the workspace as an app, Row level security will work for all end users, except for Admins, Members and Contributors in the workspace. These three roles can see all data, whether in the workspace or app.

for this, My user 1 and User 2 would be added into the App and then they can see the reports with Row Level Security in Place

Both Static and Dynamic Role level security need a fair amount of maintenance.

Another security logic area that need to be looked at in more detail is when you want users to see everything in the top level report, but they can only drill down on the detail if they are allowed to see the detail.

Power BI Row Level Security 2 – Dynamic Security

We have already looked at a very simple Static Row level solution example using two tables. Now We move to dynamic security

Here you create 1 rule only and the users are added to a table. Each logged in user will see their own data.

Dynamic Row Level security therefore uses a Users log in credentials to filter datasets.

Power BI Desktop Get Data

Open up Power BI and click on recent Sources because we are going to reuse the Adventureworks data

Select DimEmployee, FactResellerSales, DimProduct, DimProductCatgory and DimProductSubCategory (Import)

You can go to the relationships pane to view the relationships of the data brought in

For this example, every employee wants to be able to view their own information, but no one else’s.

The Email address in DimEmployee contains the email address of the Power BI account

For this example, we are going to give two employees email address that use Power BI within our own company. (Because obviously, we don’t have Adventureworks email addresses set up with our Power BI Service)

At this point it should be noted that I have Admin Access in the App Workspace that we are publishing to. This means that I have full access regardless of row level security

Choose two other team members with Power Bi Pro Access to add to the dataset in the next step. This will be set in the next step of the process

Power BI Desktop. Create New Measures

First of all, I am going to create two measures on the Employee Table. Click on … Against Employee in Fields and Select New Measure

Repeat for Employee Principal User = USERPRINCIPALNAME()

And a measure in the fact table Now = NOW()

What is the difference between USERNAME() and USERPRINCIPALNAME()

The USERNAME() DAX function returns the user’s domain login in the format domain\user) locally, but returns the user principal name (the user’s login credential) in the Power BI service.

Therefore, security role filter expressions, user permissions tables, and any other dynamic user functionality added to Power BI datasets should align with the UPN email address format provided by USERPRINCIPALNAME().

Power BI Power Query. Set up UserName

Click on Transform Data from Home

Go to Transform and select the email address column. Go to replace Values and replace linda3@adventure-works.com with another email address (I am using my own email address here)

Do the same with a couple more email addresses for the two test addresses (Internal email addresses of your choosing)

Once completed Close and Apply.

Power BI Desktop. Create Visualisations in Report

The report is specifically to show the dynamic row level security so I’m not setting up anything special

  • Card: User (Measure above)
  • Card: Now. (Go to Modelling and format to change the format of Now (As above))
  • Table: Title, FirstName, MiddleName, LastName, EmailAddress, BirthDate, EmployeeNationalIDAlternateKey, MaritalStatus, Phone, SickLeaveHours, Status, Gender, VacationHours
  • Column Chart: Axis – Order Date, Value – Sales Amount
  • Pie Chart: Legend – EnglishProductSubcategory, Value – Sales Amount
  • Card: Principal User (Again, measure created above)

Note the User Name shown in Desktop. Before we set up the roles lets simply publish what we have into the service

I will again Publish to Adventureworks demo. Click on the new report from the App Workspace you have chosen

Note now we are at service level my email address has become my user name, which now matches the Email Address in the Employees Table. My Email address is used to authenticate my Power BI account.

As an admin in the app workspace security doesn’t apply to this email address

Go back to Desktop

Set up Security

Modelling > Manage Roles

If you try viewing as Employee in Desktop you will see no data because within desktop, your email address isn’t used as your user name. Republish and replace your Report

Service

In Service, as before, go to Security by clicking … against the data set within the App workspace.

Add all the email addresses of members here. If it matches employee email they will see their information. Add all your email addresses as members above

Top tip. Obviously you don’t want to be adding single emails here so the best thing to do is to create an Azure AD Group and add all your users into this. They can then be managed centrally and we simply add the group here

I also add the other two users into Members. if you click … by Employee you can test as role

Note that now I can see my sales information and personal details and can only see my information. I can change to another email to see their details.

How does this effect Access in the App workspace and App?

There are two places to look at the reports. the App workspace where developers contribute to the work.

Access Levels Admin – Member – Contributor – Viewer (We dont tend use viewer within the app workspace)

AND the App where the reports are consumed by people in the business. the access level here is basically Viewer with a few possible add ons from contributor if required

If the email above isn’t in th App Workspace, the user cant access from here.

If the user hasn’t been added to the App they wont be able to view the report contents. Lets have a look at some examples

The Model

for Dynamic Security you must have a table with your user details (Email Address) The simplest design is to have this in a dimension that connects to the fact table

The more complex the table relationships you will have to check that your Security still works.

If your table is not joined to the DimEmployee table, you will see all the records.

If you have issues with your security look at your relationships and what tables are being used in your reports

Conclusion

The logic within this post answers one specific question. But what if we want certain users to be able to see everything? The next how to guide will go into more detail on how to set up users who can see everything

Power BI Pipeline Issues and Access requirements

In a previous blog we looked at setting up a Pipeline

However, we have found an issue, when you Deploy to Test or Production. the issue resulted in getting a better understanding of the permissions you need in order to create and control the process

This image has an empty alt attribute; its file name is image-47.png

The Deployment hangs and doesn’t continue on to creating a new Premium App Workspace. If you click refresh, a workspace gets created but it is none premium. In other cases nothing gets created at all.

This is due to the person who is setting up the Pipeline. They may be Admin in the initial Power BI App Workspace but they may not be able to continue on to actually create the Premium Workspaces

In our example, the Power BI Administrator set up the Premium App workspace and then assigned myself as admin. I didn’t set it up.

there are two ways of doing this, especially when working against Dev and Test. They can be set up as Power BI Embedded in an A SKU

Or you can have a Premium capacity Workspace (You must have this if you want the Production area)

Example using the A SKU

We are using Power BI Embedded created in Azure

I am able to create the Premium test and Premium Production environments. But after testing with a colleague, the issue happened.

Lets have a look at what we have set

Power BI App Workspace

We are both Admin

Azure Power BI Embedded

In Azure. Search for Power Bi Embedded. We already have this set up.

Go to Access Control (IAM)

We have User 2 (Rebecca) set as Owner. We also tried this at contributor level but the issue still occurred.

  • Contributor – Lets you manage everything except access to resources
  • Owner – Lets you manage everything including access to resources

You need to go to Power BI Capacity Administrator. I was previously set as the capacity administrator to the Power BI Embedded Capacity. Once Becka was added here we were able to successfully move through the Power Bi Pipeline steps without anything hanging.

Therefore, If you are a user in charge of setting up Power BI Pipelines, you must be a Power BI capacity Administrator

To Test. Do you have to be Owner or Contributor in order to use the Power BI Pipeline once it is set up?

Azure Power BI Premium capacity

Power BI Premium is not created as an Azure Service.

Power BI Premium is Managed by the Power BI administrator within Power BI service. Settings and Admin portal

You actually don’t have to be a Capacity Admin in Premium, but do need Capacity assignment privileges.

The Capacity or Power BI Service Admin can arrange for this to be sorted
And you need to be able to create new workspaces,

that’s also an Admin setting.


https://docs.microsoft.com/en-us/power-bi/create-reports/deployment-pipelines-troubleshooting#why-di…

Pipeline Access settings Matrix

Power BI ServicePower BI EmbeddedPower BI Premium
Set up the PipelineAdmin – Perform all other actions below. Assign and remove workspacesPower BI capacity Administrator
Use the Pipeline Dev to TestAdmin – Perform all other actions below. Assign and remove workspaces

Member – View Workspace Content, Compare Stages, Deploy reports and dashboards and remove workspaces.

Dataset Owners can be either Members or admins and can also Update Datasets and configure rules

Contributor – Consume content, Compare Stages and View Datasets.
Owner? Contributor?
Use the Pipeline Test to ProdAs aboveOwner, Contributor?

Power BI Deployment Pipelines

In May 2020 a new pipeline appeared. Deployment Pipelines

Having worked with Devops, It looks like it is identical to the Pipeline that allows you run builds, perform tests and release code to the various production environments.

Power BI deployment Pipelines are a new way for BI teams to manage the content lifecycle.

It is only available in Power BI Premium

Another current restriction is that it doesnt work with dataflows

There are some great reasons to use deployment pipelines

  • To Improve your productivity
  • Faster delivery of your updates
  • Reduction of manual work and errors

Lets see how it works with an example

Requirements

  • The Workspaces must be in Premium capacity (You can set up the Dev and test areas on an A SKU to save money)
  • The Developer must have a power BI Pro License
  • The developer must be an owner of the data sets in the target workspace

Setting up the pipeline Example

In this example, I want a development Test and Production area.

Each of these areas has a separate data source. One each for Dev Test and Prod (But this might not be the case for your own development)

The first instance we will create a dataset that contains the dataflow.

You need a workspace with a diamond against it (Which signifies its in Premium capacity)

You will need to be Admin in the workspace to carry out the task of creating the Pipeline

At some point I want to check against Member access

The Report

A test report is created over the development data (Which is a table in the Dev Azure Database)

You can set up power BI to move to different environments using the following guide Moving Power BI to different Environments (Dev Test Production etc)

I have parameters set for each environment

At this point, make sure you change the current value of the parameter to check they are all working. This report has been published up to the Premium workspace

You don’t have to do this. You can use Rules within the Pipeline if you wish.

Start a Power BI Deployment pipeline

The Look and feel of the Deployment Pipeline is great

Create a Pipeline

Now we are into the actual main Pipeline area. You can only assign one workspace to the pipeline. When we move through the pipeline it automatically creates the other workspaces for you.

Our Pipeline testing workspace is going to be set up as the development area

Assign a Workspace

You dont need to start with development. You can start with test or Production but in this case we are going straight to the Dev area. Also you don’t need to have all three environments. This now gets assigned to the Development pipeline

At this point you can see what will be part of the Dev Pipeline. Show more shows you the content within. At this point you can visibly select items that you want to deploy up to the next stage, but in this example all three of them is required.

Rename Pipeline Testing to Pipeline testing (Dev). Click on … and go to Workspace settings

Publish App

Your report consumers and testers will want to use your reports and dashboards as apps. You can create Apps at every stage. To do this click on … and Publish app

Deploy to test

Clicking Deploy to test creates a copy in your Test area

It will copy your Reports, Datasets and dashboards into the test area. If you have dataflows you should note that these currently don’t get included

Rename to Pipeline testing (Test) if required

At this point we may want the test area to be connected to a different data source than the development environment. Because we set up Parameters in the pbix file to change to different databases, we can use parameter rules. If if you dont have parameters set up you can create a data source rule.

Dataset Settings

At this point. go back to your New Test Premium Workspace.

Against the data set click … and Settings

I have changed the Parameter to the correct one

Now refresh the credentials

And repeat when you get to Production App Workspace

Deployment Settings (Back in the Pipeline)

Get to Deployment Settings when clicking on the lighting bolt

Parameters have been set in the pbix files so these should be used in this example.

You can use rules(Below) if you don’t have parameters but remember to check your Data set settings first.

Because the source is a database, the pipeline knows to ask for a server and a database. Make sure your Database is set up correctly first within Service.

Deploy to Production

Clean up and set up the same rules (Ensuring after deployment you check your Data set Settings before setting up the rules).

Updates to Development Area

You can also just deploy specified items that you have worked on

For this test, go back to the desktop and add a couple of visuals. Then Publish into the development workspace.

Look to compare the changes

The comparison shows that two items have changed. Deploying into test will copy the new information across

You will also see icons for new and deleted.

Now we can see that production is still using the previous datasets, reports and dashboards. we wont copy across until we are happy the changes are correct.

These are now three individual Workspaces with individual data sets and reports. You will need to set up scheduled refresh for each area.

You can also publish downstream if required by clicking … If the item is available

Limitations

  • Not for Dataflows or Excel items
  • Premium Only

The Production workspace must be in premium. You can use A SKU or Power BI Embedded to save money. (A Sku’s can be set up within Azure and are Test environments. they can be paused)

Buying and pausing a Power BI A SKU

It doesn’t currently plug into Azure Devops. Hopefully this will be coming soon.

Workspace using Dataflows

I’m moving across to another workspace now. lets have a look at the Lineage

There is an Adventureworks Dataflow which connects to a dataset and a report.

go to Pipelines. Create a Pipeline and then…..

In this instance, The Dataset and report that sits over the dataflow is specifically selected.

Power BI is really not happy about this

The two items are copied across.

If we want to set up rules for this workspace…..

No actions are available. Your dataflow is sat in the Development area. You cannot change it

Lets do the same for Production

If you go and look at your workspaces

There are now 3 workspaces. Lets have a look at Lineage to see how the dataflow is shown for test.

Your data cannot be viewed because your dataflow is not supported.

Considering we have spent a lot of time supporting people to move to dataflows, this is a real problem

https://community.powerbi.com/t5/Service/Dataflows-are-supported-in-Power-BI-Pipelines/m-p/1173609#M100117

Comparing Dev to Test

Still looking at the reports that use the dataflow. Lets see if it can compare. The pbix files is opened and amended. then published to the dev Workspace.

At least it tells you which items have changed

With the push to move to dataflows to separate transformation to the actual creation of DAX analytics, it seems like an urgent requirement.

Hopefully this will be resolved soon.

Best Practice

When this is all up and running it is recommended to separate the datasets from reports and dashboards. to do this use the selective deploy

Plan your Permissions model

  • Who should have access to the pipeline?
  • Which operations should users with pipeline access be able to perform in each stage?
  • Who’s reviewing content in the test stage and should they have access to the pipeline?
  • Who will oversee deployment to the production stage?
  • Which workspace are you assigning and where in the process are you assigning it to?
  • Do you need to make changes to the permissions of the workspace you’re assigning?

Power BI Row Level Security 1 – Static Security

Enabling different roles and giving users access to different levels of data is called row based security.

For example; Managers of different departments should only see data for their own department. Higher level users should be able to see everything

To show how Row level security works, we are going to use the Azure Database AdventureWorksDW

There are two types of Role Based / row level security. Static and Dynamic. The first example will show static row level security.

Row Level Security and Role based security are the same thing.

Power BI Desktop. Get Data

Open Power BI Desktop and go straight into Power BI Desk top. We are going to create the data within Power BI

If you haven’t used this before you may need to log in. Username welcome1 PW trngPeak1

We are going to import two tables, DimSalesTerritory and FactResellerSales

The Report will be very simple as we just want to look at row level security for each Territory

After importing the tables, load the data

In relationships ensure DimSalesTerritory is connected to FactResellerSales by the SalesTerritoryKey

Power BI Desktop. Create Visuals

For this simple report we want the following visualisations:

Stacked Column Chart

Axis: DimSalesTerritory, SalesTerritoryCountry

Value: FactResellerSales, SalesAmount

Card

Field FactResellerSales, SalesAmount

Slicer1

Field: DimSalesTerritory, SalesTerritoryGroup

Slicer2

Field: DimSalesTerritory, SalesTerritoryRegion

Creating Roles

Role 1 We have a sales Manager that oversees USA

Role 2 We have a sales Manager that oversees Europe

Role 3 is for Pacific

In Power BI Desktop there is a Security section within the modelling tab

Click on Manage roles and create a new roll

  1. Create -New Roll. Click … to rename as USA Sales Manager
  2. Click … Next to DimSalesTerritory > Add Filter and Choose SalesTerritoryCountry and set up for “United States”
  • Same again (Create New Role) for the Europe Sales Manager
  1. Same again (Create New Role) for the Pacific Sales Manager

We now have three roles set up.

Testing roles in the Desktop

To test roles. Go back to Modelling > Security and View as

Stop viewing to go back to viewing all data without Row Level Security

Row Based Security in Service

Now we have three roles, these roles need to be assigned in the service

Publish your reports into Power BI Service. I am publishing to my App workspace Adventureworks Dev

To create an App workspace

https://docs.microsoft.com/en-us/power-bi/service-create-the-new-workspaces

Go to Power BI https://powerbi.microsoft.com/en-us/landing/signin/

Go to the workspace and check your report is available

The new Report is called RoleBasedSecurityTestAdventureworks

Next go to Security. Click on … Settings on the data set

Testing roles in the Service

Before assigning roles to a User or Group (As above), you can click on … against each role and Test as role

Once set up, it doesn’t matter how many times you republish a model. It wont cause issues with the security. This kind of security is called Static security because the values have been determined in the DAX expression in the desktop file.

Once you have tested your roles you can add users against each role.The Report is now ready use

This is fine when you are implementing a simple solution above but becomes a pain if you have, for example thousands of roles. employees who want access to their own payroll information.

The next how to guide will go through a basic dynamic security example

Power BI Lineage and Impact Analysis

Another new feature from the May 2020 Microsoft Business Applications Summit

This became available in April 2020 and its a fantastic feature. Lets have a look at it

There is a lot going on in the Adventureworks Portal. Lets go to View to go into the Lineage View

The Lineage View gives us a lot of information so lets look at everything in more detail

Data Source

We start with the data sources. this gives us information about the type of Data Source

  • Local Database
  • Azure Database
  • xlsx

Click on each source to find out more information

Dataflow

I can immediately see there are issues with the dataflow

Clicking the two arrows allows you to see the lineage of just that one object

Everything else is greyed out

Clicking on the data flow shows you the objects within the flow but you cant look any further at the items within the object or any of the code behind the objects

Data Sets

Note the blue icons. This shows that the data set has been promoted for use. There is also an icon for certified data sets (If admin allows you to certify).

You can Promote datasets, Dataflows are currently in Preview. To do this, go to the Settings and Endorsement

Back to the Lineage

Within the data set we can now see the data items within each table object.

We can also see the measures that have been created with DAX, but we cant see the code that creates the DAX

Reports

The Reports contain all the visuals. If you click on the Up Arrow you can open the report

Dashboards

Finally information about the dashboards.

the great thing about Lineage is the ability to see everything that connects together

This will even work for reports and dashboards that are connected to data sets in other App workspaces.

Alerts

Check the impact of any changes. here we see if impacts very little.

You can also send an email to everyone who needs to be notified that a new data set is being published. this goes out to your Contacts List

Go to Your App workspace and select Workspace Settings

Go into Advanced and change the contact list. this will ensure that everyone receives an email about any impacts and changes.

Power BI dataflows – Takeover

Dataflows allow you to separate your data transformations from your data sets. we can now centralise the dataflow and connect to the flow in power BI Desktop instead of having to connect to the data.

to create a dataflow, instead of getting data within the Power BI desktop pbix file, you create a dataflow in Power BI Service

This this takes you to Power Query Editor within the Service, where you can do all your transformations.

Dataflows are held in the gen2 data lake. See Use Data Lake Storage V2 as Dataflow Storage which gives more information on setting up your own data lake as storage.

This is a great feature and goes along way to providing us with our Modern Enterprise Data Experience.

there is only ever one owner of a dataflow, which means that if you are not the owner you cannot work on the dataflow. Thank fully there is now a Take Over Option.

If you are not the owner and you are a Workspace admins or member you can take over ownership to work on the flow by going to

Settings(In the top menu bar)> Settings

And then to dataflows. If you arent the owner you will see the takeover button that allows you to work on the dataflow.

This is great but we did have some questions

If the Initial owner added the Credentials to the Data Source to create the dataflow, does every Owner need to also add in the credentials?

From our Testing, when you take over the Data flow, you need to reset the Gateway, but you dont have to worry about the credentials.

This happens every time you take over the data flow

Dataflows connected to on premises data sources. How do we use the Gatway?

  • You cant use the Personal model of Gateway with a dataflow. You must use the Enterprise Gateway
  • You must have a Power BI Pro License to use the Gateway
  • The connections use the authentication and user information input into the data source configuration. When using takeover, these need to be re established.

There are three developers working on a solution. Two have been at the company for a while and have no issues accessing the dataflow. However, when I take over the data flow and get to the Power Query Editor in Service I hit the following error

Clearly I, as yet don’t have access to the Enterprise Gateway

Gateway checklist

  1. Installing the Gateway (You can download the Gateway from Power BI Service)

In this Example, the Gateway has been installed.

  • Step 1 is to Install it on a server.

2. Sign into your Power BI Account to configure the gateway

  • Once installed it needs to be configured. In Power BI Service, Settings, Manage Gateways
  • Decide upon a name and a recovery key (And make sure you don’t lose the key)

3 Add Data Sources to the Gateway

  • Server Name
  • Database Name
  • User Name
  • Password
  • Security or Authentication methods
  • Advanced setting specific to just that data source type
  • Again, because Laura can access the data source, this has all clearly been set up. Server ds2chl261 Database AppDB

4. Tell the Gateway who can access the data source.

  • Click the Users Table and add the users of the data source.

5. Amending the Gateway

  • Again you go into Settings and Manage Gateways
  • Choose your the Data source ( Server and Database ) and go to the users Tab to add Users
  • In the above example, the gateway is being amended by adding a new user.

Once in place, the user will simply be able to select the Gateway as above.

Preview of Promoted Dataflows

You can now as of June 2020, set your dataflow to promoted to ensure everyone knows that this a dataflow that has been created and managed centrally, possibly by your BI team. It will eventually give you your ‘one view of the truth’

Go to Settings for your dataflow

And Promote the dataflow in the same was as you can with a dataset.

Only do this if its established that this is now centrally managed rather than self service data mashup

Note that certified isn’t yet enabled because this is done by your Power BI Admin

Power BI Models Snowflake to Star Schema

I attended the Power BI conference from http://powerbiconference.com/ and got so many great ideas on the 16th June

One was from the session related to DAX and Power BI Models. We have already spoken about Steering clear from flat files. Power BI prefers the STAR Schema model comprising of a fact table and Dimensions providing all the filters for the metrics

I however, do seem to use snowflake schemas and it seems that even these aren’t recommended for Power BI.

So i wanted to have a look at this and see why I do it and what it actually means for Power BI. Also, how can I change my thinking on Snowflake to STAR

Example 1

This is very similar to something I am working on at the moment. We are looking at Events and these events consist of Agenda items. Its very easy to create a snowflake schema for this. Lets see how it would work in Power BI

I have loaded the data into Power BI

Fact to Dim is one to one. the relationships started as both direction. Moving the facts into a Fact table and the descriptions into a dim table in a 1 to 1 relationship is called creating a Degenerate table

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-one-to-one

I have changed it to 1 to many to get the single direction filter and the fact that the event table snowflaked off from events creates 1 to many.

If you however leave the model as one to one, both direction, it makes no difference to the results. Seperating the fact and dim when the relationship is a 1 to 1 is knows as a

this is an interesting look at the results. there are two events.

When you bring in data from the Snowflake dimension, it still shows the 2 event metric. However the metric looks a little confusing if dont understand the data

For example, the table with EventDetail and Event should sum to 12 (the 12 Agenda items) but it doesn’t. It Sums to the 2 event (Which is correct for the number of events but is not correct for the number of Agenda items)

So the next question the user wants is, how many agenda items are there per event?

I created a Measure in the fact table that does a distinct count of the Event Detail IDs

# Events = DISTINCTCOUNT('Dim Event Details'[EventDetID])

Again, this works a treat. There are 12 Agenda Items

And again, the new measure shows us correctly the number of agenda items.

the snowflake seems to be doing everything that it needs to do. At this point it still feels like Snowflake is still a good option. But, how would you change this into a snowflake Schema?

Fact table 1 to many

lets have a think about how 1 to many happens using the date dimension as a good example

Notice the the dim is on the one side and the Many is on the fact side.

This is what we are looking for. the dimension holds a short fat table (One date)

And the fact table is long and thin (The date ID is in the fact table many times)

Denormalise the Event and Agenda tables looks like this

The Fact is connected to the Event and Schedule by the Event ID

I created a new measure on the new Event and Schedule Dimension

# Events (STAR) = DISTINCTCOUNT('Event And Schedule'[EventDetID])

And I now have the same visuals against the Star and the Snowflake

So lets see how they match up usingPerformance Analyser (You may have to turn it on in Options and Settings – Options – Diagnostics)

I have created a blank page to start from

From the blank page start recording

Great, you can immediately see that the STAR was quicker. We have a tiny amount of data but if you have a lot of data this will really make a difference

So we created a Star schema and sped our reports up. Although there are no issues with the snowflake, this is a clear win for the star.

Create your website with WordPress.com
Get started