Modeling Question

View previous topic View next topic Go down

Modeling Question

Post  JasonHilton on Tue Jul 26, 2011 9:59 am

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


JasonHilton

Posts : 3
Join date : 2011-07-26

View user profile

Back to top Go down

Re: Modeling Question

Post  ngalemmo on Tue Jul 26, 2011 10:38 am

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

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

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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