Date: Tue, 1 Sep 1998 11:44:46 -0700
Reply-To: "Berryhill, Timothy" <TWB2@PGE.COM>
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: "Berryhill, Timothy" <TWB2@PGE.COM>
Subject: Re: Datamart Design Question (How to store changing data)
Clive, Experts disagree...
In our shop, the vast majority of the analysis requests are for yearly
totals or averages. We keep 12 monthly buckets, so the file has perhaps 100
ID or annual or static variables and a couple of thousand "bucketed"
variables (rev1-rev12, kwh1-kwh12, ...). This makes it easy to identify
customers with higher summer demands, or customers with yearly averages in
certain ranges. We keep a rolling 12 month file on DASD, and freeze it on
tape each month. I recognize the theoretical purity of normalized design,
and we DO have problems with customers who change identifiers, but nobody
has the horsepower to join 5 tables of 4 million records each to answer some
query. Finding things like which customers have their own minimum month
during the system maximum month cry out for arrays.
Tim Berryhill - Contract Programmer and General Wizard
TWB2@PGE.COM or http://www.aartwolf.com/twb.html
Frequently at Pacific Gas & Electric Co., San Francisco
The correlation coefficient between their views and
my postings is slightly less than 0
> From: Clive Granger[SMTP:cliveg@IHUG.CO.NZ]
> Reply To: Clive Granger
> Sent: Monday, August 31, 1998 12:33 PM
> To: SAS-L@UGA.CC.UGA.EDU
> Subject: Datamart Design Question (How to store changing data)
> Hi there,
> Just a bit of background first...
> I am developing a SAS datamart of Credit Card data. We are not looking at
> anything fancy as the bank I work for is also in the process of building a
> full data warehouse. The datamart is being built on an MVS platform
> because that is where the legacy card processing system (Cardpac) runs
> Does anyone have any insights into this?