Access Keys:
Skip to content (Access Key - 0)

Accessing the Data Warehouse with FileMaker - Configuration Instructions

Access to MIT's Data Warehouse within FileMaker using FileMaker's External SQL Sources (ESS) feature requires FileMaker 9 or later. IS&T recommends using the most current version distributed at MIT.

Setting up an ESS connection to the Data Warehouse can be grouped into the following steps:

A. Request Access to the MIT Data Warehouse

If you do not already have access to the MIT Data Warehouse, request it online on the MIT Data Warehouse web page. All users who will access Warehouse data via the ESS feature must also obtain access to the MIT Data Warehouse. Please allow at least one full working day beyond the day of your request for your access to be activated.

B. On the Host Machine: Install the Oracle Driver and Set Up Data Source

The first configuration step is to set up an ODBC data source for the Data Warehouse on the machine where the FileMaker database lives. If the database is hosted with FileMaker Server (strongly recommended), this is the server machine; if it's a local single-user file, this is the user's machine. You'll need to download and install the Oracle driver which matches the appropriate machine's platform, and then use an ODBC administrator application to create the data source name (DSN) on the system. For instructions specific to your platform, follow the appropriate link below:

Note: IS&T recommends use of its Managed Servers.

C. In the FileMaker Database: Configure FileMaker ODBC Connectivity

The next step is to define the connection between the FileMaker database and the Data Warehouse. This involves creating a link from the FileMaker database to the Warehouse DSN, and then adding the desired Warehouse tables to the FileMaker database as shadow table occurrences.

Create an External Data Source for the Data Warehouse in FileMaker

  1. Open your database in FileMaker Pro or FileMaker Advanced.
  2. Go to File > Manage > External Data Sources.
  3. Click New.
    Manage External Data Sources Window
  4. In the Name field, type warehouse.
  5. Select Type: ODBC.
  6. Under Filter by Type, check the box for Views. You may also check the boxes for Tables and System Tables, if desired, but most users will not need to do so.
  7. Under Schema Name, type WAREUSER. (Optional, but recommended.)
    Important Security Note: Make sure that Authentication is set to Prompt user for user name and password. Do not specify a username or password.


    Edit Data Source Window

  8. Click the Specify button next to the DSN field.
  9. Select warehouse from the list of data source names, and click OK.
    Edit Data Source Window
  10. Click OK to close the Edit Data Source window.
  11. Click OK to close the Manage External Data Sources window.
    Manage External Data Sources Window

Add Data Warehouse-Based Table Occurrences in FileMaker

  1. Open your database in FileMaker and go to File > Manage > Database > Relationships.
  2. Click on the Specify Table button:

    Relationships tab
  3. Select warehouse from the data source pull-down menu.
    Data Source Drop-Down Menu
  4. Enter your Data Warehouse username and password and click OK.
    Username and Password Boxes
  5. A list of Data Warehouse tables and views is displayed. Only tables and views you are authorized to access are included. Select the desired table or view and click OK. If you are not certain which table contains the desired data, consult the list of all tables, or contact the Data and Reporting Services team.
    List of Data Warehouses Tables
  6. You may be prompted to select a unique key. Check the box for the field that contains the unique key for that table and click OK. If you are not sure which field to choose, contact the Data and Reporting Services team.
    Select Unique Key window
  7. The Data Warehouse Table is added to the relationships graph in FileMaker, and is available for use in your database.
    Relationships tab with new Data Warehouse Table

Related Links

Warehouse tables not displayed in FileMaker with ODBC connection to Data Warehouse

Data and Reporting Services at MIT

IS&T Contributions

Documentation and information provided by IS&T staff members


Last Modified:

July 14, 2016

Get Help

Request help
from the Help Desk
Report a security incident
to the Security Team
Labels:
c-style c-style Delete
c-filemaker c-filemaker Delete
c-filemaker-integration c-filemaker-integration Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
Feedback
This product/service is:
Easy to use
Average
Difficult to use

This article is:
Helpful
Inaccurate
Obsolete
Adaptavist Theme Builder (4.2.3) Powered by Atlassian Confluence 3.5.13, the Enterprise Wiki