Set Up Data Gathering

The data gathering service collects content usage data that can be imported into third party data visualization tools such as Tableau Desktop. Usage data is stored in a database as JSON event records.

Example events include hand and finger touches, opening or closing a widget, playing a video, viewing a PDF, browsing to a URL, adding items to a personal space, and emailing items from a personal space.

About the Reporting Database

When an MT Showcase app with the data gathering service is running, MT Showcase saves event records in a database, mt-showcase-reporting.

This database contains two tables, schema_info and events. Database version details are saved in schema_info. Event records are saved in the events table.

If required, you can configure MT Showcase to write event records to a remote database server; see Set Up a PostgreSQL Reporting Database on a Remote Windows Computer.

Recommended DBMS: PostgreSQL or SQLite?

PostgreSQL is the recommended DBMS for the data gathering service, on both Ubuntu and Windows application computers. This is because usage data is stored as JSON event records. PostgreSQL supports data in JSON format, allowing users to extract and analyze the usage data with relative ease.

Ubuntu Application Computers

On Ubuntu application computers, PostgreSQL is installed automatically with MT Showcase. The mt-showcase-reporting database and associated database tables are also created automatically.

Windows Application Computers

On Windows application computers, SQLite is installed automatically and is the default DBMS for the MT Showcase application database and reporting database. The mt-showcase-reporting database and associated tables are also created automatically.

However, although SQLite is a suitable DBMS for the application database, it is not suitable for the reporting database. Unfortunately, this means that if you want to enable data gathering on a Windows application computer, you must install PostgreSQL and manually create the reporting database and database user account. Finally, you must reconfigure MT Showcase to store usage data in the new PostgreSQL reporting database.

When Must I Manually Install PostgreSQL?

PostgreSQL is installed automatically with MT Showcase on Ubuntu application computers. However, you may need to manually install PostgreSQL if you want to:

• Set up a reporting database on the local Windows application computer; see Set Up a PostgreSQL Reporting Database on a Windows Application Computer.

• Connect to remote reporting database; see Set Up a PostgreSQL Reporting Database on a Remote Windows Computer (Windows computers) or Set Up a PostgreSQL Reporting Database on a Remote Ubuntu Computer (Ubuntu computers).

Recommended Versions of PostgreSQL

If you manually install PostgreSQL, we recommend the following versions:

• Ubuntu: PostgreSQL 9.5.6

• Windows: PostgreSQL 9.6.2

Note

The MT Showcase data gathering service has been tested using the PostgreSQL versions listed above. It may run successfully using other PostgreSQL versions, but these have not been tested.

Set Up a PostgreSQL Reporting Database on a Windows Application Computer

(Applies to Windows application computers only)

This configuration task is mandatory if you want to enable data gathering on a Windows application computer.

The MT Showcase data gathering service requires a PostgreSQL database plus a database user with full privileges and a password. You can create this database and database user using any method that adheres to your organization's database or IT policies.

To set up a reporting database on the local Windows application computer, follow the sections below.

Install PostgreSQL on a Windows Application Computer

Download and install PostgreSQL from the EnterpriseDB web site: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Note

• For recommended versions, see Recommended Versions of PostgreSQL.

• The installation wizard assigns port 5432 to the default cluster. We recommend you accept this default port. You will specify this port number when configuring MT Showcase.

Create the Reporting Database and a Database User

Now set up a reporting database and a database user. For simplicity, we recommend you use the same value for the database name and user name. (Note that the required tables are created when you start the MT Showcase server.)

Follow these steps:

1. Open a command prompt and navigate to: C:\Program Files\PostgreSQL\<version>\bin

2. Run this command to log into PostgreSQL as the postgres admin user:

   psql -U postgres
   

3. Create the database and database user. For example, you can run the psql commands below. These commands use mt-showcase-reporting as the database name and user name and x6dgfn8 as the user password. Take careful note of the syntax!

   CREATE USER "mt-showcase-reporting" PASSWORD 'x6dgfn8';
   CREATE DATABASE "mt-showcase-reporting" OWNER "mt-showcase-reporting";
   GRANT ALL ON DATABASE "mt-showcase-reporting" TO "mt-showcase-reporting";
   ALTER USER "mt-showcase-reporting" VALID UNTIL 'infinity';
   ALTER USER "mt-showcase-reporting" WITH PASSWORD 'x6dgfn8';
   

   You will reference this database and database user in the next step.

