Spotfire® Data Table Source View API Overview

Introduction

The Data Table Source View API lets you read information on the different operations and transformations that are performed to construct a data table in an analysis, what is referred to as the data table source view. It is also possible to modify individual operations and transformations.

The API opens up a lot of possibilities in different scenarios, such as creating advanced analytic applications or automating dashboard creation.

Being part of the Spotfire Analyst .NET API, the Data Table Source View API is accessible from:

• C# extensions
• IronPython scripts
• Automation Services Custom Tasks

The functionality of the API corresponds to what is available in the Source View user interface, which is accessible from the Data Panel. The image below gives an example of a source view as it is shown in the UI. The 'Total Sales' table is created from an add rows operation that takes two data source operations as input, one SBDF and one Information Link. The SBDF data source operation also has a data transformation attached. Clicking on a data operation in the source view UI shows the settings for that operation in a panel below.

The corresponding view from the API could conceptually look something like this:

Total Sales data table

    Add rows operation

        Settings

        SBDF data source operation

            Settings

            Transformation

                Settings

        Information Link data source operation

           Settings

See Also

• IronPython Scripting in Spotfire contains examples of how to use the Data Table Source View API in an IronPython script.
  • Change join type
  • Replace a transformation
  • Replace a data source

API Overview

SourceView and DataOperation Classes

The SourceView class is the central class in this API. It represents a data table source view, which is the tree of data operations and its transformations that is performed to construct a data table.

A source view contains one or more data operations. The following data operation classes are supported:

• DataSourceOperation
• DataTransformationOperation
• AddRowsOperation
• AddColumnsOperation
• DataFunctionOperation
• DataConnectionOperation
• DataConnectionOnDemandOperation
• InformationLinkOnDemandOperation
• DataTableDataSourceOperation
• CalculationOperation
• FreezeColumnOperation
• RemoveRowsOperation
• RemoveColumnsOperation

All data operation classes share the same base class DataOperation.

Retrieving the Source View

An instance of the source view is retrieved by calling the GenerateSourceView method in the DataTable class:

 SourceView sourceView = dataTable.GenerateSourceView();

Navigate the Source View

Traversion of the source view to access all its operations typically starts with the 'last' operation by calling LastOperation property in the SourceView class, then calling Inputs on DataOperation to get its predecessors. Repeating this for each data operation will complete the traversion.

