I need your opinions on which way is best suited for OLAP to store the
historical data.

We need to capture the resource usage/booking level on the daily basis
and will be using the data collected to help analyze the efficiency in
our business process. The resource usage/booking rate is in a single
table with 200,000 rows with each row represent a single resource at a
particular time in the future. We could take a snapshot of the table
and append to the Archive table. Or we could just archive the rows
that were changed since last archive time and append those rows to the
Archive table.

The snapshot approach is straight forward and the data is ready to be
analyzed with stat tool or graphing tool since it has the complete
picture of what the resource usage/booking level looks like over time.

The second approach which just copies the changed rows into the
Archive table is a little bit harder to implement, but the size of the
Archive table is a lot smaller. However most importantly, the data in
the Archive table represents the changes and thus can not be used
directly for analysis like drawing histogram or doing statistical
analysis without back filling the data points for the days the
resource didn't change (which is very difficult to do in a few
straight sql statement).

My question is, should we store the data in the way it can be easily
used for analysis or in the way it is efficiently stored?

Hi,

I would go for all the data every time.

Basically, the approach I take is related to Essbase and Analysis Services.

It sounds like this resource usage data is the main transaction table for
the OLTP system, so consequently, I am assuming that you are pulling this
data into your cube.

What kind of detail can you give on the cube-building process involved?

Bob


"Yaxiong Lin" <lin.yaxiong (AT) mayo (DOT) edu> wrote