4. Press CTRL+C to quit from psql.

Configure MT Showcase to Use the Local PostgreSQL Reporting Database

Now you must configure MT Showcase to use the PostgreSQL reporting database. Follow these steps on the MT Showcase application computer:

1. Edit production_users.yaml. For the file location, see the Where Are My Files? section.

2. Replace the existing reporting_db line with this line:

   reporting_db: postgres:///mt-showcase-reporting?port=5432&user=mt-showcase-reporting&password=<password>
   

   Where:

   • mt-showcase-reporting is the database you created above.

   • port=5432 is the port that the reporting database listens on. This is the default port number specified by the PostgreSQL installer.

   • user=mt-showcase-reporting is the user you created above.

   • <password> is the password you supplied above.

Restart the MT Showcase Server (Local Windows)

Now restart the MT Showcase server to create the required database tables; see the Start or Stop MT Showcase section.

When the server restarts, you can enable data gathering; see Enable Data Gathering.

Set Up a PostgreSQL Reporting Database on a Remote Windows Computer

(Applies to Windows computers only)

This configuration task is optional.

By default, the mt-showcase-reporting database is created on the local application computer. But if required, you can deploy a reporting database on a remote PostgreSQL server. For example, you may want to do this if MT Showcase is running on multiple video walls and you want to store event records from these video walls in a single database.

To connect the MT Showcase application computer to a PostgreSQL reporting database on a remote Windows computer, follow the sections below.

Install PostgreSQL on the Remote Windows Computer

Download and install PostgreSQL from the EnterpriseDB web site: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Note

• For recommended PostgreSQL versions, see Recommended Versions of PostgreSQL.

• The installation wizard assigns port 5432 to the default cluster. We recommend you accept this default port.

• For simplicity, we recommend that you use multi as the password for the postgres admin user.

Create the Reporting Database and Database User (Remote Windows)

Now set up a reporting database and database user on the remote Windows computer. For simplicity, we recommend you use the same value for the database name, user name and password. (Note that the required tables are created when you start the MT Showcase server.)

The setup procedure on a Windows computer is the same, whether you are creating a local or remote reporting database, so follow these steps:

1. Log on to the remote Windows computer.

2. Follow the steps in Create the Reporting Database and a Database User.

Configure PostgreSQL to Listen for External Connections (Windows)

Follow these steps on the remote Windows computer:

1. Configure the PostgreSQL server to listen for external connections.

   1. Edit postgresql.conf on the PostgreSQL server. Find this file in: C:\Program Files\PostgreSQL\<version>\data

   2. Set the listen_addresses setting to:

      listen_addresses = '*'
      

2. Allow password authentication to the remote database from external connections.

   1. Edit pg_hba.conf on the PostgreSQL server. Find this file in: C:\Program Files\PostgreSQL\<version>\data

   2. Add the following line to pg_hba.conf:

      host mt-showcase-reporting all samenet md5
      

3. Restart the PostgreSQL service. You can either use the Services applet in Windows Administrative Tools or the Services tab in Task Manager. (The PostgreSQL service name is postgresql-x64-<version>. For example, if you installed PostgreSQL 9.6.2, the service name is postgresql-x64-9.6.)

Configure MT Showcase to Use the Remote Reporting Database (Windows)

Now you must configure MT Showcase to use the remote reporting database. Follow these steps on your MT Showcase application computer:

1. Edit production_users.yaml. For the file location, see the Where Are My Files? section.

2. Replace the existing reporting_db line with this line:

   reporting_db: postgres://<address>/mt-showcase-reporting?port=5432&user=mt-showcase-reporting&password=<password>
   

   Where:

   • <address> is the IP address of the remote PostgreSQL server.

   • mt-showcase-reporting is the remote database you created above.

   • port=5432 is the port that the reporting database listens on. This is the default port number specified by the PostgreSQL installer.

   • user=mt-showcase-reporting is the user you created above.

   • <password> is the password you supplied above.

