Building & Running a Query

Folder Structure
 

The list of folders and subfolders containing fields referred to as dimensions, details and measures.  Different field types are demarcated with different symbols - blue parallelograms, green diamonds, orange rulers.  The fields that appear are dependent on the universe selected.  To start, users may want to drill down into the folders (Identity, Program Enrollment, Point of Service Efforts, etc), subfolders (Demographics, Flattened Demographics, Duplicate Match Criteria, etc), and toggled dimensions (Name, Family Name, etc) to become familiar with the data and how it's organized. 

  
 
Result Objects
 

 

To create a new query, drag and drop elements from the Data Tab into the Results Objects boxResults Objects Box:

  • Drag elements into this box that should display results in the report (ex: Name, Gender, POS, Reasons for Dismissals)
  • Dimensions (    ), Details (   ), and Measures (     )  can be pulled into the Results box; Filters CANNOT be pulled into the Results box
  • Any elements dragged into this box will be a distinct column in the report.
 
 Query Filters
 
In ETO Results, drag and drop fields that you want to filter by into the Query Filters section.  By default, filters are set as Constant.  A constant filter will always be applied to the report.  In this example, Address 1 and Zipcode both require constant filters. 

To set the filter, click the down arrow to the right of where it reads “In list” and select the type of filter needed.  In this example, we are selecting Is not null for both Address Line 1 and Zipcode.  Keep in mind that City and State pull from Zipcode, so setting the Zip Code filter to Is not null covers all 3 of those fields.

This is just like turning on the null filter and selecting Is Not Null in the Query Wizard.

Note all of the additional query filter types available in ETO Results, including Greater than or Equal to, Less than or Equal to, Not Between, In List, Not In List, Except, etc.

Filters will often be set to Prompt, especially for Date Ranges, such as Date of Contact, Program Start Date, DOB, Date Taken for Assessments, etc.

Once Between is selected as the filter type, Prompt must be selected for both fields – meaning the starting date and ending date of the range. 

Be sure to select starting with the field on the left and end with the one on the right.  The order they are selected will determine the order they will appear in the Prompt window each time the report is run.  This order can be adjusted in Edit Query on the Properties tab, under Prompt Order. The default order is typically alphabetical.

Note that the language that appears in the fields can be edited. Enter Date of Contact(Start): and Enter Date of Contact(End): were edited to read Between this date and this date.

Prompts will collapse into one if the text matches.  The text for all standard filters (Active, Dismissed, etc.) is Begin Date and End Date.

Note that by default, prompting filters are required.  Required filters are marked with red arrows to their left.  To set a filter so that it’s optional instead of required, go to the Edit Query page and click the blue question mark icon to the right of the applicable field in the Query Filters section. Check the Optional prompt box.

Depending on the field type the filter options will vary.  Dates, arbitrary text, and string values such as exclusive choice and non-exclusive choice have pre-determined filter options, in addition to Is null and Is not null.

When the saved query is run, the prompt is presented to the user along with any text that was included in the query description. 

If you have more than one filter, the default relationship is “And”, but if you double click on the word “And”, you can create and “Or” filter relationship as well. For example, if you used filters for Race Equal to Hispanic and Gender Equal to Female, an “And” relationship would return only Hispanic Females. An “Or” relationship would return Hispanic Males, Hispanic participants with no gender entered, and Females of all races.

If you have more than two filters selected, you can create tiers of “And” and “Or” relationships  by dragging dimensions in the Query Filters section to the right. These tiers are generally referred to as “nested” filters.

 
Program Enrollment Filters
 
The list of objects below will return "Yes/No" values and can be useful for query filtering. 
 
  

Is Active set to "Yes" in the query filter will return only those participants or entities who are currently Active in a Program. 

The "Was" objects, when added to the Result Objects pane, will automate a date range.  For this reason, it is unnecessary to also include a Universe filter (see below). These objects will limit the data to anyone who "was" active/dismissed/enrolled during a particular date range. 
 

 

 

 
 
 
Universe Filters
 