Example of a source view traversion (C# code):

public void TraverseSourceView(DataOperation op)
{
    // Retrieve information (settings and transformations) 
    // on the data operation passed as argument
    // ...

    // Call the method recursively for all input data operations
    foreach (var input in op.Inputs)
    {
        TraverseSourceView(input);                
    }
}

SourceView sourceView = dataTable.GenerateSourceView();
TraverseSourceView(sourceView.LastOperation);

In many cases a specific type of data operation might be of interest. To support this, there is a generic GetAllOperations<T> method in SourceView that returns a list of all operation of a given type.

Example listing all data operations of type AddColumnsOperation:

SourceView sourceView = dataTable.GenerateSourceView();

foreach (var op in sourceView.GetAllOperations<AddColumnsOperation>())
 {
     // Do something
 }

Retrieving Data Transformations

Depending on the type of data operation there may be data transformations attached. The following data operations supports transformations:

• DataTransformationsOperation
• DataFunctionOperation
• DataSourceOperation
• DataTableDataSourceOperation
• InformationLinkOnDemandOperation
• DataConnectionOperation (from 10.8)

The transformations attached to a data operation is retrieved by calling the GetTransformations method on any of the classes above.

For convenience, the SourceView class has a property OperationsSupportingTransformations that returns all operations that can have transformations using the utility class DataOperationSupportingTransformations.

Example:

foreach (var operation in sourceView.OperationsSupportingTransformations)
{
    foreach (var transformation in operation.GetTransformations())
    {
        // Do something     
    }
}

Add or Remove a Data Operation

Operations are added to the source view with specific methods in the DataTable class, for example:

• AddColumns adds an AddColumnsOperation.
• AddRows adds AddRowsOperation.
• AddTransformation/AddTransformations adds a DataTransformationsOperation.

An operation added using any of the methods above are appended to the 'last' operation in the source view.

From the SourceView class it is possible to remove a data operation (new in 7.12). 

• CanRemoveOperation
• RemoveOperation

When removing an operation, other operations may be removed as a side effect. For example, an add rows operation will implicitly be removed when removing one of its source inputs.

Removal of operations is not supported in versions prior to 7.12.

Modify a Data Operation

The individual operations in the source view may be modified. For information on which settings that is possible to modify for a specific operation, refer to the API documentation for the corresponding class.

Add or Remove a Data Transformation

To add or remove a data transformation applied to a data operation, call the ReplaceTransformations method, either on the utility class DataOperationSupportingTransformations or on any of the data operation classes that supports transformations (DataTransformationsOperation, DataFunctionOperation, DataSourceOperation, DataTableDataSourceOperation, InformationLinkOnDemandOperation, DataConnectionOperation).

Modify a Data Transformation

The individual data transformations applied to a data operation may be modified. For information on which settings that is possible to modify for a specific transformation, refer to the API documentation for the corresponding class.

Supported transformations

• AddCalculatedColumnsTransformation
• ChangeDataTypeTransformation
• ChangeNameTransformation
• DataFunctionTransformation
• ExcludeColumnsTransformation
• ExpressionTransformation
• NormalizationTransformation
• PivotTransformation
• ReplaceColumnTransformation
• ReplaceSpecificValueTransformation
• ReplaceValuesTransformation
• UnpivotTransformation

Inspect Data Sources

A DataSourceOperation represents data loaded from a DataSource instance, which may be of any of the following types:

• DatabaseDataSource
• DataTableDataSource
• FileDataSource
• InformationLinkDataSource
• SbdfLibraryDataSource
• CustomDataSource

The DataSource instance is retrieved using the GetDataFlow method in the DataSourceOperation class. This method returns a DataFlow instance, from which the DataSource can be accessed from a property. This allows you to inspect the details of a specific DataSource and by casting the instance to the appropriate class (see list above) further type-specific information can be retrieved.

A simple example to get the Id property of a CustomDataSource:

foreach (var op in table.GenerateSourceView().GetAllOperations<DataSourceOperation>())
{
  var dataSource = op.GetDataFlow().DataSource;
  if (dataSource is MyCustomDataSource customDataSource)
  {
    ...
    id = customDataSource.Id;
    ...
  }
}

Note: A DataTable may consist of multiple DataSources and multiple other operations.

Examples

Get Source View Information

This example shows a method that returns source view information in a format similar to what is displayed in the Source Information tab in the Data Properties dialog.

public static string GenerateBasicSourceViewInformation(AnalysisApplication application)
{
    var table = application.Document.Data.Tables.DefaultTableReference;
    var sourceView = table.GenerateSourceView();

    Func<StringBuilder, DataOperation, string, int> dumpOperationInfoRecursively = null;
    dumpOperationInfoRecursively = (stringBuilder, operation, indentationSpaces) =>
    {
        int step = 1;

        // If the operation had any inputs, then first print the first input's information.
        if (operation.Inputs.Count > 0)
        {
            step = dumpOperationInfoRecursively(stringBuilder, operation.Inputs[0], indentationSpaces);
            step++;
        }

        // Print information about this operation.
        stringBuilder.AppendFormat("{0}{1}. {2}rn", indentationSpaces, step, operation.DisplayName);

        // Output names of any transformations we have.
        IList<DataTransformation> transformations = new List<DataTransformation>();
        var ost = sourceView.OperationsSupportingTransformations.FirstOrDefault(op => op.DataOperation == operation);
        if (ost != null)
        {
            transformations = ost.GetTransformations();
        }
                
        int index = 0;
        foreach (var transformation in transformations)
        {
            stringBuilder.AppendFormat("{0}{1}. {2}rn", indentationSpaces + "    ", Convert.ToChar('a' + index), transformation.Name);
            index++;
        }
                
        // Last, print information about additional inputs. (Typically the
        // additional data for an Add Rows or Add Columns operation.)
        for (int i = 1; i < operation.Inputs.Count; ++i)
        {
            dumpOperationInfoRecursively(stringBuilder, operation.Inputs[i], indentationSpaces + "    ");
        }

        // Returns the step number for this operation.
        return step;
    };

    var sb = new StringBuilder();
    dumpOperationInfoRecursively(sb, sourceView.LastOperation, string.Empty);
	
    return sb.ToString(); 
}

A similar example in IronPython code can be found here.

Extract Replaced Values

This examples extracts all replaced values in a given data table and prints them to standard out. A more advanced use case would be to extract the replaced values and correct them directlly in the source database.

Note that this example only extracts the replaced values where the user has selected "All occurences in column" in the UI. Those value replacements are represented by the ReplaceValuesTransformations class. Value replacements can also be of type "This occurence only", represented by the ReplaceSpecificValueTransformation class. In this case the replaced row can be identified by the RowIdentifyingColumnSignatures and RowIdentifyingColumnValues properties.

void ExtractReplacedValues(DataTable table)
{
    foreach (var operation in table.GenerateSourceView().OperationsSupportingTransformations)
    {
        foreach (var transformation in operation.GetTransformations().OfType<ReplaceValuesTransformation>())
        {
            Console.WriteLine("Column: {0}, Original value: {1}, New value: {2}", transformation.Column.Name, transformation.OriginalValue, transformation.NewValue);
        }
    }
}

Change Join Type

This example shows a method that changes the join type of all AddColumnsOperation instances in a given DataTable.

void static void UpdateJoinType(DataTable table, JoinType originaJoinType, JoinType newJoinType)
{
    foreach (var operation in table.GenerateSourceView().GetAllOperations[AddColumnsOperation]())
    {
        var oldSettings = operation.AddColumnsSettings;
        if (oldSettings.JoinType == originaJoinType)
        {
            var newSettings = new AddColumnsSettings(oldSettings.Map, newJoinType, oldSettings.IgnoredColumns, oldSettings.TreatEmptyValueAsEqual)
            operation.AddColumnsSettings = newSettings;   
        }
    }
}

...

UpdateJoinType(dataTable, JoinType.LeftOuterJoin, JoinType.LeftSingleMatchJoin);

See also this IronPython example for how to change join type from within a Text Area Action Control. The example comes with a attached dxp file that illustrates the difference between the join types.