EPM Query Presentation

1
State of ConnecticutCore-CT Project Query 8 hrs
Updated 4/14/2003
2
Getting Started
Welcome

• Welcome to the Query course!
• Instructor introduction
• Participant introduction
• Training facility orientation
• Ground rules
• Parking lot
• To participate in this course, you should have
  already completed the following training
• PeopleSoft Navigation Tutorial
• Financials Overview course

3
Objectives
Welcome

• In this training, you will learn to
• Use the basic concept of Query in Core-CT. We
  will specifically focus on the following topics
• Data Dictionary
• Core-CT Query Standards
• Running an Existing Query and Viewing Results
• Creating a New Query
• Maintaining a Query
• Defining Query Criteria
• Advanced Query Options
• Use Core-CT as a tool in supporting these topics
• Utilize Core-CT functionality to maximize your
  efficiency

4
Agenda
Welcome
Data Dictionary
Core-CT Query Standards
Using an Existing Query
Morning Break
Creating a Query
Lunch Break
5
Agenda
Maintaining a Query
Defining Query Criteria
Afternoon Break
Defining Prompts
Advanced Query Options
Query Wrap-Up

6
Training Tools
Welcome

• In this training session, we use tools created
  specifically for this course
• Presentation
• We use a PowerPoint presentation to guide us
  through our discussion of processes and key
  points related to using Core-CT
• Exercises
• We developed exercises that enable you to
  practice the skills you learn in each module
• Training Database
• The functionality in our database mirrors the
  Core-CT production environment closely, though
  changes may be made prior to go-live
• Significant changes made after training will be
  communicated to you
• Data is loaded into the Core-CT training database
  to help you learn how to use Core-CT, not to
  reflect a specific agencys transactions
• You can make mistakes and experiment without
  consequence

7
Training Tools
Welcome

• We also use a web-based help system during the
  class. This tool is available to you after
  go-live.
• Web Learning Assistant
• Web-based tool that provides help and detailed
  information for specific Core-CT job functions
• Contains concepts, procedures, flows, and a
  glossary
• The WLA is your primary reference tool (now and
  once Core-CT goes live)
• The WLA will evolve with Core-CT. As
  functionality changes, so will the content in the
  WLA

Lets walk-through the tool together now
WLA Browse Path Core-CT Information Access gt
Core-CT Query gt Query Basics gt Creating Simple
Queries
8
Training Tools
Welcome

• Job Aids are available to reinforce the
  information introduced in this course. Job Aids
  summarize key information and outline critical
  processes in Core-CT.
• General Job Aids
• Module Specific Job Aids
• Reporting Job Aids

9
Core-CT Navigation
Welcome

• Together we will practice the skills you learned
  in the Core-CT Navigation Tutorial
• Universal Navigation Header
• Activity Log in and use the Universal Navigation
  Header to sign off Core-CT
• Left-Hand Navigation Menu
• Activity Use the Left-Hand Navigation Menu to
  step through the various levels of the directory
  structure

10
Agenda
Welcome
Data Dictionary
Core-CT Query Standards
Using an Existing Query
Morning Break
Creating a Query
Lunch Break
11
Overview
Data Dictionary

•    The Data Dictionary
• Provides you with information about the fields
  that make up the Enterprise Performance
  Management (EPM) Reporting Tables
• The data dictionary is a spreadsheet containing
  all the tables that exist in Core-CT and the
  fields that make up those tables
• Enables you to look up and review descriptions of
  fields that you may be unfamiliar with in Core-CT
• This dictionary enables you to look up and review
  descriptions of fields that you may be unfamiliar
  with in Core-CT

12
Overview
Data Dictionary

• Separated for HRMS and Financials, the Data
  Dictionary contains the following information for
  each reporting table
• Field the data field name (e.g. DEPTID or
  VENDOR_ID)
• Label the name of the field as it appears on
  the page (Department or Vendor ID)
• Type distinguishes whether the field is made up
  of characters (char), numbers (nbr), or dates
• Length the length of the field
• Description a brief description of the field

13
Walk-through and Exercise
Data Dictionary

• Lets review the Data Dictionary
• First, we will access the Web Learning Assistant
• WLA Path Core-CT Information Access gt Core-CT
  Query gt Query Basics gt Using the FIN Data
  Dictionary
