Joining Data from Two Universes

Data-Specific Scenarios for DD002 Joining Data from the HR01 and PY01 Universes

Joining Data from the HR01 and PY01 Universes

Purpose:
This walkthrough demonstrates how to join data from queries on HR01 and PY01. We create a query that shows the last pay date for all temporary employees in a particular department. The goal is to prompt people to process termination paperwork for temps who have not been paid in many months.
After linking the two queries and creating a detail object for an unlinked dimension object, we create a variable that uses the Max function to calculate the last pay date for each employee in the report.
Start at the query panel for HR01.
Navigation / File, New
Query Panel
/ 1.  Open the Job Data, Job folders and move the following objects to Result Objects:
EmplID
Appt DeptID
Appt Dept Descr
Jobcode
Jobcode Descr
Reg Temp
Job Effdt
Empl Status
2.  Open the Person Data, Personal Data folders and move the following objects to Result Objects:
Name
Dt of Death
Note: Dt of Death is included to pick up employees who have died but have not been terminated in the system.
Query Panel (continued)
/ 3.  Using the Job Data, Job folder, create the first condition:
Object: Reg Temp
Operator: Equal to
Operand: T
4.  Create the second condition:
Object: Empl Status
Operator: In list
Operand: A,L,P
5.  Create the third condition:
Object: Appt DeptID
Operator: Equal to
Operand: Prompt (‘enter Appt
DeptID, e.g., 175500’)
Note: These conditions select all active temporary appointments in a particular department.
6.  Click Run.
Job Effective Date Options Dialog Box
/ 7.  Accept the default value (Current row as of today’s date).
8.  Click OK.
Enter or Select Values Dialog Box
/ 9.  Enter the appropriate Appt DeptID value.
10.  Click OK.
Report Window
/ 11.  Select Insert, Report.
12.  Click Insert Table .
13.  Click the place in the report where you would like the table to appear.
New Table Wizard
/ 14.  Turn on the Access new data in a different way radio button.
15.  Click Begin.
New Table Wizard (continued)
/ 16.  Verify that the Universe radio button is turned on.
17.  Click Next.
New Table Wizard (continued)
/ 18.  Select the PY01 universe.
19.  Click Finish.
Query Panel
/ 20.  Open the Gross Pay Register, Pay Ern Dist folders and select the following result objects:
Pay End Dt
Emplid
ApptDeptID
Jobcode
TL Hours1
Note: We bring in PY01 in order to get Pay End Dt. We will link on Emplid, ApptDeptID, and Jobcode.
TL Hours1 shows how many hours each person worked during the last pay cycle in which they were paid.
Query Panel (continued)
/ 21.  Create the following condition:
Object: Appt Deptid
Operand: Equal to
Query Panel (continued)
/ 22.  Under Operands, double-click Show list of prompts.
23.  Select the prompt you created for Appt DeptID from the List of Prompts dialog box.
24.  Click OK.
25.  Click Run.
Enter or Select Values Dialog Box
/ 26.  Verify that the same Appt DeptID is entered that you chose for the HR01 query.
27.  Click OK.
Report Window
/ 28.  Click View Data .
Note: We are now ready to link the dimension objects that are common to the two queries.
Data Manager Panel
/ 29.  Click the Definition tab.
30.  Click the plus sign (+) to the left of your first query.
31.  Select EmplID in the HR01 query.
32.  Click Link to…
Define link Between dimensions Dialog Box
/ 33.  Click Emplid in your second query.
34.  Click OK.
Note: Repeat steps 31-34 for the following dimension objects:
Appt DeptID
Jobcode
Data Manager Panel
/ 35.  Click OK.
Report Window
/ 36.  Select Data, Variables…
Note: We now create a detail object on an unlinked dimension object that we want to use in our report.
Variables Dialog Box
/ 37.  Click Add….
Note: We are creating a detail object on TL Hours1 in the PY01 query because that query has only two unlinked dimension objects. The HR01 query has many. When we select objects for the joined report, we will start with the HR01 query so that we can use all the unlinked dimension objects from that query.
Variable Editor Dialog Box
/ 38.  Type TL Hours detail in the Name edit field.
39.  Turn on the Detail radio button.
40.  Select Emplid from the Associated dimension drop-down list.
41.  Click the Formula tab.
Variable Editor Dialog Box (continued)
/ 42.  Double-click TL Hours1 under Variables.
43.  Click OK.
Variables Dialog Box
/ 44.  Click Add….
Note: For this particular report, we will now add a variable that calculates the last pay date for each employee in the report.
Variable Editor Dialog Box
/ 45.  Verify that the Definition tab is selected.
46.  Type last pay date in the Name edit field.
47.  Turn on the Measure radio button.
48.  Click the Formula tab.
Variable Editor Dialog Box (continued)
/ 49.  Double-click Max in the Aggregates folder.
50.  Double-click Pay End Dt in the Variables list.
51.  Click OK.
Variables Dialog Box
/ 52.  Click Close.
Report Window
/ 53.  Select Insert, Report.
Report Window (continued)
/ 54.  Verify that the Data tab is selected in Report Manager.
55.  Select the By Data Providers radio button to list variables by data provider.
56.  Use Ctrl + click to select the following variables:
Appt Dept Descr
Appt Deptid
Dt of Death
Empl Status
Job Effdt
Jobcode Descr
Jobcode
Name
Reg Temp
Last pay date
TL Hours detail
57.  Click the highlighted variables and drag them to a blank space in the report.
Report Window (continued)
/ 58.  Click any data cell in the last pay date column.
59.  Click the Insert Sort toolbar button.
60.  View results. Apply further formatting as needed.
Note: If there are empty cells in last pay date, this means the employee has not been paid since before the 7/01 conversion.
If there are zero values in the TL Hours detail column, this means the employee was not actually paid during the pay period. The entry represents a correction to the pay record.
Some employees have more than one value for last pay date. They may have multiple pay rates or jobs (associated with Empl_RCD#) or may be listed in multiple departments.

2/20/2003 14 DD002-00