Power BI Deployment Approaches When Dealing With A Large Scale Organisation

When you start working with Power BI at a smaller scale it can be fairly simple.

  • You can find your data sources
  • Import them into Power BI.
  • Do some work on the data in Power Query Editor.
  • Create some DAX to create measures
  • Add visuals into your report.
  • Publish the Reports and add dashboards.
  • Then Share them to Other Power BI Pro Users (Or free licence Users if you have Premium but at a smaller scale its very doubtful you would have Power BI Premium due to the costs.

However when you are dealing with a larger organisation there are other consideration. the last thing you want is a sprawling confusion on Power BI Reports and Dashboards, shared to anyone within the organisation (And possibly externally if external users have been added as guests)

Some reports will be really useful, others wont be used at all. Workspaces are being created everywhere and no governance is being applied to the entire set up.

This is where we need to start applying some governance and control. The first thing to understand is which Power BI Set up are you using?

If you are still at user Licence level then everyone will be working on Power BI Pro licenses, whether Contributor or consumer.

If your organisation is large, it will probably be working with a Power BI Premium capacity licence. Therefore if Workspaces are published in Premium, anyone with a Free Power BI Licence in your company can access Power BI content as a Reader

Anyone Contributing as a Developer will still need a Power BI Pro License.

There are three main deployment approaches to be aware of in a large scale environment

Business Led Self Service

This is where uses within the business teams lead on their own Power BI content

users create their own Reports, Dashboards and Datasets and add users when they want to share their content.

This is normally done by sharing at the User level

This is a great way of finding out who your Power Users are. They are the ones creating content and asking questions. get these people involved at all levels and you are on your way to a great adoption roadmap.

IT Led Self Service

This is where the BI team (IT) Create the datasets (Including all the DAX) for the business users. These users can then create all the Visualisations and reports over the data set

We can also now separate the data transformations (Dataflows) from the DAX (DataSets) which gives further opportunity to share work.

  • There will only be one owner of the dataflow and data set. The person who created it
  • because this is IT Led the data sets can be promoted (Or certified which is the highest level of attainment)
  • Promotion and certification is great for data discovery.
  • Data sets can be used by users (With Access) and Published to other workspaces
  • Users can also create their own DAX in their own published datasets.
  • Potentially, if this happens, the DAX, when proved useful should be added by the BI team into the original dataset.

At this level, the assumption is that access to the App Workspace and App with the different Power BI roles is managed as Azure AD Group Level

This means that we can centrally keep control of who has access to the App Workspaces and what Power BI roles are assigned

Certified

Only a small group of users should have certification rights, within the Power BI team. certification is the one we should always be working towards and you should have documentation that establishes the route to certification.

This means that you have control and your Small team of Power BI reviewers can check everything over and then change from promoted to certified

Promoted

If you own the data set you can promote it, to establish wider use of the data set. This can be done by BI Team members if IT Led self Service and BI team and Business Users with Pro Development License

Corporate power BI

Everything is created by the BI team centrally and consumed by the business users.

This is a great approach when you have to prioritise work and need to move fast. Its great when your report consumers possibly don’t have the skill sets to set up reports and dashboards.

Its also the best approach to take content that has been created Business led or IT Led and move it centrally, with all the security standards and governance in place. Only using the Corporate approach tends to create bottlenecks for business teams because of the longer set up period. it also disenfranchises

Which Deployment Approach to go for

Its always good to start of with the Corporate deployment Approach for your centralised analytics. Then have proper controls and governance in place to enable IT Led and Business Led deployment approaches

We need to aim for the Blended Approach which is IT Managed Self Service BI

So Ideally, you are looking at blending all three approaches. So long as you understand which approach you have used.

You still want users to get involve and want to create Power BI reports. You also want them to engage enough to want them to become certified and moved into central control when the content has proved important and useable.

Moving into the IT Managed model does involve more work. Imagine there is a self service App Workspace that has proved incredibly popular. Time to congratulate Power Users who pulled everything together and make sure they understand that moving into into Managed BI doesn’t mean they cant continue to work on their ideas and new content. Moving the content involves:

  • Checking that the data sources are good enough to work with.
  • Possibly updating the underlying Data Warehouse or Data Mart.
  • If the data is in excel spreadsheets, or text files, making sure this is managed.
  • There may be a lot of work done within the dataflow on data transformations. Some of this could be moved to the data source, freeing up Power BI resources.
  • To get the data set certified you may need extra involvement from BI team members to review the data set and verify it as acceptable for certification.

An Example Approach in Power BI

We are in an organisation called Adventureworks.

Within this We have various App Workspaces. All so far are Self Service. There has been no control over the process so far and there are three people with power BI Pro licenses (Imagine if this involved more Pro users. Some creating Separate Workspaces. Some reusing Workspaces)

Within these app workspace, users have been creating content, Reports, Dashboards, Datasets and dataflows

  • All three Workspaces have used the same data source
  • Adventureworks contains the most up to date data set and also contains dataflows
  • AT and JL workspaces contain similar information so on the whole there are three fairly identical separate data sets
  • For every workspace, users have been added, rather than user groups
  • Basically, we now have practically the same data source three times in Power BI, when there should only be one.

Bronze Silver and Gold Report template

One approach is to use the Bronze Silver and Gold Approach either via a template or a badge visual you can add to your reports

Bronze if you are doing Business led Self Service

Silver if you are doing IT led Self Service and you are using a dataset to create your own reports

Gold if its been fully checked and managed by the Central BI team. The Data set can be certified to use for Silver projects and all the reports and dashboards have been tested and have been proved as useful to the company.

The Process to entangle what has gone before

There is lots of work to do on the example above.

First of all we need to identify what type of approach have been used. For Jess and Amy, we can say that the Business led Self Service approach has been used. Each report and dashboard can be branded with the Bronze badge for business led.

For the Adventureworks we can again say that the Business led Self Service approach has been used. Dataflows within the service have been created and the data set has been promoted by the person creating the reports and dashboards but it was all done within the business.

In all three cases the user has created all the items for their reports and dashboards. therefore all reports and dashboards can be branded with Bronze.

So far we cant say that we have moved to the IT Managed blended approach, and its clear that users have been added ad hoc without much planning

Step 1. Identify if there is anything in Amy and Jess’s dataflow within Power BI that needs adding to the dataflow within Adventureworks. Then amend check off as complete.

Step 2. Identify any missing DAX Measures or calculated columns. Any identical measures with different names. Any data items that have been renamed between data sets. Any calculated Columns that can be moved into the dataflow. Ensure everything is added to the main dataset in Adventureworks and ensure the users of the other workspaces know what is happening

Step 3. Identify the differences between the reports. Hopefully each person has been working on different areas, but it may be that the reports need bringing together. This will take time. A report should never answer the same question twice.

Step 4. Amend appropriately. At the very least, all three reports should be using the Power BI Dataset that has been promoted as a usable source

Step 5. All these steps have led to a more IT managed deployment so we can add a silver badge to these reports.

Step 6. An extra review to check the dataflow and data sets within the central BI team. Also ensure that we aren’t duplicating work within the reports and dashboards, and this becomes Corporate Power BI, managed centrally. Our power users still have control and the ability to make changes. However everything is now more managed. We can now add a Gold badge to these reports and Certify the data sets

Step 7. Identify the users and the Power BI role levels they should have.

Create Groups within Azure AD for each Power BI Role and assign the users to these groups. This will mean that we are handling the contributors and readers centrally within Azure

I got lots of great help trying to put together an idea in my head. https://community.powerbi.com/t5/Service/Logic-with-dealing-with-Self-Service-and-Corporate-power-BI/m-p/1030137#M93044

Thanks so much to nickyvv from the Power BI Forums and Line Krogh providing some great info on Bronze Silver and Gold templates.

Azure Analysis Services Security

I have recently been setting up training sessions for Analysis Services and Azure Security has been one of the biggest learning curves.

Lets have a look at what we needed to do with our Adventureworks demo and all the areas of Possibility

Azure Analysis Service Access Control (AIM)

I have created an analysis Service in Azure which makes me the Administrator. I can do everything, including add models that I create in Visual Studio 2019.

However I will be working with lots of other people. In the first instance they will be using the models I have set up to create reports in Power BI

This is only a training demo and is not how I would set up a fully functioning Live Analysis Service model

in Azure we can add role assignments (Click on Access Control (IAM) but what does this mean in terms of actually using the service?

I am an owner. Azure has a number of built in roles role-based access control (RBAC) to use

  • Owner – Has full access to all resources including the right to delegate access to others.
  • Contributor – Can create and manage all types of Azure resources but can’t grant access to others.
  • Reader – Can view existing Azure resources.
  • User Access Administrator – Lets you manage user access to Azure resources.

The model above shows how you can use RBAC to segregate duties.

Using the following information, I set my users up as contributors in the hope they would be able to access the models

With Contributor in place the users could see the Analysis Service in Power BI but didn’t have access to the model

As a test the users were bumped up to Owner and still couldn’t see the models in Power BI

Azure AIM is in relation to the Analysis Service, Not the models created on the Analysis Service

Visual Studio 2019 (Roles)

The next step is to see what happens when we create a role in Analysis Services model in Visual Studio

In tabular Explorer (Make sure you have downloaded the Analysis Services extension for Visual studio) click on Roles. I have created a training role for this demo

I gave all the Users the permission of Read and Process

PermissionsDescription
NoneCant modify the model in SSMS. Cant query data.
ReadCant modify the model in SSMS. Can query data.
Read and Process Can query data. Can run process operations by running a script or package Cant make any changes to the database. Cant view the model in SSMS
Process Cant query data. Can run process operations by running a script or package Cant make any changes to the database. 
Administratorcan modify the model schema and query data

Once the training role was set , the model was reprocessed

Users now have access to the Analysis Services model and can use the model in Power BI

The question is, can the users access the model if they are in this Role, but they are not in Analysis Services (AIM)

Both users were taken out of AIM in Azure and still had access to use the models

New requirement for users to add new models to the Analysis services Database

the next stage was to allow the users to create their own adventureworks models in the Analysis Service.

With all the above security in place, when a user Processed their tabular model, the following error occurred

This user has owner Access in Azure IAM and Read and Process Access on the Analysis Services model

How do we allow users to actual create and modify models?

SSMS – Roles

SQL Server Management Studio is the place to grant model permissions

Open up SSMS as Administrator by Right Clicking and Run as Administrator.

Note that you can see the Training role set up in visual Studio. originally, Full control was unticked.

I ticked Full Control which is immediately set on the model. No need to process anything.

The user tried again and still couldn’t add the new model to the Analysis Service

Only server administrators, or database administrators having Full Control permissions, can deploy a cube from source files to a server, or create roles and assign members.

It would appear that Full Control (Administrator) is not the same as server administrator because you cant deploy a tabular model

Question, If I Re-process the visual Studio model with permission set as read and Process, will this override the new selection in SSMS?

Yes. If you reprocess your tabular model (Because you have updated the model, Added new measures etc) , Role permissions from here will override the Role permissions set in SSMS. Ensure that any Roles changed in SSMS are reflected in Visual Studio

SSMS – Analysis Services Properties

right click on the Analysis Service model (Under Connect) and Choose Properties

then select Security

Note that this is where Server Administrators are assigned and only these people can create and deploy their own models to the analysis Service

Going back to Azure, you can also set up Admins in here

Note that everything we just did in SSMS is available in Azure. Also, if we redeploy the Analysis Services Tabular model via Visual studio, it doesn’t do anything to Analysis Services admins

Our roles aren’t available to view in Azure . Just Analysis Services Admins

Everything is now working

There is still questions to be answered. Feel free to let me know your thoughts

  • When would you need to add users to Azure IAM if you can use the tabular models and also deploy models by adding users to Roles and the server administrator role in visual Studio and SSMS?