Table of contents
  1. Tools
    1. Find
      1. Find
      2. Searching in TIBCO Spotfire
    2. Data Relationships
      1. What is the Data Relationships Tool?
      2. How to Use Data Relationships
      3. Details on Data Relationships
      4. Data Relationships Column Descriptions
      5. Data Relationships Error Codes
      6. Theory and Models
        1. Overview of Data Relationships Theory 
        2. Data Relationships Linear Regression Algorithm 
        3. Data Relationships Spearman R algorithm
        4. Data Relationships Anova Algorithm 
        5. Data Relationships Kruskal-Wallis Algorithm
        6. Data Relationships Chi-square Independence Test Algorithm
        7. Requirements on Input Data for Data Relationships
    3. K-means Clustering
      1. How to Perform a K-means Clustering
      2. Details on K-means Clustering
    4. Line Similarity
      1. How to Perform a Line Similarity Comparison
      2. Details on Line Similarity
    5. Hierarchical Clustering
      1. What is the Hierarchical Clustering Tool?
      2. Details on Hierarchical Clustering
      3. Theory and Practice
        1. Overview of Hierarchical Clustering Theory
        2. Distance Measures
          1. Distance Measures Overview
          2. Correlation
          3. Cosine Correlation
          4. Tanimoto Coefficient
          5. Euclidean Distance
          6. City Block Distance
          7. Square Euclidean Distance and Half Square Euclidean Distance
        3. Clustering Methods
          1. Clustering Methods Overview
          2. UPGMA
          3. WPGMA
          4. Single Linkage
          5. Complete Linkage
          6. Ward's Method
        4. Ordering Weight
        5. Hierarchical Clustering References
    6. Predictive Modeling
      1. What is Predictive Modeling?
      2. Regression Modeling
        1. Linear Regression Method
        2. Regression Tree Method
        3. Details on Regression Modeling – General
        4. Details on Regression Modeling – Options
      3. Classification Modeling
        1. Logistic Regression Method
        2. Classification Tree Method
        3. Details on Classification Modeling – General
        4. Details on Classification Modeling – Options
      4. How To Use the Model Page
        1. The Model Page
        2. Using a Model Summary
        3. Using a Table of Coefficients
        4. Available Diagnostic Visualizations
      5. How To Use The Evaluation Page
        1. The Evaluation Page
        2. Using an Evaluation Summary
        3. Using a Confusion Matrix
        4. Available Diagnostic Visualizations
      6. What is the Analytic Models Panel?
      7. Details on Duplicate Model
      8. Details on Evaluate Model
      9. Details on Insert Predicted Columns
    7. Data Functions
      1. What are Data Functions?
      2. How to Use Data Functions
      3. Details
        1. Details on Register Data Functions
        2. Details on Input Parameter
        3. Details on Output Parameter
        4. Details on Save as Library Item
        5. Details on Data Functions – Select Function
        6. Details on Data Functions – Parameters
        7. Details on Select Columns
        8. Details on TIBCO Spotfire Statistics Services Login
      4. Data Type Mapping
      5. Name Encoding for Column Names Sent to Spotfire Statistics Services
    8. Information Designer
      1. What is the Information Designer?
      2. General Workflow
      3. General Guidelines for Setting Up an Information Model
      4. Icon Explanations
      5. Fundamental Concepts 
      6. Information Links
        1. Information Links
        2. Editing Information Links
          1. Creating an Information Link 
          2. Modifying an Information Link 
          3. Deleting an Information Link 
          4. Adding Hard Filters 
          5. Adding Prompts 
          6. Using Prompt Groups 
          7. Adding Procedures
          8. Parameterized Information Links
          9. Editing a Parameter
          10. Personalized Information Links
          11. Editing the SQL of an Information Link 
          12. Selecting Join Path
        3. Opening Information Links
          1. Opening Information Links from Information Designer 
          2. Using Prompts 
          3. Prompted Information Link Example 
          4. Using Current Filter Settings as a Filter
        4. Transforming the Data
          1. Eliminating Duplicates 
          2. Pivoting Data in Information Designer 
          3. Example of Pivoting in Information Designer
          4. Example of Pivoting with Aggregation in Information Designer
      7. Data Sources
        1. Data Sources Overview
        2. Creating a Data Source
        3. Modifying a Data Source
        4. Removing a Data Source
        5. Creating a Table Alias in Information Designer
      8. Folders
        1. Folders Overview 
        2. Creating a New Folder in Information Designer
        3. Setting Folder Permissions 
        4. Editing a Folder
        5. Deleting a Folder 
        6. Moving a Folder 
      9. Joins
        1. Overview 
        2. Creating a Join in Information Designer 
        3. Freehand Joins in Information Designer
        4. Editing a Join in Information Designer
        5. Deleting a Join in Information Designer
        6. Creating a Table Alias in Information Designer
      10. Column Elements
        1. Overview 
        2. Creating a Column Element in Information Designer
        3. Creating Multiple Column Elements in Information Designer
        4. Editing a Column in Information Designer
        5. Deleting a Column in Information Designer
        6. Calculating a Column in Information Designer
        7. Defining a Column Filter in Information Designer
        8. Date and Time Columns in Information Designer
        9. Working with Aggregation
          1. Using Aggregation in Information Designer
          2. Aggregating Over Many Columns in Information Designer
          3. Using Drillable 
      11. Filter Elements
        1. Overview 
        2. Creating a Filter Element 
        3. Editing a Filter in Information Designer
        4. Deleting a Filter in Information Designer
        5. Relational Operators
      12. Procedures
        1. Overview
        2. Creating a Pre- or Post-procedure
        3. Creating a Query Procedure
        4. Editing a Procedure
        5. Deleting a Procedure
        6. Multiple Value Procedure Prompts
      13. User Interface Details
        1. Elements Tree
        2. Icon Explanations 
        3. Data Sources Tree
        4. Tab
          1. Start Tab
          2. Information Link Tab
          3. Column Element Tab
          4. Multiple Column Elements Tab
          5. Filter Element Tab
          6. Procedure Element Tab
          7. Join Element Tab 
          8. Data Source Tab 
        5. Details
          1. Pop-up Menus
          2. Details on Edit SQL 
          3. Pivot Conditioning in Information Designer
          4. Details on Prompt Groups
          5. Details on Add Column
          6. Details on the Save As Dialog
          7. Details on Create Multiple Columns
          8. Details on Add/Edit Column Property
          9. Details on Add/Edit Information Link Property
          10. Details on Open Information Link
          11. Details on Column Values
          12. Details on Edit Parameter
          13. Details on Missing Parameter Value
          14. Details on Missing Information Link
          15. Details on Create Default Information Model
          16. Details on Select Destination Folder
          17. Data Source Login Dialog
          18. Details on Edit Properties
      14. Tips and Examples
        1. Understanding Filters in Information Designer
        2. When to Use Prompt Groups 
        3. Replacing Null 
        4. Limiting the Number of Records Returned 
        5. Concatenating Strings 
        6. Aggregate Functions
        7. Examples of Generated SQL
          1. SQL – Filters 
          2. SQL – GROUP BY 
          3. SQL – Subqueries 
        8. PL/SQL Functions
    9. Library Administration
      1. Introduction
      2. Permissions
      3. Creating a New Folder
      4. Deleting an Item
      5. Moving an Item
      6. Copying an Item
      7. Importing to Library
      8. Exporting from Library
      9. Searching the Library
      10. Editing Properties
      11. Editing Folder Permissions
      12. Copying URLs
      13. How Are Conflicts Resolved?

Tools

Last modified
Table of contents
  1. Tools
    1. Find
      1. Find
      2. Searching in TIBCO Spotfire
    2. Data Relationships
      1. What is the Data Relationships Tool?
      2. How to Use Data Relationships
      3. Details on Data Relationships
      4. Data Relationships Column Descriptions
      5. Data Relationships Error Codes
      6. Theory and Models
        1. Overview of Data Relationships Theory 
        2. Data Relationships Linear Regression Algorithm 
        3. Data Relationships Spearman R algorithm
        4. Data Relationships Anova Algorithm 
        5. Data Relationships Kruskal-Wallis Algorithm
        6. Data Relationships Chi-square Independence Test Algorithm
        7. Requirements on Input Data for Data Relationships
    3. K-means Clustering
      1. How to Perform a K-means Clustering
      2. Details on K-means Clustering
    4. Line Similarity
      1. How to Perform a Line Similarity Comparison
      2. Details on Line Similarity
    5. Hierarchical Clustering
      1. What is the Hierarchical Clustering Tool?
      2. Details on Hierarchical Clustering
      3. Theory and Practice
        1. Overview of Hierarchical Clustering Theory
        2. Distance Measures
          1. Distance Measures Overview
          2. Correlation
          3. Cosine Correlation
          4. Tanimoto Coefficient
          5. Euclidean Distance
          6. City Block Distance
          7. Square Euclidean Distance and Half Square Euclidean Distance
        3. Clustering Methods
          1. Clustering Methods Overview
          2. UPGMA
          3. WPGMA
          4. Single Linkage
          5. Complete Linkage
          6. Ward's Method
        4. Ordering Weight
        5. Hierarchical Clustering References
    6. Predictive Modeling
      1. What is Predictive Modeling?
      2. Regression Modeling
        1. Linear Regression Method
        2. Regression Tree Method
        3. Details on Regression Modeling – General
        4. Details on Regression Modeling – Options
      3. Classification Modeling
        1. Logistic Regression Method
        2. Classification Tree Method
        3. Details on Classification Modeling – General
        4. Details on Classification Modeling – Options
      4. How To Use the Model Page
        1. The Model Page
        2. Using a Model Summary
        3. Using a Table of Coefficients
        4. Available Diagnostic Visualizations
      5. How To Use The Evaluation Page
        1. The Evaluation Page
        2. Using an Evaluation Summary
        3. Using a Confusion Matrix
        4. Available Diagnostic Visualizations
      6. What is the Analytic Models Panel?
      7. Details on Duplicate Model
      8. Details on Evaluate Model
      9. Details on Insert Predicted Columns
    7. Data Functions
      1. What are Data Functions?
      2. How to Use Data Functions
      3. Details
        1. Details on Register Data Functions
        2. Details on Input Parameter
        3. Details on Output Parameter
        4. Details on Save as Library Item
        5. Details on Data Functions – Select Function
        6. Details on Data Functions – Parameters
        7. Details on Select Columns
        8. Details on TIBCO Spotfire Statistics Services Login
      4. Data Type Mapping
      5. Name Encoding for Column Names Sent to Spotfire Statistics Services
    8. Information Designer
      1. What is the Information Designer?
      2. General Workflow
      3. General Guidelines for Setting Up an Information Model
      4. Icon Explanations
      5. Fundamental Concepts 
      6. Information Links
        1. Information Links
        2. Editing Information Links
          1. Creating an Information Link 
          2. Modifying an Information Link 
          3. Deleting an Information Link 
          4. Adding Hard Filters 
          5. Adding Prompts 
          6. Using Prompt Groups 
          7. Adding Procedures
          8. Parameterized Information Links
          9. Editing a Parameter
          10. Personalized Information Links
          11. Editing the SQL of an Information Link 
          12. Selecting Join Path
        3. Opening Information Links
          1. Opening Information Links from Information Designer 
          2. Using Prompts 
          3. Prompted Information Link Example 
          4. Using Current Filter Settings as a Filter
        4. Transforming the Data
          1. Eliminating Duplicates 
          2. Pivoting Data in Information Designer 
          3. Example of Pivoting in Information Designer
          4. Example of Pivoting with Aggregation in Information Designer
      7. Data Sources
        1. Data Sources Overview
        2. Creating a Data Source
        3. Modifying a Data Source
        4. Removing a Data Source
        5. Creating a Table Alias in Information Designer
      8. Folders
        1. Folders Overview 
        2. Creating a New Folder in Information Designer
        3. Setting Folder Permissions 
        4. Editing a Folder
        5. Deleting a Folder 
        6. Moving a Folder 
      9. Joins
        1. Overview 
        2. Creating a Join in Information Designer 
        3. Freehand Joins in Information Designer
        4. Editing a Join in Information Designer
        5. Deleting a Join in Information Designer
        6. Creating a Table Alias in Information Designer
      10. Column Elements
        1. Overview 
        2. Creating a Column Element in Information Designer
        3. Creating Multiple Column Elements in Information Designer
        4. Editing a Column in Information Designer
        5. Deleting a Column in Information Designer
        6. Calculating a Column in Information Designer
        7. Defining a Column Filter in Information Designer
        8. Date and Time Columns in Information Designer
        9. Working with Aggregation
          1. Using Aggregation in Information Designer
          2. Aggregating Over Many Columns in Information Designer
          3. Using Drillable 
      11. Filter Elements
        1. Overview 
        2. Creating a Filter Element 
        3. Editing a Filter in Information Designer
        4. Deleting a Filter in Information Designer
        5. Relational Operators
      12. Procedures
        1. Overview
        2. Creating a Pre- or Post-procedure
        3. Creating a Query Procedure
        4. Editing a Procedure
        5. Deleting a Procedure
        6. Multiple Value Procedure Prompts
      13. User Interface Details
        1. Elements Tree
        2. Icon Explanations 
        3. Data Sources Tree
        4. Tab
          1. Start Tab
          2. Information Link Tab
          3. Column Element Tab
          4. Multiple Column Elements Tab
          5. Filter Element Tab
          6. Procedure Element Tab
          7. Join Element Tab 
          8. Data Source Tab 
        5. Details
          1. Pop-up Menus
          2. Details on Edit SQL 
          3. Pivot Conditioning in Information Designer
          4. Details on Prompt Groups
          5. Details on Add Column
          6. Details on the Save As Dialog
          7. Details on Create Multiple Columns
          8. Details on Add/Edit Column Property
          9. Details on Add/Edit Information Link Property
          10. Details on Open Information Link
          11. Details on Column Values
          12. Details on Edit Parameter
          13. Details on Missing Parameter Value
          14. Details on Missing Information Link
          15. Details on Create Default Information Model
          16. Details on Select Destination Folder
          17. Data Source Login Dialog
          18. Details on Edit Properties
      14. Tips and Examples
        1. Understanding Filters in Information Designer
        2. When to Use Prompt Groups 
        3. Replacing Null 
        4. Limiting the Number of Records Returned 
        5. Concatenating Strings 
        6. Aggregate Functions
        7. Examples of Generated SQL
          1. SQL – Filters 
          2. SQL – GROUP BY 
          3. SQL – Subqueries 
        8. PL/SQL Functions
    9. Library Administration
      1. Introduction
      2. Permissions
      3. Creating a New Folder
      4. Deleting an Item
      5. Moving an Item
      6. Copying an Item
      7. Importing to Library
      8. Exporting from Library
      9. Searching the Library
      10. Editing Properties
      11. Editing Folder Permissions
      12. Copying URLs
      13. How Are Conflicts Resolved?
  1. Tools
    1. Find
      1. Find
      2. Searching in TIBCO Spotfire
    2. Data Relationships
      1. What is the Data Relationships Tool?
      2. How to Use Data Relationships
      3. Details on Data Relationships
      4. Data Relationships Column Descriptions
      5. Data Relationships Error Codes
      6. Theory and Models
        1. Overview of Data Relationships Theory 
        2. Data Relationships Linear Regression Algorithm 
        3. Data Relationships Spearman R algorithm
        4. Data Relationships Anova Algorithm 
        5. Data Relationships Kruskal-Wallis Algorithm
        6. Data Relationships Chi-square Independence Test Algorithm
        7. Requirements on Input Data for Data Relationships
    3. K-means Clustering
      1. How to Perform a K-means Clustering
      2. Details on K-means Clustering
    4. Line Similarity
      1. How to Perform a Line Similarity Comparison
      2. Details on Line Similarity
    5. Hierarchical Clustering
      1. What is the Hierarchical Clustering Tool?
      2. Details on Hierarchical Clustering
      3. Theory and Practice
        1. Overview of Hierarchical Clustering Theory
        2. Distance Measures
          1. Distance Measures Overview
          2. Correlation
          3. Cosine Correlation
          4. Tanimoto Coefficient
          5. Euclidean Distance
          6. City Block Distance
          7. Square Euclidean Distance and Half Square Euclidean Distance
        3. Clustering Methods
          1. Clustering Methods Overview
          2. UPGMA
          3. WPGMA
          4. Single Linkage
          5. Complete Linkage
          6. Ward's Method
        4. Ordering Weight
        5. Hierarchical Clustering References
    6. Predictive Modeling
      1. What is Predictive Modeling?
      2. Regression Modeling
        1. Linear Regression Method
        2. Regression Tree Method
        3. Details on Regression Modeling – General
        4. Details on Regression Modeling – Options
      3. Classification Modeling
        1. Logistic Regression Method
        2. Classification Tree Method
        3. Details on Classification Modeling – General
        4. Details on Classification Modeling – Options
      4. How To Use the Model Page
        1. The Model Page
        2. Using a Model Summary
        3. Using a Table of Coefficients
        4. Available Diagnostic Visualizations
      5. How To Use The Evaluation Page
        1. The Evaluation Page
        2. Using an Evaluation Summary
        3. Using a Confusion Matrix
        4. Available Diagnostic Visualizations
      6. What is the Analytic Models Panel?
      7. Details on Duplicate Model
      8. Details on Evaluate Model
      9. Details on Insert Predicted Columns
    7. Data Functions
      1. What are Data Functions?
      2. How to Use Data Functions
      3. Details
        1. Details on Register Data Functions
        2. Details on Input Parameter
        3. Details on Output Parameter
        4. Details on Save as Library Item
        5. Details on Data Functions – Select Function
        6. Details on Data Functions – Parameters
        7. Details on Select Columns
        8. Details on TIBCO Spotfire Statistics Services Login
      4. Data Type Mapping
      5. Name Encoding for Column Names Sent to Spotfire Statistics Services
    8. Information Designer
      1. What is the Information Designer?
      2. General Workflow
      3. General Guidelines for Setting Up an Information Model
      4. Icon Explanations
      5. Fundamental Concepts 
      6. Information Links
        1. Information Links
        2. Editing Information Links
          1. Creating an Information Link 
          2. Modifying an Information Link 
          3. Deleting an Information Link 
          4. Adding Hard Filters 
          5. Adding Prompts 
          6. Using Prompt Groups 
          7. Adding Procedures
          8. Parameterized Information Links
          9. Editing a Parameter
          10. Personalized Information Links
          11. Editing the SQL of an Information Link 
          12. Selecting Join Path
        3. Opening Information Links
          1. Opening Information Links from Information Designer 
          2. Using Prompts 
          3. Prompted Information Link Example 
          4. Using Current Filter Settings as a Filter
        4. Transforming the Data
          1. Eliminating Duplicates 
          2. Pivoting Data in Information Designer 
          3. Example of Pivoting in Information Designer
          4. Example of Pivoting with Aggregation in Information Designer
      7. Data Sources
        1. Data Sources Overview
        2. Creating a Data Source
        3. Modifying a Data Source
        4. Removing a Data Source
        5. Creating a Table Alias in Information Designer
      8. Folders
        1. Folders Overview 
        2. Creating a New Folder in Information Designer
        3. Setting Folder Permissions 
        4. Editing a Folder
        5. Deleting a Folder 
        6. Moving a Folder 
      9. Joins
        1. Overview 
        2. Creating a Join in Information Designer 
        3. Freehand Joins in Information Designer
        4. Editing a Join in Information Designer
        5. Deleting a Join in Information Designer
        6. Creating a Table Alias in Information Designer
      10. Column Elements
        1. Overview 
        2. Creating a Column Element in Information Designer
        3. Creating Multiple Column Elements in Information Designer
        4. Editing a Column in Information Designer
        5. Deleting a Column in Information Designer
        6. Calculating a Column in Information Designer
        7. Defining a Column Filter in Information Designer
        8. Date and Time Columns in Information Designer
        9. Working with Aggregation
          1. Using Aggregation in Information Designer
          2. Aggregating Over Many Columns in Information Designer
          3. Using Drillable 
      11. Filter Elements
        1. Overview 
        2. Creating a Filter Element 
        3. Editing a Filter in Information Designer
        4. Deleting a Filter in Information Designer
        5. Relational Operators
      12. Procedures
        1. Overview
        2. Creating a Pre- or Post-procedure
        3. Creating a Query Procedure
        4. Editing a Procedure
        5. Deleting a Procedure
        6. Multiple Value Procedure Prompts
      13. User Interface Details
        1. Elements Tree
        2. Icon Explanations 
        3. Data Sources Tree
        4. Tab
          1. Start Tab
          2. Information Link Tab
          3. Column Element Tab
          4. Multiple Column Elements Tab
          5. Filter Element Tab
          6. Procedure Element Tab
          7. Join Element Tab 
          8. Data Source Tab 
        5. Details
          1. Pop-up Menus
          2. Details on Edit SQL 
          3. Pivot Conditioning in Information Designer
          4. Details on Prompt Groups
          5. Details on Add Column
          6. Details on the Save As Dialog
          7. Details on Create Multiple Columns
          8. Details on Add/Edit Column Property
          9. Details on Add/Edit Information Link Property
          10. Details on Open Information Link
          11. Details on Column Values
          12. Details on Edit Parameter
          13. Details on Missing Parameter Value
          14. Details on Missing Information Link
          15. Details on Create Default Information Model
          16. Details on Select Destination Folder
          17. Data Source Login Dialog
          18. Details on Edit Properties
      14. Tips and Examples
        1. Understanding Filters in Information Designer
        2. When to Use Prompt Groups 
        3. Replacing Null 
        4. Limiting the Number of Records Returned 
        5. Concatenating Strings 
        6. Aggregate Functions
        7. Examples of Generated SQL
          1. SQL – Filters 
          2. SQL – GROUP BY 
          3. SQL – Subqueries 
        8. PL/SQL Functions
    9. Library Administration
      1. Introduction
      2. Permissions
      3. Creating a New Folder
      4. Deleting an Item
      5. Moving an Item
      6. Copying an Item
      7. Importing to Library
      8. Exporting from Library
      9. Searching the Library
      10. Editing Properties
      11. Editing Folder Permissions
      12. Copying URLs
      13. How Are Conflicts Resolved?

Tools

Find

Find


The find tool is a fast way to find contents in your data, navigate in the analysis, and to perform actions found in the menus of Spotfire. It consists of a text field where you enter a search string and a list of results for the search.

Find.png

  • To reach the Find dialog:

  1. Press Ctrl+F.
    OR

  2. Select Tools > Find....

Searchable categories

  • Go to Page - If there are several pages in the analysis, select a page from the list to go to it.

  • Go to Visualization - Select a visualization to go to the relevant page in the analysis and highlight the visualization.

  • Tag Marked Rows With - If you have created tags in the Tags panel, select a tag in the list to add the marked values in the visualization to that tag.

  • Perform Action - Select an action to perform it.

  • Add Values To Marking - Mark a selected subset from the data table. If all the values are already marked, they will be unmarked by this. If there are multiple data tables, each table with matching data will get a separate category with the name of the data table added. Select All matching values to mark all the matching values in a specific column or in the entire data table. If there are more than 1000 matching values for a column, the individual values will not be listed, just the All matching values option.

  • Open File - Lists recently used files; select one to open it.

Example

When text is entered in the search field, all matching actions, parts of the analysis, and subsets of the data are returned. Some additional examples of what can be entered in the search field are:

  • If nothing is entered, the latest executed actions from find are listed.

  • y:sales will list all visualizations with Sales on the y-axis.

  • sales category:page will only list all pages with "sales" in the title. Category: can be used with all the categories listed above; type part of the category's name after the colon.

Syntax

You can also use logical expressions in your search strings. For information on the syntax for searches, see the Searching in TIBCO Spotfire page.

See also:

What are Tags?

Searching in TIBCO Spotfire


There are many places in TIBCO Spotfire where you can search for different items. For example, you can search for filters, analyses in the library or elements used to build information links in the Information Designer. All of the available search fields use the same basic search syntax, which is presented below. For more information regarding search of a specific item, see the links at the bottom of this page.

Basic Rules

  • By default, a search will match against all words in the name, description or keywords. Searching for Apple will match every item that includes words that start with Apple, and will match items with the values "Apples are tasty" and "This is an apple".

  • Search matches the beginning of all words. For example, ple will match Plenty and Plexiglas but not Apple.

  • Search is not case-sensitive.

  • Boolean AND is implicit when words in the search expression are separated by space. For example, Apple Banana will match anything where a word starts with Apple and another word starts with Banana.

Keyword

Example

Function

*

*ple

*ple*

Finds items with a word ending in ple.

Finds items where a word contains ple.

Quotation marks

"A Green Apple"

Finds items where the word starts with the phrase "A Green Apple".

Double quotation marks

"9"" nails"

Finds items where the word starts with 9" nails. Add a second double quote to escape a literal quote.

AND

Apple AND Fruit

Finds items with a word that starts with Apple and another word that starts with Fruit.

OR

Apple OR Banana

Finds items that include a word that starts with Apple or Banana.

NOT

Ban NOT *ana

Finds items that have a word that starts with Ban but does not end with ana. For example, Bangles and Banned would be found, but Banana would not.

( )

Apple and (Banana or Pear)

Used to group items in Boolean searches. See below for more information about searching for text within parentheses.

Quoted keywords

"and"    "or"    "not"

Finds strings that are protected keywords. Just typing and in the search field will not find anything since the word and is a protected keyword. If you need to search for the word "and", you must use quotation marks around it.

:

DataType:Integer

[Not applicable for row search.]

The colon is used to search for item attributes. In this example it finds columns where the column property DataType has a word that starts with Integer.

Almost any property can be used in the search, including custom properties. See Column Properties Descriptions for information about the available default column properties.

::

Name::Apple

 

 

 

 

 

DataType::Date

[Not applicable for row search.]

Finds items where the exact value of the property Name is Apple. In this example, an item named only Apple would match, but an item named Apple from Spain would not.

If you want to search for the Date data type, and not get any hits on DateTime columns, use :: instead of a single colon.

:<

DistinctValueCount:<10

[Not applicable for row search.]

Finds columns with less than or equal to 10 unique values.

:>

RowCount:>10

[Not applicable for row search.]

Finds columns with more than or equal to 10 values.

Null, Empty or not existing column property.

Tag:null  or  DistinctValueCount:null

 

 

 

Keywords:null

[Not applicable for row search.]

Finds all items with no Tags or if the column property DistinctValueCount does not exist.

 

Finds all items with no keywords.

Logical precedence

Search expressions are evaluated from left to right for logical operators with the same precedence. For example, the search expression:

Apple Banana or Pear

will be evaluated as  

(("Apple") AND (("Banana") OR ("Pear")))

Tip: If you cannot find what you are looking for, try adding more wildcards. For example, to locate a filter called "Sales ($)" , enter the search expression "Sales ($*", to avoid interpreting the text within the parenthesis as a Boolean expression.

See also:

Find

Searching the Library

Searching for Filters

Text Filter

Searching for elements in Information Designer

Data Relationships

What is the Data Relationships Tool?


The Data Relationships tool is used for investigating the relationships between different column pairs. The tool always works on the currently filtered data. The Linear regression and the Spearman R options allow you to compare numerical columns, the Anova option will help you determine how well a category column categorizes values in a (numerical) value column, the Kruskal-Wallis option is used to compare sortable columns to categorical columns, and the Chi-square option helps you to compare categorical columns.

For each combination of columns, the tool calculates a p-value, representing the degree to which the first column predicts values in the second column. A low p-value indicates a probable strong connection between two columns.

The resulting table displays the p-value for each combination of Y and X columns. The table is sorted by p-value. Clicking on a column heading will sort the rows according to that column.

Example:

Consider the following data table, which lists a few attributes of a group of people:

Eye color, Gender, Height (m), Weight (kg), Age

blue, female, 1.65, 62.7, 29

blue, female, 1.50, 57.0, 31

blue, female, 1.69, 64.2, 18

blue, male, 1.58, 63.2, 31

green, male, 1.76, 70.4, 44

green, male, 1.82, 72.8, 26

green, male, 1.92, 76.8, 33

green, female, 1.54, 61.6, 39

green, female, 1.76, 70.4, 22

brown, female, 1.67, 66.8, 34

brown, female, 1.47, 58.8, 41

brown, male, 1.69, 71.0, 23

brown, male, 1.78, 74.8, 35

brown, male, 1.83, 76.9, 20

brown, female, 1.62, 87, 62

blue, male, 1.87, 86.5, 23

brown, male, 1.76, 92, 65

brown, male, 1.62, 59, 13

green, female, 1.70, 59, 32

(To test the example, copy all of the above text and paste it in TIBCO Spotfire.)

  • To test if there is a relationship between numerical columns:

  1. Select Tools > Data Relationships....

    Response: The Data Relationships dialog is displayed.

  2. Select Linear Regression (numerical vs numerical) as the comparison method.

  3. Send all Available Y-columns to the Selected Y-columns list by clicking on them in the list and then click on Add >.
    WhatistheDataRelationshipsTool1.png

  4. Send all Available X-columns to the Selected X-columns list by clicking on them in the list and then click on Add >.
    WhatistheDataRelationshipsTool2.png

  5. Click OK.

    Response: A new data relationships table is created, together with a scatter plot based on the marked row in the table.

WhatistheDataRelationshipsTool3.png

The scatter plot shows the Y and X column from the currently marked row in the data relationships table. Since Height vs. Weight got the lowest p-value of all columns investigated, this column pair is listed first in the data relationships table, and is marked by default. Not surprisingly, it appears as if there is a correlation between the Height and Weight of the test persons.

By clicking on a different row in the data relationships table, the scatter plot changes to display the new column pair:

WhatistheDataRelationshipsTool4.png

The p-value for Age vs. Height is quite high and according to the scatter plot, there does not seem to be any significant correlation between those two columns in the current data.

See also:

How to Use Data Relationships

Details on Data Relationships

How to Use Data Relationships


  • To calculate Data Relationships:

  1. Select Tools > Data Relationships....

    Response: The Data Relationships dialog is displayed and all available columns are listed in the Available columns field.

  2. Type a Comparison name or use the default name Data Relationships.

  3. Select the Comparison method you wish to use, depending on the type of columns that you want to compare.

    Comment: Choose from Linear regression (numerical vs numerical)Spearman R (numerical vs numerical), Anova (numerical vs categorical)Kruskal-Wallis (sortable vs categorical) and Chi-square (categorical vs categorical).

  4. Move the desired columns into the two fields Selected Y-columns and Selected X-columns.

    Comment: Select columns from Available Y-columns and Available X-columns and click on the corresponding Add > button. You must select at least one column for the Y-columns field and one for the X-columns field. Click < Remove to move a column back to the list of available columns.

  5. Click OK.

    Response: A new page with the specified comparison name is created, showing a data relationships table and a scatter plot (for linear regression), a cross table (for chi-square) or a box plot (for Anova and Kruskal-Wallis).

  • To use the Data Relationships table to control the other visualization:

  1. In the data relationships table, click on the column pair you are interested in.

    Response: The visualization under the table is updated to show the currently marked columns on its axes.  

  • To update the Data Relationships table:

If you filter your data after performing a data relationships calculation, the p-values in the table will no longer reflect the current selection in your other visualizations, and the Refresh calculation icon will turn red.

  1. Click on the Refresh calculation icon to the left of the Data Relationships table, RefreshButton.png.

    Response: The Data Relationships table is updated to show calculated values for the currently filtered data.

  • To change the number of measures shown in the table:

  1. Right-click on the Data Relationships table.

  2. Select Properties from the pop-up menu.

  3. Select the Columns page in the dialog.

  4. Add or remove columns from the Selected columns list by using the Add > or < Remove buttons.

    Comment: Click on a column in the Selected columns list and then click Move Up or Move Down to change the order of the columns.

  5. Click Close.

  • To sort the data relationships table:

  1. Click on the column header by which you want to sort the table.

    Response: The table is sorted in ascending order.

    Comment: Click on the column heading again to sort in descending order. Note the small arrow beside the column title, showing the sort order.

  • To rearrange the horizontal order of the table:

  1. Place the mouse pointer on a table header.

  2. Drag the header to the desired position.

  3. To adjust the column width:

  1. Place the mouse pointer on the separator between two column headers.

  2. Click-and-drag the separator to the desired position.

    Comment: You can also right-click on the column header and select Column Width... from the pop-up menu to specify a column width in number of pixels.

See also:

What is the Data Relationships Tool?

Details on Data Relationships

Details on Data Relationships


DetailsonDataRelationships.png

Option

Description

Data table

Specifies the data table on which the calculation will be performed.

Calculation name

The name that will be displayed on the new page, created by the data relationships calculation.

Comparison method

The method by which you wish to compare the columns. Choose from Linear regression (numerical vs numerical)Spearman R (numerical vs numerical), Anova (numerical vs categorical)Kruskal-Wallis (sortable vs categorical) and Chi-square (categorical vs categorical).

Available Y-columns

The columns available for use on the Y-axis in the calculation.

Click a column name in the list to select it. To select more than one column, press Ctrl and click the column names in the list. Use the Add > button to send the selected columns to the Selected Y-columns field, see below.

Add >

Moves the selected columns from the Available Y-columns field to the Selected Y-columns field.

< Remove

Removes the selected columns from the Selected Y-columns field.

Remove All

Removes all columns from the Selected Y-columns field.

Selected Y-columns

The selected columns that you wish to compare against the columns below.

Available X-columns

The columns available for use on the X-axis in the calculation.

Click a column name in the list to select it. To select more than one column, press Ctrl and click the column names in the list. Use the Add > button to send the selected columns to the Selected X-columns field, see below.

Add >

Moves the selected columns from the Available X-columns field to the Selected X-columns field.

< Remove

Removes the selected columns from the Selected X-columns field.

Remove All

Removes all columns from the Selected X-columns field.

Selected X-columns

The selected columns. Categorical columns should typically not contain too many unique values. If more than 1000 unique values are available in a selected column, the resulting data relationships table for the Anova, Kruskal-Wallis and Chi-square options will display an error.

See also:

What is the Data Relationships Tool?

How to Use Data Relationships

Data Relationships Column Descriptions


The Data Relationships table displays a number of different measures for the different types of calculations. A description of the statistics available is found below:

All calculations

Option

Description

Y (numerical/categorical)

The name of the Y column concerned.

X (numerical/categorical)

The name of the X column concerned.

p-value

The calculated p-value, representing the degree to which the first column predicts values in the second column. A low p-value indicates a probable strong connection between two columns.

n

The number of valid pairs.

Linear regression

Option

Description

FStat

The F-statistic calculated according to [Ref. Arnold].

RSq

The squared correlation value.

R

The correlation value.

