Re: data warehouse

From: <bmeltz_at_mediaone.net>
Date: Wed, 13 Sep 2000 14:53:21 GMT
Message-ID: <8po4cm$ner$1_at_nnrp1.deja.com>


We had just such a problem with my last warehouse. The line number of an order could change. We had to construct and test a surrogate key of order number + sku + other dimensional information in order to calculate the keys of the fact record.

You are right, it was a pain, but there is no way around it.

David Cressey is also right that no matter how you identify an individual, you would want to abstract the actual primary key to something like a seqentially generated number.

With individuals, you may need to conceed that you will have some duplication. Unless the source system has some kind of unique identifyer, SSN may be the best you are going to do. Anything else is even fuzzier.

Barry

In article <39BF8355.15344659_at_memphis.edu>,   "Rajkumar Manickavasagam (Raj)" <rajm_at_memphis.edu> wrote:
> This is a multi-part message in MIME format.
> --------------A5C3A08C9808CAACA863F57C
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> i understand what you say....but our problem is that our source
 systems use SSN
> to identify a person's record and we all know that these numbers can
 change
> especially in a higher ed institution. that is why we want to make
 up our own
> identifier in the warehouse...something that would be a true primary
 key. now
> i am repeating what i had written in my earlier posting-
> Has anyone faced a situation in data warehousing where your source
> system has primary keys that could change? In such a case one would
> develop artificial keys for the warehouse and maintain a map between
 the
> id's in the warehouse and the source system Maintaining the map would
> be difficult because of the volatility of the primary keys.
>
> If anyone has a solution please let me know
>
> Raj
>
> David Cressey wrote:
>
> > There are other reasons to use artificial keys (or "surrogate
 keys") in
> > data warehouses.
> > You may wish to relate a fact to the dimensions as they stood at
 the time
> > the fact was
> > stored.
> >
> > For example, the price of an item may vary over time. If the fact
 is a
> > sale of the item,
> > you may have lots of different rows in the dimension table that all
> > correspond to the
> > same item, but at different points in time, and with different
 prices. A
> > fact denoting a sale
> > would be linked to the correct entry in in the dimension table via a
> > surrogate key.
> >
> > Rajkumar Manickavasagam (Raj) wrote in message
> > <39BE961C.B417C3BE_at_memphis.edu>...
> > Has anyone faced a situation in data warehousing where your source
> > system has primary keys that could change? In such a case one would
> > develop artificial keys for the warehouse and maintain a map
 between the
> > id's in the warehouse and the source system Maintaining the map
 would
> > be difficult because of the volatility of the primary keys.
> >
> > If anyone has a solution please let me know
> >
> > Raj
>
> --------------A5C3A08C9808CAACA863F57C
> Content-Type: text/x-vcard; charset=us-ascii;
> name="rajm.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Rajkumar Manickavasagam (Raj)
> Content-Disposition: attachment;
> filename="rajm.vcf"
>
> begin:vcard
> n:Manickavasagam;Raj
> tel;fax:901.678.3061
> tel;work:901.678.0849
> x-mozilla-html:FALSE
> org:The University of Memphis;Information Systems Operations
> adr:;;;;;;
> version:2.1
> email;internet:rmnckvsg_at_memphis.edu
> title:Database Programmer Analyst
> fn:Rajkumar Manickavasagam
> end:vcard
>
> --------------A5C3A08C9808CAACA863F57C--
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Sep 13 2000 - 16:53:21 CEST

Original text of this message