HIERARCHICAL DIMENSION ANALYSIS IN MULTI-DIMENSIONAL PIVOT GRIDS

• US 20160335303A1
• Filed: 05/12/2015
• Published: 11/17/2016
• Est. Priority Date: 07/10/2014
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Madhalam, Kalpana, Mahashabde, Rahul, Pattabhiraman, Balaji, Narayanan, Deepankar, Gupta, Virad
• Granted Patent: US 10,585,892 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A system and method facilitating transforming or updating displayed data using data hierarchy information. An example method includes storing hierarchy information characterizing a dataset via one or more tree tables; selectively accessing a database to update data characterized by metadata of the one or more tree tables; employing one or more pivot grids to replicate one or more hierarchical properties of the one or more tree tables; and displaying information based on the one or more pivot grids. The displaying step may further include using the pivot grid(s) to generate and display analytic(s), wherein data for the pivot grid(s) and analytics(s) is obtained from flattened tree tables. Dynamically executed Structured Query Language (SQL) construct(s) may retrieve un-cached fresh multi-dimensional data used for the pivot grid(s) or analytic(s) in a manner consistent with hierarchy information retrieved (via SQL) from the tree tables as tree metadata.

Claims

1. A method for facilitating transforming or updating displayed data in an enterprise computing environment, the enterprise computing environment including one or more client devices in communication with a server system, wherein the server system executes webpage software, wherein the webpage software provides webpage information accessible to one or more client devices, the server system executing steps of the method, the method comprising:

storing hierarchy information characterizing a dataset via one or more tree tables;

selectively accessing a database to update data characterized by metadata of the one or more tree tables;

using one or more pivot grids to replicate one or more hierarchical properties of the one or more tree tables; and

providing information based on the one or more pivot grids, the information adapted to be displayed via one or more client devices.

2. The method of claim 1, wherein displaying further includes using the one or more pivot grids to generate and display an analytic.

3. The method of claim 1, further including:

employing one or more Structured Query Language (SQL) constructs to represent multi-dimensional data included in the one or more pivot grids, using one or more SQL queries.

4. The method of claim 3, further including:

dynamically executing the one or more SQL queries against the database, whereby when data in the database changes, corresponding data in the one or more tree tables is automatically updated.

5. The method of claim 3, wherein the one or more SQL constructs includes an SQL rollup construct, and further including employing the SQL rollup construct to aggregate data of the dataset from the database, and wherein the one or more SQL constructs further include an SQL grouping construct.

6. The method of claim 1, further including:

representing a set of data via a first hierarchical tree structure;

using the first hierarchical tree structure to generate a first set of one or more tables of data characterizing data of the set of data;

receiving user input indicating one or more operations to be performed on the first set of one or more tables;

performing the one or more operations on the first set of one or more tables, resulting in an updated second set of one or more tables in response thereto; and

using the updated second set of one or more tables to generate a second hierarchical tree structure reflecting a change in an arrangement of data responsive to the updating of the first set of one or more tables.

7. The method of claim 6, wherein employing further includes using metadata to store hierarchical information characterizing the first hierarchical tree structure, and wherein the first and second sets of one or more tables include one or more tree tables.

8. The method of claim 6, wherein performing includes using one or more pivot grids to perform the one or more operations.

9. The method of claim 8, wherein the one or more operations include a join operation, whereby the join operation results in joining two or more tables of the first set of one or more tables of data.

10. The method of claim 6, further including:

mapping data of the updated set of one or more tables to a pivot grid reflecting a hierarchical arrangement of data of the updated set of one or more tables.

11. The method of claim 10, wherein the pivot grid includes one or more hierarchical dimensions.

12. The method of claim 11, further including:

providing a first user option to manipulate data of the pivot grid, and using an Structured Query Language (SQL) construct to facilitate manipulating data of the pivot grid in response to user selection of the first user option.

13. The method of claim 12, further including:

displaying an analytic illustrating data of the pivot grid, and providing a user option to modify the analytic, wherein modifications to the analytic trigger automatic updating of the pivot grid and the first hierarchical tree structure to reflect the modifications.

14. The method of claim 11, further including:

mapping hierarchical data dimensions of the second hierarchical tree structure to one or more dimensions of the pivot grid.

15. The method of claim 14, further including:

implementing the mapping without caching data identified by metadata nodes of the first or second hierarchical tree structures.

16. The method of claim 10, wherein the first set of one or more tables include one or more flattened tables.

17. The method of claim 16, wherein the one or more operations to be performed on the one or more tables include a rollup operation, and further including:

using the rollup operation to obtain one or more subtotals for one or more lowest levels of a dimension of the pivot grid, and wherein the one or more operations include a table join operation.

18. The method of claim 1, further including providing a user option to augment a hierarchy characterizing the pivot grid by:

attaching a hierarchical tree structure to a dimension of the pivot grid; and

mapping the attached hierarchical tree structure to the dimension of the pivot grid.

19. An apparatus comprising:

a digital processor coupled to a display and to a processor-readable storage device, wherein the processor-readable storage device includes one or more instructions executable by the digital processor to perform the following acts;

storing hierarchy information characterizing a dataset via one or more tree tables;

selectively accessing a database to update data characterized by metadata of the one or more tree tables;

using one or more pivot grids to replicate one or more hierarchical properties of the one or more tree tables; and

displaying information based on the one or more pivot grids.

20. A processor-readable storage device including instructions executable by a digital processor, the processor-readable storage device including one or more instructions for:

storing hierarchy information characterizing a dataset via one or more tree tables;

selectively accessing a database to update data characterized by metadata of the one or more tree tables;

using one or more pivot grids to replicate one or more hierarchical properties of the one or more tree tables; and

displaying information based on the one or more pivot grids.