Breaking Up Blended Transaction Table (Inventory Control)

Good Morning,

I'm currently modeling an inventory control process and was hoping to get some input/feedback on breaking up a blended transaction table.

The source system has a single table that stores inventory transactions (this will obviously be the source for an inventory periodic snapshot table). There are a variety of transaction types in this table (inventory checkout, inventory return, stock adjustment, move stock, etc). For each transaction there is a "ChargeToEntity" which can refer to a number of business entities depending on the transaction type.

For example, an "inventory checkout" transaction can be charged to a "work order", "employee", "equipment", or a "location". While a "stock movement" transaction can only be charged to a "location".  Then there are the "manual price adjustment" transactions which aren't charged to anything (this is more for tracking the value of inventory on hand at various points in time).

I know I need to break the transactions into separate inventory-fact tables (ex. Fact Inventory Checkout, Fact Inventory Return, Fact Inventory Stock Adjustment, etc).  However, should the fact tables for the transaction types that can be charged to different ChargeToEntities be further broken down?

For example, should the Fact Inventory Checkout table be broken down into:

• Fact Inventory Checkout Charged To Employee
  
• Fact Inventory Checkout Charged To Work Order
  
• Fact Inventory Checkout Charged To Location
  

Or is that overkill?  The other option being a single fact table with multiple ChargeTo fields:
Fact Inventory Checkout

• Charge_To_Employee_Key
  
• Charge_To_Work_Order_Key
  
• Charge_To_Location_Key
  

Update 20130923: to add a bit more context, the transaction table in the source system uses a generic fields (ChargeTo + ChargeToId) without FK-constraints to model this...where the ChargeTo value indicates the entity (employee, work order, or location) and the ChargeToId is the key that can be used to join to the entity table.

bump for input :-)

If you think about it , there should only be a single fact for the Inventory_Checkout (Inventory Issue) process, one record for each item issued from inventory.

When I've done this in the past, I included keys to all three dimension on the fact (Employee, work order, Location).

Just make sure you have dimension records for "Unknown" so you can populate all the keys.

You may also want to include a "charge_type" flag (or dimension) - I've found it makes queries/reports a little more straight forward.

- Hope it helps.

First, I am not convinced you need to break up the inventory transaction fact in the first place. Wither it be an issue, return, or stock adjustment they are all simply changes to inventory which can be reflected by either a positive or negative quantity and value. They are simply different transaction types. For example, in a banking application you would not expect to see a separate deposit fact and a separate withdrawal fact. Inventory is essentially the same thing. Just add a transaction type dimension.

On the second point, who/what it is issued against, this is very similar to what happens in logistics. You have a shipping fact table and the ship from and ship to references could be to a multitude of different entities: customers, warehouses, stores, vendors, distribution centers, and so forth. A while ago (1999 or 2000) Ralph invited me to write an article for his Intelligent Enterprise magazine column and I wrote on this topic and proposed what I called a 'Unity Dimension'. In relational terms it would be referred to as a sub-type cluster. Essentially it would be a consolidated dimension of all the possible entities with a subset of attributes, including the natural key, they have in common and any type specific attributes you may want to include for reporting purposes. You would then have a single FK in the fact that references this dimension. In the article I suggested this is easiest to do if you use an abstracted natural key, that is to say as single string made up of a delimited concatenation of the components of the NK, so your logic only needs to deal with a single column, rather than different columns depending on the type. It is also much simpler if all dimensions have mutually exclusive surrogate keys (i.e. you use a single sequence to assign all dimension surrogate keys). If that is not the case, you would include the true dimension SK in this table as well should the user wish to include attributes for a query on a specific entity (for example, looking only at issues to a work order).

Bottom line, you should only have one fact table. Breaking it out into multiple fact tables will only drive you and the users crazy.

Thank you both for the input. I ended up breaking everything out for the sake of flexibility and to avoid painting myself in a corner (this was unfortunately one of those "cart before the horse" modeling scenarios where the metrics and intended usage by end users was unknown at the time of development...here's the source system, go model it).  FWIW, I used a layer of views to consolidate everything into a small handful of tables (and then created calculated measures in the MDXScript to further consolidate things) for the initial cube...to simplify things from the perspective of the user.


Nick - I like the "Unity Dimension" idea (unfortunately it's too late to circle back on this particular project) do you have a link for further reading?

Intelligent Enterprise folded and the link is gone.  However, I did manage to find a cached version of the article at http://www.gigablast.com/get?q=&c=dmoz3&d=68107678507&cnsp=0

...except it only shows the first page. You can try doing a more extensive google search.

awesome - ty

btw, after a bit of googling, I found this source...which appears to be a complete ripoff of your article w/ zero credit ;-)

Hmmm... well, imitation is the sincerest form of flattery.