Item Master and Inventory Organisation
2 posters
Page 1 of 1
Item Master and Inventory Organisation
In Oracle eBusiness Suite there is an Item Master on Instance Level.
Each item can be attached to each inventory organisation and each item attribute from item master can be overwritten on inventory organisation level.
For different purposes can be used either the attributes from item master or the attributes from Inventory Organisation.
In the most facts which contain items we have inventory organisation as well.
For example in Customer Order Line Fact we have:
- Inventory Org (Key to Inventory Org Dim)
- Item (Key to Item Dim)
- Quantity (Measure)
- Amount (Measure)
- Discount (Measure)
But there is a request for a report based on a selection of inventory organisation dependent attribute.
My approach is to create a new combined dimension (Inventory Item Dim) with the inventory org dependent attributes and add this dimension to each fact types in which there are connection to inventory org dim and item dim.
An other idea was to create an outrigger table in which to storage the inventory org dependent attributes but it will be very complex because the item dim is SCD Type 2.
Please advice which approach will be more convinient?
Each item can be attached to each inventory organisation and each item attribute from item master can be overwritten on inventory organisation level.
For different purposes can be used either the attributes from item master or the attributes from Inventory Organisation.
In the most facts which contain items we have inventory organisation as well.
For example in Customer Order Line Fact we have:
- Inventory Org (Key to Inventory Org Dim)
- Item (Key to Item Dim)
- Quantity (Measure)
- Amount (Measure)
- Discount (Measure)
But there is a request for a report based on a selection of inventory organisation dependent attribute.
My approach is to create a new combined dimension (Inventory Item Dim) with the inventory org dependent attributes and add this dimension to each fact types in which there are connection to inventory org dim and item dim.
An other idea was to create an outrigger table in which to storage the inventory org dependent attributes but it will be very complex because the item dim is SCD Type 2.
Please advice which approach will be more convinient?
lmetodiev- Posts : 13
Join date : 2009-02-20
Re: Item Master and Inventory Organisation
An inventory org/item dimension will work. It shouldn't matter that item is a type 2 as this dimension is independent of the item dimension.
What attributes are org dependent? Off hand, I could thing of cost, storage location, maybe accounting codes and a few others. Would it make sense to put cost in the fact table? Could the org dependent attributes be put into a junk dimension?
What attributes are org dependent? Off hand, I could thing of cost, storage location, maybe accounting codes and a few others. Would it make sense to put cost in the fact table? Could the org dependent attributes be put into a junk dimension?
Re: Item Master and Inventory Organisation
Yes, cost is a measure and it is in the cost fact.
The most inventory organisation dependent attributes are the categories - make/buy item class, accounting category, ABC Category, etc. also the differance flags as location controlled flag, serialised flag.
UOM is can be also different - for example in USA inventory organisation in foods, in Europe one in meter.
My problem is that I have to add this combined dimension (item, inventory org) in all facts in which I have both connection - to item and to inventory org - for example - material transaction, purchase order line, customer order line, delivery notes, customer invoice, vendor invoice, item cost, .... and it is a lot of work.
That why I am looking for a more tricky solution.
The most inventory organisation dependent attributes are the categories - make/buy item class, accounting category, ABC Category, etc. also the differance flags as location controlled flag, serialised flag.
UOM is can be also different - for example in USA inventory organisation in foods, in Europe one in meter.
My problem is that I have to add this combined dimension (item, inventory org) in all facts in which I have both connection - to item and to inventory org - for example - material transaction, purchase order line, customer order line, delivery notes, customer invoice, vendor invoice, item cost, .... and it is a lot of work.
That why I am looking for a more tricky solution.
lmetodiev- Posts : 13
Join date : 2009-02-20
Re: Item Master and Inventory Organisation
The static attributes (flags and stuff) could be handled by its own dimension (either junk or a org/item based dimension). UOM conversion facts would need to be expanded to include the org dimension... you would then convert based on org/item/uom.
If you don't want to create a new dimension (and add keys to all the facts) the only alternative would be to have a table keyed by org and item that contains the additional attributes. (Ralph... please don't shoot me for this!) Maybe you can call it a factless fact table with a bunch of degenerate dimensions...
If you don't want to create a new dimension (and add keys to all the facts) the only alternative would be to have a table keyed by org and item that contains the additional attributes. (Ralph... please don't shoot me for this!) Maybe you can call it a factless fact table with a bunch of degenerate dimensions...
Similar topics
» How to model single item vs multiple item components
» Millions of client information that can not be uniquely identified
» DataModelling - Organisation definition
» Separate Product dimension from Organisation ?
» Item Costs - Dimension or Fact
» Millions of client information that can not be uniquely identified
» DataModelling - Organisation definition
» Separate Product dimension from Organisation ?
» Item Costs - Dimension or Fact
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum