General Instructions for Generating a Discoverer Query

General Instructions for Generating a Discoverer Query

This document is intended to provide you with information about generating query results using Oracle Discoverer (Oracle BI Discoverer Desktop 11g), specifically with the data in the IRDB.

The information presented here serves as a general guide for all types of queries; it is not intended to answer any one specific research or analytic question. We’ve produced other documents that walk you through the steps to generate a query for a particular analytic purpose, such as trends in headcount and FTE enrollment, 6-year outcomes, pass rates on exit from remediation, and others. These will give you additional experience with IRDB data, as well as serve as templates for other analyses you may want to carry out.

The following steps guide you through the process of generating a Discoverer Query with IRDB data. After making a connection to the IRDB (see Getting Started with Oracle 9iAS Discoverer Plus and the CUNY IRDB), a “wizard” window appears and guides you through the steps for creating a query.

The Discoverer Wizard has 6 steps for making a data Table, and 5 steps for making a Crosstab:

Step 1: Create Workbook

Choose a Table or Cross tab (page detail or no page detail). These decisions can be changed later if you decide you want a different design. In general, Tables are useful for creating data extracts and for verifying unit record values. Cross tabs are useful for frequencies, simple and multi-layer cross tabs and other kinds of summary reporting.

Step 2: Select Items

If you are making a Table to export a data file, select the items you want in your data set. Be sure to include current SSN from the ID table. If you are creating a Cross tab, select the data point(s), row, column and page items. You can also set implicit conditions in this step. Implicit conditions are set when you select one or more specific values of an item – automatically a condition is created to limit the query results to records meeting that condition (or conditions).

Step 3: Table Layout

For a Data table, put the items in the order you want (in general, it’s best to select the items in the order you want them to appear in your table/data set. You can change the order later, but it’s more efficient if you don’t have to change the order for many variables); for a Cross tab, move fields to make the rows, columns and page items as desired. Selecting more than one field as rows, or more than one as columns results in nesting of the items. Using one or more fields as page items allows you to filter your query results for a particular value or combination of values across multiple page item fields (without having to re-query the database).

Step 4: Conditions

Set explicit conditions. If you set implicit conditions in step 2, you’ll see them here. If you want to set additional conditions, you will select the item or items using Boolean logic. Separate conditions assume an AND logic among conditions, but by creating a single condition on multiple fields or values, you have the option of setting OR conditions.

Step 5: Sort (for Tables only)

Sort the data – ascending or descending – according to the values in one or more fields. You can also sort the results of a Cross tab, but there is no wizard step; sort after the query results appear.

Step 5/6: Calculations

Create new items from existing items to serve either as data points or rows, columns or page items. Creating calculations really increases the power of the IRDB and your query reports. You can compute pass rates, or set up new fields that will show categories side by side rather than nested and create lots of analyses and data summary presentations that would otherwise not be possible. All users should strive to understand the power of calculations and be comfortable creating and testing them. See additional documentation on creating calculations.

After launching the Oracle BI Discoverer Desktop 11g, log in to the database use your user name and password. Then the instructions below will guide you through the steps of the wizard. The instructions are presented as questions to help you think about and design your analysis.

I.  Do I want to create a Table query or a Crosstab query? Create a Table to extract data from Discoverer which can then be imported into another program such as SPSS, or if your question can be answered directly with Discoverer you should select a Crosstab query. You can start with one format and easily switch to the other while you are creating the query, or after it has finished running, but it’s useful to have some idea of what the final product should look like. (After you select the “Create a new workbook”, the Table/Crosstab options will show.)

II.  What business area do I work in? The Database Administrator (DBA) has organized the Discoverer End-User Layer (EUL) into business areas, each of which contains a related set of folders of items that are the data elements of your table or the data points, rows, columns and page items of your Crosstab. Within a business area, the relationship among folders has been pre-established. You can work in only one business area for a particular query, because folders are not linked across business areas. However, you can have two different queries (worksheets) in the same file (workbook) that draw from two different business areas.

The following guidelines should help you determine which business area to work in:

1. Use the Degree Facts Business Area to answer questions about degrees granted in one or more semesters. The following is an alphabetical list of the folders available in this business area:

February 21, 2012 City University of New York General Instructions…1 of 9

Office of Institutional Research and Assessment

·  Degree ACT Survey Dim

·  Degree College Dim

·  Degree Dim

·  Degree Facts

·  Degree Health Alert Dim

·  Degree ID Number Dim

·  Degree Major 1 Dim

·  Degree Major 2 Dim

·  Degree Major 3 Dim

·  Degree Major Facts

·  Degree Special Population Dim

·  Degree Student Dim

·  Degree Test Facts

February 21, 2012 City University of New York General Instructions…1 of 9

Office of Institutional Research and Assessment

1. Use the History Facts Business Area to answer questions about enrollment, GPA, course taking and assessment results for one or more terms. The following is an alphabetical list of the folders available in this business area:

February 21, 2012 City University of New York General Instructions…1 of 9

Office of Institutional Research and Assessment

·  History ACT Survey Dim

·  History All Skill Exemption Dim

·  History All Skill Test Facts

·  History College Dim

·  History Course Perf Facts

·  History Course Show Facts

·  History Dim

·  History Facts

·  History Financial Aid Award Facts

·  History Health Alert Dim

·  History ID Number Dim

·  History Major 1 Dim

·  History Major 2 Dim

·  History Major 3 Dim

·  History Major Facts

·  History Prelude Home College Dim

·  History Prelude Host College Dim

·  History SKAT Best Facts

·  History SKAT Exit Facts

