Getting user input in Tibco Spotfire using IronPython
As a data analyst, visualizing data is my core business. In my company, we use Tibco Spotfire to create dashboards. This is great for visualizing data, but sometimes users need more than visualizations. Sometimes, they want to interact with the data. But how can we allow users to modify data from the dashboard? Spotfire allows you to extend basic functionalities using IronPython. So I started looking around whether people in my company had experience with IronPython and I came across this message on Slack:
Well, that’s a good start don’t you think? However, I am not one to give up easily. So I started anyway. One thing I noticed is that decent examples of how to get anything done in IronPython in Spotfire are scarce. Therefore I decided to write this post, so that others might copy it and adjust it to their needs.
Scripts and data
You can find all the scripts and data on my GitHub account, so feel free to retrieve them from there.
The dashboard
So in this post I am going to explain how I created a dashboard (including the IronPython code) to add / extend or delete comments in a excel dataset. Despite that I got it to work, I will mention several shortcomings below. Make sure to check them and do tell me if you discover more shortcomings that I missed. So without further ado, here is a screenshot of what the dashboard looks like.
Step 1 — Create a Text Area, a button and link the script.
So let’s start nice and easy. Open Spotfire (I’m using version 10.3.3 with IronPython 2.7.7) and create a text area.
Edit the text area (little pencil right top) and add a text field (insert property control -> Input field). You will be asked to link it to a ‘Document Property’. I called my document property “Comment” because I want to be able to add comments. Resize it as you see fit. If you want to change the property name you can, but note that you need to adjust the property name in the scripts too.
Next, add a button (insert action control). Give it a name (Add Comment), go to Script and click ‘New…’
The ‘Add Comment’ script
If you haven’t yet, navigate to the GitHub repository to copy the code for the ‘Add Comment’ button. Simply paste it in there. There are lots of comments in the code to explain what’s going on. So make sure to check them if you want to know what is happening.
Step 2 — Document properties
One document property was mentioned before, the ‘Comment’ document property. This property captures whatever a user types into the text field we created above. We do need a couple of more properties.
The document properties needed for this project are highlighted in blue (the others are default). So go to ‘File->Document Properties->Properties’ and add the ‘Message’, ‘ModificationTable’, ‘CommentColumn’, ‘ModificationDateColumn’, ‘IDColumn’ and ‘SavePath’ properties.
The ‘ModificationTable’ property points to the table name of the table that needs to be modified. In my case I called the table FakeData, which contains the data from the FakeData.xls downloaded from GitHub.
The ‘CommentColumn’ refers to the column name which should contain the comments. It is simply called ‘Comment’ here.
The ‘ModificationDateColumn’ contains the column name that should contain the date/time of the most recent adjustment of the comment. That column is called ‘Comment_modification_date’ here.
The ‘IDColumn’ contains a unique identifier of a row, simply called ‘UniqueID’ here.
The ‘SavePath’ is the location of the fakedata.xls file you’ve downloaded from GitHub. This dashboards overwrites the input file to save the comments so it should point to the same file where you imported the data from.
The ‘Message’ property can be empty at the start but it is used to display messages to the user so should exist. You can simply set it to ‘NA’ to begin.
Step 3 — The first comment
Alright, let’s add a table to show the data on the same page which contains our text field and button. I’ve put it on the bottom of the screen but place it where ever you want. Your dashboard should look something like this now:
Cool, type something into the text box, highlight the row (or rows) you want to add this comment to and click the ‘Add Comment’ button (you did copy the script and create the document properties as I told you didn’t you?).
Et voila, your first comment.
Step 4 — Add the other buttons.
In the same way you added the ‘Add Comment’ button, add buttons for ‘Extend Comment’, ‘Delete Comment’, ‘Export Comments’ and ‘Reload Data’.
You can find the scripts for each buttons on the GitHub page.
The ‘Extend Comment’ will paste the new comment after the already existing one separated by a pipe (|). The code is almost the same as for the Add Comment button.
The ‘Delete Comment’ does what it states, it deletes the comments in the rows you selected. Again, the code is very similar to the Add Comment button.
The ‘Export Comments’ button will export the entire data table as it is now present in the dashboard. It will overwrite the existing xls file on the location you’ve specified in the ‘SavePath’ property, which should be the same as your input file.
The ‘Reload Data’ will reload the data table by reading the xls file from disk again. We use the xls file from a network location and if some else has added comments while you were doing something else you might want to know about it, therefore this button exists.
Step 5 — Add a message box.
All the scripts above change the value of the ‘Message’ property. The only thing we need to do now it to display it so the user knows what is going on.
So edit your text area again, type ‘Message:’ and behind that insert a Label (same method as the text field we did above but this time choose ‘Label’ instead of ‘Input field’). A pop-up will appear asking you to which property we want to link this label, select ‘Message’. Now, this will show the message to the user.
Conclusion
Congratulations! You have just created a dashboard where users can enter comments into the data. There are many scenario’s where the ability of users to input data from a Spotfire dashboard could prove useful. However, a warning is in place. This system is not made to serve many users at the same time, nor is it the fastest implementation. So use with care. Below I will list a few more notes and shortcomings. Thanks for reading and feel free to comment below.
PS. I’ve placed a little Easter egg in the data. If you are bored go look for it.
Notes / shortcomings
As I mentioned above, it is not the best system to have many users at the same time. It does however have some checks in place to prevent multiple users from interfering with one another. These can be either improved or removed (checks take time, so removing them makes it faster).
Check 1) If the ‘Add Comment’ / ‘Extend Comment’ / ‘Delete Comment’ button is pressed, it will refresh the data and check whether the rows you want to modify have been changed in the meanwhile. If not, it will continue. If they did change (because someone else changed them) it will not proceed with the changes and message the user that changes were made (which need to be reviewed first).
Check 2) When exporting the data, it will refresh the data first. As I understood it, Spotfire will basically apply the changes you’ve made on top of the new data at that time. So if 2 users export shortly after each other, both changes will be kept, but the changes of the second user might overwrite the changes of the 1st user if they modified the same rows (needs to be tested).
Check 3) The export script will try to save the data. However, if at that exact time someone else is writing to that file (or has it open in excel..) it cannot save the data. It will wait for 10 seconds, try again. It will try to save up to 3 times this way. If it cannot save, it will notify the user that it cannot save because the file is busy. The user will have to try again later or find out who has the file open (again, inconvenient with many users).
Note: all times are given in UTC. This is simply because our server is in a different country which would give different times depending on whether you run the desktop client or the web player.
Note: I am using xls and not xlsx or another format. This is simply because an excel file is convenient for many people and the xlsx export seemed slower / less reliable.
Note: IronPython didn’t make me cry (yet). But maybe I haven’t grown up yet.
Note: It does become slow on large datasets. 1000x11 is ok, but 10k rows will probably be a bit slow. It will also depend on the speed of your device, importing from a network disk over VPN is a lot slower than from your own disk.
