topic Re: Facts tables share dimension key that can have different values in App Development

Facts tables share dimension key that can have different values

cvanderdrift — Tue, 18 Jul 2023 08:26:24 GMT

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?

Re: Facts tables share dimension key that can have different values

marcus_sommer — Tue, 18 Jul 2023 13:05:06 GMT

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.

Re: Facts tables share dimension key that can have different values

cvanderdrift — Mon, 24 Jul 2023 15:23:24 GMT

Thanks, that pushed me in the right direction!

Got it working! 👍