For further information about setting up a PostgreSQL server, we recommend: https://help.ubuntu.com/community/PostgreSQL

Restart the MT Showcase Server (Remote Windows)

Now restart the MT Showcase server to create the required database tables; see the Start or Stop MT Showcase section.

When the server restarts, you can enable data gathering; see Enable Data Gathering.

Set Up a PostgreSQL Reporting Database on a Remote Ubuntu Computer

(Applies to Ubuntu computers only)

This configuration task is optional.

By default, the mt-showcase-reporting database is created on the local application computer. But if required, you can deploy a reporting database on a remote PostgreSQL server. For example, you may want to do this if MT Showcase is running on multiple video walls and you want to store event records from these video walls in a single database.

To connect the MT Showcase application computer to a PostgreSQL reporting database on a remote Ubuntu computer, follow the sections below.

Install PostgreSQL on the Remote Ubuntu Computer

Download and install PostgreSQL from the EnterpriseDB web site: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Note

• For recommended versions, see Recommended Versions of PostgreSQL.

• The installation wizard assigns port 5432 to the default cluster. We recommend you accept this default port.

Create the Reporting Database and Database User (Remote Ubuntu)

Now set up a reporting database and database user on the remote Ubuntu computer. For simplicity, we recommend you use the same value for the database name and user name. (The required tables are created when you start the MT Showcase server.)

1. Right-click the desktop and launch a terminal emulator.

2. Run this command to log into PostgreSQL as the postgres admin user:

   sudo -u postgres psql
   

3. Create the database and database user. For example, you can run the psql commands below. These commands use mt-showcase-reporting as the database name and user name and x6dgfn8 as the user password. Take careful note of the syntax!

   CREATE USER "mt-showcase-reporting" PASSWORD 'x6dgfn8';
   CREATE DATABASE "mt-showcase-reporting" OWNER "mt-showcase-reporting";
   GRANT ALL ON DATABASE "mt-showcase-reporting" TO "mt-showcase-reporting";
   ALTER USER "mt-showcase-reporting" VALID UNTIL 'infinity';
   ALTER USER "mt-showcase-reporting" WITH PASSWORD 'x6dgfn8';
   

   You will reference this database and database user when configuring MT Showcase.

4. Type \q to quit psql.

Configure PostgreSQL to Listen for External Connections (Ubuntu)

Follow these steps on your remote Ubuntu computer:

1. Configure the PostgreSQL server to listen for external connections.

   1. Edit postgresql.conf on the PostgreSQL server. Find this file in: /etc/postgresql/<version>/main

   2. Set the listen_addresses setting to:

      listen_addresses = '*'
      

2. Allow password authentication to the remote database from external connections.

   1. Edit pg_hba.conf on the PostgreSQL server. Find this file in: /etc/postgresql/<version>/main

   2. Add the following line:

      host mt-showcase-reporting all samenet md5
      

3. Run the following command to restart the PostgreSQL server:

   sudo /etc/init.d/postgresql restart
   

Configure MT Showcase to Use the Remote Reporting Database (Ubuntu)

Now you must configure MT Showcase to use the remote reporting database. Follow the steps in Configure MT Showcase to Use the Remote Reporting Database (Windows).

Restart the MT Showcase Server (Remote Ubuntu)

Now restart the MT Showcase server to create the required database tables; see the Start or Stop MT Showcase section.

When the server restarts, you can enable data gathering; see Enable Data Gathering.

Enable Data Gathering

To enable data gathering, you simply add the data gathering service to a service set, and then add that service set to your MT Showcase app. You do this in the MT Showcase Editor. For instructions, see the MT Showcase Editor Manual.

After you enable data gathering for an app, MT Showcase tracks content usage and generates event records whenever the app is running.

Useful psql Commands

psql is a command line interface for working with PostgreSQL databases. This section lists some useful psql commands for managing the mt-showcase-reporting database.

