Skip to main content

Spotfire Writing back to a database



Lot of time users need to write back to a database.

Spotfire provides different ways to do this, Without going thru the code I am going to talk thru the different options available. But if you need detailed instructions, please leave some comments and will try to provide one

1) Stored Procedures.


Advantages : Complex logic is easy to encapuslate in procedures. Also you are not storing username/passwords anywhere 

Spotfire can execute stored procedures and these procedures could be fetching data, or can be procedures that can be run Pre or Post running a query. You can then use procedures that basically update your database table or even inserts new rows in tables.

When using Spotfire information model it allows you to use these procedures which you can repurpose for updating your database.
Spotfire also understands inputs to a procedure and they can be mapped to your marked rows/filtered rows/ properties/constants etc from your analytics.

Steps to do that would be
1)Create a procedure that accepts inputs e.g. what is the value you want to update and some sort of key column(s) identifier for the record to update.
2) Create a Spotfire procedure element
3) Depending if you procedure is going to bring back some data or not, use the query or pre or post option correctly.
4) Create an Information Link that uses this Procedure element.
5) Ensure that your inputs to procedures are parameters and then use ?name syntax to map them to parameters.
6)Now in your analysis you can add this information link and chose to make it on demand.
7)Map you inputs to desired marking, filtering or property controls etc.
8) Generally I would recommend not using Automatic updates to avoid unnecessary write backs and also not select caching to ensure the procedure is run every time you need to.

9)Then the last step is writing a small ironpython script trigger the procedure whenever desired. Attach the script to a button or a link as desired in text area

if ondemandTable.IsRefreshable and ondemandTable.NeedsRefresh:
   ondemandTable.Refresh()
   dataTable.Refresh()

2) Information Links
Advantages : Quick and can be done right from within spotfire information designer, without having to define Procedures
Username and passwords are used from information model and hence not stored anywhere in clear text.

Create an information link, that will bring back some dummy data, or may be data that you want to bring back to spotfire after updating a table etc.

Once you have created an information link, hit the SQL button
In the Edit SQL Dialog, there is an option to provide preupdate query and also a post update query. In our case we will use the preupdate query option to write a query that will write back.
You can parameterize the query with the ?varname syntax, so your query could look like

Update tablename set salestotal = ?inputsalesvalues where record_id=?myrecordid

Then proceed to do steps 6 thru end as mentioned in method 1

3) Iron Python Script
Advantages : You get a lot of control over using ironpython to massage your data.

You can use the link below to see details on how to go about doing this http://spotfirecommunity.tibco.com/community/blogs/tips/archive/2010/12/12/writing-calculations-annotations-and-other-values-back-to-the-database.aspx?pcode=100044




