Relocation Dashboard Report
Using SQL to get report data
Section of the relocation dashboard report
In 2012, I was asked to create a report for Wells Fargo's upper management summarizing the usage of relocation services and the costs associated with them. The report I created was the Relocation Dashboard report and it was provided to upper management weekly.
I developed the report using MS SQL Server and later refined it using AQT. The Relocation Dashboard query set was designed to obtain high-level information about relocation activity for the current year, including the number of relocations in process by business line, amount spent to date, and other important high-level information. This query set drove a weekly report provided to upper-level management.
In addition to the dashboard report, I also developed reports for external groups including HR, finance, and business line management; and internal reports for the relocation department. Each report was the result of what was essentially a reference interview. In developing a report, I questioned the end user to determine their information need. I then used my knowledge of the relocation database's structure and contents and my skills with SQL to create a report that would satisfy that need.
Developing reports of this type taught me how to extract data from a database to satisfy a specific information need.
I developed the report using MS SQL Server and later refined it using AQT. The Relocation Dashboard query set was designed to obtain high-level information about relocation activity for the current year, including the number of relocations in process by business line, amount spent to date, and other important high-level information. This query set drove a weekly report provided to upper-level management.
In addition to the dashboard report, I also developed reports for external groups including HR, finance, and business line management; and internal reports for the relocation department. Each report was the result of what was essentially a reference interview. In developing a report, I questioned the end user to determine their information need. I then used my knowledge of the relocation database's structure and contents and my skills with SQL to create a report that would satisfy that need.
Developing reports of this type taught me how to extract data from a database to satisfy a specific information need.
Theatre Club Database
Creating a database from scratch
Part of the ERD developed for my theatre database.
To expand my knowledge of SQL databases beyond my self-taught queries and updates, I took a course on relational database management systems. For this course, I designed a database from the ground up beginning with an entity relationship diagram (ERD) and ending with the code to actually create the database. I decided to use my knowledge and interest in theatre as a basis for the database. I designed a database that a theatre club could use to track shows they have seen including venues, playwrights, actors, directors, crew, and musicians. The basic code for creating this database, creating indexes, and assigning permissions came easily to me because of my familiarity with SQL queries. This formal training in SQL helped to refine my query skills and improve my confidence in a skill that was previously entirely self-taught.
This project also served as a practice run for a database that I am developing for my family's personal household use. Early in my college career, I was a theatre major. Today, I regularly attend live performances with my family. One of our favorite pre-show pastimes is consulting the cast list to see which actors we have seen before. With each show, I am asked to create a database that will let us do this more efficiently and accurately. My next big database project will be a mobile-enabled database that we can use to look up an actor and check whether we've seen them on stage before.
This project also served as a practice run for a database that I am developing for my family's personal household use. Early in my college career, I was a theatre major. Today, I regularly attend live performances with my family. One of our favorite pre-show pastimes is consulting the cast list to see which actors we have seen before. With each show, I am asked to create a database that will let us do this more efficiently and accurately. My next big database project will be a mobile-enabled database that we can use to look up an actor and check whether we've seen them on stage before.
Dublin Core
Learning XML
A portion of the Dublin Core schema developed for this project
As part of a course in Metadata Design and Interoperability, I worked with a group to design an application profile for using Dublin Core (DC) with the Criterion Collection of movies. The initial part of the project had each group member designing their own XML schema for DC. This initial project introduced me to the concept of application profiles and illustrated for me the variety of approaches possible for the implementation of a metadata schema. In developing my DC schema, I chose to use substitution groups to reflect the structure of DC and allow for elements such as <title> and <alternative> to coexist without having an outer container to hold all the title elements. For our application profile, we incorporated my DC schema, the FilmStandards.org standard EN15907 for cinematographic works, and some local terms specific to the Criterion Collection. The final application profile was set up to contain three element groups: Dublin Core elements, FilmStandards.org elements, and Criterion elements. Our profile is not perfect and could use a little refinement; however, I learned a lot about how to approach developing an application profile and how metadata schemas are used to describe content.
Metadata Object Description Schema (MODS)
Continuing to Learn XML
MODS AP presentation slide
My second experience with designing an application profile was also part of the Metadata Design and Interoperability course. This project was an application profile for using MODS with a collection. My group decided to create a profile for Stanford's Dime Novel and Story Paper Collection. After creating an XML schema for MODS, we selected some local elements unique to the Dime Novel collection that we felt could be used to enhance MODS when used with this collection. We created the application profile with a base MODS schema and added local elements as refinements of that schema. We then tested our profile by creating an XML instance containing records for 20 items. This experience refined my understanding of how an application profile works and how one can be used for a particular collection.
Metadata Encoding and Transmission Schema (METS)
Final project in XML class
METS presentation title screen
My final project for my Metadata Design and Interoperability course was the development of an application profile based on METS. For this project, I chose to work with sheet music from the Historic American Sheet Music collection at Duke University Libraries. I created a Dublin Core application profile combined with some local elements for the descriptive metadata used in the collection. For administrative and technical metadata, I created an application profile using MIX. I allowed for the use of MIX for source and digital provenance metadata as well but was unable to test this section since I did not have access to the relevant metadata. For file structural and behavioral metadata, I chose to use standard METS sections. This project combined the work from the prior projects and required a broad view of the process during the development of my application profile. I addition to creating the XML profile, I created an accompanying data dictionary, an overview presentation, and a writeup. Developing this profile on my own gave me the chance to explore various possibilities for accomplishing my goals and helped me to understand how the different metadata sets can be used in conjunction with each other.
orcid.org/0000-0002-2569-6556