BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sanalitics
Obsidian | Level 7

Hi All,

 

For those who are familier with SAS DDS model for insurance... Kindly let me know if we have a logic which could be followed to generate RK... for example: In the Party table we use Party_id to generate Party_rk. Similar to this do we have a method that could be followed to find all the business keys that could be refered to generate RK? Say for example I want to know all the Key combinations that should be considered to generate the Policy_Rk in the Insurance table ?

 

Thanks,
Sandeep

 

1 ACCEPTED SOLUTION

Accepted Solutions
gostan
SAS Employee

An RK – a retained key – is a type of surrogate key (non-intelligent/artificial key) that remains the same (retained/persist/durable) when a record in a table is changed. RKs (e.g. party_rk) are generated based on a business key (e.g. party_id plus additional business attributes that define a unique instance of a party) and are usually part of  a composite primary key in a table that tracks changes of over time. That other part of the composite key is typical a date/time based column. Let’s take a simplified example from health insurance using a party table:

 

party_rk

valid_from_dttm

party_id

party_type_cd

valid_to_dttm

Party_status_cd

1

01/01/2000

ABC

Provider

01/31/2000

Non-certified

1

02/01/2000

ABC

Provider

 

Certified

2

01/01/2000

XYZ

Member

02/28/2000

Current

2

03/01/2000

XYZ

Member

 

Past

 

party_rk is the retained key

party_rk + valid-from_dttm is the primary key

party_id is a non-composite business key (business keys can be composites too, e.g. name + birthday + gender). Business keys are sometimes called natural keys.

party_status_cd a column for which values have changed over time for an instance of a business key

 

Whenever a value of a business key changes or a new one gets introduced a new RK will be generated. The reason why we don’t use business keys as part of the primary key is because they are often unstable: subject to change if business rules change; difficult to construct if they are sourced from multiple places (especially true for data warehouses); have a tendency to changes values  over time and sometimes getting reused.

 

In case of the insurance_policy table the policy_rk can be generated based on any unique combination of non-key attributes (excludes policy_rk and valid_from_dttm)  that are candidates for a unique business key, and this can be different from customer to customer. For instance, policy_no could be a unique business key for one customer, and yet for another customer policy_no + policy_version might represent a unique policy identifier.

 

RKs are frequently used as part of the primary key design in normalized data warehouse and ODS implementations that manage history.

 

Hope this answers the question about use of RKs in SAS DDSs.

View solution in original post

4 REPLIES 4
gostan
SAS Employee

An RK – a retained key – is a type of surrogate key (non-intelligent/artificial key) that remains the same (retained/persist/durable) when a record in a table is changed. RKs (e.g. party_rk) are generated based on a business key (e.g. party_id plus additional business attributes that define a unique instance of a party) and are usually part of  a composite primary key in a table that tracks changes of over time. That other part of the composite key is typical a date/time based column. Let’s take a simplified example from health insurance using a party table:

 

party_rk

valid_from_dttm

party_id

party_type_cd

valid_to_dttm

Party_status_cd

1

01/01/2000

ABC

Provider

01/31/2000

Non-certified

1

02/01/2000

ABC

Provider

 

Certified

2

01/01/2000

XYZ

Member

02/28/2000

Current

2

03/01/2000

XYZ

Member

 

Past

 

party_rk is the retained key

party_rk + valid-from_dttm is the primary key

party_id is a non-composite business key (business keys can be composites too, e.g. name + birthday + gender). Business keys are sometimes called natural keys.

party_status_cd a column for which values have changed over time for an instance of a business key

 

Whenever a value of a business key changes or a new one gets introduced a new RK will be generated. The reason why we don’t use business keys as part of the primary key is because they are often unstable: subject to change if business rules change; difficult to construct if they are sourced from multiple places (especially true for data warehouses); have a tendency to changes values  over time and sometimes getting reused.

 

In case of the insurance_policy table the policy_rk can be generated based on any unique combination of non-key attributes (excludes policy_rk and valid_from_dttm)  that are candidates for a unique business key, and this can be different from customer to customer. For instance, policy_no could be a unique business key for one customer, and yet for another customer policy_no + policy_version might represent a unique policy identifier.

 

RKs are frequently used as part of the primary key design in normalized data warehouse and ODS implementations that manage history.

 

Hope this answers the question about use of RKs in SAS DDSs.

sanalitics
Obsidian | Level 7

Hi Gosten... Thanks for the response and welcome to the group 🙂

 

Below statement of your helps... For the insurance policy, validating the correct combination of Business keys for generating RK is the challenge I have .. it all depends on the source details though... agree .. let me know if you have some standard model with the business key details for each DDS table that could be referred... I undersatnd that it might not be appropriate for all cases though

 

In case of the insurance_policy table the policy_rk can be generated based on any unique combination of non-key attributes (excludes policy_rk and valid_from_dttm)  that are candidates for a unique business key, and this can be different from customer to customer. For instance, policy_no could be a unique business key for one customer, and yet for another customer policy_no + policy_version might represent a unique policy identifier

 

 

Patrick
Opal | Level 21

In case of the insurance_policy table the policy_rk can be generated based on any unique combination of non-key attributes (excludes policy_rk and valid_from_dttm)  that are candidates for a unique business key, and this can be different from customer to customer. For instance, policy_no could be a unique business key for one customer, and yet for another customer policy_no + policy_version might represent a unique policy identifier

 


It's nothing out of the ordinary that you need site specific amendments for a generic load template and data model so I wouldn't consider this an issue. 

For your example above: You could eventually use {policy_no, policy_version} as business key in the load template. If at a specific site policy_version is constant or missing then no change is required.

 

 

 

sanalitics
Obsidian | Level 7

Thanks for the clarification Patrick 🙂

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1496 views
  • 0 likes
  • 3 in conversation