Data Packages

Overview

Veracross offers a premium service called “Data Packages” which has the ability to generate data packages based on queries. Schools can use this to export their data in one of a couple formats to a configured destination (provided by the school) on a set schedule. Schools may find several uses for this functionality:

  • Export data on a recurring basis for use in external reporting tools
  • Build custom integrations using existing Veracross queries and fields
  • Backing up or archiving specific data sets on a schedule
  • Sending reminder emails that include specific Axiom links

This premium service is extra cost. Ask your account manager for pricing or if you have any further questions.

Data Package Setup

Security Configuration

Admin Access: “Data_Package_Admin” security role gives full read/write access to all packages and categories.

User Access: There are two aspects to user access.

 First, “Data_Package_User” security role must be assigned for users who will be granted read/execute permissions.

Second, to grant those users access to a specific category of packages, those users must be listed on the Package Category “Permissions” tab in addition to having the “Data_Package_user” security role, as shown in the screenshot at right.

In other words, the User security role enables the ability for the user to see packages; and then listing the user on that category enables the user to see those specific packages and execute them.

Note that the User security configuration makes Data Packages completely read-only to that user, except for the package Related Links (read below) which are read/write as a key part of the user configuration and experience.

In the initial version, there is no native way for users to access “their” Data Packages in Axiom. Instead, Admins ought to save a query (or bookmark) to a Workspace so that users can easily access the packages they have access to.

General Tab

On General tab are located several basic configuration options:

  • Category: Data packages can be organized into categories. It is possible to configure as many different categories as desired. Categories, like packages, are also configured via links on the System homepage. Category is the level at which individual users are configured to have read/execute permissions.
  • Description: plain text description to identify the Data Package
  • Access Name (Not required): the name used by the system to name the file that is generated. This should typically be similar to the description, for ease of use, but is not necessary. This may be left blank so that the Access Name on the specific query (see below) may be used to generate the file name.
  • Notes: enter notes as needed to identify the data source, purpose, etc.
  • Status: select the appropriate status
    • Draft: still in process, not used yet
    • Active (Manual): the Data Package is active, but running manually (i.e., not scheduled)
    • Active (Automatic): the Data Package is active and running on a schedule
    • Disabled: will disable the schedule after the next run

There are two summary sections:

  • Schedule Sync: displays the sync status
  • Configuration Summary: displays a plain-text description of the data sources, format, destination, and schedule

Related Links

The Related Links tab allows storage of links related to the Data Package for reference. For instance, a direct link to the SFTP site to which the data is being exported could be stored, or a custom-generated link from the reporting software being used. As many links as desired can be stored. Click “Add Record” to add a link.

Configuration

The basic configuration steps, reflected in the four Configuration tabs, are:

  1. Select the data
  2. Select the format
  3. Select the destination
  4. Select the schedule

Data Sources

The data are determined by one or more saved Axiom queries. To use a query in a Data Package, it must already exist with its own query id. Before setting up a Data Package, locate the queries and write down their query id’s so they can be entered. Use as many queries as desired to populate the data sources.

The data source access name is also used in creating the file and its name. How exactly “Access name” is used to name the file depends on the Format that is selected (see below). If the “Access Name” on the general tab is left blank, and the format is “individual excel files” or “CSV,” then this specific Source access name can be the only component of the resulting filename, giving users rather complete control over how files are named.

The Run Queries as User field is a required field. Pulling in a person determines the security surrounding what displays when these queries are run.

Add and Edit a Data Source

To add a data source, click Add Record . Type an access name (no spaces), the query id, and (optionally) notes. Click Update  to confirm, and when the screen reloads, there will be a “View query…” link to jump directly to the indicated query.

The access name, query id, and notes can be modified at any time. Updating the query id will generate the “View query…” URL when Update is clicked. Delete a data source by clicking the red X and then Update.

Format

Several format options are available. “Per query” means “for each query specified in the Data Sources tab.”

  • CSV: creates a separate CSV file per query
  • XLSX Raw (Multiple Files): creates one “raw” (no subtotals, colors, etc.) Excel file per query
  • XLSX Raw (Single File): creates a single “raw” (no subtotals, colors, etc.) Excel file, with a separate tab per query (each tab has the access name of the query)
  • XLSX Formatted (Multiple Files): creates one formatted Excel file per query
  • Axiom Query Link: links to the original query or queries. (This format is necessary if the “Export Destination” is set as Composer email – see below)