• On your own, review some of the Financials
  Reporting Tables
• Click the specific Reporting Table link to access
  the table
• Raise your hand if you need any assistance

14
Agenda
Welcome
Data Dictionary
Core-CT Query Standards
Using an Existing Query
Morning Break
Creating a Query
Lunch Break
15
Overview
Core-CT Query Standards

• Private vs. Public Queries
• Private Queries only the User ID that created
  the query can open, run, modify, or delete the
  query
• Public Queries any user with access to the
  records used by the query can open, run, modify,
  or delete the query

16
Overview
Core-CT Query Standards

• Naming Standards
• All query names must begin with the first 3
  letters of your Department ID
• Use letters and numbers (no symbols)
• Use underscores (_) instead of spaces

17
Agenda
Welcome
Data Dictionary
Core-CT Query Standards
Using an Existing Query
Morning Break
Creating a Query
Lunch Break
18
Overview
Using an Existing Query

• Query is an end user reporting tool which allows
  you to specify and extract the precise
  information that you want to retrieve from
  Core-CT and use it for many purposes
• You can use queries in the following ways
• To run queries as a separate process
• To display data
• To download query results to an Excel spreadsheet
  to further manipulate the data

19
Process Flow
Using an Existing Query

• Where does the Core-CT system fit into the
  process?

Access Query Manager
Define Selection Criteria?
Output to Excel or Page?
Download Query to Excel
No
No
Excel
Create Joins?
Run Query
Create Query
Yes
Yes
Page
Add Record Join
Define Selection Criteria
View Query on Page
20
Key Points
Using an Existing Query

• When using an existing query, please note the
  following
• The information you extract from Core-CT is data
  refreshed from the HRMS or Financials system as
  of the previous day. As a result, all of the
  information is only as current as the previous
  day.
• The Run option is useful if you want to run
  multiple queries or run the same query multiple
  times with different run time prompt values and
  compare the results of the queries
• You can configure your environment to open the
  Excel file in a separate window or save it as a
  file on your local hard drive by modifying the
  File Type Option settings in Excel
• When running a report to Excel, the data is
  static and will not automatically update when
  the query is run again. You will have to download
  the query to Excel again to see the updated
  version.

21
Walk-through and Exercise
Using an Existing Query

• Lets log-in to Core-CT
• First, we will walk through the process together
• Scenario Running a Query and Downloading
  Results to Excel
• WLA Path
• Core-CT Information Access gt Core-CT Query gt
  Query Basics gt Running Queries
• Core-CT Information Access gt Core-CT Query gt
  Query Basics gt Downloading a Query to Excel
• Core-CT Path Reporting Tools gt Query gt Query
  Manager
• On your own, complete Exercise 1 in your
  Exercise packet
• Remember to utilize the Web Learning Assistant
• Raise your hand if you need any assistance

22
Walk-through and Exercise
Using an Existing Query

• Lets log-in to Core-CT
• First, we will walk through the process together
• Scenario Running a Query and Downloading
  Results to Excel
• WLA Path
• Core-CT Information Access gt Core-CT Query gt
  Query Basics gt Using Query Viewer
• Core-CT Information Access gt Core-CT Query gt
  Query Basics gt Downloading a Query to Excel
• Core-CT Path Reporting Tools gt Query gt Query
  Viewer
• On your own, complete Exercise 2 in your
  Exercise packet
• Remember to utilize the Web Learning Assistant
• Raise your hand if you need any assistance

23
Agenda
Welcome
Data Dictionary
Core-CT Query Standards
Using an Existing Query
Morning Break
Creating a Query
Lunch Break
24
Agenda
Welcome
Data Dictionary
Core-CT Query Standards
Using an Existing Query
Morning Break
Creating a Query
Lunch Break
25
Overview
Creating a Query

• Creating your own queries enables you to select
  the tables that you want to execute the query
  from and tailor the fields so that only the data
  you want displays
• The steps involved in creating a simple query
  include
• Selecting records
• Adding fields
• Editing field properties
• Editing query properties
• A more complex query may also include
• Defining selection criteria
• Creating joins

26
Process Flow
Creating a Query

• Where does the Core-CT system fit into the
  process?

