Power BI Architecture Guidelines

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

Implementing a Power BI solution is not just about developing reports, creating a data model, or using visuals. Power BI, like any other technologies, can be used in a correct, or incorrect way. Any technology can be used more effective if it harnesses the right architecture. A right architecture can be achieved after a requirement gathering and designing aspects and components of the technology to fit the requirement. In this post, you will learn about some of the most common architectures to use Power BI. You will learn about using Power BI in different architecture guidelines;

  • Sharing architecture
  • Self-service architecture
  • Enterprise architecture

If you want to learn more about Power BI, read Power BI book; from Rookie to Rock Star.

Disclaimer

The advised architecture is a general recommendation. For every solution and implementation, it is always the best to tweak the architecture based on the specific requirement. A general architecture only explains what the best overall methodology is.

Power BI Sharing Architecture

Considering all sharing methods that you already know about Power BI, it is a hard decision some time to choose a sharing mechanism or architecture for Power BI implementation. This chapter explains a blueprint of a recommended architecture.

Developer Team

Sharing Power BI content with a team of developers can be effectively done with a shared workspace. Everyone in the team of developers can have edit access to this workspace. The shared workspace will be like a collaborative development environment. Developers can make changes to the Power BI content in this workspace without affecting user’s environment. Here is a screenshot of developer’s sharing architecture.

Test User

For a test user, an easy way of sharing without too much hassle is usually the best way of sharing. Sharing dashboards and reports are simply available in the service. Through this way, test users can be notified that a Power BI content is shared with them. Test users then can give feedback to the developer team, whom can then iterate the change back through another cycle of development.

Organizational or External Users

After going through some development/test cycles, the Power BI content becomes gold. Gold Power BI content is a content that has been developed based on the requirement, and tested, and successfully past checking all the requirement with high quality. The gold content can be shared with end users (which can be in the organization or even external) through Power BI Apps. For sharing the content with people outside of the organization, Azure B2B services should be leveraged. Power BI app users will be end users who only consume the content as read-only.

Public Users

In every business, there might be a set of reports and datasets available for the public to use. For example, an overall annual report of a company without any confidential data. The public report can be shared through Publish to web functionality of the Power BI service. The most important tip about this way of sharing is that; the public report should use a completely isolated dataset that doesn’t have any confidential data in it. Because even if the data is not exposed in the report, users can access the data rows with export or view rows option of the Publish to web.

SharePoint Users

If in your company, the culture is nurtured that users use the SharePoint online as their portal of accessing content and documents, then Power BI content is best to be shared through the same portal. The best way of sharing is the way that users can feel familiar with it. When users, are used to SharePoint online portal; then embedding the Power BI content in the SharePoint online is usually a great option.

Custom Application Users

For ISVs and many other types of businesses; the portal for users is a custom application. The custom application that has been used for some time and users has their user account under it, is probably the best place to share the Power BI content through it. Embedding content into an application would also create a single sign-on sharing solution for the users. Power BI embedded into the custom application will give users a seamless experience of their web application. Users can view the Power BI content easily through the application without the need to login to Power BI.

On-Premises Organizational Users

If Power BI Report Server has been used in an organization, then the content can be shared with users of active directory through sharing via the report server. This way of sharing doesn’t need users to have Power BI accounts. However, the licensing should be either Premium or Software Assurance of the SQL Server Enterprise. This method of sharing is for companies whom are not yet ready for cloud, and want to leverage the Power BI content on-premises.

Sharing Architecture is not One Method

As you have learned so far, each method of sharing gives you part of the architecture; Workspace is good for the developer environment. Simple sharing is good for sharing with test users, Power BI Apps is good for end users, and other methods gives you other options. When you think about the sharing architecture; don’t limit yourself to only one or two methods; You can use multiple methods. You can use a different method for Dev environment, and another method for Prod environment. The main objective is to ease the access for users, meet the requirement, and isolate the developer environment.

Self-Service Architecture

Power BI is a self-service tool, and you don’t want to take this power of self-service from your users. In every business, some users know the business well, and they are also good at analyzing data, we call these users power users. A power user is someone who may not model the data, and may not right DAX calculations or M expressions, but can (and want to) do some visualization. Power users can leverage the self-service capabilities of Power BI and build their reports from the dataset which developer team provided for them.

Power BI Desktop; Get Data from Power BI Service

Get Data from Power BI Service is a feature that enables power users, to use Power BI Desktop to connect to an existing data model live from the Power BI Service. With this feature; users connect to the dataset without being able to modify the dataset, change the model, change the calculation, or even relationships. Users can only build visualizations on top of the existing models.

Users, however, can write their measures if they want, these measures are called as report-level measures. Accessing the centralized model from Power BI service is one of the ways that can build an architecture with one data modeler and multiple data visualizer in the team.

The diagram below is showing how this feature can be implemented;

Excel Users; Analyze in Excel, and Power BI Publisher for Excel

Some of the power users might be very familiar with Excel as a slicing and dicing tool. In another post, I explained how the integration between Excel and Power BI works. Excel users can connect directly to the Power BI model in the cloud using Analyze in Excel feature, or use the Power BI Publisher for Excel add-in for Excel.

Excel users, would be able to use PivotTable and PivotChart in Excel to slice and dice the data from Power BI service. Excel users, can save their sample Excel file and re-use it later. Excel users can connect to Power BI models even if they have read-only access to the model.

Power BI Service; Creating Reports from the Web

Power users also can create a report directly from the website. Creating reports directly from the service is a very good option for non-windows power users because Power BI Desktop can be only installed on a windows machine (At the time of writing this post).

Self-Service Architecture

Combining three methods mentioned above, you can give power users the ability to slice and dice the data of the Power BI model. They can build new visualizations, or even sometimes new calculations (report level measure).

