Toad Data Point 3.7 - Installation Guide

Create Excel Connections

Connecting to an Excel file is easy. To create the connection, simply open the Excel file from the Create New Connection dialog .

To create an Excel connection

1. Click on the Navigation or Connection Manager toolbar.

2. Select Excel from the Group list.

3. Browse to and select the Excel file.

4. Complete the Create New Connection dialog. Review the following for additional information:

   General Tab	Description
   Category	Select a category if you want to color code your connection. Tip: The color is applied in the Connection Manager and other connection dialogs. You can also create a new category for your connection.
   Advanced Tab	Description
   Enable import mode	Select this checkbox to set IMEX=1 in the connection string, which converts intermixed data to text.
   Show system tables	Select to display worksheets as system tables. Toad uses the worksheet name suffixed with a dollar sign ($).
   Automatically create ranges	Select this option if you want Toad to automatically create named ranges. Toad creates one named range in each worksheet (unless the Toad name, AutoRange_<worksheet name>, already exists). Note: Toad does not overwrite the user defined named ranges. The Toad-created named ranges and the user defined named ranges are both visible in the Object Explorer after connecting. Deselect this option if the Excel file contains user defined named ranges and you do not want Toad to create additional named ranges. Important: If you deselect this option, and your Excel file does not contain named ranges, Toad is unable to create tables from your data. The feature is selected by default.
   Hide named ranges	Select the named ranges to hide. Note: Named ranges are not displayed until after a connection is created (in the Connections or Connection Properties dialog). In Excel 2007 files, named ranges are not displayed for active connections.
   Open in Excel	Click to open the selected file in Excel. This is useful if you want to modify your file or define named ranges before connecting.

4. Click Connect to connect immediately while saving the connection information. Optionally, click Save to save the connection without connecting.

Understand how Toad creates data tables from an Excel file

Toad uses Excel named ranges (regions) to define the data to include in a table. Therefore, any data that you want to display and easily query in Toad must be included in an Excel named range (region).

You can create named ranges using one of the following methods:

• Preferred method: You can ask Toad to automatically create named ranges when you create the connection to the Excel file. Toad creates one table from each worksheet and includes the contents of every formatted cell.
• If you do not want to include a worksheet's entire contents in one named range, or if a worksheet contains more than one table, then define the named ranges ahead of time in Excel.

To create a named range in Excel

1. In the Excel file, select the data that you want to include in the table (including column headings). Then define a named range.

   »

   In Excel 2003, select Insert | Name | Define.

   »

   In Excel 2007, right-click the data and select Name a Range.

2. Save your Excel file. In Toad, create a new connection to the file. In the New Connection dialog, deselect Automatically create ranges.

To allow Toad to automatically create named ranges

Tip: For an Excel file with an existing connection, in the Navigation Manger, right-click the file and select Properties. On the Advanced tab, select Automatically create ranges. Toad creates the named ranges the next time you reconnect to this file.

To hide named ranges

You can hide an Excel file's named ranges so that they do not display as tables in the Object Explorer.

1. In the Navigation Manager, right-click an Excel file connection and select Properties.

   Note: In Excel 2007 files, the connection must be inactive for the named ranges to display.

2. On the Advanced tab, select the named ranges to hide.

To view or modify connection properties

To view an Excel connection's properties, do one of the following:

• Right-click an Excel file connection in the Navigation or Connection Manager and select Properties.
• Select on the Connections toolbar to open the Connections dialog. Select the Excel file connection in the left pane to view the connection properties in the right pane.

Click here to view a video about creating connections in Toad Data Point

Tip: Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory link in Help | About.

See Troubleshoot Excel® Issues for more information about troubleshooting Excel connection issues.

Create Google Analytics Connections

Toad allows you to create connections to your Google Analytics data. A connection in Toad allows you to access the Google Analytics accounts and data that are available to you from your Google user account.

If you have multiple Google user accounts, you can create a separate Google Analytics connection in Toad for each account. For an explanation of Google accounts, see the following: https://developers.google.com/analytics/resources/concepts/gaConceptsAccounts

Note: This feature is available in the Toad Data Point Professional Edition only.

About Google Analytics Connections

When you create a Google Analytics connection in Toad, you log in to Google using your Google account. During the connection creation process, you also grant Toad access to the Google Analytics data accessible from that Google account.

