REPORT DATABASE DEPENDENCY TRACING THROUGH BUSINESS INTELLIGENCE METADATA

• US 20090193050A1
• Filed: 08/18/2008
• Published: 07/30/2009
• Est. Priority Date: 01/25/2008
• Status: Active Grant

• Current Assignee: Avaya Incorporated
• Original Assignee: Avaya Incorporated
• Inventors: Olson, Jeffrey J.
• Granted Patent: US 8,856,182 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

In one embodiment, the present invention provides a data stream reference module 250 that can enable a user to perform forward tracing, backward tracing, and tracing indirect dependencies in a data model and/or metadata model. The tracing is not necessarily along a common hierarchical level but can be from one level to another.

Claims

1. A method:

(a) providing a database defined by a data model, a metadata model comprising descriptive information, the descriptive information defining transformations and rules to convert raw data in the database to selected output, and a query engine to construct, based on user input and the descriptive information, appropriate queries and/or commands to the database, wherein the data and metadata models collectively define a hierarchical dependency structure representable as a directed acyclical graph;

(b) to assist the user in interacting with the query model, displaying a plurality of images to the user, the plurality of images being linked together and wherein at least one of the following substeps is performed;

(B1) forward tracing from a selected first item in a first level of the graph to a second item in a second level of the graph, the second item depending upon the first item, by selecting a second link in a first image, the second link being associated with the second item and, in response, providing the user with a second image containing, in turn, the definition and/or dependency information respecting the second item;

(B2) providing a plurality of the following operating modes;

(B2i) in a usage mode, providing a first display to the user, the first display comprising, for the selected first item, an indicator of a degree to which the selected first item is used in the preparation of and/or incorporated in a report;

(B2ii) in a users mode, providing a second display to the user, the second display comprising, for the selected first item, a listing of items using the selected first item;

(B2iii) in a location mode, providing a third display to the user, the third display including, for the selected first item and or any other items shown, the complete location of said item(s) within the hierarchical tree structure in the layers of the reporting system;

(B2iv) in an expression mode, displaying, to the user, an algorithm used to determine the selected first item, the algorithm referencing links to other items in the graph;

(B2v) in a description mode, displaying, to the user, a textual definition of the selected first item, the textual definition being importable and exportable into a markup language specification of a reporting model; and

(B2vi) in other modes, providing the user with a plurality of;

spreadsheets listing pairings of database columns with dependent items, single-page displays of chained items involved in a definition of a single report data item, and tagging of items in a reporting model;

(B3) back tracing from the selected third item in a second level of the dependency graph to a fourth item in a first level of the graph, the selected third item being dependent upon the fourth item, by selecting a third link in a second image, the third link being associated with the fourth item and, in response, providing the user with a third image containing, in turn, the definition and or dependency information respecting the fourth item;

(B4) providing the user with a series of displays, the displays permitting the user to at least one of (i) expand and contract items in a chain of dependency relationships in the graph to reach a desired item;

(ii) move directly from a first item in a first dependency chain to a second item in a second, different dependency chain; and

(iii) move directly from a first item in a first dependency chain to a second item in the first dependency chain, the second item being separated, in the first dependency chain, from the first item by a plurality of intervening items;

(B5) selecting a fifth link leading to the relations a fifth item in a fifth image to obtain a sixth image comprising a plurality of items related through foreign key(s) to the fifth item and comprising a seventh link to a seventh image, the seventh image containing, in turn, the definition and or dependency information representing an item related to the fifth item through a foreign key relation;

(B6) selecting an eighth link leading to the filters for an eighth item in an eighth image to obtain a ninth image comprising a plurality of filter identifiers associated with the eighth item, which may be expanded to reveal a ninth item in the definition of that filter and comprising a ninth link to a tenth image, the tenth image containing, in turn, the definition and or dependency information for the ninth item; and

(B7) providing an eleventh image listing a plurality of output reports, each output report identifier corresponding to a respective image comprising a set of items in the corresponding report, each identified item in turn having a respective link to a respective image, the respective image comprising at least one of a definition of the corresponding identified item and a set of database columns on which the corresponding identified item depends.

2. The method of claim 1, wherein substep (B1) is performed.

3. The method of claim 2, wherein the first level is not adjacent to the second level

4. The method of claim 1, wherein substep (B2) is performed.

5. The method of claim 4, wherein the first level is not adjacent to the second level.

6. The method of claim 5, wherein the plurality of operating modes includes (B2i).

7. The method of claim 5, wherein the plurality of operating modes includes (B2ii).

8. The method of claim 5, wherein the plurality of operating modes includes (B2iii).

9. The method of claim 5, wherein the plurality of operating modes includes (B2iv).

10. The method of claim 5, wherein the plurality of operating modes includes (B2v).

11. The method of claim 5, wherein the plurality of operating modes includes (B2vi).

12. The method of claim 1, wherein substep (B3) is performed.

13. The method of claim 1, wherein substep (B4) is performed.

14. The method of claim 1, wherein substep (B5) is performed.

