Power BI can connect natively to many different SOR (System of Record) data sets found in an organization. They can be from transactional databases, CRM, financial systems, Excel files and so on. While connecting directly to visualize data in Power BI may seem more efficient, the results are often not optimal.
There are advantages and disadvantages of connecting directly to the SOR compared to bringing those data sets into an OLAP (Online Analytical Processing) database instead, such as a Data Mart or Data Warehouse that is specifically designed for reporting and data visualization.
Faster to implement
Less infrastructure to manage
No ETL skills required for dashboard designers
Requires business users to understand underlying SOR database structure
Dashboards can be fragile if underlying databases change
Data blending can have poor performance
Requires extensive permissions across databases if there is sensitive data
Requires direct access from Tableau Server to SOR
Analysis is often less robust and complete
Dashboards resilient to underlying SOR changes
Business does not need to understand underlying SOR database structure
Combining data sets provides more analysis and insights
Data access permissions can be consolidated into a single location
Dashboards never go directly against SOR
Enterprise solution that can grow with the organization
Requires technical (ETL) skills to implement
ETL scripts require updating as underlying database changes
Requires planning and construction time
Additional infrastructure required (e.g., software, hardware)
Not sure if you need a data warehouse or data mart to achieve optimal dashboard performance?