Df

The degrees of freedom = the number of non-empty rows in the column pair - 2.

Spearman R

Option

Description

FStat

The F-statistic calculated according to [Ref. Lehmann].

Rank R sqared

The square of rank R.

Rank R

The correlation of the ranked values of the X and Y columns.

Df

The degrees of freedom = the number of non-empty rows in the column - 2.

 

Anova

Option

Description

FStat

The F-statistic. See Anova algorithm for more information.

S2Btwn

The sum of squares between groups.

S2Wthn

The sum of squares within groups.

dfBtwn

The degree of freedom between groups.

dfWthn

The degree of freedom within groups.

Kruskal-Wallis

Option

Description

H-stat

The H-statistic. See Kruskal-Wallis algorithm for more information.

Df

The degrees of freedom = k-1, where k is the number of categories.

Chi-square

Option

Description

Chi2-stat

The Chi2-statistic, which is a direct relationship between the observed and the expected values.

Df

The degrees of freedom = (I-1)(J-1) where I is the number of unique values in the first column and J is the number of unique values in the second column.

See also:

Data Relationships Error Codes

Data Relationships Error Codes


If your data contain empty values or errors, or if filtering has reduced the number of valid rows too much, the data relationships calculation may result in errors for specific cells in the table. The available error codes are described below:

Error Code

Description

#No valid pairs

There were no valid rows to calculate on. This may occur if you have filtered out too many rows, or if the two compared columns have a different set of valid rows on which to perform the calculation only.

#Only one valid pair

There were not enough valid rows to perform a calculation. See above.

#Only two valid pairs

There were not enough valid rows to perform a calculation. The number of valid pairs must be larger than two. See '#No valid pairs' above.

#Only one unique value for column '{0}'  

This happens if the number of unique values equals one.

#More than 1000 unique values for categorical column '{0}'

Categorical data relationships methods, i.e., Anova, Kruskal-Wallis and Chi-square, cannot be used on columns where the number of unique values is larger than 1000. In that case, the number of categories to split by becomes too large.

#All values unique  for column '{0}'

Categorical data relationships methods, i.e., Anova, Kruskal-Wallis and Chi-square, cannot be used if you only have a single value within each category.

See also:

Data Relationships Column Descriptions

Theory and Models

Overview of Data Relationships Theory 

The Data Relationships tool calculates a probability value (p-value) for any combination of columns. This p-value can be used to determine whether or not the association between the columns is statistically significant.

  • Linear regression

  • Spearman R

  • Anova

  • Kruskal-Wallis

  • Chi-square

Linear regression

(For a mathematical description of linear regression, see Data Relationships Linear regression algorithm.)

The linear regression option is used to calculate an F-test investigating whether the independent variable X predicts a significant proportion of the variance of the dependent variable Y.

Linear regression, or the "least squares" method, works by minimizing the sum of the square of the vertical distances of the points from the regression line and obtain a correlation coefficient. The correlation coefficient can take values between -1 and +1. If there is a perfect negative correlation, then R=-1; if there is a perfect positive correlation, then R=+1. If R=0, then there is no correlation at all and the two columns are completely independent of each other.

OverviewofDataRelationshipsTheory1.png

Spearman R

(For a mathematical description of Spearman R, see Data Relationships Spearman R algorithm.)

The Spearman R option is used to calculate a nonparametric equivalent of the correlation coefficient. It is used on occasions when the variables can be ranked. Since it is only the rank of the values that is interesting in the calculation, Spearman R can be used even if the underlying distribution family is unknown, provided that each row can be assigned a rank. Similar to Linear regression, the correlation coefficient can take values between -1 and +1.

Anova

(For a mathematical description of Anova, see Data Relationships Anova algorithm.)

Anova means Analysis oVariance. The Anova option is used for investigating how well a category column categorizes a value column. For each combination of category column and value column, the tool calculates a p-value, representing the degree to which the category column predicts values in the value column. A low p-value indicates a probable strong connection between two columns.

Consider the following scatter plot representing data about eight subjects: gender (male/female), owns car (yes/no), income ($), and height (cm). Income is plotted on the horizontal axis, and height on the vertical.

OverviewofDataRelationshipsTheory2.png

Blue markers represent car owners, red markers represent non-car owners. Squares represent male subjects, circles female subjects. If we perform an Anova calculation with gender and car as category columns, and income and height as value columns, the result will be four p-values as follows.

Value column

Category column

p-value

Height

Car

0.00464

Income

Gender

0.047

Height

Gender

0.433

Income

Car

0.519

A low p-value indicates a higher probability that there is a connection between category and value column. In this case, Height and Car seem closely related, while Income and Car are not. We can verify this by examining the scatter plot.

See Requirements on input data for data relationships for more information about what data to use with this tool.

Kruskal-Wallis

(For a mathematical description of the Kruskal-Wallis test, see Data Relationships Kruskal-Wallis algorithm.)

The Kruskal-Wallis option is used to compare independent groups of sampled data. It is the nonparametric version of one-way Anova and is a generalization of the Wilcoxon test for two independent samples. The test uses the ranks of the data rather than their actual values to calculate the test statistic. This test can be used as an alternative to the Anova, when the assumption of normality or equality of variance is not met.

Chi-square

(For a mathematical description of the chi-square calculation, see Data Relationships Chi-square independence test algorithm.)

The chi-square option is used to compare observed data with the data that would be expected according to a specific hypothesis (for example, the null-hypothesis which states that there is no significant difference between the expected and the observed result). The chi-square is the sum of the squared difference between observed and expected data, divided by the expected data in all possible categories. A high chi-square statistic indicates that there is a large difference between the observed counts and the expected counts.

From the chi-square statistic it is possible to calculate a p-value. This value is low if the chi-square statistic is high. Generally, a probability of 0.05 or less is considered to be a significant difference.

Data Relationships Linear Regression Algorithm 

The Linear Regression option calculates the p-value under the assumption that there are no empty values in the data table.

Note: If there are empty values in the data table, the data table will first be reduced to the rows containing values for both the first and the second column.

Let n be the total number of values and denote by (xi, yi), i = 1, ..., n the set of data points to fit a straight line

DataRelationshipsLinearRegressionAlgorithm.png.

The p-value is then calculated from the F-distribution where the F-statistic is calculated with the sum of squares between the estimated line and the total mean of the yi's having one degree of freedom as numerator and the residual sum of squares divided by the number of degrees of freedom (n-2) as denominator.

References:

Arnold, Steven F., The Theory of Linear Models and Multivariate Analysis.

Rice, John A., Mathematical Statistics and Data Analysis, 2nd ed. pp 509.

Data Relationships Spearman R algorithm

The Spearman R option calculates the p-value under the assumption that there are no empty values in the data table.

Note: If there are empty values in the data table, the data table will first be reduced to the rows containing values for both the first and the second column.

The Spearman R calculation is a nonparametric comparison based on the ranks of the observations, rather than on the values themselves. This test can be used as an alternative to the Linear Regression, when the assumption of normality or equality of variance is not met. For example, this is useful on occasions where outliers contribute too much to the calculations in a parametric test.

Spearman R can be calculated in several different ways depending on whether or not ties are common in the data table , that is, if several values are identical and thus have the same rank. Since it is quite common with ties in general data analysis, TIBCO Spotfire uses an algorithm where these can be handled. When ties occur, they are all given the mean of the ranks that they would have had if they had not been exactly identical (see Ranking Functions, "ties.method=average").

The correlation value is calculated according to:

DataRelationshipsSpearmanRalgorithm1.png

where

N = the number of valid pairs of measurements (xi, yi),

fk= the number of ties in the kth group of ties among the Y-column values

and

gm= the number of ties in the mth group of ties among the X-column values.

 

The test statistic, FStat is then:

 

DataRelationshipsSpearmanRalgorithm2.png

where

rs2= RSq = the squared correlation value.

In TIBCO Spotfire, the Spearman t method has then been applied to calculate the p-values. This method has been chosen in order to allow the same calculation method to be used at all times and with an acceptable performance. The Spearman exact method is not suitable for cases with a lot of ties in the data. The Spearman Monte-Carlo method is suitable for any type of data, but when a lot of p-values are to be calculated then this method has too low performance.

References:

Lehmann, E. L., Nonparametrics: Statistical Methods based on Ranks (1975), p. 297 – 303.

Kendall, M., Rank Correlation Methods (1948), p. 37-54.

Data Relationships Anova Algorithm 

The Anova option computes the difference between groups by comparing the mean values of the data in each group. The results are obtained by testing the null hypothesis; the hypothesis that there is no difference between the means of the groups. More formally, the p-value is the probability of the actual or a more extreme outcome under the null-hypothesis.

Note: If there are empty values in the data table, the data table will first be reduced to the rows containing values for both the first and the second column.

  • For each combination of category and value column, a p-value is computed as follows:

  1. Rows are grouped according to their value in the category column.

  2. The total mean value of the value column is computed.
    DataRelationshipsAnovaAlgorithm1.png

  3. The mean within each group is computed.

  4. The difference between each value and the mean value for the group is calculated and squared.

  5. The squared difference values are added. The result is a value that relates to the total deviation of rows from the mean of their respective groups. This value is referred to as the sum of squares within groups, or S2Wthn.

  6. For each group, the difference between the total mean and the group mean is squared and multiplied by the number of values in the group. The results are added. The result is referred to as the sum of squares between groups, or S2Btwn.
    DataRelationshipsAnovaAlgorithm2.png

  7. The two sums of squares are used to obtain a statistic for testing the null hypothesis, the so called F-statistic. The F-statistic is calculated as:

    DataRelationshipsAnovaAlgorithm3.png
    where dfBtwn (degree of freedom between groups) equals the number of groups minus 1, and dfWthn (degree of freedom within groups) equals the total number of values minus the number of groups.

  8. The F-statistic is distributed according to the F-distribution (commonly presented in mathematical tables/handbooks). The F-statistic, in combination with the degrees of freedom and an F-distribution table, yields the p-value.

The p-value is the probability of the actual or a more extreme outcome under the null-hypothesis. The lower the p-value, the larger the difference.

Note: A very small p-value may also arise if an effect is tiny but the sample sizes are large. Similarly, a higher p-value can arise if the effect is large but the sample size is small. This is because the hypothesis tests whether the effect is zero or not.

Reference:

Arnold, Steven F., The Theory of Linear Models and Multivariate Analysis.

Data Relationships Kruskal-Wallis Algorithm

The Kruskal-Wallis option calculates the p-value under the assumption that there are no empty values in the data table.

Note: If there are empty values in the data table, the data table will first be reduced to the rows containing values for both the first and the second column.

The Kruskal-Wallis test can be seen as the nonparametric version of a one-way Anova. The test uses the ranks of the data rather than their actual values to calculate the test statistic. This test can be used as an alternative to the Anova, when the assumption of normality or equality of variance is not met.

For k groups of observations, all N observations are combined into one large sample, the result is sorted from smallest to largest values and ranks are assigned, assigning ties (when values occur more than once) the same rank.

Now, after regrouping the observations, the sum of the ranks are calculated in each group. The test statistic, H, is then:

DataRelationshipsKruskal-WallisAlgorithm.png

 

k = number of categories

N = number of cases in the sample

Ni = number of cases in the ith category

DataRelationshipsKruskal-WallisAlgorithmR.png = average of the ranks in the ith category

Tj = ties for the jth unique rank

m = number of unique ranks

A p-value can be calculated from the test statistic by referring the value of H to a table with the chi-square distribution with k-1 degrees of freedom. This can be used to test the hypothesis that all k population distributions are identical.

Example:

For the following data table, the different parameters used in the test are as follows:

Data table

Parameters

Category

Value

Rank

Ties

A

1

1

1

A

3

2.5

2

A

3

2.5

B

5

5.5

2

B

5

5.5

B

4

4

1

k = 2

N = 6

NA= 3

NB= 3

DataRelationshipsKruskal-WallisAlgorithmRAB.png

T1= 1

T2= 2

T3= 2

T4= 1

m = 4

H = 4.091

Reference:

Lehmann, E. L., Nonparametrics: Statistical Methods based on Ranks (1975), p. 204 – 210.

Data Relationships Chi-square Independence Test Algorithm

The Chi-square option calculates the p-value under the assumption that there are no empty values in the data table.

Note: If there are empty values in the data table, the data table will first be reduced to the rows containing values for both the first and the second column.

Let n be the total number of values and denote by I the number of unique values in the first column and by J the number of unique values in the second column. Also for i = 1, ..., I let ni be the number of occurrences of the ith unique value and for j = 1, ..., J, let nj be the number of occurrences of the jth unique value. If we now let nij denote the number of rows containing the ith unique value in the first column and the jth unique value in the second column, the Pearson's chi-square statistic is:

DataRelationshipsChi-squareIndependenceTestAlgorithm.png

with (I-1)(J-1) degrees of freedom.

The p-value is then calculated from the chi-square distribution with (I-1)(J-1) degrees of freedom.

Reference:

Rice, John A., Mathematical Statistics and Data Analysis, 2nd ed., p 489-491.

Requirements on Input Data for Data Relationships

Experimental design

In this tool, a one-way layout of Anovas has been employed. This means that the experimental design should be of the type where the outcome of a single continuous variable is compared between different groups. The tool cannot be used to analyze experiments where two or more variables vary together.

Tip: You can create a new column using the Concatenate function (or '&') of the Insert Calculated Column tool (Insert > Calculated Column...) if you want to analyze two or more variables together.

Distribution of data

The Anova and Linear regression comparisons assume the following:

  • The data is approximately normally distributed.

  • The variances of the separate groups, or the variances of the errors in the case of linear regression, are approximately equal.

If the data do not fulfill these conditions, the Anova and Linear Regression comparisons may produce unreliable results. In this case, it may be more valid to use a Kruskal-Wallis or Spearman R comparison instead.

Note: If more than one test is performed at the same time, then it is more likely that there will be at least one p-value less than 0.05 than in the case where only one test is performed. A guideline of when to reject the hypothesis is then "Reject the hypothesis if the p-value is less than 0.05 divided by the number of tests". This is called the Bonferroni method for multiple tests.

K-means Clustering

How to Perform a K-means Clustering


The K-means Clustering tool cannot be used unless you have created a suitable line chart to base the calculation on. For example, you cannot use multiple Y-axes scales or an X-axis which is both continuous and binned when performing a K-means clustering. See below for more information about how to set up the line chart.

  • To cluster data:

  1. Create a line chart visualization by clicking on the New Line Chart button on the toolbar.

    Comment: The tool uses the lines specified in a line chart to define the data for the calculation.

  2. Make sure that all values that should be included in the calculation are selected on the Y-axis.

  3. If more than one column is selected on the Y-axis, make sure that (Column Names) is selected on the X-axis.

    Comment: (Column Names) is an option that treats the names of the columns selected on the Y-axis as separate categories.

  4. Use Line By, Color By or Trellis By to split the lines according to at least one column, in order to create multiple lines.

    Comment: See examples on how to split lines on How to Use the Line Chart. If you want to create one line for each individual row, one of these options must be set to define a unique identifier for all rows. "(Row Number)" is a fictive column representing the row index of all rows and can be used for this purpose.

  5. Select Tools > K-means Clustering....

    Response: The K-means Clustering dialog is displayed.

  6. Make sure that the line chart you just created is selected under Line chart to work on.

  7. Select whether to Create new result column or Update existing result column.

    Comment: Update existing is only available when you have previously performed a K-means clustering during this analysis.

  8. Select a Distance measure to use in the calculation.

    Comment: For more information see Correlation or Euclidean distance.

  9. Specify the Max number of clusters that you wish to create.

    Comment: The actual number of clusters may be smaller than the specified maximum.

  10. Click OK.

    Response: A result column is created, specifying a cluster ID for each individual row (line).

    Comment: Note that the result column is based on a snapshot of the line chart from the moment of performing the calculation and it may become invalid when any additional filtering is applied.

Note: When opening an analysis file in which data has been saved linked to, any result columns generated by the clustering operation are dynamically re-evaluated, based on the new data.

Note: If the input line chart is trellised, the column or expression used to trellis by will be moved to the Line By setting upon running a K-means clustering. This is done in order to keep the original lines in the line chart after presenting the K-means result in trellis panels.

Tip: If you do not want to be able to overwrite the result column by consecutive clusterings, or when saving an analysis file with linked data, you can turn it into a static column by performing the following: Select Edit > Column Properties. Click on the result column to select it, and then click on the Freeze Column button in the lower part of the General tab.

See also:

Details on K-means Clustering

Details on K-means Clustering


K-means clustering is an algorithm for partitioning a data table into subsets (clusters), in such a way that the members of each cluster are relatively similar.

The K-means clustering in TIBCO Spotfire is based on a line chart visualization which has been set up either so that each line corresponds to one row in the root view of the data table, or, if the line chart is aggregated, so that there is a one to many mapping between lines and rows in the root view. The clustering is initialized using data centroid based search, using unit weights, and correlation or Euclidean distance as the distance measure. The clustering is always performed on filtered rows. If you wish all rows to be included in the clustering you need to reset all filters prior to clustering. The columns the clustering operation should be based on are specified in the line chart that is used as starting point.

If "break on empty" is not active, empty values will be replaced using row (line) interpolation, similar to what is shown in the visualization. If "break on empty" is active, any rows (lines) containing empty values shall be excluded from the clustering operation.

Note: If the input line chart is trellised, the column or expression used to trellis by will be moved to the Line By setting upon running a K-means clustering. This is done in order to keep the original lines in the line chart after presenting the K-means result in trellis panels.

  • To reach the K-means Clustering dialog:

  1. Make sure you have set up a line chart according to the steps in How to Perform a Line Similarity Comparison.

  2. Select Tools > K-means Clustering....

DetailsonK-meansClustering.png

Option

Description

Line chart to work on

Select the line chart on which you want to base the calculation from the drop-down list.

Result column options

 

   Create new

Use this option to create a new K-means clustering result column.

   Update existing

Use this option to update a previously added result column. Only available when at least one clustering result column has been added earlier.

Distance measure

Select the distance measure to base the similarity calculation on. See Distance measures for more information.

Max number of clusters

The maximum number of clusters that you want to calculate (some may turn out empty and will in that case not be displayed).

References:

Mirkin, B. (1996) Mathematical Classification and Clustering, Nonconvex Optimization and Its Applications Volume 11, Pardalos, P. and Horst, R., editors, Kluwer Academic Publishers, The Netherlands.

MacQueen, J. (1967). Some methods for classification and analysis of multivariate observations. In Le Cam, L. M. and Neyman, J., editors, Proceedings of the Fifth Berkeley Symposium on Mathematical Statistics and Probability. Volume I: Statistics, pages 281-297. University of California Press, Berkeley and Los Angeles, CA.

Hair, J.F.Jr., Anderson, R.E., Tatham, R.L., Black, W.C. (1995) Multivariate Data Analysis, Fourth Edition, Prentice Hall, Englewood Cliffs, New Jersey.

See also:

How to Perform a K-means Clustering

Line Similarity

How to Perform a Line Similarity Comparison


Note: The Line Similarity tool cannot be used unless you have created a suitable line chart to base the calculation on. For example, you cannot use multiple Y-axes scales or an X-axis which is both continuous and binned when performing a line similarity comparison. See below for more information about how to set up the line chart.

  • To perform a line similarity comparison:

  1. Create a line chart visualization by clicking on the New Line Chart button on the toolbar, .

    Comment: The tool uses the lines specified in a line chart to define the data for the calculation.

  2. Make sure that all values that should be included in the calculation are selected on the Y-axis.

  3. If more than one column is selected on the Y-axis, make sure that (Column Names) is selected on the X-axis.

    Comment: (Column Names) is an option that treats the names of the columns selected on the Y-axis as separate categories.

  4. Use Line By, Color By or Trellis By to split the lines according to at least one column, in order to create multiple lines.

    Comment: See examples on how to split lines on How to Use the Line Chart. If you want to create one line for each individual row, one of these options must be set to define a unique identifier for all rows.  "(Row Number)" is a fictive column representing the row index of all rows and can be used for this purpose.

  5. If desired, mark one or more lines to use as the master line against which the search will be performed.

  6. Select Tools > Line Similarity....

    Comment: You can also right-click in the line chart and select Line Similarity... from the pop-up menu.

    Response: The Line Similarity dialog is displayed.

  7. Make sure that the line chart you just created is selected under Line chart to work on.

  8. Select whether to Create new result columns or Update existing result columns.

    Comment: Update existing is only available when you have previously performed a line similarity comparison during this analysis.

  9. Select a Distance measure to use in the calculation.

    Comment: For more information see Correlation or Euclidean distance.

  10. Select whether to use a master line Based on marked lines or on a Custom shape.

    Comment: Marked lines create a master line which is the average of all marked lines. With the custom alternative, you can select a master line from a number of predefined line shapes.

    Comment: If the currently marked lines contain empty values they cannot be used to create a master line, and the custom alternative will automatically be selected.

  11. Click OK.

    Response: Two new columns are added to the data table (and two new filters representing the columns are shown in the filters panel).

    Comment: Note that the result columns are based on a snapshot of the line chart from the moment of performing the calculation and they may become invalid when any additional filtering is applied.

Tip: If you do not want to be able to overwrite the result columns by consequent calculations, or when saving an analysis file with linked data, you can turn them into static columns by performing the following: Select Edit > Column Properties. Click on a result column to select it, and then click on the Freeze Column button in the lower part of the General tab.

See also:

Details on Line Similarity

Details on Line Similarity


The Line Similarity tool is used to compare the lines in a line chart to a selected master line. As a result, two new columns are generated. The first is a similarity column, where the similarity to the master line is presented for each individual row (line). The second is a rank column, where the line most similar to the master line receives the rank 1. Correlation or Euclidean distance is used as the distance measure.

If "break on empty" is not active, empty values will be replaced using row (line) interpolation, similar to what is shown in the visualization. If "break on empty" is active, any rows (lines) containing empty values shall be excluded from the line similarity calculation.

  • To reach the Line Similarity dialog:

  1. Make sure you have set up a line chart according to the steps in How to Perform a Line Similarity Comparison.

  2. Select Tools > Line Similarity....

DetailsonLineSimilarity.png

Option

Description

Line chart to work on

Specifies which line chart will be used in the calculation (if there are more than one suitable line chart in the document).

Result column options

 

   Create new

Use this option to create new line similarity result columns. Create a new (calculation) is marked by default. Specify a name for the group, to which the two created columns belong.  

   Update existing

Use this option to update previously added result columns. Only available when at least one line similarity has been calculated earlier.

Distance measure

Select the distance measure to base the similarity calculation on. See Distance measures for more information.

Master line

 

   Based on marked lines

Use this option to search for lines as similar to the marked lines as possible.

   Custom

Use this option to search for lines as similar to the shape selected in the drop-down list as possible.

See also:

 How to Perform a Line Similarity Comparison

Hierarchical Clustering

What is the Hierarchical Clustering Tool?


The Hierarchical Clustering tool groups rows and/or columns in a data table and arranges them in a heat map visualization with a dendrogram (a tree graph) based on the distance or similarity between them. When using the hierarchical clustering tool, the input is a data table, and the result is a heat map with dendrograms. You can also initiate hierarchical clustering on an existing heat map from the Dendrograms page of the Heat Map Properties. See How to Use the Heat Map to learn more.

  • To perform a clustering with the Hierarchical Clustering tool:

  1. Select Tools > Hierarchical Clustering....

    Response: The Hierarchical Clustering dialog is displayed.

  2. If the analysis contains more than one data table, select a Data table to perform the clustering calculation on.

  3. Click Select Columns....

    Response: The Select Columns dialog is displayed.

  4. Select the columns you want to include in the clustering, and then click OK to close the dialog.

  5. Select the Cluster rows check box if you want to create a row dendrogram.

  6. Click the Settings... button to open the Edit Clustering Settings dialog.

  7. Select a Clustering method.

    Comment: For more information on clustering methods, see Clustering Methods Overview.

  8. Select a Distance measure.

    Comment: For more information on distance measures, see Distance Measures Overview. Distances exceeding 3.40282e+038 cannot be represented.

  9. Select Ordering weight to use in the clustering calculation.

    Comment: For more information see Ordering Weight.

  10. Select an Empty value replacement Method from the drop-down list.

    Comment: The available replacement methods are described in Details on Edit Clustering Settings.

  11. Select a Normalization Method to use in the clustering calculation.

    Comment: For more information, see Normalizing Columns.

  12. Click OK.

  13. Select the Cluster columns check box if you want to create a column dendrogram.

  14. Go through steps 6 to 12 to define settings for the column dendrogram.

  15. Click OK.

    Response: The hierarchical clustering calculation is performed, and a heat map visualization with the specified dendrograms is created. A cluster column is also added to the data table and made available in the filters panel.

    Comment: See Dendrograms and Clustering to learn more about dendrograms and cluster columns.

See also:

Overview of Hierarchical Clustering Theory

Details on Hierarchical Clustering

Dendrograms and Clustering

What is a Heat Map?

Details on Hierarchical Clustering


  • To reach the Hierarchical Clustering dialog:

  1. Open the Tools menu.

  2. Select Hierarchical Clustering....

DetailsonHierarchicalClustering.png

Option

Description

Data table

Lists the data tables currently available in the analysis. Select the data table for which you want to perform a clustering calculation.

Columns

 

   Select Columns...

Click to open the Select Columns dialog, where you can select which columns to include in the clustering calculation.

Cluster rows

Select this check box to perform clustering on the rows in the data table.

   Settings...

Opens the Edit Clustering Settings dialog where you can define which clustering method, distance measure, and ordering weight to use for the clustering calculation. You can also define a normalization method and a method to use for replacing empty values.

To learn more about hierarchical clustering, see Overview of Hierarchical Clustering Theory. To learn more about normalization, see Normalizing Columns.

Cluster columns

Select this check box to perform clustering on the columns in the data table.

   Settings...

Opens the Edit Clustering Settings dialog where you can define which clustering method, distance measure, and ordering weight to use for the clustering calculation. You can also define a normalization method and a method to use for replacing empty values.

To learn more about hierarchical clustering, see Overview of Hierarchical Clustering Theory. To learn more about normalization, see Normalizing Columns.

See also:

Overview of Hierarchical Clustering Theory

What is the Hierarchical Clustering Tool?

Theory and Practice

Overview of Hierarchical Clustering Theory

Hierarchical clustering arranges items in a hierarchy with a treelike structure based on the distance or similarity between them. The graphical representation of the resulting hierarchy is a tree-structured graph called a dendrogram. In Spotfire, hierarchical clustering and dendrograms are strongly connected to heat map visualizations. You can cluster both rows and columns in the heat map. Row dendrograms show the distance or similarity between rows, and which nodes each row belongs to as a result of clustering. Column dendrograms show the distance or similarity between the variables (the selected cell value columns). The example below shows a heat map with a row dendrogram.

OverviewofHierarchicalClusteringTheory.png

You can perform hierarchical clustering in two different ways: by using the Hierarchical Clustering tool, or by performing hierarchical clustering on an existing heat map visualization. If you use the Hierarchical clustering tool, a heat map with a dendrogram will be created. To learn more about heat maps and dendrograms, see What is a Heat Map? and Dendrograms and Clustering.

Algorithm

The algorithm used for hierarchical clustering in Spotfire is a hierarchical agglomerative method. For row clustering, the cluster analysis begins with each row placed in a separate cluster. Then the distance between all possible combinations of two rows is calculated using a selected distance measure. The two most similar clusters are then grouped together and form a new cluster. In subsequent steps, the distance between the new cluster and all remaining clusters is recalculated using a selected clustering method. The number of clusters is thereby reduced by one in each iteration step. Eventually, all rows are grouped into one large cluster. The order of the rows in a dendrogram are defined by the selected ordering weight. The cluster analysis works the same way for column clustering.

Note: Only numeric columns will be included when clustering.

See also:

What is a Heat Map?

Dendrograms and Clustering

What is the Hierarchical Clustering Tool?

Distance Measures
Distance Measures Overview

The following measures can be used to calculate the distance or similarity between rows or columns:

  • Correlation

  • Cosine Correlation

  • Tanimoto Coefficient

  • Euclidean Distance

  • City Block Distance

  • Square Euclidean Distance

  • Half Square Euclidean Distance

The term dimension is used in all distance measures. The concept of dimension is simple if we are describing the physical position of a point in three dimensional space when the positions on the x, y and z axes refer to the different dimensions of the point. However, the data in a dimension can be of any type. If, for example, you describe a group of people by their height, their age and their nationality, then this is also a three dimensional system. For a row (or column), the number of dimensions is equal to the number of variables in the row (or column).

Note: The result from a cluster calculation will be presented either as the similarity between the clustered rows or columns, or as the distance between them. Euclidean distance, City block distance, Square Euclidean distance, and Half square Euclidean distance will present the distance between the rows or columns. The results from Correlation, Cosine correlation, and Tanimoto coefficient, on the other hand, are presented as similarity between the rows or columns.

Note: When used in clustering, the similarity measures Correlation, Cosine correlation, and Tanimoto coefficient may be transformed so that they are always greater than or equal to zero (using 1 – similarity value).

See also:

Overview of Hierarchical Clustering Theory

Correlation

The correlation between two points, a and b, with k dimensions is calculated as:

Correlation1.png

This correlation is called Pearson Product Momentum Correlation, simply referred to as Pearson's correlation or Pearson's r. It ranges from +1 to -1 where +1 is the highest correlation. Complete opposite points have correlation -1.

 

Correlation2.png

a and b are identical, which means they have maximum correlation.

a and b are perfectly mirrored, which means they have the maximum negative correlation.

See also:

Distance Measures Overview

Cosine Correlation

The Cosine correlation between two points, a and b, with k dimensions is calculated as:

CosineCorrelation1.png

The cosine correlation ranges from +1 to -1 where +1 is the highest correlation. Complete opposite points have correlation -1.

Comparison between Cosine correlation and Correlation

The difference between Cosine correlation and Correlation is that the average value is subtracted in Correlation. In the example below, the Cosine correlation will be +1 between any combination of points ab, and c, but it will be slightly less than that between point d and any of the other points (+0.974). However, the regular Correlation will be +1 between any of the points, including point d.

CosineCorrelation2.png

See also:

Distance Measures Overview

Tanimoto Coefficient

The Tanimoto coefficient between two points, a and b, with k dimensions is calculated as:

TanimotoCoefficient.png

The Tanimoto similarity is only applicable for a binary variable, and for binary variables the Tanimoto coefficient ranges from 0 to +1 (where +1 is the highest similarity).

See also:

Distance Measures Overview

Euclidean Distance

The Euclidean distance between two points, a and b, with k dimensions is calculated as:

EuclideanDistance.png

The Euclidean distance is always greater than or equal to zero. The measurement would be zero for identical points and high for points that show little similarity.

The figure below shows an example of two points called a and b. Each point is described by five values. The dotted lines in the figure are the distances (a1-b1), (a2-b2), (a3-b3), (a4-b4) and (a5-b5) which are entered in the equation above.

See also:

Distance Measures Overview

City Block Distance

The City block distance between two points, a and b, with k dimensions is calculated as:

CityBlockDistance.png

The City block distance is always greater than or equal to zero. The measurement would be zero for identical points and high for points that show little similarity.

The figure below shows an example of two points called a and b. Each point is described by five values. The dotted lines in the figure are the distances (a1-b1), (a2-b2), (a3-b3), (a4-b4) and (a5-b5) which are entered in the equation above.

 

In most cases, this distance measure yields results similar to the Euclidean distance. Note, however, that with City block distance, the effect of a large difference in a single dimension is dampened (since the distances are not squared).

The name City block distance (also referred to as Manhattan distance) is explained if you consider two points in the xy-plane. The shortest distance between the two points is along the hypotenuse, which is the Euclidean distance. The City block distance is instead calculated as the distance in x plus the distance in y, which is similar to the way you move in a city (like Manhattan) where you have to move around the buildings instead of going straight through.

See also:

Distance Measures Overview

Square Euclidean Distance and Half Square Euclidean Distance

The Square Euclidean distance between two points, a and b, with k dimensions is calculated as

SquareEuclideanDistanceandHalfSquareEuclideanDistance.png

The Half Square Euclidean distance between two points, a and b, with k dimensions is calculated as

 

The half square Euclidean distance is always greater than or equal to zero. The measurement would be zero for identical points and high for points that show little similarity.

The figure below shows an example of two points called a and b. Each point is described by five values. The dotted lines in the figure are the distances (a1-b1), (a2-b2), (a3-b3), (a4-b4) and (a5-b5) which are entered in the equation above.

See also:

Distance Measures Overview

Clustering Methods
Clustering Methods Overview

Hierarchical clustering starts by calculating the distance between all possible combinations of two rows or columns using a selected distance measure. These calculated distances are then used to derive the distance between all clusters that are formed from the rows or columns during the clustering. You can select one of the following clustering methods:

  • UPGMA

  • WPGMA

  • Single Linkage

  • Complete Linkage

  • Ward's Method

See also:

Overview of Hierarchical Clustering Theory

UPGMA

UPGMA stands for Unweighted Pair-Group Method with Arithmetic mean.

Assume that there are three clusters called C1, C2 and C3 including n1, n2 and n3 number of rows or columns. Clusters C2 and C3 are aggregated to form a new single cluster called C4.

The distance between cluster C1 and the new cluster C4 is calculated as:

UPGMA.png

 

See also:

Clustering Methods Overview

WPGMA

WPGMA stands for Weighted Pair-Group Method with Arithmetic mean.

Assume that there are three clusters called C1, C2 and C3 including n1, n2 and n3 number of rows. Clusters C2 and C3 are aggregated to form a new single cluster called C4.

The distance between cluster C1 and the new cluster C4 is calculated as:

WPGMA.png

See also:

Clustering Methods Overview

Single Linkage

This method is based on minimum distance. To calculate the distance between two clusters, each possible combination of two rows (or columns) between the two clusters is compared. The distance between the clusters is the same as the distance between the two rows (or columns) in the clusters that are least distant.

Assume that there are three clusters called C1, C2 and C3. Clusters C2 and C3 are aggregated to form a new single cluster called C4. The distance between cluster C1 and the new cluster C4 is calculated as:

SingleLinkage.png

See also:

Clustering Methods Overview

Complete Linkage

This method is based on maximum distance and can be thought of as the opposite of Single linkage. To calculate the distance between two clusters, each possible combination of two rows (or columns) between the two clusters is compared. The distance between the two clusters is the same as the distance between the two rows (or columns) in the clusters that are most distant.

Assume that there are three clusters called C1, C2 and C3. Clusters C2 and C3 are aggregated to form a new single cluster called C4. The distance between cluster C1 and the new cluster C4 is calculated as:

