Wrap a Data Source inside a Data Function

Introduction

A custom data source is the preferred way of loading data into Spotfire from data sources not supported by any of the Spotfire native data access components. In complex scenarios, the data loading part may need input from the document such as metadata or data already loaded in a previous phase. There may also be the need to load data on demand in a similar way as data connectors and information links can be used. For this purpose you can develop a custom data function. In case you have already implemented a custom data source you can easily re-use that inside the data function to handle the data loading part. 

This article provides a code template that illustrates the general pattern on how to wrap a custom data source inside a custom data function in order to load data on-demand with input from the existing document and user prompting. The example is intentionally kept simple so that you can easily apply the pattern to your use case. You can download the source code from the attachment to this article.

CustomDataSource

Let's start with the data source component. As you can see, the implementation is quite straightforward. The only difference compared to other data source examples is that here we expose the configuration settings so that the data source can be configured later from a wrapper component (in this case our CustomDataFunctionExecutor). There are two kinds of settings, the data source specific settings such as a database connection string and runtime parameter values that are typically input from the current document but can also be from user prompting.

// Copyright © 2020. TIBCO Software Inc. Licensed under TIBCO BSD-style license.

[Serializable]
class MyDataSource : CustomDataSource
{
    public MyDataSource()
    {
        this.Parameters = new Dictionary<string, List<object>>();
    }

    private MyDataSource(SerializationInfo info, StreamingContext context)
    {
        this.SomeSetting1 = info.GetString("SomeSetting1");
        this.SomeSetting2 = info.GetString("SomeSetting2");
        this.Parameters = (Dictionary<string, List<object>>)info.GetValue("Parameters", typeof(Dictionary<string, List<object>>));
    }

    public override bool IsLinkable => true;

    // Some settings, could be some service URL or database connection string etc.
    public string SomeSetting1 { get; set; }
    public string SomeSetting2 { get; set; }

    // Some kind of on-demand parameter values.
    public Dictionary<string, List<object>> Parameters { get; }

    protected override DataSourceConnection ConnectCore(IServiceProvider serviceProvider, DataSourcePromptMode promptMode)
    {
        return DataSourceConnection.CreateConnection2(this, this.ExecuteQuery, serviceProvider);
    }

    public override void GetObjectData(SerializationInfo info, StreamingContext context)
    {
        base.GetObjectData(info, context);
        info.AddValue("SomeSetting1", this.SomeSetting1);
        info.AddValue("SomeSetting2", this.SomeSetting2);
        info.AddValue("Parameters", this.Parameters);
    }

    private DataRowReader ExecuteQuery(IServiceProvider serviceProvider)
    {
        // This is where you'd do something with the values in this.Parameters to generate your query,
        // execute it, and return a data row reader.
    }
}

CustomDataFunctionExecutor

Now let's see how the data source is wrapped inside a data function. The extension point for a custom data function is the CustomDataFunctionExecutor class. The other two parts in the data function framework, the DataFunctionDefinition and DataFunction classes, are not extendable. Further down in this article you'll see how these classes are instantiated and configured in a CustomTool. 

When the data function is run, either manually or automatic, the ExecuteFunctionCore method will be called. The settings and runtime parameters are then passed on from the DataFunctionDefinition object to the wrapped data source, which is then run through a DataConnection object. Note how the resulting DataRowReader object, returned from the DataSource.ExecuteQuery method, is simply passed on, via the DataConnection, to the data function output parameter without any need for further processing.
 

// Copyright © 2020. TIBCO Software Inc. Licensed under TIBCO BSD-style license.

class MyFunctionExecutor : CustomDataFunctionExecutor
{
    protected override IEnumerable<object> GetPromptRequestsCore(ImportContext importContext, DataFunctionDefinition functionDefinition)
    {
        // This is where you'd prompt for credentials if needed, see e.g. the KMeans developer example
        yield break;
    }

