Power BI Architecture: A Detailed Explanation

Power BI, as we know, is a business and data analytics service that facilitates the processing, analysis, and visualisation of large amounts of data. The Microsoft service helps in the extraction of insights, drawing conclusions, and sharing of results in the form of visual reports or dashboards across various departments. While knowledge of Power BI is important, it is equally important to understand how Power BI Architecture works.  

According to Microsoft, over 5 million subscribers have adopted the tool for modern business intelligence. Power BI is changing the way that organisations handle data, helping them turn data into opportunity. This blog explores everything you need to know about Power BI Architecture and provides insights into its components and working process. 

Table of Contents

1) What are the components of Power BI Architecture? 

   a) Data Sources 

   b) Power BI Desktop 

   c) Power BI Service 

   d) Power BI Report Server 

   e) Power BI Gateway 

   f) Power BI Query 

   g) Power BI Pivot 

   h) Power BI View 

   i) Power BI Map 

   j) Power BI Q&A 

   k) Power BI Mobile 

   l) Power BI Embedded 

2) How does Power BI Architecture work? 

   a) On-Premise 

   b) On-Cloud 

   c) The working process of the Power BI Architecture 

3) What is Power BI Service and how does its Architecture work? 

   a) Front End Cluster 

   b) Back End Cluster  

4) Conclusion 

What are the components of Power BI Architecture? 

Before we delve deeper into how the Power BI Architecture works, we need to have a brief idea of the components of make up the architecture. In this section of the blog, we will learn about the different components that combine to form the Power BI Architecture.  

Data Sources 

One of the most important components of Power BI’s architecture is its vast range of data sources. With Power BI, you can import data from files in your system, cloud-based online sources, or establish direct connections. It's important to note that the limit for data imported on-premises or from online services is 1 GB. Some of the commonly used data sources in Power BI include: 

1) Excel 

2) XML 

3) JSON 

4) Text/CSV 

5) MySQL Database 

6) Oracle Database 

7) PostgreSQL Database 

8) IBM DB2 Database 

9) Sybase Database 

10) SAP HANA Database 

11) Teradata Database 

12) SAP Business Warehouse server 

13) Impala 

14) Azure SQL Database 

15) Google BigQuery (Beta) 

16) Google Analytics 

17) Salesforce Reports 

18) Github 

19) Amazon Redshift 

20) Facebook 

Power BI Desktop 

Power BI Desktop is an important component of the architecture. It is essentially a client-side tool that is best used as a companion tool for development and authoring. It is a desktop-based software equipped with tools and functionalities to help connect to data sources, transform data, model data, and create reports. One can download and install the component on their system for free. Using the Power BI Desktop feature, one can cleanse data, create business metrics and data models, define hierarchies, as well as create visuals and reports. 

Power BI Service 

Power BI Service is a web-based platform that can be used to share reports created on Power BI Desktop, collaborate with other users, and create dashboards. This web-based feature is available in three versions, namely the free version, the pro version, and the premium version. Power BI Service is also known by several other names such as “Power BI Site”, “Power BI.com”, “Power BI Workspace”, and “Power BI Web Portal”. Another feature of Power BI Service is that it offers advanced features, such as natural language Q&A and alerts.   

Power BI Report Server 

The Power BI Report Server is very similar to the Power BI Service. While there are ample similarities between the two components, there is one significant feature that helps differentiate between the two. Power BI Service is a cloud-based platform, while Power BI Report Server is an on-premises platform. Power BI Report Server is generally used by organisations that are not willing to publish their reports on the cloud and have concerns about the security of their data. Power BI Report Server facilitates the creation of dashboards and sharing of reports with other users while maintaining proper security protocols. However, it must be kept in mind that in order to use this service, one needs to have a Power BI Premium license.  

Power BI Gateway 

Power BI Gateway is generally used to connect and access on-premises data in secured networks. Organisations that keep data in security generally use the feature of Power BI Gateway. The Gateway allows data extraction to Power BI platforms through secure channels for analysis and reporting purposes.  

Power Query 

Power Query is a feature that helps users discover, connect with, and combine data according to their specific and customised needs. Power Query can be downloaded as an additional tool in Microsoft Excel or used directly as a part of the Power BI Desktop tool. 

Power Pivot 

Power Pivot in Power BI is a data modelling technique that facilitates the creation of data models, establishing relationships, and creating calculations. The Power BI feature uses the Data Analysis Expression (DAX) language to model both simple and complex data.   

Power View 

Power View is a feature of Power BI that can be used with Excel, SharePoint and SQL Server. It allows users to create interactive visuals such as charts, graphs, and maps that bring data to life. Power View can also connect to different data sources and apply suitable filters to the data for specific elements or the entire report.   

Power Maps 

Another useful component of Power BI is Power Maps for Excel and Power BI. It is a 3D data visualisation tool that allows users to map their data and plot millions of rows on Bing Maps in 3D format directly from Excel. The Power Maps feature works in tandem with Bing Maps to find the most suitable visualisation based on parameters such as latitude, longitude, country, state, city, and street address data.   

Power Q&A 

The Q&A feature embedded in Power BI helps users explore data according to their specific needs. It is known as the most convenient way to answer questions using natural language. Once a user has built their data model and deployed it into the Power BI website, they can ask questions and get quick answers. 

Power BI Mobile Apps 

Power BI Mobile Apps are another set of native applications that runs on iOS, Android, and Windows mobile devices. These applications are commonly used for viewing reports and dashboards on-the-go and at short notice.  

Power BI Embedded 

Power BI Embedded is known for offering APIs that facilitate the embedding of visuals into custom applications. 

To know more about identifying and connecting to data sources in Power BI, join our Microsoft Power BI Data Analyst PL300 Course. 

