State of Connecticut Core-CT Project

1
State of ConnecticutCore-CT Project Query 4 hrs
Updated 1/21/2011
2
Objectives

• In this training, you will learn to use basic
  concepts of Query in CORE-CT. We will
  specifically focus on the following topics
• Reference Materials and Training Tools
• CORE-CT website
• EPM Job Aids
• EPM Reporting Tools
• Data Dictionary
• Query Manager
• Searching for and Executing an Existing Query
• Modifying an Existing Query
• Define Criteria including Prompts, Add and Remove
  Data Fields
• Editing an Existing Query
• Translate Values, Column Order, Sort
• Creating a New Query
• Maintaining a Query
• Schedule, Copy to another user, Delete, Rename,
  Mark as Favorite

3
Introduction
Why EPM? Answer EPM allows users to query data for many purposes such as to generate reports, analyze trends as well as audit transactions. What is a Query? Answer 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. 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






4
Data Architecture
Live Data
Static Data
Transactional Data
EPM Warehouse
Source Tables
EPM Reporting Using PS Query
Informatica (ETL)
Data Loader (App Engine)
PS Query/ nVision/ Crystal
Reporting Tables
5
Data
Live Data

• Understanding the Data is Key
• To successfully query data, users must be
  familiar with the following aspects of data
  fields
• Functional use what the data represents at the
  agency level
• Relationship to other data fields
• Data format text, number, field length, case
  sensitive

Transactional Data
EPM Warehouse
6
Reference Materials and Training Tools

• CORE CT website is central repository for a
  wealth of information, reference materials and
  training tools to assist users.
• http//www.core-ct.state.ct.us
• Specifically, 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

7
EPM Job Aids
8
EPM Job Aids
9
OverviewEPM Reporting Tools

• EPM Reporting Tools
• Data Dictionary
• II. Query Manager

10
OverviewEPM Reporting Tools

•    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 EPM and the
  fields that make up those tables
• This dictionary enables you to look up and review
  descriptions of fields that you may be unfamiliar
  with in Core-CT

11
Data Dictionary
12
Data Dictionary
Exercise 1
13
OverviewEPM Reporting Tools
Query Manager The Query Manager tool allows
users to 1. Search for and Execute an Existing
Query 2. Modify an Existing Query 3. Edit an
Existing Query 4. Create a New
Query 5. Maintain a Query
14
Query Manager
15
Query Manager
Search for and Execute an Existing
Query Exercise 2
16
Query Manager

• Modifying an Existing Query
• Existing queries can be modified to meet new
  reporting needs as well as can be enhanced to
  increase readability and presentation.
• Modifications can include
• Adding or removing Data Fields
• Establishing, altering or removing Criteria
• Editing format and presentation layout

17
Defining Query Criteria

• Selection criteria enables you to selectively
  retrieve only the data you want
• Can be built into the query design or applied as
  a Prompt.
• A prompt is an efficiency mechanism that allows
  users to establish criteria each time a query is
  executed rather than having to alter the query
  design.
• 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

18
Effective Date Criteria
Queries using Effective Dated Tables

• The Effective Date (EFFDT) field provides a
  historical and future 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, CORE-CT generates
  a new effective date and 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

19
Effective Date
20
Query Manager
Modifying an Existing Query Exercise 3
21
Query Manager

• Editing an Existing Query
• Existing queries can be edited to increase
  readability as well as enhance the formatting of
  the presentation of the final report.
• The edit functionality allows a user to
• Translate Values
• Order Columns
• Sort Data
• Utilize simple Aggregate functions

22
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 

23
Query Manager
Editing an Existing Query Exercise 4
24
Creating Your Own 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 and query properties
• Defining selection criteria
• A more complex query may also include
• Creating joins

25
Creating a Query-Keypoints

• 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

26
Key Fields
27
Query Naming Standards

• Best Practice
• Users should follow this standard when naming
  private queries
• Remember There can be no spaces in the naming
  convention.

Private Queries USERSINITIALS_AGENCYACRONYM_M
ODULE_FUNCTION_QNAME Example DM_APA_HR_EMP_CHANG
ES
28
Query Manager
Creating a New Query Exercise 5
29
Query Manager

• Maintaining a Query
• Users can perform the following functions
• Scheduling a Query
• Use of Folders and Favorites
• Deleting a Query
• Copying a Query to another User
• Rename a Query

30
Query Manager
Maintaining a Query Exercise 6
31
Query Manager
Challenge Exercise
32
Conclusion
Query Wrap-Up

• Wrapping up the Query course
• Summary of completed course objective
• CORE CT websiteJob Aids and Resources
• Introduction to the EPM Reporting Tools Data
  Dictionary and Query Manager
• Search for and Execute an Existing Query
• Modify An Existing Query
• Edit an Existing Query
• Creating a New Query
• Maintain a Query
• Challenge Exercise
• Complete the course evaluation forms

33
Questions?