dedicated to people who want to share Blackbaud data across systems. Its
called Open Blackbaud. We are envisioning it as a place for discussion and
the sharing of all our SQL queries and strategies. We don't have much up
there yet, but were hoping you would be interested.
http://groups.google.com/group/openbb
On 10/7/09 2:11 PM, "Thomas Phelan" <tphelan@peddie.org> wrote:
> In a nutshell, what I've done is create a separate SQL database with abou=
t a
> dozen stock tables for everything from student, teacher, and relation bio
> and address information to classes, courses, and grades. I populate these
> tables automatically on a schedule from the core Blackbaud FE7/EE7 databa=
se.
> The initial SQL queries I use to populate these tables were a little tedi=
ous
> to build, but now access to the data I need is very easy and I never have=
to
> give anyone access directly to the core Blackbaud tables. The Blackbaud d=
ata
> schema (../Help/FEDatabaseSchema.chm) is very helpful and the SQL view fo=
r
> Blackbaud queries can also be used to save some time tracing down foreign
> keys. I'll be glad to share my SQL without anyone who wants it. While I
> could make the data real-time or almost real-time, I've found that a dail=
y
> rebuild works fine for all of my current applications and the daily refre=
sh
> approach makes it easier to deal with permissions and potential performan=
ce
> issues.
>=20
> The main thing I use my stock tables for is as a data source for ASP
> applications. However, I also use the stock tables and SQL views based on
> the tables as data sources for pivot tables in Excel as well as a data
> source for other software such as a very simple but powerful email merge
> program called WorldMerge. Below are a few examples of ASP apps I've buil=
t
> using the stock tables:
>=20
> *Warning List*: Blackbaud doesn't offer a solution that works well for ou=
r
> warning lists which occurs 6 times a year for us. In the past we would ha=
ve
> to export all student/class data and then import this data to another
> database used by faculty to submit their warning list. While not a big de=
al,
> this took several hours 6 times a year. I've build a simple ASP applicati=
on
> for handling the warning list, and since I can access all student, teache=
r,
> and class info via my tables, I was able to make loading the data as easy=
as
> selecting the year and term and clicking a single button. Now I can load =
a
> warning list in literally a minute or two.
>=20
> *GradeYard:* Teachers often have a need to look at student grades and
> progress reports in a number of different ways, but Faculty Access offers
> very little flexibility in this. For example, suppose a dorm supe wants t=
o
> look at all the grades and comments for student in his dorm? The only way=
to
> do this in Faculty Access is to go student by student looking at grade
> reports (note: this method is also flawed in that all you can do in FA is
> see another teacher's gradebook comments, not the actual comment stored i=
n
> the students permanent record). While this kind of information is fairly
> easy to pull out of the Blackbaud client, teachers don't have access to t=
he
> client. To solve this problem I created web application called GradeYard
> (catchy, huh?) to view grades and I have built a dozen or so filters. For
> example, now it is very simple for a dorm supe (or dept heads, or coaches=
,
> or advisors, or ...) to look at say all freshmen and sophomores in his do=
rm
> getting a c+ or lower by setting a couple of filters and clicking a "View
> Reports" button.
>=20
> *Summer Mailing / Enrollment:* I've also built a summer mailing applicati=
on
> which delivers all of our summer mailing information via the web. When I
> load an item (e.g. new student health form) I also specify a SQL filter f=
or
> the item which determines which students should get the item. After I loa=
d
> the system in May, the items pretty much maintain themselves based on the=
ir
> SQL filter. When a new student is enrolled the student automatically gets
> all the items based on the SQL filter associated with each item. I've als=
o
> made it very easy to email and/or contact parents who have items that are
> overdue. All the info comes from Blackbaud tables so no extra data entry =
is
> needed to maintain the system such as when a parent changes an email
> address, a student enrolls or withdraws, etc.
>=20
> I've built a few other apps and have several more waiting for me to find
> time to build. My next general purpose app is an emailer program which wo=
uld
> allow emailing students, parents, and/or faculty based on a simple web pa=
ge
> with assorted easy to use filters. While I know NetMail can do this, send=
ing
> something out via NetMail can be a chore and require Blackbaud client
> access. I want something that would, for example, enable the College Offi=
ce
> to shoot off an email to the current Blackbaud email address of parents o=
f
> all female day student juniors (pick your bizarre filter) in the time it
> takes to write the email itself and with no instructions required.
>=20
> Lastly, of course the one huge gotcha with all this is that you can never
> and YOU MUST NEVER EVEN THINK ABOUT writing data back directly to the
> FE7/EE7 tables. You can automate the creation of import files, but any da=
ta
> your application creates must come back into the FE7/EE7 tables via the
> Blackbaud client. This is a definite handicap and it prevents me from doi=
ng
> a lot of cool time saving stuff. This is also what's so exciting about th=
e
> 8.x platform with a web services layer. Since all interaction with the
> database will be through the web services layer, I could create applicati=
ons
> that connect to the web services layer which could do pretty much anythin=
g
> the client could do from a data perspective as long as Blackbaud adequate=
ly
> documents the web services layer which they've promised to do. This would
> give us enormous flexibility to create (or hire a 3rd party to create)
> custom applications whenever the stock Blackbaud application doesn't do
> exactly what we need. Furthermore, the ability of niche companies (e.g.
> Naviance, Infosnap, etc.) to build truly seamless applications would be
> pretty much unlimited. This is why I'm SO frustrated that 8.x for Educati=
on
> Edge looks literally years off from what I can tell. You may have noticed
> that I have somewhat of a love-hate relationship with Blackbaud. I plan t=
o
> get counseling any day now ... :)
>=20
> If you would like more info please contact me directly. I think I have
> reached my quota of ISED-L posts for the week.
--=20
Richard Kassissieh
Director of Information Technology
Catlin Gabel School
kassissiehr@catlin.edu
www.kassblog.com
[ For info on ISED-L see http://www.gds.org/ISED-L ]
Submissions to ISED-L are released under a creative commons, attribution, non-commercial, share-alike license.
RSS Feed, http://listserv.syr.edu/scripts/wa.exe?RSS&L=3DISED-L
