Data Warehouse Design Issue

Question

I am developing a data warehouse and am facing a problem. I am not sure how to fix this. The current scheme is defined below:

DimInstructor <- Size chart for instructors DimStudent <- Size chart for students

I want to implement a scenario in which, when changing instructor details in my OLTP database, I want to add a new record to the DimInstructor table for historical reasons.

Now I want to create a lesson dimension table called DimLesson. In DimLesson, I want to create a link to an instructor.

The DimInstructor table contains:

InstructorDWID <- Identification field when entering DW InstructorID <- identifier of the instructor that comes from the OLTP database

Now I can not make InstructorID the primary key, because it is not guaranteed to be unique (if the teacher changes his name, 2 records with the same InstructorID value will be indicated in the DW).

So my question is: how do I contact an instructor from DimLesson? Do I use InstructorDWID? If so, should I have 2 entries for the instructor in the DimInstructor, this will make it difficult to request when I want to see all the lessons from a particular instructor.

Any help would be appreciated!

Answer 1

Pavel

There are several ways to handle this. You can use the effective date / inactive date, serial number, or version number to distinguish between records with the same instructor identifier.

DIM, , .

create table DIM_INSTRUCTOR(
  instr_guid number, --populated through a sequence     -----Composite pk-Part1
  istr_oid   number, --direct id from the OLTP system   -----cmposite  pk-part2
  instr_name number,
  other_attr varchar2(25),
  eff_date   date,
  expiration_date date
);

instr_guid OLTP.

. instr_guid , (instr_guid, instr_guid) .. Datawarehousing.

:

http://en.wikipedia.org/wiki/Surrogate_key http://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2

Answer 2

, , 2. Kimball 2 ETL - .

, , - -. , - , , , . , , dimInstructor :

InstructorKey  InstructorBusinessKey  FirstName LastName  row_ValidFrom row_ValidTo   row_Status
  1234           jane_doe_7211           Jane     Doe       2000-03-11   2010-08-12     expired
  7268           jane_doe_7211           Jane     Smith     2010-08-12   3000-01-01     current

, , dimLesson - ( - ), dimLesson InstructorKey. ETL- (7258) dimInstructor 1234 dimLesson 7268.

Answer 3

guid/uuid