    protected override IEnumerable<object> ExecuteFunctionCore(DataFunctionInvocation invocation)
    {
        var functionDefinition = invocation.DataFunctionDefinition;

        // This is where you'd prompt for execution-specific settings with a yield return promptModel

        var ds = new MyDataSource();

        // This is where you'd deserialize all your settings to turn them into what the data source
        // actually expects. Using a json serializer is probably easiest.
        ds.SomeSetting1 = functionDefinition.Settings["MySettings1"];
        ds.SomeSetting2 = functionDefinition.Settings["MySettings2"];

        foreach (var input in functionDefinition.InputParameters)
        {
            // For multi-column arguments you'd have a list of tuples (or list of lists) and look at all the cursors
            var values = new List<object>();
            var reader = invocation.GetInput(input.Name);
            var cursor = reader.Columns[0].Cursor;
            while (reader.MoveNext())
            {
                values.Add(cursor.CurrentDataValue.HasValidValue ? cursor.CurrentDataValue.ValidValue : null);
            }

            ds.Parameters[input.Name] = values;
        }

        var connection = ds.Connect(invocation.ImportContext, DataSourcePromptMode.None);
        invocation.AddDisposable(connection);

        // I'm assuming a single output here, which is typically the case when using it as a data source
        // but you could also have value outputs to propagate some information
        var outputName = functionDefinition.OutputParameters.First().Name;
        invocation.SetOutput(outputName, connection.ExecuteQuery2());

        yield break;
    }
}

CustomTool

The last step to complete the solution is to create a CustomTool that will be used to configure and execute the data function.

In this example the data function is configured to be executed manually. It is also possible to have the execution be triggered automatically by a change in one of the input parameters. This is done by setting 

 function.UpdateBehavior = DataFunctionUpdateBehavior.Automatic  

 function.Execute()

// Copyright © 2020. TIBCO Software Inc. Licensed under TIBCO BSD-style license.

class MyTool : CustomTool<Document>
{
    public MyTool()
        : base("My tool")
    {
    }

    protected override void ExecuteCore(Document context)
    {
        // Show UI for configuring the data source here

        // Create the function definition
        var funcDefBuilder = new DataFunctionDefinitionBuilder("Test", MyTypeIdentifiers.MyFunctionExecutor);
        funcDefBuilder.Settings.Add("MySettings1", "Settings that do not depend on the data go here");
        funcDefBuilder.Settings.Add("MySettings2", "Settings that do not depend on the data go here");

        // This is your "on-demand parameter", you could have several of them if you eg want to connect
        // some things to document properties as well.
        var inputBuilder = new InputParameterBuilder("Input", ParameterType.Table);
        foreach (var dt in DataType.AvailableDataTypes)
        {
            inputBuilder.AddAllowedDataType(dt);
        }
        var input = inputBuilder.Build();
        funcDefBuilder.InputParameters.Add(input);

        // This is the result of the function.
        var outputBuilder = new OutputParameterBuilder("Output", ParameterType.Table);
        var output = outputBuilder.Build();
        funcDefBuilder.OutputParameters.Add(output);

        context.Transactions.ExecuteTransaction(
            () =>
            {
                // Add the function to the document, it will be in manual execution mode.
                var function = context.Data.DataFunctions.AddNew("MyFunction", funcDefBuilder.Build());

                // At this point you can use the function as a prompt model to the prompt service to show
                // the Spotfire configuration UI,  or you can show your own UI, or have done this already
                // in the initial configuration UI.
                function.Inputs.SetInput(input, "[Data Table].[Id Column]");

                // A Columns output act like calculated columns, the executor has to make sure the resulting
                // columns have the same row count as the input and that the values are in the right order.
                function.Outputs.SetColumnsOutput(output, context.Data.Tables["Data Table"]);

                // Finally, execute the function to get started
                function.Execute();
            });
    }
}

Attachments

datasourceindatafunctionexample_0.zip