Building Queries and Data Views

Using Data Views

Data views play a central role in the Liquid Data enterprise development model.

• The Enterprise and the Data View
• Understanding Data Views
• Creating a Data View
• Creating a Parameterized Data View
• Data View Query Samples

 

---

The Enterprise and the Data View

In Liquid Data, data views are central to solving the data integration problem one time (as opposed to once per query) and providing a basis for simpler application development work on top of that integrated view. In this model:

• A data architect with an intimate knowledge of the relationship of the available diverse data sources develops a set of data views based on the needs of various parts of the enterprise.

For example, a view of an employee developed for an enterprise might include employee salary and address information from one data source; information about their health insurance from another data source; information from their company assets (computer, phone, etc.) might be included from a third data source.

• Liquid Data is then used to create, refine, and validate each of the data views through queries built up through the Data View Builder.
• Once validated, a reusable representation of each data view is developed through the Data View Builder and Liquid Data node of the WebLogic Administration Console as a new data view.
• Then the Liquid Data data view can be used throughout the enterprise as a virtual data source for queries. For example, a query for a new payroll division application might select salary information from this view.

In this model a data view provides an appropriate architectural view of corporate data that is available for specialized queries and sharable throughout the enterprise.

 

---

Understanding Data Views

In Liquid Data a stored query and a target XML schema comprise a data view.

Figure 8-1 Components of a Data View

 

To create a data view from a query:

• You first create a query and save it.
• Then you configure a data view data source description for the query in the Liquid Data node of the WebLogic Administration Console.

To create a virtual data source in this way, you must first create a query and save it to the Liquid Data server repository, then configure a data view data source description for the query in the WebLogic Administration Console. It is recommended that you create the query and save it to the repository using the Data View Builder, but it is also possible to use hand-coded queries in generally the same way.

The following sections explain what a data view is and how to use a data view data source with the assumption that you are using the Data View Builder to construct the query. Also included is a clarification of the relationship between a query and a a data view.

Functionally, a data view extends the power of a stored query through its association with a target schema that describes the data. This combination allows a data view to be identified in the Data View Builder as a data source for additional queries.

The following sections describe in detail how to create Liquid Data data views and use such views as data sources. Also included is a discussion of the relationship between a query and a data view.

A Data View Use Case

eWorld Co, a company that through multiple mergers and acquisitions has 50,000 employees, also has multiple payroll systems. Using Liquid Data, information in each of these systems can be accessed. The company also has two relational databases from separate vendors for tracking incentive bonuses. Human Resources very frequently gets questions about when such bonus payments will show up in affected employee's paychecks.

• To enable HR to get answers to employees quickly and economically, an Information Technology data architect creates a query using Liquid Data that can access relevant information from the multiple payroll systems and the company's incentive bonus databases.
• Once satisfied that the query works, the IT architect creates a data view and makes it available to an HR data specialist. This specialist can then use Liquid Data to quickly get answers to inquiries from individual employees about their bonus payments.

The benefits of this approach are significant:

• A single integrated view can be created for use throughout an enterprise. Access to sensitive information is controlled and consistency is maintained.
• HR can quickly get the information it needs without having to either staff up with its own data architect or get in the queue for expensive and low-availability IT custom programming services.
• Since data views are typically created by information architects, more time can be spent designing and testing the generalized query.

Simple and Parameterized Data Views

The difference between a simple and a parameterized data view is that a parameterized data view has one or more input parameters. Specifically views that centrally contain functional sources such as an application view, web service, custom function, or stored procedure often require an input parameter.

Using Data Views as Data Sources

From the Data View Builder, you access a data view as you would any other data source. There is no limit to the number of data views that can be used in creating a new query, although currently there may be performance implications to nesting data views. A data view can reference on another data view.

 

---

Creating a Data View

The following sections explain the steps needed to turn a query into a data view data source:

• Creating and Saving the Query to the Liquid Data Repository
• Configuring a Data View Data Source Description
• Adding a Data View as a Data Source

Creating and Saving the Query to the Liquid Data Repository

Follow these steps to create and save a query to the Liquid Data repository:

Construct the query in the Design view as described in Building Queries.
Test the query in the Test Query view as described in Running, Saving, and Deploying Queries.
Save the query to the Liquid Data repository as a stored query as described in Steps to Save a Query to the Repository in Running, Saving, and Deploying Queries.

Note: When you are creating a data view, it is important that the query and its target schema be in conformance. In the current release this means that all required elements in a target schema must be mapped if the query is to be turned into a view. See Source and Target Schemas and subsequent discussions for details.