Universe filters are automated filters available in certain folders within the universe.  When dragged into the Query Filter pane, they automate data filtering for the report.  Below are some examples:
 
 
 
 Caseload Filters can be confusing.  Here is an explanation of the purpose for each:
  • My Caseload Date Range Filter:  Will return the caseload for the user logged in. If someone is currently active during the date range on any staff caseload and the same participant had a historical record on the users’ caseload, they will display in this report.   As a result this filter is most useful for Staff Dashboard reports.
  • Active Caseload Date Range: This displays only participants who are currently active on any caseworkers’ caseload during the date range.  Caseload must be maintained with clear start and end dates to reflect accurately.  Most useful for management/supervisor review.
  • Current Caseload Date Range: this runs without a prompt to display any active caseload participants as of today. Not specific to any particular caseworker.
  • Enrolled/Dismissed Caseload Date Range:  displays participants for only those Enrolled or Dismissed during the date range. Not specific to any particular caseworker.
 
 
Optimized Queries

Building Queries in ETO Results is an art form.  The following guide presents general concepts that guide query development generally, but will not apply in every circumstance.  Many users may need to take a “trial and error” approach to building more efficient queries, using the rules herein.

Selecting a Universe:

The ETO Results Report Developer has three Universe tools at his or her disposal: Standard Universes, Custom Universes and Query Wizard Universes.

  • Standard Universes: The Standard Universes (such as the etoxxx Participant Universe) are frequently improved, and generally offer the most efficient method of querying data.  Social Solutions generally recommends using the Standard Universes whenever feasible.
  • Universe Creator Wizard: Enterprises can develop their own custom universe using the Manage Report Universes feature.
  • SSG Custom Universes:  Custom Universes can be requested on a fee basis.  These can often be created to improve the efficiency of querying for particular data.  However, many times, Custom Universes are designed to view data in a simpler format and can actually run slower than Standard Universes.
  • Query Wizard Universes:  Generally, queries built and shared to ETO Results by the Query Wizard will run slower than corresponding queries in the Standard Universes.    However, with a low-volume data set, Query Wizard Universes can sometimes improve performance.

Factors Affecting Query Processing

  • Volume of Data: The higher the volume of data queried, the slower the report will run.  In more complex queries, the volume of data will exacerbate the slower performance.  Conversely, a low-volume data set may not experience any performance issues whatsoever.  Report Builders using low-volume data sets may not need to utilize any of the tips herein.
  • Folder Location:  Certain folders are inherently slower or faster than others.  This means that a Report Developer can utilize the faster folders with reckless abandon, while the slower folders should only be utilized when necessary.

o   Certain Folders are just plain slower (or faster) than others

o   The Fastest Folders

§  Point of Service (with the exception of EQs)

§  Program Enrollment

§  Employments

§  Groups

§  Identity and Flattened Demographics (with the exception of Caseloads)

o   The Slowest Folders

§  Effort Qualifiers

§  Caseloads

§  Assessments

General Tips for Building Optimized Queries:

  • Build a Custom Universe using the Manage Report Universes feature.  This feature is specific to focus areas available in the enterprise-specific universes (etoxxx Participant Universe and etoxxx Touchpoint Universe).  The most efficient custom universes will be built on a narrow focus for large data sets.  This may be limited to a single Touchpoint form along with Program Enrollment, Family information and flattened demographics, as an example.
  • Start with the end in mind: Know exactly which data you’ll need to build your formulas and present your data.  Limit the data pulled to only what the report needs.  Some helpful tips incude:

o   Include a data range filter (Active, dismissed, enrolled, start date, end date etc).

o   Filter by specific function, prompt name and value (ie Effort qualifiers, Assessment question(s); POS)

Avoid combining data unnaturally: Data from different main folders (Assessments, Point of Service, Employment, etc.), do not naturally identify with each other, but rather, through the “Identity” folder.  A query that contains dimensions from many main folders will “join” the data in complex manners, leading to slow run times.  How to remedy:

o   Separate out your data into multiple queries, and limit each query to a single “folder” where possible. 

  •  Avoid Query-side Measures: The standard “Query-side” measures (such as Count of Participants), can be useful to a new report builder.  However, they create query-side processing that can slow down the execution of the query.  All of these measures can be built simply on the report-side with a custom variable. 
  • Avoid “Results from another Query”: When using multiple queries to create a resulting data set, if can be tempting to use the data from one query to filter the results of another query.  Using this “Results from another Query” function can be very powerful, but also very slow.  This method should only be used in low-volume data sets.

 


Tags: "efficient" "optimize"

Comments