How to query the information links contained in each template in the database?

[Mark123]

Nice weekends,every one

    Now, I can select all information of item_type='dxp' or item_type='query' through SQL in SQL Server.

    

   But I don't know how to correspond the two, that is, which information links each template corresponds to.

   Is there any way or certain field to achieve it?

[David Boot-Olazabal]

Hi Mark,

Not sure if I understand your question correctly.
Do you mean that you want to know, which information link is related to item_type=query or item_type=dxp?

Or do you mean something else? In that case, can you elaborate a bit more what you are trying to achieve?

Kind regards,

David

[Mark123]

42 minutes ago, David Boot-Olazabal said:

Hi Mark,

Not sure if I understand your question correctly.
Do you mean that you want to know, which information link is related to item_type=query or item_type=dxp?

Or do you mean something else? In that case, can you elaborate a bit more what you are trying to achieve?

Kind regards,

David

Thank you for your reply.

There are multiple templates in the library, and each template data comes from multiple information links.
How to select each template and its corresponding information link in SpotfireDB.
As shown in the following figure:

[David Boot-Olazabal]

Hi Mark,

What is a template in this scenario? Is that an analysis file, used to cater for multiple analysis files?

Kind regards,

David

[Mark123]

Yes.A DXP file.For example:Sales and Markrting.dxp

[David Boot-Olazabal]

Hi Mark,

Right, so you want to see which information links are connected to a dxp file.
You can try this query:
 

SELECT 
lib1.ITEM_ID,
lib1.TITLE,
lib1.DESCRIPTION,
lib1.ITEM_TYPE,
lib2.TITLE as IL
FROM	
[spotfire_server144].[dbo].[LIB_RESOLVED_DEPEND] libdep 
join [spotfire_server144].[dbo].[LIB_ITEMS] Lib1 on libdep.DEPENDENT_ID = lib1.ITEM_ID
join [spotfire_server144].[dbo].[LIB_ITEMS] Lib2 on lib2.ITEM_ID = libdep.REQUIRED_ID
WHERE 
lib1.ITEM_TYPE = (SELECT TYPE_ID FROM LIB_ITEM_TYPES WHERE LABEL in ('dxp'))
and lib1.Title like 'Bikes%'
and lib2.ITEM_TYPE = (SELECT TYPE_ID FROM LIB_ITEM_TYPES WHERE LABEL in ('query'))
ORDER BY
lib1.ITEM_TYPE, lib1.TITLE

In my example, I have a dashboard that starts with the name Bikes (you can either try this with one of your dxp title names, or just remove that 'Where clause' part if you want all dxp's to be listed).
The result of the above query is (there is only 1 information link used in my Bikes demo analysis file):

Kind regards,

David

[Olivier Keugue Tadaa]

Hi Mark, if you are comfortable with IronPython, this script will provide you with different types of dependencies.

Moreover, it will survive all future Spotfire versions since it is based on the public API.

loadcontent2.py

[Mark123]

15 hours ago, Olivier Keugue Tadaa said:

Hi Mark, if you are comfortable with IronPython, this script will provide you with different types of dependencies.

Moreover, it will survive all future Spotfire versions since it is based on the public API.

loadcontent2.py 15.41 kB · 0 downloads

Thank you for your help.But But the client requires us to use SQL.

[Mark123]

18 hours ago, David Boot-Olazabal said:

Hi Mark,

Right, so you want to see which information links are connected to a dxp file.
You can try this query:
 

SELECT 
lib1.ITEM_ID,
lib1.TITLE,
lib1.DESCRIPTION,
lib1.ITEM_TYPE,
lib2.TITLE as IL
FROM	
[spotfire_server144].[dbo].[LIB_RESOLVED_DEPEND] libdep 
join [spotfire_server144].[dbo].[LIB_ITEMS] Lib1 on libdep.DEPENDENT_ID = lib1.ITEM_ID
join [spotfire_server144].[dbo].[LIB_ITEMS] Lib2 on lib2.ITEM_ID = libdep.REQUIRED_ID
WHERE 
lib1.ITEM_TYPE = (SELECT TYPE_ID FROM LIB_ITEM_TYPES WHERE LABEL in ('dxp'))
and lib1.Title like 'Bikes%'
and lib2.ITEM_TYPE = (SELECT TYPE_ID FROM LIB_ITEM_TYPES WHERE LABEL in ('query'))
ORDER BY
lib1.ITEM_TYPE, lib1.TITLE

In my example, I have a dashboard that starts with the name Bikes (you can either try this with one of your dxp title names, or just remove that 'Where clause' part if you want all dxp's to be listed).
The result of the above query is (there is only 1 information link used in my Bikes demo analysis file):

Kind regards,

David

Something is confusing.

I have chosen a template named ‘控制模版’ that contains multiple information links, but the SQL query you provided only yielded one result.

[Mark123]

In addition, when querying all templates, the results are not displayed completely.

I'd like to know the meanings of field DEPENDENT_ID and REQUIRED_ID in view LIB_RESOLVED_DEPEND.

[David Boot-Olazabal]

Hi Mark,

That is strange, because when I run this query on a dashboard 'user_actions' (which I have three times in my repo in various folders/forms), I get this result back:

You may also want to checkout this article, where the lib_resolved_depend table is a bit explained as well giving you another way of querying the Spotfire database: https://support.tibco.com/s/article/How-to-query-Spotfire-database-to-get-the-item-names-on-which-a-particular-analysis-file-depends-on (in fact, searching for lib_resolved_depend on https://support.tibco.com will present you with more queries that may be of use).

This is the output of that (smaller query), when I use the highlighted ITEM_ID as input (the TITLE column holds the name of the Information Links, the DISPLAY_NAME is the item type):

Kind regards,

David

[Mark123]

Thank you for your patience.

I may have found the problem that some information links in the templates have been abandoned.

I will look for a few more templates to confirm.And I guess there shouldn't be any problem.
Thank you again for your help.