Accumulate Stock

[ribula]

I am working for a manufacturing company and I am creating a datamart for stock and delivery analysis.
We have the big amount of delivery and the sales transaktion.
The problem is that delivery has a big gap before the first
transaction will came.
We need to keep track and calculate stock amaunt from the
first delivery for product and location.
I want to avoid to store the unchanging delivery and stock store in fact table. The fact table is alrady around
500 000 000 rows. How i can calculate stock and fined last change record with a god prestanda.
Mi DW is on Oracle.
Thanks for all tips

 

[ribula]

Hi!
Are enybody works with stock datamart and has prestanda problem. Mi quastin if it is Oracle help to make solution bater. The big volum on tables and demanding to find first delivery transaktion for some location product and find last sales transaktion for the same product must be vary haevy sql and it is supous take time. I wounder if somebody knows best solution tips to avoid to store unchanged stock information to store in table and speed ETL process to build datamart.
Thanks again

 

[Hans63]

HI Ribula,

lets see if I get your quetion right.

You have a big transaction table with sales AND delivery information (do both facts reside in this table, or are they seperate source tables?).

There is a large gap (in time) between a sale and the first delivery.

You have to keep track of the stock amounts FROM the first delivery onward (for production and location).
You don't want to store identical fact records in your fact table.

One way to get rid of these identical records is to slightly change your fact table. Add a date_from and a date_to field which identify the time a record is valid. Of course your queries on this table have to take these dates into consideration.

To speed this in you ETL proces you have to identify all the records that have changed on your ETL-date. So you need to be able to identify these changes. But you probably want to identify these changes as soon as possible in order to avoid needless processing.

Hope this helps a bit.