CompleteLinkage.png

See also:

Clustering Methods Overview

Ward's Method

Ward's method means calculating the incremental sum of squares. Half square Euclidean distance is the only distance measure that can be used with this clustering method. Therefore the distance measure is automatically set to Half square Euclidean distance when Ward's method is selected.

Assume that there are three clusters called C1, C2 and C3 including n1, n2 and n3 number of rows (or columns). Clusters C2 and C3 are aggregated to form a new single cluster called C4.

The distance between cluster C1 and the new cluster C4 in the example above is calculated as:

Ward'sMethod.png

See also:

Clustering Methods Overview

Ordering Weight

The ordering weight controls in what vertical order the rows are displayed in the row dendrogram. For column dendrograms it controls the horizontal order of the columns. The two subclusters within a cluster (there are always exactly two subclusters) are weighted and the cluster with the lower weight is placed above (to the left of) the other cluster. The weight can be any one of the following:

  • Input average rank of the rows (or columns). This is the order of the rows (or columns) during import to Spotfire.

  • Average value of the rows (or columns). For example, a row a with 5 dimensions would have the average (a1+a2+a3+a4+a5 )/5. The average for a row a with k dimensions is calculated as

OrderingWeight1.png

Calculating the weight of a cluster

To calculate the weight w3 of a new cluster C3 formed from two subclusters C1 and C2 with a weight of w1and w2, and each containing n1 and n2 rows, you use the following expression:

OrderingWeight2.png

See also:

Overview of Hierarchical Clustering Theory

Hierarchical Clustering References

Hierarchical clustering

Mirkin, B. (1996) Mathematical Classification and Clustering, Nonconvex Optimization and Its Applications Volume 11, Pardalos, P. and Horst, R., editors, Kluwer Academic Publishers, The Netherlands.

Sneath, P., Sokal, R. R. (1973) Numerical taxonomy, Second Edition, W. H. Freeman, San Francisco.

General information about clustering

Hair, J.F.Jr., Anderson, R.E., Tatham, R.L., Black, W.C. (1995) Multivariate Data Analysis, Fourth Edition, Prentice Hall, Englewood Cliffs, New Jersey.

See also:

Overview of Hierarchical Clustering Theory

Predictive Modeling

What is Predictive Modeling?


TIBCO Spotfire provides you with the tools to incorporate predictive models into your analysis using either regression modeling or classification modeling.

  • Regression modeling is useful for making numeric predictions, such as profit and expenses.

  • Classification modeling is useful for making predictions for typically two nodes or classes, such as whether a business transaction is fraudulent or legitimate.

The three tasks of predictive modeling include:

  • Fitting the model.

  • Evaluating the model.

  • Predicting from the model.

To fit the model, in the Regression Modeling or Classification Modeling dialog, select the model options and click OK. TIBCO Enterprise Runtime for R for Spotfire creates the model and returns it to the analysis. Each model creates a number of new data tables that you can use for further analysis.

A model page is created (see The Model Page) and the model is added to the Analytic Models panel. You can include more than one model in your analysis, and then you can iterate and evaluate through all models with new data.

After you have completed your evaluation, optionally you can predict from the model. When you include a model in the analysis, you can use the model to insert predicted columns into your data table and share the result with others.

See also:

Linear Regression Method

Regression Tree Method

Logistic Regression Method

Classification Tree Method

Regression Modeling

Linear Regression Method

Linear regression models the numeric response column as a weighted sum of the predictor columns. The weights, also known as the regression coefficients, are selected by the method of least squares, which minimizes the sum of the squared differences between the observed response and the predictions based on the weighted sum.

Any predictor column with character data is expanded into a set of indicator columns, one column for each unique value in the character column. The indicator column for a character value is 1 if the corresponding entry in the original column contains that character value, otherwise it is zero. Character data columns used as predictors should each have small numbers of unique values relative to the total number of rows in the data set.

See also:

Regression Tree Method

Logistic Regression Method

Classification Tree Method

Details on Regression Modeling – General

Regression Tree Method

Regression trees are a nonparametric regression method that creates a binary tree by recursively splitting the data on the predictor values.

The splits are selected so that the two child nodes have smaller variability around their average value than the parent node. Various options are used to control how deep the tree is grown. Regression predictions for an observation are based on the mean value of all the responses in the terminal node.

The Predictor columns can be either numeric or character (provided there are not more then 31 unique character values in any one character column). There is no need for making transformations of the response or predictor columns; the same tree is grown for any monotone transformations of the data.

See also:

Linear Regression Method

Logistic Regression Method

Classification Tree Method

Details on Regression Modeling – General

Details on Regression Modeling – General

This tool allows you to create regression models using the TIBCO Enterprise Runtime for R engine, without the need of writing any scripts yourself. A model page will be created (see The Model Page) and the model will be added to the Analytic Models panel.

  • To reach the Regression Modeling dialog:

  1. Open the Tools menu.

  2. Select Regression Modeling....

DetailsonRegressionModeling–Options.png

Option

Description

Name

The name of the model as you want it to be referenced in the Analytic Models panel.

Comment

A field for optional comments on the model. It is recommended that the comment field describes what type of data you should use this model for.

Model method

Specifies the prediction model method. Choose from Linear Regression and Regression Tree.

QuestionMark.png

Displays more information about the currently selected model method.

Data table

Specifies the data table on which the model will be calculated.

Response column

Specifies what you are trying to predict. The selected response column is automatically added to the Formula expression field.

All numeric columns in the selected data table are available for selection.

Predictor columns

Lists all columns in the selected data table that can be used as predictor columns. Select all variables that you think might affect the values you are trying to predict (the selected response column) and click Add after specifying a suitable transformation.

[Drop-down list]

Select a transformation from this list to apply it to the selected predictor columns before clicking Add and sending the predictor columns to the Formula expression field.

The  selected method should reflect the expected relationship between the response column and the selected predictor column. For example, if the response column is expected to linearly depend on another column, then this column should be added as a predictor column using the Linear: x method. If the relationship between the two columns instead is logarithmic, then you might add the predictor column using the Log: log(x) method.

Add

Adds the selected predictor columns to the Formula expression field using the transformation selected in the drop-down list.

Formula expression

Displays the relationship set up between the response column and the selected predictor columns. You can also edit the formula expression field manually if you want to create more advanced expressions.

Clear

Clears the Formula expression field.

See also:

Details on Regression Modeling – Options

Details on Regression Modeling – Options

This tool allows you to create regression models using the TIBCO Enterprise Runtime for R engine, without the need of writing any scripts yourself. A model page will be created (see The Model Page) and the model will be added to the Analytic Models panel.

  • To reach the Regression Modeling dialog:

  1. Open the Tools menu.

  2. Select Regression Modeling....

DetailsonRegressionModeling–Options.png

Top part of dialog

Option

Description

Name

The name of the model as you want it to be referenced in the Analytic Models panel.

Comment

A field for optional comments on the model.

Model method

Specifies the prediction model method. Choose from Linear Regression and Regression Tree.

QuestionMark.png

Displays more information about the currently selected model method.

Data table

Specifies the data table on which the model will be calculated.

Options Tab – Linear Regression

Option

Description

Use weights column

Select this option if you want to specify a weight column. A weight column is used to increase or decrease the importance of the values on specific rows by multiplication with the number in the weight column.

Options Tab – Regression Tree

Option

Description

Use weights column

Select this option if you want to specify a weight column. A weight column is used to increase or decrease the importance of the values on specific rows by multiplication with the number in the weight column.

Minimum split

Specifies the minimum number of observations in a node to consider splitting.

Complexity parameter

The complexity parameter is used for controlling the size of the regression tree and for selecting an optimal tree size. The building of the tree stops if the addition of another variable to the regression tree from the current node has a higher cost than the value of the complexity parameter. The building of the tree only continues if the overall lack of fit is decreased by a factor of the complexity parameter.

If the complexity parameter is set to zero then a tree will be built to its maximum depth, which may be very large.

Maximum depth

Specifies the maximum depth of any node in the tree.

Cross validation group size

Specifies the cross validation group size.

See also:

Details on Regression Modeling – General

Classification Modeling

Logistic Regression Method

Logistic regression is a classification method used when the Response column is categorical with only two possible values. The probability of the possible outcomes is modeled with a logistic transformation as a weighted sum of the Predictor columns. The weights or regression coefficients are selected to maximize the likelihood of the observed data.

Any Predictor column with character data is expanded into a set of indicator columns: one column for each unique value in the character column. The indicator column for a character value is one if the corresponding entry in the original column contains that character value; otherwise, it is zero.  Character data columns used as predictors should each have small numbers of unique values relative to the total number of rows in the data set.

See also:

Linear Regression Method

Regression Tree Method

Classification Tree Method

Details on Classification Modeling – General

Classification Tree Method

Classification trees are a nonparametric classification method that creates a binary tree by recursively splitting the data on the predictor values. The splits are selected so that the two child nodes are purer in terms of the levels of the Response column than the parent node. Various options are used to control how deep the tree is grown. Class predictions for an observation are based on the majority class in the terminal node for the observation.

Classification trees can handle response variables with more than two classes. The Predictor columns can be either numeric or character (provided there are not more then 31 unique character values in any one character column). There is no need to make transformations of the Predictor columns; the same tree is grown for any monotone transformations of the data.

See also:

Linear Regression Method

Regression Tree Method

Logistic Regression Method

Details on Classification Modeling – General

Details on Classification Modeling – General

This tool allows you to create classification models using the TIBCO Enterprise Runtime for R engine, without the need of writing any scripts yourself. A model page will be created (see The Model Page) and the model will be added to the Analytic Models panel.

  • To reach the Classification Modeling dialog:

  1. Open the Tools menu.

  2. Select Regression Modeling....

DetailsonClassificationModeling–General.png

Option

Description

Name

The name of the model as you want it to be referenced in the Analytic Models panel.

Comment

A field for optional comments on the model.

Model method

Specifies the prediction model method. Choose from Logistic Regression and Classification Tree.

QuestionMark.png

Displays more information about the currently selected model method.

Data table

Specifies the data table on which the model will be calculated.

Response column

Specifies what you are trying to predict. It should generally be a column with not too many values. The selected response column is automatically added to the Formula expression field.

Response level

[Only available when the Model method is set to Logistic Regression.]

The Response level is the value of the response which you want to model all of the other values against. Typically, there should only be a couple of possible response values available (for example., "true" and "false", or "absent" and "present").

If there are more than two possible response levels available, they will automatically be grouped into two groups, with your selected choice in the first group and the other options in the second group. For example, if the response can be five different outcomes and if you have selected "Outcome 1" as response level, then all other possible outcomes will be grouped together into a "Not_Outcome 1" group.

Predictor columns

Lists all columns in the selected data table that can be used as predictor columns. Select all variables that you think might affect the values you are trying to predict (the selected response column) and click Add after specifying a suitable transformation.

[Drop-down list]

Select a transformation from this list to apply it to the selected predictor columns before clicking Add and sending the predictor columns to the Formula expression field.

The  selected method should reflect the expected relationship between the response column and the selected predictor column. For example, if the response column is expected to linearly depend on another column, then this column should be added as a predictor column using the Linear: x method. If the relationship between the two columns instead is logarithmic, then you might add the predictor column using the Log: log(x) method.

Add

Adds the selected predictor columns to the Formula expression field using the transformation selected in the drop-down list.

Formula expression

Displays the relationship set up between the response column and the selected predictor columns. You can also edit the formula expression field manually if you want to create more advanced expressions.

Clear

Clears the Formula expression field.

See also:

Details on Classification Modeling – Options

Details on Classification Modeling – Options

This tool allows you to create classification models using the TIBCO Enterprise Runtime for R engine, without the need of writing any scripts yourself. A model page will be created (see The Model Page) and the model will be added to the Analytic Models panel.

  • To reach the Classification Modeling dialog:

  1. Open the Tools menu.

  2. Select Classification Modeling....

DetailsonClassificationModeling–Options.png

Top of Dialog

Option

Description

Name

The name of the model as you want it to be referenced in the Analytic Models panel.

Comment

A field for optional comments on the model.

Model method

Specifies the prediction model method. Choose from Logistic Regression and Classification Tree.

QuestionMark.png

Displays more information about the currently selected model method.

Data table

Specifies the data table on which the model will be calculated.

Options Tab – Logistic Regression

The options available on this tab depend on which model method you have selected.

Option

Description

Use weights column

Select this option if you want to specify a weight column. A weight column is used to increase or decrease the importance of the values on specific rows by multiplication with the number in the weight column.

Maximum iterations

Specifies the maximum number of iterations to use in the regression calculation.

Convergence tolerance

Specifies the convergence tolerance of the model.

Options Tab – Classification Tree

Option

Description

Use weights column

Select this option if you want to specify a weight column. A weight column is used to increase or decrease the importance of the values on specific rows by multiplication with the number in the weight column.

Minimum split

Specifies the minimum number of observations in a node to consider splitting.

Complexity parameter

The complexity parameter is used for controlling the size of the classification tree and for selecting an optimal tree size. The building of the tree stops if the addition of another variable to the classification tree from the current node has a higher cost than the value of the complexity parameter. The building of the tree only continues if the overall lack of fit is decreased by a factor of the complexity parameter.

If the complexity parameter is set to zero then a tree will be built to its maximum depth, which may be very large.

Maximum depth

Specifies the maximum depth of any node in the tree.

Cross validation group size

Specifies the cross validation group size.

See also:

Details on Classification Modeling – General

How To Use the Model Page

The Model Page

Each time a new model is created, a new page, the model page, is added to the analysis. It consists of four different sections:

  1. The Model Summary.

  2. The Table of Coefficient.

  3. The Available Diagnostic Visualizations.

  4. The visualization area where the diagnostic visualizations can be displayed.

TheModelPage.png

The Analytic Models panel can be shown or hidden on the model page just like any other popovers or panels.

See also:

What is Predictive Modeling?

Using a Model Summary

A model summary is automatically created when running a regression modeling or a classification modeling. The model summary displays the name of the model, the model type, and the model formula.

For parametric models (Linear Regression and Logistic Regression), additional summary statistics, appropriate for the particular model type are also shown. These statistics can give an indication of how well the model fits the data and can also be used to compare one model with another model of the same type.

For tree models, a text description of the tree structure is displayed, followed by table showing the model improvement at each split. Finally, a summary of each individual split, starting at the root node, is shown.

 

Icon

Description

RefitsModel.png

[Only visible if there has been any changes to the underlying data table, e.g., through filtering.]

Refits the model.

OpensRegressionModel.png

Opens the Regression Modeling or the Classification Modeling dialog where you can make changes to your current model.

OpensEvaulateModel.png

Opens the Evaluate Model dialog where you can test the model against another set of data in order to see how well the model fits other data.

OpensInsertPredictedColumns.png

Opens the Insert Predicted Columns dialog where you can use the model to insert predicted columns into a data table.

OpensDuplicateModel.png

Opens the Duplicate Model dialog where you can type a new name and create a duplicate of the model. This allows you to edit a copy rather than the original model if you need to make adjustments to a model.

Summary Statistics

Regression Model

Description

Residual standard error

The residual standard error is a measure of the error variability after the effects of the predictors used in the model are accounted for. It is in the units of the response variable.

R-squared (or R2) and Adjusted R-squared

R-squared measures the fraction of the variability in the data that is explained by the model. It is a number between 0 and 1 with 1 being a perfect fit model (all observations are predicted exactly). The Adjusted R-squared is like R-squared with an adjustment to account for the number of predictors in the model. Adding predictors, even nonsense ones, will increase R-squared.

F-statistic

The F-statistic is a statistics test of the overall significance of the model. A statistically significant model will have a small p-value (typically less than 0.05). The DF or degrees-of-freedom are associated with the F-statistic and are used to compute the p-value.

 

Classification Model

Description

Null deviance and Residual deviance

The deviance is another measure of the variability in the model. The null deviance is for the model with no predictors and the residual deviance is the measure after the effects of the predictors used in the model are accounted for.

AIC

AIC is the Akaike Information Criterion, a measure of the goodness of fit of the model. Like the adjusted R-squared for regression model, it takes into account the number of predictors included in the model. For the same response variable and different combinations of predictor variables, the model with the smallest AIC would be preferred.

CP nsplit rel_error xerror xstd

This table shows a collection of optimal pruned trees based on the value of the complexity parameter. For each tree, the complexity value (CP), the number of splits, (the number of nodes in the tree is 1 + nsplit), the relative error (rel_error) as well as the cross-validated error (xerror) and the standard error of the cross-validated error (xstd). The error columns are scaled so that the first node has an error of 1. The complexity value is also scaled.

See also:

Using a Table of Coefficients

Available Diagnostic Visualizations

Using a Table of Coefficients

The table of coefficients provides the model coefficients for the parametric models (linear and logistic regression). In addition to the estimates of the coefficients, the table includes a measure of the variability or error of each estimate and a test statistic (t.value or z.value) of the null hypothesis that the coefficients is zero (in other words, not needed in the model). A p-value for the statistical test is also provided. A small p-value (typically less that 0.05) indicates that the null hypothesis can be rejected--that is, that the coefficient is significant or important in the model.

Note that the test for each coefficient is based on the model with all the other coefficients included in the model.

The absolute value of the test statistic can provide a measure of variable importance for that term in the model. The larger the absolute value, the greater the importance.

The table of coefficients is added as a separate data table in Spotfire and can be used for further analysis.

See also:

Using a Model Summary

Available Diagnostic Visualizations

Available Diagnostic Visualizations

This section lists the available diagnostic plots for the model. They can be an aid to help determining the validity of a predictive model. Different model methods display different lists of diagnostic plots. Click on an option to display the visualization in the model page.

Residuals vs. Fitted

The residuals vs. fitted visualization is a scatter plot showing the residuals on the Y-axis and the fitted values on the X-axis. You can compare it to doing a linear fit and then flipping the fitted line so that it becomes horizontal. Values that have the residual 0 are those that would end up directly on the estimated regression line. The residuals vs fit plot is commonly used to detect non-linearity, unequal error variances and outliers.

Shape (exaggerated)

Conclusion

AvailableDiagnosticVisualizations1.png

When a linear regression model is suitable for a data set, then the residuals are more or less randomly distributed around the 0 line.

AvailableDiagnosticVisualizations6.png

When residuals form a pattern in the visualization, then the current model might be less suitable for the data.

 

 

Normal Quantile-Quantile

The normal quantile-quantile visualization calculates the normal quantiles of all values in a column. The values (Y-axis) are then plotted against the normal quantiles (X-axis).

Things to look for:

Shape (exaggerated)

Conclusion

AvailableDiagnosticVisualizations7.png

Approximately normal distribution.

AvailableDiagnosticVisualizations8.png

Less variance than expected. While this distribution differs from the normal, it seldom presents any problems in statistical calculations.

AvailableDiagnosticVisualizations9.png

More variance than you would expect in a normal distribution.

AvailableDiagnosticVisualizations10.png

Left skew in the distribution.

AvailableDiagnosticVisualizations11.png

Right skew in the distribution.

AvailableDiagnosticVisualizations12.png

Outlier. Outliers can disturb statistical analyses and should always be thoroughly investigated. If the outliers are due to known errors, they should be removed from the data before a more detailed analysis is performed.

 

 

Note: Plateaus will occur in the plot if there are only a few discrete values that the variable may take on. However, clustering in the plot may also be due to a second variable that has not been considered in the analysis.

Scale – Location

The scale – location plot is similar to the residuals vs fit plot, but instead of linear residuals it uses the square root of the residuals. It is used to reveal trends in the magnitudes of residuals. For a good model, the values should be more or less randomly distributed.

AvailableDiagnosticVisualizations6.png

Cook's Distance

Cook's distance is a statistic which tries to identify those values which have more influence than others on the estimated coefficients. High peaks in the bar chart might represent values that should be investigated further, since they have a larger effect on the coefficients.

AvailableDiagnosticVisualizations2.png

Response vs. Fitted or Predicted

The Response vs. Fitted or Response vs. Predicted visualization is a scatter plot of the response variable versus the fitted values for the model or the predicted values computed from new data using a previously computed model. The ideal shape for this plot is all points on a line with an intercept of 0 and a slope of 1 (about a 45 degrees angle). This would indicate that the response values and values computed from the model match up perfectly. In reality, the points will be in a diagonal band around the (0,1) line.  Points that deviate greatly from this band can indicate outliers or deficiencies in the model.

AvailableDiagnosticVisualizations3.png

Generally, the Residuals vs. Fitted or Predicted scatter plot is a better visualization to diagnose model deficiencies, since the deviations are centered around the horizontal line, y=0, instead of around the (0,1) line.

Predicted Probability Histograms

The Predicted Probability is a histogram of the predicted probabilities for a particular level of the response variable. For a two level response, you would like to have all the values in one histogram close to one and, in the other histogram, all the values should be close to zero.

AvailableDiagnosticVisualizations4.png

ROC Curve

An ROC, or receiver operating characteristic curve, shows the performance of the classifier as the threshold for class prediction is varied. It is a plot of the sensitivity, or true positive rate of the classifier, versus one minus the specificity, or false positive rate. The true positive rate is the number of the predicted positives out of true positives and the true negative rate is the number of the predicted negatives out of the number of false positives. The predicted positives and negatives varies as the threshold for class prediction varies.

AvailableDiagnosticVisualizations5.png

For example, with classes A and B, if the threshold is set very low for class A (close to zero) then all the tree class A observations will be classified as A (sensitivity is one). However, many class B observations will also be incorrectly classified as A leading to a large false positive rate. The ideal ROC curve starts at (0,1) goes up to (0,1) and then over to (1,1).

Randomly assigning predicted classes leads to an ROC curve that is a line with a slope of 1 from (0,0) to (1,1).

See also:

Using a Model Summary

Using a Table of Coefficients

How To Use The Evaluation Page

The Evaluation Page

Each time a model is evaluated, a new page, the evaluation page, is added to the analysis. It consists of three different sections:

  1. The Evaluation Summary.

  2. The Confusion Matrix (only available for the classification modeling methods).

  3. The Available Diagnostic Visualizations.

  4. The visualization area where the diagnostic visualizations can be displayed.

TheEvaluationPage.png

The Analytic Models panel can be shown or hidden on the model page just like any other popovers or panels.

Note: The evaluation page uses a number of temporary data tables that are created by the model evaluation process. Deleting the evaluation page will prompt you to delete those temporary data tables as well, and it is recommended that you do so. Otherwise, you may experience unexpected behavior in the analysis.

See also:

What is Predictive Modeling?

Using an Evaluation Summary

An evaluation summary is automatically created when you are evaluating a regression model or a classification model.

The evaluation summary displays the name of the model, including the model type, the data table used in the model evaluation, and the model formula.

Additional summary statistics, appropriate for the particular model type are also shown. These statistics can give an indication of how good the model predicts the data and can also be used to compare model predictions against another model of the same type.

Summary Statistics

Regression Model

Description

R-squared (or R2)

R-squared measures the fraction of the variability in the data that is explained by the model. It is a number between 0 and 1 with 1 being a perfect fit model (all observations are predicted exactly).

SSE

SSE, or Sum of Squares Error is the sum of the squared differences between the observed response and the predicted values from the models.

 

Classification Model

Description

Accuracy

The accuracy for a classification model is the fraction of values that are classified correctly by the model. Be careful when using this measure when the numbers in the classes are not balanced.

Kappa

Kappa is a measure of agreement for classification predictions that takes into account the agreement occurring by chance. Kappa takes on value between -1 (total disagreement) to 1 (total agreement).

See also:

Available Diagnostic Visualizations

Using a Confusion Matrix

The confusion matrix for a classification model compares the counts of the predicted class values with the observed or true class values.

It is a k x k table where k is the number of classes in the response. A good classifier has most of the counts on the diagonal from upper left to lower right (correct classifications) and few values on the off diagonal.

See also:

Using an Evaluation Summary

Available Diagnostic Visualizations

Available Diagnostic Visualizations

This section lists the available diagnostic plots for the model. They can be an aid to help determining the validity of a predictive model. Different model methods display different lists of diagnostic plots. Click on an option to display the visualization in the model page.

Residuals vs. Fitted

The residuals vs. fitted visualization is a scatter plot showing the residuals on the Y-axis and the fitted values on the X-axis. You can compare it to doing a linear fit and then flipping the fitted line so that it becomes horizontal. Values that have the residual 0 are those that would end up directly on the estimated regression line. The residuals vs fit plot is commonly used to detect non-linearity, unequal error variances and outliers.

Shape (exaggerated)

Conclusion

AvailableDiagnosticVisualizations1.png

When a linear regression model is suitable for a data set, then the residuals are more or less randomly distributed around the 0 line.

AvailableDiagnosticVisualizations6.png

When residuals form a pattern in the visualization, then the current model might be less suitable for the data.

 

 

Normal Quantile-Quantile

The normal quantile-quantile visualization calculates the normal quantiles of all values in a column. The values (Y-axis) are then plotted against the normal quantiles (X-axis).

Things to look for:

Shape (exaggerated)

Conclusion

AvailableDiagnosticVisualizations7.png

Approximately normal distribution.

AvailableDiagnosticVisualizations8.png

Less variance than expected. While this distribution differs from the normal, it seldom presents any problems in statistical calculations.

AvailableDiagnosticVisualizations9.png

More variance than you would expect in a normal distribution.

AvailableDiagnosticVisualizations10.png

Left skew in the distribution.

AvailableDiagnosticVisualizations11.png

Right skew in the distribution.

AvailableDiagnosticVisualizations12.png

Outlier. Outliers can disturb statistical analyses and should always be thoroughly investigated. If the outliers are due to known errors, they should be removed from the data before a more detailed analysis is performed.

 

 

Note: Plateaus will occur in the plot if there are only a few discrete values that the variable may take on. However, clustering in the plot may also be due to a second variable that has not been considered in the analysis.

Scale – Location

The scale – location plot is similar to the residuals vs fit plot, but instead of linear residuals it uses the square root of the residuals. It is used to reveal trends in the magnitudes of residuals. For a good model, the values should be more or less randomly distributed.

AvailableDiagnosticVisualizations1.png

Cook's Distance

Cook's distance is a statistic which tries to identify those values which have more influence than others on the estimated coefficients. High peaks in the bar chart might represent values that should be investigated further, since they have a larger effect on the coefficients.

AvailableDiagnosticVisualizations2.png

Response vs. Fitted or Predicted

The Response vs. Fitted or Response vs. Predicted visualization is a scatter plot of the response variable versus the fitted values for the model or the predicted values computed from new data using a previously computed model. The ideal shape for this plot is all points on a line with an intercept of 0 and a slope of 1 (about a 45 degrees angle). This would indicate that the response values and values computed from the model match up perfectly. In reality, the points will be in a diagonal band around the (0,1) line.  Points that deviate greatly from this band can indicate outliers or deficiencies in the model.

AvailableDiagnosticVisualizations3.png

Generally, the Residuals vs. Fitted or Predicted scatter plot is a better visualization to diagnose model deficiencies, since the deviations are centered around the horizontal line, y=0, instead of around the (0,1) line.

Predicted Probability Histograms

The Predicted Probability is a histogram of the predicted probabilities for a particular level of the response variable. For a two level response, you would like to have all the values in one histogram close to one and, in the other histogram, all the values should be close to zero.

AvailableDiagnosticVisualizations4.png

ROC Curve

An ROC, or receiver operating characteristic curve, shows the performance of the classifier as the threshold for class prediction is varied. It is a plot of the sensitivity, or true positive rate of the classifier, versus one minus the specificity, or false positive rate. The true positive rate is the number of the predicted positives out of true positives and the true negative rate is the number of the predicted negatives out of the number of false positives. The predicted positives and negatives varies as the threshold for class prediction varies.

AvailableDiagnosticVisualizations5.png

For example, with classes A and B, if the threshold is set very low for class A (close to zero) then all the tree class A observations will be classified as A (sensitivity is one). However, many class B observations will also be incorrectly classified as A leading to a large false positive rate. The ideal ROC curve starts at (0,1) goes up to (0,1) and then over to (1,1).

Randomly assigning predicted classes leads to an ROC curve that is a line with a slope of 1 from (0,0) to (1,1).

See also:

Using a Model Summary

Using a Table of Coefficients

What is the Analytic Models Panel?


The Analytic Models panel is used to manage all models within your analysis. Click on the corresponding icon to perform a task with the selected model.

WhatistheAnalyticModelsPanel.png

Option

Description

Model Name

Displays the names of all models currently in the analysis.

Method

Displays the prediction model method used for each model.

Data Table

Displays the data table used to create the model.

Formula

Displays the formula used for the prediction.

Comment

Displays the comment provided when creating the model.

Edit

Opens the Regression Modeling or the Classification Modeling dialog where you can make changes to your current model.

Evaluate

Opens the Evaluate Model dialog where you can test the model against another set of data in order to see how well the model fits other data.

Predict

Opens the Insert Predicted Columns dialog where you can use the model to insert predicted columns into a data table.

Delete

Deletes the selected model from the analysis.

The analytic models panel also contains a pop-up menu where you can perform all the tasks explained above, and also select Open Model Page to reopen a closed model page, or select Refresh List to update the models shown in the panel when models have been added to deleted.

You can view and manage analytic models in a popover, in a docked panel, or as a floating window. See Panels and Popovers.

See also:

What is Predictive Modeling?

Details on Duplicate Model


You can use an old model as the base for a new one by clicking Duplicate model icon, OpensDuplicateModel.png, on the model page for the base model.

DetailsonDuplicateModel.png

Option

Description

Model name

Type a new name for the new model.

See also:

The Model Page

What is the Analytic Models Panel?

Details on Evaluate Model


This dialog allows you to compare the model to another data table that includes the values you are trying to predict using the model. For example, a model can be created using confirmed sales data for the month of January, evaluated by using confirmed sales data for February and then used to predict future sales.

When evaluating a model, you can select a data table to evaluate the model on and match the response and predictor columns in the model data and the evaluation data.

DetailsonEvaluateModel.png

Option

Description

Selected model

Select the model in the analysis to evaluate.

QuestionMark.png

Displays the information provided in the Comment field when creating the model.

Select data table

Select the data table to evaluate the model against.

Match response column

Match the response column from the model with the one in the specified data table.

Match predictor columns

Match the predictor columns from the model with the ones in the specified data table.

See also:

What is Predictive Modeling?

Details on Insert Predicted Columns


The Insert Predicted Column dialog allows you to embed model predictions in your data. You must create an analytic model using Regression Modeling or Classification Modeling before you can insert any predicted columns.

  • To reach the Insert Predicted Columns dialog:

  1. Open the Insert menu.

  2. Select Predicted Columns....

DetailsonInsertPredictedColumns.png

Option

Description

Select model

Select the model in the analysis from which to insert a predicted column.

QuestionMark.png

Displays the information provided in the Comment field when creating the model.

Select data table

Select the data table to insert the column into.

Match predictor columns

Match the response column from the model with the one in the specified data table.

See also:

What is Predictive Modeling?

Data Functions

What are Data Functions?


Data functions are calculations based on S-PLUS, open-source R, SAS®,  MATLAB® scripts*, or R scripts running under TIBCO Enterprise Runtime for R for Spotfire, which you make available in the TIBCO Spotfire environment. Once a data function has been defined and saved in the Spotfire library using the Register Data Functions dialog, it can be applied at many different places in an analysis. For example, it can be used as a transformation step when you add or replace data tables. It can also be a separate tool that is run from the Insert menu.

S-PLUS or open-source R data functions can be defined from either an existing function in the corresponding Spotfire Statistics Services package repository, or by writing a script directly in the Register Data Functions dialog, and then running in the appropriate engine (either in the S-PLUS engine for S-PLUS functions, or in the TIBCO Enterprise Runtime for R engine or open-source R engine for R functions).  SAS or MATLAB engines can only be accessed using scripts, and only when you have a working installation of the selected software available. See http://support.spotfire.com/sr.asp for information about the TIBCO Spotfire Statistics Services system requirements.

During the set-up of a data function, a number of input and output parameters can be specified.

It is a best practice to avoid sending very large data sets from Spotfire to a statistical engine, or to invoke complex, long-running calculations. This ensures a rapid response and a good user experience.

Concerning R:

To use open-source R functionality with TIBCO Spotfire, you must configure TIBCO Spotfire® Statistics Services with a TIBCO Enterprise Runtime for R engine or with an open-source R engine. For more information on setting up Spotfire Statistics Services or your Spotfire Statistics Services Local Adapter using R, please read the TIBCO Spotfire Statistics Services Installation and Administration Guide or the README file included with the Statistics Services Local Adapter. (Statistics Services Local Adapter does not support the TIBCO Enterprise Runtime for R engine. Other third-party calculation tools can be added using the TIBCO Spotfire API.)

Open-source R is available under separate open source software license terms and is not part of TIBCO Spotfire.  As such, open-source R is not within the scope of your license for TIBCO Spotfire. Open-source R is not supported, maintained, or warranted in any way by TIBCO Software Inc.  Download and use of open-source R is solely at your own discretion and subject to the free open source license terms applicable to open-source R.

Example 1:

A simple conversion of the values in a column from degrees Celsius to degrees Fahrenheit. This is of course just as easy to accomplish using the Insert Calculated Column tool, but it serves as an example simple enough to show input and output parameter handling in more detail.

  • To create and run an R script data function in a TIBCO Enterprise Runtime for R Engine:

  1. Assume that the data table in TIBCO Spotfire contains a column with temperatures expressed in degrees Celsius.

  2. First, select Tools > Register Data Functions....

  3. Choose the Type R script - TIBCO Enterprise Runtime for R from the drop-down list and define the script that will perform the conversion on the Script tab:
    # Define the convertTemperature function:
    convertTemperature <- function(x)
    {
      x*(9/5) + 32
    }
    # Run the function to produce the output:
    out <- convertTemperature(x);

  4. Define the Input parameter x as a column with the allowed data types Integer and Real.
    WhatareDataFunctions1.png

    Tip: You can select the parameter in the Script tab and use the pop-up menu option Input Parameter... to reach the Input Parameter dialog directly.

  5. Define the Output parameter out as a column.
    WhatareDataFunctions2.png

    Tip: You can select the parameter in the Script tab and use the pop-up menu option Output Parameter... to reach the Output Parameter dialog directly.
    Note that the output display name will not be propagated to the output column name. The column name is always the output specified by the R script.

  6. Save the data function to the library, as Temperature converter.

  7. To connect the input and output parameters to your current data in TIBCO Spotfire and run the calculation, select Insert > Data Function....

    Comment: You need to have some data loaded in TIBCO Spotfire to use this functionality.

  8. Click on the Temperature converter data function and click OK.

  9. In the Data Functions - Parameters dialog, specify that the input parameter x should be a column and select the data table and column to convert.
    WhatareDataFunctions3.pngWhatareDataFunctions4.png

  10. Click on the Output tab and specify that the output parameter out should be handled as a column.WhatareDataFunctions5.png

     

    WhatareDataFunctions6.png

     

  11. Click OK

