Working with Metadata and Query Builder

This chapter discusses how to:

Manage the HCM metadata repository.
Use Query Builder.

Managing the HCM Metadata Repository

To manage the HCM metadata repository, use the Base Catalog component (HCMD_BASE_CATALOG), the Object Catalog component (HCMD_OBJ_CATALOG), the Export component (HCMD_EXPORT_OBJS), and the Import component (HCMD_IMPORT_OBJS).

This section provides an overview of the HCM metadata repository and discusses how to:

Define base classes.
Define object classes.
Enter class attribute details.
Enter class relationship details.
Export metadata.
Import metadata.

Understanding the HCM Metadata Repository

Metadata is data about the data in your system. It defines every object, object attribute, and relationship between objects. Metadata enables you to define and manipulate the records in your PeopleSoft HRMS database as objects.

The HCM metadata object model consists of the following two elements:

PeopleTools-level metadata

Provides information about fields, field labels, and database records and views.
HCM-level metadata

Establishes relationships between objects at the object level, defines objects's services, and alternative labels.

HCM metadata object definitions are registered in the metadata repository as classes and these classes are grouped in the following two catalogs according to general behavior, processing, and attribute needs:

Base catalog.

Object classes:
- Define the general behavior and attributes for a class.
- Group objects for better processing and querying.
- Provide a single layer of inheritance for derived classes.
Object catalog.

Object classes:
- Can be the child object of a parent base class, inheriting all of the attributes of the base class.
  
  Note. Object catalogs do not need to have a parent class object.
  
  Note. Any metadata class definitions that are not obviously a parent class object, should be defined in the object catalog.
- Are defined with unique attributes in addition to any inherited attributes.

For example, the base class object, Person Base, is the parent to the derived object class, Person.

Common Elements Used in this Section

Basic Information	Define the class's basic characteristics by entering information into the following fields: Class Name Display Label Object Owner ID Active Flag
Class Use	Define how the class object is used by: Selecting one of the following options: Production: a standard production object. Staging: a specialized staging object used to load data into the HCM system. Reporting: a specialized reporting object. System: an HCM system object not usually visible to users. Configuration: a specialized object used in HCM system configuration. Source View (object class only) Attribute Map (object class only) Key Map (object class only) Temporary (object class only) Selecting Use in UI? to display the class in a dynamic user interface. Selecting Use in Queries? to display the class in Query Builder. The system selects the System Data check box if the class object is delivered as HCM system data.
Persistence Mapping	Enter the name of the PeopleTools record to which this class maps. The PeopleTools record is the sole source for the class object. Each class definition maps to one PeopleTools record
Class Attributes (Record Field)	Review or enter the class attributes. Click the View Attribute Details button to review details or enter a details for a new class attribute on the Class Attribute Details page.
	Click to populate the Class Attributes grid with all the fields associated with the PeopleTools record you selected in the persistence mapping Record Name field. The button does not overwrite valid fields. Note. PeopleSoft recommends that you keep the attributes in sync with the PeopleTools record fields by using this button after you make a modification to the PeopleTools record.
Relationships	Define the relationships this class definition has with other class definitions. For example the Person class that maps to the PeopleTools Person record could be have relationships with the: Name class. People in the system could have one or more names. Address class. People in the system could have one or more address. Job class. People in the system could have zero or more jobs. Click the View Relationship Details button to review details or enter a details for a new class attribute on the Class Relationship Details page.
Class Extensions	Class extensions enable you to define any additional attributes that you can access through APIs (you cannot use these properties with Query Builder). For example, you could create a Java type class extension called MyJava and with a value of the Java file name that programmers can access as required. Define the class extensions by entering values into the following fields: Type Name Value

Pages Used to Manage the HCM Metadata Repository

