Systems and methods for providing value hierarchies, ragged hierarchies and skip-level hierarchies in a business intelligence server

• US 8,326,857 B2
• Filed: 03/01/2011
• Issued: 12/04/2012
• Est. Priority Date: 05/28/2010
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Bolsius, Roger, Venkatasubramanian, Raghuram, Ni, Ling, Donjerkovic, Donko, Chowdhury, Saugata
• Primary Examiner(s): Corrielus, Jean M
• Application Number:
  US13/038,231
• Publication Number:
  US 20110295836A1
• Time in Patent Office: 644 Days
• Field of Search: 707/603, 707/714, 707/765, 707/790, 707/802
• US Class Current: 707/765
• CPC Class Codes: G06F 16/243 Natural language query formulation
• CPC Class Codes: G06Q 10/10 Office automation; Time management

Abstract

A business intelligence (BI) server and repository are described which support a set of hierarchical relationships among the data. The BI server receives user input specifying a set of parent-child or other ancestral relationship among a set of data in a data source. The BI server generates a set of SQL queries and executes the queries to pre-populate a set of tables which specify the parent child relationships among the data in the data source. One such table is a parent-child relationship closure table that defines the inter-member relationships among the data members. Once the tables are populated, the BI server uses the closure tables to answer queries that require knowledge of the ancestral relationships among data.

Claims

1. A method for providing value hierarchies in a business intelligence server environment, said method comprising:

receiving, on a user interface of a business intelligence (BI) server, a specification of ancestral relationships among a set of data stored in a data source;

generating, by the BI server, a set of structured query language (SQL) queries and executing said SQL queries on the data source to pre-populate a set of closure tables, wherein each closure table contains one row for every pair of members that have said ancestral relationship of any distance; and

receiving, at the BI server, a request for the ancestral relationship of a particular data member; and

employing the set of closure tables by said BI server to generate a response to said request, said response indicating the ancestral relationship of said data member,

wherein the BI server represents a level hierarchy by implementing a separate column for each level of the hierarchy,

wherein each level of the hierarchy is represented by a level key;

wherein the BI server determines that a particular level in the level hierarchy is skipped and contains no value at said particular level and represents the skipped level of the hierarchy with a null key, and

wherein if the skipped level is discovered by the BI server during retrieval of information while drilling down through the level hierarchy, the BI server determines which next level following the skipped level contains a value and returns said value in the next level.

2. The method of claim 1, wherein each of the set of closure tables further includes a set of columns comprising one or more of the following:

a member key, an ancestor key, a depth of the member from the root of the hierarchy, a distance of the member from the ancestor;

a key of each ancestor indexed beginning from the current member; and

a key of each ancestor indexed beginning from the root member.

3. The method of claim 1, wherein the request is an SQL query and wherein the BI server replaces all operators of the SQL query that request an ancestor or descendant of the member with sub-queries on the closure table.

4. The method of claim 1, wherein the BI server replaces all operators in the SQL query that request a parent or leaf of the member with sub-queries on a base table.

5. The method of claim 1, wherein the BI server replaces all operators in the SQL query that request a child of the data member with an IN expression.

6. The method of claim 1, wherein the BI server rewrites ancestor value functions or value at depth functions by performing an inner join on a base table with a flattened table to get a desired ancestor key.

7. The method of claim 1, wherein for aggregate by hierarchy request, the BI server joins a fact table with the closure table in a derived table and computes a hierarchical aggregate within the derived table.

8. The method of claim 1, further comprising:

representing a level hierarchy in said BI server by implementing a separate column for each level of the hierarchy, wherein each level of the hierarchy is represented by a level key;

determining that the level hierarchy is ragged and contains an unbalanced structure with levels of varying depth; and

designating the columns for levels of varying depth with null values to balance the level hierarchy, wherein if the level hierarchy is navigated, the BI server exposes a function to determine whether the column is a level to determine if the BI server can drill drills on the value specified by the function.

9. The method of claim 1, further comprising:

populating, by the BI server, a flattened table that includes a flattened representation of the value hierarchy with a key of each ancestor in a separate column, wherein the flattened table includes one or more of the following;