The data function calculation is performed and a new column is added to the data table. You can change the parameter settings or refresh the calculation later by selecting Edit > Document Properties, Data Functions tab.

Example 2:

If the function to use is a Principal Component Analysis (PCA) calculation, the input would be a number of numerical data columns retrieved from the current data in TIBCO Spotfire and, optionally, a parameter specifying the percent variation to be preserved by the principal components. The output would include three new data tables (scores, loadings and eigenvalue/explained variance table) and a scalar indicating the number of principal components generated.

See also:

How to Use Data Functions

Details on Register Data Functions

* SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

MATLAB is a trademark or registered trademark of The MathWorks, Inc.

How to Use Data Functions


Data functions can be used to enhance the functionality of TIBCO Spotfire in many ways. Below are a few examples of where and how data functions can be defined and applied. Note that you or your admin must first set the address to TIBCO Spotfire Statistics Services or TIBCO Spotfire Statistics Services Local Adapter as explained below. See also What are Data Functions?

  • To register an S-PLUS function in Spotfire:

  1. When a function that you want to use from within TIBCO Spotfire has been defined and saved to your package repository in Spotfire Statistics Services, you need to write down or remember its name, together with the names of all required input and output parameters.

  2. In TIBCO Spotfire, select Tools > Register Data Functions....

    Response: The Register Data Functions dialog is displayed.

  3. From the Type drop-down list, select S-PLUS function.

  4. In the Packages field, type the exact name of the package where the function is located in the Spotfire Statistics Services package repository. (This is only necessary if there is more than one function bearing the same name in the repository, or the packages are not loaded automatically.)

  5. In the Function name field, type the exact name of the function of interest, as it was defined in the Spotfire Statistics Services package repository.

    Comment: TIBCO Spotfire will not be able to locate the function in the Spotfire Statistics Services package repository unless the name is exactly the same.

  6. If desired, type a new Description of the function.

  7. On the Input Parameters tab, add all required input parameters.

    Comment: How the input parameters should be handled is defined upon execution of the data function.

  8. If necessary, move the input parameters so that the order in the list reflects the order in which the input parameters should be retrieved.

  9. On the Output Parameters tab, add all required output parameters.

    Comment: How the output parameters should be handled is defined upon execution of the data function.

  10. Save the data function.

    Comment: You can specify keywords upon saving that will help in locating the function in the library at a later stage.

    Response: The registered data function is saved in the library.

  11. Click Close.

    Comment: The data function can now be added to an analysis by running it from the Insert menu (see below).

The same steps would be used if the function was created using open-source R.

When open-source R scripts are used to register the data function, for step 3, you must specify the engine that you want your function to run in. Specify either the TIBCO Enterprise Runtime for R engine or the open-source R engine.

When S-PLUS or open-source R scripts are used to register the data function, step 5 is omitted.

For SAS and MATLAB scripts, step 4 is also unapplicable.

  • To map and execute a previously registered data function from the Insert menu:

  1. In TIBCO Spotfire, select Insert > Data Function....

    Response: The Data Functions - Select Function dialog is displayed.

  2. Optionally, click on a Keyword or use the search field to limit the number of shown functions in the dialog.

    Comment: For more information about search expressions, see Searching in TIBCO Spotfire.

    Response: Only the functions whose names match the keyword or search expression are shown in the dialog.

  3. Click to select the desired function.

  4. Click OK.

    Response: The Data Functions - Parameters dialog is displayed.

  5. Specify whether or not to Refresh function automatically.

    Comment: If the check box is selected a new calculation will be performed each time the input parameters change. If the check box is cleared, you need to refresh the data function manually.

  6. Specify what to do with the input or output parameters and then click OK.

    Comment: For a description of the available input and output handlers, see Details on Data Functions - Parameters.

    Response: The function is executed. Depending on how the output parameters were set up, you may get a new or updated data table, new columns or rows, or an updated data table or document property, which can be used to control a variety of settings in your analysis. A Details... link with progress information  will be available in the lower left part of the TIBCO Spotfire window. Click on the link for more information.

Note: Time and Date formats are not directly supported by Spotfire Statistics Services. When sending this type of input to Spotfire Statistics Services you will get the data back in a DateTime (TimeDate) format where a default date or time part has been appended to the original data. Columns with the data type Currency cannot be used in data functions.

  • To refresh a data function from a text area:

Once executed in the document, data functions can also be refreshed by clicking on an action link or button in a text area. This can be a way to make it easier for other users of the analysis to find and use the data function. For example, if the data function has been set up to work with filtered rows, many people can access the analysis, filter to their special area of interest and click on a button to receive calculation results relevant for them.

  1. Create an analysis where the data function can be used and run the data function from the Insert menu as described above.

    Comment: This is done in order to make the current document aware of the data function.

  2. Create a text area and enter edit mode by clicking on the Toggle Edit Mode button, ToggleEditModeButton.png.

  3. Click on the Insert Action Control button, ActionControlButton.png.

    Response: The Action Control dialog is displayed.

  4. Click on Data Function in the left-hand pane.

  5. Type a Display text to use on the button or link.

  6. Select the Control type to use.

  7. Click to select the data function of interest from the Available data functions list.

    Comment: Only those data functions that have been executed from within the current document and do not use automatic refresh will be available.

  8. Click OK.

    Response: The button or link (or image) is added to the text area.

  • To use the result from a data function to draw a line in a visualization:

Data functions can be used to calculate new data which can be used to do such things as drawing a line in a visualization. This can be accomplished in a variety of ways, but the steps below describe an example where the data function result is presented as two new columns containing coordinate values.

  1. Define and execute a data function which uses the current data columns as input and performs a calculation before the new resulting columns are added to the data table.

    Response: The data function is added to the document, and the result columns are added to the data table.

  2. Create a visualization which can display lines and curves, such as a scatter plot.

    Comment: Line from Column Values cannot draw curves over categorical axes, so make sure that the value columns on both axes are continuous.

  3. Right-click in the visualization and select Properties.

    Comment: You can also select Edit > Visualization Properties from the main menu.

    Response: The Visualization Properties dialog is displayed.

  4. Click on Lines & Curves.

  5. Click Add and select Line from Column Values....

    Comment: This is the option to use if the data results are located in one X-values and one Y-values column. If the results from the data function are presented as curve parameters you should use Curve from Data Table instead, and if a curve equation is calculated, use Curve Draw.

    Response: The Line from Column Values dialog is displayed.

  6. Select the Data table with line values.

    Comment: This should be the data table where the result columns were added.

  7. Select the X-values column.

  8. Select the Y-values column.

  9. If desired, select a Sorted by column, to specify a different sort order.

  10. Specify whether to Ignore (skip) empty values or to Split curve at empty values.

  11. Specify whether to use an Automatic or a Custom curve name.

  12. Click OK.

    Response: The curve is added to the visualization.

  • To change settings for a saved data function in an analysis:

  1. Select Edit > Document Properties.

    Response: The Document Properties dialog is displayed.

  2. Click on the Data Functions tab.

    Response: All data functions that have been executed in the analysis are shown in the Available data functions list.

  3. Click to select the function of interest in the Available data functions list.

  4. Click Edit....

    Comment: To simply update the data function using the same settings as last time, click Refresh.

    Comment: Use the Sync button to update the data function with any changes done to the data function in the library.

    Response: The Data Functions - Parameters dialog is displayed.

  5. If desired, change whether or not to Refresh function automatically.

  6. Change the Input parameter settings and click OK.

    Comment: You cannot change the Output parameter settings from here. If this is required, you must execute the data function from the Insert menu again.

    Response: The data function is updated using the new settings.

  • To change the address to Spotfire Statistics Services:

The address to Spotfire Statistics Services is normally specified by the TIBCO Spotfire administrator, on a group level. This step instruction shows how an administrator can change the address for a user group. It is also possible to use a local calculation engine (see below).

  1. Login to  TIBCO Spotfire as a user with administration rights.

  2. Select Tools > Administration Manager.

    Response: The Administration Manager is displayed.

  3. Click on the Preferences tab.

  4. Click on the group of interest in the Selected group list.

    Response: The preferences and configuration sets for the selected group are displayed.

  5. On the Preferences tab, click on the plus sign next to TIBCO Spotfire Statistics Services to expand that group.

  6. Click on StatisticsServices in the TIBCO Spotfire Statistics Services group.

    Response: The currently specified TIBCO Enterprise Runtime for R URL, S-PLUS URL, R URL, SAS URL and MATLAB URL are shown.

  7. For the implementation of your choice, click Edit.

    Response: The Edit Preferences dialog is displayed.

  8. Type or paste a new URL for Spotfire Statistics Services.

  9. Click OK.

  10. Click Close.

  • To use an alternative calculation engine, e.g., TIBCO Spotfire® Statistics Services Local Adapter:

  1. Select Tools > Options.

    Response: The Options dialog is displayed.

  2. Go to the Data Functions page.

  3. Click the Custom URL radio button.

  4. Type the address to the local calculation engine.

  5. Click OK.

  • To delete a saved data function from the library:

If you have administrative rights, you can delete and edit the name, description and keywords of a data function using the Library Administration tool.

  1. Select Tools > Library Administration.

    Response: The Library Administration dialog is displayed.

  2. Click to select the data function of interest.

    Comment: You can use the search field at the top right corner of the dialog to help in locating the data function. (Use 'type:datafunction' as the search expression in the search field to find all data functions.) See Searching the Library for more information about search expressions.

  3. Click on the Delete button at the top of the dialog.

    Response: A dialog asking you if you are sure you want to delete the data function is displayed. Click Yes and the data function is removed.

  4. Click Close.

  • To edit the name, description or keywords of a saved data function in the library:

If you have administrative rights you can delete and edit the name, description and keywords of a data function using the Library Administration tool.

  1. Select Tools > Library Administration.

    Response: The Library Administration dialog is displayed.

  2. Click to select the data function of interest.

    Comment: You can use the search field at the top right corner of the dialog to help in locating the data function. (Use 'type:datafunction' as the search expression in the search field to find all data functions.) See Searching the Library for more information about search expressions.

  3. Click on the Edit... link next to the Selected Item header in the lower part of the dialog.

    Response: The Edit Properties dialog for the selected data function is displayed.

  4. Change the properties for the data function and click OK.

  5. Click Close.

  • To recreate a deleted result column:

If you run a data function which creates one or more columns and you then delete one of the result columns from the analysis, the lost column will remain gone when refreshing the data function. In order to recreate the lost column you must run the data function from the Insert menu once again.

See also:

What are Data Functions?

Details

Details on Register Data Functions

The Register Data Functions dialog is used to set up data functions that will allow you to add calculations written in S-PLUS or open-source R to your analysis, which then runs in an S-PLUS engine, or in an R engine or a TIBCO Enterprise Runtime for R engine, respectively. If you have access to the corresponding software, you can also use SAS® or MATLAB® scripts when registering data functions. See the TIBCO Spotfire Statistics Services - Installation and Administration Guide for information on how to connect to different engines.

The Register Data Functions dialog also allows you to specify which S-PLUS or open-source R functions in your Spotfire Statistics Services package repository should be available for end users of TIBCO Spotfire. Once a function has been registered, it is available from the library and can be used in any analysis.

  • To reach the Register Data Functions dialog:

  1. Select Tools > Register Data Functions....

RegisterDataFunctions.png

Option

Description

New Function

Clears all text fields and list views in the dialog so you can start defining a new data function.

Open

Allows you to open a previously saved data function from the library for further configuration.

Save

Saves an edited data function to the library.

Save As

Opens the Save as Library Item dialog where you can specify a name and a location in the library where you want the data function to be saved.

Run

Opens the Data Functions - Parameters dialog so that you can specify settings for the input and output parameters and execute the current data function. This is mostly meant as a shortcut for testing the data function before it is saved to the library. Run the saved data function using Tools > Data Function...  before saving the analysis in order to be able to synchronize the data function with any updates in the library in the future. See also Document Properties - Data Functions.

Name

Displays the name of the data function when it has been saved to the library. If the function has not yet been saved, this field will be empty.

Type

Allows you to select what type of function to use. The types available will depend on which calculation engine you have access to. For predefined functions, select S-PLUS function or open-source R function. To define a new script, select S-PLUS script, R script - Open Source R, R script - TIBCO Enterprise Runtime for R, SAS script or MATLAB script.

Packages

[Available for R scripts run in TIBCO Enterprise Runtime for R for Spotfire, S-PLUS functions and scripts, and open-source R functions and scripts only, not available for SAS or MATLAB scripts.]

Optional. Allows you to specify any packages in the Spotfire Statistics Services package repository that are being used by your current data function.

For example, if you want to create a data function based on a predefined statistical S-PLUS function, you need to provide the name of the package in the S-PLUS repository where this function is located (e.g., stat).

If more than one package is required, separate the package names with semicolons.

Function name

[Available for S-PLUS and open-source R functions only, not for scripts.]

This is where you type the function name as it has been defined in Spotfire Statistics Services.

Description

Optional but recommended. A description of the data function. This description is stored in the library and can help the end users of the data function to select the proper function.

Allow caching

Select this check box to allow calculations to be reused if the same subset of input values has been calculated before.

Clear this check box if some of the input data comes from somewhere else than your current analysis and you want a new calculation from Spotfire Statistics Services each time input data are changed (even for changes into something that has already been computed before). Input data are often changed when the input is depending on filtered values, marked values or a property value.

For example, if the data function includes a random number generator, you will probably not want to cache a previously generated random number but instead calculate a new one for each refresh of the data function. Another example of when to clear the check box is when the data function includes the current date or time.

Script tab

Only available for script type data functions. To change to a script type data function, click on the Type drop-down list above and select S-PLUS script, or R script - TIBCO Enterprise Runtime for R, or R script - Open Source R.

RegisterDataFunctions-ScriptTab.png

Here you can type or paste any script in the specified script type language.

The font settings for the script tab can be changed using Tools > Options, Fonts page and selecting Expression and script editor.

Input Parameters tab

RegisterDataFunctions-InputParametersTab.png

Option

Description

Parameters

Lists all input parameters that have been mapped using the Input Parameter dialog.

Add...

Opens the Input Parameter dialog.

Edit...

Opens the Input Parameter dialog with the selected input parameter and its current settings available for editing.

Remove

Removes the selected input parameter from the list.

Move Up

Moves the selected input parameter up one step. The order of the input parameters in this list determines the order in which the input parameters should be retrieved.

Move Down

Moves the selected input parameter down one step.

Output Parameters tab

RegisterDataFunctions-OutputParametersTab.png

Option

Description

Parameters

Lists all output parameters that have been mapped using the Output Parameter dialog.

Add...

Opens the Output Parameter dialog.

Edit...

Opens the Output Parameter dialog with the selected output parameter and its current settings available for editing.

Remove

Removes the selected output parameter from the list.

Move Up

Moves the selected output parameter up one step. Use this button to place the most important output parameters at the top of the list.

Move Down

Moves the selected output parameter down one step.

See also:

What are Data Functions?

How to Use Data Functions

Details on Input Parameter

DetailsonInputParameter.png

Option

Description

Input parameter name

The name of the parameter as it has been referred to in the function or script.

Display name

The name of the parameter as you want it to be displayed to the end users.

Type

The input type, which can be Value, Column or Table. This defines whether the input parameter can be one or more columns, or just a single value.

Allowed data types

Specifies which data types are supported by this input parameter. Select the check box for all data types that you want to allow.

All

Click All to select all data types.

Numeric

Click Numeric to select all numeric data types.

None

Click None to clear all check boxes. Then select one or more data types to continue.

Description

Optional. Can contain more information about the input parameter.

Required parameter

Select this check box to make the parameter required when calling the function. If the check box is cleared, the parameter is optional and the function should be able to work without it.

See also:

What are Data Functions?

How to Use Data Functions

Details on Output Parameter

DetailsonOutputParameter.png

Option

Description

Result parameter name

The name of the parameter as it has been referred to in the function or script.

Display name

The name of the parameter as you want it to be displayed to the end users.

Type

The output type, which can be Value, Column or Table. This defines whether the output parameter can be one or more columns, or just a single value.

Description

Optional. Can contain more information about the output parameter.

See also:

What are Data Functions?

How to Use Data Functions

Details on Save as Library Item

This dialog is reached when you have selected to save a new item (e.g., a data function or a color scheme) in the library.

DetailsonSaveasLibraryItem.png

Option

Description

Library path

(Upper part of the dialog)

Shows the path from the library root folder down to the folder where you are currently located in the library. You can navigate upwards in the folder structure by clicking on the name of a folder that you want to navigate to.

Folder contents list

(Middle part of the dialog)

Lists all the folders and files in the folder you are currently located in.

Name

The name of the item as it will be displayed in the library. It is important that the title is informative with regard to what the item actually does.

Note: The name can be 256 characters at most, and the following characters are not supported:

\ / : * ? " < >  |

Keywords (separated by semicolons)

Keywords specific to the content of the item. Keywords are separated by a semicolon, i.e., if you write "temperature conversion; Fahrenheit" this will be two keywords, "temperature conversion" and "Fahrenheit".

Keywords are used to group data functions in the Data Functions - Select Data Function dialog. A data function will appear in several different groups if it has multiple keywords.

New Folder...

Opens the New Folder dialog where you can specify a new folder.

Folder Permissions...

Opens a dialog where you can change the folder permissions.

See also:

What are Data Functions?

Library Administration - Introduction

Details on Data Functions – Select Function

This dialog is used to select which data function in the library to execute. The data functions are organized by the keywords entered while saving them to the library. A data function can appear in several different groups if it has multiple keywords. Data functions without keywords are only placed in the default (All) group.

  • To reach the Data Functions - Select Function dialog:

  1. Open the analysis in which to apply the data function.

  2. Select Insert > Data Function....

DetailsonDataFunctionsSelectFunction.png

Click to select a keyword in the Keywords list that matches the type of data function you are looking for. You can further limit the number of data functions shown by typing some text in the search field. This limits the data functions visible to the ones matching the current search expression. For more information about valid search expressions, see Searching in TIBCO Spotfire.

See also:

Details on Data Functions - Parameters

What are Data Functions?

Details on Data Functions – Parameters

This dialog allows you to define how the input and output parameters of the selected data function should be handled. It is necessary to specify a mapping of all required parameters to Spotfire in order to use the data function.

If you have reached the dialog from the Document Properties - Data Functions dialog then any previously executed output cannot be changed. When a data function already has been executed, only the input parameters and previously unspecified output parameters are available. Execute the data function from the Insert menu again if you need to change some of the other output parameter settings.

  • To reach the Data Functions - Parameters dialog:

  1. Open the analysis in which to apply the data function.

  2. Select Insert > Data Function....

  3. Click to select the function of interest from the list, then click OK.

Input tab

DetailsonDataFunctionsParameters.png

Option

Description

Name

Lists the name of the selected data function.

Refresh function automatically

Select this check box to update the results from the data function automatically each time the input settings are changed. If the check box is cleared, a manual refresh is needed in order for any updates to take effect.

A data function set to load automatically will switch to manual update if cyclic dependencies are detected in the analysis.

Input parameters

Lists all input parameters that have been defined for the selected data function. Select an input parameter in this list to edit its settings.

Input handler

Lists all possible input handlers for the selected input parameter. Depending on which input handler you select in this list, a different set of settings is available to the lower right in the dialog.

[Input handler settings]

See the table below.

OK

Executes the selected data function.

 

Input handler settings

Note that which input handlers are available depends on the type of input parameter that is selected (Value, Column or Table). You will not be able to select from all of the input handlers described below when specifying the input for a selected parameter.

Option

Description

Column

 

   Data table

Allows you to select the data table from which to retrieve the input column.

   Column

Allows you to specify which column to use as input from the selected data table.

   Limit by

Use a combination of filtering and markings to limit the calculations to rows matching the specified settings only. If more than one option is selected then calculations will be performed for rows matching the intersection of the selected filtering and markings only.

Leave both the Filtered rows and the Marked rows check boxes blank to base calculations on all rows.

      Filtered rows

Select this check box to limit the calculations to rows remaining after filtering with the specified filtering scheme.

      Marked rows

Select this check box to limit the calculations to rows marked by the selected markings.

If more than one marking is available in your analysis you need to determine which marking or markings to control the calculation. If more than one marking is selected, then calculations will be performed for rows matching the intersection of the markings.

Columns

 

   Data table

Allows you to select the data table from which to retrieve the input columns.

   Columns

Lists the selected input columns. Click Select Columns... to change columns.

   Select Columns...

Opens a dialog where you can specify which columns to include as input to the function.

   Limit by

See a description of the options under Column above.

Expression

 

   Data table

Allows you to select the data table to evaluate the expression against.

   Expression

Displays the expression.

   Edit...

Opens the Edit Expression dialog where you can specify an expression.

   Limit by

See a description of the options under Column above.

Value

 

   Value

Allows you to type an input value in the text box.

Document property

 

   Property

Allows you to select a document property to use as input. Use the search field to help locate your property.

   New...

Opens the New Property dialog where you can define a new document property to use as an input parameter.

   Edit...

Opens the Edit Property dialog where you can change the value of the selected property.

   Delete

Deletes the selected property.

Data table property

 

   Data table

Allows you to select the data table to work with.

   Property

Allows you to select a data table property to use as input. Use the search field to help locate your property.

   New...

Opens the New Property dialog where you can define a new data table property to use as an input parameter.

   Edit...

Opens the Edit Property dialog where you can change the value of the selected property.

   Delete

Deletes the selected property.

Column property

 

   Data table

Allows you to select the data table to work with.

   Column

Allows you to select which column to work with.

   Property

Allows you to select the column property you wish to use as input.

   New...

Opens the New Property dialog where you can define a new column property to use as an input parameter.

   Edit...

Opens the Edit Property dialog where you can change the value of the selected property.

   Delete

Deletes the selected property.

None

No input handler has been selected. This can be used for optional input parameters. If the input parameter is required, you must specify a different input handler to be able to continue.

 

Output parameters tab

DetailsonDataFunctionsParameters1.png

Option

Description

Name

Lists the name of the selected data function.

Refresh function automatically

Select this check box to automatically update the results from the data function each time the input settings are changed. If the check box is cleared, a manual refresh is needed in order for any updates to take effect.

Output parameters

Lists all output parameters that have been defined for the selected data function. Select an output parameter in this list to edit its settings.

Output handler

Lists all possible output handlers for the selected output parameter. Depending on which output handler you select in this list, a different set of settings will become available in the lower right portion of the dialog.

[Output handler settings]

See the table below.

OK

Executes the selected data function.

Output handler settings

Note that which output handlers are available depends on the type of output parameter that is selected (Value, Column or Table). You will not be able to select from all of the output handlers described below when specifying the output for a selected parameter.

Option

Description

Data table

 

   Create a new data table

Allows you to create a new data table with the name specified in the text field.

   Replace existing data table

Allows you to replace a previously added data table by selecting it from the drop-down list.

Columns

 

   Data table

Allows you to select a data table where the result columns should be placed.

   Map to input rows

Allows you to specify how the resulting values should be added to the data table. For example, if you have chosen to calculate results for filtered values only, you will probably want to add the results to those rows that were filtered when the calculation was performed.

   Replace columns on update

Select this check box if you want the results to update the columns in the data table rather than adding new columns for each calculation.

Rows

 

   Data table

Allows you to select a data table destination where the resulting rows should be placed.

    Replace rows on update

Select this check box if you want the results to update existing rows in the specified data table rather than adding new rows for each refresh.

Document property

 

   Property

Allows you to specify which document property to update using the output from the data function.

   New...

Opens the New Property dialog where you can define a new document property to use as an output parameter. Note that a property created at this point will not be of any specified data type, and the property will not be created until the data function has successfully finished its execution.

   Edit...

Opens a dialog where you can change the name of a newly created property. Note that you cannot edit properties that have been created before entering the Data Functions - Parameters dialog.

   Delete

Deletes the selected property. (Newly created properties only.)

Data table property

 

   Data table

Allows you to specify which data table the property is associated with.

   Property

Allows you to specify which data table property to update using the output from the data function .

   New...

Opens the New Property dialog where you can define a new data table property to use as an output parameter. Note that a property created at this point will not be of any specified data type, and the property will not be created until the data function has successfully finished its execution.

   Edit...

Opens a dialog where you can change the name of a newly created property. Note that you cannot edit properties that have been created before entering the Data Functions - Parameters dialog.

   Delete

Deletes the selected property. (Newly created properties only.)

Column property

 

   Data table

Allows you to specify which data table the property is associated with.

   Column

Allows you to specify which column the property is associated with.

   Property

Allows you to specify which column property to update using the output from the data function.

   New...

Opens the New Property dialog where you can define a new column property to use as an output parameter. Note that a property created at this point will not be of any specified data type, and the property will not be created until the data function has successfully finished its execution.

   Edit...

Opens a dialog where you can change the name of a newly created property. Note that you cannot edit properties that have been created before entering the Data Functions - Parameters dialog.

   Delete

Deletes the selected property. (Newly created properties only.)

None

No output handler has been selected. Use this option when the data function is set up with many output parameters and there is one output parameter that you do not want to use in the current calculation.

See also:

What are Data Functions?

Details on Select Columns

This dialog allows you to select a number of columns on which to base the selected calculations.

DetailsonSelectColumns.png

Option

Description

Available columns

Lists all columns available for selection.

[Type to search]

Type a search string to limit the number of items in the Available columns list. It is possible to use the wildcard character * in the search. See Searching in TIBCO Spotfire for more information.

Selected columns

Lists the columns selected to be used in this particular location.

Add >

Adds the columns selected in the Available columns list to the Selected columns list.

< Remove

Removes the selected columns from the Selected columns list.

Remove All

Removes all columns from the Selected columns list.

Move Up

Moves the selected column up in the Selected columns list. The order of the columns in this list determines the order of the columns in the temporary data table to be used in calculations.

Move Down

Moves the selected column down in the Selected columns list. The order of the columns in this list determines the order of the columns in the temporary data table to be used in calculations.

See also:

Details on Data Functions - Parameters

Details on Data Function - Transformation

Details on TIBCO Spotfire Statistics Services Login

This dialog is shown when the TIBCO Spotfire Statistics Services administrator has turned on Authentication and you try to execute a data function. Enter your credentials and click Login.

DetailsonTIBCOSpotfireStatisticsServicesLogin.png

Option

Description

Username

Type your TIBCO Spotfire Statistics Services username.

Password

Type your TIBCO Spotfire Statistics Services password.

URL

The URL to TIBCO Spotfire Statistics Services that you are trying to connect to.

See also:

What are Data Functions?

Data Type Mapping


Data dimensions

TIBCO Spotfire data

S-PLUS / TIBCO Enterprise Runtime for R / R data

Value

Vector  of length 1 (list for timeDate)

Column

Vector (list for timeDate)

Data table

data.frame

Data types sent and received via data functions in S-PLUS / TIBCO Enterprise Runtime for R / R engines

TIBCO Spotfire data type

Class (mode)  for TIBCO Enterprise Runtime for R and  R data type

Class (mode)  for S-PLUS

Integer

integer (numeric)

integer (numeric)

Real

numeric (numeric)

numeric (numeric)

SingleReal

Double  --  not supported with R

single (numeric)  

String - encoded as UTF-16

character (character) --  encoded as ISO8859-1

character (character)

Date

POSIXct or POSIXlt

timeDate (list)

Time

POSIXct or POSIXlt

timeDate (list)

DateTime

POSIXct or POSIXlt

timeDate (list)

Boolean

logical (logical)

logical (logical)

Binary

character

character

  • a POSIXct or POSIXlt received from the TIBCO Enterprise Runtime for R engine or an open-source R engine is always stored as DateTime in TIBCO Spotfire.

  • S-PLUS, TIBCO Enterprise Runtime for R, or open-source R factors are sent from TIBCO Spotfire Statistics Services as strings.

  • Other data types than the ones described above are not supported.

Support for invalid values

All numeric invalid values in TIBCO Spotfire are represented as NAs in the S-PLUS, TIBCO Enterprise Runtime for R,  and open-source R engines. There is no special support for invalid values of other types; they become valid default values in the S-PLUS, TIBCO Enterprise Runtime for R,  and open-source R engines.

Calling conventions for functions

The names of the data function input arguments must match the names of the S-PLUS, TIBCO Enterprise Runtime for R,  and open-source R  function arguments.

For functions that return only one value (not a list of values), the single data function output parameter will be matched to the value.

For functions returning a list of values, the data function output parameters must match the names of the list elements.

Sending and Receiving Data from SAS® and MATLAB®

When executing data functions via SAS and MATLAB scripts, the TIBCO Spotfire input and output data tables are converted to data types that these engines can process. See the TIBCO Spotfire Statistics Services - User's Guide for more details on the SAS and MATLAB data types used, and for information regarding limitations on data conversion.

Concerning R:

R is available under separate open source software license terms and is not part of TIBCO Spotfire.  As such, R is not within the scope of your license for TIBCO Spotfire. R is not supported, maintained, or warranted in any way by TIBCO Software Inc.  Download and use of R is solely at your own discretion and subject to the free open source license terms applicable to R.

See also:

What are Data Functions?

Name Encoding for Column Names Sent to Spotfire Statistics Services

Name Encoding for Column Names Sent to Spotfire Statistics Services


Column names in TIBCO Spotfire are stored as UTF-16 encoded strings, while variable names in TIBCO Spotfire Statistics Services are built from 8-bit ASCII characters matching [.0-9a-zA-Z]. Thus, the column names that are sent to TIBCO Spotfire Statistics Services must be encoded. This is done automatically when sending data to TIBCO Spotfire Statistics Services via the built-in data functions input handlers. If you need to provide column name input by some other means (e.g., via a document property) you may need to use the NameEncode function manually, in order to encode the column names prior to applying the data function.

Decoding may be necessary to interpret column names when the result from a data function is a text report about the columns. Use the NameDecode function to decode results that have not been automatically decoded by Spotfire output handlers.

NameDecode (and NameEncode) can be written as an S-PLUS script for ASCII and ISO-8859-1 characters.

Code UTF-16 to 0-9a-ZA-Z and .

Let 0-9, a-z, A-Z be the digits in a 62-base system. Cast 16-bit characters to unsigned short integers and write those integers in the 62-base system. Example; '£' is casted to 162, this is coded to '02C'. Let .. be the starting tag for a coded character.

The code range will be ..001 to ..h31 (where h31 is equal to 65635 in the 62-base system).

Code  examples:

NameEncode("Column.2") = "Column.2"

NameEncode("Column 2") = "Column..00w2"

NameEncode("Column £") = "Column..00w..02D"

See also:

What are Data Functions?

Data Type Mapping

Information Designer

What is the Information Designer?


The Information Designer is a tool for setting up data sources and creating and opening information links. An information link is a database query specifying the columns to be loaded and any filters needed to narrow down the data table prior to creating visualizations in TIBCO Spotfire. In Information Designer, information links are created from building blocks such as columns and filters using joins, calculations and aggregations.

The Elements tree in Information Designer is a representation of the folder structure in the library. The permissions for each folder specify which databases and elements should be available for different users or groups when creating information links. Folder permissions can be specified in the Information Designer, but the main permission handling is done with the Library Administration tool.

Once information links have been created in Information Designer, they can be opened by any user who has the appropriate licenses, allowing users who may not have knowledge of SQL or the underlying database structures to be able to execute advanced database queries.

Information links are opened by selecting File > Open From > Library....

Note that neither the Information Designer, nor the resulting information links are available when you are working offline.

See also:

General Workflow

General Guidelines for Setting up an Information Model

Start Tab

General Workflow


This is the general workflow for using Information Designer:

1. Set up the data sources

Enter the information required to connect to the databases which will be accessed through Information Designer.

More

2. Create folders for storing elements and set permissions

The library is a hierarchical structure where data sources, elements and information links can be organized into folders. Given that the library is also used for storing analysis files, it could be worthwhile to spend some time thinking over a suitable structure.  More

Different groups of users are given different levels of access to data. More

3. Combine tables by creating joins

If you want to work with data from different tables, you first need to create joins.

More

4. Define column elements from available data sources

Define the column elements to be shown when creating information links. These columns can be taken directly from tables in one or more databases. They can also be calculated, filtered or otherwise modified.

More

5. Create filter elements to limit the data retrieved

Create filter elements with descriptive names to be applied when creating information links.

More

6. Create information links

Create information links that retrieve data from one or more databases and share them with your colleagues.

More

See also:

What is the Information Designer?

General Guidelines for Setting Up an Information Model

General Guidelines for Setting Up an Information Model


The Information Model (IM) concept aims to supply each end user with the data they need, with a minimum of effort and confusion.  Consequently, when building an IM, it is important to understand who the end users are and what data they require for their work.

Who are the end users?

Permissions are set on the folder level. Finding groups of users who work on related data will give you a good clue about the folder structure you should implement. Do not give all users access to everything - this will only cause confusion.

What data do they need?

What information is needed? How much data can users handle in a single request? Are there any commonly used threshold values? Answering these questions will guide you in setting up the correct joins, columns and filters.

Will users build their own information links?

Some end users will want to use the column and filter elements that you design, and assemble their own information links using Information Designer. Others will be less experienced, or may perform repetitive tasks. For these you should consider preparing complete information links in advance.

Tip: If your data source contains well-ordered data you can right-click on the data source and select Create Default Information Model... in order to quickly set up a simple information model.

See also:

Creating a New Folder

Creating a Join

Creating a Column Element

Creating a Filter Element

Setting Folder Permissions

Icon Explanations


In the Elements tree, the following icons may appear. Click on a link in the table below to find out more about each element type. In the Data sources tree only the items belonging to a database are visible.

Icon

Element type

Folder.png

Folder

InformationLink.png

Information link

InformationLink.png

Filter

BooleanColumn.png

Boolean column

StringColumn.png

String column

IntegerColumn.png

Integer column or LongInteger column

RealColumn.png

Real, ShortReal or Currency column

DateColumn.png

Date column

DateTimeColumn.png

DateTime column

DateTimeColumn.png

Time column

Procedure.png

Procedure

BinaryLargeObject.png

Binary Large Object

CharacterLargeObject.png

Character Large Object

Join.png

Join

DatabaseInstance.png

Database instance.

Database link/catalog - a link to another database.

Schema.png

Schema - a collection of tables and/or procedures.

