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
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.
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.
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
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.
Thanks for the clarification Patrick 🙂
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.
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.