bea.com | products | dev2dev | support | askBEA

Download Docs | Site Map | Glossary
Search

Building Queries and Data Views

Overview and Key Concepts

This section introduces key concepts you need to understand to plan, design, build and test queries using BEA Liquid Data for WebLogicTM. The notion of a stored query versus an ad hoc query is introduced. Also covered is using a hand-coded query versus a Builder-generated query. Since we want to encourage most users to leverage the Data View Builder to generate queries for Liquid Data, many of the considerations and concepts introduced here assume use of the Builder, including a GUI overview for the Builder. However, key concepts that are relevant to all types of query-smiths are introduced here as well such as data sources, stored queries, data views, XQuery, the anatomy of a query (joins, unions, aggregates and so on), and the process of building and testing a query.

The following topics are covered.

- W3C XQuery, XML, and Liquid Data

- How Do Liquid Data and Data View Builder Use XQuery?

- The Role of XML in Creating Global Business Solutions

- Which Versions of XML Schema Does Liquid Data Support?

- How Can I Learn More About the XQuery Language?

- What is the Data View Builder?

- Why Do I Need it?

- How Does it Work?

- Key Concepts of Query Building

- Query Plans

- Stored Queries

- Ad Hoc Queries

- Different Kinds of Data Sources

- Source and Target Schemas

- Anatomy of a Query: Joins, Unions, Aggregates, and Functions

- Next Steps

W3C XQuery, XML, and Liquid Data

XQuery is a World Wide Web consortium (W3C) standard XML-based Query language. Whereas SQL is a well-known query language for querying relational databases, XQuery is a query language for querying XML-based information. Developers who are familiar with SQL will find XQuery to be a natural next step. Liquid Data uses XQuery to query multiple types of data sources—the structure of which are represented as XML by the query engine.

XML is evolving from a W3C specification for a markup language to an entire family of specifications and technologies. The W3C has chartered working groups focused on creating, among other things, a more approachable XML language for database developers, including the published specifications for schemas and a query language. The evolving language is XQuery, which gives XML developers a structured solution for accessing XML data. The W3C Query Working Group used a formal approach by defining a data model and formal query algebra as the basis for XQuery. XQuery uses a simple type system and supports query optimization. It is statically typed, which supports compile-time type checking. It includes familiar database operations such as projection, iteration, selection, and join.

How Do Liquid Data and Data View Builder Use XQuery?

BEA Liquid Data uses a stable components of the W3C XQuery specification to take advantage of XML query power as the standards continue to evolve. By using XQuery, Liquid Data can model XML schemas for various types of data sources. These schemas are surfaced as design tools in the Data View Builder, which generates queries in XQuery in the background. The Liquid Data server can process ad hoc or Builder-generated queries in XQuery syntax and use them to query all different kinds of data sources (relational databases, Web services, application views, data views, and so on) and return results in XML.

Once you have configured Liquid Data access to the data sources you want to use (see the Liquid Data Administration Guide), you can query the data by sending queries written in XQuery to the data sources via Liquid Data.

The Role of XML in Creating Global Business Solutions

By supporting XML technology, creating specifications, fostering software development, the W3C hopes to use XML as a forum for information exchange, business development, and global communication.

XML is being used on the Internet is to create a simple way to exchange data among diverse clients. Proprietary data definitions and access methods inhibit data exchange. They lock you into using only those products and programs that can send, receive, and process your data.

You could compare the universality of XML to a global monetary exchange standard, or to an international spoken language that removes barriers to global commerce and communication. Data View Builder and the Liquid Data query generation engine adhere to these standards to facilitate cross-platform and cross-repository access to critical business information.

You can learn more about XML on the W3C Web site at http://www.w3.org/XML/.

Which Versions of XML Schema Does Liquid Data Support?

XML schemas are used in Liquid Data to describe the hierarchical structure of the various data sets with which you are working. Liquid Data recognizes XML Schema versions 2001, 2000/08, and 2000/10.

You can learn more about XML schemas on the W3C Web site at http://www.w3.org/XML/Schema and http://www.w3.org/2001/12/xmlbp/xml-schema-wg-charter.html.