Table.png Table - a set of columns.

TableAlias.png

Table alias. You can create a duplicate reference to a database table from Information Designer. This duplicate is called a Table Alias.

UnknownColumn.png

Unknown column.
Must be set manually to one of the accepted column types (above) before it can be saved as a column element.

See also:

Elements Tree

Data Sources Tree

Data Types

Creating an Information Link

Fundamental Concepts

Fundamental Concepts 


The Information Designer in TIBCO Spotfire requires no prior knowledge of query languages such as SQL. However, it is important to understand a few terms and concepts as they are used in this product:

  • Information links

  • Column elements

  • Filter elements

  • Folders

  • Procedures

Information Links

Information Links

An information link is a structured request for data which can be sent to the database. These specifications include one or more columns, and may include one or more filters.

Stated in plain English, an information link could be: "Fetch the Name, Address and Phone_number for employees that pass the filter High_Income."

Information links can also be used to limit what data to open in an analysis in a number of different ways. See Loading Data Overview for a summary of the various methods.

See also:

Creating an Information Link

Modifying an Information Link

Adding Hard Filters

Adding Prompts

Editing Information Links
Creating an Information Link 

Information Designer allows you to create information links. The building blocks are column-, filter- and procedure elements that have been set up by you or the database administrator.

  • To create an information link:

  1. Click New and select Information Link.

    Response: An Information Link tab is opened.

  2. In the Elements tree, select the column or filter element that you want to include. You can also include procedures.

    Comment: If the Elements tree is not visible in the left-hand pane, click on the Elements tab to display it. Click the + symbol to expand a folder. You can select multiple elements simultaneously by pressing Ctrl and clicking on the elements of interest, or by pressing Shift and clicking on the first and last element in a list.

  3. Click Add >.

    Response: The selected element is added to the Elements list on the Information Link tab.

    Comment: To remove an item from the information link, select the item and click < Remove.

  4. If the selected elements come from more than one data source table, you need to specify a Join path. This means that you must create all joins needed to link all data tables together and you must also specify these in the Join path section on the Information Link tab.

  5. In the Description field, type some text describing the purpose of the information link. This is optional.

  6. If you want to filter the data, open the Filters section and follow the instructions under Adding Hard Filters.

  7. If you want to add prompts (run-time filtering), open the Prompts section and follow the instructions under Adding Prompts.

  8. Optionally, you can also choose to specify any Conditioning, Parameters or Properties that are needed for your information link. See Information Link Tab for more information and links.

  9. Click Save.

    Response: The Save As dialog appears.

  10. In the folder tree, select where you want the information link to be saved.

  11. In the Name field, type a name for the information link.

  12. Click Save.

    Response: The new information link is added to the library. It can now be accessed by other users.

Tip: You can test the information link directly by clicking on Open Data.

Tip: You can view and edit the SQL behind the information link by clicking SQL....

See also:

Information Links

Modifying an Information Link

Deleting an Information Link

Opening Information Links

Modifying an Information Link 

If you have Browse + Access + Modify permission to a folder, you can edit existing information links in that folder.

  • To modify an information link:

  1. In the Elements tree, double-click on the information link you wish to edit. 

    Response: The content of the information link is opened in a new Information Link tab.

  2. Edit the link in the Information Link tab by adding or removing elements or by changing the filtering or prompting for a column.

  3. Open the modified link by clicking Open Data or save it by clicking Save.

    Comment: Replace the old information link by choosing the same name and folder location as the old link. Type a new name (or put the link in another folder) to keep both the old and the modified information links.

Note: Some characters are forbidden in the element names and file system of TIBCO Spotfire 3.0 and forward: /\*"?:<>|

Old information models with element names containing these characters can be imported. However, you cannot edit and resave elements using their old names if they contain forbidden characters.

See also:

Creating an Information Link

Deleting an Information Link

Adding Hard Filters

Adding Prompts

Deleting an Information Link 

  • To delete an information link from the Information Model:

  1. In the Elements tree, right-click on the information link you wish to delete. 

  2. Select Delete from the pop-up menu.

    Response: A dialog appears, prompting you to confirm that you want to delete the link.

  3. Click OK.

See also:

Creating an Information Link

Adding Hard Filters 

Hard filters are set up as you define the information link and are saved with the link. This means that they cannot be reused in other links. Also, hard filters can only specify a range or a list of values, and are therefore less complex than filter elements.

  • To add hard filters for a column:

  1. Create an information link.

  2. Go to the Filters section on the Information Link tab for that information link.

  3. Click Add.

    Response: The Add Column dialog is displayed.

  4. Select the column for which you wish to add a hard filter from the drop-down list.

  5. Click OK.

    Response: The column is added to the Filters list.

  6. Repeat steps 3 to 5 add filters for more columns.

  7. For each column, use the Filter Type drop-down list to select whether to filter by range or by values.

    Comment: Filtering by range means entering the upper and lower limits of the desired range. Filtering by values means entering the exact values that you want to include in the returned data, separated by semicolon.

  8. Set the upper and lower limits (range) in the Min Value and Max Value fields, or type the desired values, separated with semicolons in the Values field. You can also type ?param_name in the Values field to use a parameter as the filter for the chosen column, where param_name is the name to identify the parameter by.

    Comment 1: Limits are inclusive. In other words, if the lower limit is set to 1000, the value 1000 will be included in the data table.

    Comment 2: When setting upper and lower limits on columns of type String, 'A' is considered to come before 'AA', and 'S' comes before 'Smith'. This means that the name 'Smith' will not be present when choosing names from 'D' to 'S'. The order of characters is standard ASCII.

Tip: When entering values for filtering you are allowed to use wildcard characters. These are:

*

Matches any characters (example: '*mber' would return the following months: September, November and December).

?

Matches any single character (example: '???ember' would only return the months November and December).

See also:

Creating an Information Link

Adding Prompts

Information Link Tab

Parameterized Information Links

Adding Prompts 

An alternative to setting filter conditions in the Filters section (see Adding Hard Filters) is to configure your information link to prompt for filter values as the columns are retrieved. This way you do not have to specify filter conditions in advance. The data table will be pared down before the next column in sequence is processed (unless you have specified prompt groups to treat the columns as independent). See Prompted Information Link Example for further information.

  • To enable prompts:

  1. Create a new information link or modify an existing link.

  2. On the Information Link tab, go to the Prompts section.

  3. Click Add.

    Response: The Add Column dialog is displayed.

  4. Select the column for which you wish to add a prompt from the drop-down list.

  5. Click OK.

    Response: The column is added to the Prompts list.

  6. Repeat steps 3 to 5 add prompts for more columns.

  7. For each column, use the Prompt Type drop-down list to select how you want to be prompted for filter conditions during data retrieval.

    Comment: Selecting Values will let you enter a list of values to include. Range will let you specify a range of values. Multiple selection will present a list of available values from which you can select multiple values. Single selection will present a list of available values from which you can select a single value.

  8. Save the information link, or open it.

Note: When opening a prompted information link, the prompts will appear in the order that the columns appear in the Information Link tab. You can use the Move Up and Move Down buttons to move columns up or down. (Filter elements are always applied before any prompts, regardless of order.)

Tip: When entering values for filtering you are allowed to use wildcard characters. These are:

*

Matches any characters (example: '*mber' would return the following months: September, November and December).

?

Matches any single character (example: '???ember' would only return the months November and December).

See also:

Creating an Information Link

Using Prompts

Prompted Information Link Example

Using Prompt Groups 

The default behavior of prompted information links is that each subsequent step lists values based on earlier selections (See Prompted Information Link Example). However, if you are working against a STAR schema database the procedure may require multiple joins since the elements queried for the prompts can only be joined by also joining over the large fact table in the STAR schema. This may result in very long times passing between each prompt. To avoid the long prompt times, it may be useful to treat the various elements as independent and avoid filtering upon each prompt step. This can be accomplished by using prompt groups.

  • To use prompt groups to make elements independent:

  1. Create a new information link or modify an existing link.

  2. In the Information Link tab, go to the Prompts section.

  3. Click on Groups....

    Response: The Prompt Groups dialog is displayed.

  4. For each element, specify a prompt group by clicking on the number to the right of the column name.

    Comment: All column elements belonging to the same prompt group will depend on each other and, hence, filtering will be performed between each prompt step for these elements. Elements belonging to a different prompt group will be independent of all filtering made within a different prompt group. Note that setting columns or filters as independent may result in no data returned from the information link.

  5. Save the information link, or open it.

Note: By specifying a separate prompt group for an element no previous selections in the prompt steps will be reflected in the listing for this (independent) element. Neither will any of the selections made in the prompt step for the independent element be reflected in later prompt steps (regardless of whether the later prompt steps are independent or not).

See also:

Information Link Tab

When to Use Prompt Groups

Adding Procedures

Procedures are added to an information link in the same way as any other elements such as a column or a filter. You select them from the Elements tree and click Add >. The procedure icon looks like this: Procedure.png

Priority of Execution:

You can include column elements, filter elements and procedure elements in the same information link. These will be executed in a certain priority order when the information link is executed:

  • All pre-update procedures will execute in top-down order. The procedure is executed before any data is retrieved. No data is expected from the pre-update procedure, and no join is performed. Any prompting will occur in the top-down order.

  • All query procedures and column elements will execute in top-down order. The procedures run as a part of the data retrieval. Data from the procedures are joined with the rest of the columns. Any prompting will occur in the top-down order.

  • All post-update procedures will execute in top-down order. The procedure is executed after the data has been retrieved. No data are expected from the post-update procedure, and no join is performed. Any prompting will occur in the top-down order.

Note that the order of the elements in the user interface only has significance for the elements of the same "priority class". A pre-update procedure will always execute before a query procedure regardless if that pre-update procedure is placed at the bottom of the list.

See also:

Creating an Information Link

Modifying an Information Link

Deleting an Information Link

Adding Hard Filters

Adding Prompts

Parameterized Information Links

Using parameterized information links you can set up a data source to return only information applicable for a certain user or group. Depending on which user is logged in and accesses the information link, or, for example from where the user accesses the information link, different subsets of the data will be available.

The concept is similar to the personalized information links, but more general, and in need of API configurations to work properly. While the parameters are created in Information Designer, their properties and definitions are defined in the API.

A parameterized information link is an information link that contains a number of parameters. In Information Designer, you define these parameters with a name and a type, and can use the parameters when creating various elements in the information link. To be able to open a parameterized information link in Spotfire, the values for these parameters must be provided. How is determined in the API.

Why use parameters?

Parameters can be used if you, for example, want to set up a Web Player analysis with data loaded from a database. If the Web Player is integrated into a portal or similar, the contents of the analysis can be defined by parameters. The values of these parameters can then be defined by another application in the portal, or by settings the users have applied in other parts of the portal.

Another example is if you wanted to create an analysis file but then be able to easily create different versions of it for different users. You can then set up a template file using parameterized information links, and from that file, and using the API, create different files with different parameter values.

Where can parameters be included?

Element Type

Parameterized Property

Column

Column calculation.

Aggregate column key expression.

Filter condition.

Filter

Filter condition.

Procedure

Input parameter default value.

Information Link

Static filter.

Edited SQL (Pre-updates, Query, Post-updates).

Data Source

Open session commands.

Close session commands.

Examples:

For filter condition expressions, parameters can be used as the condition for the selected columns.

ParameterizedInformationLinks1.png

 

In the SQL editor, you can set parameters directly.

 

FROM

   "Sales"."dbo"."SalesandCost" S1

WHERE

   (S1."Sales" > ?MinSales)

Note: If you alter parameters in the SQL code, the changes will override the parameter settings made elsewhere, such as the filter part of the information link tab.

Syntax

The syntax of the naming of the parameterized information links is:

?param_name

Where "?" indicates that it is a parameter and param_name is the name the parameter is identified by.

Note: The parameter name should start with a letter from A-Z or a-z and can contain the following characters: a-z, A-Z, 0-9, _ and '.'.

See also:

Editing a Parameter

Editing a Parameter

  • To edit a parameter:

  1. Make sure the information link of interest is opened on an Information Link tab.

  2. Click Parameters.

  3. Select the parameter you want to edit.

    Comment: You may need to click Refresh to be able to see the parameter, Note that if you have edited the SQL of the information link, the altered SQL code overrides the parameters settings in the user interface and a parameter that was added in, for example, the filters section of the information link tab will not be visible.

  4. Click Edit...
    Response: The Edit Parameter dialog is displayed.

  5. Change Data type and/or Value type.

  6. Click OK.

See also:

Parameterized Information Links

Personalized Information Links

Using personalized information links you can set up a data source to return only information applicable for a certain user or group. Depending on which user is logged in and accesses the information link, different subsets of the data will be available. For example, you could set up an information link that detects whether the user retrieving data is a member of the sales force for Europe, Asia or the US and only return data for that continent.

Below are two examples of personalized information links. The first will retrieve data depending on which user is logged in, the second depending on which groups that user is a member of.

User Name via Lookup Table

Scenario: You want to set up an information link to a data table showing sales made by all the sales people in the company. However, depending on which person from the sales department accesses an analysis using this information link, only the sales figures pertaining to that single user should be retrieved.

First, take a look at the Sales table containing the total sales of every person in the sales force.

Order ID

Employee ID

Product

Sale ($)

1

101

Cornflakes

100

2

150

Soda

550

3

244

Cornflakes

160

4

101

Mineral water

400

5

101

Soda

120

6

339

Mineral water

200

 

Then you must create a "Lookup Table" on the data source, that matches the Employee ID to the actual Spotfire Username for each person logging into the TIBCO Spotfire Server.

 

Spotfire Username

Employee ID

mikesmith

101

lauraclarke

150

sarahdonovan

244

malcolmreynolds

339

 

Next, you create a Filter or Column element that constrains the "Spotfire Username" column to only return values for the currently logged in user. (In the example below, a new, restricted column element is created by adding the personalized condition as a hard filter on the column element. This is the column element that will be used in the information link.)

PersonalizedInformationLinks1.png

An example of the resulting SQL for the user malcolmreynolds  would be:  "LookupTable"."Spotfire Username" = 'malcolmreynolds'.

Finally, you create a Join between the Sales table and the Lookup table—joining the Employee ID columns.

Add the restricted column element (or the filter element) to an information link, together with any other columns of interest and save it. The information link is now ready to be used and will only retrieve data for the currently logged in user.

Group Membership

Scenario: You want to set up an information link to a data table showing sales made by all the sales people in the company. However, depending on which person from the sales department accesses an analysis using this information link, only the sales figures pertaining to the region that sales person is assigned to should be retrieved. For example, if a sales person is working in the East region, she should only be allowed to see sales figures made in that region (by any person).

The Spotfire Administrator has created groups on the Spotfire Server named SalesForce-East, SalesForce-West, SalesForce-South, and SalesForce-North. Each sales person is a member of one or more of these.

Note: You can also create a Lookup table, just as in the first example, in which you assign various users or groups to categories that match your Sales table. That way you do not need to create superfluous groups on the Spotfire Server if groups with matching names are not already available.

You then take a look at the Sales table containing the total sales of every person in the sales force.

 

Order ID

Employee ID

Region

Product

Sale ($)

1

101

SalesForce-East

Cornflakes

100

2

150

SalesForce-West

Soda

550

3

244

SalesForce-North

Cornflakes

160

4

101

SalesForce-East

Mineral water

400

5

101

SalesForce-East

Soda

120

6

339

SalesForce-East

Mineral water

200

 

Next, you add a Filter or Column element that constrains the "Region" column so that it only returns values if the currently logged in user is a member of a group with that exact name. (In the example below, a new, restricted column element is created by adding the personalized condition as a hard filter on the column element. This is the column element that will be used in the information link.)

PersonalizedInformationLinks2.png

An example of the resulting SQL for a user belonging to the SalesForce-East and the SalesForce-North groups would be:  "SalesTable"."Region" = ('SalesForce-East','SalesForce-North').

Add the restricted column element (or the filter element) to an information link, together with any other columns of interest and save it. The information link is now ready to be used and will only retrieve data for groups that the currently logged in user is a member of.

Syntax

The syntax for the personalized information link parameters is:

%CURRENT_USER%

and

%CURRENT_GROUPS%

See also:

Defining a Column Filter

Creating a Filter Element

Editing the SQL of an Information Link 

Information links are created using the Information Designer. However, sometimes there may be situations where complex SQL queries are needed that cannot be generated by Information Designer and the elements in the information model. An example might be to retrieve information from relational databases or databases with complex schemas, where queries generated by Information Designer are not fast enough and need tuning to reach an acceptable performance. For those purposes the possibility to manipulate the generated SQL of an information link has been introduced.

  • To modify the SQL of an information link:

  1. Make sure the information link of interest is opened on an Information Link tab.

  2. Click SQL....

    Response: The Edit SQL dialog is displayed.

  3. Select the Data source that you want to work with from the drop-down list.

    Comment: Complex information links may contain elements from several different data sources. You can only edit the SQL part of the query that belongs to a single data source at a time. After you have saved your first changes you can switch to another data source to edit the rest of the SQL.

  4. To edit Pre-Updates, Query or Post-Updates, click the corresponding radio button.

    Comment: Use Query to modify the SELECT statement of the information link. Use Pre-Updates or Post-Updates to add new statements or scripts to be run before or after the data retrieval.

  5. Edit the SQL statement (or add Pre- or Post-Updates) in the Modified SQL text box.

    Comment: You can compare the changes that you have made in the Modified SQL to the Original SQL at all times. Multiple SQL statements are allowed as long as they are separated with a semicolon and new line (except the last statement in sequence).
    Comment: You can use parameters in your SQL statements (and in your Pre- and Post-updates).
    Note: If you alter parameters in the SQL code, the changes will override the parameter settings made in, for example, the filter part of the information link tab.

  6. Click OK when you are finished.

    Comment: If you change your mind and want to start over with the original SQL, click Reset to Original.

Note: The modified SQL is not validated before execution. For this reason, you should not:

  • alter the name of a column (the AS-part of SELECT xxx AS yyy)

  • alter the number of columns returned

  • alter the datatype of columns returned

  • alter the order of columns returned

  • remove <temporary_result_#> from the FROM-clause in information links that go against multiple data sources

  • remove the trailing WHERE <conditions>, as it will be replaced by any conditions applied at runtime

See also:

Details on Edit SQL

Selecting Join Path

When you are using columns from more than one table in the same information link you have to specify a join path between all used tables. If multiple joins are created, you can specify which joins will be the default ones. The default joins will be presented in the join path field when the Suggest Joins button is clicked (if they are suitable for the current information link).

However, sometimes you may want to specify a different join path for an information link.

Let's say the default join path between Table 1 and Table 4 is via Table 3 and the joins TABLE1.ID=TABLE3.ID and TABLE3.NAME=TABLE4.NAME.

SelectingJoinPath.png

For a certain information link this is not what you want, instead you wish to configure the link to join via Table 2 using the joins TABLE1.ID=TABLE2.ID and TABLE2.COST=TABLE4.COST.

This can be done in the Join Path section for the information link by simply selecting the desired joins instead of the suggested ones.

  • To Select a Join Path:

  1. Go to the Join Path section on the information link tab.

  2. Select a join from the Joins tree that you wish to use and click Add.

    Response: The new join is added to the Selected Joins list.

  3. Repeat step 2 for any additional joins.

  4. Click Save to save the information link.

    Comment: The Selected Joins will be saved with the information link, and will always be used when retrieving data via the information link.

Note: When executing an information link against several data sources, all subqueries are run first and then the main query, joining all subresults. This means that the joins in the main query are performed after all joins in the subqueries.

See also:

Information Link Tab

Opening Information Links
Opening Information Links from Information Designer 

Once defined, information links are normally opened by selecting File > Open From > Library.... However, you can also open information links from within Information Designer. This can be useful while creating the information link, or if you need to make some minor adjustments to the original information link.

  • To execute an information link from Information Designer:

  1. Create a new information link or edit an existing link (see Modifying an Information Link).

    Comment: You can also right-click in the Elements tree and select Open Data... from the pop-up menu.

  2. Click Open Data.

    Response: The query is executed, and the data is shown in TIBCO Spotfire. If the information link includes prompted filters, one or more dialogs will appear before the data is loaded. See Using Prompts for details.

See also:

Creating an Information Link

Adding Prompts

Using Prompts 

  • To specify filter parameters during retrieval:

  1. Open an information link that contains prompts.

    Response: The system will begin to retrieve data. For each column with a prompt (see Adding Prompts) a dialog will appear, asking you to specify the filter conditions for this column.

  2. Enter the filter conditions that you want to apply.

    Comment: If you are using the Multiple selection or Single selection prompt methods, then you will find that only values that have not been eliminated by previous filter conditions (in the same prompt group) are shown.

  3. Click Next >, or Finish when you have come to the last column.

    Response: The data is retrieved and displayed in TIBCO Spotfire.

Note: Dialogs are displayed in the order the columns appear in the Information Link tab. In other words, the first column for which a prompt has been added will be the first to display a dialog.

See also:

Creating an Information Link

Adding Prompts

Prompted Information Link Example

Prompted Information Link Example 

Information links may include more than one column with prompts. In this case each filter will reduce the data table, so that subsequent prompts may present fewer values. (This can be overridden by using prompt groups.) Consider the following example:

This is the data as it would look if no filters were being applied:

Name

Salary

Location

Prompt: None

Range

Multiple selection

Miller

1300

New York

King

1400

New York

Clark

700

New York

Ford

1100

Dallas

Adams

900

Dallas

Scott

1300

Dallas

Jones

1200

Dallas

James

800

Chicago

Turner

1000

Chicago

As the information link is opened, Salary will be filtered first (the order is set in the Information Link tab).

PromptedInformationLinkExample1.png

The user enters the limits 1100 to 1300. After the first filter, the following remains:

Name

Salary

Location

Prompt: None

Range

Multiple selection

Miller

1300

New York

Ford

1100

Dallas

Scott

1300

Dallas

Jones

1200

Dallas

Several rows, including all Chicago employees, have been filtered out. This means that when the Location filter is shown, Chicago will not be presented as an option:

PromptedInformationLinkExample2.png

See also:

Adding Prompts

Using Prompts

Using Prompt Groups

Opening Information Links

Using Current Filter Settings as a Filter

When opening information links with columns prompted for values (see Adding Prompts) it is possible to use the visual capabilities of TIBCO Spotfire to set these values.

  • To use the current visualization for filter definition:

  1. Open a suitable data table in TIBCO Spotfire.

  2. Choose a subset either by filtering or by marking rows.

  3. Open an information link that contains prompts.

  4. When the Open Information Link dialog for the desired column appears, click Get Values....

    Response: The Column Values dialog is displayed.

  5. From the Column drop-down list, select the column (in the visualized data table) from which you want to get the values.

  6. Click All rows, Filtered rows or Marked rows, depending on which values you want to use.

  7. Click OK.

  8. In the Open Information Link dialog, click Next > (or Finish if this is the last prompted column).

  9. Repeat steps 4 to 8 for each prompted column.

    Response: The data is loaded and displayed in a visualization.

See also:

Using Prompts

Transforming the Data
Eliminating Duplicates 

Eliminating duplicates means removing all duplicate rows from the returned data table. By duplicates we mean rows where all fields are identical to all fields of another row. Consider the following example:

Before removing duplicates

Name

Income

Smith

1200

Jones

700

Banks

700

Smith

1200

Smith

900

After removing duplicates

Name

Income

Smith

1200

Jones

700

Banks

700

Smith

900

  • To eliminate duplicates:

  1. Create a new information link or edit an existing link (see Modifying an Information Link).

  2. In the Information Link tab, go to the Conditioning section.

  3. Click Distinct.

  4. Open or save the information link.

    Response: Duplicate rows will be removed from the data table returned by this information link when it is opened.

See also:

Creating an Information Link

Pivoting Data in Information Designer 

Pivoting is a method of rearranging rows into columns. This flexibility allows you to rotate row and column headings around the core data. In general, pivoting is used to be able to carry out visual analyses on data that originally reside in a tall/skinny format. Pivoting may also be used to create more filters in the filters panel by splitting a column into several other columns.

  • To pivot data:

  1. Create a new information link or edit an existing link (see Modifying an Information Link).

  2. In the Information Link tab, go to the Conditioning section.

  3. Select Pivot as conditioning type.

  4. Click Edit....

    Response: The Pivot Conditioning dialog opens.

  5. In the Identity section, click Add... to select the columns that you want to use to identify rows.

    Comment: Each unique value in the chosen identity column produces a row in the generated table.

  6. In the Category section, click Add... to select the columns that you want to use for generating new columns in the new table.

    Comment: Each unique value in the chosen category column produces a new column in the generated table.

  7. In the Values section, click Add... to select the column that you want to aggregate.

    Comment: The column from which the data is pulled. The values in the generated table are computed according to the method selected under Aggregation method in the Add Column dialog.

  8. Type a Column name expression to use for naming the pivoted columns.

  9. In the Other columns section, click Add... to select any other columns that you want to include in the new table.

  10. Click OK.

    Response: The Pivot dialog is closed.

  11. Open or save the information link.

    Response: The data will be pivoted when the information link is opened.

See also:

Example of Pivoting

Example of Pivoting with Aggregation

Example of Pivoting in Information Designer

Pivoting a data table means changing it from a tall/skinny format to a short/wide format. Consider the following tall/skinny table, based on a series of temperature measurements:

City

Month

Temp

London

February

4

New York

February

6

London

May

16

New York

May

19

London

August

28

New York

August

26

London

November

13

New York

November

11

 

As we add more observations, the table grows taller, but remains three columns wide. While useful during data collection, this format may not be appropriate for certain types of calculations or visualizations. For example, the entities that interest us are the different cities, so we may want a representation with a single row for each city.

Pivoting this table produces the following (note that avg(Temp) is the average of a single cell):

City

avg(Temp) for February

avg(Temp) for May

avg(Temp) for August

avg(Temp) for November

London

4

16

28

13

New York

6

19

26

11

 

Each city is now represented by a single row. The following steps have been performed during the pivoting:

  • A row has been created for each unique value in City.

  • A column has been created for each unique value in Month.

  • A value from Temp has been entered for each cell in the resulting grid.

The following settings were made in the Pivot Conditioning dialog to produce this result:

  • Identity: City

  • Category: Month

  • Values: Temp

See also:

Pivoting Data

Example of Pivoting with Aggregation

Example of Pivoting with Aggregation in Information Designer

Note: To understand this example, it is recommended to read Example of Pivoting first.

Apart from changing format from tall/skinny to short/wide, pivoting can be used to create a more compact table. Consider the following table, based on a series of temperature measurements:

City

Month

Day

Temp

London

February

1

5

London

February

15

8

London

May

1

15

London

May

15

22

New York

February

1

9

New York

February

15

7

New York

May

1

18

New York

May

15

24

 

Tall/Skinny=>Short/Wide conversion lets us pivot and aggregate this table, producing the following:

City

avg(Temp) for February

avg(Temp) for May

London

6.5

18.5

New York

8

21

  

A smaller table has been created, summarizing the original table. The following settings were made in the Pivot Conditioning dialog to produce this result:

  • Identity: City

  • Category: Month

  • Values: Temp

See also:

Pivoting Data

Example of Pivoting

Data Sources

Data Sources Overview

Data sources are the physical units from which data can be retrieved, usually databases. Connecting to a database requires technical know-how not always found among end users. Therefore, Information Designer lets the administrator make all the required settings in advance, including database name, username, password, etc. This information becomes part of elements and items in the library so that when an end user executes an information link, the connection to any required databases can be established automatically and invisibly.

User authentication to the data source can be made in two ways. The first way is to set up the authentication while creating the data source connection. In this case, all users connect with the same credentials. This is a simple way to authenticate users when everyone has the same permissions to the data source. Further limitation of the access to a data source can be applied by placing the data source within a folder with limited permissions for some users or groups. For more information, see Setting Folder Permissions.

The other way is to require authentication each time a connection is used. This is useful when a more detailed security model is required. Using user authentication, row level security can be obtained. The credentials can either be supplied by the user or by a plug-in when running an information link against this data source. A plug-in minimizes the number of times one has to log into the TIBCO Spotfire environment, leaving it up to the plug-in to deliver the data source credentials. Without a plug-in, users will have to supply their data source credentials in a prompt. Credentials can be cached in the data source so that users are prompted only once for each session.

The layout and handling of the data sources affect most other actions involving the creation of information links. Therefore, it is most valuable to do a proper planning before starting to work on the layout. Modifications to the data sources after the structure has been set up in the library and information links have been defined will probably mean that some manual work is needed to make sure all information links are still functional.

See also:

Creating a Data Source

Modifying a Data Source

Removing a Data Source

Creating a Data Source

  • To create a new data source:

  1. On the Start tab, click Setup Data Source.

    Response: A Data Source tab is displayed.

  2. In the Name field, type the name of the new data source.

  3. Specify the Type of the data source.

  4. Modify the Connection URL.

  5. Type a Username and Password that gives access to the data source.

  6. Select whether a user must be authenticated when accessing the data source.

    Response: If this option is selected, authentication is required when accessing this connection, either by the user or by a plug-in. If not selected, the credentials supplied above will be used for all users. Even if user authentication is selected, Username and Password must be supplied since they are used by Information Designer. When using a data source with user authentication the database administrator needs to make sure that the data source user has sufficient privileges, so that it is allowed to truncate and drop temporary tables in the data source.

  7. Enter any additional configuration settings.

  8. Click Save.

Note: If you are connecting to a non-writable data source (for example, SAS/SHARE or ODBC) you must clear the Allow writing in temporary tables check box.

Note: For SAS/SHARE and ODBC data sources, both Min and Max No of connections should be set to 0 in order to disable connection pooling. The same holds for other data sources that do not support pooled connection since there is no valid ping command.

See also:

Data Source Tab

Data Sources Overview

Modifying a Data Source

  • To modify an existing data source:

  1. In the Data Sources tree or in the Elements tree, double-click on the data source you wish to edit. 

    Response: The data source settings are shown in a new Data Source tab.

  2. Change any fields.

  3. Click Save.

    Response: The data source is updated.

Note: Some characters are forbidden in the element names and file system of TIBCO Spotfire 3.0 and forward: /\*"?:<>|

Old information models with data source names containing these characters can be imported. However, you cannot edit and resave data sources using their old names if they contain forbidden characters.

See also:

Data Sources Overview

Removing a Data Source

  • To remove a reference to a database:

  1. In the Data Sources tree, right-click on the data source you wish to delete. 

  2. Select Delete from the pop-up menu.

    Response: A dialog appears, prompting you to confirm that you want to delete the data source.

See also:

Data Sources Overview

Creating a Table Alias in Information Designer

You can create a duplicate reference to a database table from Information Designer. This duplicate is called a Table Alias, and references the very same data, only using an alias. This can be useful in certain cases, most often when in need of a self join within a table (below).

  • To create a table alias:

  1. Select a table on the Data Sources tab.

  2. Right-click the table and select Create Table Alias.

    CreatingaTableAliasinInformationDesigner.png

  3. Type a name for the table alias and click OK.

    Response: A table alias of the table is created.

Self Joins

A self join is a join from a table to itself. In Information Designer this is implemented by using a table alias.

ID

Name

Manager

1

Sarah

2

2

Mike

3

3

Carla

Null

4

Vincent

2

5

Adrian

3

 

The above database table lists a number of employees at a company, and specifies who the manager of each employee is. Carla is the head of the department. Mike and Adrian report directly to Carla, whereas Sarah and Vincent report to Mike.

In order to produce a list where the ID stated in the Manager column is replaced by a name (see below), we would need a self join in the table. The table needs to look up the ID column in itself.

Name

Manager

Sarah

Mike

Mike

Carla

Carla

 

Vincent

Mike

Adrian

Carla

 

This is done by first creating a Table Alias of the table, and then creating a join between the table and its alias:

WHERE TABLE.ID=TABLE_ALIAS1.MANAGER

See also:

Data Sources Overview

Joins Overview

Folders

Folders Overview 

Folders.png

Folders are used to organize information links and other elements such as filters, columns and joins in the library. The hierarchical structure of the folders can be seen in the Elements tab of Information Designer, and they have the same collapsible and expandable behavior as folders in any tree structure. The folder structure in the library is independent of the physical location of data, so it is possible to combine elements from several different data sources in one folder.

A folder may contain other folders. For example, a department may have a folder, within which each research group has its own subfolder. If you have administrative rights, you can control which users have access to specific folders. More

Folder permissions can be changed directly in the Information Designer by editing each folder, or by using the Library Administration tool. With TIBCO Spotfire 3.0 and forward it is also possible to place data sources within folders and, hence, control which users should have access to a certain data source through its folder permissions.

The export and import of folders and their content is handled by the Library Administration tool.

See also:

Fundamental Concepts

Creating a New Folder

Editing a Folder

Deleting a Folder

Creating a New Folder in Information Designer

To be able to save column and filter elements, you must first create a folder in the library. This can be done either in Information Designer or by using the Library Administration tool.

  • To create a new folder in Information Designer:

  1. Click New and select Folder.

    Response: The New Folder dialog is displayed.

  2. In the Name field, type the name of the new folder.

  3. In the Description field, type your own description of this folder (optional).

  4. If desired, you can add Keywords to help locate the folder when searching in the library.

  5. Click OK.

    Response: The folder is added to the library.
    Comment: If you want to change the permissions for the new folder, follow the instructions under Setting Folder Permissions.

See also:

Folders Overview

Setting Folder Permissions 

Access rights to different data sources, elements and information links in the library are specified on a folder level. If you have administrator rights, you can give users or groups which have been set up using the Administration Manager tool different levels of access to your folders. The available levels of permission are described here. The folder permissions can also be changed in the Library Administration tool.

  • To set permissions for a folder in Information Designer:

  1. Create a new folder or edit an existing one.

  2. In the Elements tree, right-click on the folder of interest.

  3. Select Folder Permissions... from the pop-up menu.

    Response: The Library Folder Permissions dialog is displayed.

  4. Select whether or not you want the folder to Inherit permissions from parent folder by selecting or clearing the check box.
    If you choose to inherit permissions then you cannot specify any more details. Click OK and you are done.
    If you choose to set explicit permissions then proceed to the next step.

    Response: When the check box is cleared, the fields displaying the available groups and the different levels of permission become available. The previously inherited permissions are shown to give you a template to start working from.

  5. Use the drop-down list below the search field to select whether to Search users, Search groups or Search users/groups.

  6. In the search field, type a search expression to display  users or groups of interest and click Search.
    Tip: You can use wild cards, or asterisks (*), to simplify the search. For example, use * to display all users and groups, or append it to a word to display all users and groups beginning with that word. Example: Group4* will find Group41, Group421 and so on.  

  7. Click to select the appropriate users or groups in the list and then click the > button of the permission level they should receive.

  8. Repeat steps 5 through 7 until you have added all the users and groups to the permission levels you want.

    Comment: To remove a user or group from a permission level, select it and click the < button for that permission level.

  9. Once you have selected the users or groups of interest, click OK.

    Response: The folder will be updated with the new permissions.

