1

I want to create a data warehouse from 1 OLTP database. The tables in the OLTP db have got integer primary keys. So they are the business keys.

The tables are: Client, customer, products and sales. They have primary key and foreign key relationships.

I am writing an ETL to model this into dimensions and facts.

My manager insists that I create surrogate keys. I know to achieve this I will have to load the dimension tables first (so that they get their surrogate key), and then load the fact table by using the business key to add the corresponding surrogate key into the fact tables. I know this is appropriate when the business keys are alphanumeric or large value but in my case the business keys are auto incrementing integers.

In my situation, what is the disadvantage of not creating surrogate key?

variable
  • 3,591
  • 3
  • 28
  • 81
  • 1
    I would suggest that your tables /already have/ Surrogate Keys - those meaningless, ever-increasing numbers that tie everything together but probably don't mean /anything/ to your Users. If anything, your process should be to remove these and replace them with the User-meaningful names/descriptions, of which the numeric values are only encodings. – Phill W. Dec 14 '21 at 12:24
  • You mean my OLTP tables are already have surrogate keys? I didn't get your point - do you want me to do some changes on the OLTP tables to remove these and replace them with user-meaningful names? I don't want to modify the OLTP database tables. – variable Dec 14 '21 at 12:28
  • 3
    Does this answer your question? Should every table have a single-field surrogate/artificial primary key? – mustaccio Dec 14 '21 at 13:13
  • You /don't/ change the source tables.
    You 'Transform' the data (after 'Extract'ing it and before 'Load'ing it) into your Data Warehouse to present it in more "user-friendly" ways and one way to do that it get rid of, say, "dept_id : 784465" and replace it with "Department : Accounting".     – Phill W. Dec 14 '21 at 14:03

2 Answers2

1

Even if your source data has an id as business key you should use your own surrogate key to manage slow changing dimensions.

For example,you can, in this way, follow the changes made on the product numer 25 in you source database having different versions of this product.

Example of dimension:

id bk   desc             from       to

1  25  product25         2021-01-01 2021-03-31


2  25  prduct25-versionb 2021-04-01 2021-06-30


3  25  prduct25-versionc 2021-07-01 NULL

Without surrogate key you can't.

MBuschi
  • 4,450
  • 1
  • 5
  • 16
  • What if I don't require slowly changing dimension. For example - I am just creating a datawarehouse which is effectively a reporting database. – variable Dec 15 '21 at 11:29
  • Maybe you don't require SCD today but it is a best practice to do it. After all, every DWH or DataMart are for reporting... – MBuschi Dec 15 '21 at 11:32
-1

I think surrogate key in target table in the DWH makes sense. Because the meaning of the candidate primary keys' you wrote about is, that they represent facts in the DWH (e.g. customer 3 purchased product 1). However, they don't seem right to be used to identify records in the DWH (i.e. act as keys).

To answer your question about the disadvantages of not doing as your manager insists: Imagine the source data in OLTP changes after they've been brought over to DWH and you need to reflect the change in the source data also in the target DWH. Without surrogate key, you'd be changing primary key in the DWH (right now I am in this situation myself and surrogate keys will be helpful).

Sranda
  • 360
  • 1
  • 10
  • What do you mean by source data in OLTP changes please? Can you give an example of that point. – variable Dec 14 '21 at 14:40
  • Yes .. in our production, when the finished goods leave the production and enter "packaging" phase, the record in database marking this milestone has also columns "sent to quality" and "sent to erp" ... the transfer of these records to other systems(ERP&quality DB) does not happen immediately, but after some time. Meanwhile, the records are already sent to DWH and as soon as data is sent to erp and quality system (& updated in OLTP), the update in DWH needs to be done too. And we update the DWH based on its surrogate key in tables i question. HTH. – Sranda Dec 14 '21 at 14:49