Page Name	Object Name	Navigation	Usage
Base Class Definition	HCMD_BASE_PG	Set Up HRMS, Product Related, Compensation, HCM Metadata Repository, Base Catalog, Base Class Definition	Set up and review definitions for base classes.
Object Class Definition	HCMD_OBJS_PG	Set Up HRMS, Product Related, Compensation, HCM Metadata Repository, Object Catalog, Object Class Definition	Set up and review definitions for derived classes.
Class Attribute Details	HCMD_OATT_SPG	Set Up HRMS, Product Related, Compensation, HCM Metadata Repository, Base Catalog, Base Class Definition Click the View Attribute Details button on the Base Class Definition page Set Up HRMS, Product Related, Compensation, HCM Metadata Repository, Object Catalog, Object Class Definition Click the View Attribute Details button on the Object Class Definition page	Enter or review class attribute details
CLass Relationship Details	HCMD_OREL_SPG	Set Up HRMS, Product Related, Compensation, HCM Metadata Repository, Base Catalog, Base Class Definition Click the View Relationship Details button on the Base Class Definition page Set Up HRMS, Product Related, Compensation, HCM Metadata Repository, Object Catalog, Object Class Definition Click the View Relationship Details button on the Object Class Definition page	Enter or review class relationship details.
Export Metadata Definitions	HCMD_EXPORT_OBJS	Set Up HRMS, Product Related, Compensation, HCM Metadata Repository, Export, Export Metadata Definitions	Export metadata definitions from the catalog to XML.
Import Metadata Defintions	HCMD_IMPORT_OBJS	Set Up HRMS, Product Related, Compensation, HCM Metadata Repository, Import, Import Metadata Definitions	Import metadata definitions.

Defining Base Classes

Access the Base Class Definition page.

Defining Object Classes

Access the Object Class Definition page.

Class Attributes

Parent Class

If this class object is built upon a parent class object, select it here. The class object will inherit all of the parent's attributes.

Leave this field blank if the object class has no parent.

Component Interface Name

Select the PeopleTools component interface used to save data for the class. You can use the component interface in Query Builder to perform bulk updates. For example, you can create a query for all the people at Location A and then update the location information on the Job Data component for that group of people to Location B.

Search Component

Select the search component to search for instances for this derived class object. The system uses the Query Builder search component as a default if you do not enter a value here.

Edit Component

When you enter a component in this group box for a class, the system enables you to link to the component or page from a hyperlink in the query results when you query the class in Query Builder. This enables you to make updates to the component while you are reviewing the query results

Enter the component's Menu Name, Menu Bar Name, Item Name, and Page Name.

Entering Class Attribute Details

Access the Class Attribute Details page.

Attribute Name

Enter the attribute name. It can be different from the corresponding field.

Alias

Enter an alias for the attribute.

Field Name

Each attribute must have a corresponding field in the PeopleTools record.

Configuration Controls

Allow Modifications?	Select if the users can modify the attribute in dynamic user interfaces.
Allow Deletion?	Select if the users can delete the attribute in dynamic user interfaces.

Use Options

Use in Queries?	Select to display the class in Query Builder.
Use in UI?	Select to display the attribute in a dynamic user interface

Key Settings

Business key?

Select if the business logic indicates that this attribute (field) is a key. You do not need to define the attribute as a key field at the record level.

Class Attribute Extensions

Class attribute extensions enable you to define any additional attributes that you can access through APIs (you cannot use these properties with Query Builder). For example, you could create a Java type class attribute extension called MyJava and with a value of the Java file name that programmers can access as required.

Define the class extensions by entering values into the following fields:

Type
Name
Value

Entering Class Relationship Details

Access the CLass Relationship Details page.

Relationship Name

Enter the name of the relationship.

Use in Queries?

Select to display the relationship in Query Builder.

Use in UI?

Select to display the relationship in a dynamic user interface.

Source Cardinality

Select the cardinality setting of the source class object from the following options:

zero to 1 (0.. 1)

zero to many (0.. n)

one to one (1.. 1)

one to many (1.. n)

Target Cardinality

Select the cardinality setting of the target class object from the following options:

zero to 1 (0.. 1)

zero to many (0.. n)

one to one (1.. 1)

one to many (1.. n)

Relationship Target

Select the relationship's target class object.

Relationship is Bidirectional?

Select to make the reverse relationship (with the source and target object classes reversed) automatically available.

Bypass Indirection

Select to bypass automatic setID indirection for those relationships that include setID indirection fields.

Query Build automatically adds the required SetID logic as required. If you select this option, you must define the logic in the relationship attribute mapping. For example, SETID_Location maps directly to the SetID field on the Locations table (LOCATION_TABLE) to improve performance.

Relationship Attribute Mapping

Enter the names of the attributes that you are mapping. You can map any number of attributes and the attributes do not have to have the same name to be mapped.

Exporting Metadata Definitions

Access the Export Metadata Definitions page.

Click to export all the metadata definitions in the catalog to an XML file. The system will display the Export Metadata Definitions page where you enter the export path and file name to be created.

