Understanding Connected Query

Connected Query enables you to create a single XML file based on a set of queries with parent-child relationships.

A connected query is a hierarchical object built with existing PeopleSoft queries. A parent query can nest n levels of child queries and any child query can have m sibling queries within a hierarchy. No artificial limits exist for n and m, but typical use involves a single parent-child relationship or a few levels of nesting. Fields in a child query are mapped to related fields in the immediate parent query. A connected query returns a hierarchical data set in which data returned by child queries is filtered by the results of its immediate parent query.

Connected queries are used to analyze data, supply to other systems with PeopleSoft data using Web Services, and create BI Publisher reports that use connected query as a Data Source.

See Creating Data Sources, Registering Data Sources, Running BI Publisher PeopleSoft Query Reports.

Connected queries are significantly useful:

• To simplify task for a business user.

  User create a set of linked simple queries to collect data from multiple tables instead of creating a complicated query with multiple equal, outer joins, and grouping conditions.

• As a tool to combine the main report and its subreports in the same template for using with BI Publisher reports.

• When outputs need to be in structured XML.

  A simple query (including a query with multiple joins) creates a tabular plain layout. To have a hierarchical output, you need to use multiple grouping conditions that involve complicated logic and is not always straight forward. Connected Query processing creates a hierarchical output where a single row of data from parent query results in a set of rows in a child query. This data is processed row by row and produces structured data. It has similarity with SQR and Application Engine nested loops processing.

  Instead of using Application Engine or SQR to collect data for file processing by BI Publisher, use Connected Query as the data collection tool because no conditional logic involves in this process. However, use SQR for processes with high volume of data or when high performance is required.

You can quickly create a single parent-child relationship using the Connected Query Quick Start component (CQ_WIZARD), and create complex connected queries using the Connected Query Manager (PSCONQRS).

Image: Example of Connected Query's structure

This example shows the structure that Connected Query uses to collect data of each employee, who directly and indirectly reports to specific departments.

In this example:

• The first query (QE_DEPARTMENTS_ROLLUP_PERS) selects a list of departments that report to a specific department defined by user in the first query prompt.

• The second query (QE_EMPLOYEE_FROM_PERSDATA_TREE) selects lists of employee based on the departments returned from the parent query.

  Query QE_EMPLOYEE displays detailed information of every employee that is selected by query QE_EMPLOYEE_FROM_PERSDATA_TREE.

• The last query (QE_DEPARTMENT_DATA) displays detailed information of each department that participated in the report.

You can run a connected query using one of the following options:

• PeopleCode-based Preview XML

  Connected Query Report Developers use the Preview XML button to run a connected query using the application server when they build a new or edit an existing connected query. This option does not require saving the connected query to the database.

  See Using Connected Query Viewer.

• C++ based CQRuntime

  End users use the CQRuntime option to run a connected query using the Process Scheduler server. This method requires saving the connected query object to the database before running it.

  See Using Connected Query Scheduler.

• C++ based CQRuntime with the OrderBy Runtime option

  If you use C++ based CQRuntime with the OrderBy Runtime option, parent and child queries are dynamically altered to include the ORDER BY clause, every SQL statement runs once, and the connected query dramatically cuts the number of SQL statements that are generated at runtime. As a result, processing is faster and consumes fewer server resources.

  Note: Report developers can set the OrderBy processing option for all members or some members of the specific connected query.

  See the Using the OrderBy Processing Option section in Using Connected Query Manager.

The following table lists the delivered Connected Query permission lists and roles.