Mixed grain issues

View previous topic View next topic Go down

Mixed grain issues

Post  gcoello on Wed Jun 17, 2009 2:31 pm

I am in the process of modelling a typical sales invoice. The header contains the usual suspects...Invoice Date, Customer Number, Tax Amount, Total Amount. The detail is also unremarkable...Product Number, Qty Ordered, Qty Shipped, Total Amount.

In reading Design Tip #95, it indicates that I should allocate all header amounts down to the detail level, so that all information can be contained in a single fact table. This doesn't work for me, because not all header amounts can be allocated down to the detail level. Certain line items are tax exempt, and the source system does not identify which line items are tax exempt and which are not.

Would the best solution be to use two fact tables?

FactHeader
InvoiceDateKey
CustomerKey
TaxAmount
TotalAmount
InvoiceNumber (DD)

FactDetail
InvoiceDateKey
CustomerKey
ProductKey
InvoiceNumber (DD)
QtyOrdered
QtyShipped
ExtendedAmount

gcoello

Posts : 3
Join date : 2009-03-24

View user profile

Back to top Go down

Re: Mixed grain issues

Post  ngalemmo on Thu Jun 18, 2009 12:18 am

Two tables will work. Use the header table for tax reporting and the other for almost everything else. At least it is clear where sales tax lies.

But, another way is to put sales tax in the line level place the value on one line (and zero for the other lines). It eliminates the other table, but does introduce confusion in the model (not a good thing), as users may attempt to include sales tax in a query grouped by product. (I can only begin to imagine the flurry of email a query like that would cause!)

So, you're on the right track. But, if you can somehow properly allocate tax to a line, it would be the better solution overall.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Mixed grain issues

Post  VHF on Wed Jul 15, 2009 12:08 pm

In the (non-ideal) scenario of storing sales tax as rows in the FactDetail table, how should ProductKey be set for those records (as they would not apply to a specific item in the Product dimension)?


Last edited by VHF on Wed Jul 15, 2009 12:16 pm; edited 1 time in total (Reason for editing : clarified question)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Mixed grain issues

Post  ngalemmo on Wed Jul 15, 2009 2:07 pm

Usually you manually create a 'Sales Tax' row in the product dimension with a made-up natural key then use that key to locate the PK during fact loading. The purpose is simply to have a row the product key can point to. You would still have a tax jurisdiction dimension if such information is necessary for your application.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Mixed grain issues

Post  Sponsored content


Sponsored content


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