Access Query Manager
Define Selection Criteria?
Output to Excel or Page?
Download Query to Excel
No
No
Excel
Create Joins?
Run Query
Create Query
Yes
Yes
Page
Add Record Join
Define Selection Criteria
View Query on Page
27
Key Points
Creating a Query

• When creating a query, please note the following
• You can extract precise information using visual
  representations of your Core-CT database, without
  writing Structured Query Language (SQL)
  statements
• If you click the Save button without first
  completing the Properties page, a dialog box
  displays prompting you to type the Query Name,
  Description, and Owner fields
• Once you set up a query, you have many options to
  format, output and save the query. You can also
  set the query criteria
• Core-CT allows you to run and/or create ad-hoc
  queries through the web browser, and download
  results to Microsoft Excel

28
Walk-through and Exercise
Creating a Query

• Lets log-in to Core-CT
• First, we will walk through the process together
• Scenario Creating a New Query
• WLA Path Core-CT Information Access gt Core-CT
  Query gt Query Basics gt Creating Simple Queries
• Core-CT Path Reporting Tools gt Query gt Query
  Manager
• On your own, complete Exercise 3 in your
  Exercise packet
• Remember to utilize the Web Learning Assistant
• Raise your hand if you need any assistance

29
Troubleshooting
Creating a Query

• If the query does not produce the desired
  results, you can
• Access the Edit Field Ordering page and re-number
  the columns in the New Column group box
• Access the Edit Field Properties page and replace
  the Column number with next text

30
Walk-through and Exercise
Creating a Query

• Lets log-in to Core-CT
• First, we will walk through the process together
• Scenario Creating a Query and Changing Its
  Column Headings
• WLA Path Core-CT Information Access gt Core-CT
  Query gt Query Basics gt Creating Simple Queries
• Core-CT Path Reporting Tools gt Query gt Query
  Manager
• On your own, complete Exercise 4 and 5 in your
  Exercise packet
• Remember to utilize the Web Learning Assistant
• Raise your hand if you need any assistance

31
Agenda
Welcome
Data Dictionary
Core-CT Query Standards
Using an Existing Query
Morning Break
Creating a Query
Lunch Break
32
Agenda
Maintaining a Query
Defining Query Criteria
Afternoon Break
Defining Prompts
Advanced Query Options
Query Wrap-Up

33
Overview
Maintaining a Query

• Core-CT enables you to maintain and update
  existing queries by
• Deleting queries that are no longer needed
• Renaming queries
• Updating query information to fit your query
  needs

34
Process Flow
Maintaining a Query

• Where does the Core-CT system fit into the
  process?

Access Query Manager
Define Selection Criteria?
Output to Excel or Page?
Download Query to Excel
No
No
Excel
Create Joins?
Run Query
Create Query
Yes
Yes
Page
Add Record Join
Define Selection Criteria
View Query on Page
35
Walk-through and Exercise
Maintaining a Query

• Lets log-in to Core-CT
• First, we will walk through the process together
• Scenario Deleting a Query
• WLA Path Core-CT Information Access gt Core-CT
  Query gt Query Basics gt Deleting Queries
• Core-CT Path Reporting Tools gt Query gt Query
  Manager
• On your own, complete Exercise 6 in your
  Exercise packet
• Remember to utilize the Web Learning Assistant
• Raise your hand if you need any assistance

36
Walk-through and Exercise
Maintaining a Query

• Lets log-in to Core-CT
• First, we will walk through the process together
• Scenario Renaming a Query
• WLA Path Core-CT Information Access gt Core-CT
  Query gt Query Basics gt Renaming Queries
• Core-CT Path Reporting Tools gt Query gt Query
  Manager
• On your own, complete Exercise 7 in your
  Exercise packet
• Remember to utilize the Web Learning Assistant
• Raise your hand if you need any assistance

37
Walk-through and Exercise
Maintaining a Query

• Lets log-in to Core-CT
• First, we will walk through the process together
• Scenario Updating an Existing Query
• WLA Path Core-CT Information Access gt Core-CT
  Query gt Query Basics gt Creating Simple Queries
• Core-CT Path Reporting Tools gt Query gt Query
  Manager
• On your own, complete Exercise 8 and 9 in your
  Exercise packet
• Remember to utilize the Web Learning Assistant
• Raise your hand if you need any assistance

