Toad Data Point 6.0 - User Guide

Welcome to Toad Data Point Understand Toad's Workflow

Create ODBC Connections Create Oracle Connections Create Snowflake Connections Create SQL Server Connections Create SharePoint Connections Understand Toad Connections Troubleshoot SQL Server Connections

Understand

Determine the Best Method to Locate Objects Object Explorer Filter Databases and Objects Understand a Table Using the Database Explorer Search for Objects Create an Database Diagram to Use as a Query Template Browse Data in Related Tables

Query

Tutorial: Build a Query Visually Create Cross-Connection Queries Reverse Engineer Queries Build a Subquery Edit SQL Editor Shortcut Keys View Result Sets Compare Differences in Data Export Data with One Click Export Share Excel Linked Queries

Report

Tutorial: Create a Toad Data Report Manage Reports

About Managing Reports DB2 Reports MySQL Reports ODBC Reports Oracle Reports SQL Server Reports

Publish and Share Reports

Automate

Getting Started with Automation Tutorial: Automate Excel Reports Automate Tasks Use Database Automation Activities Use File Automation Activities Use System Automation Activities Use Variables In Automation Execute/Schedule Automation Scripts

Automate Publishing Data to Intelligence Central

Summary

In this tutorial, you will build an Automation script that publishes data as a dataset to Toad Intelligence Central. Your script can either overwrite the dataset or append data with each script execution.

Note: This feature is available in the Toad Data Point Professional edition only and requires access to a Toad Intelligence Central server.

In this tutorial you will learn:

How to publish data to Toad Intelligence Central using an Automation script
How to use the Execute Script activity in Automation to publish a dataset

You will need:

A sample database
A Toad Intelligence Central server to which you can publish a dataset

To learn how to use the Execute Script activity to automate other tasks, see Automation and Executing Scripts and Automate Creating and Refreshing Snapshots in Local Storage.

To learn how to refresh an existing snapshot in Intelligence Central using Automation, see Automate Refreshing Snapshots in Intelligence Central.

Introduction

Toad Data Point allows you to publish data to Toad Intelligence Central in order to store and share data from remote data sources. You can publish data as a view, snapshot, or dataset. Toad Automation allows you to automate the process of publishing data. When using Automation, data is published as a dataset.

In Automation, the Execute Script activity is used to publish data to Intelligence Central. This activity executes a SQL query and then exports the result set to the selected Intelligence Central connection.

To learn more about publishing data to Intelligence Central, see About Publishing Data.

Create an Intelligence Central Connection

You must first create a Toad Intelligence Central connection before you can automate the process of publishing. If you already have a saved Intelligence Central connection, skip ahead to the instructions for creating the script.

Click in the Connection/Navigation Manager.
Select Intelligence Central from the Group list.
To create a new Intelligence Central account, select the Register New User tab. Enter the host server name and enter credentials for the new account. See Create a Connection to Intelligence Central for more information.
To connect using an existing Intelligence Central account, select the Login as Existing User tab. Enter the host server name and enter the credentials for the existing account. The script will use this account to connect to Intelligence Central for the purpose of publishing. If you have access to multiple accounts, select the most appropriate one for this purpose.

If this Automation script is eventually published to and run on an Intelligence Central server, the account used to run the script will be specified during the script publishing process and can be different from the account used to connect.
Select Save Password. For a scheduled Automation script to run successfully, the connection password must be saved with the connection.

Create the Script

To get started, connect to your sample database.
Select Tools | Automation to open the Automation window.
Click the Execute Script activity to add it to the script design pane, or drag the activity from the Toolbox to the design pane.
In the Activity Input tab, select the connection for your sample database (if not already selected).
Then select a SQL file or enter a SQL query to use to select the data you want to publish. Use one of the following methods:
- Enter a SQL statement in the editor.
- Click to browse to and select a SQL file.
- To retain a link to the SQL file, first select Link SQL file and then click to browse to and select the SQL file.
- To edit the selected SQL file, click .
Select Intelligence Central in the Export to section.
Click the Publishing Wizard button to specify publishing options. Toad applies the specified options when the object is published.

If there is no open Intelligence Central connection, Toad prompts you to select a connection. If there is an open Intelligence Central connection, the Publish to Intelligence Central dialog opens.
In the Publishing dialog, select a different connection, if necessary. To review the connection properties for the selected connection, click .
In the Destination Folder field, select an existing folder or click the drop-down arrow to create a new folder.
Enter a name for your new dataset. Only alpha and numeric characters and the underscore (_) are permitted in object names. A name cannot begin with an underscore. If the dataset name already exists in the folder, Toad prompts you for another name.
Select Overwrite Existing Object for this tutorial. The Append Data to Existing Object option is also available for datasets.
Click Add Tags. You can select an existing tag or create a new one. Tags can be used to filter objects in the Object Explorer in Toad. In addition, tags are displayed in the Details pane for the selected object in the Intelligence Central Web Console. Tags can be useful as part of a strategy for identifying, filtering, or describing objects in Intelligence Central.
Click the Sharing link to modify sharing (visibility) options. For this tutorial, select Keep this data private. As the object owner, you have manage privileges to the newly-published object and can modify the sharing privileges later, if necessary.
Click the Indexing Options link. This page can be used to add an index to a dataset, if necessary. Click to return to the Summary page.
Click the Preview SQL link. This page allows you to review the SQL that will be used to select the data to publish. Click to return to the Summary page.
When finished, click OK to save your specifications and close the dialog.
When you are ready to publish the dataset, click Run to save and run the Automation script.
To confirm your dataset was published, review the Automation script log, or use the Object Explorer to view objects in the Toad Intelligence Central server to which you published the dataset.

Locate the dataset in the folder you specified as the destination. Right-click the dataset and select View Details. Select the Information tab to review the modified date, visibility options, and other object details.
To modify sharing (visibility) and manage privileges for the published dataset, in the Object Explorer right-click the object and select Operations | Share Object. In the Alter Visibility dialog you can share the object with a list of users you specify, share the object with all users, or keep the object private.

You can also use this dialog to specify which users will be allowed to manage the object. See Specify Sharing and Manage Privileges for Objects for more information.