Working With In-Database Data

When you are working with data from an external data source (in-database or in-db data) there are a number of features that you can use with in-memory data that are unavailable. See below for more information.

One thing to think about when working with in-db data is that changes to the underlying database schema will not automatically be reflected in the Spotfire analysis. This means that if a column is added to a database table you need to perform a Refresh Schema operation in Spotfire in order to see the new column in the analysis. Not all users will have the sufficient database privileges to perform a full schema refresh. However, changes to the actual data can be updated using a simple Reload/Refresh Data by most users.

Note: Before you can work with in-db data in Spotfire there are few prerequisites that need to be met (see the System Requirements at http://support.spotfire.com/sr.asp for details):

You may need to have drivers for the data source of interest installed on your machine.
Some connectors require additional packages to be deployed on the Spotfire server.
You need to have been granted access to the licenses by your Spotfire administrator.

Limitation	Why?
The table visualization and the Details-on-Demand (D-o-D) cannot display more than 10000 rows from an in-db data source. The table and the D-o-D do not support data from cubes. Virtual columns are not supported for in-db data.	There is a preset limit of 10000 rows to prevent large databases from overflowing Spotfire.
Marking in table visualizations is only supported if the underlying database table has a primary key defined.	A primary key is required to know which rows to mark.
The summary table cannot be used with in-db data.	The summary table has not yet been adjusted to be able to handle in-db data.
The box plot cannot be used with in-db data.	The box plot requires both aggregated and non-aggregated data and has not yet been adjusted to handle in-db data.
Scatter plots and 3D scatter plots need to be configured as aggregated visualizations when using in-db data, if the number of rows in the table exceeds 10000 rows.	There is a preset limit of 10000 rows to prevent large databases from overflowing Spotfire.
Dendrograms can only be shown in heat maps if the X-axis is set to (None), when using in-db data. Highlighting and marking in the dendrogram is not supported.	Dendrograms are row-index based and in-db data tables do not have any row index.
Not all of the standard Spotfire aggregation methods and expressions are available on all external systems. On the other hand, there may be other methods available that Spotfire does not have by default.	The aggregation methods supported by the external data source determines which methods will be available for in-db data.
OVER expressions must be written as post-aggregation expressions.	OVER expressions must be performed on the aggregated view. See Using Expressions on Aggregated Data (the THEN Keyword) for more information.
Automatically created date and time hierarchies are not available for in-db data.	The shortcuts for setting up date and time hierarchies have not been adjusted to handle in-db data.
You cannot save in-db data embedded in the analysis file.	Since no data is stored within the analysis in runtime, it is not possible to save it.
Transformations are not available for in-db data.	Since no data is stored within the analysis in runtime, it is not possible apply any transformation on the data.
If the connection to the external data source is lost, no further analysis using that data source can be performed.	Since no external data is stored within the analysis in runtime and each change induces a new query to be sent to the external data source, the data source must be available as long as changes to the visualizations are to take place.
Tags are not available for in-db data.	Tags are dependent on row numbers which are not available for in-db data.
You cannot delete columns or rows from in-db data tables.	In-db data tables contain no data in runtime so nothing can be deleted. (However, you can hide columns from the source tables when selecting tables in the Data Tables in Connection dialog. This does not apply to cube data sources.)
You cannot apply any of the following tools on external data tables: Insert Columns, Insert Rows, Insert Calculated Column, Insert Binned Column, Data Relationships, K-means Clustering, Line Similarity, Data Functions, Regression Modeling, Classification Modeling, Insert Predicted Columns	In-db data tables contain no data in runtime and can, hence, not incorporate any other data.
Auto-bin Column only works for certain connecors; e.g., Teradata, Oracle, PostgreSQL and IBM Netezza.	The ability to use width_buckets for auto-binning is only supported by some external data sources.
Export Data from Data Table cannot be used when exporting from in-db data tables.	In-db data tables contain no data in runtime so nothing can be exported from the data table. Tip: Use the Export Data from Visualization option when you want to export from in-db data tables. Note that for all visualizations other than the table and the cross table you need to mark the items of interest before opening the Export Data dialog.
String comparisons between different systems may provide different results due to handling of trailing spaces.	External systems that adhere to the SQL-92 standard (e.g., SQL Server, MySQL and Netezza) will trim trailing spaces for strings.
You need to provide wildcards to obtain fuzzy matching in text filters when working with in-db data.	Typing a part of a word with in-db data will only search for the exact match of those letters. (With in-memory data there will automatically be a wildcard appended to the entered letters.)
If you have multiple filters for an in-db data table, you will not get the visual clue indicating which values have been filtered out using the other filters, the way you do with in-memory data tables.	While graying out values filtered out by other filters is a costly operation, there is currently no visual connection between filters for in-db data tables.