Toad provides you with an interface to the Google log-in and authorization pages and displays these pages within the Create New Connection (and Connection Properties) dialog. The Google pages displayed are independent of Toad. The log-in process is the same as you normally encounter with your other Web-based Google log-in procedures. The only difference is that by creating a connection in Toad, you are giving Toad permission to access your Google Analytics data. This is accomplished when you click Accept in the Google authorization page.

To create a Google Analytics connection

1. Click on the Connections toolbar (ALT+F+N).
2. In the Group list, select Google Analytics. The Google log-in page immediately displays within the Create New Connection dialog.

3. Enter the credentials (email address and password) for your Google account and click Sign in.

   Note: If you have multiple Google accounts and have previously logged in to Google (or connected through Toad), the Google log-in page may display the last Google account used. To specify a different Google account for the new connection, use the Google links/menu to select another account.

4. (optional) You can enter a name for your new account and select a category. You can also make these modifications later using the Connection Properties dialog.

5. In the Google authorization page that displays, click Accept to allow Toad access to your Google Analytics data. Toad then attempts to establish a connection to Google Analytics. If the connection is successful, the Create New Connection dialog closes.

6. Upon creating an initial connection, Toad automatically maps the data source objects. This process runs in the background, and until it is finished, you may experience a delay when attempting to access these objects.

Reauthorizing a Connection

If you encounter an error message when attempting to connect using your Google Analytics connection, Toad's authorization (access) to the Google account for that connection may have been lost. Use the following procedure to re-establish authorization for the connection.

You can also use this procedure to specify a different Google log-in account for a connection.

To Reauthorize

1. In the Navigation Manager, disconnect from the connection. You must disconnect before you can re-authorize.
2. After disconnecting, right-click the connection and select Properties.
3. In the Connection Properties dialog, click Reauthorize.
   • If you are no longer logged in to Google, the Google log-in page displays.
     • To reauthorize using the same Google account, re-enter your Google account credentials and click Sign-in. Then click Accept on the authorization page.
     • To specify a different Google account for this connection, use the Google links to switch the account just as you normally would in the Google Web site. Once you specify a different account, this is the Google account associated with this connection in Toad. Click Sign-in. Then click Accept on the authorization page.
   • If you are still logged in to Google, the Google page for granting Toad access to your Google Analytics data displays.
     • To reauthorize using the same Google account, click Accept.
     • To specify a different Google account for this connection, use the Google menu or links to switch the Google account just as you normally would in the Google Web site. Once you specify a different account, this is the Google account associated with this connection in Toad. Click Sign-in. Then click Accept on the authorization page.

Notes:

• You can specify a proxy server to use for Business Intelligence and NoSQL connections at Tools | Options | Database | Data Services.

Create Hive Connections

Toad allows you to connect to a Hive data warehouse system. Apache Hive is one of the NoSQL data sources supported by Toad.

Note: This feature is available in the Toad Data Point Professional Edition only.

To create a Hive connection

1. Click on the Connections toolbar (ALT+F+N).
2. Select Apache Hive from the Group list.

3. Enter the connection information in the Create New Connection dialog. Review the following for additional information:

    

   Login tab
   Host	Enter the host name of the Hive data source.
   Port	Enter the port number.
   Schema	Select a default schema to open upon connection.
   Session Initialization	Enter any Hive session initialization commands. These will be executed once at the start of each connection.
   Category	(Optional) Select an existing category or create a new one.
   Advanced tab
   Connect with SSH	Select to use SSH to connect to Hive.
   SSH user	Enter the user name to log in using SSH.
   SSH port	Select the SSH port number. The default is 22.
   Authenticate with a key file	Select if the host uses a key file to authenticate the SSH user field. Private key path—Enter the absolute path (including file name) to the private key file on the host (not the client). Pass phrase—Enter your passphrase.
   Authenticate with a password	Select if the host uses a password to authenticate the SSH user field. Password—Enter the password to log in using SSH.
   Note: In NoSQL and Business Intelligence connections, Toad automatically saves the password in the connections.xml file as obfuscated text, as well as in Toad. To add additional password security, use Toad's Master Password feature.

4. Click Connect to connect immediately while saving the connection information. Optionally, click Save to save the connection without connecting.

5. Upon creating an initial connection, Toad automatically maps the data source objects. This process runs in the background, and until it is finished, you may experience a delay when attempting to access these objects.

Tips: 

• Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory link in Help | About.
• You can specify a proxy server to use for Business Intelligence and NoSQL connections at Tools | Options | Database | Data Services.

Create Intelligence Central Connections

The purpose of Toad Intelligence Central (TIC) is to give Toad Data Point users a place to store and share data. After creating a connection to an Intelligence Central instance, not only can you view data that is made available to you, you can publish data from other sources in the form of snapshots, views, and datasets.

Note: This feature is available in the Toad Data Point Professional Edition only.

About Toad Intelligence Central Connections

To create a new TIC connection, you must provide the TIC server name and then register with TIC by creating a new user account for yourself. The new user name and password will be used by TIC to identify you from among the group of TIC users and to manage the objects you create and are granted access to on the TIC server.

A TIC connection displays in the Navigation Manager in the format: username@server. The username is the name of the new user you create when you create a new TIC connection.

To create an Intelligence Central connection

1. Click on the Connections toolbar (ALT+F+N).
2. Select Intelligence Central from the Group list.
3. Select the Register New User tab.

4. Register New User. Use this tab to register with TIC by creating your own account (user name and password). Review the following for additional information:

   Note: If you are creating a connection to a TIC instance for which you already have a user account, do not fill in this tab again. Skip ahead to the next step (Login tab).

   Register New User
   Use Windows Authentication	Select to create a new user account using your Windows credentials. Toad automatically populates the fields with your account information. See Limitations of Using Windows Authentication.
   User Name	Create a new user name for this connection. This is the name you will be logged in with. The objects you create will be linked to this user name. All your TIC rights (like sharing) will be linked to this user name. This user name will display in your connection in the Navigation Manager in the format: username@server Note: User names must be lower case only. When you enter a user name, all upper-case characters are automatically converted to lower-case.
   Password	Create a password.
   Verify Password	Re-enter the password to confirm.
   Full Name	Enter your full name. This name, along with your user name, displays on the Sharing tab of the Publishing dialog and in the Alter Visibility dialog. Your full name will help users identify you when they select users with which to share an object.
   Email Address	Enter your email address.
   Host	Enter the name of the TIC host server.
   Port	Enter the port number for the App Server.

5. Login as Existing User tab. Use this tab to create a new connection to a TIC server for which you already have a user account. (When you open an existing connection, this tab displays your user account information.)

   Login as Existing User
   Host	Enter the name of the TIC host server.
   User	Enter an existing server user name.
   Password	Enter the existing password for the user name.
   Data Source	(Optional) Enter the name of a default data source to open upon connection.
   Port	Enter the port number for the App Server.
   Category	(Optional) Select an existing category or create a new one.

6. Advanced tab. (Optional) Use the Advanced tab to specify ODBC drivers to use when publishing to this TIC server. You can also modify logging levels here.

   Note: Specify an ODBC driver only if you are having difficulty publishing to this TIC instance from a particular database type.

   Advanced
   ODBC Drivers	Specify an ODBC driver for each database type that you are having difficulty publishing from. That ODBC driver will be used when publishing from this database type using this connection.
   Log Level	Sets the logging level for logs generated by this Intelligence Central instance, including the App Server, Scheduler, and Hub logs. Use this option to increase the logging level for debugging purposes. This option is enabled only for connections to Intelligence Central 2.3 or later. Note: If you increase the logging level, please note that the log file is included in the support bundle of other Toad Data Point users with this Intelligence Central connection. Reduce the logging level after debugging. Tip: You can set the logging level for the Scheduler separately. See Configure Automation Script Scheduler for more information.

7. (Optional) Select Save Password to save your password with this connection.

8. Click Connect to connect immediately and save the connection information. Optionally, click Save to save the connection without connecting.

To change your TIC user password

You can change your password for your TIC user account at any time.

Note: You cannot use this procedure to change the password for a TIC account that uses Windows Authentication.

1. In the Navigation Manager, right-click the TIC connection and select Change Password.
2. Enter the old password and then enter the new password. Enter the new password a second time to confirm.

To switch your TIC user account to a Windows Authentication account

You can switch your current TIC account to a Windows Authentication account if both accounts have the same user name.

1. In the Connection Manager, right-click your current TIC account connection and select Properties.
2. In the Register New User tab, select Use Windows Authentication. Toad prompts you to switch your account.

Limitations of Using Windows Authentication

Tip: Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory link in Help | About.