Accessing the Data Warehouse with FileMaker - Overview

On this page:

FileMaker External SQL Sources (ESS) Overview

The FileMaker External SQL Sources (ESS) feature, which creates a live, persistent connection to an external SQL data source, allows tables living in a system such as the MIT Data Warehouse to be added to the FileMaker relationship graph as shadow tables and accessed from within FileMaker. Data in these shadow tables is live and up-to-date with the external source. The user's permission to view ESS data in FileMaker is governed by the authorizations granted by the external data source (additional restrictions may be defined at the FileMaker privilege set level, though there is rarely a need to do this).

Connecting FileMaker solutions to MIT's Data Warehouse using ESS is the recommended method for integrating data from other systems of record at MIT. Setting up an ESS connection to the Warehouse can generally eliminate the need for local import and/or storage of such data. Note that locally stored data originating from the Warehouse is at risk of becoming stale, while locally stored sensitive data poses a far greater security risk than data stored in the Warehouse. When Warehouse data is displayed in a FileMaker solution using ESS, the user must first authenticate to the Warehouse, and the user's Warehouse authorizations govern what data the user is able to see. Since the Warehouse data is not actually stored in the FileMaker file, unauthorized users will not be able to view the external Warehouse data.

Prerequisites

Recommended prerequisites for setting up a FileMaker ESS connection to the MIT Data Warehouse:

• Host the database with FileMaker Server if at all possible. IS&T strongly recommends the use of its Managed Servers.
• Identify all users who will need access to Warehouse data within FileMaker, and then request access for them: Data Warehouse authorizations.

Configuration Instructions

Configuring FileMaker to connect to the Data Warehouse is a multi-step process. Please see: Accessing the Data Warehouse with FileMaker - Configuration Instructions

Security Guidelines

• Do not store an individual's Data Warehouse username and password in FileMaker databases. During the setup of the ESS connection, enable the setting to "Prompt user for user name and password." Note: In certain cases, it may be permitted obtain a limited access DW system account used only for querying non-sensitive data, such as MIT directory information. It may be acceptable in these cases to store the credentials for this type of account in FileMaker. If you would like to request this type of account, send an email to warehouse-admin@mit.edu.
• Do not share Data Warehouse passwords with anyone.
• Do not import Warehouse data into local FileMaker tables unless you have a good reason to do so.

More Information

MIT Resources

• Warehouse tables not displaying
• For troubleshooting assistance with FileMaker connections to the Data Warehouse, please contact filemaker-support@mit.edu.
• FileMaker - MIT Security Guidelines

Documentation from FileMaker, Inc.

• Introduction to External SQL Sources
• FileMaker ODBC and JDBC Guide

Related Links

• Data and Reporting Services at MIT