38
Agenda
Maintaining a Query
Defining Query Criteria
Afternoon Break
Defining Prompts
Advanced Query Options
Query Wrap-Up

39
Overview
Defining Query Criteria

• Core-CT enables you to add the following criteria
  to your query
• Selection Criteria
• Expressions
• Having Criteria
• Effective Date Criteria

40
Overview Selection Criteria
Defining Query Criteria

• Selection criteria enables you to selectively
  retrieve only the data you want
• Refines your query by specifying conditions that
  the retrieved data must meet
• Serves as a test that Core-CT applies to each row
  of data in the table that you are querying
• If a row passes, Core-CT retrieves it
• If a row does not pass, Core-CT does not retrieve
  it

41
Overview - Expressions
Defining Query Criteria

• Expressions are calculations that Core-CT
  performs as part of a query.
• You can use expressions
• To calculate a value that Core-CT does not
  provide by default
• As comparison values in selection criteria
• As columns in the query output

42
Overview Having Criteria
Defining Query Criteria

• A Having criteria is like a Where clause for rows
  of data that have been aggregated into a single
  row of output
• A Where clause evaluates individual rows before
  they are grouped together by an aggregate
  function
• A Having clause evaluates the data after the rows
  have been grouped together by an aggregate
  function

43
Overview Effective Date Criteria
Defining Query Criteria

• The Effective Date (EFFDT) field provides a
  historical perspective, allowing you to see how
  the data has changed over time
• When you add a row of data to an effective dated
  table, you specify the date on which the data
  becomes effective
• When you change a row of data, you specify a new
  effective date and Core-CT retains the previous
  version of the row as history
• Please note that you can only specify effective
  dated criteria for tables that contain the EFFDT
  field

44
(No Transcript)
45
Process Flow
Defining Query Criteria

• Where does the Core-CT system fit into the
  process?

Access Query Manager
Define Selection Criteria?
Output to Excel or Page?
Download Query to Excel
No
No
Excel
Create Joins?
Run Query
Create Query
Yes
Yes
Page
Add Record Join
Define Selection Criteria
View Query on Page
46
Process Flow
Defining Query Criteria

• Where does the Core-CT system fit into the
  process?

Define Selection Criteria?
Yes
Define Selection Criteria
Add Expressions
Add Prompts
Add Criteria
Add Having Criteria
47
Walk-through and Exercise
Defining Query Criteria

• Lets log-in to Core-CT
• First, we will walk through the process together
• Scenario Adding Criteria to a Query
• WLA Path Core-CT Information Access gt Core-CT
  Query gt Defining Selection Criteria gt Entering
  Selection Criteria
• Core-CT Path Reporting Tools gt Query gt Query
  Manager
• On your own, complete Exercise 10 and 11 in
  your Exercise packet
• Remember to utilize the Web Learning Assistant
• Raise your hand if you need any assistance

48
Agenda
Maintaining a Query
Defining Query Criteria
Afternoon Break
Defining Prompts
Advanced Query Options
Query Wrap-Up

49
Agenda
Maintaining a Query
Defining Query Criteria
Afternoon Break
Defining Prompts
Advanced Query Options
Query Wrap-Up

50
Overview
Defining Prompts

• When running a query, you can add a prompt to
  further refine a query
• A dialog box displays for you to specify the
  values
• The query uses the value as the comparison value
• Query results are returned based on the prompt
  value

51
Process Flow
Defining Prompts

• Where does the Core-CT system fit into the
  process?

Access Query Manager
Define Selection Criteria?
Output to Excel or Page?
Download Query to Excel
No
No
Excel
Create Joins?
Run Query
Create Query
Yes
Yes
Page
Add Record Join
Define Selection Criteria
View Query on Page
52
Process Flow
Defining Prompts

• Where does the Core-CT system fit into the
  process?

Define Selection Criteria?
Yes
Define Selection Criteria
Add Expressions
Add Prompts
Add Criteria
Add Having Criteria
53
Key Points
Defining Prompts

• When defining prompts, please note the following
• To ensure that the user selects only valid values
  for the field
• Select an associated prompt table (if applicable)
  from the Edit Prompt Properties page
• To allow the use of a wildcard () in order to
  return all values
• Select No Table Edit from the Edit Type drop
  down list on the Edit Prompt Properties page
