Spotfire® User Guide

Connector for TIBCO Data Virtualization — Features and settings

You can connect to and access data from TIBCO Data Virtualization. On this page, you can find information about the capabilities, available settings, and things to keep in mind when you work with data connections to TIBCO Data Virtualization.

Page content

Connector features

The following functionality is available when you access data with the connector for TIBCO Data Virtualization.

Feature Supported?
Load methods
  • Import (in-memory)
  • External (in-database)
  • On-demand
Custom queries Yes
Stored procedures No
Custom connection properties No
Single sign-on with identity provider Yes
Authoring in web client No
Included in Spotfire distribution on Linux Yes

Data source properties

The following are the supported data source properties that you can configure when you create a data connection with the connector for TIBCO Data Virtualization.

Option Description
Server The name of the server where your data is located. To include a port number, add it directly after the name preceded by colon.
Example with port number: 
MyDatabaseServer:1234
Encrypt (SSL) The TIBCO Data Virtualization connector has SSL support.
Authentication method The authentication method to use when logging into the database. Choose from the following options:
Username and password
With Username and password the authentication is done using a database user. Database credentials can be stored, unencrypted, as part of the analysis file, using a setting in the Data Source Settings dialog. If credentials are found in the analysis file they will be used to automatically authenticate against the database.

If no credentials or credentials profiles are found in the analysis file all who open the file will be prompted for database credentials.

Note that there will be no prompting for credentials if the credentials embedded in the analysis file fail.

Kerberos authentication

When using Kerberos authentication the access token of the logged in user will be used. Users that have been given the appropriate access rights to the TIBCO Data Virtualization system will be able to connect and read data.

Domain credentials are not stored in the analysis file.

NTLM authentication
When using NTLM authentication, the access token of the logged in user will be used. Users that have been given the appropriate access rights to the TIBCO Data Virtualization system will be able to connect and read data. Domain credentials are not stored in the analysis file.
Identity provider (OAuth2)

[Only available in on-premises Spotfire environments.] Use an identity provider, such as Okta or Keycloak, to log in to TIBCO Data Virtualization. When you open the data connection, a web browser window opens, and you log in with the identity provider's login procedure.

If you use the same identity provider for authentication in Spotfire and TIBCO Data Virtualization, you can access your data with single sign-on in Spotfire.

To be able to use this authentication method, you must add your identity provider in the Spotfire configuration. See Single sign-on with an identity provider (OAuth2) for connectors.

Kerberos SPN [Only applicable when using Kerberos authentication.]

The service principal name (SPN) used to identify the Kerberos authentication service.

Domain The authentication domain to use when logging into the TIBCO Data Virtualization system.
Username The username you wish to use when logging into the TIBCO Data Virtualization system.
Password The password for the specified username.
Identity provider [Only applicable when using Identity provider (OAuth2) authentication.]

Select the identity provider you want to use for logging in to TIBCO Data Virtualization.

The options available in the drop-down menu are the identity providers you have added to the OAuth2IdentityProviders preference.

Scopes [Only applicable when using Identity provider (OAuth2) authentication.]
Scopes determine what permissions Spotfire requests on your behalf when you log in to TIBCO Data Virtualization.
Default
Use the default scopes that you have specified for your identity provider in the OAuth2IdentityProviders preference.
Custom
Enter scopes manually in the text box. Separate values with a space. 
Scope_1 Scope_2
Connect Connects you to the specified server and populates the list of available databases below.
Database Select the database of interest from the drop-down list.

Supported data types

When you are setting up a connection to an external data source, Spotfire needs to map the data types in the data source to data types in Spotfire. The following are the data types that the TIBCO Data Virtualization connector supports.

Note: In custom queries, Spotfire parses INTERVAL types to String. This is because of a limitation in the driver regarding non-specific type IDs. If you want the column or columns to be of, for example, the TimeSpan type, you can edit the column's type and cast it to INTERVAL DAY TO SECOND in the custom query.
Database data type Spotfire data type
BINARY Binary
BLOB Binary
VARBINARY Binary
BOOLEAN Boolean
BIT Boolean
DATE Date
INTERVAL YEAR TO MONTH Date
Note: Note: The date 00-00-000 [mm-dd-yyyy] is not valid, since in Spotfire, the data type 'Date' starts from 01-01-0000. This is the reason for the conversion of INTERVAL YEAR TO MONTH to 'Date' instead of 'Integer'.
TIMESTAMP DateTime
INTEGER Integer
INTERVAL MONTH Integer
INTERVAL YEAR Integer
SMALLINT Integer
TINYINT Integer
BIGINT LongInteger
REAL Real
FLOAT SingleReal
CHAR String
CLOB String
LONGVARCHAR String
STRING String
VARCHAR String
XML String
TIME Time
INTERVAL TimeSpan
INTERVAL DAY TimeSpan
INTERVAL DAY TO HOUR TimeSpan
INTERVAL DAY TO MINUTE TimeSpan
INTERVAL DAY TO SECOND TimeSpan
INTERVAL HOUR TimeSpan
INTERVAL HOUR TO MINUTE TimeSpan
INTERVAL HOUR TO SECOND TimeSpan
INTERVAL MINUTE TimeSpan
INTERVAL MINUTE TO SECOND TimeSpan
INTERVAL SECOND TimeSpan
DECIMAL(p, s) When p <= 9 and s = 0: Integer

When 9 < p <= 18 and s = 0: LongInteger

Else: Real

Supported functions

Supported functions are the functions that you can use when you work with in-database data tables, for example for calculated columns and custom expressions. If a function is not supported natively by your database then TIBCO Data Virtualization imports the data and computes it in-memory automatically.

Note: Some supported functions might not be possible to use with your database. This depends on what functions are available in the database, which often differs between database versions and types.

The following are the functions that the TIBCO Data Virtualization connector supports.

Function type Functions supported
Date and Time Day, DayName, DayOfMonth, Days_Between, Hour, MilliSecond, Minute, Month, Months_Between, Second, Quarter, Year
Conversion SN
Math Abs, ACos, ASin, ATan, Cbrt, Ceiling, Cos, Cot, Degrees, Exp, Floor, Ln, Log, Pi, Power, Radians, Rand, Random, Round, Sin, Sqrt, Tan
Operators +, -, *, /, %
Statistical Avg, Corr, Count, CoVar_Pop, CoVar_Samp, Max, Median, Min, Sum, Stddev, Stddev_Pop, Stddev_Samp, UniqueCount, Var_Pop, Var_Samp, Variance, Variance_Pop, Variance_Samp
Text Concat, Dle_Dst, InitCap, Instr, Le_Dst, Length, Lower, LPad, RPad, RTrim, Trim, Upper, Space, Replace, Substring
Note: In Spotfire versions earlier than 12.3.0, when you created a connection to TIBCO Data Virtualization, you had to specify the type of data source in the System Selection drop-down menu. The selection determined what functions you could use, because not all functions were available for all databases.

The System Selection drop-down menu is no longer available. When you open an analysis in a Spotfire version older than 12.3.0, the System Selection will be Default automatically. If the function that you use in your analysis is data source specific and not available in Default, you might see an error message similar to 'Missing function: <function>' or observe other unwanted behavior of the data.

Copyright © Cloud Software Group, Inc. All rights reserved.