EM Knowledge Base‎ > ‎Integration‎ > ‎Exports‎ > ‎

Advanced Export

Every Enrollment Manager client has access to the daily Standard Export, available via FTP download each morning. In addition to the Standard Export, we offer the Advanced Export. The Advanced Export helps you maintain a local copy of relevant Enrollment Manager data on campus in a relational database. This local copy includes all of the most common data without the record limitations of the Standard Export. Like the Standard Export, the Advanced Export includes data on any record with an opportunity for current active terms. The data will include current (not historical) records and daily updates as they occur. The Advanced Export offers the following advantages over the Standard Export:

  • Relational Database: Your Advanced Export data is stored in SQL Server with all tables joined, making it ideal for integration and/or reporting efforts. This provides a very robust environment compared to the Standard Export, which is a single text file.
  • Automatically Updated: While the Standard Export requires you to download a new file before using it, the Advanced Export database is updated automatically for you on a daily basis. This process is designed to provide excellent reliability with minimal bandwidth requirements.
  • Full Table Records: The Standard Export includes some limitations on the number of child records (activities, scores, relationships, interests, etc.). With the Advanced Export you'll get every record from these tables, with linking fields for joining them to the appropriate Person/Opportunity. (NOTE: Activities used to record System updates (Activity Category=System) are not included in the Advanced Export data).

Data Available
The Advanced Export includes relevant data from the following tables:

  • Person
  • Opportunity
  • Activities
  • Scores
  • Interests
  • Relationships
  • Person Organizations
  • IDs
  • Races
  • Status Changes
  • Financial Aid Awards
  • Notes
  • Custom Data

Relationship Diagram

  

 
 

Hardware Requirements

The Advanced Export requires a local server with the following:

  • Windows 2008 Server R2 (64-bit). 
  • A recent multi-core processor 
  • At least 4GB RAM (6 GB or more is recommended)
  • SQL Server 2008 R2 Standard or Enterprise Edition 
  • SQL Server Integration Services. 
  • Sufficient disk space to store both the database and backup files which can use as much as 50-100 GB depending on the size of your database. This 50-100 GB must be local storage though the database itself can used shared storage. 
  • SQL Server will need to be setup in  Mixed Mode (which allows SQL Server and Windows Authentication mode) during installation. 
  • User account with Administrator permissions

Note: Other operating systems or versions of SQL Server will not be supported. 

Updating the Advanced Export Database Manually

The process for installing or updating the Advanced Export database consists of:
  • Downloading the zipped database backup from the FTP site
  • Unzipping and restoring the database backup file in SQL Server
  • Restoring user permissions, if needed

Downloading the zipped file
The file is stored on our Secure FTP Server in the AE folder here: <orgname>.ftp.422x.com, where <orgname> is your Enrollment Manager organization name (for example: gu.ftp.422x.com). You will use the same FTP account and password you use to get the Standard_Export.zip file, using port 22. If you are unsure of this username and password, contact the Support Team. Your Advanced Export database file should be updated each day by 6 AM daily. Please contact Support to make special arrangements if an earlier time is preferred.

Unzipping and Restoring the Database Backup File 

  1. Use Winzip or another tool to unzip the backup file onto a drive where SQL Server is installed.
  2. Restore the database onto SQL Server by opening Management Studio's and right-clicking the databases folder, and selecting Restore Database.  In that dialog box, under Destination for restore, enter the name of your database in the To field. Under Source for Restore, select the From device radio button, then click on the ellipses to browse to find your unzipped file, then click OK twice. Then check the Restore box by the database name in the lower window. If you are overwriting an earlier version of the database, change to the Options tab and check the Overwrite the existing database box. Please note the database may currently not be in use or the restore will fail. Click OK to begin the restore.
  3. Depending on permissions granted on your campus, you may need to grant permission to read the database to your users.

Automating Advanced Export Updates

To automate the entire process, a document is available below with recommended steps and freeware tools that be used. Other tools can be used, of course, but this should provide the information needed to help you automate the process at no cost and with little effort.

Troubleshooting the Advanced Export Process

There are several possible explanations for the Advanced Export database not showing expected data. 

Problem:                                         Possible Cause:                                            Action to Resolve:                                                           
  •  FTP job did not start

  •  If scheduled, be sure task is enabled, runs under proper account
  • Verify FTP site is accessible
  •  FTP job got interrupted
   
  •  Database restore did not start
Check the current SQL Server Log File in Management Studio th  
  •  Database restore did not finish
Check the current SQL Server Log File in Management Studio th  
  •  RuffaloCODY job preparing the database backup file failed, did not run or was delayed
backup file was downloaded, but the backup was from a previous day  
  •  RuffaloCODY job updating the Warehouse failed, did not run or was delayed
 Warehouse updating finished after the database copy was taken, zipped and placed on FTP  
  • Data change in Enrollment Manager was made after the Warehouse was updated
   


Using the Advanced Export Database

How you use the Advanced Export database is completely up to you. Being a relational database stored in Microsoft SQL Server gives you many options for both integration and reporting-- assuming you know how to connect to and use a relational database. RuffaloCODY does not offer support other applications used to connect to your Advanced Export database. If you are not familiar with this type of technology, you should not use the Advanced Export. See the attached document below for further details on the data available within Advanced Export.

More Information

For pricing information or further details, contact the Support Team at adlabsupport@ruffalocody.com.
Ċ
Jeff Arnold,
Feb 12, 2013, 7:43 AM
Ċ
Jeff Arnold,
Feb 12, 2013, 7:43 AM
Comments