For an introduction on working with schemas in the Data View Builder see Source and Target Schemas.

How Can I Learn More About the XQuery Language?

You can learn more about the standard on the W3C Web site at http://www.w3.org/TR/xquery/.

For a comprehensive list of relevant XQuery references, see the topic XQuery Links in "Liquid Data Concepts" in the Liquid Data Product Overview.

What is the Data View Builder?

The Data View Builder is a GUI-based tool for designing and generating XML-based queries (in W3C XQuery syntax). You can then run the queries against heterogeneous data sources to retrieve information. The Builder provides a pictorial, drag-and-drop mapping approach to query design and construction. Using the Data View Builder frees you from having to focus on the intricacies of query languages so that you can give full attention to information design, the conceptual synthesis of information coming from multiple sources, and the content and shape of the information you want in the query result or target. In this way, you can to directly access distributed, heterogeneous data sources as "integrated logical views."

Why Do I Need it?

The Data View Builder lets you create queries using an intuitive, drag-and-drop mapping strategy that frees you from having to grapple with the details of query languages. The XML schema representations and mappings of source and target data are packaged and saved as a project. Users can retrieve the full picture of the query complete with source schemas and target mappings thereby getting access to the query in the context of a design model. Queries can also be stored as data views that can be configured as data sources themselves in Liquid Data and re-used to create nested subqueries; "views on views" of information.

How Does it Work?

In the Data View Builder, you drag and drop elements and attributes among XML schema representations of data sources to create source conditions (joins, unions, and so on). The default source condition is a join (that uses an equality function); you can also use the more complex functions provided in the Builder toolbar. You also map source to target schema elements and attributes to shape the structure of the query result. As you build up the query with drag-and-drop modeling, the Builder is constructing the query in the background in valid, well-formed XQuery syntax. An "Optimize" view is also available for adding optimization hints to a query to improve performance. When you are ready to run a query, you can switch to the Builder "Test" view, see the generated XQuery for the current query, run it and see the query result in XML.

The Data View Builder provides hierarchical tree XML schema representations of all data sources configured in Liquid Data, regardless of the data source type. Once a data source has been configured in Liquid Data using the WebLogic Server Administration Console (see the Liquid Data Administration Guide), it shows up in the Builder toolbar where you can access its XML schema representation. The structure of the data stored in relational databases, Web services, application views, data views, and XML files themselves are all represented as XML schemas in the Data View Builder. By creating this coherent picture of heterogeneous data sources as XML schemas, Data View Builder makes it easy for you to browse and map data elements and attributes among different types of data sources.

Key Concepts of Query Building

The following terms and concepts introduced here:

- Query Plans

- Stored Queries

- Ad Hoc Queries

- Different Kinds of Data Sources

- Source and Target Schemas

- Anatomy of a Query: Joins, Unions, Aggregates, and Functions

Query Plans

A query plan is a compiled query. Before a query is run, Liquid Data compiles the XQuery into an optimized query plan. At runtime, Liquid Data executes the query plan against physical data sources and returns the query results.

Stored Queries

A stored query is a query that has been saved to the Liquid Data repository in the stored_queries folder. Queries must be saved with a .xq extension to be recognized as stored queries in Liquid Data. There is a performance benefit to using a stored query because caching is available as follows:

- The query plan for a stored query is always cached in memory. (For an ad-hoc query, the query plan is not cached.)

- The query result for a stored query can be cached.

Caching of query results for stored queries is configurable through the Administration Console (see Configuring the Query Results Cache in the Liquid Data Administration Guide). Using this feature, you can specify whether or not to cache query results for stored queries.

Ad Hoc Queries

An ad hoc query is a query that has not been stored in the Liquid Data repository as a stored query but rather is passed to the Liquid Data server on the fly. Liquid Data does not cache the query plan or the result for an ad hoc query the way it can for a stored query, so an ad hoc query cannot leverage the performance benefit of caching.

Different Kinds of Data Sources