15. The method of claim 1, wherein in at least one image a displayed item identifier is identified as being one of reported (indicating that the displayed item is referenced directly by at least one output report, exposed (indicating that no current output report references the displayed item but that the displayed item is available in the external user interface so that output reports can include the displayed item if selected by the user), required (indicating that the displayed item is a lower level item supporting a reported item reported by an output report), expected (indicating that the displayed item is a lower level item supporting an exposed item), and optional (indicating that the displayed item does not contribute in any way to the external interface and can be removed safely without any detrimental effect on the data analysis).

16. The method of claim 1, wherein a user can select in at least one image a plurality of the following options:

location (which shows a hierarchical tree location of the corresponding displayed item), description (which provides a definition of the corresponding displayed item), expression (which provides mathematical expression used to determine the corresponding displayed item), usage (which displays the degree to which the corresponding displayed item is used by and/or exposed to reports), and users (which displays a set of items using the corresponding displayed items).

17. A computer readable medium comprising processor executable instructions operable to perform the steps of claim 1.

19. The system of claim 17, wherein operation (B1) is performed.

20. The system of claim 19, wherein the first level is not adjacent to the second level

21. The system of claim 17, wherein operation (B2) is performed.

22. The system of claim 21 wherein the first level is not adjacent to the second level.

23. The system of claim 22, wherein the plurality of operating modes includes (B2i).

24. The system of claim 22, wherein the plurality of operating modes includes (B2ii).

25. The system of claim 22, wherein the plurality of operating modes includes (B2iii).

26. The system of claim 22, wherein the plurality of operating modes includes (B2iv).

27. The system of claim 22, wherein the plurality of operating modes includes (B2v).

28. The system of claim 22, wherein the plurality of operating modes includes (B2vi).

18. A system, comprising:

(a) a database, the database being defined by a data model;

(b) a database management system comprising a metadata model comprising descriptive information, the descriptive information defining transformations and rules to convert raw data in the database to selected output, a query engine to construct, based on user input and the descriptive information, appropriate queries to the database, and a data stream reference module operable to assist the user in interacting with the query model by displaying a plurality of images to the user, the plurality of images being linked together, wherein the data and metadata models collectively define a hierarchical dependency structure representable as a directed acyclical graph and wherein the data stream reference module is operable to perform at least one of the following operations;

(B1) forward trace from a selected first item in a first level of the graph to a second item in a second level of the graph, the second item depending upon the first item, by selecting a second link in a first image, the second link being associated with the second item and, in response, providing the user with a second image containing, in turn, the definition and/or dependency information respecting the second item;

(B2) provide a plurality of the following operating modes;

(B2i) in a usage mode, provide a first display to the user, the first display comprising, for the selected first item, an indicator of a degree to which the selected first item is used in the preparation of and/or incorporated in a report;

(B2ii) in a users mode, provide a second display to the user, the second display comprising, for the selected first item, a listing of items using the selected first item;

(B2iii) in a location mode, provide the user with at least one display, the at least one display comprising, for the selected first item and relative to the graph, a first listing of a first set of items on which the selected first item depends and a second listing of a second set of items which depend on the selected first item;

(B2iv) in an expression mode, display, to the user, an algorithm used to determine the selected first item, the algorithm referencing links to other items in the graph;

(B2v) in a description mode, display, to the user, a textual definition of the selected first item, the textual definition being importable and exportable into a markup language specification of a reporting model; and

(B2vi) in other modes, provide the user with a plurality of;

spreadsheets listing pairings of database columns with dependent items, single-page displays of chained items involved in a definition of a single report data item, and tagging of items in a reporting model;

(B3) back trace from the selected third item in a second level of the dependency graph to a fourth item in a first level of the graph, the selected third item being dependent upon the fourth item, by selecting a third link in a second image, the third link being associated with the fourth item and, in response, providing the user with a third image containing, in turn, the definition and or dependency information respecting the fourth item;

(B4) provide the user with a series of displays, the displays permitting the user to at least one of (i) expand and contract items in a chain of dependency relationships in the graph to reach a desired item;

(ii) move directly from a first item in a first dependency chain to a second item in a second, different dependency chain; and

(iii) move directly from a first item in a first dependency chain to a second item in the first dependency chain, the second item being separated, in the first dependency chain, from the first item by a plurality of intervening items;

(B5) select a fifth link associated with a fifth item in a fifth image to obtain a sixth image comprising at least one of a plurality of fact and/or dimension table column identifiers and a database query and comprising a seventh link to a seventh image, the seventh image comprising a plurality of keys associated with a plurality of dimension and/or fact tables, wherein the sixth image includes a sixth item dependent upon both fact and dimension tables;

(B6) select an eighth link associated with an eighth item in an eighth image to obtain a ninth image comprising at least one of a plurality of fact and/or dimension table column identifiers and a database query and comprising a tenth link to a tenth image, the tenth image comprising a plurality of locale filter identifiers associated with at least one of a plurality of dimension and/or fact tables; and

(B7) provide an eleventh image listing a plurality of output reports, each output report identifier corresponding to a respective image comprising a set of items in the corresponding report, each identified item in turn having a respective link to a respective image, the respective image comprising at least one of a definition of the corresponding identified item and a set of database columns on which the corresponding identified item depends.

29. The system of claim 18, wherein operation (B3) is performed.

30. The system of claim 18, wherein operation (B4) is performed.

31. The system of claim 18, wherein operation (B5) is performed.

32. The system of claim 18, wherein in at least one image a displayed item identifier is identified as being one of reported (indicating that the displayed item is referenced directly by at least one output report, exposed (indicating that no current output report references the displayed item but that the displayed item is available in the external user interface so that output reports can include the displayed item if selected by the user), required (indicating that the displayed item is a lower level item supporting a reported item reported by an output report), expected (indicating that the displayed item is a lower level item supporting an exposed item), and optional (indicating that the displayed item does not contribute in any way to the external interface and can be removed safely without any detrimental effect on the data analysis).

33. The system of claim 18, wherein a user can select in at least one image a plurality of the following options:

location (which shows a hierarchical tree location of the corresponding displayed item), description (which provides a definition of the corresponding displayed item), expression (which provides mathematical expression used to determine the corresponding displayed item), usage (which displays the degree to which the corresponding displayed item is used by and/or exposed to reports), and users (which displays a set of items using the corresponding displayed items).