Note: What licenses are enabled for a certain user or group can also limit the functionality available for that user or group. See Description of the Licenses for more information.

See also:

Folders Overview

Editing a Folder

  • To change the name and description of a folder:

  1. In the Elements tree, right-click on the folder that you want to edit.

    Response: A pop-up menu is displayed.

  2. Select Edit Properties... from the pop-up menu.

    Response: The Edit Properties dialog is opened with the information for the selected folder.

  3. Modify the desired fields.

  4. Click OK.

Note: You can only edit the properties of folders where you have sufficient permissions.

Note: Some characters are forbidden in the element names and file system of TIBCO Spotfire 3.0 and forward: /\*"?:<>|

Old information models with element names containing these characters can be imported. However, you cannot edit and resave elements using their old names if they contain forbidden characters.

See also:

Setting Folder Permissions

Folders Overview

Deleting a Folder 

  • To delete a folder and all its contents:

  1. In the Elements tree, right-click on the folder that you want to delete.

    Response: A pop-up menu is displayed.

  2. Select Delete from the pop-up menu.

    Response: The folder and all its content are deleted from the library.

Note: You can only delete folders to which you have sufficient permissions.

See also:

 Folders Overview

Moving a Folder 

  • To move a folder and all its contents:

  1. In the Elements tree, drag the folder you wish to move and drop it on a different folder.

    Response: The folder and all its contents are moved to the new location.

See also:

Folders Overview

Joins

Overview 

Joins are used to define how rows in different tables relate to one another. Normally an identifier column in one table maps to a similar column in another table. This allows information links to draw values from two or more different tables. When column elements from two or more data tables are to be retrieved within the same information link you must always include a join path at the creation of the information link. Joins can also be set up between tables in different data sources.

In the Elements tree, joins are represented by this icon: Join.png

From a technical point of view, join elements can be placed anywhere in the tree structure. The Suggest Joins button on the Information Link tab helps you to find suitable joins for the currently used elements. However, you might want to place joins where they can easily be found. Just like filter and column elements, join elements are under permission control. This means that you must make sure that the folder where the joins are located is accessible to all users and groups that need to create their own information links.

See also:

Creating a Join

Freehand Joins

Editing a Join

Deleting a Join

Creating a Join in Information Designer 

If you want to combine columns from two disparate data sources, for example if one field is in a different table than the rest of the information, you need to create a join between two tables.

  • To create a join:

  1. Click New and select Join.

    Response: A Join Element tab is opened.

  2. In the Data Sources tree, select the first column to include in the join operation.

  3. Click Add >.

    Response: The selected column is added to the Source columns list on the Join Element tab.

  4. Select the second column to include in the join operation.

  5. Click Add >.

  6. Optionally, repeat steps 4 and 5 for any additional columns to be included in the join.

  7. Select a join operator from the Condition drop-down list, to specify how the two tables should be joined.

    Comment: There are two basic types of joins that can be done between tables: inner joins and outer joins. An inner join will return rows for which only the matching fields in both tables are equal. An outer join will return all the rows (including NULL values) from one table, and only the matching rows from the other table. If none of the default join options is suitable for your purposes, use the Freehand Join option to specify the join conditions manually.

  8. Select the Target tables using the two drop-down lists.

    Comment: This is necessary when more than two tables are involved in defining the join. See also Freehand joins.

  9. In the Description field, type your own description of the join.

  10. Sometimes you may want to create more than one join between the same two tables. In such a situation, you may want to make one of the joins a default join that most information links should use. The default join is displayed when clicking on the Suggested Joins button in the Information Link Join Path field. If you want the join you are currently creating to be the default one, select the check box Default join.

  11. Click Save or Save As... to specify the name and the folder to save the join in.

    Response: The join is added to the library and can be seen in the selected folder in the Elements tree.

See also:

Joins Overview

Freehand Joins in Information Designer

In many situations, a join can be defined using one of the operators in the Condition drop-down list. However, it may sometimes be desirable to use more complex conditions, such as:

  • %1 = 2 * %2 (mathematical calculation prior to comparison)

  • %1 = %3 AND %4 = %2 (an intermediate table used to set up the join)

In the latter case, it is important to select which tables to join - the tables in which %1 and %2 occur. The other columns included in the join condition, %3 and %4, are found in an intermediate table. No join is created between this table and the other two.

Example:

In the following tables, we want to create a join that links T1 and T3. In this way, we will be able to query the database for, say, the address of the person who earns 1400. To achieve this, T2 is used as intermediate table. The tables T1 and T3 should be selected from the Target tables' drop-down lists (see Creating a Join). The join, again, is defined as:

%1 = %3 AND %4 = %2

T1

Name (%1)

Salary

John

1000

Steve

1400

Lisa

1200

T2

Name (%3)

Number (%4)

John

1

Steve

2

Lisa

3

T3

Number (%2)

Address

1

Boston

2

Stockholm

3

Tokyo

See also:

Creating a Join

Joins Overview

Editing a Join in Information Designer

  • To edit a join:

  1. In the Elements tree, double-click on the join that you want to edit.

    Response: The join is displayed in a Join Element Tab.

  2. Modify the desired fields.

  3. Click Save or Save As....

Note: Some characters are forbidden in the element names and file system of TIBCO Spotfire 3.0 and forward: /\*"?:<>|

Old information models with element names containing these characters can be imported. However, you cannot edit and resave elements using their old names if they contain forbidden characters.

See also:

Joins Overview

Deleting a Join in Information Designer

  • To delete a join:

  1. In the Elements tree, right-click on the join that you want to delete.

    Response: A pop-up menu is displayed.

  2. Select Delete from the pop-up menu.

    Response: The join is deleted from the information model and its icon is removed from the Elements tree.

See also:

Joins Overview

Creating a Table Alias in Information Designer

You can create a duplicate reference to a database table from Information Designer. This duplicate is called a Table Alias, and references the very same data, only using an alias. This can be useful in certain cases, most often when in need of a self join within a table (below).

  • To create a table alias:

  1. Select a table on the Data Sources tab.

  2. Right-click the table and select Create Table Alias.

    CreatingaTableAliasinInformationDesigner1.png

  3. Type a name for the table alias and click OK.

    Response: A table alias of the table is created.

Self Joins

A self join is a join from a table to itself. In Information Designer this is implemented by using a table alias.

ID

Name

Manager

1

Sarah

2

2

Mike

3

3

Carla

Null

4

Vincent

2

5

Adrian

3

 

The above database table lists a number of employees at a company, and specifies who the manager of each employee is. Carla is the head of the department. Mike and Adrian report directly to Carla, whereas Sarah and Vincent report to Mike.

In order to produce a list where the ID stated in the Manager column is replaced by a name (see below), we would need a self join in the table. The table needs to look up the ID column in itself.

Name

Manager

Sarah

Mike

Mike

Carla

Carla

 

Vincent

Mike

Adrian

Carla

 

This is done by first creating a Table Alias of the table, and then creating a join between the table and its alias:

WHERE TABLE.ID=TABLE_ALIAS1.MANAGER

See also:

Data Sources Overview

Joins Overview

Column Elements

Overview 

Column elements in an information link may refer to multiple tables in different databases. However, with Information Designer, columns are represented as if they were located in the same spreadsheet, regardless of the physical location of the data.

In a TIBCO Spotfire context, columns are entities that can be assigned to the axes of a visualization. For example, data from a fruit company may include fruit type, sales and cost:

ColumnElementsOverview.png

All information in a relational database is represented explicitly as values in tables, composed of rows (records) and columns (fields).

Column elements created in Information Designer can be based on several database columns. For example, a column element can be calculated as the sum of the values in two different columns. The underlying columns can reside in the same database table, in different tables, or even on different databases.

Columns can be defined with built-in column filters that are automatically applied when the column is retrieved. You can also add a personalized or a parameterized column filter condition that limits data depending on the currently logged in user, see Personalized Information Links and Parameterized Information Links. Columns can also be set up to include aggregated data.

In the Information Designer, columns are represented by these icons:

RealColumn.png

Real, SingleReal or Currency column

IntegerColumn.png

Integer or LongInteger column

StringColumn.png

String column

BooleanColumn.png

Boolean column

DateColumn.png

Date column

DateTimeColumn.png

DateTime column

TimeColumn.png

Time column

BinaryLargeObject.png

BLOB (binary large object) column

CharacterLargeObject.png

CLOB (character large object) column

UnknownColumn.png

Unknown column (in the Data Sources tree only).
Must be set manually to one of the accepted column types (above) before it can be saved as a column element.

See also:

Creating a Column Element

Editing a Column

Deleting a Column

Data Types

Creating a Column Element in Information Designer

  • To add a column:

  1. Click New and select Column.

    Response: A Column Element tab is opened.

  2. In the Data Sources tree, select the column (leaf node) that you want to include.

  3. Click Add >.

    Response: The selected element is added to the Source columns list on the Column Element tab.

    Comment: Repeat this step if you need to include more data to calculate your column element.

  4. If you want the column to be calculated, type the expression in the Expression field.

    Comment: For instance, if you have added two numerical columns you could calculate the sum by entering "%1+%2". More

  5. Select the Data type of the column element.

    Comment: It is recommended that you use conversion functions for mapping columns. See notes below.

  6. In the Description field, type your own description of the column.

  7. In the Filter section, click Click.png to show the filtering controls. More

  8. In the Aggregation section, click Click.png to show the aggregation controls. More

  9. In the Properties section, click Click.png to show the column properties controls. More

  10. Click Save.

    Response: The column is saved in the library and is shown with an icon denoting its type in the Elements tree.

Note: If the column in the database is of the type Real, and you want to create a column element of the type Integer, then set the Data type (step 5 above) to Integer. Similarly, if the column in the database is of the type Unknown, you must manually choose an appropriate type before the column can be saved. It is also strongly recommended that you use a conversion function (for example ROUND) in the Expression field (step 4 above). Look for descriptions of available conversion functions in the manual for your database.

See also:

Column Elements Overview

Creating Multiple Column Elements in Information Designer

The Multiple Columns tab can be used if you want to create many column elements in one operation.

Note: Whole databases and schemas cannot be added, only columns and tables are available.

  • To add multiple columns to the information model:

  1. Click New and select Multiple Columns.

    Response: A Multiple Column Elements tab is opened.

  2. In the Data Sources tree, select the column (leaf node) or table that you want to include.

  3. Click Add >.

    Response: The selected column is added to the Source columns list. If a table was selected, all columns in the table will be added to the list on the Multiple Column Element tab.

    Comment: Repeat this step if you want to include more data columns.

  4. Click on an element in the Source columns list and change its name by typing in the Column element name field (or go to step 8 to add the columns using the default settings).

    Comment: This step is not necessary unless you want to change the name or description of the element.

  5. If desired, change the description of each column element.

    Comment: The description is metadata on the column which can be used in search expressions inside TIBCO Spotfire.

  6. Click Create Columns....

    Response: The Create Multiple Columns dialog is displayed.

  7. Click to select the folder where you wish to store the column elements.

  8. Click Create Columns.

    Response: The columns are stored in the specified folder in the library. Columns of type Unknown will be mapped to the data type String when they are added through the Multiple Columns tab. The data type can be changed later by editing each column element.

See also:

Editing a Column

Editing a Column in Information Designer

  • To edit a column:

  1. In the Elements tree, double-click on the column element you wish to edit. 

    Response: A Column Element tab is displayed.

  2. Modify the desired fields.

  3. Click Save to save/update the column, or click Save As... to create a copy.

    Comment: Use Save As... if you want to keep the old column definition unchanged.

Note: Some characters are forbidden in the element names and file system of TIBCO Spotfire 3.0 and forward: /\*"?:<>|

Old information models with element names containing these characters can be imported. However, you cannot edit and resave elements using their old names if they contain forbidden characters.

See also:

Column Elements Overview

Deleting a Column in Information Designer

  • To delete a column:

  1. In the Elements tree, right-click on the column element you wish to delete. 

    Response: A pop-up menu is displayed.

  2. Select Delete from the pop-up menu.

    Response: The column is deleted from the information model.

See also:

Column Elements Overview

Calculating a Column in Information Designer

  • To calculate a column:

  1. Start by adding at least one column from the Data Sources tree.

  2. If you want a column to be calculated, type an expression in the Expression field, for example %1+%2, or AVG(%1).

  3. Click on Click.png next to the respective section to access functions for aggregating data, or for creating a built-in filter.

  4. If desired, type a Description.

  5. Click Save or Save As....

See also:

Date and Time Columns

Column Elements Overview

Defining a Column Filter in Information Designer

Column filters are automatically applied when the column is retrieved. Such filters, unlike filters that are defined and saved as separate elements (filter elements), cannot be disabled by the user.

Column filters are defined while the column element is being created. The following steps should be taken in addition to what is described in Creating a Column Element.

  • To define a column filter:

  1. In the Filter section, click Click.png to show the filtering controls.

  2. In the Data Sources tree, select a column and then click the Add > button to add the column to use in the filter expression.

    Comment: Repeat this step if you need to include more columns in your filter expression.

  3. In the Expression field, define a filter condition based on the chosen column or columns. For example, %1<2000, or if you are using two columns, %1<%2.

    Comment: To combine conditions, use the AND or OR operators, for example %1>2000 AND %2<1000.

    Comment: You can also add a personalized filter expression that limits data depending on the currently logged in user. See Personalized Information Links for more information.
    Comment: You can also add a parameterized filter expression. See Parameterized Information Links for more information.

  4. Write a clear description of how the column is filtered. This is important, since the user will not be able to disable the filter when using this column element.

Note: When an information link is executed, aggregation is always applied before the filter.

See also:

Column Elements Overview

Date and Time Columns in Information Designer

A data source may provide date and time information as DATE columns. This format can be mapped to a DATE, a TIME or a DATETIME data type when a column element is created in Information Designer (see Creating a Column Element).

  • To find the number of days between two DATE columns:

  1. From the Data Sources tree, select two columns of type DATE.

  2. In the Expression field, type the expression %1 - %2.

    Comment: By selecting only one column, you can also find the elapsed number of days from today, using the expression sysdate - %1 if you are running against an Oracle data source.

  3. Set the Data type to Integer.

  4. To generate a Spotfire Date column from a DATE column:

  1. From the Data Sources tree, select a column of type DATE.

  2. Set the Data type to Date.

Note: Any SQL arithmetic expression may be entered into the Expression field. However, you need to make sure to use the correct syntax since no validation will be performed. Also note that different databases may have different syntaxes.

See also:

Column Elements Overview

Working with Aggregation
Using Aggregation in Information Designer

  • To calculate average values:

  1. Click New and select Column.

  2. Click to select a value column in the Data Sources tree, for example a Sales column.

  3. Click the topmost Add > button.

  4. In the Expression field, type:

    AVG(%1)
     

    Comment: AVG is the average function. You can find more aggregate functions here.

  5. Click Click.png next to Group By to show the controls determining over which column the aggregation will be made.

  6. Click New to add an empty group by expression.

  7. Click to select the column to group by in the Data Sources tree, for example a Type column.

  8. Click Add > in the Group By Settings section.

  9. Click Save.

    Response: The resulting column will consist of the average of the value column, for each value in the group by column. Using the example columns above it would be a column containing the average sales for each type of product.

Tip: Rather than hard coding the columns to aggregate over, you may consider marking the column as Drillable. The column will then be aggregated over any other columns retrieved in the same information link. More...

Note: When an information link is executed, aggregation is always applied before a column filter.

See also:

Aggregating Over Many Columns

Aggregate Functions

Using Drillable

Column Elements Overview

Aggregating Over Many Columns in Information Designer

There may be situations where it is necessary to use more than one column to group by when an aggregated column is calculated. In the following table, for example, we can compare the salary of each employee with the average salary of employees at the same department and job.

AggregatingOverManyColumnsinInformationDesigner.png

To produce this table, we must define the Average Salary column with two group by expressions: Job and Department.

  • To use multiple group by expressions:

  1. Select a value column from the Data Sources tree, for example Salary.

  2. Click the topmost Add > button.

  3. In the Expression field, type:

    AVG(%1)
     

    Comment: AVG is the average function. You can find more aggregate functions here.

  4. In the Group By section, click Click.png to show the group by controls.

  5. Click New to add a new Group by expression.

  6. From Data Sources, select the first column to group by (for example Job).

  7. Click Add > in the Settings field.

  8. Click New to add a second Group by expression.

  9. Select the second column to group by (for example Department).

  10. Click Add >.

  11. Click Save.

    Response: The resulting column will consist of the average of the value column, for each value in the group by columns.

Note: It would not have been possible to produce the table above using the Drillable option. If the Average Salary column element had been drillable, it would have appeared identical to the Salary column, since the Employee Name and Salary columns would also have been used to group by.

See also:

Using Aggregation

Aggregate Functions

Using Drillable

Using Drillable 

When creating an aggregated column, it is possible to specify group by expressions determining over which categories the aggregation should be made. This is done from the Column Element tab, in the Group By section.

It is also possible to ignore the Group By settings, by selecting the Drillable check box. This way the column will always be aggregated over all other columns being retrieved in the same information link. The following table illustrates this. Average Salary Drillable is calculated using the AVG function. No group by expressions have been specified, and instead the Drillable check box has been selected.

UsingDrillable1.png

As we see above, the average salary is computed for each value in Job. We could just as well have defined a salary column explicitly aggregated over Job. However, by using the Drillable option, we can now retrieve a different set of columns and still get a useful answer:

UsingDrillable2.png

In this table, the Average Salary Drillable column displays the average salary for the clerks from each department instead of a combined value for all clerks.

See also:

Using Aggregation

Aggregating Over Many Columns

Aggregate Functions

Column Elements Overview

Filter Elements

Overview 

Filters are conditions that limit the amount of data returned by an information link. For example, a filter could set the conditions "X > 10 AND Y < 100". There are three ways of filtering data when working with information links:

FilterElementsOverview.png

 

 

Filter elements appear in the Elements tree of Information Designer. They can be added to any information link, just like column elements. See Creating an information link to find out how to include a filter element.

Hard filters are set up as you define the information link, and are saved with the link. This means that they cannot be reused in other links. Also, hard filters can only specify a range or a list of values, and so are less versatile than filter elements. See Adding Hard Filters for more details.

Prompts are also set up when you create an information link. However, the actual conditions are entered by the person running the information link only when the link is opened (executed). For each column that has been set up like this, a dialog will appear allowing the user to enter threshold values or select individual values. See Adding Prompts for more details.

Filters correspond to the WHERE clauses in SQL, and are used to specify that only certain rows of a table shall be retrieved from the data source, based on the criteria described in the filtering condition.

Filter elements can be applied as required by the person creating information links. In the Elements tree, they are represented by this icon: Filter.png

See also:

Creating a Filter Element

Editing a Filter

Deleting a Filter

Relational Operators

Personalized Information Links

Parameterized Information Links

Creating a Filter Element 

This topic describes how to create filters as separate elements. These can be applied as required by the person creating information links. See Defining a Column Filter for information on how to add a filter as part of a column definition.

  • To create a filter:

  1. Click New and select Filter.

    Response: A Filter Element tab is opened.

  2. In the Data Sources tree, select the column to use in the filter condition.

  3. Click Add >.

    Response: The selected column is added to the Source columns list on the Filter Element tab.

    Comment: Repeat this step if you need to include more columns in your filter expression.

  4. Define a filter expression with the chosen columns, for example %1>=2000, or if you are using two columns, %1<%2. See Relational Operators for more information on defining expressions.

    Comment: You can also add a personalized filter expression that limits data depending on the currently logged in user. See Personalized Information Links for more information.
    Comment: You can also add a parameterized filter expression. See Parameterized Information Links for more information.

  5. In the Description field, type your own description of the filter.

  6. Click Save.

    Response: The Save As dialog is displayed.

  7. Click to select the folder where you wish to save the filter element.

  8. Click Save.

    Response: The filter element is saved in the library.

See also:

Filter Elements Overview

Editing a Filter in Information Designer

  • To edit a filter element:

  1. In the Elements tree, double-click on the filter that you want to edit.

    Response: The name of the filter and filter conditions are shown on a Filter Element tab and enabled for editing.

  2. Modify the desired fields.

  3. Click Save or Save As....

Note: Some characters are forbidden in the element names and file system of TIBCO Spotfire 3.0 and forward: /\*"?:<>|

Old information models with element names containing these characters can be imported. However, you cannot edit and resave elements using their old names if they contain forbidden characters.

See also:

Filter Elements Overview

Deleting a Filter in Information Designer

  • To delete a filter:

  1. In the Elements tree, right-click on the filter that you want to delete.

    Response: A pop-up menu is displayed.

  2. Select Delete from the pop-up menu.

    Response: The filter is deleted from the library.

See also:

Filter Elements Overview

Relational Operators

The following operators can be used in filters:

=

Equal

<> or !=

Not Equal

<

Less Than

>

Greater Than

<=

Less Than or Equal To

>=