Criteria Filter

To limit the classes, enter filter criteria in the Class ID, Class Type, Object Owner ID, or Active Flag fields.

Note. You can select more than one class ID in this field by separating the values with commas.

	Click to apply the criteria you entered. The system populates the Search Results grid with the class objects that meet the criteria.
	Click to clear the search criteria fields.

Search Results

The system populates this grid with the class objects that meet your search criteria and automatically selects all of them. Clear those the check box next to those class objects that you do not want to export.

Click to export the selected class objects to an XML file.

Export Metadata Definitions page

The system displays the Export Metadata Definitions page when you click the Export All Class Definitions button or Export Selected Definitions button. Enter the full path to the application server and the file name. If you do not specify a file name, the process will create the file in the default directory.

Importing Metadata Definitions

Access the Import Metadata Definitions page.

Enter the file path and name to load the data from the XML file. The system will display a list of classes that exist in the exported file and an icon indicates if the class already exists. Select which classes should be imported and if the system should overwrite existing class definitions.

Building SQL Queries with Query Builder

To build SQL queries with Query Builder, use the Query Builder component (EQRY_SAMP_HTML_CMP) and the Pre-defined Queries component (EQRY_VIEWER_CMP).

This section provides an overview of Query Builder and discusses how to:

Specify, save, and load result attributes.
Define query criteria.
Save query definitions.
View query results.
Reuse queries predefined in Query Builder.

Understanding Query Builder

Query Builder is a flexible tool that is used to query class objects that are defined in the PeopleSoft HCM metadata repository.

Query Builder lets you:

Create queries of unlimited length and complexity without the need to know the underlying database model.

Query builder automatically generates the most efficient SQL based on the metadata class definitions, generates all SQL joins automatically based on the relationships defined in metadata, and includes SetID and effective-dated logic.
Select any number of available output fields to display results from both the underlying class and the class relationships.
Modify current queries to include different output and criteria.
Run previously defined queries.
Navigate to objects that are returned as query results.
Group the output with counts, sums, and averages and include minimums and maximums for the grouped output.

Query Builder automatically generates the appropriate SQL for aggregations.
Preview the Structured Query Language (SQL) query generated by Query Builder.

Whereas PeopleSoft Query uses records and fields in queries, Query Builder queries objects and attributes. This approach has two benefits:

You are more likely to recognize an object name than record and field names, and hence find it easier to query objects that are in the system.
Underlying record and field definitions of system objects can change without affecting existing query definitions, resulting in more robust queries.

Pages Used to Build Queries

Page Name	Object Name	Navigation	Usage
Result Attributes	EQRY_OUTPUT_PG	Set Up HRMS, Product Related, Compensation, HCM Query Builder, Query Builder, Result Attributes	Select the object attributes that you want to use as result attributes in the query results. Select as many attributes as needed.
Save Attributes	EQRY_OUT_S_PG	Set Up HRMS, Product Related, Compensation, HCM Query Builder, Query Builder, Result Attributes Click the Save Attributes button on the Result Attributes page.	Save the result attributes of the query. Enter a name and description for the query. The query is available to all users who have access to Query Builder. If you don't want the query generally available, select Private Query.
Load Attributes	EQRY_OUT_L_PG	Set Up HRMS, Product Related, Compensation, HCM Query Builder, Query Builder, Result Attributes Click the Load Attributes button on the Result Attributes page.	Load or append predefined result attributes selection for use in the current query. The attributes that are available depend on the target object that is selected when Query Builder is invoked.
Criteria	EQRY_ISCRIPT	Set Up HRMS, Product Related, Compensation, HCM Query Builder, Query Builder, Criteria	Specify the attributes for which you want to query, and define expressions by using operators and values.
Criteria Attribute Tree	EQRY_ATT_TREE_PG	Set Up HRMS, Product Related, Compensation, HCM Query Builder, Query Builder, Criteria Click the Select Attribute button next to the Attribute Name field on the Criteria page.	Using the object selection tree, navigate to the attribute that you want to use as query criteria and select it.
Select Value	EQRY_VBUILD_PG	Set Up HRMS, Product Related, Compensation, HCM Query Builder, Query Builder, Criteria Click the Value lookup button on the Criteria page.	Select the attribute value to use as query criteria
Save Criteria	EQRY_PROP_PG	Set Up HRMS, Product Related, Compensation, HCM Query Builder, Query Builder, Criteria Click the Save Criteria button on the Criteria page.	Save the current criteria selection by name for use in later queries. The system saves the query criteria selection for the target object that is selected when Query Builder is invoked.
Load Criteria	EQRY_LOAD_PG	Set Up HRMS, Product Related, Compensation, HCM Query Builder, Query Builder, Criteria Click the Load Criteria button on the Criteria page.	Load an existing criteria selection.
Results	EQRY_RESULT_PG	Set Up HRMS, Product Related, Compensation, HCM Query Builder, Query Builder, Results	Review the results of your query.
Save/Load Query	EQRY_COMBO_SAVE_PG	Set Up HRMS, Product Related, Compensation, HCM Query Builder, Query Builder, Save/Load Query	.Save the current query or load a predefined query.
Pre-defined Queries	EQRY_VIEWER_PG	Set Up HRMS, Product Related, Compensation, HCM Query Builder, Pre-defined Queries	Run a query that you've predefined on the Query Builder component. The page looks like the Query Builder - Results page.

