Inventory Model
Page 1 of 1
Inventory Model
I work in manufacturing, and we would like to be able to report on current inventory levels and current shipments of finished goods for the month.
I can see declaring the grain at tag level saying a fact row is created when a tag is created, or the answer could be more specific and
say when a RAW tag is received, a WIP tag is created, a finsihed goods tag is created etc.
Our inventory is spread across several tables in our source system, and it would be nice to consolidate so that a plant
could see all inventory for their location regardless of the type it is. Other uses would filter on the tag type for reports.
Inventory can be in the state or RAW, WIP, At an Outside Processor, a finished good, or finished goods history.
Does it make sense to model this as one fact table. Some dimensions are shared across types but not all.
For example, finished goods would have a production but Outside Processor and WIP would not.
I could deal with these cases with dummy records.
Some measures in the fact table would have to be zeroed out depending on what we were looking for.
For example, if we wanted to include information used for other finished goods repots I'd have to include cost information and that wouldn't exist for WIP, RAW, or Outside Processors.
If these were treated as separate FACTS, I know we could ETL them up to a summary FACT table. I just know of the desire to consolidate inventory in the database so that might be a harder sell here.
I can see declaring the grain at tag level saying a fact row is created when a tag is created, or the answer could be more specific and
say when a RAW tag is received, a WIP tag is created, a finsihed goods tag is created etc.
Our inventory is spread across several tables in our source system, and it would be nice to consolidate so that a plant
could see all inventory for their location regardless of the type it is. Other uses would filter on the tag type for reports.
Inventory can be in the state or RAW, WIP, At an Outside Processor, a finished good, or finished goods history.
Does it make sense to model this as one fact table. Some dimensions are shared across types but not all.
For example, finished goods would have a production but Outside Processor and WIP would not.
I could deal with these cases with dummy records.
Some measures in the fact table would have to be zeroed out depending on what we were looking for.
For example, if we wanted to include information used for other finished goods repots I'd have to include cost information and that wouldn't exist for WIP, RAW, or Outside Processors.
If these were treated as separate FACTS, I know we could ETL them up to a summary FACT table. I just know of the desire to consolidate inventory in the database so that might be a harder sell here.
shelzalee- Posts : 6
Join date : 2011-12-06
Similar topics
» Inventory model: aging
» Complex Inventory Model
» Inventory Snapshot
» Inventory Table
» Rule based algorithm to convert an ER model to a dimensional model
» Complex Inventory Model
» Inventory Snapshot
» Inventory Table
» Rule based algorithm to convert an ER model to a dimensional model
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum