Trigger On-Demand Queries with IronPython

  • Are you using on-demand queries that take a long time to load?
  • Are you worried users will tie up their machines if queries run automatically anytime the trigger is pulled?
  • Would you like to be able to trigger those queries with a button instead?

In previous posts, I’ve hinted I’m working on a large project merging several data sets.  Well, I’m still working on that project, and as I was reviewing it today, I realized Data Shop Talk readers might be interested in using IronPython to trigger on-demand queries with a button.  Let’s look at my use case to understand this a little better.

Use Case

A header data table populates a map with well locations.  Users mark the map to choose which wells they want to query production data for.  The marking triggers on-demand queries of three different production data sets.

Problem

These on-demand queries take a few minutes to execute.  If the queries actually trigger every time the user pokes at the map, it ties up their machine while the query executes, and they might not be ready for the query to execute.  I would much rather have the user click a button to trigger the query when they are ready.

Solution

This is easy to do with a simple IronPython script.  Note, in the code snippet below, I am actually triggering the update of three different tables. Here’s how you do it.

Detailed Steps

  1. Add a text area to the page
  2. Toggle the text area into edit mode
  3. Add an action control button
  4. Name the button something like “Update On Demand tables”
  5. Click the Script button
  6. Click New
  7. Name the script
  8. Copy and paste the script from below.  Use the screenshot as a guide in case the copy/paste operation corrupts the spacing
  9. Add the parameters
  10. Connect the parameters to the table(s)
  11. Run the script to make sure there are no errors
  12. Click ok until you are out.

Script

if Table.IsRefreshable and Table.NeedsRefresh:
Table.Refresh()
if Table1.IsRefreshable and Table1.NeedsRefresh:
Table1.Refresh()
if Table2.IsRefreshable and Table2.NeedsRefresh:
Table2.Refresh()

 

Lastly, make sure the on-demand query is set up NOT to refresh automatically, which would look something like this.

Spotfire Version

Content developed with Spotfire version 7.9.1.

 

Guest Spotfire blogger residing in Whitefish, MT.  Working for SM Energy’s Advanced Analytics and Emerging Technology team!

6 thoughts on “Trigger On-Demand Queries with IronPython

  1. Hi,

    Thanks for the post. I tried implementing it in my dashboard. I almost have 14 On Demand Queries running in my dashboard.

    when I execute the script all tables are refreshing but the data is not fetching for 1-2 tables. When I execute the script for the second time then I am able to see data.

    Could you please help me in understanding what the issue could be.

    Thanks much in Advance for clarifying.

    1. Clarification question — do the on demand queries work without the script? or did you add the script and the on demand query at the same time?

      1. I ask this question because I have had issues with on demand queries not executing when they run into database or virtualization layer limitations. More specifically, the on demand was triggered by marking on a map by well number. The user was marking 5000 wells, which generated a very “painful” SQL statement that the system wouldn’t accept. The query said something like WHERE Well no = 123 or Well no = 456 (x 5000 wells), and there was a limitation. Therefore, I ask if the query worked prior to the script or if you created them at the same time.

  2. Hi,
    TS-55681 – Visualization does not always updates after refresh is done via Python script

    After deploying the above hot fix the issue got resolved.
    Now I had a new issue where a pop up comes like Communication error in Ajax.Needsrefresh..
    Any thoughts on this issue please..

Leave a Comment

Your email address will not be published. Required fields are marked *