• Connect to the mt-showcase-reporting database:

  psql -p 5434 -U mt-showcase mt-showcase-reporting
  

  Where 5434 is the port that mt-showcase-reporting listens on.

• List recent event records:

  select * from events order by id desc limit 10;
  

• List event records that contain "video-start":

  select * from events where event->>'event' = 'video-start'
  order by id desc limit 10;
  

Example Database Views

This section contains SQL commands for creating simple views of the mt-showcase-reporting database.

Inputs by Location and Timestamp

The database view, input_tracking, lists all inputs (touch events) on the video wall by screen location and timestamp. It includes three columns: object, location, and timestamp. The object column shows the type of input (finger, hand, pen, Codice).

Execute the following commands:

DROP VIEW IF EXISTS input_tracking;

CREATE VIEW input_tracking AS
  SELECT events.event->>'object-type' AS object,
         events.event->>'location' AS location,
         events.event->>'timestamp' AS timestamp
  FROM events WHERE events.event->>'event' = 'object-down';

SELECT * FROM input_tracking;

Asset Usage Counts

The database view, asset_usage_count, generates usage counts for individual media library assets. That is, this view counts how many times individual assets were loaded into a widget. It includes two columns: asset and count.

For example, you can use this view to calculate how many times your users opened specific images, videos or PDFs from a finger menu.

Execute the following commands:

DROP VIEW IF EXISTS asset_usage_count;

CREATE VIEW asset_usage_count AS
  SELECT events.event->>'asset' AS asset,
  COUNT (*)
  FROM events WHERE events.event->>'event' = 'asset-loaded'
  GROUP BY asset;

SELECT * FROM asset_usage_count;

Asset Usage by App

The database view, asset_usage_count_per_app, generates usage counts for media library assets by app. This time, the view counts how many times individual assets were loaded into a widget in each app. It includes three columns: asset, application and count.

Execute the following commands:

DROP VIEW IF EXISTS asset_usage_count_per_app;

CREATE VIEW asset_usage_count_per_app AS
  SELECT events.event->>'asset' AS asset,
         events.event->>'application' AS application,
         COUNT (*)
  FROM events WHERE events.event->>'event' = 'asset-loaded'
  GROUP BY asset, application;

SELECT * FROM asset_usage_count_per_app;

Identifying Related Events

A single user action, such as starting a video or browsing a PDF, typically generates several event records. This section briefly explains how to identify related event records.

Note

For details about event types and the fields in event records, see Event Types.

• Identify which widget an event record refers to

  Most event records include a widget ID. But to identify which widget this ID refers to in your app, you must ensure that your widgets are named in the Editor. You can then examine a widget-created event record to compare the name and widget-id fields:

  1. Before you enable the data gathering service, assign a name to the widget in the Editing a structure screen of the MT Showcase Editor.

  2. In the mt-showcase-reporting database, locate the widget-created event record with this widget name in its name field.

  3. Note the value in the widget-id field of this widget-created record.

  4. You can now search for all event records with this widget-id.

• Match an object-down event with an object-up event

  Simply search the database for object-down and object-up events with a matching object-id.

• Identify related video events

  To identify which video file was started or ended, you need to match the video-start event to an asset-loaded event:

  1. Note the widget-id of the video-start event.

  2. Search for the corresponding widget-id in the asset-loaded event.

  3. Examine the asset field in the asset-loaded event.

• Identify which PDF was viewed

  You need to match the pdf-page-viewed event to an asset-loaded event:

  1. Note the widget-id of the pdf-page-viewed event.

  2. Search for the corresponding widget-id in the asset-loaded event.

  3. Examine the asset field in the asset-loaded event.

Event Types

The table below lists the event types recorded by the data gathering service, plus the fields in each event record. Note that all event records, regardless of event type, include app and video wall identifiers plus a timestamp.

Note

Example event records are listed in Example Output Records.

All events

All event records include the app name, the event type, the site name, and the event timestamp. The site field identifies which video wall the app was running on. It corresponds to the Site Name attribute in your app's service set.