Greater Than or Equal To

  • Generally, with text columns, it is best to use = or !=.

  • Make sure that any text that appears in the statement is surrounded by single quotes (').

  • To combine multiple conditions, use the AND or OR operators, for example %1>2000 AND %2<1000.

See also:

Filter Elements Overview

Procedures

Overview

Database Procedures

A database procedure is a set of SQL statements that can be stored in the database. Once this has been done, clients do not need to keep reissuing the individual statements but can refer to the database procedure instead.

Database procedures can be useful in many situations:

  • When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.

  • Some institutes, like banks, where security is important, use database procedures for all common operations. This provides a consistent and secure environment, and procedures can ensure that each operation is properly logged. In such a setup, applications and users would not get any access to the database tables directly, but can only execute specific database procedures.

  • Database procedures can provide improved performance because less information needs to be sent between the server and the client. However, this increases the load on the database server system because more of the work is done on the server side and less is done on the client (application) side. Take this into consideration if many client machines (such as Web servers) are serviced by only one or a few database servers.

  • Database procedures also allow you to have libraries of functions in the database server. This is a feature shared by modern application languages that allow such design internally, for example, by using classes.

Database Procedures in Information Designer

Using Information Designer you select pre-made database procedures and configure these to be accessible in information links. These information links are available to the TIBCO Spotfire users to retrieve or manipulate data.

In Information Designer you select a database procedure from your available data sources, and define which input parameters that the procedure should prompt for, and any potential resulting columns and joins.

Then you configure a complete information link with one or more combinations of procedures and columns from other tables.

The Three Kinds of Procedures

Information Designer defines three kinds of procedures:

  • Pre-update procedure - this procedure does not return any data, it only performs an operation on one or more databases. All pre-update procedures in an information link will always be executed before any query procedure.

  • Query procedure - just like a database table, this procedure returns data.

  • Post-update procedure - this procedure does not return any data, it only performs an operation on one or more databases. All post-update procedures in an information link will always be executed after any query procedure.

Note: If using Oracle stored procedures that return data, Information Designer only support procedures that return data of the type REF CURSOR (also known as Table Function).

See also:

Creating a Pre- or Post-update Procedure

Creating a Query Procedure

Procedure Element Tab

Creating a Pre- or Post-procedure

  • To Create a Pre-update or Post-update procedure:

  1. Click New and select Procedure.

    Response: A Procedure Element tab is opened.

  2. In the Data Sources tree, click on the database procedure you wish to use.

  3. Click Select.

    Response: The database procedure is added to the tab. Information Designer analyzes the database procedure and makes a guess as to whether it is a pre-update procedure or a query procedure (it never sets post-procedure by default).

    CreatingaPre-orPost-procedure1.png

  4. Select the Procedure type: Pre-update procedure or Post-update procedure.

    Comment: Pre-update procedures are always executed first in an information link. Post-update procedures are always executed last in an information link.

  5. If the database procedure requires any Input Parameters, their name and type are displayed.
    CreatingaPre-orPost-procedure2.png

  6. Select whether the input parameter should receive a Default Value by typing a value (of the appropriate type) in the input field. If not, leave the field blank.

    Comment: Type ?param_name to use a parameter as the default value, where param_name is the name to identify the parameter by. Note: If you use a parameter for the default value, you must choose Prompt: None. See Parameterized Information Links for more information.

  7. Select whether you want the end user to be prompted for a single value, multiple values, or not at all, from the Prompt drop-down list.

    Comment: If you select multiple values, the end user will be allowed to enter several values. The procedure will run once for each of these values in an iterative loop. For more information, see Multiple Value Procedure Prompts.

  8. If you want to allow the input parameter to be Null, select the Permit Null check box.

    Example 1: Type a Default value and set prompt to None. This means that the specified default value will always be used as input parameter.
    Example 2: Leave Default value empty, select Permit Null and Single value prompt. This will cause the end user to be prompted for a value. If the end user does not enter a value in the prompt, Null will be used.
    Example 3: Leave Default value empty, leave Permit Null empty and Single value prompt. This will cause the end user to be prompted for a value. If the end user does not enter a value in the prompt, an error message will appear since Null is not allowed.
    Comment: You cannot select the combination: No Default value, Not allow Permit Null and No Prompt, since this is a paradox.

  9. In the Description field, type your own description of the procedure.

  10. Click Save.

    Response: The Save As dialog is displayed.

  11. Click to select the folder where you wish to save the procedure element.

  12. Click Save. NEED IMAGES

    Response: The procedure is saved in the selected folder, and will be denoted with the Procedure.png icon. Note that the procedure object will be visible to the end user in the list of information links. You can also make larger, more complex information links using the procedure when creating an information link in Information Designer.

See also:

Procedures Overview.

Creating a Query Procedure

  • To Create a Query procedure:

    1. Click New and select Procedure.

      Response: A Procedure Element tab is opened.

    2. In the Data Sources tree, click on the database procedure you wish to use.

    3. Click Select.

      Response: The database procedure is added to the tab. Information Designer analyzes the database procedure and makes a guess as to whether it is a pre-update procedure or a query procedure (it never sets post-procedure by default).

      CreatingaQueryProcedure1.png

    4. Select the Procedure type: Query.

    5. In the Description field, type your own description of the procedure.

    6. If the database procedure requires any Input Parameters, their name and type are displayed.
      CreatingaQueryProcedure2.png

    7. Select whether the input parameter should receive a Default Value by typing a value (of the appropriate type) in the input field. If not, leave the field blank.

      Comment: Type ?param_name to use a parameter as the default value, where param_name is the name to identify the parameter by. Note: If you use a parameter for the default value, you must choose Prompt: None. See Parameterized Information Links for more information.

    8. Select whether you want the end user to be prompted for a single value or not at all, from the Prompt drop-down list.

    9. If you want to allow the input parameter to be Null, select the Permit Null check box.

      Example 1: Type a Default value and set prompt to None. This means that the specified default value will always be used as input parameter.
      Example 2: Leave Default value empty, select Permit Null and Single value prompt. This will cause the end user to be prompted for a value. If the end user does not enter a value in the prompt, Null will be used.
      Example 3: Leave Default value empty, leave Permit Null empty and Single value prompt. This will cause the end user to be prompted for a value. If the end user does not enter a value in the prompt, an error message will appear since Null is not allowed.
      Comment: You cannot select the combination: No Default value, Not allow Permit Null and No Prompt, since this is a paradox.

    10. Expand the Result Columns section.

    11. Either keep the Include all result columns check box selected, or clear the check box and click Add... to specify your own result columns. If the check box is selected, go to step 15, otherwise continue with step 12.

    12. In the Original name field, enter the exact name of a column the database procedure returns. This name is specified in the actual database procedure, so you have to know this beforehand.

    13. Type a Display name to use when the result column is opened in TIBCO Spotfire.

    14. Select the Data type the resulting column should have.

    15. If you want to specify a join between the database procedure and another table, use the Join section.
      Comment: Joining is necessary if you intend to use the result columns from this procedure together with column elements from another data table in an information link. You can only specify one join.

    16. Join columns can be added from three different sources:
      * From the Data Sources tree. This option is similar to selecting join columns at the creation of join elements. Click on a column in the Data Sources tree (or click on the column under its data source in the Elements tree) and then select the From Data Sources Tree option in the Add drop-down list.
      * From a previously specified Result Column. Any result columns that have been defined will be listed directly in the Add drop-down list.
      * From a New Result Column. The third option is used if you want to join over a result column from the procedure but you do not want to use it as output when retrieving data.
      Click Add and select an option from the drop-down list.

    17. Repeat step 16 for another join column. Normally, you will have one column from the data sources tree and one result column in the join.

    18. Select a Condition from the drop-down list.
      Comment: There are two basic types of joins that can be done between tables: inner joins and outer joins. An inner join will return rows for which only the matching fields in both tables are equal. An outer join will return all the rows (including NULL values) from one table, and only the matching rows from the other table.  See also Freehand Joins.

    19. Click Save.

      Response: The Save As dialog is displayed.

    20. Click to select the folder where you wish to save the procedure element.

    21. Click Save.

      Response: The procedure is saved in the selected folder, and will be denoted with the Procedure.png icon. Note that the procedure object will be visible to the end user in the list of information links. You can also make larger, more complex information links using the procedure when creating an information link in Information Designer.

See also:

Procedures Overview.

Editing a Procedure

  • To edit a procedure:

  1. In the Elements tree, double-click on the procedure that you want to edit.

    Response: The procedure is loaded on a Procedure Elements tab and enabled for editing.

  2. Modify the desired fields.

  3. Click Save or Save As....

Note: Some characters are forbidden in the element names and file system of TIBCO Spotfire 3.0 and forward: /\*"?:<>|

Old information models with element names containing these characters can be imported. However, you cannot edit and resave elements using their old names if they contain forbidden characters.

See also:

Procedures Overview.

Deleting a Procedure

  • To delete a procedure:

  1. In the Elements tree, right-click on the procedure that you want to delete.

    Response: A pop-up menu is displayed.

  2. Select Delete from the pop-up menu.

    Response: The procedure is deleted from the library.

See also:

Procedures Overview

Multiple Value Procedure Prompts

If a list is used as input for a pre- or post-update procedure, the procedure is run as many times as the number of values in the list. If lists of different lengths are used at the same time, one of two things will happen depending on the length of the lists.

 

Example 1:

If one of the lists contains only one value, that value will be distributed to the values of the other list.

 

List X: [1,2,3]

List Y: [A       ]

 

Result: First call procedure with parameters: 1,A

Then: 2,A

Then: 3,A

 

Example 2:

If both lists contain more than one value, the values of the shorter list will be distributed to the values of the longer list, and null will be added to the remaining values.

 

List X: [1,2,3]

List Y: [A, B  ]

 

Result: 1,A

2,B

3,null

 

A query procedure does not accept a list as input, only a single value.

See also:

Creating a Pre- or Post-procedure

User Interface Details

Elements Tree

The Elements tree displays the data access layer (the information model) in the library as a folder structure including all available information links, column elements, filter elements and joins. All elements can be sorted into different folders. Click the plus (+) and minus (-) next to a folder icon to expand the folder and browse the tree. Click on the desired element to select it. It is possible to drag an element from one place to another. Right-clicking on an element opens a pop-up menu where you can edit the selected element, validate information links, etc.

Use the Search field to locate folders or elements in the tree. The search field automatically searches for the name and keywords of the elements, but you can also search for other properties using the following syntax: <property name>:<value>. See Searching in TIBCO Spotfire and Searching the Library for more information regarding search. Click on Clear Search to return to the full Elements tree.

Note: Searching for data sources does not include searching for database entities like catalogs, schemas or tables. It is only the database instance itself that can be located via search.

ElementsTree.png

Tip: You can also press the * key on the numeric keypad to expand all nodes at the highest unexpanded level below the selected folder.

See also:

Icon Explanations

Icon Explanations 

In the Elements tree, the following icons may appear. Click on a link in the table below to find out more about each element type. In the Data sources tree only the items belonging to a database are visible.

 

Icon

Element type

Folder.png

Folder

InformationLink.png

Information link

Filter.png

Filter

BooleanColumn.png

Boolean column

StringColumn.png

String column

IntegerColumn.png

Integer column or LongInteger column

RealColumn.png

Real, ShortReal or Currency column

DateColumn.png

Date column

DateTimeColumn.png

DateTime column

TimeColumn.png

Time column

Procedure.png

Procedure

BinaryLargeObject.png

Binary Large Object

CharacterLargeObject.png

Character Large Object

Join.png

Join

DatabaseInstance.png

Database instance.

DatabaseLink.png

Database link/catalog - a link to another database.

Schema.png

Schema - a collection of tables and/or procedures.

TableIcon.png

Table - a set of columns.

TableAlias.png

Table alias. You can create a duplicate reference to a database table from Information Designer. This duplicate is called a Table Alias.

UnknownColumn.png

Unknown column.
Must be set manually to one of the accepted column types (above) before it can be saved as a column element.

See also:

Elements Tree

Data Sources Tree

Data Types

Creating an Information Link

Fundamental Concepts

Data Sources Tree

The data sources tree lists all currently defined data sources and their content. Click the plus (+) and minus (-) next to a data source icon to expand/collapse the data source. Click on the desired element to select it. Right-clicking on an item opens a pop-up menu where you can edit the selected data source, create multiple column elements etc.

Tip: You can also press the * key on the numeric keypad to expand all nodes at the highest unexpanded level below the selected node.

Use the Search field to search for the name, description and keywords of a database. Click on Clear Search to return to the full Data Sources tree.

Note: Searching for data sources does not include searching for database entities like catalogs, schemas or tables. It is only the database instance itself that can be located via search.

DataSourceTree.png

Icon

Item

Description

DatabaseInstance.png

Database instance

The name of the database instance.

DatabaseLink.png

Database link / Catalog

A link to another database.

Schema.png

Schema

The owner or database administrator that has set up the different tables.

TableIcon.png

Table

A set of columns.

StringColumn.png, ...

Column

The column has an icon that denotes the column type. See Column Elements Overview for a description of the different column icons.

TableAlias.png

Table Alias

You can create a duplicate reference to a database table from Information Designer. This duplicate is called a table alias.

See also:

Creating a Data Source

Tab
Start Tab

When Information Designer is started, the Start tab is visible. From here, you can reach all functions of the Information Designer. Each link in the start tab opens a separate tab where you can perform the various tasks.

Tip: Close a tab by clicking on the x symbol on the top right hand side of the tab page. You can also click on the tab with the middle mouse button or mouse wheel in order to close it.

Tip: You can right-click on any tab in Information Designer to display a pop-up menu which allows you to navigate to any of your current tabs or to close all tabs simultaneously. You can also locate the currently edited element in the Elements tree.

Option

Description

Create Information Link

Opens an Information Link tab. You can have multiple information link tabs open at the same time.

Create Elements

Column - Opens a Column Element tab where you can define a column element which shall be a part of the information model and, hence, available when creating information links.

Multiple Columns - Opens a Multiple Column Elements tab where many column elements can be defined simultaneously.

Filter - Opens a Filter Element tab where a column filter can be defined.

Procedure - Opens a Procedure Element tab where a stored procedure can be defined.

Create Join

Opens a Join Element tab where you can define a join between columns from different tables.

Setup Data Source

Opens a Data Source tab where you can specify a data source that should be available for creating elements.

StartTab.png

See also:

Elements Tree

Icon Explanations

Data Sources Tree

Information Link Tab

InformationLinkTab1.png

Option

Description

Add >

Adds the element selected in the Elements tree to the information link.

< Remove

Removes the selected element from the link.

Elements

Lists all elements currently included in the information link. Clear the Retrieve check box to prevent a column from being loaded. (Filter conditions will still affect the amount of data loaded.)

Move Up

Moves the selected element up in the list.

Move Down

Moves the selected element down in the list.

Edit

Opens the Column Element tab for the selected element so that it can be edited.

SQL...

Opens the Edit SQL dialog where you can view and edit the SQL that the current information link is generating.

Save As...

Displays the Save As dialog which saves the configuration currently shown in the information link tab into a new information link.

Save

Saves the information link.

Open Data

Executes the currently selected data as an information link and retrieves the data into Spotfire.

 

InformationLinkTab2.png

Option

Description

Add >

Adds the selected join from the Elements tree to the Selected joins list.

< Remove

Removes the selected join from the Selected joins list.

Selected joins

Lists the joins that will be used in the join path between the tables in the information link.

Suggest Joins

If any joins have been specified between the tables currently used in the information link, clicking this button will automatically add one or more suitable joins to the Selected joins list. If a default join has been specified, then this join will be used if possible.

Join data source

If more than one join database have been defined during the setup of the server, then you can select where the joining should take place here.

 

InformationLinkTab3.png

Option

Description

Description

An optional description of the contents of the information link. This can be helpful for end users of the information link when searching for information links in the library.

 

InformationLinkTab4.png

Option

Description

Column

The names of added columns to filter on.

Filter Type

Select Range or Values to specify how the filtering conditions will be set.

Values

List the required values (separated by semicolons) for a values filter.

Type ?param_name to use a parameter as the filter for the chosen column, where param_name is the name to identify the parameter by.

Min Value

Type the lower range value for a range filter in this field.

Max Value

Type the higher range value for a range filter in this field.

Add

Opens the Add Column dialog where you can select a column to filter on.

Remove

Removes the selected column from the Filters section.

 

InformationLinkTab5.png

Option

Description

Column

The names of added columns to prompt.

Prompt Type

Select what kind of selections should be available in the prompt when the link is opened.

Values lets you enter your own list of values to include. This prompt type should only be used if all end users of the information link know which values are valid for the column.

Range lets you specify a range of values. This prompt type is suitable for numerical or Date/DateTime/Time columns when a sustained range of numbers or time periods are to be retrieved.

Multiple selection presents a list of available values from which you can select multiple values. This type of prompt can be used even if the end users of the information link have no previous knowledge of the data.

Single selection presents a list of available values from which you can select a single value only. This type of prompt can be used even if the end users of the information link have no previous knowledge of the data.

See also Details on Open Information Link.

Mandatory

Select this check box to make it mandatory to select values in the prompt. It is not possible to clear the check box if Single selection has been chosen.

For non-mandatory prompts the end user can leave the prompt step dialog untouched in order to retrieve all data for that step.

Max Selections

Type the maximum number of selections allowed. It is not possible to specify a maximum number of selections if Single selection or Range has been chosen.

Add

Opens the Add Column dialog where you can select a column on which to add a prompt.

Remove

Removes the selected column from the Prompts section.

Move Up

Click to move an element up. This is used to control the order of the columns with prompts. Filter elements are always applied before prompts regardless of order.

Move Down

Click to move an element down.

Groups...

Opens the Prompt Groups dialog where you can specify different prompt groups for different prompts, making elements independent from each other. This is used to improve the performance of information links when retrieving data from STAR schema databases. See Using Prompt Groups for more information.

 

InformationLinkTab6.png

Option

Description

None

No conditioning.

Distinct

Removes all duplicate rows (rows where all fields are identical) from the returned data table.

 Pivot

Allows you to transform your data from a tall/skinny format to a short/wide format by rotating row and column headings around the core data. Select which columns to work on by clicking Edit....

Edit...

Opens the Pivot Conditioning dialog.

 

InformationLinkTab7.png

Option

Description

Parameter Name

Lists the names of all added parameters. Note: You have to click Refresh for parameters to appear in the list the first time you display the list after creating a parameter.

If a parameter is not listed even after clicking Refresh, you may have created a parameter containing unsupported characters. See Parameterized Information Links for more information. It is also possible that you have edited the SQL of the information link. In that case, the altered SQL code overrides the parameters settings in the user interface.

Data Type

Lists the data type of all added parameters.

Value Type

Lists the value type of all added parameters.

Edit...

Opens the Edit Parameter dialog which lets you edit the selected parameter in the list.

Refresh

Refreshes the list of parameters. Note: You have to click Refresh for parameters to appear in the list the first time you display the list after creating a parameter.

 

InformationLinkTab8.png

Option

Description

Property name

Shows the names of custom properties defined for this information link. Custom properties are metadata that can be used when searching in the Library by using the following syntax: <Property name>:<Value>. For example, MyCompany.Property:Property1.

Value

Shows the value of each custom property.

Add...

Opens the Add Information Link Property dialog where custom properties can be defined.

Edit...

Opens the Edit Information Link Property dialog.

Delete

Deletes the selected property.

See also:

Creating an Information Link

Elements Tree

Parameterized Information Links

Column Element Tab

ColumnElementTab1.png

Option

Description

Add >

Select a column from the Data Sources tree and click on this button to include it in the new column.

< Remove

Deletes the selected column from the composition.

Source columns

Lists all source columns that will be included in the calculation of the new column element.

Expression

To perform any calculation on the column, enter the expression in this text field.

Data type

Specifies the data type of the column.

 

ColumnElementTab2.png

Option

Description

Description

An optional description of the column element.

 

 

ColumnElementTab3.png

Option

Description

Add >

Select a column from the Data Sources tree and click on this button to include it in the filter.

< Remove

Removes the selected column from the composition.

Expression

In this field, enter an expression containing the selected column or columns.

 

ColumnElementTab4.png

The Group by section is only important if you have specified some type of aggregation in the Expression field at the top of the page. When an aggregation has been defined, you can select the categories to group by here. See Using Aggregation in Information Designer for more information.

Option

Description

Group by expressions

Lists the group by expressions specified using the source columns selected under Settings and any calculations done in the Expression field.

New

Adds a new, empty expression to the Group by expressions list.

Delete

Deletes the selected expression from the Group by expressions list.

Add >

Adds the columns selected in the Data Sources tree to the Source columns list, where it can be used in a group by expression.

< Remove

Deletes the selected column from the Source columns list.

Source columns

Lists the columns that are to be used in the expression determining what to group by.

Expression

Enter an expression here to define what  to group by.

Drillable

Select the check box if you want the column to group by all other columns that are included together with this column element in an information link. See Using Drillable for more information.

 

ColumnElementTab5.png

Option

Description

Property name

Shows the names of custom properties defined for this column. Custom properties are metadata that can be used when searching for columns using the following search syntax: <Property name>:<Value>. For example, MyCompany.Property:Property1.

Some types of data require specific properties and values to be treated correctly by Spotfire. An example is map columns which require the property mapchart.columntypeid, with the Value Geometry, XMax, XMin, YMax, YMin, XCenter or YCenter set on the different kinds of columns used when viewing WKB data in a map chart. See Configuration of Geographical Data for Map Charts for more information.

Another example is chemical structure data, which requires the ContentType property to be set to chemical/x-mdl-molfile for the structure column and a "LeadDiscovery.Column" property with particular values defined for ID columns and search columns. See the TIBCO Spotfire Lead Discovery Installation Manual for details.

Custom column properties can also be used for drawing lines in some of the visualizations.

Value

Shows the value of each custom property.

Add...

Opens the Add Column Property dialog where custom properties can be defined.

Edit...

Opens the Edit Column Property dialog.

Delete

Deletes the selected property.

See also:

Creating a Column Element

Data Sources Tree

Multiple Column Elements Tab

MultipleColumnElementsTab.png

Option

Description

Add >

Select one or more tables or columns from the Data Sources tree, and then click this button to include the columns as new column elements in the library.

< Remove

Deletes the selected columns from the Source columns list.

< Remove All

Removes all columns from the Source columns list.

Column element name

The name of the column to be saved as it will be displayed in the Elements tree once imported to the library. Click on a column element name to edit the text.

Source column name

Displays the source name of the selected column element.

Type

Displays the data type of the selected column element.

Path

Displays the path to the column in the data sources tree.

Selected column description

A short (optional) description of the purpose of the column. (Click on a column element in the Source columns list to edit the description of that column.)

Create Columns...

Creates the columns and adds them to the library, after you specify their location in the Create Multiple Columns dialog.

See also:

Creating Multiple Column Elements

Data Sources Tree

Filter Element Tab

FilterElementTab.png

Option

Description

Add >

Select a column from the Data Sources tree, and then click this button to include it in the filter.

< Remove

Deletes the selected column from the composition.

Condition

In this field, enter a condition containing the selected column or columns.

Description

A short (optional) description of the purpose of the filter.

Save As

Click Save As to create a copy of the filter element.

Save
(

Click Save to save or update the filter.

See also:

Filters Overview

Creating a Filter Element

Editing a Filter

Deleting a Filter

Procedure Element Tab

ProcedureElementTab1.png

Option

Description

Select

Select a database procedure from the Data Sources tree, and then click this button to include it.

Procedure type

Information Designer defines three kinds of procedures:

Pre-update procedure - this procedure does not return any data, it only performs an operation on one or more databases. All pre-update procedures in an information link will always be executed before any query procedure.

Query procedure - just like a database table this procedure returns data.

Post-update procedure - this procedure does not return any data, it only performs an operation on one or more databases. All post-update procedures in an information link will always be executed after any query procedure.

Save As...

Click Save As to create a copy of a procedure.

Save

Click Save to save or update the procedure.

Run

Runs the procedure.

ProcedureElementTab2.png

Option

Description

Description

A short description of the purpose of the procedure.

 

ProcedureElementTab3.png

Option

Description

Name

States the name of the input parameters detected in the database procedure.

Type

States the type of the input parameters detected in the database procedure.

Default Value

If the input parameter should receive a default value, type a value (of the appropriate type) in the input field. If not, leave the field blank.

Type ?param_name to use a parameter as the default value, where param_name is the name to identify the parameter by.

Note: If you use a parameter for the default value, you must choose Prompt: None.

Permit Null

Select this check box if you want to allow the input parameter to be Null.

Prompt

Select whether you want the end user to be prompted for a single value, multiple values or not at all, from the Prompt drop-down list box.

Comment: If you select multiple values, the end user will be allowed to enter several values. The procedure will run once for each of these values in an iterative loop. For more information, see Multiple Value Procedure Prompts.

 

ProcedureElementTab4.png

Option

Description

Include all result columns

Select this check box to include all columns from the procedure. Clear the check box if you like to specify the result columns yourself.

Add

This button becomes available when the Include all result columns check box is cleared. Click on the button to add a new row in the result columns list on the right. A dialog is opened where you can enter the Original name and Display name of the result column, as well as specify its data type.

Delete

Deletes the selected result column.

Original Name

In the Original Name field, the exact name of a column the database procedure returns should be entered. This name is specified in the actual database procedure, so you have to know this before adding the result column.

Display Name

Enter a descriptive Display Name for the resulting column. This is the name the end user will see when columns have been imported to TIBCO Spotfire.

Type

Select the Type the resulting column should have.

 

ProcedureElementTab5.png

 

Option

Description

Add

Displays a drop-down list containing columns from three different sources:

* From Data Sources Tree. This option is similar to selecting join columns at the creation of join elements. Click on a column in the Data Sources tree (or click on the column under its data source in the Elements tree) and then select the From Data Sources Tree option in the Add drop-down list.
* Previously specified Result Columns. Any result columns that have been defined above will be listed directly in the Add drop-down list.
* New Result Column.... The third option is used if you want to join over a result column from the procedure but you do not want to use it as output when retrieving data.
Select a column from either source to include it in the join.

Remove

Removes a column from the Join columns list.

Join columns

Lists the name of the columns you wish to join.

This may be one of the columns specified in the Result Columns section, but can also be another procedure column as long as it is available from the database procedure result (for example an ID column). It also lists the columns you have added from the Data Sources tree.

Condition

Select one of the alternatives to specify which type of join to use.

An inner join will return rows for which only the matching fields in both tables are equal. An outer join will return all the rows (including NULL values) from one table, and only the matching rows from the other table.

Freehand

You can also specify your own join definition by typing directly in the freehand field. See Freehand Joins for more information.

See also:

Procedures Overview

Creating a Pre- or Post-update Procedure

Creating a Query Procedure

Join Element Tab 

JoinElementTab.png

Option

Description

Add >

Select a column from the Data Sources tree, and then click this button to include it in the join.

< Remove

Removes a column from the list of columns included in the join.

Join columns

Lists the columns that should be included in the join.

Condition

Select one of the alternatives to specify which type of join to use.

An inner join will return rows for which only the matching fields in both tables are equal. An outer join will return all the rows (including NULL values) from one table, and only the matching rows from the other table.

Freehand

Select this option to specify your own join definition.

Target tables

Select one table from each drop-down list. If you are only using two columns in the join, then you cannot alter the default choices. If more than two columns are included in the join condition (for example, an intermediate table), then it is important to select the two tables that are to be joined.

Default join

Select this check box if you want this join to be the default one if two different joins between the same tables exist. The default join is the one that is suggested in the Join path field when defining an information link, if the Suggest Joins button is clicked.

Description

A free-text description of the join.

Save As

Click Save As to create a copy of the join element.

Save

Click Save to save or update the join.

See also:

Joins Overview

Creating a Join

Data Source Tab 

All data sources that have been defined and you have access to are listed in the Data Sources tree and also in the Elements tree. By placing a data source in a folder, you can specify which users will have permission to access the data therein. Right-click on a data source and select Edit from the pop-up menu to modify a previously added data source.

DataSourceTab.png

 

Option

Description

Name

The name of the data source, as you want it to appear in the Data Sources tree and in the Elements tree.

Type

Type of database. For example, choose from Oracle and SQL Server through JTDS or DataDirect. If more databases have been set up, they will appear in this list. See TIBCO Spotfire Server – Installation and Configuration Manual for details on setting up connections to other databases.

Connection URL

URL of the database. The format of this URL depends on the type of database. Change the placeholders in the default URL so that it links to your selected database.

No of connections

Min is the minimum number of database connections created for a given data source.

Max is the maximum number of database connections created for a given data source. There can never be more connections open at a given moment than the specified max number.

Note: If you use SAS/SHARE, ODBC or other data sources that do not support pooled connections (i.e., there is no valid ping command), set both min and max to 0.

Username

Username for the data source.

Password

Password for the data source.

User authentication

Use individual usernames to authenticate users when running information links. By default, this will prompt the user for credentials when running the information link against this data source for the first time. Optionally, you can develop a custom plug-in and use it to retrieve the credentials.

Note: If you are using Kerberos Delegation for database connections be sure to select this check box.

Note: When using a data source with user authentication the database administrator needs to make sure that all users has sufficient privileges to create, truncate and drop the temporary tables in the data source.

Credentials timeout (hours)

The number of hours to save credentials so that an end user does not have to login again when making multiple connections to the same data source. If no number is specified, the credentials will need to be specified again after 24 hours.

Data source credentials caching is done on both the TIBCO Spotfire client as well as on the TIBCO Spotfire Server.

On the client, data source credentials are cached for the duration of the TIBCO Spotfire session. This means that once a user has authenticated against a data source those credentials will be cached on the client side until the client is closed. Once the user has authenticated against a particular data source he/she will not be prompted for credentials for that data source for the remainder of the client session.

In addition, data source credentials are also cached on the TIBCO Spotfire server. On the server, data source credentials are stored on a per user, per data source basis. Credentials can be stored from a minimum of 36 seconds (0.01 hours) up to a full week.

Once authenticated a user will not be prompted for data source credentials during this time even if the client is restarted.

The server side credentials caching uses what is called a moving window for the timeout of the cached credentials. Hence, if a credential is used the caching period will be reset to the maximum timeout period.

Allow writing in temporary tables

Allows the Information Services to create temporary tables in this data source. This is needed when running information links that join data from several data sources or have a large number of filter values.

Note: The data source user must have sufficient privileges to create, truncate and drop the temporary tables in the data source.

Note: When using a data source with user authentication the database administrator needs to make sure that all users have sufficient privileges to create, truncate and drop the temporary tables in the data source.

Open session commands

Commands executed when acquiring a database connection from the connection pool or creating a new connection if pooled connections are not supported.

Can be used, for example, to authorize a user in an Oracle VPD context.

 

Example:

exec set_vpd_user(%CURRENT_USER%)

Close session commands

Commands executed when returning a database connection to the connection pool or closing a connection if pooled connections are not supported.

Can be used, for example, to clear an authorized user in an Oracle VPD context.

 

Example:

exec set_vpd_user(")

Connection initialization

Command executed when initializing a database connection.

Fetch size

The maximum number of values in each block of data retrieved from the database. Used for performance tuning. In general, use higher values for physically distant databases. Use lower values when the number of users is high. Entering the value zero will make the JDBC driver use its default value.

Batch size

The maximum number of values in each block of data sent to the database. Used for performance tuning. In general, use higher values for geographically distant databases. Use lower values when the number of users is high. Entering the value zero will make the JDBC driver use its default value.

Save As

Click Save As to save a copy of the data source.

Save

Click Save to save or update the data source.

See also:

Data Sources Overview

Creating a Data Source

Details
Pop-up Menus

Elements:

This pop-up menu is reached by right-clicking on an element in the Elements tree:

Option

Description

Open Data

[Only available when right-clicking on an information link.]

Imports data into TIBCO Spotfire.

Edit...

Opens the tab used to edit the selected element (Information Link, Join, Column, Filter, Procedure or Data source).

Edit Properties...

Opens the Edit Properties dialog where you can change the name and description of the selected item. You can also add keywords for improving the chances of finding the correct elements when searching the library.

New >

[Only available when right-clicking on a folder.]

Provides a shortcut entry to the Create Element options, opening a new tab were the new element can be specified.

Delete

Removes the selected element from the library.

Refresh

Refreshes the content of the currently selected folder.

Refresh All

Refreshes the entire tree.

Find All References

[Only available when right-clicking on a column, filter, procedure or join element.]

Opens a Find Results tab where all references to the selected element are listed. (For example, all information links where a certain column element is included.)

Validate

Validates the content of the selected element and opens a Validation Result dialog or a tab with information about errors and/or warnings.

Copy ID

Copies the GUID of the selected element to the clipboard.

Folder Permissions...

[Only available when right-clicking on a folder.]

Opens the Folder Permissions dialog where you can change the permissions for all users or groups to the selected folder.

Data Sources:

This pop-up menu is reached by right-clicking in the Data sources tree or on a data source in the Elements tree:

Option

Description

Edit...

Opens the tab used to edit the selected data source.

Edit Properties...

[Only available when right-clicking on a data source.]

Opens the Edit Properties dialog where you can change the name and description of the selected data source. You can also add keywords describing the data source.

Create Default Information Model...

Allows you to specify a location and automatically create a default set of elements and information links based on the selected data source, catalog, schema or table. See also Details on Create Default Information Model.

Create Column Elements

[Only available when right-clicking on a table.]

Opens a Multiple Column Elements tab with the content of the table already added to the Source columns list.

Create Table Alias

[Only available when right-clicking on a table.]

Opens a dialog where you can specify a name for the table alias and appends a copy of the selected table to the data source. See Creating a Table Alias for more information.

Delete

[Only available when right-clicking on a data source or a table alias.]

Removes the selected data source or table alias from the library.

Refresh

Refreshes the content of the currently selected instance.

Refresh All

Refreshes the entire Data sources tree.

Find All References

Opens a Find Results tab where all references to the selected instance are listed. (For example, all elements referring to a column from a certain database table.)

Validate

[Only available when right-clicking on a data source.]

Validates the content of the selected data source and opens a Validation Result dialog or a tab with information about errors and/or warnings.

Copy ID

[Only available when right-clicking on a data source.]

Copies the GUID of the selected data source to the clipboard.

Tab titles:

This pop-up menu is reached by right-clicking on the tab title of the Start tab or any other open tabs on the right-hand side of Information Designer.

Option

Description

Close

Closes the currently active tab.

Close All but This

Closes all tabs except the currently active tab and the Start tab.

Locate in Tree

Locates the currently active tab in the Elements tree.

[Titles of open tabs]

Allows you to quickly navigate to any of the currently open tabs.

See also:

Elements Tree

Icon Explanations

Details on Edit SQL 

DetailsonEditSQL.png

Option

Description

Data source

Displays the data sources used by the current information link in a drop-down list. You can only edit the SQL of one data source at a time.

Pre-Updates

Click this radio button to enter statements to be executed before the data retrieval. For example, this could be a call to a stored procedure or statements such as 'CREATE TABLE' or 'INSERT'.

Query

Click this radio button to display and modify the SQL of the information link.

Post-Updates

Click this radio button to enter statements to be executed after the data retrieval. For example, 'DROP TABLE'.

Modified SQL

Modify the SQL of the information link in this text box. Multiple SQL statements are allowed in pre-updates and post-updates as long as they are separated with double new lines.

Note: If you alter parameters in the SQL code, the changes will override any parameter settings made elsewhere, such as in the filter part of the information link tab.

Original SQL

Displays the original SQL of the information link so that you can immediately see the differences that you have made upon your modification.

Reset to Original

Resets the Modified SQL to the SQL originally created in the information link.

See also:

Editing the SQL of an Information Link

Pivot Conditioning in Information Designer

PivotConditioninginInformationDesigner.png

Option

Description

Identity

Each unique value in the chosen identity column produces a row in the generated table.

If you choose more than one column, then the new table will have a separate row for each unique combination of values in the chosen columns.

Other columns

Each unique value in the chosen category column produces a new column in the generated table.

Selecting more than one column  means that the new table will have a separate column for each unique combination of values in the chosen columns.

Values

The column from which the data is pulled. The values in the generated table are computed according to the method selected under Aggregation (for example, Average).

Note: If you are certain that each combination of Identity and Category has a unique value, then you can select the Aggregation: None which will not apply any aggregation of the data. However, the pivot will fail if you select None, and each combination of Identify and Category is not unique.

   Column name expression

You can select how the pivoted columns should be named. By default the predefined option is:

  Method(Value) for Column

You can also create a custom naming scheme for your pivoted columns.

Other columns

This option allows you to include an overall average of a particular measurement, for each row in the generated table.

See also:

Creating an Information Link

Pivoting Data

Example of Pivoting

Details on Prompt Groups

Prompt groups are used to specify which prompts should be dependent of each other, and which should not.

DetailsonPromptGroups.png

Option

Description

Element

Lists all elements currently available in the information link.

Group

By entering the same group number for two columns you specify that these columns should be dependent of each other, but not by columns from a different prompt group.

See also:

Using Prompt Groups

Details on Add Column

This dialog is shown when you have selected to add a filter or a prompt to an information link. It is also used to select columns when you want to apply a Pivot conditioning. Columns that have been added to the information link are available in the drop-down list.

DetailsonAddColumn.png

Option

Description

Select column

Specifies which of the column elements should be used in this particular place.

Aggregation method

Specifies the aggregation method to use for the column.

See also:

Adding Prompts

Using Prompts

Using Hard Filters

Understanding Filters

Details on the Save As Dialog

DetailsontheSaveAsDialog.png

Option

Description

Search

Type a search expression to limit the tree view below the search field so that it shows folders and elements matching the search expression only.

Clear Search...

[Link available only after a search has been performed.]

Click on the link to clear the search and return to view the full Elements tree.

[Elements tree or search results]

Click to select the folder where you want to save the element.

Name

The name of the element to be saved/edited.

Description

A short description of the purpose of the element.

Save

Click Save to save or update the element.

Note: Some characters are forbidden in the element names and file system of TIBCO Spotfire 3.0 and forward: /\*"?:<>|

Old information models with element names containing these characters can be imported. However, you cannot edit and resave elements using their old names if they contain forbidden characters.

Details on Create Multiple Columns

DetailsonCreateMultipleColumns.png

Option

Description

Place columns in

Click on the folder in the tree where you want the new column elements to be created.

Search

Type a search expression to limit the tree view below the search field so that it shows folders and elements matching the search expression only.

Clear Search...

[Link available only after a search has been performed.]

Click on the link to clear the search and return to view the full Elements tree.

Create Columns

Creates the column elements in the specified folder.

See also:

Multiple Column Elements Tab

Details on Add/Edit Column Property

Custom properties are metadata that can be used when searching for columns using the following syntax: <Property name>:<Value>. For example, IncludedColumn:True.

DetailsonAdd-EditColumnProperty.png

 

Option

Description

Property name

Specifies the name of the custom property.

Value

Allows you to specify a value for the selected property.

See also:

Column Element Tab

Details on Add/Edit Information Link Property

Custom properties are metadata that can be used when searching in the library by using the following syntax: <Property name>:<Value>. For example, DataTableType:On-Demand or DataTableType:"Additional Data Table". Information link properties are transformed to data table properties when the information link is opened in an analysis.

DetailsonAdd-EditInformationLinkProperty.png

 

Option

Description

Property name

Specifies the name of the custom property.

Value

Allows you to specify a value for the selected property.

See also:

Information Link Tab

Details on Open Information Link

The Open Information Link dialog appears when an information link is being opened, and a column is encountered that has been set up with a prompt (a run-time filter). The dialog may take various forms depending on the type of prompt selected.

Note: If the prompt step is not marked as mandatory by the person setting up the information link, then clicking Finish or Next > will automatically retrieve all available values for that step. However, if you make any changes to the dialog, such as selecting the Include empty values for this step check box, then the step is seen as modified and you will only retrieve the values actually selected in the dialog. This applies to all prompt types except Single selection.

Values

DetailsonOpenInformationLink1.png

Option

Description

Enter values

Type column values that you want to use as a constraint and separate them by pressing ENTER.

Type * to retrieve all possible values.

Get Values...

Use this button to set the column filter by fetching values from loaded data tables in TIBCO Spotfire. Opens the Column Values dialog.

Include empty values for this step

Select this check box to also include rows that do not contain any data for this column.

Range

DetailsonOpenInformationLink2.png

Option

Description

Lower limit

Type the lower limit for the values of the column.

Upper limit

Type the upper limit for the values of the column.

Include empty values for this step

Select this check box to also include rows that do not contain any data for this column.

Multiple selection

DetailsonOpenInformationLink3.png

Option

Description

Select values

Select individual values for the column by clicking the entries in the list. To select consecutive values, click the first item, press and hold down SHIFT, and then click the last item. To select multiple entries that are not consecutive, press and hold down CTRL, and then click each item.

Include empty values for this step

Select this check box to also include rows that do not contain any data for this column (and consequently are unavailable in the list).

Single selection

DetailsonOpenInformationLink4.png

Option

Description

Select one value

Select a single value for the column by clicking on it in the list.

Include empty values for this step

Select this check box to also include rows that do not contain any data for this column (and consequently are unavailable in the list).

See also:

Using Prompts

Details on Column Values

  • To reach the Column Values dialog:

  1. Open an Information Link with prompts.

  2. When the Open Information Link dialog for the desired column appears, click Get Values....

DetailsonColumnValues.png

Part

Description

Table

Select a data table from the drop-down list.

Column

Shows the available columns in the selected data table. Select a column by clicking on it in the list. You can narrow down the list of available columns by typing a part of a name in the "Type to search" field.

All rows

Gets values from all rows available in the entire data table, regardless of filtering.

Filtered rows

Gets values from the rows remaining after the current filtering (using the filtering scheme on the active page) only.

Marked rows, defined by:

Gets values from the rows marked in the visualizations (using the specified marking) only, regardless of filtering.

See also:

Details on Open Information Link

Details on Edit Parameter

DetailsonEditParameter.png

Option

Description

Name

Shows the name of the parameter you want to edit.

Data type

Lists the different data types available for your parameter. Select one from the list to change the data type your parameter expects the values it receive to be in.

Value type

 

  Single value

Choose single value if the parameter is supposed to receive just one value.

  Array

Choose array if the parameter is supposed to receive several values.

See also:

Editing a Parameter

Details on Missing Parameter Value

If you have the correct license, this dialog is shown when you open a parameterized information link or file and for some reason the value of a parameter has not been supplied. You can use this dialog to type a value for that parameter.

DetailsonMissingParameterValue.png

Note: If the parameter expects an array as input (multiple values) and the values come from a string column that has commas within the values (e.g., last name and first name in a name column)  the syntax to use is "Smith, John", "Williams, Peter", and so on. Any comma that is not within quote signs will be interpreted as the end of a value.

See also:

Parameterized Information Links

Details on Missing Information Link

This dialog is shown if an analysis with data linked to an information link is opened and the information link is missing.

DetailsonMissingInformationLink.png

Option

Description

Copy GUID

Copies the GUID of the information link to the clipboard. The GUID is a unique identifier for the information link that remain the same if the name of the information link is changed. This can be useful for support or an administrator if the information link cannot be found.

Open analysis anyway

Opens the analysis without the missing information link.

Browse for the missing information link

Opens a dialog that lets you browse or search for the missing information link.

Details on Create Default Information Model

This dialog allows you to specify a description and keywords for all elements created by the Create Default Information Model menu option. This type of information can be used to identify all elements created by this operation at a later stage. It also lets you specify how to handle any name conflicts with existing elements.

DetailsonCreateDefaultInformationModel.png

Option

Description

Description

An optional description of the data. The description could contain detailed information about the data enabling quick overviews.

Keywords

Keywords specific to the data. Keywords are separated by semicolons. This means that if you write "sales representatives; cost" it will be interpreted as two keywords, "sales representatives" and "cost".

Keywords are used to enhance organization and search capabilities.

If a created item has the same name as an existing item

 

   Automatically assign a new name to the created item

Appends a number, e.g., (2), to the name of the new item.

   Replace existing item

Replaces all existing items.

An update is performed so that the GUID for the item of a certain name is kept and any analyses referencing to the item with this name should still work. However, note that any manual modifications made to the default item will be lost upon replacement.

   Keep existing item

Keeps all existing items and adds only those items that previously did not exist.

See also:

How Are Conflicts Resolved?

Details on Select Destination Folder

This dialog is used to specify where to place the new default information model.

DetailsonSelectDestinationFolder.png

Navigate through the folders, and select the folder where you want the new elements and information links to be created. Information about the selected folder is displayed to the right of the list of folders. Which library folders you have access to is controlled by group privileges. Contact your Spotfire administrator if you cannot reach all the necessary folders.

You can search for a folder in the library by entering a name, or part of a name in the search field in the upper right corner in the dialog, and then pressing Enter. All folders matching your search string will then be listed. See Searching the Library for more information about search expressions.

See also:

Details on Create Default Information Model

Data Source Login Dialog

This dialog is displayed when the data source administrator has selected to use data source authentication (the User authentication check box on the Data Source Tab in Information Designer).

DataSourceLoginDialog.png

Option

Description

Username

The username for the specified data source.

Password

The password for the specified data source.

Data source

The data source that you are trying to connect to.

See also:

Data Source Tab

Details on Edit Properties

This dialog is used to edit the properties for an item in the library. It can be reached by right-clicking on the item of interest in the Information Designer tree structure and selecting Edit Properties... from the pop-up menu.

DetailsonEditProperties.png

To edit the properties of an item you must have Browse + Access + Modify permissions to the folder it is placed in.

Option

Description

Name

The name of the library item. The following characters are not accepted in titles:
\ / : * ? " < > | $ & + = @ # % ; , { [ ] } ^ ' ~ ´

Description

A description of the library item.

Keywords

Allows you to add keywords which can be used for finding the item in the library. Keywords are separated by a semicolon.

See also:

Elements Tree

Data Sources Tree

Tips and Examples

Understanding Filters in Information Designer

There are two ways to control filtering in Information Designer. One is to create separate filter elements. These will appear as icons, and allow the end user to apply them in an information link at will. The other method is to associate a filter directly to the column. This means that when the column element is used in an information link, the column filter is automatically applied.

Use column filters (hard filters) only when there is no reason to believe the user will ever want to use the column without a filter. Make sure the column description makes it clear to the user that a filter is being applied.

Use filter elements when you want to give users the option to use or not use the filter. Again, remember to write a good description of the filter.

See also:

Filter Elements Overview

Creating a Filter Element

Filter Element Tab

When to Use Prompt Groups 

Prompt groups can be used to treat elements (columns or filters) as independent from elements in other prompt groups during the opening of prompted information links. The default behavior of prompted information links is that each subsequent step lists values based on earlier selections. However, if you are working against, for example, a STAR schema database the procedure may require multiple joins since the elements queried for the prompts are linked only by the large fact table in the STAR schema.

By assigning an element to a different prompt group no previous selections in the prompt steps will be reflected in the listing for the independent element. Neither will any of the selections made in the prompt step for the independent element be reflected in later prompt steps (regardless of whether the later prompt steps are independent or not). It may also be of interest to specify a different prompt group for an element that is not prompted, for example, a column with a hard filter.

Example:

Say that you have information about your employees and their salaries stored in a STAR schema database with a layout similar to the one below:

WhentoUsePromptGroups1.png

Now, you want to retrieve information about the salary of your employees in the Chicago office, with the possibility to filter using DeptNo and Gender upon running the information link.

  • These are the steps you would perform to retrieve this type of information:

  1. Create an information link by adding all the interesting columns to the Information Link tab.

  2. In the Filters section, add the Location column.

  3. Select Values as Filter Type.

  4. Type Chicago in the Values text field.
    WhentoUsePromptGroups2.png

  5. In the Prompts section, add the DeptNo and Gender columns.

  6. Select suitable Prompt type options (for example, Multiple selection)) for the DeptNo and Gender columns.

  7. Click Groups... and enter a different prompt group for the Location column.
    WhentoUsePromptGroups3.png

By making Location independent you avoid having to join to the large fact table for each prompt. When the information link is opened you will first be prompted to choose a DeptNo. Instead of going through the large fact table to the Location table and filter out all values except the ones for Chicago, you will see all values in the DeptNo column. For the next prompt step the much quicker connection between DeptNo and Gender is used directly, thus improving the performance of the information link.

Note: When Location is made independent you will see all possible values in the prompt step for DeptNo even though some of them perhaps are inapplicable due to the hard filter setting Chicago on the Location column.
WhentoUsePromptGroups4.png

This means that if all employees in the Chicago office belong to department number 30, you might accidentally select number 20 in the prompt step for DeptNo with the result that no data is retrieved from the server.

 

If no prompt group changes had been made, the hard filter on Chicago would have been applied prior to the first prompting step and DeptNo 30 would have been the only one displayed in the prompt list:

WhentoUsePromptGroups5.png

 

In this case, the risk of selecting wrong data in a prompt step is minimized.

See also:

Information Link Tab

Using Prompt Groups

Replacing Null 

Sometimes a column returns null values. By using the Oracle SQL function NVL, null values can be replaced with another value.

Note: The following example only applies to data retrieved from an Oracle database.

  • To modify a column to replace null values with 0 (zero):

  1. Right-click on the column in the Elements tree.

  2. Select Edit from the pop-up menu.

  3. In the Expression field, type:

    NVL(%1,0)

  4. Click Save.

Note: Sometimes a null value has a meaning different from zero, such as "value unknown". Make sure you understand how the data is meant to be interpreted before replacing values!

See also:

Creating a Column Element

Column Element Tab

Limiting the Number of Records Returned 

The Oracle pseudo column ROWNUM makes it possible to use a filter to control the number of rows returned by an information link. In the example below, only the first ten rows are to be retrieved.

Note: The following example only applies to data retrieved from an Oracle database

.LimitingtheNumberofRecordsReturned.png

  • To create a filter that limits the number of rows returned by an information link:

  1. Click New and select Filter.

    Response: A Filter Element tab is opened.

  2. In the Expression field, type:

    ROWNUM < 11
     

  3. Type a suitable Description.

  4. Click Save.

See also:

Filter Elements Overview

Creating a Filter Element

Concatenating Strings 

The Oracle SQL function CONCAT lets you create a column by concatenating strings from different tables.

Note: The following example only applies to data retrieved from an Oracle database.

  • To combine strings from two different tables into a single column element:

ConcatenatingStrings.png

  1. Click New and select Column.

    Response: A Column Element tab is opened.

  2. In the Data Sources tree, select the first column (Region in the example above).

  3. Click Add >.

    Response: The selected element is added to the Source columns list on the Column Element tab.

  4. Select the second column (State in the example above).

  5. Click Add >.

  6. In the Expression field, enter:

    concat ( concat (%1, ' '), %2)
     

  7. Select String from the Data type drop-down list.

  8. Type a suitable Description.

  9. Click Save.

See also:

Creating a Column Element

Column Element Tab

Aggregate Functions

Aggregate functions summarize the values in a column. Below are some examples of aggregate functions that can be used when retrieving data from an Oracle database. Please see the documentation for your own database for more information.

Note: The following functions only apply to data retrieved from an Oracle database:

Function

Description

SUM (%1)

total of all the rows in column %1, given that the column is numeric

AVG (%1)

average of column %1

MAX (%1)

largest figure in column %1

MIN (%1)

smallest figure in column %1

COUNT(%1)

number of rows in column %1

CORR(%1,%2)

coefficient of correlation of columns %1 and %2

COVAR_POP(%1,%2)

population covariance of columns %1 and %2

COVAR_SAMP(%1,%2)

sample covariance of columns %1 and %2

REGR_SLOPE(%1,%2)

slope of the line described by columns %1 and %2

REGR_INTERCEPT(%1,%2)

y-intercept of the regression line described by columns %1 and %2

REGR_COUNT(%1,%2)

integer that is the number of non-null number pairs used to fit the regression line

REGR_R2(%1,%2)

coefficient of determination (also called "R-squared" or "goodness of fit") for the regression

REGR_AVGX(%1,%2)

average of the independent variable (%2) of the regression line

REGR_AVGY(%1,%2)

average of the dependent variable (%1) of the regression line

REGR_SXX(%1,%2)

computed as:
REGR_COUNT(%1, %2) * VAR_POP(%2)

REGR_SYY(%1,%2)

computed as:
REGR_COUNT(%1, %2) * VAR_POP(%1)

REGR_SXY(%1,%2)

computed as:
REGR_COUNT(%1, %2) * COVAR_POP(%1, %2)

STDDEV(%1)

sample standard deviation of column %1

STDDEV_POP(%1)

computes the population standard deviation of column %1 and returns the square root of the population variance

STDDEV_SAMP(%1)

computes the cumulative sample standard deviation of column %1 and returns the square root of the sample variance

VAR_POP(%1)

population variance of column %1

VAR_SAMP(%1)

sample variance of column %1

VARIANCE(%1)

variance of column %1

See also:

Creating a Column Element

Using Aggregation

Examples of Generated SQL
SQL – Filters 

In this example we will define a column element with a filter. The column should only return sales transactions above 10000.

SQL–Filters.png

Save the column element with the name High Sales.

The column element defined above will result in the following SQL when executed as part of an information link:

SELECT

   S1."Sales" AS "HIGHSALES"

FROM

   "Sales"."dbo"."SalesandCost" S1

WHERE

   (S1."Sales" > 10000)

   AND <conditions>

See also:

Filter Elements Overview

Creating a Filter Element

Understanding Filters

SQL – GROUP BY 

In the following example, we will use aggregation to calculate the average profit that our company made for a number of products, in order to locate our best product. We are assuming that the database provides the columns containing the price we got from the buyers at each transaction (Sales) and the price our company paid for the raw material (Cost) at that time. We also have a column containing the type of the product sold at each transaction (Type).

Define a column element called "Average Profit" as follows:

SQL–GROUPBY.png

We must also define a column element "Type" from Type which returns the product type.

If we define an information link using these three columns, the following SQL will be generated (notice the GROUP BY clause):

SELECT

   tmp1."AVERAGEPROFIT",

   E1."TYPE" AS "TYPE"

FROM

   "Sales"."dbo"."SalesandCost" S1

      SELECT

         AVG(E1."Sales" - E1."Cost") AS "AVERAGEPROFIT",

         E1."TYPE" AS col1

      FROM

        "Sales"."dbo"."SalesandCost" S1

      GROUP BY

         E1."TYPE"

   ) tmp1

WHERE

   (E1."TYPE" = tmp1.col1)

   AND <conditions>

See also:

Creating a Column Element

Using Aggregation

SQL – Subqueries 

Information Designer supports subqueries (inner SQL). In this example we will demonstrate how to retrieve all sales transactions greater than the average.

SQL–Subqueries.png

We create a new column "High Sales Transactions" with the following filter expression:

%1 > (Select avg(Sales) from Sales.dbo.SalesandCost)

Note: Be careful to include all brackets or it will not work!

The column element defined above will result in the following SQL when executed as part of an information link (notice that the WHERE clause includes the sub query from the filter condition):

SELECT

   S1."Sales" AS "HIGHSALESTRANSACTIONS"

FROM

   "Sales"."dbo"."SalesandCost" S1

WHERE

   (S1."Sales" >(

      Select

         avg(Sales)

      from

         Sales.dbo.SalesandCost

   ))

   AND <conditions>

See also:

Creating a Column Element

Column Element Tab

PL/SQL Functions

In the following example, we will retrieve sales information for employees in both USD and EUR. One way of doing this is to write a function in SQLplus that performs the calculation:

CREATE FUNCTION money_converter

( amount IN NUMBER)

  RETURN NUMBER IS

  return_val NUMBER (10,2) := 0;

  BEGIN

    return_val := amount * 0.75;

    Return (return_val);

  END;

/

The sales in USD is used as input, and the output is a value that is 0.75 times smaller. Store the function in the SDP_ADMIN schema (or elsewhere if a different administrator username is used) to make it accessible by Information Designer.

Now create a column element with the name "Sales (EUR)" as follows:

PL-SQLFunctions.png

Also create a column element "Type" with product types, and "Sales (USD)" with the original sales values.

When an information link with these three columns is executed from Information Designer, the generated SQL looks something like this:

SELECT

   S1."Type" AS "Type",

   S1."Sales" AS "SALESUSD",

   money_converter(S1."Sales") AS "SALESEUR"

FROM

   "Sales"."dbo"."SalesandCost" S1

See also:

Creating a Column Element

Column Element Tab

Library Administration

Introduction


The TIBCO Spotfire system features a library. Using the Spotfire library, people can publish and share all their analysis material, information links, data sources, etc. The Spotfire library contains a folder structure where you can set permissions on folder level.

While most Spotfire users can open and save files to the library from the normal Open and Save dialogs in TIBCO Spotfire, the Library Administration tool lets certain users access the library in a more administrative role. The Library Administration tool lets you structure the library and its contents. You can copy and move files and folders, create new folders and build folder structures. You can set permissions for folders so that only certain users are allowed access to certain parts of the library. If you want, you can also import content into the library, or export parts of the library to file.

To access the Library Administration tool you must have enabled the license feature Library Administration included in the TIBCO Spotfire Administrator license. Note that this license does not affect the actual permissions set for the various folders in the library, it only enables a user to open the Library Administration tool.

To have full control over the library you must be a member of the Library Administrator group that is always present in the Spotfire system. Members of this group will override any permission levels in the library and have full control of the entire library. Members of this group are also the only ones allowed to create, delete and change permissions for top level folders in the library.

See also:

Permissions

Permissions


Permissions

Permissions are always set on folders, never on items.

There are four levels of permissions that can be set for a folder in the library:

  • Access - The user or group is allowed to execute information links that are placed in the folder, but is not allowed to browse the contents or open analysis files.

  • Browse + Access - The user or group is allowed to access and browse the folder contents. The user can open and use items found in such folders, but not save or modify them.

  • Browse + Access + Modify - The user or group is allowed to browse and access the contents of the folder and also modify items or save new items. The user can also create new subfolders.

  • Full Control - The user or group is allowed to access and browse the folder, modify and save items, and also change permissions for the folder and its contents.

The Library Administrator Group

There is a group called Library Administrator that is always present in the Spotfire system. Members of this group will override any permission levels in the library, and have full control of the entire library. By default, members of this group are also the only ones allowed to create, delete and change permissions for top level folders.

Inheritance

By default, a subfolder inherits the permissions of its parent folder. This holds true for entire chains of subfolders in the library. However, if you have Full Control of a folder, you can edit the permissions for it and specify the exact permissions that you want it to have. When explicit permissions are set for a folder, the inheritance from the parent folder (or any folder higher up in the hierarchy) is completely severed.

It is important to note that for a user to be able to browse the contents of a folder, he must also have Browse + Access permission for the parent folder, and its parent folder, and so on all the way to the top level of the library. If the Browse + Access permission is removed for a folder in the hierarchy, the user cannot browse to any folders further down that folder structure regardless of the permissions set for such folders.

See also:

Introduction

Creating a New Folder


To create a new folder you must have Browse + Access + Modify permissions in the folder where you want the new folder to appear.

  • To create a new folder:

  1. Navigate to the folder in which you want to create a new folder.

  2. Click on the New Folder button.

    Response: The New Folder dialog appears.

  3. Enter a Name for the folder.

    Comment: The following characters are not accepted in folder titles: \ / : * ? " < > | $ & + = @ # % ; , { [ ] } ^ ' ~ ´

  4. Optionally, enter a Description for the folder.

  5. Optionally, enter Keywords for the folder. These should be separated with semicolons.

  6. Click OK.

Note: The new folder will by default inherit the permissions from its parent folder.

Deleting an Item


To delete items you must have Browse + Access + Modify permissions for the folder they are placed in. If you want to delete a folder, you must have Browse + Access + Modify permissions for all subfolders in the underlying folder structure.

  • To delete an item using the Library Administration tool:

  1. Select the items and folders to delete by clicking on them. You can use Ctrl or Shift to multi-select.

  2. Click on the Delete button.

  3. When prompted, confirm that you want to delete the selected items by clicking OK.

Tip: You can also right-click an item in the library tree and select Delete from the pop-up menu. The pop-up menu is also available in the Open from Library and Save as Library Item dialogs.

It is possible to have access to the Library Administration tool without being a member of the Library Administrator group. To get access to the Library Administration tool, the license called TIBCO Spotfire Administrator, as well as the license feature called Library Administration below it, must be enabled.

Moving an Item


To move items you must have Browse + Access + Modify permissions for both the source folder and the destination folder. If you want to move a folder, you must also have Browse + Access + Modify permissions for the entire underlying folder structure of the source folder.

  • To move an item:

  1. Select the items or folders to move.

    Comment: You can multi-select using Ctrl or Shift.

  2. Click the Move icon.

    Response: The Select Destination Folder dialog appears.

  3. Select a destination folder.

  4. Click OK.

  5. Select how you want to handle any potential name conflicts, and click OK.

    Comment: Read more about how to handle conflicts in How Are Conflicts Resolved?

The moved folders will keep any permissions explicitly set for them. If a folder is set to inherit its permissions, then it will inherit its permissions from the new parent folder after it has been moved.

See also:

How Are Conflicts Resolved?

Copying an Item


To copy items you must have Browse + Access + Modify permissions for both the source folder and the destination folder. If you want to copy a folder, you must also have Browse + Access + Modify permissions for the entire underlying folder structure of the source folder.

  • To copy an item:

  1. Select the items or folders to move.

    Comment: You can multi-select using Ctrl or Shift.

  2. Click the Copy icon.

    Response: The Select Destination Folder dialog appears.

  3. Select a destination folder.

  4. Click OK.

  5. Select how you want to handle any potential name and conflicts, and click OK.

    Comment: Read more about how to handle conflicts in How Are Conflicts Resolved?

The copied folders will keep any permissions explicitly set for them. If a folder is set to inherit its permissions, then the new copy will inherit its permissions from the new parent folder after it has been copied.

Folders or items that replace other folders or items will take on the identity of the destination folder or item, which means that references to the destination item or folder will continue to work but references to the moved folder will be broken.

See also:

How Are Conflicts Resolved?

Importing to Library


To import any content into the library from file, you must be a member of the Library Administrator group.

Note: Before importing content into the library, you should always make a backup of your existing library by using the Export function. Read more about this in Exporting from Library.

Note: When information model elements are being imported, the elements should not be accessed from the library or from Information Designer until after the import has been completed. If the import takes place on a server used by others, it is recommended either that no external access is allowed during the import, or that the information model is temporarily imported into a folder with no access permissions for other users. After the import has been performed, the permissions for the import folder can be changed, or the resulting elements can be moved to another place in the library.

  • To import content into the library from file:

  1. Click on the Import button.

    Response: The Import dialog appears.

  2. Click Browse.

    Response: The Select File to Import dialog opens.

  3. Select the file you want to import, and click OK.

    Comment: You can only import zip archives containing appropriate library content. It is not possible to import a single analysis. Also, such zip archives must be placed in a specific folder (or in its sub-folders) on a specific machine. This folder is called the "Shared Disk Location" and is set from the TIBCO Spotfire Configuration Console. In an environment with only one TIBCO Spotfire Server, this folder is by default located on the TIBCO Spotfire Server machine in the folder <server installation directory>/tomcat/application-data/library. In an environment with more than one Spotfire Server, one of them is dedicated to hold Library exports. See the TIBCO Spotfire Server - Installation and Configuration Manual for more information about how to set this up.

  4. In the Import items of type drop-down list, select whether or not you want to limit the import to only include certain types of items.

  5. Select if you want to Include permissions if such are stored in the file. If you do not include any permissions, the imported items will inherit the permissions of the destination folder.

  6. Select Ignore empty directories if you do not want to import any empty library folders.

  7. Select how you want to handle any potential name and/or GUID conflicts.

    Comment: Read more about how to handle conflicts in How Are Conflicts Resolved?

  8. Click OK.

    Response: The Select Destination Folder dialog appears.

  9. Select a folder to import to, and click OK.

    Response: The Library Import status dialog appears.

  10. Click Refresh to update the status messages in the dialog.

  11. Click Close when the Log states Import done.

See also:

How Are Conflicts Resolved?

Exporting from Library


To export a folder and its contents to file you must be a member of the Library Administrator group.

The folder and its contents will be saved as a zip archive. This zip archive will be placed in a specific folder (or in its subfolders) on a specific machine. This folder is called the "Shared Disk Location" and is set from the TIBCO Spotfire Configuration Console. In an environment with only one TIBCO Spotfire Server, this folder is by default located on the TIBCO Spotfire Server machine in the folder <server installation directory>/tomcat/application-data/library/. In an environment with more than one Spotfire Server, one of them is dedicated to hold Library exports. See the TIBCO Spotfire Server - Installation and Configuration Manual for more information about how to set this up.

  • To export a folder:

  1. Select the folder to export.

  2. Click the Export icon.

    Response: The Export dialog appears.

  3. Enter a name for the file that will be created.

  4. In the Export items of type drop-down list, select if you want to limit the export to only include certain types of items.

  5. Select whether to Include permissions or not.

  6. Click OK.

    Response: The Library Export status dialog appears.

  7. Click Refresh to update the status messages in the dialog.

  8. Click Close when the Log states Export done.

Note: Make sure that all dependencies (columns, filters, etc.) needed by the exported elements are included in the exported folder.

Searching the Library


You can search for library items in the Open from Library dialog, in the Library Administration tool and in Information Designer.

Searching for a text string will by default look for matching text in the title and keywords of the items in the library. You can use wildcards and boolean operators to search for parts and combinations of words. For a listing of the basic search syntax, see Searching in TIBCO Spotfire.

Library specific search:

Keyword

Example

Function

title:<word in title>

title:sales

Locates library items with the specified word (or part of word) somewhere in the title.

created_by:
<username>

created_by:admin

 

created_by::admin

Locates library items created by a certain user.

In the first example, all items modified by any users beginning with admin will be found. In the second example, only items modified by the user 'admin' will be found.

modified_by:
<username>

modified_by:admin

Locates library items modified by a certain user.

item_type:<type>

or

type:<type>

item_type:datasource

Locates items of a specific type. The available types are: column, filter, join, procedure, query (=information link) folder, dxp (= TIBCO Spotfire analysis file), datasource, datafunction and colorscheme.

item_id::<GUID>

or

id::<GUID>

item_id::dac3cd8c-47ec-454a-a8f2-691c60ece052

Locates a specific library item based on its unique identifier.

depends_on
(
<expression>)

depends_on(item_id::538bcde4-7212-475f-a348-5bb41ba39c41)

 

depends_on(Sales)

Locates all items that depend on a specific element.

 

required_by
(
<expression>)

required_by(item_id::6f6dc7e0-57bd-11d7-5ac0-0010ac110132)

Locates all items that are required by another item. If the GUID in the example to the left belongs to an information link, the search will find all columns, filters, etc. that are included in that information link.

modified

modified:"2 days ago"

modified:"a week ago"

modified:>"an hour ago"

modified:today

modified:<"this month"

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

modified::>created

 

 

modified:"2009-02-01T18:27:55CEST"

It is possible to search for items that have been modified during a specified time span, relative to today. There are two different ways of describing relative dates and times:

1) State the number of time parts ago in a string surrounded by quotes. The available time parts are seconds, minutes, hours, days, weeks, months and years. For example, search for modified:<"6 months ago". The given number of time units will be subtracted from the current time in the search.

