Day 4 - Queries, Queries Everywhere!

Day 4 - Queries, Queries  Everywhere!

In day 3 of the Aspen 21 Day Challenge we learned about field sets and how to create them to display the data according to the fields we wanted to view. Once you begin to get a deeper understanding of the fields in Aspen you'll want to be able to dig deeper. Your answer is the query tool in Aspen. A query is simply your ability to ask a question of the data. It sounds simple enough, but whenever you ask a data system a question you have to know what you're asking for, and how to ask the question. Initially the process may drive you batty, but stick with it. The benefits of learning how to query your data in Aspen will outweigh the initial frustration of creating a query and getting zero results displayed. Again stick with it, and soon you'll be making your data sing!

The Query Tool: Dissecting the Options

Before we get into the "how tos" about running a query lets take a look at the options for running queries. The first step is to know where to find the query tool.  You can run a query from most of the top tabs, and side tabs in Aspen. Simply click on a top tab, in illustration 1 I've selected the student top tab from the district view. Once you've selected a top tab click on the Options menu item, and drag to the Query tool. This is where the magic starts ;)

Illustration 1: How to Locate the Query Option

The 3 Different Query Modes: Simple, Advanced & SQL

You can run one of three different types of query modes in Aspen. The Simple Mode is the easiest query mode to use in Aspen, and what appears as the default. The Advance Mode, and the SQL Mode are used by end users that have more experience with the query tool. It's an easy jump from the simple mode to the advanced mode, but the SQL mode takes an understanding of SQL. Illustration 2 displays each of the query modes.

Illustration 2: Query Modes in Aspen

For our purposes in today's blog we'll discuss the simple mode query tool. In future blogs we'll explore the advanced mode, and the direct SQL mode. The simple mode query tool is quite powerful, and relatively easy to use if you understand some basic information about your data.

Conceptual Ideas connected to queries

●A query in Aspen is connected to your knowledge of field sets..
●A query asks for information pulled from fields that are located within tables.
●A query can pull (or ask for) information from more than one table.
●A query can be simple or complex. You can create a series of simple queries, or one more
complicated question.
●The information collected from a query can be collected in a field set that can be exported to
Google Docs or Excel using the CSV exporter tool.

Step 1: Start Off With A Question

The best place to start with queries is to figure out what question you want to answer using your data. In my example I want to quickly identify the students in my class that scored less than a 2 on the State of RI's standardized assessment, Mathematics NECAP. For the purposes of this example I'll be searching for that data in the Staff View of Aspen. You can refer back to Day 1 of the 21 Day Aspen Challenge for more information on Aspen views. I need to know some information, and know what specific question I want to ask. I would like to ask the following question using the simple mode query in Aspen.  

• Which students scored a 2 or less on their mathematics achievement level on the 2013-14 NECAP assessment? 

In order for me to ask that question successfully I need to understand that there are several questions built into one question. There are actually three questions, or three pieces of information that I need to know before I can ask the right question in my query. I really need to know the following:

1. What is the NAME of the assessment definition. Assessment definitions are where we keep the different assessment data in Aspen. It's how we categorize one assessment from another, such as NECAP vs. PALs.
2. What was the DATE range of when the assessment took place. Sometimes it's easier to think about that as did the date of the assessment take place on or after a certain date.
3. What is the name of the specific data FIELD that you're trying to get to in your query. This is why it's important to have an understanding of your data dictionary in Aspen. 

Thinking about queries in this manner will help your significantly in your quest to become the master of your data. 

Step 2: Understand Your Data Dictionary

To answer my question,  "Which students scored a 2 or less on their mathematics achievement level on the 2013-14 NECAP assessment?", I need to know some information about my data dictionary. It would be helpful to visit the data table using the top tabs and side tabs in Aspen. Essentially every time you click on a top tab or a side tab you are accessing a data table in the system. To better understand the assessment definition data table I will go to the district view, click on the assessment top tab, click the assessment definition side tab, and finally locate the NECAP assessment definition details. Illustration 4 displays the many field that can be queried using the simple mode query in Aspen. Remember we want to query information about the DATE, the NAME, and the MATHEMATICS ACHIEVEMENT LEVEL.

Illustration 4: Assessment Definition Details

All of the fields that are collected in a specific assessment definition can be displayed in a field set within Aspen. The field set view is what allows you as a teacher or administrator an easier representation of the data. In the WWPS we actually import our assessment data in two steps. The first step pulls the data into Aspen and allows us to view it or query against it from the fields illustrated in illustration 4. This is great because our data is now in a system where we can run multiple queries against it, but the view isn't very helpful. We take the data, and reimport it into fields that most teachers and administrators want to access. That information shows up in the field set view in the Assessment History side tab. Once the information is displayed in that manner it allows us to see it, and query it in a much simpler way. Illustration 5 shows assessment data, and how it can be displayed in a field set within the Assessment History side tab.

Illustration 5: Understanding Field Names & Data Tables

Step 3: Putting the Query Together

Now that we have a better understanding of where the assessment data is housed, and the fields that contain the bits of data from one NECAP assessment we're ready to run our query. If we think back to our original question we want to find the students in our classes that scored a 2 or below on the October 2013 NECAP assessment. Our question was:

• Which students scored a 2 or less on their mathematics achievement level on the 2013-14 NECAP assessment? 

To ask that question we would click on the student top tab from our staff view. Once our students were listed we could click on the Options menu and drag to the Query tool. Once the New Query popup displayed we could begin to ask the three separate questions that make up our query. Before we go further let's understand the six parts that make up our query. See Illustration 6 to get a break down on each part of a query.

Illustration 6: Query Dissected

My first task is to get to the right table for my query. I want to access the information from the Student Assessments table. I do that by clicking on the triangle from the tables field, and scroll to Student Assessments. Illustration 7 shows how to select the Student Assessments table.

Illustration 7: Choose the Correct Table

Once I've chosen the correct table, I can begin to ask the right questions concerning the fields in the data table. Again I'm going to refer to my original question to ensure that I build the query in the correct manner.

• Which students scored a 2 or less on their mathematics achievement level on the 2013-14 NECAP assessment? 

I'm going to start out by building my query on the assessment definition name. We established earlier that I had an assessment definition called NECAP. My query needs to identify the correct assessment. I want to make sure that delineate between NECAP, PALs, Grade Score or Local Assessments. That query ends up looking like the following statement: Student Assessments > Assessment Definition > Name Contains NECAP. I ended up choosing the CONTAINS operator, because it's more forgiving than the equals or exactly matches operators. Once I've asked my first question, I can build on my query and ask my next question. I want to check on NECAP scores for the 2013-2014 school year so I need to make sure I ask the right question. For this query I asked the following: AND Student Assessments > Date On or After 9/1/2013. I used the AND button because I wanted to create a logical progression that included the first question and the second question. I also used the ON OR AFTER operator to let Aspen know that I wanted information concerning NECAP after the start of this particular school year. I'm ready to ask my third and final question which is: AND Student Assessments > Math AL Less than or equal to 2.0.  In this final question I used the and operator again to tell Aspen that I wanted all of the information from the first two questions and this final question in the same search. Essentially Aspen is looking in the Math AL field for scores of 2.0 or less. Illustration 8 displays the final query.

Illustration 8: Your Finished Query

Once you've finished writing your query you can click on the search button. Your results based on the question you asked will be displayed in your present field set. Once you have your results you can export the data as a CSV to import into Google Spreadsheets, create a quick chart or print out your data. We'll explore ways to export and view the data within upcoming blogs on the 21 Day Aspen Challenge. Make sure you comment on the blog to let me know what questions you'd like answered.