As BI Developers, we often have the choice between working with Tableau or Power BI.
Fortunately, I’ve had the opportunity to work with both tools across various projects.
For this blog, instead of covering the tools comprehensively, I’ll delve into the data modelling aspect, highlighting their differences, similarities, and when to choose one over the other.
Let’s explore the nuances of data modelling in these platforms, focusing on 3 major components:
- Data Preparation
- Data Model Structure & Relationships
- Calculations
Data Preparation
Tableau
While Tableau offers basic data preparation capabilities within the platform, complex data transformations often require external tools such as Alteryx. This limitation becomes evident when intricate data transformations are needed. Alteryx provide advanced features for data cleaning, shaping, and transformation, which complement Tableau’s robust visualisation capabilities but introduce a dependency on these external tools.
Power BI
Power BI’s Power Query Editor serves as a comprehensive tool for data cleaning, transformation, and shaping. Users can easily load, transform, and model data within the Power BI environment, streamlining the data preparation process. Unlike Tableau, Power BI is not dependent on external data preparation tools.
Real-world Example:
I used to receive files from external stakeholders to update the monthly CSV files in a Tableau report. While Tableau’s built-in data preparation tools managed some basic tasks, they could not automatically fetch and integrate the new month’s file from its storage location due to changing file names each month. As a result, we could not analyse historical data for previous months unless we manually unioned each file every month.
By leveraging Alteryx for data preparation or Power Query in Power BI, we automated the process of fetching files via email, storing them in a designated folder, and combining them using a union each month automatically. This significantly streamlined our workflow and eliminated the need for manual intervention.
Data Model Structure & Relationships
Tableau
Tableau’s approach to data modelling enables the integration of multiple data sources into a cohesive structure with various types of joins and relationships. Unlike PowerBI, which emphasises relationship modelling (primarily star scheme) in the data model.
Logical & Physical Layer Diagram:
Data Model
Relationships:
Tableau’s data modelling capabilities encompass both physical and logical layers, offering users a comprehensive framework for organising and analysing data.
- Logical Layer: This is the top-level view of your data source, also known as the Relationships canvas. Here, you combine tables using relationships, not joins. Tableau automatically determines the appropriate join types based on your analysis.
- Physical Layer: This is where you perform joins and unions to combine data. It’s referred to as the Join/Union canvas and includes the use of pivots. Each logical table can include one or more physical tables. In earlier versions of Tableau, this was the only data modelling layer.
Power BI
Power BI’s approach to data modelling revolves around establishing relationships between tables, adhering to a traditional star schema model. Tableau has similar capabilities, but they are not its primary focus.
Star Schema Diagram:
Users import data from various sources, such as Excel, data sources, SharePoint, etc., and define relationships based on common fields. The main focus is on dimensional modelling, aiming to have a fact table surrounded by dimension tables. Any joining of tables occurs at the Power Query level before modelling while on Tableau can achieved on the Physical layer.
Relationships
When building relationships in Power BI, it is crucial to carefully consider cardinality and cross-filter direction to ensure effective data modelling.
- Cardinality defines how data in different tables relate to each other, influencing how aggregations and relationships are handled in both tools. In Power BI, cardinality is managed through relationships in the Model view, while in Tableau, it’s managed through joins and relationships in the Data Source tab.
- Cross-filtering affects how interactions in one visualisation impact others. Power BI uses visual interactions and slicers, while Tableau uses dashboard actions and filters to enable cross-filtering and highlighting across visualisations.
Calculations
Tableau
In Tableau, calculations are formulated using a language reminiscent of SQL, offering users a familiar syntax for performing advanced analytics. If you are familiar with SQL, you might have an advantage initially. Tableau’s calculation engine supports a wide range of functions and operations, empowering users to derive insights from their data with ease. Moreover, Tableau’s unique table calculations provide additional flexibility for creating dynamic visualisations and dashboards, which is the main selling point, especially for creating creative visualisations.
Power BI
Data Analysis Expressions (DAX) serve as the backbone of Power BI’s calculation engine, offering users a powerful language for creating custom calculations and aggregations. Similar to Excel formulas, if you are familiar with Excel formulas, you have an advantage in learning DAX. DAX functions enable users to perform a wide range of calculations, from simple arithmetic operations to complex statistical analysis.
Tableau v Power BI Summary Table
My Personal Experience & Insight
In my experience, integrating the SharePoint connector to access an Excel file in Tableau has been challenging. Tableau lacks a direct, built-in SharePoint connector, requiring separate sign-ins and setups. On the other hand, Power BI offers a more seamless experience, particularly because many organisations use the Microsoft Suite. Power BI integrates smoothly with other Microsoft products such as PowerApps and Power Automate, providing powerful automation capabilities with email, SharePoint files, and other tools.
However, if you choose Tableau, you benefit from its advanced and customisable visualisations, high performance with large datasets, and greater flexibility in data analysis.
A Streamlining Tip . . .
In a previous engagement, I was tasked with improving a monthly manual process that involved receiving a CSV file via email, manually downloading it, and then updating the data source. I was using Tableau because it was the primary tool of the organisation. To enhance efficiency, I automated the process by integrating Power Automate and SharePoint folders. Now, when the file is received each month, Power Automate automatically retrieves it and downloads it to SharePoint folder, triggering an update to the Tableau Data source for the report.
While Tableau can connect to SharePoint as a data source, it has limitations in terms of post-update actions. If we were using Power BI instead, we could add a button directly to the PowerBI Dashboard to fetch data, which would be particularly useful when unexpected data is provided by stakeholders mid-month.
Conclusion
In summary, both Power BI and Tableau offer robust solutions for data modelling.
However, Tableau excels in basic dynamic visualisations and flexible data modelling. On the other hand, Power BI stands out for its seamless integration with the Microsoft ecosystem, advanced data preparation capabilities, and powerful DAX calculation engine, making it a comprehensive solution.
Based on my personal experience, users should consider Tableau when the primary focus is on in-depth data analysis and creating complex dashboards.
Conversely, Power BI is preferable for scenarios requiring extensive data transformation, sophisticated data modelling, and integration with Microsoft products. For large organisations with a long-term perspective, Power BI often proves to be a more robust solution, due to the Microsoft eco-system.
Author: Ameet Anand
Need some expert advice?
At Skillfield, we understand that selecting the right BI tool is crucial for your data strategy. Whether you prioritise advanced visualisations or efficient data preparation, both Tableau and Power BI offer distinct advantages. Our team of experts is here to help you navigate these options and implement the best solution for your needs. Contact us to get personalised advice.
