Sparse data in fact table

View previous topic View next topic Go down

Sparse data in fact table

Post  boernard on Wed Mar 07, 2012 2:23 pm

Hello,

I have a transaction fact table with measurements like:
grand total original (eg before voucher)
grand total original net
grand total
grand total net
total net product costs
quantity of items

Now I am thinking about how to integrate returned products. My colleague and I have two different views:

1.
Extend the fact table with the facts
grand total original after return
grand total original net after return
grand total after return
grand total net after return
total net product costs after return
quantity of items after return

Here I see two problems:
a) sparse data! we have about 10% returns, maybe in the worst case 25% of returns
b) You have a more complex ETL process as you have to look up the historical order data and edit it

2.
Every time there is a return, add a row to the fact table with negative values for the mentioned attributes (but the same DegenerateDimension order number). Maybe add an attribute "return reason" in the junk dimension.

What are your thoughts about this? In general, shouldn't one avoid editing historical data in the fact table?


boernard

Posts : 13
Join date : 2012-01-19

View user profile

Back to top Go down

Re: Sparse data in fact table

Post  ngalemmo on Wed Mar 07, 2012 3:09 pm

What is 'grand total' doing in a transactional fact table?

Anyway, just add a new row with negative amounts reflecting the value of the return.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Sparse data in fact table

Post  boernard on Thu Mar 08, 2012 11:12 am

thx for the reply!
grand total is the price that is on the bill which the customer receives. Isn't that right?
Another question:
Should I store the monetary values as integers (in cent) or as decimal (in Euro) ?

And do you have an idea for:
-"In general, shouldn't one avoid editing historical data in the fact table?"

boernard

Posts : 13
Join date : 2012-01-19

View user profile

Back to top Go down

Re: Sparse data in fact table

Post  ngalemmo on Thu Mar 08, 2012 4:54 pm

If this is sales, you usually maintain line level facts, that is if someone buys 5 different items, there are 5 rows in the fact. The total purchase is simply a sum of the rows. If by 'grand total' you mean the sum of the 5 different items, then you are building an aggregate which is not advisable unless it is a summary of an existing atomic level fact table.

Working at the atomic (i.e. lowest level of detail) level, a return is simply another row with negative values. When summed with the other values (Fact tables queries are almost always a sum of some sort), it will properly reflect total gross revenues and quantities sold.

When storing monetary values, always use an exact (i.e. base 10) data type with appropriate decimal positions. Depending on your database, the data type may be called NUMBER or MONEY. Never use a non-exact binary type (FLOAT, REAL, etc...).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Sparse data in fact table

Post  boernard on Fri Mar 09, 2012 7:18 am

Aaah I didn't mention that I already modelled a fact table on line item level. The fact table on order level has additional information which can't be allocated on the line item level. Thank you!

boernard

Posts : 13
Join date : 2012-01-19

View user profile

Back to top Go down

Re: Sparse data in fact table

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