Facts tables share dimension key that can have different values

[cvanderdrift]

Hi,

I'm trying to create an app were I can monitor all productionorders that are created. 

I want to keep track on the items and quantities that are consumed in the pack order and the items and quantities that are created. Also I want to see to which customers the produced lots are sold.

I have the following tables;

Facts

• PackOrderline: this table contains the productionorders
• Input: this table contains all lotnumbers and quantities that are consumed in the packorder
• Output: this table contains all lotnumbers and quantities that are created in the packorder
• Sales: this table contains all lotnubers and quantities that are sold

Dimensions

• Source
• Customer
• MasterCalendar
• Item
• LotInfo

I'm having difficulties to fit the 'Item' table in my data model. Because this is a production module, the 'Input Item No' will be different than the 'Output Item No'.

Do I need to load the "Item" table multiple times as seperate dimensions? Or is there a way to do this differently?

[marcus_sommer · Accepted solution]

I think I would merge the four fact-tables into a single table. Probably by concatenating the input- and output-table and maybe also the sales-table. By the production-table I'm not sure if a concatenate is suitable or if its better to map the relevant information to the other fact-tables. Further harmonizing all field-names (only a single field for QTY, Weight and so on) and data-structures and filling all possible information from the other fact- and dimension-tables. Also adding an extra Source field to each fact-table which is then used within the UI as dimension, selection and/or set analysis condition to differentiate between them.

In the end it would be a star-scheme data-model which is the officially recommendation data-model as best compromise in regard to efforts and performance. 

[cvanderdrift]

Thanks, that pushed me in the right direction!

Got it working! 👍