this is the query for a workbook i use to get specific info on rel between 2 tables :-
query get_info
{
workbooks(filter:{name:"AppWorkbook-test"}){
name
upstreamDatabases
{
name
__typename
}
upstreamTables
{
name
}
viewsConnection{
nodes{
name
}
}
}
}
Actually the result are 2 views ( App LEFT JOIN Person table) - which i get in the above grapql output. However i need the relationship info between the 2 views.
It would be great if you can give specific instruction on graphql side to get this JOIN relationship which is a "LEFT JOIN"
PS: this was orginally a published extract that had these 2 views..i downloaded this workbook and took a local copy of the DS to get to the Venn diagram view( relationship view) and i re-published it hoping i would get this relationship info in graphiql output.
Also the underlying views are from Sql Server. Thanks in advance!!
Hi @Karthik Valluri ,
Unfortunately the metadata API does not hold this type of data, which is quite frustrating.
You can extract the relationships from the workbook XML instead. I suggest using the metadata API to extract the workbook id, and then use the rest API to download the workbook, finally leverage python or other similar language to extract the information from the workbook xml.
When looking for the place this data is stored in the workbook XML you need to navigate down to this XPATH "./datasources//datasource//_.fcp.ObjectModelEncapsulateLegacy.true...object-graph". Once you are at that XPATH you then need to use conditional logic for either the new datasource relationships (noodles), or the legacy joins (Like in your example).
For the legacy connections you look into XPATH ".//properties[@context=""]/relation[@join]" Within that XPATH there are then more paths that detail the joins.
Below is an example of a datasource XML with a legacy join. (note I have stripped out a lot of the XML to simplify the example).
In this example we have two tables, table_name_1 and table_name_2 and I am performing a left join from table_name_1 to table_name_2 on the Job Nbr field.
<datasources>
<datasource inline='true' name='Datasource Name' version='18.1'>
<_.fcp.ObjectModelEncapsulateLegacy.true...object-graph>
<objects>
<object caption='Migrated Data' id='Migrated Data'>
<properties context=''>
<relation join='left' type='join'>
<clause type='join'>
<expression op='='>
<expression op='[table_name_1].[Job Nbr]' />
<expression op='[table_name_2].[Job Nbr]' />
</expression>
</clause>
<relation connection='sqlserver.16ll0gx0pfxge21edz9430i76exv' name='table_name_1' table='[ssdi].[table_name_1]' type='table' />
<relation connection='sqlserver.16ll0gx0pfxge21edz9430i76exv' name='table_name_2' table='[ssdi].[table_name_2]' type='table' />
</relation>
</properties>
<properties context='extract'>
<relation name='Extract' table='[Extract].[Extract]' type='table' />
</properties>
</object>
</objects>
</_.fcp.ObjectModelEncapsulateLegacy.true...object-graph>
</datasource>
</datasources>
No that the above is a simple example, and there is much more nesting where multiple joins are present
