As a frequent trainer in Power BI, I often receive questions about Power BI architecture. There are quite a few excellent visual resources available from Microsoft and Microsoft partners. However, I have found that none of them are quite as comprehensive or current as I would like, so I went ahead and created the graphic below. Let’s dive into the details.
1. Sourcing Data
One of the most attractive features of Power BI is the ability to source data from a variety of file formats, databases and cloud/web sources. There are two primary options for sourcing data: importing the data to Power BI or using Direct Query. When importing data, Power BI Pro limits the user to 1 GB (compressed) data in your dataset. For datasets greater than 1 GB compressed, you can utilize Direct Query. The Direct Query data sources denoted with an asterisk in the above graphic, are currently supported by Power BI. Microsoft maintains a current list of Direct Query support sources and you can access the list here. Microsoft also supports Live Connection to SQL Server Analysis Services.
More recently, Microsoft has provided two additional options for directly accessing large datasets: Azure Analysis Services and Power BI Premium.
2. Transforming Data (Query)
Once you select your data sources for import, Power BI provides a preview window, which allows you to select the columns/entities to be included in your dataset. If you choose to edit the query, Power BI provides an ever-growing number of transformation options to shape your data.
3. Reporting and Publishing
Once you have sourced and shaped your data, you now ready to prepare reports. Reports typically consist of visual representations of your data such as line charts, column charts, pie charts, etc. along with filters and slicers. Calculated columns and measures are available using Digital Analysis Expressions (DAX). These links provide helpful information for report generation and DAX.
One of the more unique features of Power BI is support for third party custom visuals. In fact, there are more than 50 custom visuals available.
Once you have completed your report development, you are now ready to publish your report to the Power BI service. A prerequisite to report publishing is a Power BI tenant and valid credentials for either Power BI Free or Pro version. You can create a Power BI account by signing up here. Alternatively, your organization may have a Power BI tenant established and they can create an account for you manually or take advantage of Active Directory synchronization options to Azure Active Directory.
Microsoft recently released the ability to publish reports to Power BI Report Server residing on premises. This option requires either Power BI Premium (described later in this post) or SQL Server Enterprise Edition (with software assurance).
4. Creating Dashboards
After you have published your dataset and report to Power BI, you are ready to create dashboards. You can pin individual elements of your report or pin the live report page. When you pin individual elements, the visual retains the filter setting you selected when you saved the report. Pinning the live report page allows the dashboard user to interact with the visual by selecting slicers and filters. You can learn more about creating dashboards here.
5. Sharing, Collaboration and Security
When you first sign in to the Power BI service, you will be in your My Workspace, which is your personal workspace for accessing your published datasets and reports, and creating dashboards. You can create Power BI App Workspaces to collaborate with your colleagues including editing of content and sharing of datasets, reports and dashboards, and creating apps. Further information on App Workspaces can be found here.
You can control access to Power BI content at the workspace level. In order to provide more granular, data security, you can implement role-based security. You can learn more about this capability here.
Providing access outside the App Workspace can be accomplished by sharing dashboards with individual users or groups (including Active Directory security groups) or publishing an app.
6. Excel and PowerPoint Integration
As you might expect, Microsoft provides integration with popular Office applications like Excel and PowerPoint. You can pin existing Excel content to Power BI dashboards using Power BI Publisher for Excel. You also have the ability to save a report to PowerPoint.
7. On-Premises Data Gateway
Once you have published your dataset and reports to Power BI, you will most likely want to refresh the data on a regular basis. In order to refresh the data from data that resides within your organizational environment, you will need to install the Power BI On-Premises Data Gateway. This establishes a secure connection between the Power BI service and your local network environment, which houses your data sources. The gateway is required for data refresh scenarios as well as Direct Query to data sources within your environment. You can learn more about On Premises Gateway here.
8. Power BI Mobile
Power BI provides apps for popular mobile platforms such as iOS, Android and Windows. These apps, which can be downloaded for free from the applicable app store, allow access to dashboards and reports which you have created or have been shared with you. More about Power BI mobile apps here.
9. Azure Integration
Power BI integrates directly with a number Azure data platform services including Azure SQL, Azure SQL Data Warehouse, Azure Data Lake, Azure Analysis Services, Azure HD Insight and Azure Machine Learning. These platforms provide a variety of services for accessing Big Data, cloud-based data warehousing, analytics and machine learning. The full breadth of Azure data and analytics services can be found here.
10. Flow and Power Apps
For those users interested in integrating workflow and data entry to your dashboards, you can take advantage of Microsoft Flow and Power Apps. For example, you can set a threshold data alert for certain visuals in your dashboard and initiate a work flow when the threshold is reached. Detailed information regarding integrating Flow with Power BI can be found here.
If you are interested in entering data to your dashboard and having the data show up in your dashboard upon refresh, Power Apps can be utilized for this purpose.
11. R Integration
Data scientists and statisticians often do in depth data analysis of large data sets in order to uncover patterns and discover new insights. Power BI provides the ability to run R scripts directly in the Power BI Desktop and import the results to your Power BI dataset. Furthermore, you can create R visuals using Power BI.
12. Stream Analytics
For those scenarios when real-time dashboards are needed, Power BI supports streaming data. This application is often found in IoT use cases where there is a need to integrate machine sensor data to your dashboards. An Azure capability, Stream Analytics, is available to support this scenario.
13. Power BI Embedded
Microsoft provides the ability to embed Power BI dashboards and reports in your custom applications. This capability is encompassed within the Power BI Premium SKU highlighted below. A number of blog posts and public content provide information on how to embed Power BI reports and dashboards. A useful starting point can be found here.
14. Power BI Premium
Power BI Premium, which was recently announced, provides additional capabilities and resources to support large enterprise deployments of Power BI. Power BI Premium provides licensing by capacity/usage with a dedicated environment, managed by Microsoft. This provides greater scale and performance for large Power BI deployments and embedded scenarios. Power BI Premium also includes the ability to utilize existing SQL Server Enterprise licensing to deploy Power BI Report Server on premises. You can learn more about Power BI Premium here.
I hope you have found this post to be valuable as a reference and learning tool for Power BI. My plan is to keep this updated on a regular basis to highlight new Power BI capabilities as they are announced.