How to search in spotfire library the Datasource Name, Schema Name and Schema table Name associated to specific IL with the help of Ironpython script.

[Shailendra Tripathi 2]

How to search in spotfire library the Datasource Name, Schema Name and Schema table Name associated to specific IL with the help of Ironpython script.

[Fredrik Rosell]

Hello!

If you are still looking for this information, the following could be a starting point for doing it.

In short:

Given the path to an Information Link (IL),

I searched the library (LibraryManager.Search) for all items required by the Information link, using

required_by(item_id::<InformationLinkGUID>)

Then I listed the various properties of those hits.

Refer to https://docs.tibco.com/pub/sfire-analyst/12.4.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/index.htm#t=lib%2Flib_searching_the_library.htm&rhsearch=search&rhhlterm=search&rhsyns=%20 for more information on the search syntax.

 

Refer to the Spotfire API documentation for more information on the various methods used, such as

https://docs.tibco.com/pub/doc_remote/sfire_dev/area/doc/api/tib_sfire-analyst_api/html/T_Spotfire_Dxp_Framework_Library_LibraryManager.htm

# Import namespaces
from Spotfire.Dxp.Framework.Library import *
 
#ilID = "50db9f7e-d0f4-4080-a33c-fe5ff04f8d99"
#ilPath = "Information Model/SQLServer2019_fro04/dbo/USERS2"
ilPath = Document.Properties["propILPath"]
result = ""
 
libraryManager = Application.GetService[LibraryManager]();
 
#There are two options - look up LibraryItem from GUID or Path 
#TryGetItem(Guid, LibraryItem,LibraryItemRetrievalOption[])
#TryGetItem(String, LibraryItemType, LibraryItem,LibraryItemRetrievalOption[]) 
 
#Get the IL Item
(foundItem, ilItem) = libraryManager.TryGetItem(ilPath, LibraryItemType.InformationLink, LibraryItemRetrievalOption.IncludeProperties)
 
if foundItem:
	ilID= str(ilItem.Id)
	result += "Found the given Library Item. It has Id = " + ilID
	
	hits = libraryManager.Search("required_by(item_id::" + ilID + ")",LibraryItemRetrievalOption.IncludeProperties)
	
	result+="Number of required items for the IL = " + str(hits.Count)
	
	#Data Sources
	print ("Data Sources")
	print ("============")
	for hit in hits:
		if hit.ItemType == LibraryItemType.DataSource:
			print "Data Source = " + str(hit.Title)
	print ""
	
	#Columns
	print ("Columns")
	print ("============")
	for hit in hits:
		if hit.ItemType == LibraryItemType.Column:
			print "Column = " + str(hit.Title)
			print "======"
			properties = hit.Properties
			for prop in properties:
				print prop.Name
				for value in prop.GetEnumerator():
            				print value
				print ""
			print "-----"	
 
else:
	result+="No item found"
 
Document.Properties["propILLookupResults"] = result

Example output:

Data Sources

============

Data Source = MyDataSource

Columns

============

Column = LAST_MODIFIED_MEMBERSHIP

======

schema

dbo

datatype

datetime

catalog

spotfire_server

column

LAST_MODIFIED_MEMBERSHIP

table

USERS

-----

Column = FIXED

======

schema

dbo

datatype

integer

catalog

spotfire_server

column

FIXED

table

USERS

[Shailendra Tripathi 2]

Thanks for the solution. This works additionally I have one more query, Is it possible to search and pull sql query of information link from library.

[Shailendra Tripathi 2]

With the help of above code, I could pull the properties of Joins as well. But unable to build logic using those property values. Such as schema/table/fields inner join schema/table/fields. something similar to this. is it possible? please help!