Using a database to translate a natural key to a surrogate key

Patent metadata

Field	Value
Publication number	US-9747359-B2
Application number	US-201113276803-A
Country	US
Kind code	B2
Filing date	Oct 19, 2011
Priority date	Oct 19, 2011
Publication date	Aug 29, 2017
Grant date	Aug 29, 2017

Abstract

A technique includes, in response to an access to a database involving access to a table and specifying a natural key, using the database to translate the natural key to a surrogate key based at least in part on a mapping.

First claim

What is claimed is:

1. A method comprising:
associating a natural key-to-surrogate key mapping with a table of a database by at least one physical processor; and
in response to an access to the database involving access to the table and specifying a natural key, using the database to translate the natural key to a surrogate key based at least in part on the mapping, by the at least one physical processor,
wherein the access comprises a request specifying the natural key and being submitted to the database and using the database to translate the natural key to the surrogate key comprises using a compiler of the database to transform the request to replace the natural key with the surrogate key based at least in part on the mapping,
wherein the table comprises a fact table, the request comprises a request for a query to the fact table, and wherein using the database to translate the natural key to the surrogate key comprises:
using the compiler of the database to transform the query to join the fact table with a dimension table to evaluate at least one predicate against a natural key column that resides in the dimension table, based at least in part on a first mapping rule associated with the fact table and a second mapping rule associated with the dimension table.

2. The method of claim 1 , wherein the mapping comprises a natural key-to-surrogate key mapping indicated by a declaration of the dimension table.

3. The method of claim 2 , wherein the natural key-to-surrogate key mapping indicated by the declaration of the dimension table comprises a mapping identifying a natural key column of the dimension table and identifying a row of the dimension table.

4. The method of claim 1 , further comprising:
performing a load to the fact table that specifies another natural key value by
using the compiler of the database to identify a first surrogate key of the fact table and a second surrogate key of the dimension table based at least in part on a third mapping rule associated with the fact table and the other natural key; and
using the compiler to transform the load request based at least in part on the other natural key value, the third mapping rule associated with the fact table and a fourth mapping rule associated with the dimension table.

5. A system comprising:
a database compiler comprising a processor and a memory that includes instructions that when executed, cause the compiler to:
in response to an access to a database including a fact table, use a natural key-to-surrogate key mapping associated with the table to translate a natural key to a surrogate key,
wherein the natural key is associated with a natural key column, the fact table is associated with a dimension table, the access comprises a load request to the fact table and specifies a natural key value, and the database compiler is further adapted to:
identify a first surrogate key of the fact table and a second surrogate key of the dimension table based at least in part on a first mapping rule associated with the fact table and the natural key; and
transform the load request based at least in part on the natural key value, the first mapping rule associated with the fact table and a second mapping rule associated with the dimension table.

6. The system of claim 5 , wherein the database compiler is further adapted to translate the natural key to the surrogate key based at least in part on a natural key-to-surrogate key mapping indicated by a declaration of the dimension table.

7. The system of claim 6 , wherein the natural key-to-surrogate key mapping indicated by the declaration of the dimension table comprises a mapping identifying a natural key column of the dimension table and identifying a row of the dimension table.

8. The system of claim 5 , wherein the database compiler receives a query to the fact table including a second natural key, and the database compiler is further adapted to:
transform the query to join the fact table with the dimension table to evaluate at least one predicate against a natural key column that resides in the dimension table, based at least in part on a third mapping rule associated with the fact table and a fourth mapping rule associated with the dimension table.

9. The system of claim 5 , wherein the natural key is one of a plurality of natural keys associated with a plurality of dimensions, and the database compiler is further adapted to map a plurality of natural key values to a plurality of respective surrogate key values.

10. An article comprising a non-transitory computer readable storage medium storing instructions that when executed by a processor-based system cause the processor-based system to:
associate a natural key-to-surrogate key mapping with a table of a database;
in response to an access to the database involving access to the table and specifying a natural key, in a compiler of the database, translate the natural key to a surrogate key based at least in part on the mapping,
wherein the table comprises a fact table, the access comprises a query to the fact table, and
transform the query to join the fact table with a dimension table to evaluate at least one predicate against a natural key column that resides in the dimension table, based at least in part on a first mapping rule associated with the fact table and a second mapping rule associated with the dimension table.

11. The article of claim 10 , wherein the database comprises a dimension table and the mapping comprises a natural key-to-surrogate key mapping indicated by a declaration of the dimension table.

12. The article of claim 10 , wherein the natural key-to-surrogate key mapping indicated by the declaration of the dimension table comprises a mapping identifying a natural key column of the dimension table and identifying a row of the dimension table.

13. The article of claim 10 , wherein the storage medium stores instructions that when executed by the processor-based system cause the processor-based system to:
receive a load request to the fact table including a second natural key that is associated with a natural key column;
identify a first surrogate key of the fact table and a second surrogate key of the dimension table based at least in part on a third mapping rule associated with the fact table and the second natural key; and
transform the load request based at least in part on the second natural key value, the mapping rule associated with the fact table and a fourth mapping rule associated with the dimension table.

Assignees

• Jain Rohit N
• Hewlett Packard Entpr Dev Lp

Inventors

• Jain Rohit N

Classifications

• G06F16/284 Primary

  Relational databases · CPC title

• G06F17/30595 Primary

  Physics · mapped topic