a member key, a parent key, a depth of the member from the root of the hierarchy, a distance of the member from the ancestor;

a key of each ancestor indexed beginning from the current member; and

a key of each ancestor indexed beginning from the root member.

10. A system for providing value hierarchies, said system comprising:

a memory;

at least one processor coupled to the memory;

a business intelligence (BI) server including a graphical user interface that is employed to receive a specification of at least one ancestral relationship among a set of data; and

a data source storing the set of data;

wherein the BI server generates a set of structured query language (SQL) queries and executes said SQL queries on the data source to pre-populate a set of closure tables, wherein each closure table contains one row for every pair of members that have said ancestral relationship of any distance;

wherein,

after pre-populating said closure tables, the BI server receives a request for the ancestral relationship of a particular data member; and

employs the set of closure tables on said BI server to generate a response to said request, said response indicating the ancestral relationship of said data member, wherein the BI server represents a level hierarchy by implementing a separate column for each level of the hierarchy,

wherein each level of the hierarchy is represented by a level key;

wherein the BI server determines that a particular level in the level hierarchy is skipped and contains no value at said particular level and represents the skipped level of the hierarchy with a null key, and

wherein if the skipped level is discovered by the BI server during retrieval of information while drilling down through the level hierarchy, the BI server determines which next level following the skipped level contains a value and returns said value in the next level.

11. The system of claim 10, wherein each of said set of closure tables further includes a set of columns comprising one or more of the following:

a member key, an ancestor key, a depth of the member from the root of the hierarchy, a distance of the member from the ancestor;

a key of each ancestor indexed beginning from the current member; and

a key of each ancestor indexed beginning from the root member.

12. The system of claim 10, wherein the request is an SQL query and wherein the BI server replaces all operators of the SQL query that request an ancestor or descendant of the member with sub-queries on the closure table.

13. The system of claim 10, wherein the request is an SQL query and wherein the BI server replaces all operators in the SQL query that request a parent or leaf of the member with sub-queries on a base table.

14. The system of claim 10, wherein the request is an SQL query and wherein the BI server replaces all operators in the SQL query that request a child of the data member with an IN expression.

15. The system of claim 10, wherein the BI server rewrites ancestor value functions or value at depth functions by performing an inner join on a base table with a flattened table to get a desired ancestor key.

16. The system of claim 10, wherein for aggregate by hierarchy request, the BI server joins a fact table with the closure table in a derived table and computes a hierarchical aggregate within the derived table.

17. The system of claim 10, wherein the BI server represents a level hierarchy by implementing a separate column for each level of the hierarchy, wherein each level of the hierarchy is represented by a level key;

wherein the BI server determines that the level hierarchy is ragged and contains an unbalanced structure with levels of varying depth; and

wherein the BI server designates the columns for levels of varying depth with null values to balance the level hierarchy, wherein if the level hierarchy is navigated, the BI server exposes a function to determine whether the column is a level to determine if the BI server drills on the value specified by the function.

18. A computer readable storage medium storing a set of instructions for providing value hierarchies in a business intelligence server, said instructions, when executed by one or more processors, causing the one or more processors to perform the steps of:

receiving, on a user interface of a business intelligence (BI) server, a specification of ancestral relationships among a set of data stored in a data source;

generating, by the BI server, a set of structured query language (SQL) queries and executing said SQL queries on the data source to pre-populate a set of closure tables, wherein each closure table contains one row for every pair of members that have said ancestral relationship of any distance;

receiving, at the BI server, a request for the ancestral relationship of a particular data member; and

employing the set of closure tables by said BI server to generate a response to said request, said response indicating the ancestral relationship of said data member, wherein the BI server represents a level hierarchy by implementing a separate column for each level of the hierarchy,

wherein each level of the hierarchy is represented by a level key;

wherein the BI server determines that a particular level in the level hierarchy is skipped and contains no value at said particular level and represents the skipped level of the hierarchy with a null key, and

wherein if the skipped level is discovered by the BI server during retrieval of information while drilling down through the level hierarchy, the BI server determines which next level following the skipped level contains a value and returns said value in the next level.