Semantic Modeler: Additional keys physical table

Question

Hi community,

could someone explain to me what "Additional Keys" are in the definition of a physical table in OAC Semantic Model? (General tab)

According to the documentation:

"Displays a list of keys that, in addition to the join keys, defines identifier columns for the table"

But what does this mean?

These keys are not specified manually by the developer, according to which criteria are defined?

These keys are not primary keys. So what these are?

Thanks in advance for your help.

Ambient · Answer

@AM HRE

In Data Warehouses/Data Marts it is very common to define the Primary Key (PK) with one column that contains a unique, system generated id for each row. This also known as a "surrogate key".

This approach simplifies the design of tables because Foreign Keys (FK or join keys) between tables can be defined with just one column (Think: joining the primary key of a Order_Header table to the primary key of an Order_Detail table)

The main problem with this strategy is that there is no mechanism for enforcing the uniqueness of the data in the table from a business perspective (Think: Employee table where you would need to include First_Name, Middle_Name, Last_Name & Address in a unique key to ensure that each record identifies a single, unique person). This also known as an Alternate Key (AK).

Without this AK in place, you have the possibility that any ETL/Data Entry processes could introduce duplicate records into the table, which in turn has the potential to compromise the integrity of analysis that your users perform when joining that table to others (i.e,. doubling the counts/amounts)

Having an AK in place ensures that regardless of what ETL/Data Entry processes are updating your table, any attempt insert a duplicate record or update a record so it results in a duplicate record, will be automaitically rejected by the database itself.

Take a look at the this article for a nice little overview:

https://www.simplilearn.com/alternate-key-in-dbms-article

Ram-Oracle · Answer

Hi @AM HRE , Suppose you have a table that does not have primary key column to uniquely identify each row ,then we use two columns to bring uniqueness to each row.

we can define these two columns as additional keys in physical table of oac semantic modeller

And also if you want to  join two tables other than primary keys then you can define additional keys and use it in joining conditions