Data extensibility using external database tables

• US 20070083572A1
• Filed: 10/06/2005
• Published: 04/12/2007
• Est. Priority Date: 10/06/2005
• Status: Active Grant

• Current Assignee: Avaya Incorporated
• Original Assignee: Avaya Technology Corporation Miami Lakes FLA US
• Inventors: Jennings, Terry, Olson, Jeffrey, Friedl, Stephan, West, Barbara, Bland, M.
• Granted Patent: US 7,752,230 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

In one embodiment, a data processing system includes:

• • (a) a first database 304 maintained by a first party;
  • (b) a second database 324, 328 maintained by a second party different from the first party;
  • (c) a work queue 316 operable to specify data to be added to the second database;
  • (d) an extract, transform and load module 308 operable to write data from the first database to the work queue; and
  • (e) a data import module operable 320 to import data from the work queue to the second database. In the system, the first party is not privileged to write the data directly to the second database.

Claims

1. A data processing system, comprising:

a first database maintained by a first party;

a second database maintained by a second party different from the first party;

a work queue operable to specify data to be added to the second database;

an extract, transform and load module operable to write data from the first database to the work queue; and

a data import module operable to import data from the work queue to the second database, wherein the first party is not privileged to write the data directly to the second database.

2. The system of claim 1, wherein the first party is a customer and the second party is a vendor of the second database, wherein the first party is not privileged to modify directly the semantics of the second database, wherein the first party is not privileged to delete pre-existing data from and update pre-existing data in the second database, and wherein the second party is not privileged to modify directly the semantics of the first database.

3. The system of claim 1, further comprising:

a semantics module operable to effect updates to type 2 dimension semantics in the second database to accommodate the imported data.

4. The system of claim 1, further comprising:

a specification to extend a second database maintained by a second party, the specification being provided by the first party; and

a modifier operable to modify a data model describing the second database to reflect the specification and to create the work queue corresponding to the modification.

5. The system of claim 1, wherein the first party is privileged to view at least selected parts of the second database.

6. The system of claim 1, wherein the modification is at least one of the following:

(i) addition of custom facts to an existing fact table in the second database;

(ii) addition of custom facts to a new table that parallels an existing fact table in the second database;

(iii) addition of custom attributes to existing dimension table in the second database;

(iv) addition of custom attributes to a new table that parallels an existing dimension table in the second database; and

(v) addition of a custom table.

7. The system of claim 1, wherein the work queue comprises a natural key to the first database, the natural key being external to the second database, and wherein the data import module converts the natural key to a key internal to the second database and external to the first database.

8. A method, comprising:

(a) receiving from a first party a set of specifications defining an extension to a second database maintained by a second party;

(b) modifying the second database as set forth in the set of specifications, wherein the modification comprises at least one of a custom table, and a custom column in a table;

(c) creating a work queue corresponding to the modification;

(d) the work queue receiving first data from a first database;

(e) normalizing the first data to form second data, wherein the first data is semantically compatible with the first database and semantically incompatible with the second database and wherein the second data is semantically incompatible with the first database and semantically compatible with the second database; and

(f) writing the second data to the at least one of a custom table, and a custom column in a table.

9. The method of claim 8, wherein a work queue is created for each of a plurality of sets of specifications.

10. The method of claim 8, wherein the first party is a customer and the second party is a vendor of the second database, wherein the first party is not privileged to modify directly the semantics of the second database, wherein the first party is not privileged to write directly to the second database, and wherein the second party is not privileged to modify directly the semantics of the first database.

11. The method of claim 8, further comprising:

(g) permitting the first party to view only selected parts of the second database.

12. The method of claim 8, wherein the modification is at least one of the following:

(i) addition of custom facts to an existing fact table in the second database;

(ii) addition of custom facts to a new table that parallels an existing fact table in the second database;

(iii) addition of custom attributes to existing dimension table in the second database;

(iv) addition of custom attributes to a new table that parallels an existing dimension table in the second database; and

(v) addition of a custom table.

13. The method of claim 8, further comprising:

(g) after step (b), deleting the processed set of specifications.

14. The method of claim 8, wherein, in step (e), the work queue comprises a natural key to the first database, the natural key being external to the second database, and wherein the data import module converts the natural key to a key internal to the second database and external to the first database.

15. The method of claim 8, wherein the writing step comprises:

(f1) comparing a first set of attributes in the second database with a corresponding preexisting second set of attributes in the second database; and

(f2) when the first and second set of attributes are different, determining whether a type 2 attribute has changed.

16. The method of claim 15, wherein the writing step further comprises:

(f3) when a type 2 attribute has not changed, executing a type 1 update to the second database; and

(f4) when a type 2 attribute has changed, generating a new surrogate key and inserting into the second database a new record containing the new surrogate key and the first set of attributes.

17. The method of claim 12, wherein the modification is (i).

18. The method of claim 12, wherein the modification is (ii).

19. The method of claim 12, wherein the modification is (iii).

20. The method of claim 12, wherein the modification is (iv).

21. The method of claim 12, wherein the modification is (v).

22. A computer readable medium comprising executable instructions to perform the steps of claim 8.