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.
Instead of me providing answers, let me provide questions.
what are your transactions and data tables? This is where you start. I think you have
consumption
stock
orders.
Don't assume you need to use the same names as your source system. You can call it "outs" if you like, but to me it is "consumption"
these are all fact tables.
Next step is to determine if you need 3 fact tables, or if you can combine these into 2 or 1 table. I think 3 is fine.
then ask, who, what, when, where.
who: vendor
what: item
when: date
where: not sure.
Keep the minimum i of about who, what, when, where in the fact table: just the keys. Put all the detail in the dim tables.
these are general guidelines. I hope it helps.