1

I'm having difficulties with designing the Primary and Foreign key relationship between my fact table and a Type 6 SCD Dimension table.

The dimension table has the following definition:

CREATE TABLE DimTable
(
surrogate_key           INT,
row_key                 INT IDENTITY (1,1),
natural_key             INT NOT NULL,
current_value           INT NOT NULL,
historic_value          INT NOT NULL,
is_current              BIT NOT NULL,
record_start_date_id    INT NOT NULL,
record_end_date_id      INT NOT NULL

-- Primary Key
CONSTRAINT pk_dimtable_surrogate_key_row_key PRIMARY KEY (surrogate_key, row_key);

A sample of how the data looks like:

surrogate_key | row_key | natural_key | current_value | historic_value | is_current | record_start_date_id | record_end_date_id
-------------------------------------------------------------------------------------------------------------------------------
121           | 2591227 | 123456      | 20090807      | 20090807       | 0          | 20180807             | 99991231
121           | 2591228 | 123456      | 20140807      | 20090807       | 0          | 20180807             | 99991231
121           | 2591229 | 123456      | 20141107      | 20140807       | 1          | 20180807             | 99991231
122           | 2591230 | 456789      | 20090807      | 20090807       | 1          | 20180807             | 99991231

From my understanding of the wikipedia page, I should be able to enforce Referential integrity through PK/FK relationship, however the master surrogate key is not unique across this table so I don't know how to point the surrogate_id in my fact table to the surrogate_key with a FK constraint.

Is there any way around this limitation, or do I understand the description wrong?

Btw, this is my first time asking a question here, so if anything is unclear or missing please let me know!

EDIT: Column names are generic dummynames. The actual colnames are more descriptive.

sndekoning
  • 11
  • 2
  • `surrogate_key` is a horrible column name. Especially since it's not a surrogate key... – jarlh Aug 08 '18 at 11:41

1 Answers1

0

I believe you misunderstood the concept of SurrogateKey. Instead the Row_Key attribute here makes more sense of SurrogateKey. I suggest go one and read books to understand the surrogate key. You may require lot of changes in your process.

lokesh
  • 85
  • 1
  • 9
  • I guess so, but that makes the Wikipedia example even more confusing. In the pure type 6 implementation example it says the following: "A pure Type 6 implementation does not use [a surrogate key for each time slice], but uses a Surrogate Key for each master data item (e.g. each unique supplier has a single surrogate key)." Doesnt this imply that it is possible that a surrogate key can be non-unique if there are historical records in the dimtable?? – sndekoning Aug 08 '18 at 12:58
  • Well.. Understood your point. You are right in the term that it is a case of Pure 6 SCD. But within Wikipedia if go down and read this statement about SurrogateKey and Natural Key for same example. They have mentioned. **"Some cautions: Referential integrity by DBMS is not possible since there is not a unique key to create the relationship."** – lokesh Aug 09 '18 at 15:26
  • I don't think physical referential integrity is possible in Pure 6. May be someone else could no better. As far as i think off, You should make it as Type2\Type6 which will require process of Fact everytime whenever dimension is changed. **OR** Normalise your Pure 6 Dimension to make a mini dimension. Hence you will have two dimension table – lokesh Aug 09 '18 at 15:30
  • Thanks @lokesh, I had hoped there was something I'd overlooked. The type 2/6 hybrid best fits my purpose, but is a bit more complex in dealing with changes in the dimension. – sndekoning Aug 13 '18 at 08:23