Forum Discussion
Need help with a data model
Hey guys,
So I've been working on a data model, but I cannot seem to get it to work properly. I've tried a lot but I'm a bit lost at this point. It feels like there is a simple solution to my problem, yet I cannot seem to find it.
The data is sensetive, so unfortunately I am not able to share it, but I'll try to be as concise and clear as possible.
The data is about the purchasingprocess of my employer.
In short I have 4 tables (with information about):
1. Suppliers
2. Contracts
3. Purchasing Orders
4. Invoices
A supplier can have several contracts,
a contract can have several Purchasing Orders,
a Purchasing Order can have several invoices
However the invoices can have these 4 options:
1. An invoice with: A contract, Purchasing Order and a Supplier
2. An invoice with: A contract and a Supplier
3. An invoice with: A Purchasing Order and a Supplier
4. An invoice with: Only a Supplier
I made a data model that follows the following route: Supplier --> Contract --> Purchasing Order --> Invoice
I am able to manage relations 1, 2 and 4 from the invoice options above.
I made a seperate table for invoices with all the information which was able to follow the entire path, so no problems here
I made a seperate table for invoices with only contracts and suppliers, I connected this to the contracts table (it skips purchasing orders)
I made a seperate table for invoices with only suppliers, I connected this directly to the suppliers table
However, I'm having trouble with option 3 (An invoice with a Purchasing Order and a Supplier, but no Contract). When I try to fit this one in the model it is causing trouble because there is no contract and therefore the model is not able to "walk the correct path".
On the reporting side we want to report on all levels.
So we want to see the Supplier with all its contracts, purchasing orders and invoices, eventhough not all invoices of this supplier have a purchasing order, contract or both. Also we want to see all purchasing orders for a contract, all invoices for a purchasing order, etc.
Does anyone know how to fix this?
4 Replies
- IdrissshatilaSuper User
Hello Anonymous ,
you need to have dimension tables for the fields that you may use to filter more than one table and connect them to the tables you've mentioned.
check the concept of star schema data modeling https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
- AnonymousNot applicable
I thought about a similair structure. That would mean that the invoice table would be the fact table and the other tables will be the dimension tables, right? Does this also ensure that I am able to filter on all levels and not just on the invoice-table?
- IdrissshatilaSuper User
HEllo Anonymous ,
true you can filter the invoice table from the other tables.