Information can reside in various kinds of data sources in an enterprise or across business entities. The most obvious of these is the relational database, which we typically think of as a data storage and retrieval resource. The reality is that the development of global business and distributed systems has generated information in many other types of data sources as well. Information resides not only in various kinds of databases, but also in packaged enterprise information system (EIS) applications such as PeopleSoft or Siebel, and in emerging net-based technologies like Web services and XML documents. Liquid Data and Data View Builder give you the ability to query and get views into data that resides in all these kinds of information sources.

Relational Databases

All types of businesses and other organizations use an RDBMS (relational database management system) to store information. Relational refers to the way the database organizes information. All information in a relational database appears in logical tables with rows and columns. Instead of a series of static records with one or more data fields that can be redundant from one file to another, information is directly accessible using queries. You can create logical table records that contain just the data you need by constructing a query.

Some databases track information, such as reservations or overnight package delivery information. Other databases store information for perpetual access, such as the IRS or the Library of Congress. Others change dynamically, depending on frequent updates, additions, and deletions, such as a newspaper subscriber database. Databases can reside on large mainframes, web servers, or powerful desktop systems.

Imagine how many times your employee number can appear in static records that describe your company 401K investment, employment, and health insurance records. In an isolated case, this represents three separate files with much of the same information repeated in each instance where there is a record of information. A comprehensive RDBMS would store your employee number, name, address, and other information once with pointers to other related pieces of information about you. Well designed queries could extract only information related to a specific task.

Tuples

Tuples are another way to refer to data in a database. In a relational database, a tuple is a complete set of information, or a logical record. For example, a personnel schema might contain records that has four fields: an employee number, a name field, an address field, and a phone number field. This tuple, or record, might occur many thousands of times in a very large company with many employees.

XML Files

Extensible Markup Language (XML) files are proving to be a convenient and portable format for storing many different kinds of information for document processing and information exchange. Liquid Data and Data View Builder supports use of XML files as data sources.

Web Services

A web service is a self-contained, platform-independent morsel of business logic, located somewhere on the Internet, that is accessible through standards-based Internet protocols like HTTP or SMTP. Web services facilitate application-to-application communication over the Internet or within and across enterprises. A familiar example of an externalized Web service is a weather portlet or stock quotes that you can integrate into your Web browser. You can use Web services to encapsulate information and operations. With the standards and wide-spread use of Web services for enterprise information exchange evolving, Web services are becoming important resources of global business information. Liquid Data and Data View Builder support the use of Web services as data sources.

Application Views

Enterprise Information Systems (EIS) and custom applications store information that you might need to aggregate for a complete view of data. You can query and retrieve subsets of relevant information from applications such as SAP, Siebel, PeopleSoft, Oracle Financial and so on and treat these views as application view data sources in your data integration solution.

Data Views—Using the Result of a Query as a Data Source

A data view is a special type of data source in which the result of a query is used as a data source. The query result will change as your data changes. In this way, you can build on the queries you design to create "views on data views" for an up-to-date picture of continually changing information.

Source and Target Schemas

XML schemas are used in Liquid Data to describe the hierarchical structure of the various data sets with which you are working. The Data View Builder uses XML schema representations as follows:

- Source Schemas—XML schemas that describe the structure of the source data.

- Target Schema—An XML schema that describes the structure of the target data; that is, the structure of the query result

Liquid Data requires data sources to be configured on the server and they must have an associated schema. A default relational database schema can be obtained automatically. XML files, Views, or Web Services must be configured on the server before you try to use them in queries.

Note: For information on which versions of XML schema are supported, see Which Versions of XML Schema Does Liquid Data Support?.

Understanding Source Schemas

A source schema is the XML schema representation of the structure of the data in a data source.

The Data View Builder provides graphical representation of source schemas in a tree structure format. Nodes contain elements, attributes, and sub-elements and attributes that you can expand or collapse. If you are building a query that queries more than one data source, you will use multiple source schemas (one for each data source).

Understanding Target Schemas

A target schema describes the structure of a query result that will be produced when the query runs. The Data View Builder provides graphical representation of target schemas in a tree structure format. Nodes represent elements, attributes, and sub-elements and attributes that you can expand or collapse. Only one target schema per query is allowed.