Comments

  1. I need help with writing back to a database using an IL. I am getting an error with the IronPython script. Can you please provide detailed instructions as to how I should go about it?

    ReplyDelete
  2. what kind of error, your best bet may still be to work with spotfire support here, but if you provide more details about the error you are getting I can try to help

    ReplyDelete
  3. also try https://tibbr.tibcommunity.com/tibbr/ there are really some great people helping there and lot of times folks working for Tibco and Spotfire product

    ReplyDelete
  4. So I created the IL, added the pre-update query (which is an insert statement) and loaded the IL as an on-demand data table. I associated one property with the parameter that I specified in the pre-update query. The script works just fine. However, when I try to refresh the data table, I am getting the following error:
    Failed to execute data source query.
    at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
    at Spotfire.Dxp.Data.OnDemand.InformationLinkFunctionExecutor.OnDemandQueryBuilder.SetOutputResult(DataSource dataSource)
    at Spotfire.Dxp.Data.OnDemand.InformationLinkFunctionExecutor.d__0.MoveNext()
    at Spotfire.Dxp.Data.DataFunctions.DataFunctionExecutorService.d__6.MoveNext()

    Also, the value does not seem to be written into the table.
    Is there any other way to write back using an IL?
    Thanks for your help! :)

    ReplyDelete
    Replies
    1. Hi

      Did you manage to resolve this. I wanted to ask for your help with a similar situation, I've not managed to get the script written

      Delete
  5. hello i need urgent help with a spotfire script..help me anyone
    In the text area I have drop down list which has time frame data that is LW(last week),MTD,QTD and in the dashboard i have a tree map.Now i selected LW and mark some area in treemap and now if i change time frame from LW to MTD those markings won't there so i need the script that makes markings constant even time frame data in dropdown list changes.

    ReplyDelete
  6. Hi Babu, add a script for the dropdown and when the MTD,QTD is selected, capture the marked rows from the data table and reapply that marking on the datatable. If you need more information, let me know.

    ReplyDelete
  7. Can I edit the SQl in the information link to have a little complex sql? IS this recommended?

    ReplyDelete
  8. With low entropy plaintexts, there is a brute force attack on this system, which we call a ... Hence, for large enough X, collision attack is infeasible. Keygen X-Force

    ReplyDelete
  9. (x-Force keygen) для всех продуктов autocad 2021 Avid Sibelius 8 Camtasia Studio 8 Crack is trustful software which records your computer XForce Keygen Download

    ReplyDelete
  10. Avid Sibelius Ultimate 2022 Crack is a fantastic playback of your scores with its powerful music tools. It can add and modify notes easily.Avid Sibelius Ultimate 2023.10 Crack

    ReplyDelete

Post a Comment

Popular posts from this blog

Using Accordions in Spotfire Text Areas

Spotfire 6.0 provides great tools for quickly building your dashboard, but here we will learn to spice up things with the help of new 6.0 text area. If you are not aware the new 6.0 Text area supports HTML, Javascript and CSS. This opens up possibilities for doing a lot more with the text area. Some of the basic stuff can be seen here http://spotfire.tibco.com/qrt/UBSAN/presentation.html?pcode=100044   One of the cool things you can do is build an accordion panel in the text area for better utilization of the valuable screen real estate and helping the overall experience.(Watch the 0:15 second video below to see how accordion works - Sorry for the jitter in the video. The screen recorder does not record fast enough) Here is the html behind the text area : Please note that Spotfire controls are automatically created when you add text area controls, like filters, drop down lists etc. <DIV id="myaccordion"> <H3>Analysis Details</

Calendar Chart in Spotfire

I recently had a colleague asking me to show him the data in a calendar format.. Something like this I basically used a scatterplot for this.  You can check out the original DXP here  http://bit.ly/spotcalendar Some key settings to make it a calendar. Please note Order date is the date of interest here These expressions are out of the box expressions, no need to handcode them, but select them in the axis value  X Axis    <BinByDateTime([Order Date],"DayOfWeek",0)> Y Axis    Week([Order Date]) Trellis into panels   <BinByDateTime([Order Date],"Year.Quarter.Month",2)> Manual layout 4 X 3 Labels  UniqueConcatenate(DayOfMonth([Order Date])) Shape Tiled Markers Marker by   <UniqueConcatenate([Order Date])> Happy plotting your calendar!!!

Spotfire Auto Save for recovery

This is an extension I build to auto save local copies of currently open DXP every 15 minutes.. I have tested briefly and it seems to be doing its job, please please please try and test it if you can access a server and deploy.. Key Notes - This add-on will Auto Save Files while only in Spotfire Analyst Client. The save time is hard coded for every 15 minutes. Files are saved only if they are changed or user has done some actions like marking, filtering etc. The Files are saved in temporary location of the system, generally which will be like C:\Users\<username>\AppData\Local\Temp\Spotfire AutoSave If a Library file is opened it will be saved on your local machine under the temp path C:\Users\<username>\AppData\Local\Temp\Spotfire AutoSave\Library Content\ Library path will be replicated in this autosave location. e.g. C:\Users\<username> \Local\Temp\Spotfire AutoSave\LibraryContent\mylibraryfolder\mylibraryfilename_mmddyyyy_HH