Home : Customer Support / Developers / Developer Blog

Developer Blog

Tips, tricks and development news for DecisionSite visioneers

Using Stored Procedures with DecisionSite

DecisionSite’s Information Services supports executing database procedures.

This capability opens up a few interesting customization capabilities that I get quite a lot of questions about. I thought I’d put together an article that explains how you can use stored procedures and how simple it can be to integrate database actions into DecisionSite.

This article walks though the following:

- What is a database procedure?

- How are Spotfire customers using procedures?

- A simple tutorial & how to extend it further

- A real customer example

- More reading

Show extended entry >>

What is a Database Procedure?

A stored procedure is a program stored in a database such as Oracle or Microsoft SQL server. Procedures are usually written in a proprietary database language like PL/SQL for Oracle databases. The advantage of a stored procedure is that it runs directly by the database engine. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

Typical uses for stored procedures include data validation (often implemented as triggers), or encapsulating some complex processing such as aggregating a large dataset to produce a summarized result.

Stored procedures can also be a very good thing when a database is manipulated from external programs such as web pages or Spotfire DecisionSite. By embedding this data manipulating "business logic" in the actual database there is no need to embed and maintain any custom code that manipulates the database in the external programs. This can simplify the creation and particularly the maintenance of the programs involved.

So... how are Spotfire customers using Stored Procedures?

A common scenario how Spotfire customers are embedding "business logic" in the database using stored procedures is for capturing annotations on their data.

For example:

Bad quality data needs to be tagged as "bad quality" in the actual database. Typical scenarios would be tagging experimental results that are out of scope or perhaps some manufactured that have units gone bad and should be discarded.
Data that has been found as "interesting" is marked for follow-up. This could be lists of customer IDs that should be part of the next Marketing email campaign, or chemical compounds that have passed a certain screening phase.

By capturing this data and logic in the database it automatically becomes centrally managed, secure, backed-up and very very easy to integrate into applications that take actions based on the data. And of course, this new data will then be easy to use from DecisionSite when you need to further analyze it :-)

A simple example

Let's take a simple scenario where we want our DecisionSite users to simply tag some data as "bad quality".

Our annotations are managed in the corporate database as a separate table that maps annotations columns such as Quality to the identifiers:

In our database we have a stored procedure that adds records to the annotation table:

create or replace procedure insert_into_annotation_table (id_in char, quality_in char)

as

begin

insert into annotationtable values (id_in, quality_in);

end;

