How to get the list of analysis files along with their preview image using Information services.

How to get the list of analysis files along with their preview image using Information services.

This article briefly describes how you can get the list of analysis files along with their preview image by querying the Spotfire database using Information Link. The theme of this article is to fetch the preview image from the Spotfire database which is stored in the binary format. Steps:

• Setup a Data Source connected to Spotfire Database in Information Designer.
•  From the LIB_ITEMS table create a column element of TITLE column.
•  From the LIB_PROPERTIES table create a column element of PROPERTY_BLOB_VALUE column.
• Create a Join (Inner Join) on column ITEM_ID from both the tables LIB_ITEMS and LIB_PROPERTIES.
• Create a Filter element on the PROPERTY_NAME column from the LIB_PROPERTIES table with the expression: %1='Spotfire.Preview.Thumb'
• Create an Information Link with the above two column elements, filter element, and the join. The following is how the SQL looks like:

SELECT
   L1."TITLE" AS "TITLE",
   L2."PROPERTY_BLOB_VALUE" AS "PROPERTYBLOBVALUE"
FROM
   "TSS_10_3"."dbo"."LIB_ITEMS" L1,
   "TSS_10_3"."dbo"."LIB_PROPERTIES" L2
WHERE
   (L1."ITEM_ID" = L2."ITEM_ID")
   AND (L2."PROPERTY_NAME"='Spotfire.Preview.Thumb')
   AND <conditions>

 

• The PROPERTY_BLOB_VALUE is a base64encoded column in the binary format. Modify the SQL to convert this column to a string format as:

SELECT
   L1."TITLE" AS "TITLE",
   CONVERT(varChar(MAX),L2."PROPERTY_BLOB_VALUE") AS "PROPERTYBLOBVALUE"
FROM
   "TSS_10_3"."dbo"."LIB_ITEMS" L1,
   "TSS_10_3"."dbo"."LIB_PROPERTIES" L2
WHERE
   (L1."ITEM_ID" = L2."ITEM_ID")
   AND (L2."PROPERTY_NAME"='Spotfire.Preview.Thumb')
   AND <conditions>

 

• In the Information Link Elements, select the PROPERTY_BLOB_VALUE column element >> Edit >> Under Data Type dropdown, select String. This is to specify its data type.
• Here is how the final Information Link looks like in the Information Designer.

• Open this Information Link and create a calculated column with the following expression: 

Base64Decode([PROPERTY_BLOB_VALUE])

This column fetches the preview image as shown in the table visualization image below.


The function Base64Decode() decodes a base64 string into a binary large object so it can be viewed as an image in Spotfire.

NOTE: In the modified SQL, the column is converted to String using the syntax as per the SQL Server. Modify the query accordingly if the Spotfire database is on Oracle or PostgreSQL (Supported from 10.9 and onwards)

NOTE: The use case described in the article can also be achieved using a custom query in the Data Connection.

Disclaimer: The content of this article is for informational purposes only. The subject material may change in any new versions with no notice and there is no responsibility by TIBCO to maintain or support future access to this internal application content. Modification of any internal application content is not recommended and can lead to an unsupported configuration.  It is not intended to be used "As Is" in a Production environment. Always test in a development environment.