Building Queries and Data Views

Data Sources

The best known and most pervasive traditional data source is the relational database. An RDBMS can be thought of as a tabular data storage and retrieval resource.

The reality is that the development of global business and distributed systems has generated information in many other forms as well such as in packaged enterprise information system (EIS) applications (PeopleSoft, Siebel, etc.), 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 create views into data that resides in all these types of information sources.

In this chapter the following LD-supported data sources are briefly described:

Data source descriptions available in the connected Liquid Data Server are easily accessed from the Data View Builder.

Figure 3-1 Liquid Data Sample Data Sources As Displayed in the Data View Builder

. Liquid Data Sample Data Sources As Displayed in the Data View Builder

Relational Databases

All types of businesses and other organizations use a RDBMS (relational database management system) to store information. Relational refers to the way the database maintains information — 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.

Note: When Data View Builder inspects the metadata for a relational database, if the schema contains any columns that start with numeric values, the Data View Builder adds an underscore character (_) to the beginning of the element name that represents the column. For example, if you have a column in the database named 123_COLUMN, the element corresponding to that column in the Data View Builder is labeled _123_COLUMN.

Also, the following characters from any catalog, schema, table, or column names are replaced with an underscore character:

: < > \ / $ , <tab>, <newline>, and <spaces>

For example, a table named <customer><$table> can be referenced as customer___table (three underscore characters replace the three special characters).

Additionally, if you are hand-editing queries, the element or attribute names that refer to column names that start with a numeric value must begin with an underscore character (_) when used in XPath expressions.

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 unit 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. Web services are becoming important resources of global business information. Liquid Data and the 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 the results as application view data sources in your data integration solution.

Data Views

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 if your underlying 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. To learn more about Data Views see Using Data Views.

SQL Calls

Two types of SQL queries can appear as data sources under SQL Calls:

Stored Procedures. For relational databases that support stored procedures, you can create stored procedures in the RDBMS and expose them to Liquid Data. Liquid Data treats a stored procedure as a function which requires one or more inputs to produce zero or more outputs. A stored procedure allows database programmers to combine business logic with database queries, and they provide a powerful way to efficiently and securely produce information from relational databases. Also, stored procedures allow the database administrators to tune the queries run by the stored procedures, thus ensuring good performance and minimizing impact on database performance.
SQL Statements. These are stored SQL queries available for execution against relational data sources. These statements can be used as virtual tables as you would any other Liquid Data data source.

Delimited Files

Spreadsheets provide a useful means of storing and manipulating information, especially information which needs to be changed quickly. You can use spreadsheet data that has been saved in comma separated value (CSV) file format in Liquid Data queries and data views. Although the separator field is conventionally referred to as a comma you can set the separator to be any ASCII character using the Liquid Data node of the WebLogic Administration Console. See "Configuring Access to Delimited Files" in the Liquid Data Administration Guide.