·  History SKAT Initial Facts

·  History SKAT Year 1 Facts

·  History SKAT Year 2 Facts

·  History Special Population Dim

·  History Student Dim

·  History Test Facts

·  History Waiver Facts

February 21, 2012 City University of New York General Instructions…1 of 9

Office of Institutional Research and Assessment

1. Use the Cohort Facts Business Area to answer questions that involve tracking cohorts of freshmen or transfers over time, from one semester or year to the next. The following is an alphabetical list of the folders available in this business area:

February 21, 2012 City University of New York General Instructions…1 of 9

Office of Institutional Research and Assessment

·  Cohort ACT Survey Dim

·  Cohort Facts

·  Cohort Health Alert Dim

·  Cohort ID Number Dim

·  Cohort Student Dim

·  Cohort Test Facts

·  Cohort Year 1 Fall College Dim

·  Cohort Year 1 Fall History Dim

·  Cohort Year 1 Fall History Facts

·  Cohort Year 1 Fall History Major 1 Dim

·  Cohort Year 1 Fall History Major 2 Dim

·  Cohort Year 1 Fall History Major 3 Dim

·  Cohort Year 1 Fall Prelude Host College Dim

·  Cohort Year 1 Fall SKAT Best Facts

·  Cohort Year 1 Fall SKAT Exit Facts

·  Cohort Year 1 Fall SKAT Initial Facts

·  Cohort Year 1 Fall SKAT Year 1 Facts

·  Cohort Year 1 Fall SKAT Year 2 Facts

·  Cohort Year 2 Fall College Dim

·  Cohort Year 2 Fall History Dim

·  Cohort Year 2 Fall History Facts

·  Cohort Year 2 Fall History Major 1 Dim

·  Cohort Year 2 Fall History Major 2 Dim

·  Cohort Year 2 Fall History Major 3 Dim

·  Cohort Year 6 Degree College Dim

·  Cohort Year 6 Degree Dim

·  Cohort Year 6 Degree Facts

·  Cohort Year 6 Degree Major 1 Dim

·  Cohort Year 6 Degree Major 2 Dim

·  Cohort Year 6 Degree Major 3 Dim

February 21, 2012 City University of New York General Instructions…1 of 9

Office of Institutional Research and Assessment

Folders named as “Facts” contain facts or data points (see III for further discussion on data points) and commonly used categorical fields that can be selected as rows, columns and page items, or used to set conditions (See V for further discussion on conditions). Folders named Dim” contain categorical items (no data points).

III.  What data do I want to display? Determine what fact(s) or data point(s) you intend to report on (e.g., headcount enrollment or number of students, semester FTE enrollment, GPA, credits attempted, credits earned). The answer to this question will determine in which folder you should begin your item selection. Choose your data point(s) first, unless you will use your own calculation as a data point, then these can be calculated in wizard step #6 for a Crosstab or wizard step #7 for a Table.

A data point is a numeric item that fills the cells of your Crosstab. Data points are variables that have values upon which you can perform a mathematical operation such as sum, average, minimum, maximum (contrast with a categorical field).

To answer the question “how many students?” use Headcount, the most commonly used data point. But many other data points are available, including semester GPA, cumulative, semester credits earned, cumulative credits earned, semester FTEs, age. When creating a data table, choose the detail for numeric items (defaults may be set to sum or average). When creating a Crosstab, select the appropriate operation for the data point (headcount-sum, age-average, etc.) or use the default operation, which is usually the one you want, anyway.

Items that can serve as data points are identified with an icon that looks like this: , or if the item is calculated by the Discoverer Administrator, the field will have an icon that looks like this: Your own calculations can also be used as data points.

IV.  What column(s) and row(s) do I want to include in my Crosstab? Select categorical items for your Crosstab query to form one or more column layers and one or more row layers (e.g., degree level by ethnicity by gender by full/part-time status). Categorical items have an icon that looks like this: . You should select the DESC version of the variable, but you may want the CODE as well. At the same time you are selecting items as rows, columns or page items, you may select individual values of those items as a way of setting a condition (implicit condition). Please see V below for more information on implicit and explicit conditions.

V.  What population do I want to report on? You’ll need to determine which records should be selected for your query; that is, determine what population you want to report on (e.g., all undergraduates enrolled in fall 2003, first-time freshmen enrolled in associate programs in fall 1999 through fall 2003, or students who graduated with a bachelor’s degree in biology from 1997-98 through 2000-01). You can query information about a broad group of students or a very narrow group by setting conditions on which records will be selected into your query.

You can create conditions or selections in two ways, implicitly or explicitly. The query results will be the same no matter which way you set your conditions, but your data table or Crosstab table may look a little different depending upon which conditions you set implicitly and which you set explicitly.

It is generally easier to set implicit conditions but setting all of your conditions this way may result in a messy Crosstab display, or your data table may contain items that you don’t need to include. In general, it is best to use implicit conditions when the item on which you are setting a condition will form a row, column or page item in your Crosstab table, or for which values will vary in your data table. For conditions on items that do not need to appear in your Crosstab, or for which all records in your data table will contain the same value, make explicit conditions.

Set conditions on codes rather than descriptions if you plan to use this workbook in the future because descriptions may change, but codes will not.

Set an implicit condition by selecting a particular value or values of an item you are selecting into your query in the selection window (wizard step #2). For example, in the image below, I have selected the following values of Year Enrolled: ‘1999’, ‘2000’, ‘2001’, ‘2002’, ‘2003’. Selecting particular values will result in a condition being set based on those values, which you will see when you get to the Conditions window (wizard step #4).