How to Architect Your Power BI Solution
The best place to start when learning to develop Power BI solutions is to review the logical architecture your Data Visualization projects will have. There are many moving parts and pieces to a Power BI solution, especially when looking at the varied data sources you can use. From on-premises to web-based, from public to confidential, you must ensure you expose your data securely. Having an excellent clear architecture will help you achieve a good solution and a secure one. The following article will show you how you can architect your 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 architecture covers at a high level both the online PowerBI.com (SaaS) and on-premises Power BI Report Server.
Actors / Personas (1) & (2)
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 not only in granting access to data but also in 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 same data for reports, having one set of views will save a great deal of time, not only in development but also in auditing the final results. It also saves 100 people from performing the same transformations and data modifications.
2. Your Organization’s Stakeholders & User Community
This group of people is the ones that 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 out Audit as a specific group. These are the data owners or a group that is tasked with making sure the data on the final reports balance with the source systems. (This should be job 1)
The QA user role should include the balancing and validation of calculations and formulas used in the solution. Auditing should also be proactive, with balance reports to capture breaking changes that may occur during the life of your project. Trust in your solution is an important part of your reputation. If you have incorrect data, it is very 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 than most and 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 the dashboards and reports themselves, which will build upon your solution.
End Users
These are simply the consumers of the reports and dashboards. Sometimes the executive teams who may have different needs than power users and analysts. Needs 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 is showing an issue, then drill to another report with different detail on the member selected, which could lead to another report with a different analysis. Get to know how your users will use the data, what their questions are, and how they develop solutions to issues brought forward from the data.
Data Sources (3) & (4)
Data sources in this architecture 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 are using the Power BI Report Server, which is a server located on your on-premises network, you do not have to worry about your data being housed in the Cloud.
If you are using PowerBI.com, you can still use your on-premises data sources using a Power BI Gateway, which is 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 and still using it in the cloud requires the same care as you have in 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. Through users and groups, it provides a broad range of hierarchical style identity and security-related services. For Power BI Report Server, for example, you would create groups that would contain users to provide the correct level of rights to each user based on the role that they would have 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). This allows for object security and identity management in a central location with centralized policies and rules. This mirrors 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 keeping the dashboards 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. There are a couple of gateways depending on the solution you need. The Power BI gateways are installed on a server or even a desktop machine in your local network. 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 |
Power BI Reporting Server & Service
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 that 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) with the ability to host and serve up Power BI reports and datasets. The one 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. The functionality of Power BI is the same, only that there is a separate version of Power BI Desktop that 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 refreshes of the datasets using the Schedule Refresh options.
The recommended way to share and distribute your Power BI solution is by using Power BI Applications. These can be secured using Azure Active Directory and allow you greater flexibility to have team development of your Power BI solutions.
Conclusion
In order to design a successful Power BI solution, you need to address all the architectural elements in your solution. Having a good overview will allow you to avoid problems further down the road.