Selecting Query Attributes

Access the Result Attributes page.

Use the Result Attributes page to select the object attributes that you want to search against. You can select any combination of the defined attributes for the object.

At any point while defining result attributes, you can run the query and view the results. You can then return to the Result Attributes page to modify the query result attributes, go to the Criteria page to refine the query selection, or save the definitions.

Class ID to Query

Displays the class ID you selected for querying on the search page.

Select Attributes

The system displays folders for the target class ID object and any other objects that have a relationship (as entered on the Object Class Definition page) with the selected class ID. In this example, you can see that both the Person Core Objects and Person Data Model objects have a relationship with the Email Addresses object.

Click the folder to view the attributes of each object. Click an attribute to add it to the attribute grid.

Select Attributes	You can rearrange the order of the selected attributes to determine the order in which they appear on the Results page. You can also determine the sort order of the data that is in each results column.
Hide	Hides the attribute tree.
More	When Query Builder first opens, the Select Attributes tree displays the system objects that are closely related to the target object through direct association. These relationships are defined in HCM metadata and represent the objects that you most often use in queries. If you can't find the objects that you want, click the More link to access other objects .
Expand All	Click to reveal the contents of all attribute tree folders.
Collapse All	Click to collapse all attribute tree folders.
Attribute Grid	Select this check box to display the results columns as defined in the Attribute Grid.
Aggregations	Select this check box to display the results based on the groupings that are selected in the Aggregations grid.
Enable Outer Joins	Select this check box to enable the query to use outer joins to gather additional data in the query.
	Click to run the query. The system opens the Results page, showing the results of the query.
	(Optional) Click to remove the attributes that appear in the Selected Attributes list.
	(Optional) Click to set the attributes that you select as the default attributes that appear each time that you use the target object.
	(Optional) Click to save current attribute selections as a query.
	(Optional) Click to load predefined attribute selections.

Attribute Grid

Selected Attributes	The system populates the Selected Attributes list with the target object's list box items as they're defined in the Tools Record definition. You can add or delete attributes as necessary for your query.
and	Use the arrows to rearrange the attributes to determine the column display order in query results. For example, the attribute at the top of the Selected Attributes list appears as the left-most column in the query results.
Sort	(Optional) Indicate a sort order for the attribute in the column results: Blank, Asc(ascending), and Desc (descending). You can sort one or multiple columns. Specifying multiple sorts causes column results to sort from left to right. Column results that are sorted have a link that appears in the column header in the query results.
Group	Select this check box to group the results by the attribute.
Count , Sum , Avg (average), Min (minimum), and Max (maximum)	Select these options to have the system count the different groups. For attributes that are related to transaction amounts, the system can also sum, average, and indicate the minimum or maximum amounts.

Aggregations

Use the check boxes in this grid to indicate how you want the results to appear on the Results page. For example, you can tell the system to group the results by order number, and within each group, sum the order amounts as well as show the minimum and maximum amounts.

Loading Result Attributes

Access the Load Attributes page.

Use the Load Attributes page to add saved results columns to the query. The page displays all of the attribute sets that are saved by all users as well as the attribute sets that you save and mark as private.

Load	Replaces the result attributes in the query.
Append	Adds the result attributes onto the query.
User ID	The user who creates and saves the result attributes.
Private Query	Indicates the private queries.

Selecting Query Criteria

Access the Criteria page.