A target schema is usually just that part of the hierarchy that you want to appear in the result. For example, you could choose the same schema for both the source and target data structure. The query result will show only those data elements that are actually mapped to nodes in the target schema.

Anatomy of a Query: Joins, Unions, Aggregates, and Functions

A query can be thought of as a way of filtering through large amounts of data or information to extract only the specifics relevant to a particular instance. A query is made up of one or more types of conditions that accomplish the filtering task or "ask the question." The most commonly used techniques for establishing the source conditions are:

- Joins

- Unions

- Aggregates

- Functions

Joins

A join operation merges data from two sources based on a common field. A query with a join operation combines information in two data source schemas when there is a match on a common field. The common field is a link between the two schemas.

Using this common field, you can gather other information that is unique to each source into a single target schema or view of the data.

For example, you could specify first and last names of all customers in two data sources Broadband and Wireless, but limit the output (query result described by target schema) to the subset of those customers with matching customer IDs in both source schemas.

There are two types of join operations based on equality of matching fields (or columns):

- Inner Joins—An inner join is the default join type. It combines data from two data sources only if values in the joined fields match. The matching fields must have compatible data types or contain similar data.

- Outer Joins—An outer join behaves like an inner join, but it includes data that does not match the join condition. You can create two different types of outer joins by specifying which unmatched data elements to include in the query results

- Left Outer Join—In a left outer join, all selected nodes from the leftmost schema in the join clause appear. Unmatched data nodes in the rightmost schema in the join clause do not appear in the result.

- Right Outer Join—In a right outer join, all rows in the rightmost schema in the join clause appear. Unmatched data nodes in the leftmost schema in the join clause do not appear in the result.

Unions

Union operations enable you to combine data from multiple sources into a single set of results described by the structure of the target schema. Even though the content of the source schemas can be the same, or different, you can use a union query to combine selected data nodes in the source schemas into a complete view of the data. For example, we could construct a query that reports all customer orders from multiple sources into a single result.

Aggregates

Aggregate query results summarize information. You can use aggregate operations to extract summary information in different ways, such as creating totals, counts, and averages.

Functions

Liquid Data provides a functions library with built-in functions can by used by any Liquid Data client, and also supports configuration and use of user-defined custom functions related to specific business needs.

All source conditions are established using some type of function. The default function for a simple join is the standard "equals" function (abbreviated eq). If you drag and drop one data source element onto another of the same name you have created a simple join using the equals function with two parameters (the two data source elements) which gets expressed as value1 eq value2 in the Builder-generated XQuery.

You can also choose from the functions library to explicitly specify a function to use.

The W3C specification for XQuery supports a discrete list of functions—Liquid Data supports a subset of those functions. For more information on W3C standard functions, see the XQuery 1.0 and XPath 2.0 Functions and Operators specification.

Query Parameters

The parameters to a function can be elements in a source schema, or they can be query parameters that you define as generic placeholders for a variable value. You can specify the variable value at query run time. For example, a query parameter could be defined as lastname, which is a placeholder for a real last name, such as Smith, that you identify when the query runs. Each time you run the query you can change the value of lastname, which gives you a lot of flexibility without creating a separate query for every unique last name you might be interested in. By supplying a new value each time, you could run queries on customers named Smith, or Jones, or any other last name of interest without redesigning the query.

Next Steps

- If you have not already done so, we suggest working through the steps in Getting Started, which takes you through the basic tasks of configuring some data sources and using the Data View Builder to design a query using the Order Query example from our Avitek Sample.

- To learn how to start the Data View Builder and understand the GUI tools and views, see Starting the Builder and Touring the GUI.

- To learn more about planning and designing queries and using the Data View Builder to build them, see Designing Queries.

- For information on query optimization and performance, see Optimizing Queries.

- For examples of building different types of queries using advanced functions and tools, see Query Cookbook.

- For details on creating queries by using custom functions, see Using Custom Functions in Invoking Queries Programmatically.