Importing from an ODBC-compliant data source
Objectives
- Import an ODBC-compliant file into Analytics
About ODBC
Open DataBase Connectivity (ODBC) is a standard database access method developed by the SQL Access group. ODBC was developed to allow access to any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC achieves this by inserting a middle layer, called a database driver, between an application and the DBMS. This middle layer translates the application’s data queries into commands that the DBMS understands.
For this to work, both the application and the DBMS must be ODBC-compliant - that is, the application must be able to issue ODBC commands and the DBMS must be able to respond to them. Analytics is ODBC-compliant. To use it on a DBMS that is also ODBC-compliant, an ODBC driver is required.
Setting up an ODBC Driver
An ODBC Driver needs to be setup on the same computer that is running Analytics. This is a one-time setup, typically done by IT personnel. Installed ODBC drivers can be found in the Windows Control Panel under Administrative Tools > Data Sources (ODBC).
Note
Some databases will have restricted access, in which case you’ll need a user name and password to allow read-only access.
The Data Access window
The Data Access window is a component of Analytics that contains data connectors which you can use to import data to Analytics from a wide range of sources. For a complete list, see Data sources you can access with Analytics.
The Data Access window also contains features for filtering and joining tables prior to importing.
Approach
- Open an existing project or create a new project and open the Data Access window:
- Select Import > Database and application.
Connect to a data source menu appears. - Under Existing Connections, select MS Access Database (Microsoft Access Driver *.mdb, *accdb)- 32 bit.
- Select Import > Database and application.
- Locate and select the database you want to access and click OK.
The Data Access window opens, showing the database connection and the database. It also lists the available tables. - Click on the table you want to import from the list of Available Tables.
It will show up in the staging area. - From the staging area you can:
- Click Add filters to limit the results to filter for specific conditions.
- Click the join icon
to perform a SQL join if multiple tables were selected.
Note
SQL joins can only be performed in the Data Access window and are different from Analytics joins.
- In the Import Preview section, click Refresh.
A preview of your table appears. - Click Save.
The Save File As dialog box appears. - Enter a file name for the project which will save as a *.fil, and click Save.
Your table is imported into Analytics.
Considerations
- One database and its tables can be viewed at a time.
- Tables can be joined and filtered prior to importing, which means you import only the data you need, minimizing the local disk space required for your imported table.
- The imported fields and records are written to a fixed-width file with a .fil extension. Subsequent analysis is performed against this file.
- Consult your IT department on the best time to access a database to minimize load during peak times.
- The features in the Data Access window use SQL to communicate with the database you are accessing. This can be performed via the interface, or in SQL mode - which allows more specific queries to be scripted.
Folder: ODBC Import
Source Database: TaftDatabase.mdb
Tables: InventoryODBC, Sales2016ODBC
From the TaftDatabase.mdb Access database, import the InventoryODBC table. While you are in the Data Access window, filter for the records that have a quantity on hand less than 100 and a product class of 02.
- Select Import > Database and application.
The Connect to a data source menu appears. - Under Existing Connections, select MS Access Database (Microsoft Access Driver (*.mdb, *accdb)))- 32 bit.
- Locate the folder where your activity data files are saved. Navigate to Case Study - Taft Industry > ODBC Import > TaftDatabase.mdb, and click OK.
The Data Access window opens, listing the available tables in the Taft database. - Under Available Tables, click on InventoryODBC.
The table appears on the staging area. -
To add the first filter, click Add filters to limit the results:
- Under the Field drop down, select `InventoryODBC`.`Quantity_On_Hand`.
- Under the Condition drop down, select less than.
- In the blank drop down, type 100.
- To add the second filter, click Add filter:
- Click AND.
- Under the Field drop down, select `InventoryODBC`.`Product_Class`.
- Under the Condition drop down, select is.
- Under the blank drop down, type 02.
- In the Import Preview section, click Refresh.
A preview of your table appears. - Click Save.
The Save File As dialog box appears. - Save the file as InventoryODBC.fil, and click Save.
Your table is imported into Analytics.
Advanced ODBC imports
You may have noticed a few things in the Data Access window while you were navigating the activity:
- The
toggle. This switches to a text box where you can freely edit or insert SQL syntax to extract specific data. - More than one table can be placed into the staging area, which allows them to be joined.
These are handy features, however are considered advanced topics - so we've put together introductory material of Advanced Importing Concepts, to get you started in the appendix.
Caution
SQL joins are different from ACL joins. So unless you're an SQL pro, maybe leave this until the end of the course.