Executive Summary: This document aims to share valuable insights gathered during Power BI development consulting, specifically focused on the Power BI data model. It emphasizes a crucial design principle: including aggregatable measures in the fact table while avoiding non-aggregatable measures like ratios. Non-aggregated measures do not contribute to the overall model. This approach will save a lot of ETL effort and grant efficient Power BI development.
Context: Most Power BI development journeys commence with the creation of visually stunning dashboards. We have meticulously prepared high-quality data for your use, where the Power BI developer's primary task involves presenting this data through an exquisite dashboard interface. However, we can only realize this mission with a correct data model.
Analysis: Why do we strive for Power BI reports featuring aesthetically pleasing dashboards? We aim to provide meaningful data insights, typically requiring aggregating data at higher levels and performing calculations based on those aggregated values. Attempting to calculate such complex data at an intricate level proves infeasible due to the astronomical number of permutations involved. For instance, if we have 5 dimensions, each containing 10 elements, the resulting combination amounts to 10^5, rendering it either unattainable or unnecessary. Power BI and other BI tools are designed to handle these aggregate calculations natively and on the fly.
Example: To illustrate this further, let's consider two hypothetical companies: Company A and Company B. Our objective is to obtain the total sales, profit, and profit margin.
- Total sales of the two companies = sales of Company A + sales of Company B;
- Total profit of the two companies = profit of Company A + profit of Company B;
- Total profit margin = (Total profit of the two companies) / (Total sales of the two companies)
Please note that such a data model is referred to as the Power BI Query side if it is file-based, the database side if database-based, or the Data Lake side if data lake based. Regardless of what data sources, the underlying concept remains consistent from a data model perspective.
The Worst Model Design: In this flawed model, the only measure present is Profit Margin, as we assumed Power BI would solely display this metric.
- Power BI cannot display data at an aggregate level, preventing the addition of Profit Margin for Company A and Company B. We can't simply add Profit Margin for Company A to Company B.
- Unnecessary calculations burden the calculation side. Such Profit Margin is implemented on the ETL side, reducing the workload for data engineers and streamlining testing efforts.
The Bad Model Design: The inadequate model includes only Profit Margin and Sales columns for Power BI utilization.
- Power BI can display data at an aggregate level by calculating the Margin using the Sales * Profit Margin and deriving the combined Profit Margin. However, this calculation is superfluous and can introduce discrepancies in real-world projects.
- The calculation side continues to face the same challenges as before.
The Better Model Design: A superior data model encompasses solely the Sales column and Profit Column for Power BI utilization.
- Attaining individual Profit Margin and aggregated margin ratios becomes effortless.
- Eliminating the need to calculate profit margin significantly saves time and effort.
Summary: In summary, ensuring that measures within the fact table are aggregatable is imperative, discarding any reliance on ratio measures. Based on my experience, some measures contain base data, and preserving only the Delta data while maintaining the base data as static facts enables Power BI to calculate it autonomously. The key takeaway remains that all measures within the fact table must be aggregatable, except in cases where absolute justifications prevent it.
Notes: Even it is a simple sample to explain this issue, it could extend to much more complicated cases, such as each measure can be aggregated from other measures, along with complex formulas, such as income statements and balance sheets. But the basic idea still applies.
No comments:
Post a Comment