Trend Data

Question

Trend Data

beck267

Valued Member

Points: 55
More actions
December 18, 2007 at 2:21 pm

#177643

Hello
Is there anything existing in SQL BI that would compute historical trend analysis on student assessment data? For example, If I am looking at the entire 4th grade class from the 2006-2007 school year, is it possible to see those student's scores/growth as 3rd graders in the 2005-2006 school year. I am quite new to this and do not know where to start looking.
Thanks in advance for any help

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mike Seddon SSC Veteran Points: 224 More actions · Answer 1

Hi, not sure if this will help or not. I was asked to write a report to predict when servers would run out of disk capacity so I wanted to use a straight line projection to attain this. As the data was stored in SQL I searched for a function that would allow me to trend the data into the future to see where it crossed a threshold. I found nothing in SQL itself (But that doesn't mean there isn't anything).

The problem seemed simple, I needed a starting point for each machine and an periodic increment/decrement to plot the data out. In the end I created a dot net class to populate an Excel sheet, then used the trend function in Excel to determine these values and then forward populated the database. This process runs daily and overwrites the previous trend data.

I will keep an eye on this thread in the hope that someone exposes an equivalent t-SQl trend function.

Mike.

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 2

Basically, you should consider loading the data into Analysis Services. The key will be getting your dimensions correct. While there isn't space to go into all of the design elements, I'd sugest starting with the following model:

Dimensions:

Students

Tests (You'll need a way to link tests from multiple years. School year should be an attribute.)

Date

Teacher (let's evaluate the teacher as well!!)

Data

Test Score

Weighted Test score

The tough part will be trying to line up the tests, year over year. Since they can (and will) change, to compare year over year, you have to have some way of comparing them. Otherwise, just use date (no comparison of different classes).