Designing Effective Power BI Solution Architectures
A deep understanding of your solution's architecture is essential for successful data visualization and business intelligence projects. It enables the delivery of impactful insights and drives effective decision-making processes. This guide provides an in-depth look at the various parts that come into play when reviewing your data visualization project.
A Power BI solution architecture offering a clear roadmap for professionals who want to harness the full potential of their data visualization projects. From understanding the logical structure to navigating through various data sources, this article is a comprehensive resource for effectively architecting Power BI solutions.
Whether you’re dealing with on-premises or web-based data, public or confidential information, this guide provides the know-how to securely and efficiently structure your Power BI projects. Using this guide will help you have those project discussions. This is the beginning of what is involved in creating robust, secure, and scalable solutions tailored to your organization’s needs.
There are many moving parts and pieces to a Power BI solution, especially when looking at the varied data sources you can use.
You must expose your data securely from on-premises to web-based, public to confidential. An excellent, clear architecture will help you achieve a good and secure solution.
Power BI Logical Solution Architecture
Without getting too deep into Physical Architecture, a quick review of the various Logical Architecture components is pictured below. Note that you can also use Power BI as an embedded into your web application.
The sections are organized by the numbers referenced in the screen capture. This high-level architecture covers the online PowerBI.com (SaaS) and on-premises Power BI Report Server.
PowerBI Solution Architecture
Actors / Personas (1) & (2)
For this exercise, I classify two groups of actors in Power BI: Developers and Users.
(1) Power BI Developers & Designers
The first group is those that create content. They can create data sets or visualizations. They provide the role of developers and designers of the solution. This group crosses departments and roles. They may not work on Power BI full-time but should have a role in your solution.
You cannot create a visualization without data, and the DBA and data team need to be involved in granting access to data and assisting in creating views of the data.
It is important that the data is as close to presentation ready as close to the data source as possible.
This is my most important recommendation about data used in a solution: Leverage the power of all that money tied up in the database servers. If 100 people use the exact data for reports, having one set of views will save a great deal of time in developing and auditing the final results. It will also save your team from performing the same transformations and data modifications.
(2) Your Organization’s Stakeholders & User Community
This group of people will be the final consumers of the solution. For some organizations, there may be overlap, but these are divided by the minimum roles required.
Admin / Audit / QA Users
Users who maintain the final systems, including security, testing, and approvals. In addition to Quality Assurance (QA), I have split Audits into a specific group. These are the data owners or a group tasked with ensuring the data on the final reports balance with the source systems. (This should be job 1)
The QA user role should include balancing and validating calculations and formulas used in the solution. Auditing should also be proactive, with balanced reports to capture breaking changes that may occur during the life of your project. Trust in your solution has the greatest effect on your reputation. If you have incorrect data, it is easy for your users to lose faith in the report and the team or people producing the analysis.
Analysts & Power Users
These are the core users of your solution. Their demands are different from most, and they should represent your most vocal source of feedback and upgrade suggestions. You will identify these users early in your solution as they will be the main consumers of your work. They will use the data sets created in Power BI and may also create dashboards and reports, which will build upon your solution.
End Users
These are simply the consumers of the reports and dashboards. Executive teams may sometimes have different needs than power users and analysts, such as being able to drill down to detail or looking for proactive analysis and suggestions based on the data.
They will be the source for your Guided Analytics or stories that will be built from the data. For example, if one metric shows an issue, drill down to another report with different details on the member selected, which could lead to another report with a different analysis. Please learn how your users will use the data, their questions, and how they develop solutions to issues brought forward from the data.
Data Sources (3) & (4)
In this architecture, data sources are divided into two groups: those in the cloud and those on-premises. Depending on your organization’s policies, you may have to design your solution to operate on data within your firewalls.
In the following sections, discussions around the Power BI gateways will review how to provide secure access to local data by pushing updates to Power BI. This becomes part of a solution around using the Power BI cloud service to host your dashboard and reports, as they need access to the source data to reflect updates.
(3) Internet-Based Data Sources
These can take several forms: structured data such as Azure Databases, Excel files in SharePoint or OneDrive, Software as a Service (SaaS) such as Google Analytics, or even just web pages scraped for their data. The key differentiator of these sources is that they do not need to access data through your firewalls to your local, on-premises systems.
(4) On-Premises Based Data Sources
These sources are within your network. If you use the Power BI Report Server, a server located on your on-premises network, you do not have to worry about your data being housed in the Cloud.
If you use PowerBI.com, you can still use your on-premises data sources using a Power BI Gateway, referenced below. This is important if you plan to use the Power BI online SaaS destination for your reports and dashboards, as the update services need access to process updates if required.
Active Directory Security and Access (5) & (6)
Maintaining the security of your data while still using it in the cloud requires the same care as you have for your on-premises data. Azure can be just as secure, if not more secure, than your local data. When using Power BI SaaS and Power BI Report Server access, Active Directory controls access.
(5) Active Directory Domain Services (On-Premises)
Active Directory Domain Services is the foundation for security and access to the objects on your local network. It provides various hierarchical-style identity and security-related services to users and groups. For the Power BI Report Server, for example, you would create groups containing users to provide each user the correct level of rights based on their role in your solution. Security roles such as;
Administrators
Report Developers
Data Architecture team members
Quality Control team
Readers
Editors
Data Source service accounts
Project Lead
Read Only users
Business Analysts
Designers
ETL Team
(6) Azure Active Directory
This is the cloud version of Active Directory (AD). It allows for object security and identity management in a central location with centralized policies and rules, mirroring the functionality provided by an on-premises AD. You would use this to provide access to your Power BI reports and dashboards.
Integrating your internal Active Directory with Azure Active Directory can be accomplished with Azure Directory Federation Services (ADFS). Azure Active Directory (Azure AD) Connect will allow you to federate with on-premises Active Directory Federation Services (AD FS) and Azure AD. Once you have the federation sign-in, you can sign in to Azure AD-based services with your on-premises passwords with the benefit of not having to enter your passwords again.
Power BI Gateways (7)
(7) Power BI Gateway
This allows the dashboards to be created using on-premises data sources, deployed to the PowerBI.com service, and updated using either a refresh schedule or on-demand by a data steward. Depending on the solution you need, there are a couple of gateways. The Power BI gateways are installed on your local network's server or dedicated desktop machine. They provide outgoing data feeds to Power BI. Some ports need to be open for outgoing communication only. The following table reviews the current features of each version.
| Feature | On-premises data gateway |
On-premises data gateway (personal mode) |
|---|---|---|
| It runs as an app for users who aren’t administrators on the computer | Power BI, PowerApps, Azure Logic Apps, Microsoft Flow |
Power BI |
| Serves multiple users with access control per data source | X | |
| Cloud services work with | X | |
| Runs as a single user with your credentials | X | |
| Import data and set up scheduled refresh | X | X |
| Support for DirectQuery to SQL Server, Oracle, Teradata | X | |
| Support for a live connection to Analysis Services | X |
(8) Power BI Reporting Server
This provides a combined on-premises solution for self-service and enterprise reporting. Power BI Report Server is software installed on-premises, which is part of the Power BI Premium offering. The easiest way to look at this software is that it is SQL Server Reporting Services (SSRS), which can host and serve up Power BI reports and datasets. The only thing this does not have is the ability to host Power BI Dashboards.
You can create dashboards in SSRS but cannot use Power BI dashboards. Power BI’s functionality is the same, but a separate version of Power BI Desktop publishes to the Power BI Report Server URL instead of the PowerBI.com service.
(9) Power BI Service
This is the Power BI service on the web. This will house the datasets, reports, and dashboards you produce using Power BI Desktop. You can also schedule periodic dataset refreshes using the Schedule Refresh options.
The recommended way to share and distribute your Power BI solution is through Power BI Applications. These can be secured using Azure Active Directory, allowing greater flexibility in developing your Power BI solutions with a team.
Conclusion
A well-structured Power BI solution architecture is the cornerstone of successful data visualization and business intelligence initiatives, enabling organizations to securely and efficiently harness the full potential of their data. This guide has provided a comprehensive overview of the logical architecture, data source integration, and security considerations necessary to build robust, scalable, and impactful Power BI solutions.
By leveraging these insights, professionals can confidently navigate the complexities of their projects, drive informed decision-making, and create tailored solutions that meet their organization’s unique needs. Whether you’re working with on-premises or web-based data, public or confidential information, a clear architectural roadmap ensures your Power BI projects are both effective and secure.
