Working with the Data Access window

You can import data and create an Analytics table by using the Data Access window to connect to source data in either a database or a file.

Before you connect to a database or a cloud data service

Certain requirements or prerequisites exist when using the Data Access window to connect to a database or a cloud data service:

• An account You must have a database or data service account. The account may be a personal account, or an account associated with a role or with your company. This account is completely separate from your Analytics account.
• Credentials You need valid credentials for the database or data service account. The type of account credentials required depend on the data source. You may need to enter a user name and password, an access token, or some other form of credential.
• Prior configuration For data sources such as Salesforce and Concur, prior configuration is required within the cloud data service before you can connect using the Data Access window.

  Note

  Configuration of connection prerequisites within a cloud data service is typically performed by the person in your company responsible for administering the service – for example, your company's Salesforce administrator, or Concur administrator.

  For connection issues that originate with the cloud data service you need to contact your company's administrator for that service rather than Support.

Connect to the database or the file

From the Analytics main menu, select Import > Database and application.

Use an existing connection

1. In the Existing Connections tab, select the connection for the data source you want to connect to.

   Tip

   You can filter the list of available connections by entering a search string in the Filter connections box. Connections are listed alphabetically.

   For some types of connections, you are immediately connected to the source data.

   The existing connections are organized under ACL Connectors, ACL DSN Connectors (Bundled), Windows DSN Connectors, and Other Connectors.

   If you do not have any existing connections, the section or sections do not appear.

2. If you are not connected immediately, do one of the following:
   • If the Data Connection Settings panel opens, click Connect at the bottom of the panel (you may need to scroll).

     If required, you can update the connection settings before you click Connect.

   • If a file selection dialog box opens, navigate to the appropriate file and select it.

Create a new connection

1. Select the New Connections tab.
2. Select the connector for the data source you want to connect to.

   Tip

   You can filter the list of available connectors by entering a search string in the Filter connections box. Connectors are listed alphabetically.

   The available connectors are organized under ACL Connectors, ACL DSN Connectors (Bundled), Windows DSN Connectors, and Other Connectors.

3. Do one of the following:
   • If the Data Connection Settings panel opens, enter the connection settings, and click Save and Connect at the bottom of the panel (you may need to scroll).

     You can accept the default Connection Name, or enter a new one.

   • For connectors in the ACL DSN Connectors (Bundled) section, the DSN Configuration dialog box opens.
     1. Click the Show Required tab.
     2. Provide values for the required fields, if any, and click Test Connection.

        The log in page for your data source appears.

     3. Provide the connection details and authenticate your login.
     4. Click OK in the connection successful dialog box that appears.
     5. In the DSN Configuration dialog box, click OK.
   • If a file selection dialog box opens, navigate to the appropriate file and select it.

   Note

   Successful connections made with an Analytics connector are automatically saved to the Existing Connections tab.

   Connections made with Windows connectors are preserved for the current data import session only.

Manage data connections

You can rename, copy, or delete a connection created using an ACL connector. For more information, see Managing data connections.

Add one or more tables to the Staging Area

1. In the Data Access window, select the appropriate database schema from the Schema drop-down list, if required.

   Note

   Some data sources may not have a schema, or may have only one schema.

2. Optional. In the Connection panel, filter the list of available tables by entering a search string in the Search tables box.

   Matches for a literal search string (no wildcards) can appear anywhere in a table name. The search is not case-sensitive.

   You can also use one or more wildcard characters in the search string.

   Show me more

   Wildcard	Scope	Example	Matches
   * %	0 or more characters	invoice-j*	Invoice-January Invoice-June Invoice-July
   		%june	Invoice-June PO-June
   		%invoice%	Invoice-June June-Invoice
   ? _	1 character	invoice??	invoice-1 invoice-2
   		j_n	Jan Jun
   Note If you use a wildcard character in the search string, the length of matching strings, and the first and last characters, are evaluated more strictly: j_n matches only three-character strings that begin with j and end with n j*n matches strings of any length, but the strings must begin with j and end with n by contrast, jan matches strings of any length, and jan can appear anywhere in the string This matching behavior is the result of Analytics  interpreting literal search strings (no wildcards) in the following manner: jan = *jan*

3. Optional. Scroll to the bottom of the list of tables and click Show remaining # tables.

   Analytics displays the first 200 tables in the data source. If additional tables exist, you can click the Show remaining link to display them in blocks of up to 500 tables at a time.

   Note

   The Search tables box must be empty for the link to appear.