• Select Like from the Condition Type drop down
  list on the Edit Criteria Properties page

54
Walk-through and Exercise
Defining Prompts

• Lets log-in to Core-CT
• First, we will walk through the process together
• Scenario Adding Prompts to a Query
• WLA Path Core-CT Information Access gt Core-CT
  Query gt Defining Selection Criteria gt Defining
  Prompts
• Core-CT Path Reporting Tools gt Query gt Query
  Manager
• On your own, complete Exercise 12 in your
  Exercise packet
• Remember to utilize the Web Learning Assistant
• Raise your hand if you need any assistance

55
Agenda
Maintaining a Query
Defining Query Criteria
Afternoon Break
Defining Prompts
Advanced Query Options
Query Wrap-Up

56
Overview - Aggregate Functions
Advanced Query Options

• An advanced query may include the use of
  aggregate functions
• A special type of operator that returns a single
  value based on multiple rows of data
• Core-CT Query collects related rows and displays
  a single row that summarizes their content

57
(No Transcript)
58
Overview - Aggregate Functions
Advanced Query Options

• You can apply the following aggregate functions
  to a field
• Sum Adds the numerical values from each row and
  displays the total
• Count Counts the number of rows
• Min Checks the value from each row and returns
  the lowest one
• Max Checks the value from each row and returns
  the highest one
• Average Adds the values from each row and
  divides the result by the number of rows 

59
Walk-through and Exercise
Advanced Query Options

• Lets log-in to Core-CT
• First, we will walk through the process together
• Scenario Applying an Aggregate Function to a
  Field
• WLA Path Core-CT Information Access gt Core-CT
  Query gt Advanced Query Options gt Applying an
  Aggregate Function to a Field
• Core-CT Path Reporting Tools gt Query gt Query
  Manager
• On your own, complete Exercise 13 in your
  Exercise packet
• Remember to utilize the Web Learning Assistant
• Raise your hand if you need any assistance

60
Overview Creating Joins
Advanced Query Options

• Core-CT enables you to create queries that
  include data from multiple tables
• Joins
• Retrieve data from more than one table,
  presenting the data as if it came from one table
• Define relationships among fields when you query
  the records

61
Overview Creating Joins
Advanced Query Options

• You can create a join between two records by
• Selecting your initial base record
• Defining its output fields and associated
  criteria
• Returning to the Records page to select the
  second record and join the keys

62
Process Flow Creating Joins
Advanced Query Options

• Where does the Core-CT system fit into the
  process?

Access Query Manager
Define Selection Criteria?
Output to Excel or Page?
Download Query to Excel
No
No
Excel
Create Joins?
Run Query
Create Query
Yes
Yes
Page
Add Record Join
Define Selection Criteria
View Query on Page
63
Walk-through and Exercise
Advanced Query Options

• Lets log-in to Core-CT
• First, we will walk through the process together
• Scenario Creating a Record Join
• WLA Path Core-CT Information Access gt Core-CT
  Query gt Advanced Query Options gt Creating Record
  Joins
• Core-CT Path Reporting Tools gt Query gt Query
  Manager
• On your own, complete Exercise 14 in your
  Exercise packet
• Remember to utilize the Web Learning Assistant
• Raise your hand if you need any assistance

64
Agenda
Maintaining a Query
Defining Query Criteria
Afternoon Break
Defining Prompts
Advanced Query Options
Query Wrap-Up

65
Conclusion
Query Wrap-Up

• Wrapping up the Query course
• Summary of completed course objective
• Introduction to the Data Dictionary
• Introduction to Core-CT Query Standards
• Using an Existing Query
• Creating a Query
• Maintaining a Query
• Defining Query Criteria
• Applying Advanced Query Options
• Complete the course evaluation forms

66
Questions?
67
Sandbox Goals/Action Plan/Adjourn
Query Wrap-Up

• After this class, Central/Agency users can
• Use the Sandbox environment as a database for
  practice on Core-CT to reinforce what you have
  learned today
• Use all of the exercises completed in class as
  job aids in the Sandbox environment when you
  return to your agencies
• Fill out the Action Plan so you can plan when you
  will utilize the Sandbox and make the commitment
  to do so
• Check out the Core-CT website for information and
  updates!!!
• http//www.core-ct.state.ct.us