Share Excel Linked Queries
You can use linked queries to create "live" Excel reports that can be refreshed dynamically. Because a linked query contains the underlying SQL for a report, each time you refresh the report the query is executed and the latest data is retrieved. The dynamic nature of linked query reports makes them ideal for distribution to users who need to see changes to data without waiting for scheduled updates or for new reports to be distributed.
Notes:
-
Any user who opens an Excel linked query must have access to a user name and password for the queried database to refresh the data. If a user does not have access to the database, they can only see the original data in the spreadsheet.
-
If the Excel linked query was created from an ODBC connection that uses a Data Source Name (DSN), the DSN must also exist on the computer where the Excel file is opened to refresh the data.
|
Scenario You need to create an Excel linked query so that the Regional Sales Manager, can track whether the Sales Representatives in his region meet their monthly and quarterly goals. By providing a linked query, you only have to create the query once, and then the Regional Sales Manager can refresh the data at any time to view the latest sales information. |
To create an Excel linked query
- Connect to the database.
- Create a query in the Query Builder or Editor.
- Execute the query to display data results.
-
Select the result set and click one of the following options from the toolbar:
- Save the Excel spreadsheet.
- Email or upload the spreadsheet to a shared network folder to share it with colleagues.
To refresh data in an Excel linked query
- Open the spreadsheet with the linked query in Excel. The original data from the query displays.
- In Excel, select Data | Refresh to update the original data with the current data.
-
If prompted, enter a user name and password to connect to the database and refresh the data.
or
If the following error displays: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified", you must specify the DSN used by the query on your computer. Complete the following steps to resolve this issue:
- Click OK.
- Select the Machine Data Source tab, and then click New.
- Use the wizard to select a User Data Source and create a DSN that points to the database.
- Click OK to return to Excel and open an ODBC Driver Connect window.
- Enter a user name and password to refresh the data.
(Excel 2003)
Publish Data to Intelligence Central
About Publishing Data
You can use Toad Data Point to publish data to a Toad Intelligence Central (TIC) server in order to store and share data from remote data sources. Intelligence Central is like a federated server where you can store data from a variety of data sources. See About Toad Intelligence Central for more information.
Note:This feature is available in the Toad Data Point Professional edition only and requires access to a Toad Intelligence Central server.
What Data Sources You Can Publish From
You can publish data from any data source that you can connect to using Toad. Data sources that use native provider connections must also be ODBC-compliant.
What Type of Objects Can Be Published
Data can be published in the form of views, snapshots, and datasets. See Data Objects in Toad Intelligence Central for more information about these objects.
How Data is Published From Toad
You can publish data from any of the following: an object in the Object Explorer, a query statement in the Editor or Query Builder, or the data in a data grid.
Connectivity and Publishing
Toad Data Point uses three types of connectivity: native database provider connectivity, ODBC connectivity, and Data Services (Business Intelligence and NoSQL) connectivity. However, Toad Intelligence Central uses only ODBC and Data Services connectivity. If you publish data objects to a TIC server using a native database provider connection, the connection is converted to ODBC on the TIC server.
Before Publishing
If you are publishing data using a native provider or ODBC connection, the corresponding ODBC driver must exist on the TIC server. In addition, for native database connections other necessary client files must exist on the TIC server. To learn how to prepare the TIC server for publishing, see Prepare to Publish Data to Toad Intelligence Central.
Prepare to Publish Data
Toad Intelligence Central uses the following types of connectivity to make connections to remote data sources:
- Data Services (for Business Intelligence and NoSQL connections)
- ODBC (for native-provider and ODBC connections)
Because Intelligence Central uses ODBC connectivity, when data is published from a native-provider connection, Toad converts the connection to ODBC (if the database is ODBC-compliant). Before you can successfully publish data from a native-provider (or ODBC) connection, everything necessary to create the correct corresponding ODBC connection must be installed on the Intelligence Central server. This includes the corresponding ODBC driver for the database type, as well as any additional client files required for a correct ODBC connection.
If data is published from an ODBC connection, you must ensure that the DSN name on the Toad Data Point computer and the Intelligence Central server match.
Note:This feature is available in the Toad Data Point Professional edition only and requires access to a Toad Intelligence Central server.
Prepare ODBC Connections in Toad Data Point
If You Use ODBC Connectivity - Use System DSNs
If you plan to publish data objects from a remote database connection using ODBC connectivity in Toad Data Point, the ODBC connection must use a System DSN. This is required for compatibility with Intelligence Central, which uses only System DSNs.
To specify a System DSN for an ODBC connection
- Open the ODBC Data Source Administrator by selecting Start Menu | Control Panel, and then select ODBC or Data Sources (ODBC).
-
Select the System DSN tab and click Add.
Note: You must configure a System DSN because TIC uses only System DSNs.
- Then select the ODBC driver for the database to which you want to connect.
- Configure the remaining settings.
Prepare the Intelligence Central Server
In order for Toad Data Point users to successfully publish data to Intelligence Central, the Intelligence Central server must have the same ODBC driver, DSN name, and necessary client files that exist on the Toad Data Point user’s computer for each data source. These files are used by Toad Data Point to create the data source connection.
For the data source type you will be using, ensure that the necessary drivers and files are installed on the Intelligence Central server, and that they are the same as those installed on the Toad Data Point user’s computer.
Install Toad Data Point
Ensure Toad Data Point is installed on the Toad Intelligence Central host computer. If you plan to schedule and run Automation scripts on Intelligence Central, Toad Data Point must be installed. Additional benefits of having Toad Data Point installed along-side Intelligence Central include the following:
- The ODBC drivers for MySQL and SAP ASE, IQ, SQL Anywhere, and HANA are included in the Toad Data Point installation. This will ensure that theses drivers are installed on the Intelligence Central server.
- You can easily use Toad Data Point to help debug any connectivity issues.
Beginning with Intelligence Central 3.0, when you install using the Toad Intelligence Central Server Installer, Toad Data Point is automatically installed.
For best results, ensure the version of Toad Data Point on the Intelligence Central host computer is the same (or later) as that used to create the Automation scripts.
Install Additional ODBC Drivers and Necessary Client Files
In addition to installing Toad Data Point, install these additional files if publishing from any of these data sources.
Publishing from Oracle Data Sources
To prepare the Intelligence Central server to receive published data objects from an Oracle database, please ensure that the following are installed on the TIC server:
- OCI client or Oracle client—Oracle Instant Client or standard Oracle client.
- Oracle ODBC driver—If using Oracle Instant Client, you must also install the Oracle ODBC driver. If using the standard Oracle client, the Oracle ODBC driver is included.
- Tnsnames.ora file
Publishing from DB2 Data Sources
To prepare the Intelligence Central server to receive published data objects from a DB2 database, please ensure that the following are installed on the TIC server:
- DB2 client
- DB2 ODBC driver
- DB2 catalog setup
Publishing from Teradata Data Sources
To prepare the Intelligence Central server to receive published data objects from a Teradata database, please ensure that the following are installed on the TIC server:
- Teradata .Net Data Provider
- Teradata ODBC driver. You can download a Teradata driver from http://www.teradata.com.
Ensure Correct DSN Name
Publishing from ODBC Connections
If you plan to publish objects from Toad Data Point using ODBC connectivity, the Intelligence Central DSN must match the DSN of the original connection used to publish the object.
Ensure the following are true about the Toad Data Point and Intelligence Central ODBC connections:
- Both connections must use a System DSN.
- The Intelligence Central ODBC data source must use the same DSN name as the Toad Data Point connection from which the object was published.
Configure SMTP Transmission for Automation
If you plan to run Toad Data Point Automation scripts that send email, the Intelligence Central server must be configured to support SMTP email transmission. For more information about preparing the Intelligence Central server for published Automation scripts, see Prepare to Publish Automation Scripts.
Install Microsoft Excel
If an Automation Script scheduled to execute on the Toad Intelligence Central server includes an instruction to run a macro in an Excel spreadsheet, then Microsoft Excel must be installed on the Toad Intelligence Central host computer. For more information about preparing the Intelligence Central server for published Automation scripts, see Prepare to Publish Automation Scripts.