Advanced Importing Concepts

Importing from an ODBC compliant data source

The Data Access window gives you the freedom to import data from a wide variety of sources. It contains data connectors (ODBC drivers) that provide an interface between Analytics and ODBC-compliant databases and file formats, such as Microsoft SQL Server, Oracle, Salesforce, and Microsoft Excel.

The Data Access window

In the Importing from an ODBC-compliant data source topic, we examined how to import a table from a data base using the Data Access window.

The power of the Data Access window really comes into play by allowing you to:

Use SQL syntax to extract specific data
Perform SQL joins between two or more tables before importing it as one table into Analytics
Script ODBC imports via the Analytics Log

It's all about SQL

The basics

SQL syntax can be used when importing data from a database through the Data Access window. By writing import queries beyond what the interface supports, functionality is extended to the SQL universe. This is useful because it allows us to specifically extract just what we need, which saves on analysis time and disk space.

Tips to get started

Build the logic for the table you want to import using the interface for the data access window, then flip the toggle to SQL mode. This shows you the SQL syntax behind your filters. Use this as a starting point.
Read up on SQL queries. There's lots of resources available online. We suggest you focus on these concepts:
- SELECT
- WHERE
- AND/OR
- TOP
- LIKE
- ORDER BY
- GROUP BY
- DISTINCT
- JOIN
- LEFT & RIGHT
- INNER & OUTER
- UNION
- Sub Queries

Example

In SQL mode, the syntax below grabs all the fields from both the Expense_reports and Expense_details tables, using the * wildcard.

SELECT *
FROM
   `Expense_reports`
LEFT JOIN
   `Expense_details` `Expense_details`
       ON `Expense_reports`.`report_number` = `Expense_details`.`report_number`

Note

This is fully scriptable in Analytics. Copy and paste the import statement from the log into your Analytics script.

For more on editing the SQL import statement, see Import data using the Data Access window in the help documentation

SQL Joins

If you're familiar with ACL joins, there's two key differences worth noting for SQL joins.

SQL joins are used only in the Data Access window when importing two or more tables from a database as one joined table.
In basic ACL joins, only the first 'match' in the secondary table is included in the joined table. In SQL, every match is included.

Example: ACL Join vs SQL Join

If Table A was joined with Table B, using the key fields of Name A and Name B, how many matches would a SQL join produce compared to an ACL join?

Table A (primary)
1A	Angel

Table A (primary)

Record

Name A

Angel

Table B (secondary)
Record	Name B
1B	Angel
2B	Angel

Join Results

Number of matches

Joined table

ACL

1 - ACL takes only the first instance where there is a match.

Record	Name A	Record	Name B
1A	Angel	1B	Angel

SQL

2 - SQL takes all instances where there is a match and replicates the original record for each match.

Record	Name A	Record	Name B
1A	Angel	1B	Angel
1A	Angel	2B	Angel

SQL Join Types

The Data Access window allows you to choose from four SQL join types on the interface:

Venn	SQL Join	Produces
	Inner (No Unmatched)	Only matched records.
	Primary (Left)	Matched records and unmatched records from the left.
	Secondary (Right)	Matched records and unmatched records from the right.
	Outer	Matched records, unmatched records from the left, and unmatched records from the right.

Example

In the two tables below, 'Bob,' 'Angel,' and 'Vince' all have matches. 'Sasha' exists in the table on the left, but not in the table on the right while 'Ruth' exists in the table on the right, but not in the table on the left.

Table A
Record	Name A
1A	Bob
2A	Angel
3A	Vince
4A	Sasha

Table B
Record	Name B
1B	Ruth
2B	Bob
3B	Angel
4B	Vince
5B	Angel

Join Results

Venn

Join type

Joined table

Inner join produces:

only the records that match in both tables

Record	Name	Record	Name
1	Bob	2	Bob
2	Angel	3	Angel
3	Vince	4	Vince
2	Angel	5	Angel

Primary (Left) join produces:

records that match in both tables
unmatched records from the left, recorded as a null on the right

Record	Name	Record	Name
1	Bob	2	Bob
2	Angel	3	Angel
3	Vince	4	Vince
4	Sasha	null	null
2	Angel	5	Angel

Secondary (Right) join produces:

records that match in both tables
unmatched records from the right, recorded as a null on the left

Record	Name	Record	Name
null	null	1	Ruth
1	Bob	2	Bob
2	Angel	3	Angel
3	Vince	4	Vince
2	Angel	5	Angel

Outer join produces:

records that match in both tables
unmatched records from the left, recorded as a null on the right
unmatched records from the right, recorded as a null on the left

Record	Name	Record	Name
null	null	1	Ruth
1	Bob	2	Bob
2	Angel	3	Angel
3	Vince	4	Vince
4	Sasha	null	null
2	Angel	5	Angel

To read more about joining tables prior to importing using the Data Access window, see Joining tables in the Data Access window in the help documentation.