Virginia Tech® home

SCOOP

What is it?

SCOOP is SQL generator. It is a custom application (i.e. homegrown) that provides the user with the ability to query various data sources, typically the data warehouse, without knowledge of SQL syntax or data models/relationships. The application was authored by Matt Swift and Al Cooper in the early 2000s as a means for functional folks to be able to extract data without assistance from IT. The program builds an ODBC on the file to the "WHPROD" data source, executes the generated SQL statement that is modeled by the user and returns the data to Excel in a row/column format.

Who uses it?

SCOOP users are distributed across campus. Within Finance, SCOOP is used primarily by the Budget Office, Capital Assets and the FIT solutions group.

Architecture

SCOOP was written in VB 6.0. The source code was last edited in 2006.

SCOOP comprises three primary components:

  1. An Excel .XLA file (scoop.xla) - XLA is responsible for collecting the user's credentials, validating them against the data source, and passing off control to the executable file. The XLA password is in the FIT password safe.
  2. An executable (SCOOP.exe) - EXE is where the user builds their query by dragging and dropping various data fields and applying filter conditions.
  3. A data model (scoop_whdb.mdb) - MDB file contains the data model that SCOOP uses to determine the table joins. The MDB password is in the FIT password safe.

Installation

Prerequisites

  1. 32 bit Office
  2. Oracle Database Client (32-bit)
    • SCOOP is not compatible with Oracle Database Client 64-bit versions

Installation Steps

The following steps would ideally be executed from the primary user's profile.

  1. Verify all prerequisite conditions are met
  2. Close all Microsoft Office applications
  3. Run the Scoop Installer
  4. Replace the scoop.xla file at c:\program files(x86) with this one
  5. Open Excel and verify that the SCOOP icons appear on the Add-ins ribon. If not:
    1. Click on the File Tab
    2. Click Options
    3. Click Add-Ins
    4. Manage Excel-Add-ins > Go
    5. Browse to C:\program files (X86) Scoop\
    6. Double click the scoop.xla file
    7. Click Ok

Office 2019 - Additional Steps

  1. Copy the MSCOMCTL.OCX file and place the copied file under C:\Windows\SysWOW64
  2. Open a command prompt and navigate to C:\Windows\SysWOW64
  3. Register the DLL by typing regsvr32 MSCOMCTL.OCX