Data Transformation Sample Query Readme
A copy of this readme is included with the
dataTransform sample in your Liquid Data installation at <WL_HOME>/liquiddata/samples/buildQuery/dataTransform/readme.htm.
(The default install home for Liquid Data is bea/weblogic700.)
This readme includes the following topics:
What this Query Demonstrates
This query demonstrates the use of functions
for:
- Simple data transformations like to_lower_case, to_upper_case
and concatenation
- Aggregation
For example, a wireless service provider
has Broadband information stored in one database server under the schema "BROADBAND"
and wireless information are managed by the same database server under the schema
"WIRELESS". Notice that the information does not have to be stored
in the same database server since the data sources are configured separately.The
customer representative needs to find out the average spending for customers
who use both Broadband and Wireless services. The application developer can
use Liquid Data to generate the query to get the information.
How to Run the Query
- Start the Liquid Data Samples server.
- Start the Data View Builder.
- In the Data View Builder, open the following project file:
<WL_HOME>/liquiddata/samples/buildQuery/dataTransform/data.qpr
- Click the Test tab. (This shows the generated query statement.)
- Click the "Run Query" button and view the XML result. If the query requires
query parameters, enter values for these before running the query.
If You Want to Re-create the Query . .
.
Configure the Data Sources in the Administration Console
- Configure the Wireless relational database data
source as follows:
- JDBC connection pool: MyWirelessPool (username=wireless,
password=wireless)
- JDBC data source: MyWirelessDS
- Liquid Data "Relational Database" data source: MyWireless-LD-DS
(Schema=WIRELESS)
- Configure the BroadBand relational database data
source as follows:
- JDBC connection pool: MyBroadBandPool (username=broadband,
password=broadband)
- JDBC data source: MyBBDS
- Liquid Data "Relational Database" data source: MyBroadBand-LD-DS
(Schema=WIRELESS)
Build the Query in the Data View Builder
- Create a new project.
- Open the Data Sources you just defined: MyWireless-LD-DS,
and MyBB-LD-DS
- Add dta.xsd from the repository as the target schema
- Drag the function lower_case() from the left-hand side
functions panel to the spreadsheet mapping area's source side, then drag the
BROADBAND->Customer->FIRST_NAME into the same box as described in Map
Source to Target, and drag the FIRST_NAME in the target schema into the right-hand
side box and having the two aligned.
- Drag the function upper_case() and the BROADBAND->Customer->LAST_NAME
into the source of the mapping area and the LAST_NAME in the target schema
into the target area as step 4.
- Drag the concat() function into the source side twice then drag the BROADBAND->Customer->LAST_NAME
into the source, click the "constant" button on the left to expend
the constant panel, in the first textfield, type ", " (do not
type the quote, only type the comma into the field), this will concatenate
the last name with a comma, drag the BROADBAND->Customer->FIRST_NAME
in the same source box, drag the FULL_NAME in the target schema into the
corresponding target box.
- Define the equal join condition on CUSTOMER_ID between source MyBB-LD-DS->db->Customer
and souce MyBB-LD-DS->db->Customer_Order as described Join the two
sources. Drag the function sum() into the worksapce, drag the TOTAL_ORDER_AMOUNT
in MyBB-LD-DS->db->Customer_Order into the parameter1 field of the
function and drag the parameter1 into the Scope of the corresponding box
in the condition spreadsheet area as the scope of the equal join condition.
- Repeat the equal join of the CUSTOMER_ID, this time, drag the function
count() into the workspace, drag the ORDER_ID into the parameter1 field
of the count function and using the parameter1 as the equal join condition.
- Define the equal join condition on CUSTOMER_ID between source MyBB-LD-DS->db->Customer
and souce MyWL-LD-DS->db->Customer_Order as described in step 7.
- Repeat the equal join of the CUSTOMER_ID of the MyMM-LD-DS->db->Customer
and MyWL-LD-DS->db->Customer_Order as described in step 8.
- Click on Mapping tab to get back to Mapping spreadsheet, drag div from
the function panel, drag + from the function panel drop into the same source
box, drag the output of the function sum in the workspace, drag the output
of the second sum into the same source box so that the box will have "((xf.decimal(anySimpleType?)+xf.decimal(anySimpleType?))div",
drag another sum into the box, then drag output of two counts into the box,
the final output should the same as the sample schema's. Drag the "average"
from the target schema into the target box.
- Notice in the sample, we added a query parameter called "customer_id"
to speed up the query, in all equal joins, hints were given, refer
to the manual about using the hint. If this query were run against different
database server, make sure to use cast, e.g. drag decimal into the box before
dragging the parameter in, so that all the calculation will be based on
the same data type.
- Run the query. (Click the Test tab and click Run Query
button. If the query requires query parameters, enter values for these before running the query.)
Reference
- You can view the wireless data base schema file at <WL_HOME>/liquiddata/samples/config/ld_samples/scripts/ddl/WIRELESSP.sql
- You can view the BroadBand XML schema at <WL_HOME>/liquiddata/samples/config/ld_samples/scripts/ddl/BROADBANDP.sql
- You can view the target schema at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/dta.xsd