How to get the list of Columns, Tables and Views used in Spotfire?

Spotfire Objects List

Introduction

In this document, I am trying to get the list columns; Tables and views used in Spotfire by report wise and build a dashboard to show these details.

Implementation:

Prerequisite:

1. You should have access to Spotfire database.
2. Access to Spotfire.

In this document, I am referring Sql Server database. If you have Oracle, please change the logic accordingly.   

SQL Server:

All Spotfire library details stores in Spotfire_server database. We can able to get all objects list by quarrying the below tables LIB_ITEMS, LIB_RESOLVED_DEPEND, LIB_PROPERTIES tables.

Please find the query to get the list of objects by report.

Note: Give a version name based on your environment.

USE [spotfire_server]
GO

/****** Object:  View [dbo].[SPOTFIRE_REPORTS_OBJECTS_VW]     ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Description:  <Description, we created this view To get the list of objects used in Spotfire reports. This View will returns the list of views/Tables used in Spotfire report.,>

create view [dbo].[SPOTFIRE_REPORTS_OBJECTS_VW] as

SELECT distinct
       Upper(SchemaName.ReportName) ReportName,
       Upper(DatabaseName.Property_Value) DatabaseName,
       Upper(SchemaName.Property_Value) SchemaName,
       Upper(TableName.Property_Value) TableName,
       Upper(ColumnName.Property_Value) ColumnName
FROM  
( SELECT A.ReportName, D.* FROM (SELECT TITLE ReportName, ITEM_ID 
         ReportItemID
    FROM lib_items
   WHERE FORMAT_VERSION = '7.9;7.8;7.7') A

 JOIN LIB_RESOLVED_DEPEND B ON A.ReportItemID = B.DEPENDENT_ID
 JOIN LIB_RESOLVED_DEPEND C ON B.REQUIRED_ID  = C.DEPENDENT_ID
 JOIN LIB_PROPERTIES      D ON D.ITEM_ID      = C.REQUIRED_ID

WHERE D.PROPERTY_NAME = 'table') TableName

INNER JOIN (SELECT A.ReportName, D.* 
 FROM (SELECT TITLE ReportName, ITEM_ID ReportItemID
        FROM lib_items 
       WHERE FORMAT_VERSION = '7.9;7.8;7.7') A
 JOIN LIB_RESOLVED_DEPEND B ON A.ReportItemID = B.DEPENDENT_ID

 JOIN LIB_RESOLVED_DEPEND C ON B.REQUIRED_ID = C.DEPENDENT_ID

 JOIN LIB_PROPERTIES D ON D.ITEM_ID = C.REQUIRED_ID

WHERE D.PROPERTY_NAME = 'column') ColumnName
   ON TableName.ITEM_ID    = ColumnName.ITEM_ID AND 
      TableName.ReportName = ColumnName.ReportName

INNER JOIN (SELECT A.ReportName, D.* 
 FROM (SELECT TITLE ReportName, ITEM_ID ReportItemID
 FROM lib_items WHERE FORMAT_VERSION = '7.9;7.8;7.7') A

 JOIN LIB_RESOLVED_DEPEND B ON A.ReportItemID = B.DEPENDENT_ID
 JOIN LIB_RESOLVED_DEPEND C ON B.REQUIRED_ID = C.DEPENDENT_ID
 JOIN LIB_PROPERTIES D ON D.ITEM_ID = C.REQUIRED_ID
WHERE D.PROPERTY_NAME = 'catalog') DatabaseName

   ON ColumnName.ITEM_ID      = DatabaseName.ITEM_ID AND
      DatabaseName.ReportName = ColumnName.ReportName

INNER JOIN ( SELECT A.ReportName, D.*
               FROM ( SELECT TITLE ReportName, ITEM_ID ReportItemID
                        FROM lib_items 
                       WHERE FORMAT_VERSION = '7.9;7.8;7.7') A

 JOIN LIB_RESOLVED_DEPEND B ON A.ReportItemID = B.DEPENDENT_ID
 JOIN LIB_RESOLVED_DEPEND C ON B.REQUIRED_ID = C.DEPENDENT_ID
 JOIN LIB_PROPERTIES D ON D.ITEM_ID = C.REQUIRED_ID
WHERE D.PROPERTY_NAME = 'schema') SchemaName
   ON DatabaseName.ITEM_ID    = SchemaName.ITEM_ID AND
      DatabaseName.ReportName = SchemaName.ReportName
GO

You can create an Information Link by using above query and create a report to see the details in Spotfire. when every we add any elements it will capture.

If you have any questions, please drop me an email.

Regards,

Surendra

kvsurendrareddy@gmail.com