Fields: application, event, site, timestamp

application-started

Shows when an app starts. No additional fields.

application-quit

Shows when an app closes. No additional fields.

object-down

Records the start of a touch event (i.e., when a finger, hand, pen or Codice marker touches the screen). The location field gives the pixel coordinates of the touch event. The widget-id field identifies the widget that was touched. The codice-code field is only included if the touch event involves a Codice marker.

Fields: object-id, location, type, widget-id, codice-code

object-up

Records the end of a touch event (i.e., when a finger, hand, pen or Codice marker lifts from the screen). The location field gives the pixel coordinates of the touch event. The object-id field matches an object-down event to its corresponding object-up event.

Fields: object-id, location

widget-created

Records when a widget launches, including the ID of the 'creator' widget (for example, a finger menu or content hotspot). The name and component fields identify the user-defined name of the widget (if assigned) and the widget type (e.g., "PDF book").

Fields: creator-id, widget-id, name, component

widget-destroyed

Records when a widget is closed. The possible reasons are:

• "Off screen" -- User dragged the widget off-screen.

• "User closed" -- User tapped the Close button.

• "Timed out" -- The 'idle widget' timeout expired.

Fields: widget-id, reason

asset-loaded

Records when an asset (image, video or PDF) is displayed in a widget. The asset field identifies the asset in the media library.

Fields: widget-id, asset

video-start

Records when video started playing in a Video Viewer widget. The position field shows where the video playback started, measured in seconds on the widget's progress bar. (The corresponding asset-loaded event identifies the video file. Use the widget-id field to link the asset-loaded and video-start events.)

Fields: widget-id, position

video-end

Records when video stopped playing in a Video Viewer widget. The position field shows the time on the progress bar (in seconds) when the video stopped. The possible reasons are:

• "paused" -- User paused the video

• "end of video" -- Video played to the end

• "closed" -- User closed the Video Viewer widget

• "seeking" -- User fast-forwarded on the progress bar

Fields: widget-id, position, reason

widget-added-to-personal-space

Records when a user drags a widget into their personal space. The codice-code field identifies the Codice marker presented by the user. name and address are the user's name and email address.

Fields: widget-id, codice-code, name, address

widget-removed-from-personal-space

Records when a user drags a widget out of their personal space. The codice-code field identifies the Codice marker presented by the user to open their personal space.

Fields: widget-id, codice-code

email-sent

Records when a user tries to send a widget from their personal space to their registered email address. name and address identify the user's name and registered email address. success and error-string indicate whether the send operation succeeded.

Fields: name, address, success, error-string

widget-emailed

Identifies the widget sent as an attachment from a user's personal space to their registered email address. name and address identify the user's name and registered email address.

Fields: widget-id, name, address

pdf-page-viewed

Records when a user viewed a page in a PDF document. If the user views multiple pages, multiple records are generated. (The corresponding asset-loaded event identifies the PDF file. Use the widget-id field to link the asset-loaded and pdf-page-viewed events.)

Fields: widget-id, page

url-changed

Records when a user browses to a new URL in a web browser widget.

Fields: widget-id, url

Example Output Records

• application-started

  49575 | {
    "application":"MT Showcase Editor Tutorial",
    "event":"application-started",
    "site":"Helsinki Mezzanine",
    "timestamp":"Tue Apr 25 15:31:07 2017"
  }
  

• application-quit

  53149 | {
    "application":"MT Showcase Editor Tutorial ",
    "event":"application-quit",
    "site":"Helsinki Mezzanine",
    "timestamp":"Wed Apr 26 11:19:13 2017"
  }
  

• object-down

  53147 | {
    "application":"MT Showcase Editor Tutorial ",
    "event":"object-down",
    "location":"673.875, 656.828",
    "object-id":"234",
    "object-type":"pen",
    "site":"Helsinki Mezzanine",
    "timestamp":"Wed Apr 26 10:46:04 2017",
    "widget-id":"25051613340069428"
  }
  

