Jump to content

Parametrize column selection of information link to reduce DB load and RAM usage


MaxB
Go to solution Solved by David Boot-Olazabal,

Recommended Posts

Hello everyone,

I’m looking to provide standardized information links to our users in Spotfire. My goal is to keep database load and RAM usage under control by loading only the columns required for specific use cases. To achieve this, I need a way to parameterize the information link, allowing flexibility in column selection.

I have an idea of how to implement this, but I cant get it working... Here’s what I have in mind:

Parameterized Information Links

 Steps:

      1.   Create a Parameter: In the Information Designer, create a parameter to represent the list of columns the user wants to query. For example, you can name this parameter SelectedColumns.

      2.   Use the Parameter in SQL: Modify your SQL query to dynamically include or exclude columns based on the value of the SelectedColumns parameter. A basic SQL template might look like this:

SELECT ?SelectedColumns

FROM MyTable 

WHERE <contitions>

             If the user selects specific columns, those will be substituted in place of ?SelectedColumns, allowing only the chosen columns to be queried.

             SelectedColumns has a default value in case the user doesn’t specify any columns.

 Using Spotfire Modifications: Dynamic Properties

To make the selection of columns more user-friendly, I would use Property Controls to allow users to choose from a predefined list of columns.

 Steps:

      1.   Create a Document Property: Set up a multi-select list property in Spotfire (let’s call it ColumnSelection) that contains the list of available columns the user can choose from.

      2.   Bind the Document Property to the Information Link: Link this ColumnSelection document property to the SelectedColumns parameter you defined in the Information Designer.

 

Any insights or suggestions on how to get this approach working would be greatly appreciated!

Thanks in advance for your help!

Edited by MaxB
Correction of syntax
Link to comment
Share on other sites

Hi David,

thanks for replying!

I tried several different syntaxes of the column names but I  always got this error:

Could not load on-demand data for table 'dynamic_column_selection_test'

Failed to create DataTable

   at Spotfire.Dxp.Data.ColumnFactory.CreateColumns(DataRowReader reader, String documentTitleForOrigin, IDataPropertyContainer defaultProperties, DataPropertyRegistry dataPropertyRegistry, GlobalMethodRegistry globalMethodRegistry, CxxSession session, Boolean addNewProperties, PartialDataLoadReport loadReport, ResultProperties resultProperties, PendingViewRequestsManager pendingViewRequestsManager, Boolean mangleColumnNames)
   at Spotfire.Dxp.Data.DataFunctions.DataFunctionImpl.DataFunctionWorkItemState.GetOutputResults(DataFunctionInvocation functionInvocation, Dictionary`2 pendingViewRequestsManagers)
   at Spotfire.Dxp.Data.DataFunctions.DataFunctionImpl.DataFunctionWorkItemState.<>c__DisplayClass60_0.<ExecuteFunction>b__1()
   at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, ProgressOperation operation)
   at Spotfire.Dxp.Data.DataFunctions.DataFunctionImpl.DataFunctionWorkItemState.ExecuteFunction(DataFunctionInvocation functionInvocation, Dictionary`2 pendingViewRequestsManagers)
 

dynamic_column_selection_test.dxp

Link to comment
Share on other sites

Hi Max,

I have tried to make it work, but I'm afraid it's not possible.

I have discussed this also with my colleagues. The dynamic generation of columns is not possible for 2 reasons:
- the information links have their predefined list of columns;
- the values send to the SQL statement are in string format.

So whenever you select a number of columns that is similar to the list of columns in the information link (let's say you've selected city and state), you'll get a result. But this result means that all lines are filled with the name of the selected columns instead of their values (so value 'city' will be in the city column and value 'state' will be in the state column). This is due to the string format that is being injected into the SQL. Whenever you select a number of columns that isn't equal to the listed columns in the information link, Spotfire will throw an error since it expected the number of columns defined in the information link.

As we feel this could be a useful enhancement, could you create a request for this on the Ideas portal?

Kind regards,

David

 

  • Like 1
Link to comment
Share on other sites

for the same reason I wanted to do so but for a data connection but this is not possible there either. There is an idea for that open https://ideas.spotfire.com/ideas/TS-I-8241

As a work around you can write a Python script used as a data function to connect to your DB with a dynamic select query but the issue is that credentials for connecting to the DB will be visible from the script!

  • Like 1
Link to comment
Share on other sites

  • Solution

Hi Max, So Ma,

Another way of accomplishing a dynamic query, is to use a stored procedure in Spotfire.
That stored procedure is being created on your database side, which holds a prompt. See below example of such a procedure (from SQL server):
 

CREATE OR ALTER PROCEDURE GETDYNAMICCOLUMNS
    @ColumnFilter as Varchar(100)
AS
BEGIN
    DECLARE @Template AS Varchar(max)
    SET @Template = 'SELECT {ColumnList} FROM [sales].[stores]'
    SET @Template = REPLACE(@Template, '{ColumnList}', @ColumnFilter)
    EXEC (@Template)
END
GO

In the example above, I use a table called sales.stores.

Once you have your procedure, you can 'catch' it in the Information Designer (make sure you use Query as Procedure type and set the Prompt to 'Single Value':
image.png.dc467fec1da815ac7910fdbe326fa600.png

You can now create an Information Link, based on this procedure:
image.png.a71ff09a4b78addad4db019195b7bfbd.png

When you then open the Information Link, you're prompted to enter values. These values are your column names, separated by comma to inject into your SQL statement properly:
image.png.8c112cbec8fb7d2209d981db461cc4f2.png

And then display the result:
image.png.87430e30b6a43a3a995e25ff4c3da7a2.png

You still may need to figure out how to automate the prompt part from a document property (if possible at all), but this creates a dynamic query to your database as you get prompted each time you reload the data set (just check this box for the data table):
image.png.6aa12fe5b42903ba67912544a93faf1b.png

Kind regards,

David

 

 

 

 

 

  • Like 1
  • Thanks 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...