Forum Discussion
Need Help with Data Model
Hi TimmK ,
the best practices refer to Power BI as a model based tool and not as a report based tool. The goal here is to design a data model that can be applied to various questions and reports.
With a good data model you have common dimensions by which you can view your different metrics. At the same time, your model should be stable in case of changes, e.g. if you need to add another dimension or metric.
For example, the date dimension is one of the most important dimensions from my point of view and should be linked to almost every fact table. So, if modeled correctly, you can determine the inventory at any point in time (running total), your consumptions or receipts at certain points in time, and the delivery quantity at the delivery date.
A good BI data model should give analysis capabilities and not just solve a reporting question.
Hi TimmK ,
the best practices refer to Power BI as a model based tool and not as a report based tool. The goal here is to design a data model that can be applied to various questions and reports.
With a good data model you have common dimensions by which you can view your different metrics. At the same time, your model should be stable in case of changes, e.g. if you need to add another dimension or metric.
For example, the date dimension is one of the most important dimensions from my point of view and should be linked to almost every fact table. So, if modeled correctly, you can determine the inventory at any point in time (running total), your consumptions or receipts at certain points in time, and the delivery quantity at the delivery date.
A good BI data model should give analysis capabilities and not just solve a reporting question.
Thank you, mwegener
This makes sense to me in general having this kind of model perspective being able to answer various questions, also those that may not be seen yet.
However, could possible performance improvements also be important/useful in some cases? For example, when I aggregate in Power Query I can reduce the rows from let's say 1.5 million to just a few ten thousand rows. This may result in reducing overall loading time four times compared to no aggregation. So maybe one could trade-off universal flexibility/changeability with better performance in some cases?
Kimball briefly describes here "Aggregate Fact Tables" and their use to accelerate query performance:
Aggregate Fact Tables or Cubes | Kimball Group
Hi TimmK,
I would consider the performance improvement secondary. Since Power BI's analytics database is optimized for aggregating data. The main focus should be on usability and maintainability at the beginning. In my view, an increased effort for performance is only justified in case of concrete performance problems.