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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.