4. Under Available Tables, click a table name to add the table to the Staging Area.

   Tables are listed alphabetically. You can add up to ten tables to the staging area if you intend to join the tables. The SAP connector is currently limited to two tables.

   Note

   You cannot import multiple tables individually with one import operation. The tables must be joined to be imported together.

5. Optional. Select Include System Tables if you want to add any system tables to the list of available tables.

   Note

   This option does not apply to some data sources.

Join tables

If you added more than one table to the Staging Area, you need to join the tables.

For detailed information about joining tables, see Joining tables in the Data Access window. For information about joining Apache Drill tables, see Joining tables from Apache Drill data sources.

1. In the Staging Area, click the join icon to access the Join Settings.

   

2. Click the type of join you want:
   • Inner
   • Outer
   • Left
   • Right

   Note

   Some data connectors, including the Microsoft Excel and Microsoft Access connectors, do not support the Outer join type.

3. Select the common key fields by doing the following:
   1. Under Left Column, select the left table key field.
   2. Under Right Column, select the right table key field.

   Tip

   You can filter the list of available fields by entering a search string in the Left Column or Right Column boxes. Fields are listed alphabetically.

4. Optional. Click + Add key if you need to add an additional key field.
5. Click Apply to save the join settings.
6. Create join settings for each additional table you are joining.
7. Optional. In the Import Preview panel, click Refresh to see a preview of the joined tables.

Select the fields to import

By default, all fields in a table are imported unless you deselect specific fields.

1. If you want to omit one or more fields from the import, click the Show Fields dropdown list on the table.
2. Click a field name to deselect it.

   Tip

   If you want to deselect most of the fields, click the Select all toggle to deselect all fields, and then re-select the fields you want.

3. In the Import Preview panel, click Refresh to review the fields included in the import.
4. Optional. To import one or more fields as cryptographic hash values:
   1. In the Import Preview panel, select the Hash checkbox at the top of the columns you want to transform.
   2. At the bottom of the screen, in the Salt field, enter an alphanumeric string to use in the hashing function.

      The salt value is limited to 128 characters. Do not use any of the following characters: (  )  "

      If you do not provide a salt value, Analytics generates a random string. Click Refresh to view the generated string in the Salt field.

   Hash values are one-way transformations and cannot be decoded after you import the fields.

   Note

   Even though you cannot read the raw values of hashed data, it is still useful when combining or analyzing data. If you want to compare values that are hashed by ACCESSDATA during import with values that are hashed using ACLScript's HASH( ) function, you must convert any numeric or datetime Analytics fields to character values and trim any spaces before hashing the data.

   Datetime fields must use the following formats:

   • Datetime "YYYY-MM-DD hh:mm:ss"
   • Date "YYYY-MM-DD"
   • Time "hh:mm:ss"

Filter data

By default, all records in a table are imported unless you create one or more filters to omit specific records.

Specify the number of records to import

You can specify that only a certain number of records, starting from the top of the table, are imported.

1. In Select first n records, enter the number of records, starting from the top of the table, that you want to import.
2. Optional. In the Import Preview panel, click Refresh to see the records included in the import.

Create a conditional filter

1. If you want to conditionally omit records from the import, click Add filters to limit results to create a filter.
2. From the Field list, select the field you want to use for filtering.

   Tip

   You can filter the list of available fields by entering a search string in the Field box. Fields are listed alphabetically.

   Note

   If you have joined tables, you can select a field from any of the joined tables.

3. From the Condition list, select a conditional operator such as is, equals, or greater than.

   The in operator allows you to specify multiple test values. For more information, see Using the "in" conditional operator.

4. In the third field, enter the value to test against.

   Note

   If you are filtering using a Logical field, the test value may need to be one of the following, depending on the data source:

   • 'true' or 'false' (including the single quotation marks)
   • 1 or 0 (1= true, 0 = false)

   If filtering using one of the actual values in the field returns an error, try one of the values above.

5. Optional. To add another filter, do the following:
   1. Click Add filter.
   2. Select AND or OR, depending on how you want the filters to be combined.
   3. Repeat steps 2 to 4 to create the filter.

      You can continue to add filters to specify the exact set of data you want to import.

      Note

      You cannot mix Boolean operators when you combine multiple filters in a filter group. All filters in a group must be combined using either AND or OR.

