System and method for accessing dimensional databases

• US 9,183,272 B1
• Filed: 11/06/2013
• Issued: 11/10/2015
• Est. Priority Date: 11/06/2013
• Status: Active Grant

• Current Assignee: Quest Software, Inc.
• Original Assignee: Dell Software, Inc. (Dell Technologies Inc.)
• Inventors: Jonsson, Jan Henrik, Peabody, Debbie M., Bazhenov, Aleksey Mikhailovich
• Primary Examiner(s): Vo, Truong
• Application Number:
  US14/073,822
• Time in Patent Office: 734 Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/242 Query formulation
• CPC Class Codes: G06F 16/2445 Data retrieval commands; View definitions
• CPC Class Codes: G06F 16/245 Query processing
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 16/90339 by using parallel associative memories or content-addressable memories
• CPC Class Codes: G06F 16/951 Indexing; Web crawling techniques

Abstract

In one embodiment, a method includes defining a slice of a dimensional database. The slice includes at least one dimension and at least one measurement and is expressable as a table. The method further includes building, in a structured query language (SQL) dialect, a single query that queries the slice and one or more non-dimensional databases. The SQL dialect represents the at least one dimension, the at least one measurement, and data attributes of the one or more non-dimensional databases. In addition, the method includes parsing the single query. The parsing includes generating a dimensional query for the dimensional database and a non-dimensional query for each of the one or more non-dimensional databases. Furthermore, the method includes executing the dimensional query on the dimensional database. Moreover, the method includes, for each non-dimensional database of the one or more non-dimensional databases, executing the non-dimensional query on the non-dimensional database.

Claims

1. A method comprising:

on a computer system comprising at least one server computer, defining, responsive to user input, a slice of a dimensional database, the slice comprising at least one dimension and at least one measurement of the dimensional database, wherein the slice is expressable as a table;

the computer system building, in a structured query language (SQL) dialect, a single query that queries the slice and one or more non-dimensional databases;

wherein the SQL dialect represents, using SQL constructs, the at least one dimension, the at least one measurement, and data attributes of the one or more non-dimensional databases;

the computer system parsing the single query, the parsing comprising generating a dimensional query for the dimensional database and a non-dimensional query for each of the one or more non-dimensional databases;

the computer system executing the dimensional query on the dimensional database; and

for each non-dimensional database of the one or more non-dimensional databases, the computer system executing the non-dimensional query on the non-dimensional database.

2. The method of claim 1, wherein the parsing comprises:

translating the single query from the SQL dialect to SQL; and

parsing the translated single query to generate the dimensional query and the non-dimensional query.

3. The method of claim 1, wherein the defining comprises:

receiving of a selection of a dimensional data source from a user;

providing a listing of measurements and dimensions associated with the dimensional data source;

receiving a selection of the at least one dimension and the at least one measurement; and

building a SQL-dialect query representative of the slice.

4. The method of claim 1, wherein the defining comprises adding at least one virtual column.

5. The method of claim 1, wherein the defining comprises showing a live view of the slice as the user manipulates the slice.

6. The method of claim 1, wherein the slice comprises a SQL-dialect query, the single query including the SQL-dialect query in a subselect statement.

7. The method of claim 1, wherein the single query comprises a table-identifier prefix for the defined slice and a table-identifier prefix for each of the one or more non-dimensional databases.

8. The method of claim 1, comprising combining query results and returning resultant data.

9. The method of claim 1, comprising presenting the resultant data to a user.

10. The method of claim 1, wherein the building comprises:

receiving a selection of a plurality of data sources from a user, the plurality of data sources comprising the slice and the one or more non-dimensional databases;

providing to the user data attributes of each of the plurality of data sources;

permitting the user to graphically build the single query;

receiving selected data attributes from the user responsive to the permitting; and

constructing the single query in the SQL dialect.

11. An information handling system comprising:

at least one processing unit, wherein the at least one processing unit is operable to implement a method comprising;

defining, responsive to user input, a slice of a dimensional database, the slice comprising at least one dimension and at least one measurement of the dimensional database, wherein the slice is expressable as a table;

building, in a structured query language (SQL) dialect, a single query that queries the slice and one or more non-dimensional databases;

wherein the SQL dialect represents, using SQL constructs, the at least one dimension, the at least one measurement, and data attributes of the one or more non-dimensional databases;

parsing the single query, the parsing comprising generating a dimensional query for the dimensional database and a non-dimensional query for each of the one or more non-dimensional databases;

executing the dimensional query on the dimensional database; and

for each non-dimensional database of the one or more non-dimensional databases, executing the non-dimensional query on the non-dimensional database.

12. The information handling system of claim 11, wherein the parsing comprises:

translating the single query from the SQL dialect to SQL; and

parsing the translated single query to generate the dimensional query and the non-dimensional query.

13. The information handling system of claim 11, wherein the defining comprises:

receiving of a selection of a dimensional data source from a user;

providing a listing of measurements and dimensions associated with the dimensional data source;

receiving a selection of the at least one dimension and the at least one measurement; and

building a SQL-dialect query representative of the slice.

14. The information handling system of claim 11, wherein the defining comprises adding at least one virtual column.

15. The information handling system of claim 11, wherein the defining comprises showing a live view of the slice as the user manipulates the slice.

16. The information handling system of claim 11, wherein the slice comprises a SQL-dialect query, the single query including the SQL-dialect query in a subselect statement.

17. The information handling system of claim 11, wherein the single query comprises a table-identifier prefix for the defined slice and a table-identifier prefix for each of the one or more non-dimensional databases.

18. The information handling system of claim 11, the method comprising:

combining query results and returning resultant data; and

presenting the resultant data to a user.

19. The information handling system of claim 11, wherein the building comprises:

receiving a selection of a plurality of data sources from a user, the plurality of data sources comprising the slice and the one or more non-dimensional databases;

providing to the user data attributes of each of the plurality of data sources;

permitting the user to graphically build the single query;

receiving selected data attributes from the user responsive to the permitting; and

constructing the single query in the SQL dialect.

20. A computer-program product comprising a non-transitory computer-usable medium having computer-readable program code embodied therein, the computer-readable program code adapted to be executed to implement a method comprising:

defining, responsive to user input, a slice of a dimensional database, the slice comprising at least one dimension and at least one measurement of the dimensional database, wherein the slice is expressable as a table;

building, in a structured query language (SQL) dialect, a single query that queries the slice and one or more non-dimensional databases;

wherein the SQL dialect represents, using SQL constructs, the at least one dimension, the at least one measurement, and data attributes of the one or more non-dimensional databases;

parsing the single query, the parsing comprising generating a dimensional query for the dimensional database and a non-dimensional query for each of the one or more non-dimensional databases;

executing the dimensional query on the dimensional database; and

for each non-dimensional database of the one or more non-dimensional databases, executing the non-dimensional query on the non-dimensional database.