Understanding PeopleSoft Open Query

The PeopleSoft Open Query ODBC driver and API enable third-party reporting tools or applications to access PeopleSoft data in conformance with the PeopleSoft Query access architecture (the embedded SQL access intelligence provided by PeopleSoft Query). The PeopleSoft Query access architecture provides the following key features:

  • Multiple levels of security

    • Query authorization

    • Operator security

    • Operator profile

    • Record level

    • Access group

    • Row level

    • Security record

  • Standard query data access

    • Access to all supported PeopleSoft databases.

    • Ability to run stored PeopleSoft queries.

    • Automatic use of table sets.

    • Effective-dated output.

    • Translate values.

    • International translations.

Image: PeopleSoft Open Query architecture

This diagram illustrates the components involved in PeopleSoft Open Query architecture. The blocks containing boldface type represent PeopleSoft Open Query components.

PeopleSoft Open Query architecture

This table describes PeopleSoft Open Query components:

Product

Audience

Purpose

Open Query ODBC

Third-party application vendors and implementation partners

Open, documented API providing access to PeopleSoft Query as a data source. Primary means of achieving programmatic interface to PeopleSoft data.

Open Query API

PeopleTools development

PeopleSoft proprietary. Exposes Query definition and runtime functions for use within PeopleTools. API used by ODBC driver and online analytical processing (OLAP).

The PeopleSoft Open Query ODBC driver is the external means for extracting data from a PeopleSoft database.

Using ODBC as our external API has several advantages:

  • Third-party tools do not need special information about PeopleSoft data.

  • Most third-party reporting and query tools already support ODBC.

  • Custom integration with PeopleTools is no longer required.

  • Proprietary interface drivers (namely P2SPS.DLL) are eliminated.

  • ODBC is supported by other application development tools, such as Visual Basic and PowerBuilder.

  • Connectivity to PeopleSoft data is maintained by the PeopleSoft security architecture.

  • Extension or modification of driver behavior is allowed by way of the ODBC standard.

The ODBC driver does not have any intrinsic knowledge of PeopleSoft data structures. Only the interface components exposed by way of PeopleSoft Open Query API are required to extract query data for a particular purpose.

This layer provides only the data modification and conversion code necessary to comply with the ODBC software development kit (SDK) standards. None of the PeopleTools structures are exposed at this level.

The ODBC driver presents a stable interface to external applications. The PeopleSoft Open Query API is constantly under construction and therefore is not useful in this capacity. Because modifying behavior between releases presents a challenge when dealing with third-party applications, we created an intermediate layer between the ODBC driver and the underlying PeopleTools manager code. This wrapper code reduces the number of method calls to an acceptable level and allows the PeopleTools development team some leeway when new functionality is required or when the underlying code base is modified.

The PeopleSoft Open Query API enables the external driver (ODBC) and PeopleTools applications to focus solely on providing PeopleSoft data in the formats described by those products. It also abstracts the underlying connective architecture from the upper levels of the interface.

Because the architecture of this API is fluid, it is available only to PeopleTools development.

Note: You cannot use PeopleSoft Open Query APIs to write direct SQL, such as SELECT, UPDATE, INSERT, DELETE, and so on, against the database. You can use them only to execute stored procedures.

The PeopleSoft Open Query ODBC driver enables third-party reporting tools or ODBC-enabled development applications to access a PeopleSoft database. The driver enforces user, table, and row-level security through internal PeopleTools APIs. A user can leverage PeopleSoft Query to easily create platform-independent queries that run against any supported PeopleSoft database platform.

The PeopleSoft Open Query API is a valuable link to all external data access mechanisms, including data over the internet.