6. Optional. To add a filter group, do the following:
   1. Click Add filter group.
   2. Select AND or OR, depending on how you want the filter groups to be combined.
   3. Repeat steps 2 to 4 to create a filter.

      You can continue to add filters to the filter group, or create additional filter groups, to specify the exact set of data you want to import.

      Note

      The filters in each filter group are evaluated first, and then the filter groups are evaluated in relation to one another.

      You cannot mix Boolean operators when you combine multiple filter groups. All filter groups must be combined using either AND or OR.

7. Optional. In the Import Preview panel, click Refresh to see the records included in the import.

Using the "in" conditional operator

The in operator allows you to specify multiple test values. For example, you could create a conditional filter on the City field to limit the records that you import to only those for certain cities:

New York, San Francisco, Dallas

The follow rules apply to the in conditional operator:

• Separate test values with commas. Test values can contain spaces. (See the example above.)
• Enclose test values with double quotation marks " " if the values contain one or more single quotation marks '

  Depending on the data connector, you may also need to escape the single quotation mark character. For example: "\'abc123\'"

• Enclose test values with single quotation marks ' ' if the values contain one or more double quotation marks "
• Enclose test values with double quotation marks " " , or single quotation marks ' ' , if the values contain either of the following characters: comma , or backslash \

  The backslash must be followed by at least one character. For example: "\a" or "\\"

• In a single filter, do not use both double quotation marks " " and single quotation marks ' ' to enclose test values. Use one method or the other.

Adjust maximum field lengths

If the default maximum field lengths for imported character or memo fields are too short or too long, you can adjust them.

Data that exceeds the maximum field length is truncated when imported to Analytics.

1. At the bottom of the Data Access window, increase or decrease the number of characters in one or both of these fields:
   • Max Character Field Length
   • Max Memo Field Length
2. In the Import Preview panel, click Refresh to update the field lengths in the preview.

   Note

   You may need to drag a preview column wider to see all the text in the column.

Import all fields as character data

Select All Character if you want to import all fields as character data.

Importing all fields as character data can simplify the import process and allow you to get troublesome fields into Analytics without losing data. Once the data is in Analytics, you can assign different data types, such as Numeric or Datetime, to the fields, and specify format details.

The All Character option is also useful if you are importing a table with identifier fields automatically assigned the Numeric data type by Analytics when in fact they should use the Character data type.

Edit the SQL import statement

If you understand SQL, you can directly edit the SQL import statement.

Editing the SQL import statement lets you control aspects of the data import not available through the user interface. For example, you can change field names in the SQL import statement.

1. Optional. Click the SQL Mode toggle button.
2. Edit the SQL import statement.

   Note

   You cannot use ACLScript syntax (commands or functions) in the body of the SQL import statement. You must use valid SQL syntax only.

3. In the Import Preview area, click Refresh to see the effect of the updated SQL on the data that will be imported.

Preview the import

At any point during the import process you can preview the import to see the effect of joins, field selection, filtering, and field length adjustment.

1. Optional. In the Import Preview area, select Estimate Size if you want to see an estimate of the number of records in the import, and the size of the Analytics data file (.fil) that will be created.

   Caution

   Use the Estimate Size option with caution. For large data sets and certain data sources, generating the estimate is processor-intensive and can be slow.

2. In the Import Preview area, click Refresh to see the data that will be imported.

Save the Analytics data file

When you have specified the data set you want, you save the imported data to an Analytics data file.

1. At the bottom of the Data Access window, click Save.
2. Specify the name of the Analytics table and click Save.

   The data is imported and the new table opens automatically in Analytics.

Update the Analytics table and data file

You can update an Analytics table and associated data file that you imported using the Data Access window. Updating a table refreshes it with the most recent source data.

Guidelines

• Only the content is refreshed Refreshing an Analytics table updates only the content of existing fields. It does not update the table layout.

  You cannot refresh a table if the structure of the source data has changed – for example, if fields have been added or removed. You must re-import the data.

• The table is open If the table is open when you refresh it, you temporarily need disk space equal to twice the size of the table. If you have limited disk space, close the table first before refreshing it.
• Tables imported with Analytics 12 Tables that were imported using the Data Access window in version 12 of Analytics are not refreshable, even if you are using a more recent version of Analytics.

  If you want to be able to refresh these tables, re-import them using Analytics 12.5 or later.

Steps

1. In the Navigator, right-click the Analytics table that you want to update and select Refresh from Source.
2. Click Yes in the confirmation dialog box.
3. If the Password Prompt appears, enter the password for the data source and click OK.

   Note

   You can also change the user name if you want to use a different account to access the data source.

4. If one or more prompts appear asking if you want to save changes, click Yes, unless you do not want to save the changes.

   The table is refreshed.