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
- B. On the Host Machine: Install the Oracle Driver and Set Up Data Source
- C. In the FileMaker Database: Configure FileMaker ODBC Connectivity
- Create an External Data Source for the Data Warehouse in FileMaker
- Add Data Warehouse-Based Table Occurrences in FileMaker
- Related Links
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.
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:
- Windows: Accessing the Data Warehouse with FileMaker - Configuring ODBC for Windows
- Macintosh: Accessing the Data Warehouse with FileMaker - Configuring ODBC for Macintosh
Note: IS&T recommends use of its Managed Servers.
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.
- Open your database in FileMaker Pro or FileMaker Advanced.
- Go to File > Manage > External Data Sources.
- Click New.
- In the Name field, type warehouse.
- Select Type: ODBC.
- 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.
- 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.
- Click the Specify button next to the DSN field.
- Select warehouse from the list of data source names, and click OK.
- Click OK to close the Edit Data Source window.
- Click OK to close the Manage External Data Sources window.
- Open your database in FileMaker and go to File > Manage > Database > Relationships.
- Click on the Specify Table button:
- Select warehouse from the data source pull-down menu.
- Enter your Data Warehouse username and password and click OK.
- 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.
- 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.
- The Data Warehouse Table is added to the relationships graph in FileMaker, and is available for use in your database.