Distributed queries through dynamic views
A data query and retrieval system receives a query request and identifies a plurality of data warehouses, or stores, where data needed to satisfy the query is stored. Data items, or fields, called for by the query request are grouped into sets based on fields stored at the same data warehouse. Individual data connections are generated for each data warehouse accessed, and a database view defined for the fields available at each respective data warehouse. The individual views are invoked in a single query statement by a query processor for accessing the respective data warehouses via the corresponding database view. In this manner, a query operator or user need not define multiple query expressions or perform separate queries for accessing data fields or dimensions residing at different data warehouses.
1. In a query engine having multiple connections to each of a plurality of data warehouses, each data warehouse corresponding to a vendor imposed storage arrangement, a method of satisfying a query request invoking each data warehouse, comprising:
receiving a query request, the query request having a plurality of fields and one or more conditions, the conditions defining selective retrieval of the data in the fields;
identifying, for each field of the plurality of fields, a source that stores the field, each field defined by one or more dimensions in the respective source;
defining, for each of the identified sources, a view for accessing the fields stored in a respective source;
forming a hierarchical structure based on dependencies in the query request;
identifying, from the hierarchical structure, one or more sub-trees corresponding to the same source;
forming a view corresponding to each of the sub-trees sharing the same source, further comprising, for each view, identifying a set of tables stored at the source that contain the fields;
iteratively accumulating the tables identified in the view by a join establishing a connection to each source of a plurality of the sources;
accessing each of the sources via the established connection;
defining a multidimensional cube for each respective source, the multidimensional cube including a dimension for each field of the query request stored in a table in the respective source;
aggregating a plurality of the multidimensional cubes for each source called for by the query request;
executing a subquery based on the respective subtree using the respective multidimensional cube; and
performing a join of the results of each multidimensional cube for generating a result set for the query request.
2. The method of claim 1 further comprising for each view, substituting a selection operation for each field called for by the view.
3. In a query engine having multiple connections to each of a plurality of data warehouses, each data warehouse corresponding to a vendor imposed storage arrangement, a method of satisfying a query request invoking each data warehouse, comprising:
receiving a query request, the query request having a plurality of fields and one or more conditions, the conditions defining selective retrieval of the data in the fields;
identifying, for each field of the plurality of fields, a source that stores the field, each field defined by one or more dimensions in the respective source;
defining, for each of the identified sources, a view for accessing the fields stored in a respective source;
establishing a connection to each source of a plurality of the sources:
further comprising, for each source of the plurality of sources,
establishing a connection to a data warehouse defining the respective source:
accumulating a set of tables corresponding to the fields sought from the respective source; and
forming a linked list of the set of accumulated tables corresponding to each connection, wherein each connection corresponds to a set of control instructions for accessing the set of tables in the respective data warehouse, the set of tables distinct from tables in other data warehouses accessible via other connections of the plurality of connections, further comprising:
defining an island based on the set of accumulated tables at the respective data warehouse; and
defining the linked list for interconnecting the islands corresponding to the plurality of connections; and
accessing each of the sources via the established connection.
4. A network device including a query engine having multiple connections to each of a plurality of data warehouses, each data warehouse corresponding to a vendor imposed storage arrangement, a method of satisfying a query request invoking each data warehouse, comprising:
an interface to a user application for receiving a query request, the query request having a plurality of fields and one or more conditions, the conditions defining selective retrieval of the data in the fields;
query logic for identifying, for each field of the plurality of fields, a source that stores the field, each field defined by one or more dimensions in the respective source;
the query logic further configured for defining, for each of the identified sources, a view for accessing the fields stored in a respective source;
query logic for forming a hierarchical structure based on dependencies in the query request, and identifying, from the hierarchical structure, one or more sub-trees corresponding to the same source, the query processor further configured to form a view corresponding to each of the sub-trees sharing the same source, wherein, for each view, the query logic identifies a set of tables stored at the source that contain the fields, and iteratively accumulates the tables identified in the view by a join;
a query processor for establishing a connection to each source of a plurality of the sources, and accessing each of the sources via the established connection;
a multidimensional cube for each respective source, the multidimensional cube including a dimension for each field of the query request stored in a table in the respective source;
the query logic further including instructions for:
aggregating a plurality of the multidimensional cubes for each source called for by the query request;
executing a subquery based on the respective subtree using the respective multidimensional cube; and
performing a join of the results of each multidimensional cube for generating a result set for the query request.
5. The device of claim 4 wherein the query processor is operable to, for each view, substitute a selection operation for each field called for by the view.
6. A network device including a query engine having multiple connections to each of a plurality of data warehouses, each data warehouse corresponding to a vendor imposed storage arrangement, for satisfying a query request invoking each data warehouse, comprising:
an interface to a user application for receiving a query request, the query request having a plurality of fields and one or more conditions, the conditions defining selective retrieval of the data in the fields;
query logic for identifying, for each field of the plurality of fields, a source that stores the field, each field defined by one or more dimensions in the respective source;
the query logic further configured for defining, for each of the identified sources, a view for accessing the fields stored in a respective source;
a query processor for establishing a connection to each source of a plurality of the sources, the query logic is configured to, for each source of the plurality of sources,
establish a connection to a data warehouse defining the respective source;
accumulate a set of tables corresponding to the fields sought from the respective source; and
form a linked list of the set of accumulated tables corresponding to each connection, wherein each connection corresponds to a set of control instructions for accessing the set of tables in the respective data warehouse, the set of tables distinct from tables in other data warehouses accessible via other connections of the plurality of connections, further comprising:
an island define based on the set of accumulated tables at the respective data warehouse; and
a linked structure for interconnecting the islands corresponding to the plurality of connections; and
accessing each of the sources via the established connection.