Alternatively you can load queries and target schemas into the Liquid Data repository directly using the Liquid Data node of the WebLogic Administration Console. See Uploading Files to the Server Repository for details.

Configuring a Data View Data Source Description

In the WebLogic Administration Console, configure a data view for the query as described in Configuring Access to Data Views in the Liquid Data Administration Guide. Then follow these steps:

In the Liquid Data node of the WebLogic Administration Console click the Repository tab.
Double-click on the Stored Queries folder.
Find the stored query you want to use and inspect the Data Source Configuration column.
If Create Data View is available, click on that link to create you data view. (If a data view already exists based on the stored query, you will see Data View Created.)

This links you into the Data View configuration tab, automatically copies the stored query to the data_views folder for you, and assigns an xv extension to the name you select for your data view.

See Managing the Liquid Data Server Repository in the Liquid Data Administration Guide for additional details.

Adding a Data View as a Data Source

After you have created the data view, reconnect to the Liquid Data server using the File -> Connect menu command. Your new data view should appear under Data Views when the Sources tab in Design mode is selected (see Figure 8-4).

 

---

Creating a Parameterized Data View

You can use the following simple example to create a stored query and then turn it into a parameterized data view that retrieves customer order information based on a unique customer ID.

Note: To follow along with the creation of this example data view, you should have the Liquid Data sample server installed and running and be familiar with the sample. If not, please see the Liquid Data Getting Started guide.

Open the Data View Builder, drag the relational database source pb-bb onto the Liquid Data desktop. Set your target schema to customerOrders.xsd. Map elements to your target schema as shown in Figure 8-2.

Figure 8-2 Creating a Parameterized Query in the Data View Builder




 
From the Liquid Data Toolbox tab, choose Query Parameter. Create a single query parameter, CUST_ID and using the pulldown Type menu. Assign it a type string of xs:string.
Drag cust_id to the CUSTOMER_ID field of the CUSTOMER table in the PB_BB data source (also shown in Figure 8-2).
Drag CUSTOMER_ID in the Customer table to CUSTOMER_ID in the Customer Order table to create a join.
In Test mode supply CUSTOMER_1 as a value for CUST_ID and run the query.

Note: Values are case-sensitive.

The Data View Builder will display an XML report containing information on the orders made by this particular customer.

Using the File -> Save Query menu command in the Data View Builder, save your query under the name param_dv to the Repository folder. It will automatically be placed in the ld_repository\stored_query folder and the extension .xq appended.

 
Now you can use the Liquid Data node of the WebLogic Administration Console to create your data view from your newly saved query.
Start the WebLogic Administration Console.
Click the Liquid Data node.
Select the Repository tab.
Go to stored_queries.
Choose the query param-dv.xq
Select the Data View Data Source option.
Enter information as shown in Figure 8-3.
Click Create.

Your new data view should appear in the Liquid Data node of the WebLogic Administration Console list of available data views.

Figure 8-3 Creating the Data View in the Administration Console




 

See "Creating Data Views from Stored Queries" in the Liquid Data Administration Guide for information on how to generate data views from stored queries.

Return to the Data View Builder. Select File -> Connect. When you click on Data Views, your newly created data view should appear.

Figure 8-4 Data View on the Liquid Data Desktop




 

 
Drag the data view onto the Liquid Data desktop as you would any other data source (Figure 8-4).
Add a valid target schema. In this case, you can use the File menu Set Selected Source as Target Schema command. (The generated schema is shown in Figure 8-5.)

You may see a message asking if it is OK to close the existing target schema since that will remove all its mappings in the Data View Builder. Click Yes.

Figure 8-5 Setting Input and Associating Columns With Target Schema




 


 

Using the Data View Builder toolbox create a string constant called CUSTOMER_3 and drag it into the data view input CUST_ID (see Figure 8-5).

You could have provided an input parameter from a built-in XQuery function, custom function, an input from a web service, or another source.

Map all the elements in your target schema.
Test, then run your new query.

Figure 8-6 XQuery and Generated XML Report




 

 

---

Data View Query Samples

Two additional Data View Query samples are installed with the Liquid Data samples. These samples show how to create a data view, configure it as a data source, and then use that data source in other data views.

Instructions for running the samples are provided in readme files located at:

• <LDHome>/samples/buildQuery/view/readme.htm
• <LDHome>/samples/buildQuery/parameter_view/readme.htm

Also see the Liquid Data Samples page for more information on other available query samples.