History retention 2

[sweetleaf]

Hi,

In my experience working with DW's there hasn't been one place where history is stored (eg. Staging/warehouse area vs. data marts). I've been at companies where it's been either or both. I would think that data marts ought to hold history for month-over-month, year-over-year type reporting.. can anyone shed light on this?

Thanks.

 

[MDXer]

In my current projects I keep history in the DW and in some specialized marts. My Clients require All types of comparitive analysis for anywhere between 2-5 years. For one of the source systems than runs some obscure DB that is a pain to load historical data from I even dump it's raw records to csv files and rar the flat files to keep that level of history easily accessible.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[johnherman]

According to Inmon and the CIF folks, only recent history is kept in the ODS or Staging area. Aggregated data can be kept for many years as required.

According to Kimball, the Staging area is strictly for staging and is purged before each new load. All history is kept in the dimensional data store (star schema) according to data retention requirements.

Aggregate tables (aka Data Marts) may exist, even without supporting detail, in either architecture.

As MDXer stated, data retention requirements can vary between departments. Actuarial types in Insurance like very long time series of data. Accounting may not care more than 24 months. Legal may tell you to keep Insurance claims for 7 years. When dealing with toxic waste, the EPA makes you keep records FOREVER.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[sweetleaf]

Thanks guys,

Thats what I thought. You've both been very helpful!
johnherman, thanks for citing industry examples.

Cheers.