After you load or define the query result attributes, you can optionally add criteria to filter the query results on the Criteria page. You can only select attributes from the objects that are selected on the Results Attribute page. Specify as many criteria rows as needed, or save a criteria selection for later use to use as a predefined query selection in the current query.

By using indentation in expressions, you can create queries that are as complex as you need. Query Builder intelligently parenthesizes the expressions to ensure that the query is evaluated correctly.

Attribute Name

Select the attributes from the objects connected to the class ID you're querying.

Click the Select an Attribute button to view a list of all defined attributes for the object on the Select Criteria page. Select an attribute to return to the Criteria page.

Operator

Select an operator to define the condition for the criteria.

Value

Enter a value for the selected attribute to use as query criteria.

Selecting a Value

Access the Select Value page to select the values that you want to use to filter the query. The fields on the page differ depending on the operator that you select on the Criteria page.

If you use the between or not between operators, you select a From and To value.

If you used the like, not like, equal to, not equal, greater than, less than or equal to, less than, or greater than or equal to, you select a value for the system to compare the result to.

If you use the in or not in operator, you can select one or more check boxes for the values within which you want the system to return the results.

At any point while defining the criteria, you can click the View Results button to run the query, and then return to the Criteria or Results Columns page to modify or (optionally) save the definitions.

Working with Criteria

	Click to view the results of the query on the Results page.
	Click to remove all current attribute names and value selections and start over.
	(Optional) Click to save current attribute name and value selections. After selecting the criteria, you can save the query definition for future use. On the Save Criteria page, enter a name and description for the query and indicate whether or not you want it to be a private query.
	(Optional) Click to load predefined attribute name and value selection. This page works just like the Load Columns page, and displays all of the query criteria sets that are saved by all users as well as the sets that you save and mark as private.

Viewing Query Results

Access the Results page.

Use the Results page to view the results of the query. You can click any of the links that are in the results columns to go to the application page where the attribute is defined.

Select to view the query SQL, download the query results into Excel, add new criteria, or rerun the query.

Note. When you select to download the current query results to a Microsoft Excel spreadsheet, only the results currently displayed on the page are downloaded to the spreadsheet. To download all query results, click the View All link, and then click the Download to Excel link.

Aggregations grid

Shows the results sorted and totaled based on the check boxes that you select on the Results Columns page. This grid is only visible if you select the Aggregations check box on the Results Columns page.

Saving and Loading the Query

Access the Save/Load Query page.

While defining a query, you can save the result attributes and criteria as separate definitions. This allows you the flexibility to mix and match various result attributes and criteria. You can also save a query definition, which is a combination of an output (result attributes) selection and a criteria selection.

Query Name	Enter a name under which to save the query. When you save a Query Builder query, both the result attributes and the criteria selection are saved.
Private Query	Select to make the query private and available only to the user who created it.
	Click load to load a predefined query.

Running Predefined Queries

Access the Pre-defined Queries page.

Select a query by searching on output and criteria combination, description, original class ID, or a combination of these. Then click the query result that you want to run. Click the View Definition link to access the Query Builder component, where you can change the results columns, attributes, or criteria of the query and rerun the query.

Note. Predefined queries are also available by loading existing query definitions from within Query Builder.

Attribute Name	Enter the attribute name. It can be different from the corresponding field.
Alias	Enter an alias for the attribute.
Field Name	Each attribute must have a corresponding field in the PeopleTools record.

Relationship Name	Enter the name of the relationship.
Use in Queries?	Select to display the relationship in Query Builder.
Use in UI?	Select to display the relationship in a dynamic user interface.
Source Cardinality	Select the cardinality setting of the source class object from the following options: zero to 1 (0.. 1) zero to many (0.. n) one to one (1.. 1) one to many (1.. n)
Target Cardinality	Select the cardinality setting of the target class object from the following options: zero to 1 (0.. 1) zero to many (0.. n) one to one (1.. 1) one to many (1.. n)
Relationship Target	Select the relationship's target class object.
Relationship is Bidirectional?	Select to make the reverse relationship (with the source and target object classes reversed) automatically available.
Bypass Indirection	Select to bypass automatic setID indirection for those relationships that include setID indirection fields. Query Build automatically adds the required SetID logic as required. If you select this option, you must define the logic in the relationship attribute mapping. For example, SETID_Location maps directly to the SetID field on the Locations table (LOCATION_TABLE) to improve performance.