CA2553964A1 - System and method of data source agonistic querying - Google Patents
System and method of data source agonistic querying Download PDFInfo
- Publication number
- CA2553964A1 CA2553964A1 CA002553964A CA2553964A CA2553964A1 CA 2553964 A1 CA2553964 A1 CA 2553964A1 CA 002553964 A CA002553964 A CA 002553964A CA 2553964 A CA2553964 A CA 2553964A CA 2553964 A1 CA2553964 A1 CA 2553964A1
- Authority
- CA
- Canada
- Prior art keywords
- query
- data source
- data
- agnostic
- queries
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Abandoned
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
Abstract
A data source agnostic query system and method are provided. The system comprises a query set component for defining data to be retrieved from a dat a source. The method comprises the step of decomposing a data source agnostic query in to subqueries. The step of decomposing includes the steps of identifying the underlying data source specific planners that are involved in the preparation of the data source agnostic query and preparing the sub-queries corresponding to each planner.
Description
Svstem d M thud o D$ta source Qnostic O a ne FIELD OF THE INVIrNTTON' [0001] The invention relates generally' to data access middleware and in partictalar to a system and method of data source agnostic querying.
BACKGROUND OF THE I~NEN~TON
X0002] Many organizations use data stores for storing business data, such as financial data and operational data. ~n oxder to assist business users to examine their data, various data analyzing applications axe proposed. Those data analyzing applioations provide various views or reports of date to users. 1'he data analyzing applications have query engines that access the data stores to obtain desired data. Some data analyzing applications have online analytical processing (OLAP) engines to provide multidimansiox~al views of data.
[0003] r3ata extraction, conversion, transformation, and integration are databa8e issues.
Their solutions rely on low-level query languages: relational (such as structured query language or SQL), multidimensional (such us raulti-dimensional expressions or MDX), or proprietary enterprise resource planning (BRP) application programming interfaces (APIs). Business intelligence (H17 usexs, systemey and applications use tools that support the following taslrs:
~ Reporting on a wide range of data extracted from various types of database systems.
Ad-hoc querying of data residing in relational, multi dimensional, and ERP
databases.
Analysis a,nd exploration of data residing in rela~onal, mufti dimensional., and ERP databases.
Integration of data from multiple data sources iato a single report or analysis session.
[00041 BI systems need bo simultaneously access data from relational databases, dimensional databases, and ERP APIs. In such scenarios, a BI system would extract information from each of tho data sources and then merge the results into a report.
However, the extraction of infoxmatian from each data source is different. The BI system or a query author i~ presented with a query language that is tied to a specific database technology. The user interface is required to be aware of the type of data source it is reporting against and the query language or query tools used vary with the data source type. The user can be presented with a user interface that uses a semantic layer to insulate him from knowledge of low level query syntax, such ua SQL or 1V1DX. 'However, the user experience is inconsistent across data source types.
[pp05] There is a need for a batter way of providing a query that is operable for a plurality of data sources.
SUMMARY ~p THE INVENTION
[0006] In accordance with an embodiment of the present invention, there is provided a data source agnostic query systcrn far data source agnostic querying. The system comprises a query set component for defusing data to be retrieved from a data source, [p007] In accordance with another embodiment of the present invention, theta is provided a n~ethad of data source agnostic quexyiz~g, The method comprises the step of decomposing s data eoux~ca agnostic query into sub-queries. The step of decomposing includes the steps of identifying the underlying data source. specific players that are involved in ~,e preparation, of thB data source agnostic query and preparing the sub-queries eorrespondittg to each planner.
[0008] In accaxdance with another embodimart of the present itxvention, there is provided a memory containing computer executable instructions that can be read and executed by a computer for oaring out a method of data source agnostic querying. The method comprises the step of decomposing a data source agnostic query into sub-queries. The step of decomposing includes the steps of identifying the underlying data source specific planners that are involved in the preparation of the data. source ag~aoatic query and preparing the sub-queries corresponding to each planner,
BACKGROUND OF THE I~NEN~TON
X0002] Many organizations use data stores for storing business data, such as financial data and operational data. ~n oxder to assist business users to examine their data, various data analyzing applications axe proposed. Those data analyzing applioations provide various views or reports of date to users. 1'he data analyzing applications have query engines that access the data stores to obtain desired data. Some data analyzing applications have online analytical processing (OLAP) engines to provide multidimansiox~al views of data.
[0003] r3ata extraction, conversion, transformation, and integration are databa8e issues.
Their solutions rely on low-level query languages: relational (such as structured query language or SQL), multidimensional (such us raulti-dimensional expressions or MDX), or proprietary enterprise resource planning (BRP) application programming interfaces (APIs). Business intelligence (H17 usexs, systemey and applications use tools that support the following taslrs:
~ Reporting on a wide range of data extracted from various types of database systems.
Ad-hoc querying of data residing in relational, multi dimensional, and ERP
databases.
Analysis a,nd exploration of data residing in rela~onal, mufti dimensional., and ERP databases.
Integration of data from multiple data sources iato a single report or analysis session.
[00041 BI systems need bo simultaneously access data from relational databases, dimensional databases, and ERP APIs. In such scenarios, a BI system would extract information from each of tho data sources and then merge the results into a report.
However, the extraction of infoxmatian from each data source is different. The BI system or a query author i~ presented with a query language that is tied to a specific database technology. The user interface is required to be aware of the type of data source it is reporting against and the query language or query tools used vary with the data source type. The user can be presented with a user interface that uses a semantic layer to insulate him from knowledge of low level query syntax, such ua SQL or 1V1DX. 'However, the user experience is inconsistent across data source types.
[pp05] There is a need for a batter way of providing a query that is operable for a plurality of data sources.
SUMMARY ~p THE INVENTION
[0006] In accordance with an embodiment of the present invention, there is provided a data source agnostic query systcrn far data source agnostic querying. The system comprises a query set component for defusing data to be retrieved from a data source, [p007] In accordance with another embodiment of the present invention, theta is provided a n~ethad of data source agnostic quexyiz~g, The method comprises the step of decomposing s data eoux~ca agnostic query into sub-queries. The step of decomposing includes the steps of identifying the underlying data source. specific players that are involved in ~,e preparation, of thB data source agnostic query and preparing the sub-queries eorrespondittg to each planner.
[0008] In accaxdance with another embodimart of the present itxvention, there is provided a memory containing computer executable instructions that can be read and executed by a computer for oaring out a method of data source agnostic querying. The method comprises the step of decomposing a data source agnostic query into sub-queries. The step of decomposing includes the steps of identifying the underlying data source specific planners that are involved in the preparation of the data. source ag~aoatic query and preparing the sub-queries corresponding to each planner,
- 2'-[00091 In accordance with anotTter embodiment of the present invention, there is provided a carrier carrying a propagated eignal coataining.eomputer executable instructions that can be read and executed by a computer. Tho computer executable instructions ar~ used to execute a method of data source agnostic querying. The method comprises the step of decomposing a data source agnostic query into cub-queries. The step of decomposing.
includes the steps of identifyixig the tu~derlying data source specific planners that are involved in tlxe preparation of the data souxce agnostic query and preparing the sub-queries corresponding to each planner.
HRIBF D85CRIPTIdN OF TIDE DRAWINGS
[0010] These and other features of the invention will become more apparent from the following description in which refe~:nco ie made to th,e appended drawings wherein:
Figure l shows a typical data access environment;
Figure 2 shows in a diagram a non-data source agnostic approach to merging data source queries;
Figure 3 shows in a block diagram a data source agnostic query system, in accordance with an embodiment of the present invention;
Pigure 4 shows in a tree diagram an example of a data source agnostic query approach, in accordance with an embodiment of the data source agnostio query system;
Figure 5 shows in a flowchart au example of a method of data source agnostic querying, ih, accordance with an embodiment of the data aounce agnostic query system;
Figure 6 shows in a flowcbari another example of a method of data source agnaatic querying, in accordance with an embodiment of the data source aguoslfc query system;
Figure 7 shows in a diagram a representation of a shaped result set, in accordance with an embodiment of the data source agnostic query system;
Figure 8 shows in a diagram an example of the organization of the rowaets in the result set, in accordance with an embodiment of the data source agnostic query system;
includes the steps of identifyixig the tu~derlying data source specific planners that are involved in tlxe preparation of the data souxce agnostic query and preparing the sub-queries corresponding to each planner.
HRIBF D85CRIPTIdN OF TIDE DRAWINGS
[0010] These and other features of the invention will become more apparent from the following description in which refe~:nco ie made to th,e appended drawings wherein:
Figure l shows a typical data access environment;
Figure 2 shows in a diagram a non-data source agnostic approach to merging data source queries;
Figure 3 shows in a block diagram a data source agnostic query system, in accordance with an embodiment of the present invention;
Pigure 4 shows in a tree diagram an example of a data source agnostic query approach, in accordance with an embodiment of the data source agnostio query system;
Figure 5 shows in a flowchart au example of a method of data source agnostic querying, ih, accordance with an embodiment of the data aounce agnostic query system;
Figure 6 shows in a flowcbari another example of a method of data source agnaatic querying, in accordance with an embodiment of the data source aguoslfc query system;
Figure 7 shows in a diagram a representation of a shaped result set, in accordance with an embodiment of the data source agnostic query system;
Figure 8 shows in a diagram an example of the organization of the rowaets in the result set, in accordance with an embodiment of the data source agnostic query system;
-3~
Figure 9 shows in a diagram an example of tb~e organization of the mw edge rowscta, its accordance with an embodiuxent of the data source agnostic query syetea~.;
Figure 10 shows in a diagram an example of a query set, iu accordance with an embodiment of the data source agnostio ducry system;
Figure 11 shows in a diagram an example of a query, in accordance with as exnbodimerit of the data source agnostic query system;
Figure 12 shows in a diagram an example of a source, in accordance with an emboc3imem of the data source agnostic query systenn;
Figure 13 shows in a diagram. an e~cample of a selection, in accordance with an embodiment of the data source agnostic gusty system;
Figure 14 shows in a diagram an example of a quezy result deiynition, in accordance with an embodiment of the data source agnostic query system;
Figure 15 shows in a diagram, an example of an edge, in accordance with an embodiment of the data source agnostic query system; and Figuxe 16 shows in a diagram an example of a value set, in accorda~uce with an embodiment of the data source agnostic query system.
DETAILED DBSCRIPTIQI~ OF THB PREFa>ZRED EMBODIMENTS
[d011 ] Figure 1 ahaws a typioal data access environment 10 for processing data.
Typically, data is stored in a database 11. ~ database server 12 uses a query language (such as a stzuctured duery language (SQL) or a multidimensional expression language (MDX)) to access the raw data stored in the database 11. A report server 13 is used to generate reports on the raw data and instruct the database server 12 to obtain information pertaaniag to the raw data in the database 11. An, end user uses a client application 14, run~ding on a client server, to facilitate report sexver 13 operations.
Typically, a report server 13 has a query engine 15 for universal data access.
roolal Data extraction, conversion, transformation, and integration are all database problems. Their solutions rely on low-level query languages: relational (such as SQL),
Figure 9 shows in a diagram an example of tb~e organization of the mw edge rowscta, its accordance with an embodiuxent of the data source agnostic query syetea~.;
Figure 10 shows in a diagram an example of a query set, iu accordance with an embodiment of the data source agnostio ducry system;
Figure 11 shows in a diagram an example of a query, in accordance with as exnbodimerit of the data source agnostic query system;
Figure 12 shows in a diagram an example of a source, in accordance with an emboc3imem of the data source agnostic query systenn;
Figure 13 shows in a diagram. an e~cample of a selection, in accordance with an embodiment of the data source agnostic gusty system;
Figure 14 shows in a diagram an example of a quezy result deiynition, in accordance with an embodiment of the data source agnostic query system;
Figure 15 shows in a diagram, an example of an edge, in accordance with an embodiment of the data source agnostic query system; and Figuxe 16 shows in a diagram an example of a value set, in accorda~uce with an embodiment of the data source agnostic query system.
DETAILED DBSCRIPTIQI~ OF THB PREFa>ZRED EMBODIMENTS
[d011 ] Figure 1 ahaws a typioal data access environment 10 for processing data.
Typically, data is stored in a database 11. ~ database server 12 uses a query language (such as a stzuctured duery language (SQL) or a multidimensional expression language (MDX)) to access the raw data stored in the database 11. A report server 13 is used to generate reports on the raw data and instruct the database server 12 to obtain information pertaaniag to the raw data in the database 11. An, end user uses a client application 14, run~ding on a client server, to facilitate report sexver 13 operations.
Typically, a report server 13 has a query engine 15 for universal data access.
roolal Data extraction, conversion, transformation, and integration are all database problems. Their solutions rely on low-level query languages: relational (such as SQL),
-4-multidimensional (MDT, or proprietary ERP APIs. B~I users, systems, and applioationa use tools that support the following tasks;
Reporting on a wide range of data extracted from various types of database systems.
~ Ad-hoc querying of data residing in relational, mufti dimensibnal, and EIZP
databases.
~ Analysis and exploration of data residing in relational, mufti dimensional, and EIZP databases.
~ Integration of data &am multiple data sources into a single report or analysis session.
[0013] Figure Z shows in a tree diagram an example of a non-data source agnostiE
approach 50 to merging data aeon=s queries. Gestures 52 are tranelabod into data source queries such as SQL 62 or MDX 64. The queries are used to gepeaate reports 68 that are then merged into a user interface (LJI) report 58.
[0014) Figure 3 shows in a block diagram an example of a data source agnostic query system 100, in accordance with an ennbodiment of the present invention. The data source agnostic query system 100 is suitable for fulfilling the BI user, syat~m, or application without the need to use the low-level query languages and without the need to tailor the application for a specif a data source technology. 'The data source agnostic query system 100 comprises a quezy component 102 fez defining the data to be retrieved fmm the database, and a query result definition component 104 for describing the shape, or dimensional atcucture, of the result set to be returned for rendering.
[0015] Figuxe 4 shows in a tree diagram an example of a data source agnostic query approach 70, in accordance with an embodiment of tha data source agaostie query system 100. Qestures 52 are translated by the data source agnostic query 80 and sent to respective data aouxne queries 62, 64, and 56 to retrieve data from the respective data sources 7z, 74, 76. The retrieved information is pmcvssed and compiled into a report seat to the LTI 78.
[0016] Figure 5 shows in a flowchart an example of a method of data saume agnostic querying (184), in accordance with an embodinnent of the data source agnostic query system 100. A data source agnostic query is decomposed into sub-queries (182).
The underlying data source specific plarinexs that are involved in the preparation of the data source agnostic gusty are identified (184). Next the sub-queries corresponding to each planner are prepared (18b~. Other steps maybe added to the method (180).
[0017] Figure 6 shows in a flowchart another example of a method of data source agnostic querying (200), in acoordanee with an embodiment of the data source agnostic query system 100. ~'ho method (200) begins with translating gestures into a data source agnostic query (202). Next, the data source agnostic query ie divided into respective data source queries (204). Each data source query as sent to the respective data source query engine far processting (20~. The processed data is compiled into a report (208). The method is done. Uther steps may be added to this method.
[0018] The data source agnostic quory is a high a level query language supported For any data source agnostic application. Complex business queries axe expressed easily in this query language. A data source agnostic Hh query relies on the metadata model it is based on. It provides functionality for professional report authoring, casual ad-hoc querying, and sophisticated business analysis. To address the requirements of a business user, the data source agnostic BI query provides powerful query capabilities with a minimum of specifications. This implies that th~ data source agnostic query system 100 interprets many defaults rules in a sensible way. A single data source agnastio Hr query can spas multiple data source technologies and can'be resolved by the query framework 100 and its stack of software components at the coordination, planning, and execution layers into multiple SQL, MDX, and vendor specii"rc queries.
j001 ~~ 1'he dad source agnostic BI query hag the following features:
~ It is declarative.
It is simple enough that known database techniques for query optimization, cost estimation, and query rewriting oould be extended to this query.
~ It provides functionality for profeasionai report authoring, casual ad-hoc querying, and sophisticated business analysis against various data source technologies through a consistent user experience.
tooaol The data source agtzostic query sgeCiflcatioat (or system 100) is encapsulated within a querySet seetaon of the Query Service API ~execute~ command. This command represents s request that is submitted to the query framework, i.e., a query engine, by one o~ its clients. When the command is a request to retrieve the result set for the enclosed querySet, data results are returned as specified by the data source agnostic HI query result set API. , [0021 ] A query5et has one or more named queries (or query coxnpoxtentg 102) and one or more named queryResultDeflnitions (QRDs) 104. A query 102 in the querySet defines the data to be retrieved from the data source while a QP,I3104 defines the result set structure to be returned. In most cases, the query relies an the metadata model referenced in its source. The QItD 104 is the syntactic representation of the result set expected from the executiozr of a data source agnostic query (including data source agnostic HI query).
[002,2] The QR.D 104 is the main mechanism for query framework clients to tie a particular query to a particular result set, In a qtterySet, each QRI7 144 is based oxr a single query that which it references. Multiple QRDs 10A~ in the same querySet can reference the same query 102. This allows query authoxs to use the same query 102 in a crosstab and a chart result ~ets for example. 'This also allows the data source agnostic query System 100 to execute a single query against s data provider and stxucture the results in multiple ways. A query fi~amework APr MasterrDataset is returned for each queryResultIaefrnition specified ib a querySet.
[0023] The data aauree agnostic query system 100 provides the ability to provide a query language that is not tailored to the data source technology that is meant to query. The data source agnostic query system 100 may be implemented as a translator in a query framework that provides the ability to build various types of SI user experiences for reporting, ad~hoc querying, arid analysis that can use the query language in a consistent manner across various data source technologies. Furthermore, the query framework provides the ability to extract, convert, trapsfortn, acrd integrate data from multiple data sources and multiple data source types into a single report or analysis session using this high level data source agnostic query language.
[0024] The query result defvnitiotr (QItD) 1QA, which is part of the data source agnostic query, is a data source agnostic high Ievel definition of a rendered result sat. It allows a BI system to expxess the structure of the results of a data source agnostic query for rendering purposes.
[0025] Advantageously, a high-level query language with rich semantics allows a 'business intelligence (B~ system user andlor a user interface (IJI} software layer to pose BI queries to a query angina in a manner that ie independent of the type of database from which the results of the query are retrieved, [ooa6] Advantageously, a data source agnostic query language with minimum specification allows a ~T system user to perform repozrting, ad-hoc querying, apa1ysis and exploration on top of a large array of data base technologies (relational, mllup, OLAP, HOLAP, ERP) withaut tho need to understand SQL, IVIDX, or other low level query languages tied to s specific data base technology. The user experience is seamless and con$ietont acrvea BI capabilities and across data source technologies.
The Oue S Coin on t 10 [0027] Figure 10 shows an example of a query set component 101, in accordance with an embodiment of the data source agnostic query system 100. The query set component 101 comprises one or more query components 102 and a QRD 104. Optionally, the query set component 101 further includes a model path 103 and a request hints 105 that apply to the one or snore queries 102.
[0028] Figure 11 shows in a diagram. an example of a query component 102, in accordance with an embodiment of the data souxce agnostic query system 100.
The query component 102 includes a source 106 for defining tb~e metadata upon which the query is based, and a selection 108 for identifying the metadata upon which the query is based. A
query dots not define the structure or presentation o~ the retrieved data..
Optionally, the query component can also comprise filters, dimension information, and query hints.
Preferably, each query 102 is identif ed by a name attribute that is unique to the querySet 101.
(0029] Figure 12 shows in a diagram an example of a source 106, iu aacor~dance with an embodiment of the data source agnostic query system 100. The source 105 defines tlae metadata upon which the gusty is based. The source 106 typically is a model reference, but the data source agnostic query system 100 supports tho referencing of other queries 114 as well. In addition, the data source agnostic query system 100 supports direct queries against an underlying data source technology such as MDX, SQL, or a _g_ vendor interface that can be encoded within the specification. 3'he source 106 could be the outcome of a query operation involving one or wore queries followed by a unsry, binary or nary commands. The result is a projection of query items that can be used by the selection and the queryResultl?~Enition 10h.
[0030] An sqlQu~r 116 is an explicit defmitian of a SQL select, exec ar call statement that returns a xow based result. The sq1 element oontains the SQL definition as expressed in an SQL format. While not required to execute, each column in the result is preferably set to be described by a queryItem element in tho projectionList so that these queryrtems msy be referenced in the selection and or queryResultDe~nition 104.
[0031 ] An mdxQuery 118 is an explicit def3aition of an MDX statement that returns a multidimcusioz~al result. Ttte mdx element cantaina the MDX definition as expressed in an MDX format. The projectionIaist describe9 the projected queryltems that can be used in the se1~ctian and queryResultDefinition 104, The dimension infortnatiou describes the cube result. Queries in i~he. query set that refierence this mdxQuery 114 and use it as a souxce can use the dimension information as their default dimension info. They can also override, restrict, or extend it.
(0032] Query set operations 120 combine the results of two or more queries into a single result. IJ1VIQN, INTERSECT, and EXCEPT (MIIVL1S) operatians on two or,more queries result in a proj ection list upon which other queries care. be based.
[0033] A join operation 122 defines a relationship between query subjects in a metadata model. Typically, these relationships are defined in the metadata madel. This element is typically used to define the relationships between database tables ire non-modeled data sources during a modeling application import.
[0034] Figure 13 slwws in a diagram an example of a selection 108, in accordazzce with an embodiment of the data source agnostic query system 100. The selection 108 identifies the metadata elements upon which tho query is based. Art attributes table 138 is also included in Figure 13.
[003 5] A datavitem 124 represents a set of data values or members. The data values or members that oorrespoud to a dataItem I24 are defined by an expression element 126.
The Content of an expression element 126 is specified in accordance with i$e data source agnostic query expression grammar. Most often, a dataItem expression refers to a query _g.
item from a metadata model. Logical constructs, arithmetic operators, other query operators, and unified'funotions npresenting both relational and set (dimensional) algebxa may be defined in the mots complex use cases.
[0036] Aggregate functions such as totalQ, minimumQ, maximumQ, countQ, averageQ
are special query operations. While they can be specified in the dataItem expressions, these operators axe typically specified using tile aggregation xules discussed in tlae next section. .
[003?] Each data.Item 124 is identified by a name that is unique to the selection in which the dataltexn 124 is defned. It can be abased with an altos that can be more meaaingful than its name if the client application ch~oosee~ to do so. References to other data items in the same selection are permissible, whether unqualified or qualified by the query name in which the dataItem is defined. Such references imply that the expression associated with the dataItem is used in place of where it is referenced. Aggregate operations of the referenced dataltem 124 are not trattaferred with the expression. For example:
~uerY name"sampleQuery"?
~source> ...~Isource-<selection autoSummay"true">
~dataItem name-"Amt" aggregate=" sum">
~expression~[NS].[Product].[UnitPrice] ~' [Qty,]</expression>
</dataItem>
<dataltem name--"Qty" aggregate-"sum"'r <expression[NS).[UrderDetail].[Quantity]~lexpression>
~c/dataItem~
</selectiona [0038] The expression for the "Amt" item refers to the "Qty" item. In one embodiment of the data sourco agnostic query system 100, the actual "Amt" expression tbat would be executed resembles;
~expxessian. [NS].[Product].[Uxutfrioe] * [NS].[OrderDetai!].[(~uattdty]
,lexpxession.~
[0039] Note that the aggregate operator that is implicit with the "Qty" item (aggregate attribute is "stun") is nit part of the resulting expression.
[004] References to a dataItem 124 from anotber query must be qualified with the name of query 102 in which the dataItem 124 is defined. Following the syntax conventions, currently employed, each name is enclosed in square brackets; for example, °'[query].[item]". Such references can be used anywhere that a query item re~ from a metadata model is valid. The expressioa of the referenced dataItem la4 is used in the in place of the query item reference. For example: ' ~querySet~
query name-"SubQuery">
~BOUTCe~
model name="Model"i>
~'Isource>
tselectioti autoSummary="true">
<dataltem naxn~"Unit Priee" aggregat~'rsumn?
~expiression~[Model].(Pmduct]:[UuitPrioe]~/expreasion>
<IdataTtem-~dataItem namar"Qty" aggregate="sum"~
~expression~[Model].(~derDetail].[Quantity]~lexpression~
<Idataltem>
alseleatiorr~
~Iquer~
<query name="ParentQuary">
<soume>
EqueryRef reflQuery~"SubQuery"h <Isource~
<selection auto5ummary="true">
~dataltem zreme~"Amt" aggregate~'rsum'ra <expression. [SubQuery].[U't~it Prlce] ~
[SubQuery].(Qty]~lexpression-~ldataItem>
~Iselection~
=/que~rSet~
[Ob41 ] The dataItem 124 may define the aggregation rules to be applied to the eaKpression via the aggregate and rollupAggregate attributes. The aggregation rules suggest an aggregate function to wrap the expression when the dataTkem are summarized Fsach attribute may specify an explicit aggregate function (automatic, summarize, none, calculated, total, minimum, maximum, average, count]. The expression itself may define the aggregate fixnctxon (calculated], or the appropriate function may be derived fmm the underlying metadatA model. In addition, aggregation may be inhibited [noz~~], in which case the dataltem is grouped instead of summarized. Default aggregate rule is derived from the tmderlying metadata modol. If the rollupAggregate rule is omitted, it defaults to the aggregate specification, if any; otherwise, it is also derived from the underlying metadata model.
[0042] The "automatao" and "summarize" aggregation types are reduced to the other options in accordance with defined aggregation roles.
(0043] In one embodiment of the data souxee agnostic query system 100, examples of a~gregat10I1 types lllCludea "hone", "CalClllat8d" Slld °'tOtal t0 "eolltlt". "nolle'~ IneanS that no aggregation is supposed to be applied. "calculated" means that the expression content drivos the expressiaa aggregation. "total" to "count" are the standard aggregation types.
[0044] The aggregation context oxpresaion of a dataTtem having one of these aggregation types (directly or as a results of interpretation of "automatic" or '°summarize" ag~egatio~l types) eonsiets of the corresponding aggregation function applied roo the dataltem'a expression 126. For example, the dataltem 124 defined as:
tdataltem name="Qty" aggregate="total"~
~e~pression~[GQ].[f3rderDetail].[(~uatltity]~le~tpresaiona /dataItetn>
will have the aggregation context axpressiou:
fatal{ [CEO],[DrderDeta:il].(Quantity] ) [0045] The aggregate attribute of a dataltem is ignored for an 4LAP saurcc, because the OLAP source has reduced the original data by applying tbia type of aggregation during building of the cube.
[0046] In a data source agnostic query, the selection 106 element by itself does not specify any result set that can be consumed by a client of the data source agnostic query system 100. lA queryReaultDcfinition 104 is used for that purpose. In the limited sense that a selectiotl 108 defines a data extract that can be operated on internally within the query framework system, this data extract may be sorted in the sense that the set of data.
values or members represented by a dataztem may be sorted. The sort attribute an each dataTtem 124 may specify an ascending or descending sequence, or it may inhibit sorting on the values of that dataItem 124. This inter~nedia~y data extract that is represented by the selection 108 will be sorted according to the specifications on each dataltem, and nested in the order of the data item in the selection list. The default is unsorted, This sorting is in esseixe a pre-sort, It i8 the graupSart of the QRD 10~ that affects the final cart of data values in the result set of the query.
[0047] In a data soume.agnostic query, the selection 108 by itself does not specify any result net that can be consumed by a client of the data souriee agnostic query system 140.
A queryResultDefinition 104 is used for that purpose. in the limited sense that a selection defines a data extract that can be operated on internally within the query framework; this data extract may be grouped and summaria:ed automatically - an aIl-or'nothing operation that is controlled by the sutoSummary attribute. When enabled, all non-additive dataTtems 124 will be grouped it~tio a single summary level, and the additx a and semi-additive data,ltems 124 are sutnaearized. The result sat will contain a single row for each unique combination of the non-additive dataItem values, and an aggregate value for each additive or semi-additive dataltem. When disabled, the individual database records wilt be extracted as they appear in the database. The default is enabled ("true").
'When the data item expression identifies a single member value or a specific member get, the autoSummary att~ute has no meat~ix~g.
[0048] A query 102 may contain onE or more flltcrs that elix~nate data values or members from the result set and potentially affect the values of calculations.
Each filter element contains at least one f"ilterExpression. 'Two or more filterExpressions specified within a alter ate conjoined via AND operatozs. Multiple filter specifications are also conjoined via ,AND operators. Any filter or filterExpressian may be designated as optional, in which case it is not applied when no values are provided fox th~
parameters to which the filter or filterl3xpression refers.
[0049] Logically, one can think of the set of related queries 102 in a querySet 101 as blocks of operations and transformations perfon~ned on a data stream. In tbis~
logical representation, the querySet 101 can be visualized as a tree of query operations where each node, represented by a tquery~, pexfonns operations and tt~.nsfor~nationa on an input data stream defined in its source section then feeds the resulting output data stream to the next query node, which uses it as an input data stream. At the end of this process, a QIZD 104 is defined to represent the structure ofthe last output data stream for authozin,g purposes. Filtering and aggragation~ are two special query operations performed by a query node iu this logical tree. it is important to clearly specify their order. 'To do so, a detail filter 128 is defined which is appixed at the input data stream of a query node and hence before any calculations and aggregations are performed in that code. A
aumntary filter 130 is also defined, which is performed after aggregations. This summary filter 130 as logically equivalent to the detail filter of the nest query node that consumes the output data stream of the current node.
[0050] A query author can control the oxder in which filtering and aggregation should occur by using this mechanism in the data source agnostic query querySet (i.e., query based on query also known as subquery). In one embodiment, some sensible defaults and interpretations are provided for cases where the query author would like a minimum speoi~cation in a singl8 query. The author might not seek a granular control over desired query operations expressions.
[OOSl] Without the optional level attribute, a detailFilter 128 do$nes alters that are applied to the source of a query, before any aggregates are calculated. If the selection 108 is summarised {autoSumm~asy), Ibis filter inhibits source data values or members from participating in the calculation of the aggregate values; otherwise, it inhibits sourice data values ox members from appearing in the data extract represented by the selection.
[0052] The detail.Filter 128 can optionally specify the level at which the filter is applied.
Tf unspecified, the overall (or root) level of a dimension is assumed.
[0053] Without the optional level attribute, a summaryF'ilter 130 def"ures alters that are applied after aggregates are calculated, also lmown as a post-aggregation filter.
1_,ogically, while the detailFilter 128 is applied to the input data stream of a query, the summary filter 134 is applied to its output. This distinction and the timing of the Filter operation are critical ot~.ly with respect to the aggregate calculation operation. For example, the final output of th,e query operations represented by a query 102 is not affected by whether we sort them filter or conversely, we filter then sort.
Performance reqttirementa dictate that the latter is chosen during query planning;
however, one, Sequence or the other does not affect the result set.
[0054] Typically, in most practical cases, the query author specifies si:agle query in the querySet 1 O1 to define the data to be retrieved from the database, sad a single QRD 104 to define the result set structure. Headt~rs and Footers are specified in the QRIS 104 that represent aggregations at various nesting levels of the result set. In these cases, a detailFilter 128 is applied to the data values Crows or members) in the data.
source, while a ~lq_ summaryFilter 130 is applied to the footer or header values, which represent aggregate calculations. The sutnmaryFilter 130 can optionally specify the level at which the filter is applied. If unspecified, the overall (or root) level of a dimension is assumed.
Calculations at and above the specified levels are subject to the f lter conditions (i.a,, their values can be changed due to the filter condition).
[0055] Dimension in~armation 132 augments the selection 108 . It is optional and is specified by as advanced query author when There is no dimensioxt information available in the source.
T'he author wishes to override the dimension inforrnatiou in the source.
The author wishes to extend or restrict dimension information in the source.
[0056] The intent of dimension information 132 is not bo demo the presentation of the information, but to h~lp quory planning. In other words xt can be considered a form of hint, If the dimension information 132 is omitted then dimension infoimatio~n is used from the source if available. If not available, it will ba defaulted by the query fiumework sysbeux. .
[0057] ,A data source agnostic query will undergo a series of transformations bofore SQL, M17X, and or vendor spac~c APIs are produced and sent to the databaso. For example, a join strategy mast be derived from the underlying metadata. In addition, the generated query may be optimized to better retrieve the first N rows rather than. all rnws, push moat operations to the database, or automatically sort based upon group by stmctttre. These algorithms may be controlled through rowLimit, axecutionOptimiaation, queryfroceseing, autoSort, joinOptimization and subjectOrdering hints.
fh Ou rv R salt Definition Comr~on~t 104 [0058] Figuxe 14 shows in a diagram an example of a QRD component 104, in accordance with an embodiment of the data genres agnostic query system 100.
The query result definition (QIZD) component lb4 describes the shape, or the dimensional structure, of the result set to be returned for rendering. It is generally generated from th~ layout spoei~cation and is used to assist the rendering operation by delivering the data to be iterated in the expected form. The QiZD 104 unambiguously speci.~es a result set structure and represents a mete-model of the data source agnostic query result set API.
[0059] In, nan~data source agnostic query arohitectttre, there is a disconnect between the manner itt which queries were posed in a request to a common query eagine and how data is returned via the quexy set API. The intent with the data source agnostic query result set API is to align it with the data source agnostic query specification such that there is a correspondence between the structure of the queryResvltrie~.nitivn 104 of the data source agnostic query and the objects presented in the masterlpartial datasets of the result set API.
[0060] The QRD 104 cart be specified either as orte of the available templates or as a set of named canonical edges. The template specification is meant to provide the authotxng teals and the software developer kit (SDI) wifih a simple specification for the most common use cases. The QRD 104 can contain optional master-detail links, generated from the layout containment relationships, which define the master and detail contexts of the relationships. The masGcr-detail links 134 can be Bpeeified equivalently in the QRD
104 of the master or detail query.
[00b1] Snm~ple list, grouped list, and cross tab results can be specified in a QltD 104 in a unified manner using the canonical edge specification. Simple and grouped list results have a single edge. A cross tab result has two or more edges 136 {row, column, section 1 to sectioa ~. These edges 136 are uniquely named. The order in which the edges 13b are speciised nn the Q1t17104 is also the order in which they appear in the result sat. The edge information in the result set contains the unique name of the edge as specified in the Q1ZD 104. A query framework 100 client can use the edge's unique name to relate the edges 136 specified in the QRD 104 and the edges returned in the result set. A
cross tab with an empty row or caluznn edge can be apecif ed witb. a named empty edge <edge name--"row"h. A single edge cross tab and a grouped list with no details are represented by the same canonical edge specification. The result sets for a single edge cross tab and a grnuped list with no detail columns are also represented by the same result set API
structure.
[0062 Figure 15 shows in a diagram an example of an edge 136 type, in accordance with an embodiment of the data source agnostic query system 100. An edge 13b has a list of one or more edgeGmups 140. These are the outer-most groups in the edge 136.
They _l6.
represent member sets (or data values) that are unioxied together. Each oue of these edgeC3roups 140 has one or more value5ets 142 (that axe also uniorled within the edgeGroup), and one or more edgeC3roups I44. that are nested or cross joined within the valueSets 142. In other words, the edge represents an arbitrary shaped result set, which is composed by stitchit~ and intersecting sets of members.
[0063] Figure 7 shows iti a diagram a representation of a shaped result set 300, in accordance with ate embodiment o~the data sauce agnostic query system 100, An example of a QRD 104 for this case is the following:
<queryResultDefiuition ~cmlas:xsi="http;l/www,w3.org12001/xiVLLSchema-ix~stsnce"
xai:noNam~espaceSchemaLocation~"E:lbering\berin~_v5_specslmain\QuerySpeclVSQuer yResultl7afinition.xsd" name="SampleQRI~" refl~uery="Dou'tCare">
- <edges3 sedge name,"axis0">
~edgeCh~oups>
<edgeClroup>
' <valueSete>
~valueSet refDatuItem="Q1"I>
</valueSets>
EedgeGmupsa <edge(lroup>
<valueSets>
svalueSat refDataltem="Q3"I5 refDatartem="G4"/>
re~ataItem="C36"h ~valueSet <IvalueSets>
E~S~
<edgeGroup>
~valueSets>
<valueSet <IvalueSets>
<ledgeC3roup>
~IedgeGrnupa~
<IedgeGroup>
<ledg~Groups>
~I~dge(3roup>
<edge(3roup>
<valueSets>
CvalueSet ratDataltem="G2"I>
~IvalueSets~
<edgeGroups>
<edgeC3roup>
~valueSeta>
refDataItenn="C~Snh reibataTtem=~~G7"I~, CvallleSet ~IvalueSets>
~edgeGmups>
~edgeGroup>
walueSets>
~valueSet ~IvalueSets~
<IedgeGroup~
~/edgeGraups5 dedgeGroup-</edgeQroups~
=ladg~Group~
~/cdgeQroupa>
~ledge~
<ledges~
~/quoryResultDafinitior~
[0064] The data source agnostic query result set API presents each group as a rowset that can be iterated using an IRSSetIterator object. Group headers and footers are presented as separate rowsets that can be accessed within conte~rt of a group's corresponding mwset. The name of the rocvset corresponding to a group is unique and is that of the dataltemRef of the valueSet that represents the level key of the group in the QRD 104, making it clear to the client application how the data in the result set corresponds to the layout specification.
[0065] Just as the data source agnostic query speei~cation maintains a consistent approach to specifying groups in both list and cross tab queries, the data source agnostic query result set API presents a single approach to iterating values is a list report as when iterating values along the edges ova cross tab report. In the data source agnostic query result set API, list reports are accessed via the same IRSSetIterator class that is used to navigate the edgos of cross tab result sets. At any grouping level (represented by a separate rowset), header and footer values mar be obtained at any time. All of the detail rows of a report are contained in the single, innermost rowset named "details".
[0066] In the data source agnostic query result set API, there are bo restrictions oa ktow xowsets are r~lated. The result set is instead restricted by what can be authored in the data source agnostic query specification.
~18-[0067] An edgeGroup 140, represents an. arbitrary shaped set of members (data values) on an edge 136. A flat list of non-nested edge groups in an edge specification can be used to represent the unioning of member seta. Each group can have one or more valueSeta 142 that repreaeut the group's members (based on a caption key and associated body attributes), an optional header and/or footer, a earl, and suppression. Each group can also have one or more nested groups.
[0068] An explorer-mode arose t$b edge can be specified by a set of nested edge groups.
ay nesting and unioning edge groups, a query framework client can specify a reporter-mode crasstab edge.
[0069] A grouped list report can be specified by a sat of nested edge groups with the inner moat edge group representing the details. This special group is not keyed on any level (i.e., it valueSet has not re~~ataltem attribute) and its body references the detail columns as level attributes.
[0070] Figure 16 shows in a diagram an example of a valueSet 142 type, in aceardance with an embadirnent of the data source agnostic query system 100. The valueSet 142, also kCnown as a memberSet, defines a collection of values or members to be returned for an edgeClroup 140. It represents a (nesting) level in an explorer style edge.
The ref.Dataltem attribute of this element represents the "key" associated wrath the level. The name attribute identifies the valueSet within the QRD 104, and ie unique within the scope of the QRD 104.
[00'11 ] The groupHeader 146 and groupFooter 148 child elements of the valueSet 142 element define a set of data values or members that represents a summary of the group members.
[0072] The groupBody I50 child element of the valueSet element defines the attributes to be returned for each member in the grnup.
[0073] The groupSort 1.52 child element of the value5et element defines the sort order for the group members within a context def red by the entire result set. A quezy author can define a sort using projocted and nonprojected items, The groupSort 152 aan refexeuee a data item form the associated query 102 even if the data item was not used in QltD 104.
For a detail group (i.e., a group with a valueSet 142 that has no data item reference and has a group body reference a list of items) the order of the groupSort 152 items dictator the order in which the details are sorted, [0074] In or~.e embo~himent, queryResultDefmitiorl 104 tennplates represent a choice of one of three basic templates that cover the most common report type (lists, cross tabs, charts). They are meant to provide authoring tools and the SDK with simple apecificatians for the most common use cases.
se Case Sim le is [0075] One basic data source agnostic query that may be specified is the Simple List.
The result set may contain sammary or detail database rows (autoSummary). In both cases, the rosult set structure is the sanne as dead by the QRD 104, 4txe grouping level may be specified. Any aggregate speelftcations are applicable only to the lowest grouping level in a summary query - since there's only one stooping level, control break aggregates at various grouping levels are not supported. The next example is of a Simple List report containing [Drder year], [Order tt~ethad], and [Quantity].
Order Order methodr~~ti ear ' 2000 E-mail 86 884 2000 Fax 34 462 2000 Mail 54,874 2000 Sales visit135,262 2001 ~ E-mail 12 350 ~
2001 Fax 41558 2001 Mail 43 672 2001 Sales visit191 2002 Irmail 139 2002 fax 39,824 2002 Mail 25.684 2002 Sales visit208 [0076] The QRD 104 for this example (using canonioal edge specification) is the following:
<queryResultDefinitions>
~queryResultDefinition name="rsl" re#Query~"queryl "~
Ced~~S~' zedge slam~="edge0"a tedgeGroupsy ~edgeGroupa walueSets~
~valueSet~
~groupBod~
dataltemRef retbataltem="Order Year"I?
refDataltem="Order Method"h ~dataItemR.ef adataItemRef refDataltem="Quantity"I~
~lgroupHody>
~IvalueSet~
'-IvalueSets~
~/edgeGmup~
~/edgeGroups~
Eledge>
~/edgosy 4queryResultl7eftnition7 ~IqueryResultDe~nitions~
GquerySet~
[0077] The QRD for this example (using the list template specification) is tlxe following:
~queryResultDefinitions~
~queryResultDefinition name"rs 1" rei~uery~"queryl "~
4esultTemplate>
~li~tResult~
<detail~
~dataItemRef retDataItem~'Order Yeat"/5 ~dataItetnRef re~ataItera="Oxder Method"h ~dataIte:mRef refDataItem~"Quantity"/a t/details~
~/listResult~
~IxesultTemplate~
~IqtteryResultDefiuition~
~/queryResultDe~nitions~
=/querySet;
G~uped List [0078] The next example is of a list report containing [order year], [Order method], and [Quantity], grouped by [Order year] and with a report level summary.
~21-4rde~r drdet ~rtethodanti eat 2000 B-mail ' 86,884 Fax 34,4b2 Mail 54 874 Sales visit135,262 2001 E-mail 122 Fax 41 558 Mail 43,672 Sales visit191,578 2001 399,158 2002 E-mail 139 Fax 39,$24 Mail 25.684 saleB ~isit2os ssx zoo2 413 sr,~ l,lza,sri [0079] The QRD 104 for this example (using canonical edgy specif'tcatiou) is the following;
,cqueryResultDefinition game="groupedList" rofQuery-"some query"
~edges~
<edge name="edge0"~
~edgeC,~oups~
~edgeCfroup~
<valueSets~
~alueSet xefDatartem="~rder'Year"a ~groupFootez' <dataTtexnRef refDataItem~"Quatityn~~
refDataItem="Ordor Method"h refl3ataltem="Quantity"~~
</groupFaotex~
~IvaIueSei?
~IvalueSets~
~edgeQroupa-~edgeQroup>
~valueSets>
walueSet~
~groupBody~
.data,ItennRef sdatalt~mRef ~/groupBody~
<IvalueSetd>
-ZZ-~/edgeGroup~
</edgeGroups~
~/edgeGnoup:
~/edgeCrroups>
~ledge~
~/edges~
~/queryltesultDe~nition~
[0080] Figure 8 shows in a disgrarn an example of the organization of the rowset8 in the result set 320, in accordance with ati embodiment of the data source agnostic duery system 100. Note that the [Order method] rowset 322 contains both the [Order method]
and [Quantity] data sets from the QRD 104. Also, the [4rder year] Footor mwset contains ono mw of data that represents the report summaxy.
Crosstab [0081 ] A cross tab result prosents s grid of summarized data values:
effectively, it is an intarsectian of two Grouped List results. A QItD 104 with two or more edges defines a cross tab result. Aggregates at various intersections are calculated automatically. This exampte is the sart~e as the previous example, except the data ie presented as a cross tab.
2000 E-mail 86 884 ~
Fax 34 462 Mail 54,874 visit 135 Sales 262 _ 311,482 2001 &mail 122,350 Fax A~ 1 Mail 43,672 Sales 191,578 visit 2002 E-mail 139 Fax 39 824 Mail a5.G84 Sales 208 visit 858 ~~rder 1,123,872 Year [0082] Tlxe QRD 104 for this example (using canonical edge specification) is the following:
<queryResultDe$nition name="groupedLiat" rc~uery="some query">
<edgea>
<edge name"edge0">
<edgeQroups>
<edgeC3roup>
walueSets>
<valueSet xefbataItem-"Order Year">
<valueSet retbataltem~"Order Year">
<gmuphooter>
<dataItemRef refDataItem-"Quatity"h refDataItem-"Order Method">
refDataltemm'Ordex Year"I>
~IgroupFooter>
</valu~eSet~
~valuaSets>
~dgeGroups>
<edgeGroup?
'walueSets>
<value5et <groupFoote,~
<dataItemRef </groupFootet>
<IvalueSet~
<IvalueSets>
-/edgeCiroup>
<IedgeGxoupa>
</edgeC~roup>
<IedgeGroups> .
<ledge>
<edge name="odgel''> .
<edget~roups?
<edgeCrrottp%
walueSets>
walueSet reibataTtexn="Quantity">ClvalueSel>
~JvslueSets>
</edgeClroup>
</edgeClroupsa <ledge=
</edges>
</ciueryResultDefinition~
[0083] The representation of the xowsets is identical to the previous example, except that the [Order method] rowset 322 aw longer contains the [Quantity] column - those values .are now contained within a cell rowset iterator.
C~O~etab 2 [0084] Figure 9 shows in a diagram ~n example of the organization of the row edge rowseta 340, in accordance with an embodiment of the data source agnostic query system 100. This example presents simple oross tab report with [Country] 342 nested within [Product] 344 along the row edge and [Quantity] along the column edge. Sub-totals are calculated for eaclx product.
ProductlUSA 1000 Canada500 France2000 Total 3500 Product2USA 3000 CanadaX000 FranceS00 Total 5500 [OOSS] 'The QRD 104 for this example (using canonical edgc specification) is the following' queryResultDefmition mane="groupedLiet" rei~uery="some query">
<edges>
<edge nanne="rows">
Eedge(~raups>
<edgeGroup~
~aluaSet~
walueSec roipataItem="Praduct"I~
<IvalueSets>
<edgeClroups>
~edgeCrroup~
walueSets>
<valueSet re~ataltem~"Country">
<groupl7oote~
<IgroupFootez' <IvalueSel~
</valueSets~
~IedgeC~roup>
/edgeGroups> .
</edgeGroup%
~/edgeC~roups>
tledg~
sedge name="columns">
~edgeC~roups>
~dgeGxoup>
<valueSets>
~alueSet re~ataItem--"Quantity"»IvalueSet?
~/valueSets~
~JedgeGroup~
CledgeC~?roups>
aledge>
~ledges>
~/queryResultDe~nition>
[0086] Notice the empty group footer fox the country valueSet. It indicates that the rowset corresponding to this footer should have zem columns, which is a valid case.
Consumers of this result set will use the existence of this empty rowsot to ~orm grouping breaks for example when. rendering such a result set.
[0087] The systems and methods according to th~a present invention may be implemented by any hardware, software or a cambinatia~n df hardware and software having the functions described above. The software code, either is its entirety or a pact thereof, may be stored in a computer readable memory. Furtb~et', a computer data signal representing the so#tvvare code that may be embedded in a carrier wave may he transmitted via a communication network. Such a computer readable memory and a computer data signal are also within the scope of the present invention, as well as the hardware, software sad the combination thereof.
[0088] While particular embodiments of the present invention have been shown acid described, changes and modiiy.Cations may be made to such embodiments without departing from the true scope of the invention.
Reporting on a wide range of data extracted from various types of database systems.
~ Ad-hoc querying of data residing in relational, mufti dimensibnal, and EIZP
databases.
~ Analysis and exploration of data residing in relational, mufti dimensional, and EIZP databases.
~ Integration of data &am multiple data sources into a single report or analysis session.
[0013] Figure Z shows in a tree diagram an example of a non-data source agnostiE
approach 50 to merging data aeon=s queries. Gestures 52 are tranelabod into data source queries such as SQL 62 or MDX 64. The queries are used to gepeaate reports 68 that are then merged into a user interface (LJI) report 58.
[0014) Figure 3 shows in a block diagram an example of a data source agnostic query system 100, in accordance with an ennbodiment of the present invention. The data source agnostic query system 100 is suitable for fulfilling the BI user, syat~m, or application without the need to use the low-level query languages and without the need to tailor the application for a specif a data source technology. 'The data source agnostic query system 100 comprises a quezy component 102 fez defining the data to be retrieved fmm the database, and a query result definition component 104 for describing the shape, or dimensional atcucture, of the result set to be returned for rendering.
[0015] Figuxe 4 shows in a tree diagram an example of a data source agnostic query approach 70, in accordance with an embodiment of tha data source agaostie query system 100. Qestures 52 are translated by the data source agnostic query 80 and sent to respective data aouxne queries 62, 64, and 56 to retrieve data from the respective data sources 7z, 74, 76. The retrieved information is pmcvssed and compiled into a report seat to the LTI 78.
[0016] Figure 5 shows in a flowchart an example of a method of data saume agnostic querying (184), in accordance with an embodinnent of the data source agnostic query system 100. A data source agnostic query is decomposed into sub-queries (182).
The underlying data source specific plarinexs that are involved in the preparation of the data source agnostic gusty are identified (184). Next the sub-queries corresponding to each planner are prepared (18b~. Other steps maybe added to the method (180).
[0017] Figure 6 shows in a flowchart another example of a method of data source agnostic querying (200), in acoordanee with an embodiment of the data source agnostic query system 100. ~'ho method (200) begins with translating gestures into a data source agnostic query (202). Next, the data source agnostic query ie divided into respective data source queries (204). Each data source query as sent to the respective data source query engine far processting (20~. The processed data is compiled into a report (208). The method is done. Uther steps may be added to this method.
[0018] The data source agnostic quory is a high a level query language supported For any data source agnostic application. Complex business queries axe expressed easily in this query language. A data source agnostic Hh query relies on the metadata model it is based on. It provides functionality for professional report authoring, casual ad-hoc querying, and sophisticated business analysis. To address the requirements of a business user, the data source agnostic BI query provides powerful query capabilities with a minimum of specifications. This implies that th~ data source agnostic query system 100 interprets many defaults rules in a sensible way. A single data source agnastio Hr query can spas multiple data source technologies and can'be resolved by the query framework 100 and its stack of software components at the coordination, planning, and execution layers into multiple SQL, MDX, and vendor specii"rc queries.
j001 ~~ 1'he dad source agnostic BI query hag the following features:
~ It is declarative.
It is simple enough that known database techniques for query optimization, cost estimation, and query rewriting oould be extended to this query.
~ It provides functionality for profeasionai report authoring, casual ad-hoc querying, and sophisticated business analysis against various data source technologies through a consistent user experience.
tooaol The data source agtzostic query sgeCiflcatioat (or system 100) is encapsulated within a querySet seetaon of the Query Service API ~execute~ command. This command represents s request that is submitted to the query framework, i.e., a query engine, by one o~ its clients. When the command is a request to retrieve the result set for the enclosed querySet, data results are returned as specified by the data source agnostic HI query result set API. , [0021 ] A query5et has one or more named queries (or query coxnpoxtentg 102) and one or more named queryResultDeflnitions (QRDs) 104. A query 102 in the querySet defines the data to be retrieved from the data source while a QP,I3104 defines the result set structure to be returned. In most cases, the query relies an the metadata model referenced in its source. The QItD 104 is the syntactic representation of the result set expected from the executiozr of a data source agnostic query (including data source agnostic HI query).
[002,2] The QR.D 104 is the main mechanism for query framework clients to tie a particular query to a particular result set, In a qtterySet, each QRI7 144 is based oxr a single query that which it references. Multiple QRDs 10A~ in the same querySet can reference the same query 102. This allows query authoxs to use the same query 102 in a crosstab and a chart result ~ets for example. 'This also allows the data source agnostic query System 100 to execute a single query against s data provider and stxucture the results in multiple ways. A query fi~amework APr MasterrDataset is returned for each queryResultIaefrnition specified ib a querySet.
[0023] The data aauree agnostic query system 100 provides the ability to provide a query language that is not tailored to the data source technology that is meant to query. The data source agnostic query system 100 may be implemented as a translator in a query framework that provides the ability to build various types of SI user experiences for reporting, ad~hoc querying, arid analysis that can use the query language in a consistent manner across various data source technologies. Furthermore, the query framework provides the ability to extract, convert, trapsfortn, acrd integrate data from multiple data sources and multiple data source types into a single report or analysis session using this high level data source agnostic query language.
[0024] The query result defvnitiotr (QItD) 1QA, which is part of the data source agnostic query, is a data source agnostic high Ievel definition of a rendered result sat. It allows a BI system to expxess the structure of the results of a data source agnostic query for rendering purposes.
[0025] Advantageously, a high-level query language with rich semantics allows a 'business intelligence (B~ system user andlor a user interface (IJI} software layer to pose BI queries to a query angina in a manner that ie independent of the type of database from which the results of the query are retrieved, [ooa6] Advantageously, a data source agnostic query language with minimum specification allows a ~T system user to perform repozrting, ad-hoc querying, apa1ysis and exploration on top of a large array of data base technologies (relational, mllup, OLAP, HOLAP, ERP) withaut tho need to understand SQL, IVIDX, or other low level query languages tied to s specific data base technology. The user experience is seamless and con$ietont acrvea BI capabilities and across data source technologies.
The Oue S Coin on t 10 [0027] Figure 10 shows an example of a query set component 101, in accordance with an embodiment of the data source agnostic query system 100. The query set component 101 comprises one or more query components 102 and a QRD 104. Optionally, the query set component 101 further includes a model path 103 and a request hints 105 that apply to the one or snore queries 102.
[0028] Figure 11 shows in a diagram. an example of a query component 102, in accordance with an embodiment of the data souxce agnostic query system 100.
The query component 102 includes a source 106 for defining tb~e metadata upon which the query is based, and a selection 108 for identifying the metadata upon which the query is based. A
query dots not define the structure or presentation o~ the retrieved data..
Optionally, the query component can also comprise filters, dimension information, and query hints.
Preferably, each query 102 is identif ed by a name attribute that is unique to the querySet 101.
(0029] Figure 12 shows in a diagram an example of a source 106, iu aacor~dance with an embodiment of the data source agnostic query system 100. The source 105 defines tlae metadata upon which the gusty is based. The source 106 typically is a model reference, but the data source agnostic query system 100 supports tho referencing of other queries 114 as well. In addition, the data source agnostic query system 100 supports direct queries against an underlying data source technology such as MDX, SQL, or a _g_ vendor interface that can be encoded within the specification. 3'he source 106 could be the outcome of a query operation involving one or wore queries followed by a unsry, binary or nary commands. The result is a projection of query items that can be used by the selection and the queryResultl?~Enition 10h.
[0030] An sqlQu~r 116 is an explicit defmitian of a SQL select, exec ar call statement that returns a xow based result. The sq1 element oontains the SQL definition as expressed in an SQL format. While not required to execute, each column in the result is preferably set to be described by a queryItem element in tho projectionList so that these queryrtems msy be referenced in the selection and or queryResultDe~nition 104.
[0031 ] An mdxQuery 118 is an explicit def3aition of an MDX statement that returns a multidimcusioz~al result. Ttte mdx element cantaina the MDX definition as expressed in an MDX format. The projectionIaist describe9 the projected queryltems that can be used in the se1~ctian and queryResultDefinition 104, The dimension infortnatiou describes the cube result. Queries in i~he. query set that refierence this mdxQuery 114 and use it as a souxce can use the dimension information as their default dimension info. They can also override, restrict, or extend it.
(0032] Query set operations 120 combine the results of two or more queries into a single result. IJ1VIQN, INTERSECT, and EXCEPT (MIIVL1S) operatians on two or,more queries result in a proj ection list upon which other queries care. be based.
[0033] A join operation 122 defines a relationship between query subjects in a metadata model. Typically, these relationships are defined in the metadata madel. This element is typically used to define the relationships between database tables ire non-modeled data sources during a modeling application import.
[0034] Figure 13 slwws in a diagram an example of a selection 108, in accordazzce with an embodiment of the data source agnostic query system 100. The selection 108 identifies the metadata elements upon which tho query is based. Art attributes table 138 is also included in Figure 13.
[003 5] A datavitem 124 represents a set of data values or members. The data values or members that oorrespoud to a dataItem I24 are defined by an expression element 126.
The Content of an expression element 126 is specified in accordance with i$e data source agnostic query expression grammar. Most often, a dataItem expression refers to a query _g.
item from a metadata model. Logical constructs, arithmetic operators, other query operators, and unified'funotions npresenting both relational and set (dimensional) algebxa may be defined in the mots complex use cases.
[0036] Aggregate functions such as totalQ, minimumQ, maximumQ, countQ, averageQ
are special query operations. While they can be specified in the dataItem expressions, these operators axe typically specified using tile aggregation xules discussed in tlae next section. .
[003?] Each data.Item 124 is identified by a name that is unique to the selection in which the dataltexn 124 is defned. It can be abased with an altos that can be more meaaingful than its name if the client application ch~oosee~ to do so. References to other data items in the same selection are permissible, whether unqualified or qualified by the query name in which the dataItem is defined. Such references imply that the expression associated with the dataItem is used in place of where it is referenced. Aggregate operations of the referenced dataltem 124 are not trattaferred with the expression. For example:
~uerY name"sampleQuery"?
~source> ...~Isource-<selection autoSummay"true">
~dataItem name-"Amt" aggregate=" sum">
~expression~[NS].[Product].[UnitPrice] ~' [Qty,]</expression>
</dataItem>
<dataltem name--"Qty" aggregate-"sum"'r <expression[NS).[UrderDetail].[Quantity]~lexpression>
~c/dataItem~
</selectiona [0038] The expression for the "Amt" item refers to the "Qty" item. In one embodiment of the data sourco agnostic query system 100, the actual "Amt" expression tbat would be executed resembles;
~expxessian. [NS].[Product].[Uxutfrioe] * [NS].[OrderDetai!].[(~uattdty]
,lexpxession.~
[0039] Note that the aggregate operator that is implicit with the "Qty" item (aggregate attribute is "stun") is nit part of the resulting expression.
[004] References to a dataItem 124 from anotber query must be qualified with the name of query 102 in which the dataItem 124 is defined. Following the syntax conventions, currently employed, each name is enclosed in square brackets; for example, °'[query].[item]". Such references can be used anywhere that a query item re~ from a metadata model is valid. The expressioa of the referenced dataItem la4 is used in the in place of the query item reference. For example: ' ~querySet~
query name-"SubQuery">
~BOUTCe~
model name="Model"i>
~'Isource>
tselectioti autoSummary="true">
<dataltem naxn~"Unit Priee" aggregat~'rsumn?
~expiression~[Model].(Pmduct]:[UuitPrioe]~/expreasion>
<IdataTtem-~dataItem namar"Qty" aggregate="sum"~
~expression~[Model].(~derDetail].[Quantity]~lexpression~
<Idataltem>
alseleatiorr~
~Iquer~
<query name="ParentQuary">
<soume>
EqueryRef reflQuery~"SubQuery"h <Isource~
<selection auto5ummary="true">
~dataltem zreme~"Amt" aggregate~'rsum'ra <expression. [SubQuery].[U't~it Prlce] ~
[SubQuery].(Qty]~lexpression-~ldataItem>
~Iselection~
=/que~rSet~
[Ob41 ] The dataItem 124 may define the aggregation rules to be applied to the eaKpression via the aggregate and rollupAggregate attributes. The aggregation rules suggest an aggregate function to wrap the expression when the dataTkem are summarized Fsach attribute may specify an explicit aggregate function (automatic, summarize, none, calculated, total, minimum, maximum, average, count]. The expression itself may define the aggregate fixnctxon (calculated], or the appropriate function may be derived fmm the underlying metadatA model. In addition, aggregation may be inhibited [noz~~], in which case the dataltem is grouped instead of summarized. Default aggregate rule is derived from the tmderlying metadata modol. If the rollupAggregate rule is omitted, it defaults to the aggregate specification, if any; otherwise, it is also derived from the underlying metadata model.
[0042] The "automatao" and "summarize" aggregation types are reduced to the other options in accordance with defined aggregation roles.
(0043] In one embodiment of the data souxee agnostic query system 100, examples of a~gregat10I1 types lllCludea "hone", "CalClllat8d" Slld °'tOtal t0 "eolltlt". "nolle'~ IneanS that no aggregation is supposed to be applied. "calculated" means that the expression content drivos the expressiaa aggregation. "total" to "count" are the standard aggregation types.
[0044] The aggregation context oxpresaion of a dataTtem having one of these aggregation types (directly or as a results of interpretation of "automatic" or '°summarize" ag~egatio~l types) eonsiets of the corresponding aggregation function applied roo the dataltem'a expression 126. For example, the dataltem 124 defined as:
tdataltem name="Qty" aggregate="total"~
~e~pression~[GQ].[f3rderDetail].[(~uatltity]~le~tpresaiona /dataItetn>
will have the aggregation context axpressiou:
fatal{ [CEO],[DrderDeta:il].(Quantity] ) [0045] The aggregate attribute of a dataltem is ignored for an 4LAP saurcc, because the OLAP source has reduced the original data by applying tbia type of aggregation during building of the cube.
[0046] In a data source agnostic query, the selection 106 element by itself does not specify any result set that can be consumed by a client of the data source agnostic query system 100. lA queryReaultDcfinition 104 is used for that purpose. In the limited sense that a selectiotl 108 defines a data extract that can be operated on internally within the query framework system, this data extract may be sorted in the sense that the set of data.
values or members represented by a dataztem may be sorted. The sort attribute an each dataTtem 124 may specify an ascending or descending sequence, or it may inhibit sorting on the values of that dataItem 124. This inter~nedia~y data extract that is represented by the selection 108 will be sorted according to the specifications on each dataltem, and nested in the order of the data item in the selection list. The default is unsorted, This sorting is in esseixe a pre-sort, It i8 the graupSart of the QRD 10~ that affects the final cart of data values in the result set of the query.
[0047] In a data soume.agnostic query, the selection 108 by itself does not specify any result net that can be consumed by a client of the data souriee agnostic query system 140.
A queryResultDefinition 104 is used for that purpose. in the limited sense that a selection defines a data extract that can be operated on internally within the query framework; this data extract may be grouped and summaria:ed automatically - an aIl-or'nothing operation that is controlled by the sutoSummary attribute. When enabled, all non-additive dataTtems 124 will be grouped it~tio a single summary level, and the additx a and semi-additive data,ltems 124 are sutnaearized. The result sat will contain a single row for each unique combination of the non-additive dataItem values, and an aggregate value for each additive or semi-additive dataltem. When disabled, the individual database records wilt be extracted as they appear in the database. The default is enabled ("true").
'When the data item expression identifies a single member value or a specific member get, the autoSummary att~ute has no meat~ix~g.
[0048] A query 102 may contain onE or more flltcrs that elix~nate data values or members from the result set and potentially affect the values of calculations.
Each filter element contains at least one f"ilterExpression. 'Two or more filterExpressions specified within a alter ate conjoined via AND operatozs. Multiple filter specifications are also conjoined via ,AND operators. Any filter or filterExpressian may be designated as optional, in which case it is not applied when no values are provided fox th~
parameters to which the filter or filterl3xpression refers.
[0049] Logically, one can think of the set of related queries 102 in a querySet 101 as blocks of operations and transformations perfon~ned on a data stream. In tbis~
logical representation, the querySet 101 can be visualized as a tree of query operations where each node, represented by a tquery~, pexfonns operations and tt~.nsfor~nationa on an input data stream defined in its source section then feeds the resulting output data stream to the next query node, which uses it as an input data stream. At the end of this process, a QIZD 104 is defined to represent the structure ofthe last output data stream for authozin,g purposes. Filtering and aggragation~ are two special query operations performed by a query node iu this logical tree. it is important to clearly specify their order. 'To do so, a detail filter 128 is defined which is appixed at the input data stream of a query node and hence before any calculations and aggregations are performed in that code. A
aumntary filter 130 is also defined, which is performed after aggregations. This summary filter 130 as logically equivalent to the detail filter of the nest query node that consumes the output data stream of the current node.
[0050] A query author can control the oxder in which filtering and aggregation should occur by using this mechanism in the data source agnostic query querySet (i.e., query based on query also known as subquery). In one embodiment, some sensible defaults and interpretations are provided for cases where the query author would like a minimum speoi~cation in a singl8 query. The author might not seek a granular control over desired query operations expressions.
[OOSl] Without the optional level attribute, a detailFilter 128 do$nes alters that are applied to the source of a query, before any aggregates are calculated. If the selection 108 is summarised {autoSumm~asy), Ibis filter inhibits source data values or members from participating in the calculation of the aggregate values; otherwise, it inhibits sourice data values ox members from appearing in the data extract represented by the selection.
[0052] The detail.Filter 128 can optionally specify the level at which the filter is applied.
Tf unspecified, the overall (or root) level of a dimension is assumed.
[0053] Without the optional level attribute, a summaryF'ilter 130 def"ures alters that are applied after aggregates are calculated, also lmown as a post-aggregation filter.
1_,ogically, while the detailFilter 128 is applied to the input data stream of a query, the summary filter 134 is applied to its output. This distinction and the timing of the Filter operation are critical ot~.ly with respect to the aggregate calculation operation. For example, the final output of th,e query operations represented by a query 102 is not affected by whether we sort them filter or conversely, we filter then sort.
Performance reqttirementa dictate that the latter is chosen during query planning;
however, one, Sequence or the other does not affect the result set.
[0054] Typically, in most practical cases, the query author specifies si:agle query in the querySet 1 O1 to define the data to be retrieved from the database, sad a single QRD 104 to define the result set structure. Headt~rs and Footers are specified in the QRIS 104 that represent aggregations at various nesting levels of the result set. In these cases, a detailFilter 128 is applied to the data values Crows or members) in the data.
source, while a ~lq_ summaryFilter 130 is applied to the footer or header values, which represent aggregate calculations. The sutnmaryFilter 130 can optionally specify the level at which the filter is applied. If unspecified, the overall (or root) level of a dimension is assumed.
Calculations at and above the specified levels are subject to the f lter conditions (i.a,, their values can be changed due to the filter condition).
[0055] Dimension in~armation 132 augments the selection 108 . It is optional and is specified by as advanced query author when There is no dimensioxt information available in the source.
T'he author wishes to override the dimension inforrnatiou in the source.
The author wishes to extend or restrict dimension information in the source.
[0056] The intent of dimension information 132 is not bo demo the presentation of the information, but to h~lp quory planning. In other words xt can be considered a form of hint, If the dimension information 132 is omitted then dimension infoimatio~n is used from the source if available. If not available, it will ba defaulted by the query fiumework sysbeux. .
[0057] ,A data source agnostic query will undergo a series of transformations bofore SQL, M17X, and or vendor spac~c APIs are produced and sent to the databaso. For example, a join strategy mast be derived from the underlying metadata. In addition, the generated query may be optimized to better retrieve the first N rows rather than. all rnws, push moat operations to the database, or automatically sort based upon group by stmctttre. These algorithms may be controlled through rowLimit, axecutionOptimiaation, queryfroceseing, autoSort, joinOptimization and subjectOrdering hints.
fh Ou rv R salt Definition Comr~on~t 104 [0058] Figuxe 14 shows in a diagram an example of a QRD component 104, in accordance with an embodiment of the data genres agnostic query system 100.
The query result definition (QIZD) component lb4 describes the shape, or the dimensional structure, of the result set to be returned for rendering. It is generally generated from th~ layout spoei~cation and is used to assist the rendering operation by delivering the data to be iterated in the expected form. The QiZD 104 unambiguously speci.~es a result set structure and represents a mete-model of the data source agnostic query result set API.
[0059] In, nan~data source agnostic query arohitectttre, there is a disconnect between the manner itt which queries were posed in a request to a common query eagine and how data is returned via the quexy set API. The intent with the data source agnostic query result set API is to align it with the data source agnostic query specification such that there is a correspondence between the structure of the queryResvltrie~.nitivn 104 of the data source agnostic query and the objects presented in the masterlpartial datasets of the result set API.
[0060] The QRD 104 cart be specified either as orte of the available templates or as a set of named canonical edges. The template specification is meant to provide the authotxng teals and the software developer kit (SDI) wifih a simple specification for the most common use cases. The QRD 104 can contain optional master-detail links, generated from the layout containment relationships, which define the master and detail contexts of the relationships. The masGcr-detail links 134 can be Bpeeified equivalently in the QRD
104 of the master or detail query.
[00b1] Snm~ple list, grouped list, and cross tab results can be specified in a QltD 104 in a unified manner using the canonical edge specification. Simple and grouped list results have a single edge. A cross tab result has two or more edges 136 {row, column, section 1 to sectioa ~. These edges 136 are uniquely named. The order in which the edges 13b are speciised nn the Q1t17104 is also the order in which they appear in the result sat. The edge information in the result set contains the unique name of the edge as specified in the Q1ZD 104. A query framework 100 client can use the edge's unique name to relate the edges 136 specified in the QRD 104 and the edges returned in the result set. A
cross tab with an empty row or caluznn edge can be apecif ed witb. a named empty edge <edge name--"row"h. A single edge cross tab and a grouped list with no details are represented by the same canonical edge specification. The result sets for a single edge cross tab and a grnuped list with no detail columns are also represented by the same result set API
structure.
[0062 Figure 15 shows in a diagram an example of an edge 136 type, in accordance with an embodiment of the data source agnostic query system 100. An edge 13b has a list of one or more edgeGmups 140. These are the outer-most groups in the edge 136.
They _l6.
represent member sets (or data values) that are unioxied together. Each oue of these edgeC3roups 140 has one or more value5ets 142 (that axe also uniorled within the edgeGroup), and one or more edgeC3roups I44. that are nested or cross joined within the valueSets 142. In other words, the edge represents an arbitrary shaped result set, which is composed by stitchit~ and intersecting sets of members.
[0063] Figure 7 shows iti a diagram a representation of a shaped result set 300, in accordance with ate embodiment o~the data sauce agnostic query system 100, An example of a QRD 104 for this case is the following:
<queryResultDefiuition ~cmlas:xsi="http;l/www,w3.org12001/xiVLLSchema-ix~stsnce"
xai:noNam~espaceSchemaLocation~"E:lbering\berin~_v5_specslmain\QuerySpeclVSQuer yResultl7afinition.xsd" name="SampleQRI~" refl~uery="Dou'tCare">
- <edges3 sedge name,"axis0">
~edgeCh~oups>
<edgeClroup>
' <valueSete>
~valueSet refDatuItem="Q1"I>
</valueSets>
EedgeGmupsa <edge(lroup>
<valueSets>
svalueSat refDataltem="Q3"I5 refDatartem="G4"/>
re~ataItem="C36"h ~valueSet <IvalueSets>
E~S~
<edgeGroup>
~valueSets>
<valueSet <IvalueSets>
<ledgeC3roup>
~IedgeGrnupa~
<IedgeGroup>
<ledg~Groups>
~I~dge(3roup>
<edge(3roup>
<valueSets>
CvalueSet ratDataltem="G2"I>
~IvalueSets~
<edgeGroups>
<edgeC3roup>
~valueSeta>
refDataItenn="C~Snh reibataTtem=~~G7"I~, CvallleSet ~IvalueSets>
~edgeGmups>
~edgeGroup>
walueSets>
~valueSet ~IvalueSets~
<IedgeGroup~
~/edgeGraups5 dedgeGroup-</edgeQroups~
=ladg~Group~
~/cdgeQroupa>
~ledge~
<ledges~
~/quoryResultDafinitior~
[0064] The data source agnostic query result set API presents each group as a rowset that can be iterated using an IRSSetIterator object. Group headers and footers are presented as separate rowsets that can be accessed within conte~rt of a group's corresponding mwset. The name of the rocvset corresponding to a group is unique and is that of the dataltemRef of the valueSet that represents the level key of the group in the QRD 104, making it clear to the client application how the data in the result set corresponds to the layout specification.
[0065] Just as the data source agnostic query speei~cation maintains a consistent approach to specifying groups in both list and cross tab queries, the data source agnostic query result set API presents a single approach to iterating values is a list report as when iterating values along the edges ova cross tab report. In the data source agnostic query result set API, list reports are accessed via the same IRSSetIterator class that is used to navigate the edgos of cross tab result sets. At any grouping level (represented by a separate rowset), header and footer values mar be obtained at any time. All of the detail rows of a report are contained in the single, innermost rowset named "details".
[0066] In the data source agnostic query result set API, there are bo restrictions oa ktow xowsets are r~lated. The result set is instead restricted by what can be authored in the data source agnostic query specification.
~18-[0067] An edgeGroup 140, represents an. arbitrary shaped set of members (data values) on an edge 136. A flat list of non-nested edge groups in an edge specification can be used to represent the unioning of member seta. Each group can have one or more valueSeta 142 that repreaeut the group's members (based on a caption key and associated body attributes), an optional header and/or footer, a earl, and suppression. Each group can also have one or more nested groups.
[0068] An explorer-mode arose t$b edge can be specified by a set of nested edge groups.
ay nesting and unioning edge groups, a query framework client can specify a reporter-mode crasstab edge.
[0069] A grouped list report can be specified by a sat of nested edge groups with the inner moat edge group representing the details. This special group is not keyed on any level (i.e., it valueSet has not re~~ataltem attribute) and its body references the detail columns as level attributes.
[0070] Figure 16 shows in a diagram an example of a valueSet 142 type, in aceardance with an embadirnent of the data source agnostic query system 100. The valueSet 142, also kCnown as a memberSet, defines a collection of values or members to be returned for an edgeClroup 140. It represents a (nesting) level in an explorer style edge.
The ref.Dataltem attribute of this element represents the "key" associated wrath the level. The name attribute identifies the valueSet within the QRD 104, and ie unique within the scope of the QRD 104.
[00'11 ] The groupHeader 146 and groupFooter 148 child elements of the valueSet 142 element define a set of data values or members that represents a summary of the group members.
[0072] The groupBody I50 child element of the valueSet element defines the attributes to be returned for each member in the grnup.
[0073] The groupSort 1.52 child element of the value5et element defines the sort order for the group members within a context def red by the entire result set. A quezy author can define a sort using projocted and nonprojected items, The groupSort 152 aan refexeuee a data item form the associated query 102 even if the data item was not used in QltD 104.
For a detail group (i.e., a group with a valueSet 142 that has no data item reference and has a group body reference a list of items) the order of the groupSort 152 items dictator the order in which the details are sorted, [0074] In or~.e embo~himent, queryResultDefmitiorl 104 tennplates represent a choice of one of three basic templates that cover the most common report type (lists, cross tabs, charts). They are meant to provide authoring tools and the SDK with simple apecificatians for the most common use cases.
se Case Sim le is [0075] One basic data source agnostic query that may be specified is the Simple List.
The result set may contain sammary or detail database rows (autoSummary). In both cases, the rosult set structure is the sanne as dead by the QRD 104, 4txe grouping level may be specified. Any aggregate speelftcations are applicable only to the lowest grouping level in a summary query - since there's only one stooping level, control break aggregates at various grouping levels are not supported. The next example is of a Simple List report containing [Drder year], [Order tt~ethad], and [Quantity].
Order Order methodr~~ti ear ' 2000 E-mail 86 884 2000 Fax 34 462 2000 Mail 54,874 2000 Sales visit135,262 2001 ~ E-mail 12 350 ~
2001 Fax 41558 2001 Mail 43 672 2001 Sales visit191 2002 Irmail 139 2002 fax 39,824 2002 Mail 25.684 2002 Sales visit208 [0076] The QRD 104 for this example (using canonioal edge specification) is the following:
<queryResultDefinitions>
~queryResultDefinition name="rsl" re#Query~"queryl "~
Ced~~S~' zedge slam~="edge0"a tedgeGroupsy ~edgeGroupa walueSets~
~valueSet~
~groupBod~
dataltemRef retbataltem="Order Year"I?
refDataltem="Order Method"h ~dataItemR.ef adataItemRef refDataltem="Quantity"I~
~lgroupHody>
~IvalueSet~
'-IvalueSets~
~/edgeGmup~
~/edgeGroups~
Eledge>
~/edgosy 4queryResultl7eftnition7 ~IqueryResultDe~nitions~
GquerySet~
[0077] The QRD for this example (using the list template specification) is tlxe following:
~queryResultDefinitions~
~queryResultDefinition name"rs 1" rei~uery~"queryl "~
4esultTemplate>
~li~tResult~
<detail~
~dataItemRef retDataItem~'Order Yeat"/5 ~dataItetnRef re~ataItera="Oxder Method"h ~dataIte:mRef refDataItem~"Quantity"/a t/details~
~/listResult~
~IxesultTemplate~
~IqtteryResultDefiuition~
~/queryResultDe~nitions~
=/querySet;
G~uped List [0078] The next example is of a list report containing [order year], [Order method], and [Quantity], grouped by [Order year] and with a report level summary.
~21-4rde~r drdet ~rtethodanti eat 2000 B-mail ' 86,884 Fax 34,4b2 Mail 54 874 Sales visit135,262 2001 E-mail 122 Fax 41 558 Mail 43,672 Sales visit191,578 2001 399,158 2002 E-mail 139 Fax 39,$24 Mail 25.684 saleB ~isit2os ssx zoo2 413 sr,~ l,lza,sri [0079] The QRD 104 for this example (using canonical edgy specif'tcatiou) is the following;
,cqueryResultDefinition game="groupedList" rofQuery-"some query"
~edges~
<edge name="edge0"~
~edgeC,~oups~
~edgeCfroup~
<valueSets~
~alueSet xefDatartem="~rder'Year"a ~groupFootez' <dataTtexnRef refDataItem~"Quatityn~~
refDataItem="Ordor Method"h refl3ataltem="Quantity"~~
</groupFaotex~
~IvaIueSei?
~IvalueSets~
~edgeQroupa-~edgeQroup>
~valueSets>
walueSet~
~groupBody~
.data,ItennRef sdatalt~mRef ~/groupBody~
<IvalueSetd>
-ZZ-~/edgeGroup~
</edgeGroups~
~/edgeGnoup:
~/edgeCrroups>
~ledge~
~/edges~
~/queryltesultDe~nition~
[0080] Figure 8 shows in a disgrarn an example of the organization of the rowset8 in the result set 320, in accordance with ati embodiment of the data source agnostic duery system 100. Note that the [Order method] rowset 322 contains both the [Order method]
and [Quantity] data sets from the QRD 104. Also, the [4rder year] Footor mwset contains ono mw of data that represents the report summaxy.
Crosstab [0081 ] A cross tab result prosents s grid of summarized data values:
effectively, it is an intarsectian of two Grouped List results. A QItD 104 with two or more edges defines a cross tab result. Aggregates at various intersections are calculated automatically. This exampte is the sart~e as the previous example, except the data ie presented as a cross tab.
2000 E-mail 86 884 ~
Fax 34 462 Mail 54,874 visit 135 Sales 262 _ 311,482 2001 &mail 122,350 Fax A~ 1 Mail 43,672 Sales 191,578 visit 2002 E-mail 139 Fax 39 824 Mail a5.G84 Sales 208 visit 858 ~~rder 1,123,872 Year [0082] Tlxe QRD 104 for this example (using canonical edge specification) is the following:
<queryResultDe$nition name="groupedLiat" rc~uery="some query">
<edgea>
<edge name"edge0">
<edgeQroups>
<edgeC3roup>
walueSets>
<valueSet xefbataItem-"Order Year">
<valueSet retbataltem~"Order Year">
<gmuphooter>
<dataItemRef refDataItem-"Quatity"h refDataItem-"Order Method">
refDataltemm'Ordex Year"I>
~IgroupFooter>
</valu~eSet~
~valuaSets>
~dgeGroups>
<edgeGroup?
'walueSets>
<value5et <groupFoote,~
<dataItemRef </groupFootet>
<IvalueSet~
<IvalueSets>
-/edgeCiroup>
<IedgeGxoupa>
</edgeC~roup>
<IedgeGroups> .
<ledge>
<edge name="odgel''> .
<edget~roups?
<edgeCrrottp%
walueSets>
walueSet reibataTtexn="Quantity">ClvalueSel>
~JvslueSets>
</edgeClroup>
</edgeClroupsa <ledge=
</edges>
</ciueryResultDefinition~
[0083] The representation of the xowsets is identical to the previous example, except that the [Order method] rowset 322 aw longer contains the [Quantity] column - those values .are now contained within a cell rowset iterator.
C~O~etab 2 [0084] Figure 9 shows in a diagram ~n example of the organization of the row edge rowseta 340, in accordance with an embodiment of the data source agnostic query system 100. This example presents simple oross tab report with [Country] 342 nested within [Product] 344 along the row edge and [Quantity] along the column edge. Sub-totals are calculated for eaclx product.
ProductlUSA 1000 Canada500 France2000 Total 3500 Product2USA 3000 CanadaX000 FranceS00 Total 5500 [OOSS] 'The QRD 104 for this example (using canonical edgc specification) is the following' queryResultDefmition mane="groupedLiet" rei~uery="some query">
<edges>
<edge nanne="rows">
Eedge(~raups>
<edgeGroup~
~aluaSet~
walueSec roipataItem="Praduct"I~
<IvalueSets>
<edgeClroups>
~edgeCrroup~
walueSets>
<valueSet re~ataltem~"Country">
<groupl7oote~
<IgroupFootez' <IvalueSel~
</valueSets~
~IedgeC~roup>
/edgeGroups> .
</edgeGroup%
~/edgeC~roups>
tledg~
sedge name="columns">
~edgeC~roups>
~dgeGxoup>
<valueSets>
~alueSet re~ataItem--"Quantity"»IvalueSet?
~/valueSets~
~JedgeGroup~
CledgeC~?roups>
aledge>
~ledges>
~/queryResultDe~nition>
[0086] Notice the empty group footer fox the country valueSet. It indicates that the rowset corresponding to this footer should have zem columns, which is a valid case.
Consumers of this result set will use the existence of this empty rowsot to ~orm grouping breaks for example when. rendering such a result set.
[0087] The systems and methods according to th~a present invention may be implemented by any hardware, software or a cambinatia~n df hardware and software having the functions described above. The software code, either is its entirety or a pact thereof, may be stored in a computer readable memory. Furtb~et', a computer data signal representing the so#tvvare code that may be embedded in a carrier wave may he transmitted via a communication network. Such a computer readable memory and a computer data signal are also within the scope of the present invention, as well as the hardware, software sad the combination thereof.
[0088] While particular embodiments of the present invention have been shown acid described, changes and modiiy.Cations may be made to such embodiments without departing from the true scope of the invention.
Claims (18)
1, A data source agnostic query system for data source agnostic querying, the system comprising:
a query set component for defining data to be retrieved froth a data source.
a query set component for defining data to be retrieved froth a data source.
2. The data source agnostic query system as claimed in claim 4, wherein the query set component includes a set of queries, the queries having:
a source element for defining metadata upon which the data source agnostic query is based; and a selection element for identifying; metadata elements upon which the data source agnostic query is based.
a source element for defining metadata upon which the data source agnostic query is based; and a selection element for identifying; metadata elements upon which the data source agnostic query is based.
3. The data source agnostic query system as claimed in claim 2, wherein the queries further includes one or more of:
a name attribute for uniquely identifying the queries in the query set component;
a detail filter element for eliminating data values or members from a result set;
a summary filter element for eliminating data values or members from a result set;
a slicer element for slicing do data values or members from a result set;
a dimension information element for augmenting the selection element;
a memeberSet Structure that describes the set structure of the query; and a query hint element for transforming the data source agnostic query,
a name attribute for uniquely identifying the queries in the query set component;
a detail filter element for eliminating data values or members from a result set;
a summary filter element for eliminating data values or members from a result set;
a slicer element for slicing do data values or members from a result set;
a dimension information element for augmenting the selection element;
a memeberSet Structure that describes the set structure of the query; and a query hint element for transforming the data source agnostic query,
4. A data souse agnostic query system for data source agnostic querying, the system comprising:
a query result definition component for describing else structure of a result set for the data to be retrieved.
a query result definition component for describing else structure of a result set for the data to be retrieved.
5. The data source agnostic query system as claimed in claim 4, wherein the query result definition further includes one or more of;
edges for defining the placement of data items within a report; and master detail links for master-detail relationships between the queries in a query.
set.
edges for defining the placement of data items within a report; and master detail links for master-detail relationships between the queries in a query.
set.
6. The data source agnostic query system as claimed in claim 5, wherein the edge is a collection of edge groups, each edge group having:
a collection of value sets and;
a collection of nested edges groups.
a collection of value sets and;
a collection of nested edges groups.
7. The data source agnostic query system as claimed in claim 6, wherein a value set of the collection of value seta includes one or more of:
a group header for containing a collection of data item references;
a group footer for containing a collection of data item references;
a group body for containing a collection of data item references and a collection of property expressions;
a group sort for containing a collection of sort items, and a collection of property expressions.
a group header for containing a collection of data item references;
a group footer for containing a collection of data item references;
a group body for containing a collection of data item references and a collection of property expressions;
a group sort for containing a collection of sort items, and a collection of property expressions.
8. A data source agnostic query system for data source agnostic querying, the system comprising:
a query set component for defining data; and a query result definition component for describing the structure of a result set.
a query set component for defining data; and a query result definition component for describing the structure of a result set.
9. A method of data source agnostic querying, the method comprising the step of:
decomposing a data source agnostic query into sub-queries, the step of decomposing including the steps of:
identifying the underlying data source specific planners that are involved in the preparation of the data source agnostic query; and preparing the sub-queries corresponding to each planner.
decomposing a data source agnostic query into sub-queries, the step of decomposing including the steps of:
identifying the underlying data source specific planners that are involved in the preparation of the data source agnostic query; and preparing the sub-queries corresponding to each planner.
10. The method claimed in claim 9 wherein the sub-queries are grouped into data source query types.
11. The method claimed in claim 9 wherein the decomposition has intimate knowledge of the quality of ssrvice of all underlying source.
12. The method claimed in claim 9 wherein the decomposition uses the QRD and the data item expressions for optimization.
13. The method as claimed in claim 9, further comprising the step of:
translating the gesture into a data source agnostic query.
translating the gesture into a data source agnostic query.
14. The method as claimed in claim 9, further comprising the step of:
sending each sub-query to a data source query engine based upon its data source query type.
sending each sub-query to a data source query engine based upon its data source query type.
15. The method as claimed in claim 14, further comprising the step of:
reassembling of the results from the data source query engines into a single result set and the decomposed plan.
reassembling of the results from the data source query engines into a single result set and the decomposed plan.
16. The method as claimed in claim 9, further comprising the steps of:
receiving a data set result from the data source query engine; and compiling the data set result into a report.
receiving a data set result from the data source query engine; and compiling the data set result into a report.
17. A memory containing computer executable instructions that can be read and executed by a computer for caring out a method of data source agnostic querying, the method comprising the step of:
decomposing a data source agnostic query into sub-queries, the step of decomposing including the steps of:
identifying the underlying data source specific planners that are involved in the preparation of the data source agnostic query; and preparing the sub-queries corresponding to each planner.
decomposing a data source agnostic query into sub-queries, the step of decomposing including the steps of:
identifying the underlying data source specific planners that are involved in the preparation of the data source agnostic query; and preparing the sub-queries corresponding to each planner.
18. A carrier carrying a propagated signal containing computer executable instructions that can be read and executed by a computer, the computer executable instructions being used to execute a method of data source agnostic querying, the method comprising the step of:
decomposing a data source agnostic query into sub-queries, the step of decomposing including the steps of:
identifying the underlying data source specific planners that are involved in the preparation of the data source agnostic query; and preparing the sub-queries corresponding to each planner.
decomposing a data source agnostic query into sub-queries, the step of decomposing including the steps of:
identifying the underlying data source specific planners that are involved in the preparation of the data source agnostic query; and preparing the sub-queries corresponding to each planner.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA002553964A CA2553964A1 (en) | 2005-09-13 | 2006-06-23 | System and method of data source agonistic querying |
Applications Claiming Priority (3)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CA2,519,001 | 2005-09-13 | ||
| CA002519001A CA2519001A1 (en) | 2005-09-13 | 2005-09-13 | System and method of data agnostic business intelligence query |
| CA002553964A CA2553964A1 (en) | 2005-09-13 | 2006-06-23 | System and method of data source agonistic querying |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CA2553964A1 true CA2553964A1 (en) | 2007-03-13 |
Family
ID=37872100
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CA002553964A Abandoned CA2553964A1 (en) | 2005-09-13 | 2006-06-23 | System and method of data source agonistic querying |
Country Status (1)
| Country | Link |
|---|---|
| CA (1) | CA2553964A1 (en) |
-
2006
- 2006-06-23 CA CA002553964A patent/CA2553964A1/en not_active Abandoned
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US20070061318A1 (en) | System and method of data source agnostic querying | |
| Jarke et al. | Fundamentals of data warehouses | |
| Vassiliadis et al. | ARKTOS: towards the modeling, design, control and execution of ETL processes | |
| US8037108B1 (en) | Conversion of relational databases into triplestores | |
| US6636845B2 (en) | Generating one or more XML documents from a single SQL query | |
| US7668860B2 (en) | Apparatus and method for constructing and using a semantic abstraction for querying hierarchical data | |
| US20040064456A1 (en) | Methods for data warehousing based on heterogenous databases | |
| US20060294087A1 (en) | System and method for processing and decomposition of a multidimensional query against a relational data source | |
| US6748393B1 (en) | Transparent updates to partitioned views in a federated database system | |
| KR100701104B1 (en) | Method of generating database schema to provide integrated view of dispersed information and integrating system of information | |
| US20060161525A1 (en) | Method and system for supporting structured aggregation operations on semi-structured data | |
| US7747651B2 (en) | Metadata modelling for reporting | |
| Stonebraker et al. | What goes around comes around | |
| US7555786B2 (en) | Method for providing security mechanisms for data warehousing and analysis | |
| US20080313153A1 (en) | Apparatus and method for abstracting data processing logic in a report | |
| CA2553964A1 (en) | System and method of data source agonistic querying | |
| Maatuk | Migrating relational databases into object-based and XML databases | |
| Broekstra et al. | The state of the art on representation and query languages for semistructured data | |
| Gertz et al. | Integrating scientific data through external, concept-based annotations | |
| KR100666942B1 (en) | Method for Handling XML Data Using Relational Database Management System | |
| Bimonte et al. | A visual query language for spatial data warehouses | |
| Jean et al. | OntoQL: An Alternative to Semantic Web Query Languages | |
| KR100367092B1 (en) | System and method for processing queries of object-oriented DBMS tightly coupled with information retrieval facility | |
| Awadallah et al. | A Review on Utilising XML as the Mediated Layer for Data Integration | |
| Chytil | Adaptation of Relational Database Schema |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| EEER | Examination request | ||
| FZDE | Dead |