If CSV or XLSX formats are selected, the destination needs to be an SFTP location (see below), and one (or more) Files are generated, in either CSV or XLSX format. If a CSV format is selected, all field names from the original query will use the Override Value to populate the column headers.

Filename conventions

If multiple files are being generated (CSV, XLSX Raw Multiple Files), the individual file names are formatted as:

{package access name}_{data source access name}.csv or .xlsx

For example, if the format selected is CSV, and the package access name is “data_reporting,” and the data source access name is “source_two,” then the resulting filename will be “data_reporting_source_two.csv” for that particular query when the file is generated.

If a single file is being generated (XLSX single file), then a single file is generated formatted like this:

{package access name}.xlsx

And the individual sheets in the Excel file are named with the data source access name.

Destination

Two destinations are possible:

  • Email: sending an Axiom link to one or more recipients
    • When Composer Email is selected, specify both the email address to which it is sent, which must be an email address within the database, and also which Composer Template ID (or "composer_page_pk" in the export settings) to use. For reference, Composer Templates may be reviewed via Channel configuration on the Communications homepage.
    • Composer templates should include the merge field {vc:data_package_access_name} to pull in the school-defined access name for that data package, and {vc:query_access_name} to pull in the link to the Axiom query.
      • Example data package access name: {vc:data_reporting}
      • Example query access name: {vc:source_1}
  • SFTP: An SFTP server provided by the client, accessible by local Windows file sharing. This overwrites the exported file on every export.
    • When SFTP is selected, specify the URL, and credentials, and if desired, a subfolder on that domain. If a subfolder is specified for the value, “sftp.directory”, that is where the file will be saved. If that subfolder doesn’t exist yet, it will be created on the domain. So for instance, if sftp.URL is specified as “123.4.5.67”, and sftp.directory is specified as “my folder/my subfolder”, then the file will be saved at “123.4.5.67/my folder/my subfolder”.

Schedule

A Data Package can be scheduled for up to one morning (AM) and one afternoon/evening (PM) time per day, for a total of up to 14 runs per week. The schedule is based on a regular weekly cycle and is not tied to any academic or rotation calendar.

To schedule the Data Package:

  1. Enter times in the grid for AM or PM on the desired days.
  2. Click Update .
  3. Click Action menu  and then “Sync,” which syncs the schedule change with the scheduling system.

Schedule Notes:

  • The Schedule Status will alert the user if the sync needs to occur.
  • Run the Action menu item “Export Now” to bypass the schedule and run the Data Export immediately. Doing so does not affect future scheduled runs.

Status

There are two tabs that display information about the Data Package.

Processing Log

This tab displays a log of the success or failure of the Data Package.

Status Notifications

Configure notifications on all Data Package runs, only success, or only failure. A different email (or multiple emails) can be configured for each.

To add a notification, click Add Record . Select the notification type, enter an email address, and the notification content (what the email should say) of the message. Click Update  to confirm.

Module Notes

The Veracross Data Packages module is currently in Beta. Details below are subject to change during the beta period.

Data Packages Core

This initial release includes support for two primary workflows:

  • Export Axiom Query Results as CSV/Excel to SFTP
  • Email Links to Axiom queries to one or more recipients

Future workflows may not be included with the current module.

Port Restriction

The Data Packages module does not allow the use of non-default SFTP ports.

Usage Limits

Packages can run up to twice daily, and only one package may be running at a time (they are run serially, not in parallel). If two or more are scheduled at the same time, there is no specification for which will actually run first; the second of which will be queued and run when the first is complete. Each query in a package can only run for 5 minutes, and if a poorly constructed query takes longer than 5 minutes, the file will not be generated. ALL Queries for ALL Data Export Packages activated at school are regulated by a cumulative of 200,000 rows within any given hour. The only work-around is to strategically break-up and stagger your Package Schedules as well as the queries within them so that this "per hour" limit is not exceeded. Usage limits are subject to change.

IP Address for Whitelisting Veracross on SFTP Firewall

As of May 9, 2018, the IP Address for the server that sends data packages is 54.164.105.59. Depending on your school's security policies, you may wish to whitelist this IP. However, given the hosted nature of this service, it is possible that any of the following may occur with little to no notice: (a) This IP may change, if the hosted server unexpectedly requires maintenance. (b) If usage increases, more servers may be added. We'll do our best to keep this up to date, but there's a chance it may fall out of date.