How to directly query the Spotfire database to find all references for a particular library object. 'Find All References' function.

How to directly query the Spotfire database to find all references for a particular library object. 'Find All References' function.

book

Article ID: KB0077738

calendar_today

Updated On:

Products Versions
Spotfire Server All Versions

Description

In the Information Designer, the function 'Find All References' allows you to see all references/dependencies on a particular Library object (Information Link, Column, Data Source). This is useful for ensuring that changes to a schema or information model are correctly managed and all dependent analyses can be properly updated. These references are stored in the Spotfire application database, and the database can also be queried directly, to facilitate a larger number of queries and export the data for external consumption.

Issue/Introduction

How to directly query the Spotfire database to find all references for a particular library object. 'Find All References' function.

Resolution

The Spotfire database contains all library content in the following folders starting with LIB_. 

 

Based upon these tables, the following example query (for Microsoft SQL Server) will return all dependencies for a particular object in the Spotfire Library whose unique GUID has been provided in the query's WHERE clause:

 select i.TITLE as [Item Name], i.ITEM_ID as [ITEM GUID], it.LABEL as [Item Type], parent.TITLE as [Parent Name], i.PARENT_ID as [Parent GUID] from LIB_RESOLVED_DEPEND rd join LIB_ITEMS i on i.ITEM_ID = rd.DEPENDENT_ID join LIB_ITEMS parent on parent.ITEM_ID = i.PARENT_ID join LIB_ITEM_TYPES it on it.TYPE_ID = i.ITEM_TYPE where REQUIRED_ID='fc28001a-e44b-4c99-999a-38d051fc0a58' -- Update with the desired GUID from your library or REQUIRED_ID in (select dependent_id from LIB_RESOLVED_DEPEND where REQUIRED_ID='fc28001a-e44b-4c99-999a-38d051fc0a58') -- Update with the desired GUID from your library

Before executing this query, update the GUID used in its WHERE clause (REQUIRED_ID='fc28001a-e44b-4c99-999a-38d051fc0a58') to match the GUID of the item you wish to investigate.  You can find an object's GUID by right clicking on the object in the Information Designer and selecting "Copy ID".

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.

Additional Information

 The key tables used in this particular query are:
  • LIB_ITEMS - A row for each object in the library
  • LIB_RESOLVED_DEPEND - Each object's dependencies on other objects
  • LIB_ITEM_TYPES - A translation of the ITEM_TYPE GUID to an English version (folder, analysis, column, etc.)