I have setup an Information Link that takes an ID input parameter and marks it as "Bad" in the table (i.e. it adds a new record

Since we want to make it simple for our users to annotate the records, I put the annotation step into one of our existing Guides that helps the users access the appropriate data source.

I used Analysis Builder to simply add this Information Link as a step in our Guide.

I then published this Guide to the DecisionSite Library to enable my end users to simply mark a set of bad records and click the link in the Guide to dynamically tag them as "Bad" in our database.

Automatic annotations & custom UIs

There is a nice tutorial on the Developer Network that shows developers how to implement a custom UI for capturing additional annotations. The tutorial also shows how to automatically include the user name and at what time the annotation was made. See http://developer.spotfire.com/spotfire/dsd/use/is_storedprocedures.htm (login required)

Customer example

A real implementation of database write back is demonstrated in the recorded WebCast labeled "Integrating DecisionSite with Infinity Pharmaceuticals' Chemistry Framework" at http://www.spotfire.com/developercentral/developer_webcasts/detail.cfm?id=5778

In this WebCast Jeff Reed, a Senior Software Engineer at Infinity Pharmaceuticals, demonstrates how they allow their users to right-click on marked records in DecisionSite and choose Knock out to automatically annotate these records as knocked out in the corporate database.

More reading

Development tutorial on how to write back to databases using stored procedures:
http://developer.spotfire.com/spotfire/dsd/use/is_storedprocedures.htm (login required)

Database procedures in DecisionSite's Administrators guide:
http://developer.spotfire.com/spotfire/dsd/helps/dsadmin/id/id_procedures_overview.htm (login required)

Please let us know if you have any questions or ideas regarding stored procedures or DecisionSite customization in general.

<< Hide extended entry

Posted by Martin Nilsson in Development | Permalink -

8:08 AM | Send to Friend | Trackback (0)

Publishing a Guide with custom developed code to the DecisionSite Library

DecisionSite 8 provides a quick and easy way to publish Guides to the DecisionSite Library. From the Library the Guides can be accessed by your colleagues directly from the web interface, emails, URLs or through the DecisionSite Client interface as shown below.

This article is broken into two sections; section 1 outlines how to publish an existing Guide containing custom code, while section 2 deals with how to customize a Guide produced using the Analysis Builder and publish it to the DecisionSite Library.

Show extended entry >>

Section 1 - Publish a Guide with custom developed code

If you already have a system folder containing your custom Guide files then simply follow the steps below.

Launch the DecisionSite client.
Select the menu item Guides -> Publish to Library... (See Figure 1)
In the dialog box select Guide with custom developed code (See Figure 1)
Edit the guide properties and publish to the DecisionSite Library

Figure 1. Guides -> Publish to Library.

Section 2 - further customize and publish a Guide produced using the Analysis Builder

The second half of this Article outlines how to customize a Guide produced using the Analysis Builder and then publish it to the DecisionSite Library.

The Analysis Builder can be used to build the core of your Guide, adding text as a placeholder to be replaced at a later stage with custom code (See Figure 2; click here for more information on the Analysis Builder, login required).

Figure 2. Using the Analysis Builder to construct your initial Guide framework.

Once finished with the Analysis Builder you should save your Guide as a local .SFG file. The next step is to unpack your .SFG file so you can edit your HTML pages, to accomplish this you will need to use the DecisionSite Builder tool (Click here for more information on the DecisionSite Builder, login required).

Open the DecisionSite Builder and create a new project using the menu item Project -> New (See Figure 3). When creating a new Project you need to specify a 'Project folder' where your source code will reside.

Figure 3. Create a new Project using the DecisionSite Builder

Next you need to add your SFG file to the Project using the menu item Insert -> Guide, in the dialog choose to insert an 'Analysis Builder Guide' (See Figure 4).

Figure 4. Inserting your Analysis Builder .SFG file into your Project.

After you have finished editing the properties of your guide your SFG file will be extracted into your 'Project folder' (See Figure 5).

Figure 5. Your Guide code is extracted to the Project folder where it can be edited.

Navigate to your Project folder and edit the Guide HTML pages as required (See Figure 5). As you work on your custom code you can use the DecisionSite client to test and debug you code by selecting the 'Use Local User Profile' at logon (See Figure 6; click here for information on your local profile, login required). Note: In the DecisionSite Builder go to the menu item Project -> Manage Projects... and ensure your project is checked thus allowing it to be included in your local profile (See Figure 7).

Figure 6. Enable 'Use local user profile' to allow for the testing of your code.

Figure 7. Checking your Project ensures it is included in your local profile, where it can be tested.

Once you are finished editing and testing your code, you can either build and deploy your entire Application/Extension or you simply follow the steps outlined in Section 1 to publish the customized Guide to the DecisionSite Library where it can be accessed by your colleagues.

For more detailed information:

Build Guides using the Analysis Builder
http://developer.spotfire.com/spotfire/dsd/Examples/DecisionSite%208%20-%20Analysis%20Builder%20whitepaper.pdf (login required)
Build Applications/Extensions using DecisionSite Builder
http://developer.spotfire.com/spotfire/dsd/use/dsbuilder.htm (login required)
What is the DecisionSite Library?
http://www.spotfire.com/developercentral/blog/images/what_is_the_decisionsite_library.pdf
How to setup your initial Library
http://www.spotfire.com/developercentral/blog/index.cfm?commentID=16

<< Hide extended entry

Posted by Gerard Conway in Development | Permalink -

4:10 PM | Send to Friend | Trackback (0)

How to draw vertical lines in scatter plots

- How do I draw vertical lines in my scatter plots?

This is a common question that we get. There is a simple way to do it using curve drawing.

Show extended entry >>

Posted by Martin Nilsson in Development | Permalink -

1:56 PM | Send to Friend | Trackback (0)

Converting Applications/Extensions to the new 8.1 menu system

DecisionSite 8.1 is released this week. As you probably know, the 8.1 release supports a new and improved menu system that simplifies the UI access to Guides and tools.

During the Beta program and as DecisionSite 8.1 is starting to get deployed in production environments, a common question that I get is how existing Applications and Extensions will work with the new DecisionSite Client menu system.

You don’t need to port your existing applications to the new 8.1 menu system. However, with the 8.1 menu system you can even further configure the location of tools. This article describes where your existing custom tools and Guides will end up in the 8.1 menu system per default. It will also highlight you how to further configure where they should show up.

The Developer Network has also been enhanced in the 8.1 release, see http://developer.spotfire.com/spotfire/dsd/site_news.htm (login required).

For more general information about the new capabilities in DecisionSite 8.1, see http://www.spotfire.com/products/whatsnew.cfm

Show extended entry >>

DecisionSite 8.0

Suppose you have a licensed Application implemented in DecisionSite 8.0. The Application consists of one custom Guide and two custom tools: The first is a clustering tool that you have placed in the Analysis group in the DecisionSite Navigator, and the second is more of a reporting tool, so you put it in the Reporting group.

You defined the layout of these tools in the application.xml file as follows:

<group>

<name>Spotfire.Navigator.Tools.Analysis</name>

<title>Analysis</title>

<tool-ref>MartinTest.Tools.CalculateXYZ</tool-ref>

</group>

<group>

<name>Spotfire.Navigator.Tools.Reporting</name>

<title>Reporting</title>

<tool-ref>MartinTest.Tools.ExporttoXYZ</tool-ref>

</group>

The Guide and the Tools are available in the Navigator panel:

For more information on how to control where the tools and Guides end up in the DecisionSite 8.0 Navigator see http://developer.spotfire.com/spotfire/dsd/use/am_layout_merger.htm (login required)

DecisionSite 8.1

When you upgrade to DecisionSite version 8.1, and use your existing 8.0 Application as-is, the menu locations in DecisionSite 8.1 will automatically reuse the layout defined in the application.xml. The Guide automatically shows up both in the Guide pane and in the new Guide menu:

This is the default behavior for all Guides defined in Applications or Extensions, while Guides stored in the Library will be accessible from the Open Guide from Library... link in the Guides pane.

The reporting tool automatically shows up in the File > Export menu:

The analysis tool automatically shows up in the Tools menu:

The default behavior when deploying existing 8.0 components on 8.1 is as follows:

DecisionSite 8.0	DecisionSite 8.1
All Guides	Guide menu + Guide panel
Tools in Access folder (Spotfire.Navigator.Tools.Access)	File.Import menu
Tools in Analysis folder (Spotfire.Navigator.Tools.Analysis)	Tools menu
Tools in Reporting folder (Spotfire.Navigator.Tools.Reporting)	File.Export menu
All other Tools	Tools menu
All Resources	Help menu

By locating your tools in the most logical menu or sub menu, you can make the user interface more user-friendly.

For example, in the existing application let's say that you want to make your clustering tool end up in the Clustering section in the Data menu, that is, next to all other clustering capabilities in the product. This is achieved by first adding a category tag in the resource.xml for that tool, and then removing the tool-ref tag in the application.xml file:

<title>Calculate XYZ</title>

<category>Spotfire.MenuGroup.Clustering</category>

The end result is that the tool shows up in the Clustering section of the Data menu:

As you noticed, you now define the menu location directly from the tool definition, not in the Application definition. This will make it easier for you to move a tool from one Application to another. You don't need to define the location again.

For more details on how to use the category names, see http://developer.spotfire.com/spotfire/dsd/use/am_layout_category.htm (login required)

Again, more details can be found on http://developer.spotfire.com

Let us know if you have any questions.

<< Hide extended entry

Posted by Martin Nilsson in Development | Permalink -

10:11 AM | Send to Friend | Trackback (0)

How to do Curve Fitting

With the DecisionSite 8.1 release just around the corner, I thought I would publish a quick note that highlights how to use the API for curve fitting and curve drawing.

This article will address the two most common questions I get around curve fitting. First it will show how to create standard a plot with a standard IC50 curve fit, and then how to draw custom curves.

Show extended entry >>

Posted by Martin Nilsson in Development | Permalink -

2:50 PM | Send to Friend | Trackback (0)

Spotfire's interactive information visualization and analytic solutions give users a remarkable experience for quickly and easily querying data and reporting results for superior business intelligence. From portfolio management and customer retention programs to key processes such as CRM, marketing, research, bioinformatics, yield and asset management and design for manufacturing, enterprises around the world rely on Spotfire's business analytics software to improve operational performance.