• object-up

  53146 | {
    "application":"MT Showcase Editor Tutorial ",
    "event":"object-up",
    "location":"713.567, 674.87",
    "object-id":"233",
    "site":"Helsinki Mezzanine",
    "timestamp":"Wed Apr 26 10:46:04 2017"
  }
  

• widget-created

  52830 | {
    "application":"MT Showcase Editor Tutorial ",
    "creator-id":"25051613340068136",
    "event":"widget-created",
    "site":"Helsinki Mezzanine",
    "timestamp":"Wed Apr 26 10:45:38 2017",
    "widget":{
       "widget-id":"25051613340069428",
       "name":null,
       "component":"PDF book"
     }
  }
  

• widget-destroyed

  26522 | {
    "application":"MT Showcase Editor Tutorial ",
    "event":"widget-destroyed",
    "reason":"Off screen",
    "site":"Helsinki Mezzanine",
    "timestamp":"Fri Apr 21 14:36:34 2017",
    "widget-id":"25044425793842259"
  }
  

• asset-loaded

  52833 | {
    "application":"MT Showcase Editor Tutorial ",
    "asset":"assets:MT Showcase Editor Tutorial/PDFs/MT Showcase 1.9 Editor Manual.pdf",
    "event":"asset-loaded",
    "site":"Helsinki Mezzanine",
    "timestamp":"Wed Apr 26 10:45:38 2017",
    "widget-id":"25051613340069428"
  }
  

• video-start

  49154 | {
    "application":"MT Showcase Editor Tutorial ",
    "event":"video-start",
    "position":0,
    "site":"Helsinki Mezzanine",
    "timestamp":"Tue Apr 25 14:34:14 2017",
    "widget-id":"25050518444296556"
  }
  

• video-end

  49062 | {
    "application":"MT Showcase Editor Tutorial ",
    "event":"video-end",
    "position":118.80157470703125,
    "reason":"paused",
    "site":"Helsinki Mezzanine",
    "timestamp":"Tue Apr 25 14:33:34 2017",
    "widget-id":"25050515780255188"
  }
  

• widget-added-to-personal-space

  51336 | {
    "application":"MT Showcase Editor Tutorial ",
    "codice-code":"364",
    "event":"widget-added-to-personal-space",
    "site":"Helsinki Mezzanine",
    "timestamp":"Wed Apr 19 14:43:25 2017",
    "widget-id":"25041771821196402"
  }
  

• widget-removed-from-personal-space

  51859 | {
    "application":"MT Showcase Editor Tutorial ",
    "codice-code":"364",
    "event":"widget-removed-from-personal-space",
    "site":"Helsinki Mezzanine",
    "timestamp":"Tue Apr 11 17:09:48 2017",
    "widget-id":"25041771821196402"
  }
  

• email-sent

  52719 | {
    "application":"MT Showcase Editor Tutorial ",
    "email":"spencer.rimmel@unipraxis.com",
    "event":"email-sent",
    "site":"Helsinki Mezzanine",
    "success":true,
    "timestamp":"Tue Apr 11 16:11:31 2017"
  }
  

• widget-emailed

  52145 | {
    "application":"MT Showcase Editor Tutorial ",
    "email":"spencer.rimmel@unipraxis.com",
    "event":"widget-emailed",
    "name":"Spencer Rimmel",
    "site":"Helsinki Mezzanine",
    "timestamp":"Tue Apr 25 15:41:30 2017",
    "widget-id":"25050518444306561"
  }
  

• pdf-page-viewed

  53138 | {
    "application":"MT Showcase Editor Tutorial ",
    "event":"pdf-page-viewed",
    "page":9,
    "site":"Helsinki Mezzanine",
    "timestamp":"Wed Apr 26 10:46:01 2017",
    "widget-id":"25051613340069428"
  }
  

• url-changed

  52701 | {
    "application":"MT Showcase Editor Tutorial ",
    "event":"url-changed",
    "site":"Helsinki Mezzanine",
    "timestamp":"Wed Apr 26 10:45:12 2017",
    "url":"https://www.multitaction.com/software/mt-showcase",
    "widget-id":"25051613340069182"
  }