Modeling Question

[JasonHilton]

I've run into a bit of a snag working on the dimensions/facts concerning individual line items on an invoice and hope someone might be able to give me direction.

The current plan is to take the data concerning these line items and split them up. Taking the descriptive information which is based off of the products the customers are contracted for and placing it into the dimension table, and putting the measurables into a fact table. So we end up with something similar to this :

Invoice Detail Dimension
Description
ChargeType
Etc.

Invoice Details Facts
InvoiceDateId
UnitPrice
Quantity
Price
Etc.

The problem comes into how to handle the invoice specific data that relates to these details. I would really like to include the InvoiceNumber, OrderNumber and InvoiceStatus, but do not know where to put this information. These values aren't additive so I'm not sold on putting them in the Invoice Details Fact table, but we would be adding about 50,000 records a month (and growing) if I put this data in it's own Invoice Dimension.

Where should this invoice specific data live? In a Dimension that I realize is going to grow quite large? Or do I include it as unmeasurable facts on my detail fact table?

BTW : I'm not concerned about the Invoice Details dimension growing as large because multiple customers will have signed for the same product, so they would share records.


Jason

[ngalemmo]

Document numbers can be stored in a fact table as degenerate dimensions. A degenerate dimension is a value (usually a business key) that does not have a proper dimension of its own. It is very common to do this with document numbers as in your current situation.