Publish to Intelligence Central
Use the Publish to Intelligence Central dialog to publish data, Automation scripts, and Toad files to a Toad Intelligence Central server. Share data with others by publishing views, snapshots, and datasets. You can also share Automation scripts and Toad files for others to copy and/or modify.
To publish generic files, such as Excel files, see Publish Generic Files to TIC.
To publish misc resources, such as database connections or URLs, see Publish Other Toad Resources.
For additional information, see the following:
- Prepare to Publish Data to Toad Intelligence Central
- Prepare to Publish Automation Scripts
- About Toad Intelligence Central
Note: This feature is available in the Toad Data Point Professional edition only and requires access to a Toad Intelligence Central server.
To Publish to Intelligence Central
- To publish data to Intelligence Central, use one of the following methods:
- In the Object Explorer, right-click a table or view and select Publish.
- In the Editor, right-click a SQL statement and select Publish.
- In the Query Builder, right-click a SQL statement and select Publish.
- From a data grid, right-click in the data grid and select Send To | Publish Data.
- Select the Publish to Intelligence Central button in the main toolbar. See The Pick a Source Dialog for more information.
- To publish a Toad document, open the document in Toad and
-
In the Publish to Intelligence Central dialog, specify publishing options. Review the following for additional information:
Publishing Page/Options Option Description Destination Options Publish to Server
Select a Toad Intelligence Central server connection as the publishing destination.
Click
to review the connection properties for the selected TIC connection.
Note: If publishing a view, snapshot, or dataset, the destination server must be licensed for Data Connectivity.
Destination Folder
Select the folder where you want to store the published object, or create a new folder.
- To create a new folder, click the drop-down arrow and then click New Folder
You must have publish privileges to a secured folder to publish to it.
You must have manage privileges to a secured folder to create a subfolder.
(Optional) Select a destination database. This feature is provided for third-party applications that access the underlying Intelligence Central databases directly.
- To select or create a database, click
. Select an existing database or create a new one.
Object Options Publish Type
Select the type of object you want to publish. If you are publishing a script or Toad document, the type is automatically selected.
- Data object—See Data Objects in Toad Intelligence Central for object descriptions.
- Automation script—See Publish Automation Scripts to Intelligence Central for additional help.
- Toad document—See Publish Toad Documents to Intelligence Central for additional help.
- Project Manager—See Publish Projects to Intelligence Central for additional help.
Note: Data originating from an Excel file, Local Storage, or an Access database can only be published as a dataset. See Publishing from Excel or Local Storage for more information.
Name
Create a name to display in the Object Explorer.
Notes: Only alpha and numeric characters and the underscore (_) are permitted in object names. A name cannot begin with an underscore.
Publish Detail—(Dimensional View as Dataset only) Click the link to select which Dimensional Views to publish and to specify dataset names.
Overwrite Existing Object—Select to overwrite an existing object with the same name. Select this option when re-publishing after editing a script or Toad document.
Append Data to Existing Object—Select to append data to an existing dataset.
Description (Optional) Enter an object description. The description displays in the Information tab of the Database Explorer.
Tags (Optional) Add tags to use for filtering in the Object Explorer. Tags also display in the Information tab of the Database Explorer.
- Click Add Tag to add a tag. Then enter a new tag or click the drop-down arrow to select from existing tags.
- Press the Tab key to add additional tags.
Sharing Click the link to modify sharing (visibility) options. Review the following based on the folder to which you are publishing:
To a new or existing
To an existing secured folder—If you do not have Manage privileges, sharing options are disabled. The object inherits the folder's sharing and manage privileges.
To a new secured folder—The options you specify are applied to the folder and the object. Select the users to share, manage, and publish. See Secured Folders for more information.
To save your changes, click Apply or
.
To revert to the default settings, click Reset.
Indexing Options
(Dataset and Snapshot only)
Click the link to create an index.
Note: If you take no action, no columns are indexed.
See Create/Drop Index for a TIC Object for more information.
To save your changes, click Apply or
To revert to the default settings, click Reset.
Authentication
(View, Snapshot, Script, and Toad file only)
For a Snapshot or View
Click the link to view or change authentication keys, or to enter a connection password.
By default, Toad creates an Authentication key for the object and uses your user name and password to the remote data source (which is gathered from the connection used to publish the object).
(View only) For a view, you can change the personal Authentication key to a shared Authentication key. Click the link to open the Authentication Settings page. Then do the following:
- Enter the user name and password (of an existing account) to be used as the shared credentials.
- Select Share authentication. This permits all TIC users to access this data source, even those without a personal login to the remote source.
- (Salesforce.com only) If you are creating an authentication key for a Salesforce.com data source using a security token, you must enter the token. See Create Salesforce Connections for more information.
Note: For cross-connection queries, you can specify a personal Authentication key or a shared Authentication key for each data source separately.
See User Authentication in Intelligence Central for more information.
For
Click the link to view the connection information for all connections in the
- If you did not save the connection password in the Connection Properties dialog, enter the password now.
- If you did save the connection password, Toad automatically enters it.
To save your changes, click Apply or
To revert to the default settings, click Reset.
Variables
(View and Script only)
Click Default values to specify a default value for any variable in the SQL or Automation script.
Note: If you take no action, Toad uses existing (cached) values you specified in the Bind Variables dialog or in the script. If no values exist, you must specify before publishing.
See Variables in Views/Scripts and Publishing to Intelligence Central for more information.
Refresh Data
(Snapshot only)
Click the link to schedule the initial snapshot creation and the refresh frequency.
Note: If you take no action, the snapshot is populated immediately.
- Create Initial Snapshot—Specify when to initially populate the snapshot with data.
- Schedule Recurring Refresh—Specify a frequency at which Toad will automatically refresh the snapshot.
See Snapshots in TIC for more information.
To save your changes, click Apply or
To revert to the default settings, click Reset.
Scheduling
(Script only)
Click the link to schedule script execution or to run the script immediately.
Test run the script after it is published—Select to run the script once to test the database connections and the links to input files.
Note: It is recommended that you test run your script when publishing to uncover any issues such as missing input files or connections that are not properly set up on the TIC server.
See Publish Automation Scripts to Intelligence Central for more information.
To save your changes, click Apply or
To revert to the default settings, click Reset.
Run Details
(Script only)
Click the link to specify a user account to run the script.
- Specify user to execute automation script—Select to specify a Windows user account.
- Use default user to execute automation script—Select to use the default account—the account under which the Toad Intelligence Central App Server is currently running.
See Publish Automation Scripts to Intelligence Central for more information.
Preview SQL Click the link to preview the SQL statement. Send email notification to shared users Select to send a notification email to each user you specified on the Sharing settings page. After you publish the object, the email is generated and opens for you to review, edit, and send.
See View Your Intelligence Central Notifications for more information about all types of notifications.
Important: To undo changes, click Reset. Then click
- To create a new folder, click the drop-down arrow and then click New Folder
-
Click Publish to publish the object.
The dialog closes and the publishing process runs in the background.
-
After the object publishes successfully, a confirmation window displays and includes a link to the newly-published object. Click the link to open the object in the
Note: In the event of an error during publishing, use the error message and the troubleshooting section to troubleshoot, or follow the instructions displayed in the error message.
Rules for Duplicate Object Names
Views, snapshots, and datasets: No two data objects can have the same name within the same folder, regardless of object type: view, snapshot, or dataset.
Toad documents: The name must be unique within a folder per Toad document type. For example, no two ER Diagrams in the same folder can have the same name, but an ER Diagram and a Query Builder file in the same folder can have the same name.
Automation scripts: A script name must be unique among scripts. No two scripts can have the same name within an Intelligence Central instance.
Compatibility with Intelligence Central Versions
- Publishing Automation scripts is supported in Toad Intelligence Central 2.2 or later.
- Publishing Toad documents and generic files is supported in Toad Intelligence Central 2.3 or later.
Tips:
- To publish without executing a query, right-click inside the Editor window or Query Builder Query pane containing the query and select Publish.
- You can automate the task of publishing data to Intelligence Central as a dataset by using the Execute Script activity in Automation. See Use Database Automation Activities for more information.
- You can automate the task of refreshing snapshots in Intelligence Central by using the Refresh Snapshot activity in Automation. See Use Database Automation Activities for more information.
Specify a Destination Database
Note: This feature is available in the Toad Data Point Professional edition only and requires access to a Toad Intelligence Central server.
When publishing an object to Toad Intelligence Central, the Publishing dialog allows you to specify a destination database. This feature is provided for Toad Data Point users who provision data objects to those who can access Intelligence Central through a third party application, such as Qliktech or Tableau.
This feature allows the provisioner to store and organize objects into databases. Databases are used by the third-party-application user to locate and access stored objects.
Folders in Intelligence Central
The Publishing dialog allows you to select a folder or create a new folder when publishing an object to Toad Intelligence Central.
- Toad Data Point—Folders are visible to Toad Data Point users through the Object Explorer and are used by the Toad Data Point user to store, locate, and access objects.
- Third Party Application—The Intelligence Central folders are not visible to the Third Party application.
Databases in Intelligence Central
The publishing dialog allows you to select a database or create a new database when publishing an object.
- Toad Data Point—Databases are visible to Toad Data Point users through the Object Explorer. However, folders are the primary method used for organizing objects for Toad Data Point users. This is because folders are more-flexible and easily-customizable through the Object Explorer.
- Third Party Application— Databases are visible to a Third Party application through a MySQL connection to Intelligence Central and are used to store, locate, and access objects. Databases are the primary method of organizing objects since the Intelligence Central folders are not visible to the Third Party application.
Third-Party Connection to Intelligence Central
You can create a MySQL connection to Intelligence Central through a third-party application like Qliktech or Tableau. To learn how, see Third Party Connection To Toad Intelligence Central in the Toad Intelligence Central Quick Start Guide.
Tip: In Toad Data Point you can create a MySQL connection to Intelligence Central. The MySQL connection allows you to view the underlying MySQL databases in Intelligence Central. You might want to use this type of connection to view databases prior to provisioning.
Creating and Specifying Databases
When publishing an object to Intelligence Central, you can specify or create a database in which to store the object for users who will access it through a third-party application. Organizing objects into databases is important for these users because Intelligence Central folders are not visible to Third Party applications. See Publish to Toad Intelligence Central for more information about publishing objects.
To select an existing destination database
- In the Publish to Intelligence Central dialog, click
- Select an existing database.
- By default, the destination database is the one associated with the selected folder. If you are creating a new destination folder, the database name defaults to the new folder name.
- When no destination folder is selected, published_objects is the default database.
Note: If you select an existing folder in the Publishing dialog without specifying an existing or new database, Toad stores the object in the database with the same name as the folder.
To create a new Intelligence Central database
- In the Publish to Intelligence Central dialog, do one of the following:
- If you selected an existing folder in the Destination Folder field, then click
- If you created a new folder, you can use the default database name or click
Note: If you create a new folder (or sub-folder) through the Publishing dialog without specifying an existing or new database, Toad creates a default database using the folder name. For a sub-folder, the database name is <parent folder>_<sub-folder>.
- If you selected an existing folder in the Destination Folder field, then click
Viewing Databases
Third Party Application
When using a MySQL connection to Intelligence Central, the following databases which contain published objects are visible:
- User-defined databases—A database is created for each new destination database created through the Publish to Intelligence Central dialog.
- folder name database—Each time a new folder (or sub-folder) is created through the Publishing dialog without specifying an existing database, a default database is created using the folder name. This database contains each object originally assigned to that folder. Even if an object is moved to another folder through the Object Explorer, the object remains in the original database. For a sub-folder, the database name is <parent folder>_<sub-folder>.
- published_objects database—The "published_objects" database contains the objects in the "No folder assigned" folder.
- Legacy databases—If the Intelligence Central instance contains objects that were published using Toad Data Point 3.6 or earlier, a database was created for each data source (containing all objects published from that data source). See Explore Toad Intelligence Central for more information about datasource names. In addition, a database was created for each user and contains their published datasets (data_username).
Toad Data Point
In the Object Explorer, the Intelligence Central databases listed above are visible, as well as the following:
- database/folder name database—If you select an existing folder but do not use the default database and instead select a different database in the Publishing dialog when publishing an object, Toad creates a folder under the Databases node in the Object Explorer using the format database/folder.
Rules for Duplicate Object Names
Views, snapshots, and datasets: No two data objects can have the same name within the same folder, regardless of object type: view, snapshot, or dataset.
Toad documents: The name must be unique within a folder per Toad document type. For example, no two ER Diagrams in the same folder can have the same name, but an ER Diagram and a Query Builder file in the same folder can have the same name.
Automation scripts: A script name must be unique among scripts. No two scripts can have the same name within an Intelligence Central instance.
Specify Default Variable Value
Notes:
- This feature is available in the Toad Data Point Professional edition only and requires access to a Toad Intelligence Central server.
- This feature is supported in Intelligence Central 3.3 or later.
Toad allows you to publish Views and Automation scripts containing variables and then specify a default value for each variable during the publishing process. After publishing, you can modify the default value. For scripts, you can also provide a value at run time. The variables can be bind parameters or other types of variables, such as those supported in Automation scripts.
This feature allows data providers to publish views and Automation scripts with variables so that data consumers can change the variable values when they run the view or execute the script.
Object owners and users with Manage privileges can modify the default value (views and scripts). Any user with access to a script can enter a variable value at run time when manually executing. Scheduled scripts use the default value.
This feature applies to the following objects:
- View. SQL published as a view in which the SQL contains one or more variables (bind parameters or other variables)
-
Automation Script (Set Variable). An Automation script containing a Set Variable activity. To enable this feature, the Prompt during run option must be selected in the activity. See Set Variable activity in Use System Automation Activities for more information.
-
Automation Script (Send Email). An Automation script containing a Send Email activity. To enable this feature, the Prompt option must be selected in the activity. See Send Email activity in Use System Automation Activities for more information.
A Note About Datasets: Toad allows you to publish data as a dataset from SQL containing a variable. However, the cached value for the variable is used and you cannot edit this value after publishing. A cached value must exist before publishing. Execute the SQL to create a cached value.
Specify Default Value During Publishing
To specify a default value when publishing a view or script
- When publishing one of the objects listed above, in the publishing dialog click Default values in the Variables field. See Publish to Toad Intelligence Central.
- If you previously specified variable values, either through the Bind Variables dialog or through the script, Toad finds and enters those values.
-
If a predefined list of custom values has previously been defined in the Variables (Define variable prompt values) options, the values from that list will be available for selection.
- If cached values are not found, you must specify values before publishing.
Note: This option is enabled only for certain objects (listed above).
- On the Bind Variables and Parameters page, enter a value for each variable listed.
Edit Default Value After Publishing
To edit the default value after publishing
- Connect to the Intelligence Central server.
- In the Object Explorer, right-click the published view or Automation script and select View Details.
- Select the Variables tab. The default value is displayed.
- Click
to enter a new default value.
- For a view, the new value will be used when the result set is returned.
- For a script, the new value is used when the scheduled script is executed.
Tip: In the Object Explorer, select a view or script and click
Prompt for Value at Run Time (Scripts)
When manually executing an enabled Automation script in Intelligence Central, Toad prompts the user for a variable value. Enabled scripts were created with the Prompt or Prompt during run option selected in the applicable Automation activity. See list of applicable objects above. Scheduled script execution uses the default value.
To enter variable values at run time
- Connect to the Intelligence Central server.
- In the Object Explorer, select a script and click
to run it. Toad prompts you for a variable value. Enter the value. This value is used for this execution only. The default value remains unchanged.
Considerations and Limitations
| Consideration/Limitation | Description |
|---|---|
| ODBC connections: Publishing view with variable is not supported |
You cannot publish SQL containing a variable from an ODBC connection to Intelligence Central as a view. Workaround: You can publish SQL containing a variable from an ODBC connection to Intelligence Central as a dataset. The cached value for the variable is used and you cannot edit this value after publishing. |
| When inner view is located in another TIC instance | When publishing a view in which the source data is a view from another Intelligence Central instance, the variables in the inner (source) view are not included in the list of editable variables. For the inner view variables, the current default value is used. |
| Using published view in cross-connection query | When using a view (containing a variable) from Intelligence Central in a cross-connection query, you are not prompted for a variable value. The current default value is used. |
Tip: The Web Console in Toad Intelligence Central 3.3 (or later) allows the user to enter a variable value at run time. In Toad Intelligence Central 4.3 (or later) the user can also modify a variable default value. See the Toad Intelligence Central Quick Start Guide for more information.
Considerations and Limitations
Review the following considerations and limitations for publishing data to Toad Intelligence Central (TIC).
| Consideration/Limitation | Description |
|---|---|
| Toad replaces native provider connection with ODBC connection |
If you publish data using a native database provider connection, Toad converts that native connection to an ODBC connection on TIC. In order to successfully publish from a database, you must have an ODBC driver for that database installed. Some ODBC drivers are included with your Toad installation and some (such as Teradata) are not. To see a list of included ODBC drivers and how to install those you need, see Prepare to Publish Data to Toad Intelligence Central. |
| ODBC drivers on Toad Intelligence Central must match publisher's | If you publish using an ODBC connection, the same ODBC drivers that Toad used when publishing your data object must also be installed on the Toad Intelligence Central server. This is required so that those same drivers can be used by others when they access your data objects via the server. See Prepare to Publish Data to Toad Intelligence Central for more information. |
| DSN on TIC must match publisher's | If you publish using an ODBC connection, the same DSN that Toad used when publishing your data object must also be installed on the Toad Intelligence Central server. |
| Use System DSNs |
If you publish using an ODBC connection, when using the ODBC Data Source Administrator to select a DSN for an ODBC driver, you must select a System DSN. Toad Intelligence Central runs as a system service and therefore has access only to the System DSNs. |
| Specify ODBC drivers for a specific TIC connection |
You can specify the ODBC drivers to use when publishing to an Intelligence Central instance. You can do this for each Intelligence Central connection. To specify ODBC drivers:
Note: Specify an ODBC driver to use only if you are having difficulty publishing from that particular database to the TIC instance. |
| Publishing from an Oracle connection |
When publishing from an Oracle native provider connection, Toad is using an OCI client, an ODBC driver, and the tnsnames.ora file. All three of these components must exist on the client computer and on the TIC server to successfully publish an object from an Oracle connection to TIC. The Oracle alias names in the tnsnames.ora file on the TIC server must match those in the client's tnsnames.ora file. If you encounter an error when publishing from Oracle, one of these components may be contributing to an incorrect connection. Please see Troubleshoot Publishing to Toad Intelligence Central for help resolve Oracle issues that involve these components. |
| Unsupported Oracle timestamp data types |
Advanced timestamp data types, such as TIMESTAMP_WITH_LOCAL_TIME_ZONE and TIMESTAMP_WITH_TIME_ZONE, are not supported in published objects. Oracle TIMESTAMP is supported. |
| Publishing from a DB2 connection |
When publishing from a DB2 native provider connection, Toad is using a DB2 client, an ODBC driver, and the DB2 catalog. All three of these components must exist on the client computer and on the TIC server to successfully publish an object from a DB2 connection to TIC. |
| Publishing from Excel or from an Access or Local Storage database | You can publish data to Intelligence Central from these sources, but only as a dataset, not as a snapshot or view. See Publishing from Excel or Local Storage for more information. |
| Publishing from a localhost connection |
You cannot publish data (as a view or snapshot) from a local database (or Intelligence Central) connection defined as localhost. This restriction also applies when publishing Toad documents containing a connection and Automation scripts containing a connection. Workaround: Define the connection using the data source IP address or network name instead of localhost. |
| Publishing from a SQL Server connection using Windows Authentication | You cannot publish from a SQL Server connection that uses Windows Authentication. In addition, you cannot publish from a SQL Server connection if you select a publishing key that uses Windows Authentication. Intelligence Central cannot authenticate the SQL Server Windows Authentication account for a user who is not currently logged in on the Intelligence Central server. |
| Publishing from a SQL Server connection using SQL Server Authentication | To successfully publish from a SQL Server connection that uses SQL Server Authentication, the SQL Server user account must have db_datareader privileges and the view permission. |
| Publishing from a connection using Windows Authentication | You cannot publish from a database connection that uses Windows Authentication. For example, you cannot publish from an Oracle connection or an SAP HANA connection that uses Windows Authentication. To publish successfully, publish from a connection that uses database user name and password. |
| Publishing a cross-connection query - use unique aliases | If you publish a view or a snapshot from a cross-connection query, it is strongly recommended that you ensure that all aliases are unique within the entire SQL. |
| WITH clause limitations |
To successfully publish a view or snapshot from SQL containing a WITH clause, ensure the SQL adheres to the following:
Note: Here the term common table expression also refers to subquery factoring (Oracle) or any other type of named subquery block. |
| BLOBs are not supported |
When publishing an object that contains a binary large object (BLOB) data type column, the BLOB data type column will not be included in the published object. |
| Hive data sources, changing authentication key type |
If you create a Hive data source from a non-SSH Hive connection, you cannot change the authentication key to an SSH-type authentication key or vice versa. To change the authentication key to SSH (or vice versa), you must recreate the data source. |
| FULL OUTER JOIN not supported | Intelligence Central does not support full outer joins at this time. Use UNION instead. |