Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Item Master and Inventory Organisation

2 posters

Go down

Item Master and Inventory Organisation Empty Item Master and Inventory Organisation

Post  lmetodiev Mon May 18, 2009 8:52 am

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?

lmetodiev

Posts : 13
Join date : 2009-02-20

Back to top Go down

Item Master and Inventory Organisation Empty Re: Item Master and Inventory Organisation

Post  ngalemmo Mon May 18, 2009 12:27 pm

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?
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Item Master and Inventory Organisation Empty Re: Item Master and Inventory Organisation

Post  lmetodiev Mon May 18, 2009 4:04 pm

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.

lmetodiev

Posts : 13
Join date : 2009-02-20

Back to top Go down

Item Master and Inventory Organisation Empty Re: Item Master and Inventory Organisation

Post  ngalemmo Tue May 19, 2009 1:59 pm

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...
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Item Master and Inventory Organisation Empty Re: Item Master and Inventory Organisation

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum