Dimension same size as Fact

[Type2]

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,000...so 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.

Thoughts?

[ngalemmo]

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.