Power BI Architecture

The Power BI architecture is designed to facilitate seamless data processing, analysis, and visualization. The key components of the Power BI architecture include:

The architecture of Power BI can be divided into several key layers:

Data Sources
Data Processing (Power Query)
Data Storage (Data Model)
Data Visualization (Power BI Service and Power BI Desktop)
Power BI Gateways
Power BI Service (SaaS)

Each layer plays a specific role in the overall workflow of dad from gathering, transforming, visualizing, and sharing data insights.

Data Sources

The architecture starts with the data sources to gather raw data for Power BI reports. Power BI supports various data sources, such as:

On-premises databases: SQL Server, Oracle, MySQL, etc.
Cloud-based services: Azure SQL Database, Azure Blob Storage, Salesforce, and more.
Files: Excel, CSV, XML, JSON files.
Online services: SharePoint, Google Analytics, and social media platforms.

Power BI can connect to these data sources directly or through connectors. It can also establish a live connection to continually fetch updated data.

Data Processing (Power Query)

After collecting the data, it is moved into the Power Query phase for transformation. Power Query is the Extract, Transform, Load tool that allows users to clean, reshape, and merge data from different sources. This process occurs in Power BI Desktop to prepare data for analysis before visualizing it.

Key features of Power Query

Data Transformation includes cleaning data, removing duplicates, filtering rows, and managing missing values.
Data Aggregation is grouping of data by applying aggregate functions (like sum, average), and creating calculated columns.
Combining Data by Merging or appending data from multiple sources into a unified dataset.

Data Storage (Data Model)

Followed by data transformation, it is loaded into the Data Model within Power BI Desktop. To perform efficient querying and analysis, Power BI stores the transformed data in the data model, such as:

Relationships,
Calculated columns, measures,
Other structural components.

Types of data model

The efficient performance of Power BI is due to fast querying support and is highly optimized for analytical queries.

In-memory data model,
Tabular data model.

Power BI uses an in-memory data model based on a columnar database. The Tabular Model is similar to the data model used in SQL Server Analysis Services (SSAS). Power BI allows users to create relationships between different tables for complex, multi-dimensional analyses.

Data Visualization (Power BI Desktop and Service)

The processed data is then visualized through the Power BI Desktop and Power BI Service.

Power BI Desktop is the authoring tool to build reports and dashboards. It offers a rich set of visualizations such as:

Bar charts, line graphs, pie charts, maps, and custom visuals.

To perform advanced calculations, users can create calculated measures using DAX (Data Analysis Expressions).

Power BI Service is the cloud-based platform where users publish, share, and collaborate on reports and dashboards. The Power BI Service supports various features like:

Dashboards: Collections of visualizations to monitor key metrics.
Workspaces: Shared spaces where teams collaborate on reports and datasets.
Apps: Packaged collections of reports and dashboards that can be shared with users across the organization.

Power BI Gateways

Power BI Gateways facilitates secure communication between cloud-based Power BI services and on-premises data sources. Gateways allow organizations to continue using local data sources. There are two types of gateways:

On-premises Data Gateway,
Personal Gateway.

On-premises Data Gateway is used to connect Power BI Service to on-premises data sources like SQL Server, Oracle, and file systems. It ensures secure data transfer and supports both scheduled data refreshes and live queries. Personal Gateway is used for personal data refresh scenarios, where individuals work with on-premises data.

Power BI Service (SaaS)

The core of Power BI’s architecture is the Power BI Service, which is the cloud-based Software-as-a-Service (SaaS) platform. This service plays a pivotal role in:

Data sharing,
Collaboration,
Data refresh,
Security,
Natural language queries.

Users can share reports, dashboards, and datasets across the organization. Teams can co-author reports, provide feedback, and collaborate within the Power BI ecosystem. The Power BI Service automatically refreshes datasets, ensuring reports always reflect the latest data.

Power BI Service integrates with Azure Active Directory (AAD) for authentication and authorization. It also supports row-level security (RLS) to ensure users can only access data they are permitted to see. Through Power BI’s Q&A feature, users can ask natural language questions and receive answers in the form of visualizations.

Request more information

Power BI Architecture