I am from a manufacturing company. We make automotive parts and sell to customers as per their drawing. Every customer has set of part numbers and price for respective part numbers.
My data model looks like this
Following is report for one customer
I have the following questions
- Where should I have the ‘Part Number & Price’ table (AOPSBPPrice) - As Lookup table or fact table?
In my data model, I have kept the same table in both look up and fact table
The reason why I have kept the table both as look up and fact table is –
To filter the list of part number and price that we supply to one particular customer
I create a table with Part Number from PartNumberCustomerMaster table and Price from AOPSBPPrice table
I filter the customer using page level filter in the report
But I get stuck when I have to compare price between two different customers in the same market segment (for example - Passenger Car)
The table in the right hand corner of the report is created without any measure but using a page level filter for particular customer.
Thanks in advance for your help