How does Power BI Architecture work? 
 

Now that we have a brief idea of the individual components of Power BI, let’s delve deeper into how all these components work together. To begin with, data sources are the starting point of the working process in Power BI. Power BI has a feature that allows users to connect to a variety of data sources, such as files, on-premises or cloud databases, and direct connections. These data sources are then used to create data connections to authoring tools like Power BI Desktop.  

Data sources that can be connected to Power BI are divided into two broad categories – on-premise and on-cloud sources.   

On-Premises 

Power BI Desktop is used as a companion development, authoring, and publishing tool. Users can import data from data sources to Power BI Desktop, use the data for the creation and publishing of reports on a Power BI Service or Power BI Report Server. Users can also publish Excel workbooks directly using a tool called Power BI Publisher for Excel. SQL Server Data Tools and Report Publisher facilitate the creation of datasets, KPIs, mobile reports, and paginated reports, among others. All kinds of reports irrespective of their type are published to the Power BI Report Server and then distributed to the end users. 

On-Cloud 

An important component in the Power BI Architecture, as mentioned earlier, is the Power BI Gateway. It acts as a secure channel to transport data from on-premises sources to on-cloud applications. The cloud side of the architecture houses several components, such as dataflows, datasets, dashboards, reports, Power BI Embedded, Power BI Premium, and more. Users can embed their reports and dashboards into Teams, SharePoint, custom applications, and other platforms. On-cloud data sources can connect to Power BI tools via direct connections. Users who share the published reports and dashboards for collaboration and insight-based decision making can connect with each other through web browsers, Excel, and mobile devices (iOS, Windows, Android apps).  

The working process of the Power BI Architecture 

The working process of the Power BI Architecture is made up of four major steps that explain the process from data sourcing to the creation of reports and dashboards. The software implements a variety of technologies and processes in tandem to achieve the required results with precision. This section of the blog will elaborate on the steps of the working process.  

Sourcing Data 

The first step in the working process of Power BI Architecture is sourcing data. Power BI can extract data from various sources such as Microsoft Excel sheets, CSV files, and databases. The extracted data can be directly imported into Power BI, or a live service link can be established to receive the data. It’s important to note that if data is directly imported into Power BI, it can only be compressed to a maximum size of 1 GB. Once past that limit, only live queries can be run. 

Transforming Data 

Before using Power BI to visualise the data in a usable context, cleaning and pre-processing should be performed. Cleaning and pre-processing involve removing useless or missing values from rows and columns in the data sheet. After completing these processes, certain rules and policies will be applied to transform the data and load the datasets into the warehouses.  

Report and publish data 

Once the data is cleaned, processed, and transformed, visual reports will be created according to the specific needs of the organisation. A report is a visualisation of the data with different filters applied, presented in the form of graphs, pie charts, and other graphic representations. Converting raw data into contextualised information is one of the most important steps in the Power BI Architecture working process.   

Creating dashboards 

The final step in the working process of Power BI Architecture is the creation of dashboards. Power BI dashboards are created by pinning elements or pages of live reports. It’s important to note that dashboards should be created only after reports have been published to the BI service. Upon saving the report, the chosen filter settings are maintained so that the user can apply filters as per their specific demands.  

What is Power BI Service and how does its Architecture work? 

As mentioned earlier, Power BI Service is a cloud platform where all the reports created on Power BI Desktop are published. These reports and dashboards can be accessed from Power BI Service using client platforms such as websites, mobile devices, etc. This means that to access content created on Power BI, users need to interact with Power BI Service. Before delving deeper into how it works, it’s important to understand the components of its architecture. Power BI Service’s Architecture consists of two components, namely – the front end and the back end cluster.  

Front End Cluster 

The front-end cluster of the Power BI Service Architecture, also known as the front-end cluster, acts as an intermediate connection between clients and the back-end of the architecture. Front-end services are used to establish connections and authenticate clients using Azure Active Directory – a directory that stores user identities.  

Back End Cluster 

The back-end cluster of the Power BI Service Architecture handle visualisations, datasets, storage, reports, and other interactions with Power BI. A web client only has two direct points of interaction at the back-end of the architecture, namely Azure API Management and Gateway Role. These two components, together, work to achieve load balancing, authentication, authorisation, and routing. 

Power BI stores its data into two main storage types, namely – the Azure block storage and the Azure SQL database. The former stores datasets uploaded by users, while the latter stores all the metadata and system-related data. User requests are sent to the Gateway Role after Azure API Management authenticates them. The Gateway Role is in charge of processing the requests and directing them to components such as Presentation Role, Background Job Processing Role, Data Role, and more.  

User requests are redirected to suitable components based on the type of query – for instance, visualisation-related queries are sent to the Presentation Role, while data-related queries are sent to the Data Role. To connect and fetch the data from on-premises data sources to the cloud, an Azure Service Bus is used. The Azure Service Bus receives requests to fetch data, processes it, and executes the query on the on-premises data source to redirect its data to the cloud. Meanwhile, Azure Service Fabric manages the microservices associated with Power BI, and Azure AD Cache helps create reports from data stored in its in-memory. 
 

 

Conclusion 

All in all, Power BI is one of the most useful tools used in modern-day organisations to help visualise data and streamline time and effort. The Microsoft service uses its components to facilitate contextualisation and visualisation, and helps organisations increase their efficiency in data usage. Power BI also provides organisations with a level of personalisation and integration with other tools such as Excel into their workflow. All the components present in the Power BI Architecture work in tandem to assist organisations, making it important to have an understanding of how its architecture works to optimise the tool.   

Become an expert in Power BI by signing up for our Microsoft Power BI Training & Certification Course!