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

Dimension same size as Fact

2 posters

Go down

Dimension same size as Fact Empty Dimension same size as Fact

Post  Type2 Mon Oct 05, 2009 8:49 am

I have a fact table designed to hold catalog information where the grain is defined as one row = one catalog item fact by warehouse by warehouse location in order to show warehouse inventories. One item may be in multiple warehouses, and in each the item may be in multiple inventory locations.

The catalog item dimension that support this (and other fact tables) is designed to relate to these facts...but in the current design it's nearly a one to one relationship as many dimensions of these facts are very "individualistic". A single catalog item may be in 4 locations in 2 different warehouses and have different buyers, re-order points etc. I can't really normalize this any more to break the dimensions into smaller, meaningful tables.

This doesn't really hit the order of magnitude "rule" for dimension tables, but I'm leaning on keeping the design this way for a few reasons:

1) Simplicity
2) The current inventory fact table will be 250,000 rows, the dimension about 240, it's not a huge set of data
3) The inventory snapshot fact table (simply a month end snapshot of the current inventory) will be loaded every month, growing by 250,000 rows each time, making its ratio to the dimension greater each time.


Posts : 6
Join date : 2009-02-03

Back to top Go down

Dimension same size as Fact Empty Re: Dimension same size as Fact

Post  ngalemmo Mon Oct 05, 2009 12:28 pm

It sounds like you should be breaking down you catalog item dimension. I get the sense that a row in that dimension represents an item in a warehouse in a particular location. It seems to me, there should be an item dimension, warehouse dimension, possibly a location dimension (although that may be degenerate) as well as others, such as buyer.

I'm not sure where re-order points come into this, usually this is purely an operational attribute, but it may be recorded in the fact.

You need to ask what is the purpose of what you are building. I assume the data warehouse will not be used to generate purchase orders, pick lists or other inventory management tasks. Typically, inventory in a data warehouse is usually implemented as a month-end snapshot as it is not a reliable source for up-to-the-minute inventory counts (that is what the operational system's inventory module is for). In such snapshots, inventory location and many other attributes, have no use. In cases where location indicates a process step for inventory (such as receiving, QA, shipping) it is often reflected as separate counts in the fact row.

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

Back to top Go down

Back to top

- Similar topics

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