Digital Measures Extract, Transform, and Load

This project assists the Director of Academic Information in mashing up institutional data with academic data. The PHP 7 code makes a request to the Digital Measures Activity Insights REST API, parses the hierarchical XML response, transforms it into a multi-table relational SQL database structure, and then inserts it into a Microsoft SQL Server database to be consumed later by the University of Arkansas Data Warehouse and SAS Visual Analytics platform.

More information pertaining to this project is tracked in the Walton Kace Helpdesk TICK:21500

Installation

Requires PHP7 and Microsoft's ODBC Driver.

composer is used to install the required libraries.

If you're running Ubuntu 16.04, then helpful installers are included.

sudo ./install/1.php7.bash
sudo ./install/2.mssql.odbc.driver.php7.bash
sudo ./install/3.composer.bash
composer install

Copy the example files and update the credentials for:

1. Digital Measures data/digitalMeasuresCredentials.php
2. Microsoft SQL Server data/pdo.php
3. REST endpoints you want to extract data/datasets.php
4. Email credentials for error reporting data/email.php

Getting Started

For the first time, you will need to create the schema and database tables. This will actually create two sets of identical tables, one set that is prefixed with tmp as a temporary staging area. The replace script will physically load these tables during execution. If each dataset is loaded without errors, then it will load all the tmp data into the production tables.

php ./scripts/createDatabase.php

Next, to actually fetch the XML from Digital Measures and then replace the data in the database, you need to run the replace script.

php ./scripts/replaceData.php

Use a task scheduler to regularly update your data. Here's an example using cron that will run every day at 3:07AM.

7 3 * * * /usr/bin/php /path/to/dmetl/scripts/replaceData.php > /path/to/dmetl/log.txt 2>&1