TIBCO Data Virtualization® Adapter Online Help > TDV File Data Sources > Microsoft Excel Data Sources > Adding Microsoft Excel Data Sources
 
Adding Microsoft Excel Data Sources
The Microsoft Excel driver leverages Microsoft ODBC. Excel sheets and named areas within sheets are introspected as TDV tables.
For more information about semijoin fields, see the TDV Administration Guide.
To add a Microsoft Excel data source on Windows
1. Before introspecting the Excel data sheet as a TDV available data source, configure it as an ODBC-available data source with a DSN locally accessible to the TDV Server.
2. Right-click at a location in the Studio resource tree where you want this data source to reside, and select New Data Source.
3. In the New Physical Data Source dialog, select Microsoft Excel and click Next.
4. Type a name for the data source.
5. On the Basic tab, provide this information for a Microsoft Excel data source:
— DSN—Type the DSN (Data Source Name) that was defined when the data source was added to the host machine.
— Character Set—Character encoding type. See Supported Character Encoding Types.
6. Click the Advanced tab.
7. On the Advanced tab, provide:—
Advanced Tab Options
Description
Connection URL Pattern
A URL pattern that functions as a template for generating an actual URL to connect to the physical data source. Modify this template per implementation requirements, but be aware that TDV does not validate modifications. The data source adapter may or may not validate changes, ignoring invalid URL connection parameters.
Connection URL String
The literal URL string that is generated from the connection URL pattern with the connection information you provide. This string is used by the JDBC adapter to connect to the physical data source. This field is generated by the system and is not editable. For further details, see “Configuring TDV Data Connections” in the TDV Administration Guide.
When TDV is running on a Windows platform you can access a file located on the network with a file URL like the following:
file://10.1.2.199/d$/public/folder/ExcelFileName.xls
If you map a network drive from the computer hosting TDV to connect the computer to file resources, you can browse to the directory to introspect more than one Excel file at a time, or specify a file URL to add a single file. The following is an example of a Windows file URL:
file:///Z:/shared/folder/ExcelFileName.xls
Connection Properties
Enables specification of property-value pairs that are passed to the targeted JDBC data source to determine the data source behavior for the connection with TDV. A selection of commonly used properties for all the supported versions of MySQL, Oracle, and Sybase are populated on the Advanced tab with default values.
JDBC Connection Properties
Click to open a window in which to add custom JDBC connection properties for any JDBC data source. You can add multiple property-value pairs by clicking the Add Argument button, or delete pairs by clicking the Remove Argument button adjacent to them. TDV does not validate property names. The data source adapter might ignore incorrectly named properties or invalid values, or it might provide an error code.
Maximum Connection Lifetime (m)
Sets the duration, in minutes, that an inactive connection (a connection that was returned to the pool) persists if there are more open connections than the minimum pool size. The duration is calculated from the creation time not from the time that the connection was released to the pool. A value of “0” allows connections to last indefinitely. Default: 60 minutes.
Connection Validation Query
A native data source query that is sent directly to the data source without evaluation by the TDV query engine. Enter a query that gives a quick return. If the validation query returns a non-error result, this fact validates the connection to the data source.
Execution Timeout (s)
The number of seconds an execution query on the data source is allowed to run before it is canceled. The default value of zero seconds disables the execution timeout so processes that take a long time are allowed to run. For example, cache updates set to run at non-peak processing hours can be resource intensive processes that take much longer than a client initiated request.
Execute SELECTs Independently
If this option is checked, a SELECT statement submitted to this data source is executed using a new connection from the connection pool and committed immediately after the SELECT is completed. INSERT, UPDATE, and DELETE statements continue to be executed using the same connection as part of the transaction.
Connection Check-out Procedure
Specify the procedure to return a valid SQL statement for that database which can be used to initialize the connection. One common case is to initialize Oracle Virtual Private Database (VPD)-based systems.
VPD is a method of doing row-level security. Complex security policies can be set to allow or deny access to subsets of data in a table. After the connection is made, often with a generic account, the client enables certain sets of access rights by setting a security context. In this case, the init procedure returns something like dbms_session.set_identifier('username'). This would then be executed on the connection, changing the privileges associated with that connection from the default to those associated with the username passed.
In addition, other parameters can be changed. A block like this might be returned by the init procedure:
BEGIN
dbms_session.set_identifier('username');
EXECUTE IMMEDIATE 'alter session set optimizer_index_cost_adj=10';
EXECUTE IMMEDIATE 'alter session set optimizer_index_caching=90';
EXECUTE IMMEDIATE 'alter session set "_complex_view_merging"=true';
END;
 
This example code is Oracle-specific. Others databases have similar functions.
The signature of the init procedure should look like this:
IN ds_name VARCHAR, OUT sqlText VARCHAR)
 
The code should be written such that the init procedure causes rights to be revoked if not called with the appropriate context.
Supports Star Schema
Check this option if this data source can support large predicates for star schema semijoins. Do not check this option unless you are sure the data source can support receiving queries with large predicates and large cardinalities. Refer to Refer the User Guide, Section Star Schema Semijoin for more information.
Max Source Side Cardinality for Semi Join
Sets the maximum source-to-source ratio of cardinality for semijoins.
Min Target to Source Ratio for Semi Join
Sets the minimum target-to-source ratio of cardinality for semijoins.
Max Source Side of Semi Join to Use OR Syntax
Sets the maximum source-side use of the OR syntax for semijoins.
8. Click one of these buttons:
— Create & Introspect—To proceed immediately with introspection.
— Create & Close—To create the data source; you can introspect at a later time.
9. See Refer the User Guide, Section Introspecting Data Sources for how to introspect now or later.