topic Re: SAS DDS for Insurance - Logic behind creating RK? in SAS Data Management

SAS DDS for Insurance - Logic behind creating RK?

sanalitics — Sun, 11 Aug 2019 07:26:36 GMT

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

Re: SAS DDS for Insurance - Logic behind creating RK?

gostan — Wed, 14 Aug 2019 13:28:52 GMT

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.

Re: SAS DDS for Insurance - Logic behind creating RK?

sanalitics — Wed, 14 Aug 2019 13:53:41 GMT

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

Re: SAS DDS for Insurance - Logic behind creating RK?

Patrick — Thu, 15 Aug 2019 00:59:09 GMT

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.

Re: SAS DDS for Insurance - Logic behind creating RK?

sanalitics — Thu, 15 Aug 2019 07:30:09 GMT

Thanks for the clarification Patrick 🙂