2) State the time period to look back at using either of the keywords; today, yesterday, "this week", "this month", "this year". Note that you need quotes around all keywords consisting of more than one word. In this type of search, the last part of the date or time is "reset" (the time gets set to zero, the day of the month gets set to 1 etc.). The start day of a week is dependent on your server locale. For a en-US locale the first day of the week would be Sunday.

Modified, created and accessed can also be used in comparisons with each other. The  example to the left locates all items that have been modified after their creation.

Modified can also be used together with a timestamp of ISO 8601 format ("yyyy-MM-dd'T'HH:mm:ssz") to find items modified at a specific time.

created

created:>"this week"

created:<"2 weeks ago"

 

 

 

 

 

created:>"2009-02-01T18:27:55CEST"

 

 

It is possible to search for items that have been created during a specified time span, relative to today. See details regarding the allowed time spans under "modified" above.

Modified, created and accessed can be used in comparisons with each other.

Created can also be used together with a timestamp of ISO 8601 format ("yyyy-MM-dd'T'HH:mm:ssz") to find items created at a certain time.

accessed

accessed:>"this month"

accessed:<"2 weeks ago"

 

 

 

 

 

 

 

 

 

accessed:null

 

accessed:>"2009-02-01T18:27:55CEST"

It is possible to search for items that have been accessed during a specified time span, relative to today. See details regarding the allowed time spans under "modified" above.

Modified, created and accessed can be used in comparisons with each other.

Accessed can also be used together with a timestamp of ISO 8601 format ("yyyy-MM-dd'T'HH:mm:ssz") to find items accessed at a certain time.

The example accessed:null finds all items that have never been accessed.

The last example finds all items that have been accessed after the first of February 2009.

::>

modified::>created

Used to finds items strictly greater than the expression following the operator.

For example, finds all items that have been modified after their creation.

::<

accessed::<modified

Used to finds items strictly less than the expression following the operator.

For example, finds all items that have been modified after they were last accessed.

parent_id::
<folder GUID>

parent_id::538bcde4-7212-475f-a348-5bb41ba39c41

Locates all items located in the specified folder.

format_version:
<string or null>

format_version:null

Locates all items of a specified format version. For example, all items which have no format version specified can be found.

content_size:
<byte>

content_size:>10000

content_size:>500KB

content_size:<2MB

Locates all items of a specific byte size. In the first example, all items larger than 10000 bytes are found.

If nothing else is specified, the number is interpreted as bytes, but you can specify content sizes in KB, MB or GB as well.

Analysis files:

When searching for analysis files, there are a number of search parameters that may help you locating a specific group of analyses. If you want to locate analysis files only, add type:dxp to the search expression.

Keyword

Example

Function

description

description:sales

 

type:dxp description:sales

Locates all items containing the specified word in their description.

Locates all analysis files containing the specified word in their description.

keywords

keywords:sales

 

type:dxp keywords:sales

Locates all items containing the specified keyword.

Locates all analysis files containing the specified keyword.

AllowWebPlayerResume:
<true or false>

AllowWebPlayerResume:true

If true, locates all analysis files that allow personalized views for all web player users.

EmbedAllSourceData:
<true or false>

EmbedAllSourceData:true

If true, locates all analysis files that embed all source data. (Override and embed all data check box selected.)

OnDemandInformationLinks:
<GUID>

OnDemandInformationLinks:*

 

 

OnDemandInformationLinks:c45618c3-b7ac-43aa-bafe-e14f39fd4bb7

The first example locates all analyses that use on-demand data tables.

You can also specify a GUID to locate all analyses that use a specific information link as an on-demand data table.

AllTablesEmbedded:
<true or false>

AllTablesEmbedded:true

If true, locates all analysis files that only have embedded data tables.

Information Model elements:

If you want to locate information model elements of a specific type only, add type:column (or filter, join, procedure, query, folder or datasource) to the search expression.

Keyword

Example

Function

description

description:sales

 

type:query description:sales

Locates all items containing the specified word in their description.

Locates all information links containing the specified word in their description.

column

column:Sales

 

column::Sales

Locates all items referring to a source column with the specified name.

The source column could be referred to in the conditions or groupings of a column element, a filter condition, a join condition or the join condition of a procedure.

table

table:SalesandCost

Locates all items referring to a source table or stored procedure with the specified name.

This could be referred to in the conditions or groupings of a column element, a filter condition, the condition or target tables of a join or in the source procedure or join condition of a procedure.

schema

schema:dbo

Locates all items referring to a source schema with the specified name.

This could be referred to in the conditions or groupings of a column element, a filter condition, the condition or target tables of a join or in the source procedure or join condition of a procedure.

catalog

catalog:Sales

Locates all elements referring to a source catalog with the specified name.

This could be referred to in the conditions or groupings of a column element, a filter condition, the condition or target tables of a join or in the source procedure or join condition of a procedure.

datatype

datatype:integer

Locates all columns of the specified data type (integer, real, string, date, time, datetime, clob or blob).

parameter

 

parameter:MinSales

parameter:*

Locates information links using the specified parameter.

<property_name>:
<property_value>   

 

 "my.prop":*

Custom properties in any information model element are searchable using the same syntax.

However, note that the property name must by quoted if it contains a '.' delimiter.

Combinations of keywords:

You can combine many of the keywords described above to create more advanced search expressions. For example:

type:query depends_on(type:column salary) - searches for information links that contains a column named salary

type:query depends_on(column:salary) - searches for information links that contains an element that refers to a data source column named salary

required_by(type::query InformationLinkName) - shows the elements used by the information link with the name InformationLinkName.

(not (required_by(type:dxp))) and type:query - searches for information links that are not used by any analysis file in the library.

  • To search for items in the Open from Library dialog:

Depending on where you are searching, you may get different search results. Analyses and information links are shown when searching in the Open from Library dialog, not any information model elements or data sources, etc.

  1. Navigate to the top folder of the structure you want to perform the search in. If you want to search the entire library, navigate to the library root.

  2. Type the text you want to search for in the search field at the top right corner of the dialog.

  3. Click on the search button with a magnifying glass.

    Response: The dialog will switch to a Search Results view.

  4. The items matching your search criteria will be displayed in the list. To return to the normal folder view, click the Back to folder link.

  • To search for items in the Library Administration tool:

  1. Navigate to the top folder of the structure you want to perform the search in. If you want to search the entire library, navigate to the library root.

  2. Type the text you want to search for in the search field at the top right corner of the Library Administration tool.

  3. Click on the Search button.

    Response: The Library Administration tool will switch to a Search Result view. Note: Searching for data sources does not include searching for database entities like catalogs, schemas or tables. It is only the database instance itself that can be located via search.

  4. The items matching your search criteria will be displayed in the list. To return to the normal folder view, click the Back to folder link.

  • To search for items in Information Designer:

Depending on where you are searching, you may get different search results. Information model elements, information links and data sources are shown when searching in Information Designer, not any analyses, etc.

  1. Type the text you want to search for in the search field at the top of the Elements tree.

  2. Click on the search button with a magnifying glass, MagnifyingGlassButton.png.

    Response: The search results are displayed. Note: Searching for data sources does not include searching for database entities like catalogs, schemas or tables. It is only the database instance itself that can be located via search.

  3. The items matching the search result are shown in the list. To return to the normal folder view, click the Clear Search... link.

  • To use search expressions in custom RSS feeds:

You can create a customized RSS feed showing the latest changes to the library items you are interested in by appending a library search expression to a URL.

Use the following syntax to create your own feed:

http://<server>/spotfire/library[/path/to/something/interesting]?rss[&search=<search_expression>]

The path and  search parameters are optional. If you only specify http://myspotfireserver/spotfire/library?rss, the feed will return the 20 most recently modified files in the library. You can also add a max-results section if you want to limit the number of results shown, see example below.

Examples:

http://myspotfireserver/spotfire/lib...h=content_size:>500KB

http://myspotfireserver/spotfire/lib...ated_by::admin

http://myspotfireserver/spotfire/lib...ch=title:sales

  • To use search expressions in tibcospotfire links:

You can incorporate a search expression in a tibcospotfire link in order to directly populate the Open from Library dialog with some suitable analyses or information links. See Links to Analyses in the Library for more information about links. The links are a list of keys and value pairs. The key and value are separated using colon, :, and each key and value pair are also separated with colons:

tibcospotfire:<key1>:<value1>:<key2>:<value2>...<keyN>:<valueN>

The following keys and values are allowed:

Search: <search expression> with optional parameters.

OrderBy : Title | Modified | Created | Accessed | ContentSize | Description

MaxResult: <positive integer>

SortDirection: Ascending | Descending

 

The values should be encoded using the following pattern:

Value

Encoded to:

:

\:

"

\'

\

\\

Examples:

tibcospotfire:search:*:OrderBy:Modified:SortDirection:Descending:MaxResult:20

tibcospotfire:search:modified\:<\'3 days ago'\:OrderBy:Modified:SortDirection:Descending

Editing Properties


To edit the properties of an item you must have Browse + Access + Modify permissions for the folder it is placed in. You can edit the Name, Description and Keywords for all types of library items. For analyses it is also possible to edit some document-specific properties.

  • To edit properties:

  1. Select the item you want to edit properties for.

  2. Click on the Edit... link for the Selected Item.

    Response: The Edit Properties dialog is displayed.

  3. Enter a Name for the item.

    Comment: The following characters are not accepted in names: \ / : * ? " < > | $ & + = @ # % ; , { [ ] } ^ ' ~ ´

  4. Optionally, enter a Description for the folder.

  5. Optionally, enter Keywords for the folder. These should be separated with semicolon.

  6. Optionally, for an analysis file, make any changes to the properties on the Document tab.

  7. Click OK.

Editing Folder Permissions


To edit the permissions that are set for a folder, you must have Full Control permission for the folder. Read more about how permissions work in Permissions.

  • To edit folder permissions:

  1. Select the folder you want to edit permissions for.

  2. Click on the Edit... link to the right of the Permissions for Selected Folder title.

    Response: The Folder Permissions dialog appears.

  3. Select whether you want the folder to inherit permissions from its parent folder, or if you want to specify explicit permissions for it. Do this by selecting/deselecting the Inherit permissions from parent folder check box.
    If you choose to inherit permissions then you cannot specify any more details. Click OK and you are done.
    If you choose to set explicit permissions then proceed to the next step.

  4. When you clear the Inherit permissions from parent folder check box, the permissions the folder used to inherit are set explicitly. This gives you a template to work from if you want to remove certain permissions or perhaps add some.
    In the search field, type in the name of the user or group you want to add permissions for.
    Tip: You can use wild cards (that is, asterisks "*"), to simplify the search. For example, use a wild card to display all users and groups, or append it to a word to display all users and groups beginning with that word. Example: Group4* will find Group41, Group421 and so on.  

  5. Using the drop-down selector below the search field, select if you want to search only for Groups, only for Users or both Users and Groups.

  6. Click Search.

    Response: The users and/or groups matching the search criteria are displayed in the list to the left.

  7. Select the appropriate users/groups in the list and click the > button of the permission level they should receive.

  8. Repeat steps 4 through 7 until you have added all the users and groups to the permissions level you want.

  9. To remove a user or group from a permission level, select it and click the < button for that permission level.

Click OK.

Response: The folder will be updated with the new permissions.

Copying URLs


Items in the library such as analysis files have a unique URL. By copying this URL and pasting it into a web browser, you can open that file in TIBCO Spotfire or perhaps save the file to disk.

Also, if you have a TIBCO Spotfire Web Player server connected to your library, a second URL is available for each analysis file. By copying this to a web browser, the analysis will be opened in the TIBCO Spotfire Web Player running in your web browser.

  • To copy a URL and open in TIBCO Spotfire:

  1. Click on an analysis file to select it in the Library Administration window.

    Response: Under the Link to Selected Item heading,  a URL is displayed.

  2. Right-click on the URL and select Copy.

  3. Open a web browser and paste the URL into the address field.

    Response: The analysis is opened in a new instance of TIBCO Spotfire.

  • To copy a Web Player URL and open in TIBCO Spotfire Web Player:

  1. Click on an analysis file to select it in the Library Administration window.

    Response: Under the Link to Selected Item heading, a Web Player URL is displayed.

  2. Right-click on the Web Player URL and select Copy.

  3. Open a web browser and paste the URL into the address field.

    Response: The analysis is opened in TIBCO Spotfire Web Player.

  4. To copy a URL for unknown clients:

If you want to send a link to a larger audience where you do not know whether they have access to TIBCO Spotfire or just the Web Player, you can use a link to a redirect page instead.

  1. Right-click on an analysis file or information link in the Library Administration window.

  2. Select Copy TIBCO Spotfire URL > Server Redirect Page for Unknown Clients from the pop-up menu.

    Response: A URL leading to a redirect page is copied to your clipboard. Here, each user can select whether to open the analysis in TIBCO Spotfire, TIBCO Spotfire Web Player or to Download the analysis file.

See also:

Links to Analyses in the Library

How Are Conflicts Resolved?


Items and folders in the library are identified by two things: their name and a unique identifier (GUID). Several items can have the same name provided that they are not located in the same folder, but every item will always have a unique identifier. This identifier is used in external references to the item, such as a link to an analysis file that you can send to a colleague. If your colleague clicks the link, she can open the analysis file in TIBCO Spotfire. Here is an example of such a link:

http://spotfireserver:8080/spotfire/...1-96d3dfb41742

Copy and Move

When you copy or move an item to a folder where an identically named item is already present, you must decide how to handle this conflict.

  • Automatically rename copied item - this will append a "(2)" to the name of the file you copied.

  • Replace existing item - this will replace the existing item with the one you copied.

  • Keep existing item - this will keep the existing item.

Import

If an imported item has the same name or GUID as an existing item there will be a conflict. There are three options for how to handle this. These options are explained in detail below.

  • Automatically assign new name or GUID to imported item

  • Replace existing item

  • Keep existing item

The following images explain in detail how various conflicts are handled. This is primarily important for library administrators about to import content into the library, since one should be aware of the result if there are conflicts on GUIDs or names. Each section starts with an image showing the task performed, and then the result is shown for each of the three options.

Name Conflict Resolution

Import Folder A from Library 1 to Library 2

HowAreConflictsResolved1.png

 

Automatically rename copied item

HowAreConflictsResolved2.png

 

Replace existing item

HowAreConflictsResolved3.png

 

Keep existing item

HowAreConflictsResolved4.png

 

GUID Conflict Resolution

Import Folder A from Library 1 to Library 2

HowAreConflictsResolved5.png

 

Automatically assign new name or GUID to imported item

HowAreConflictsResolved6.png

 

Replace existing item

HowAreConflictsResolved7.png

 

Keep existing item

HowAreConflictsResolved8.png

 

Name and GUID Conflict Resolution

Import Folder A from Library 1 to Library 2

HowAreConflictsResolved9.png

 

Automatically assign new name or GUID to imported item

HowAreConflictsResolved10.png

 

Replace existing item

HowAreConflictsResolved11.png

 

Keep existing item

HowAreConflictsResolved12.png

Page statistics
4879 view(s) and 32 edit(s)
Social share
Share this page?

Tags

This page has no custom tags.
This page has no classifications.

Comments

You must to post a comment.

Attachments