TIBCO Spotfire® Data Table Source View API Overview
Last updated:
7:52am Apr 26, 2018

Introduction

The Data Table Source View API is a new API in 7.10 that 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:

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

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:

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:

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:

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:

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):

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 (DataTransformationsOperationDataFunctionOperationDataSourceOperationDataTableDataSourceOperationInformationLinkOnDemandOperation).

    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

    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}\r\n", 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}\r\n", 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(); 
    }
    

     

    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 a Text Area Action Control. The example comes with a attached dxp file that illustrates the difference between the join types.