Self-service users need edit access on the dataset to be able to create reports on it, except Excel users who can access the content even with read-only permission. In all methods of self-service mentioned above; users need to have Power BI Pro license.

Enterprise Architecture for Power BI

Using Power BI in an enterprise environment usually comes with some types of requirements which may not be available in a small or medium scale business. In an enterprise scale business; usually, the size of data is huge. Power BI (without premium licensing) only allows up to 1 GB size of the model, which may not be enough for the enterprise clients. Also; enterprise clients most probably already have a data warehouse, and data model in place. Another requirement of the enterprise clients is printing reports, which is the main functionality of SSRS reports. Combining all these requirements; usually, we have an architecture for enterprise clients with components below;

Data Warehouse

In an enterprise environment; usually, the data will be gathered through an ETL process from multiple data sources into a centralized data warehouse. The ETL tool for this scenario can be SSIS (SQL Server Integration Services), or any other ETL tools. The data warehouse itself will be designed with star schema to cover a data model that can solve all reporting challenges.

Analysis Services

SSAS (SQL Server Analysis Services) plays an important role in the architecture for enterprises. SSAS technology especially if used as tabular model technology, will give the data modeler the power of DAX calculation, and the partitioning feature of SSAS will cover large datasets. Some companies may use the older version of SSAS which is multi-dimensional.

On-premises gateway

If the Analysis Services is not Azure SSAS, then an on-premises gateway is required to create the connection from Power BI service to the data source on the local domain.

Power BI Live Connection
Power BI usually will be used in this scenario as a live connection (not import data) as a pure data visualization tool. Data modeling and data transformation are already done through SSAS and the data warehouse and ETL process. Power BI will only perform visualization on top of the existing SSAS model.

Paginated SSRS reports for Printing

SSRS is a great technology of Microsoft for having paginated report best for printing. If printing is an important requirement, then SSRS can be used to connect to SSAS or even to the data warehouse directly for reports generated for printing. SSRS reports can be then integrated with Power BI as I explained it in another post.

Summary

In this post, you learned about some of the architecture blueprints using Power BI. You learned about using self-service functionalities of Power BI for self-service architecture; using Power BI Desktop, service, and Excel. You also learned about the sharing architecture for Power BI, which may include multiple methods of sharing. Finally, you learned about Power BI used in an enterprise environment; when the data is in large scale, and other components such as SSAS and SSRS leveraged.

The architecture diagrams and blueprints in this post are only generic examples of recommended methods of using Power BI. For designing architecture in a real-world scenario; every single requirement should be considered specifically. Because different business has different requirements; you need to tweak and change the design and architecture, particularly for every business use case scenario.

Do you have any other architecture scenarios? please let me know in your comments.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

10 Comments

  • For Power Users whom want to create their own visuals with “Get Data from Power BI Service”, What would be the options to share the datasets with them?
    1) I dont want to share an app workspace because they could edit it.
    2) If I create an app, as far as i know they can not connect to the app from power bi desktop
    3) Create a content pack?

    • Yes, they cannot use APP. they should have edit access to be able to use the “Get Data from Power BI Service” feature. Content pack is also obsolete.
      I do recommend creating a separate workspace (separate from your production one which is for all users) just for power users, so they can play with their own reports with the copy of dataset which is there.
      Cheers
      Reza

      • If I create the app workspaces (edit mode) for them and I use a personal gateway to configure all the data source updates… can they update the data sources from their computers by doing it inside that app workspace?

        • Hi Victor.
          No one can update the data source from Power BI. Power BI is a read only tool, you can just read from the source, not write back
          Cheers
          Reza

  • Hi Reza, as usual brilliant summary of an article.

    The “Get data from Power BI Service” is a great feature, we started using it before Patrick even posted this video https://www.youtube.com/watch?v=-33xn9fFaQs

    However there is many limitations that make us doubt if MS is serious about this approach going forward:
    – Cannot share across workspaces (need to duplicate our models into multiple workspaces, and refresh data for every single one separately)
    – Need to grant users Edit access to workspace (which allows them to delete any report in it too – less secure)
    – Many fancy features don’t work on reports created this way, such as Q&A to build visuals and the new Q&A explorer (can’t add featured questions).

    Not sure if the new “Datapool” is going to address that, is MS planning to alleviate some of these limitations in the future?

    Thanks!

    • Hi Radek.
      Thanks for your comment.
      Yes, Get data from Power BI Service, needs edit access as I mentioned in the previous post. At the moment the only way for that is to create a separate workspace. and yes, the dataset of that needs to be scheduled too. However, this would be a workspace for your power users, not for everyone for sure. Q&A feature is not working, because this feature is like a live connection, as soon as we get the support of Q&A on live connection, this would come hopefully soon.
      Datapool is totally different thing. it is more related to CDS for analytics and common data service.
      Another approach is to create a central SSAS model, and then Power users can use Power BI Desktop on it. they won’t need edit access.

      Cheers
      Reza

  • Hi Reza..
    As usual, amazing written and very detailed.

    I would like to share one more approach which we are implementing is using power bi premium. As this is for an enterprise however we had some challenges setting up SSAS with aws where our data is. Since data is huge in size, we are going with power bi premium and deploy all the model straight to pbi services. Only drawback of this approach is expense for loading data from awa to pbi. However with delta load update for pbi (whicb we are hoping to release soon) we would be in a good shape.

    Do you have any comments or suggestions about this method?

    • Hi Harshit
      Thanks for sharing your scenario.
      Yes, another architecture for enterprise is to use Premium. As you mentioned the coming feature Incremental Load will definitely help to reduce the time required for refresh and will make it much more usable architecture method 🙂
      Cheers
      Reza

Leave a Reply

Your email address will not be published. Required fields are marked *