Trying to filter on specific Worksheets for User Adoption liveboards

[eckhami]

Hi,
the User Adoption Liveboards which are deployed through CS Tools are already offering a lot of insight.
However it is not possible to see, which Worksheet is being used by certain users, through Liveboards and Answers.

I have been thinking to create a view using TS_METADATA_OBJECT, TS_METADATA_COLUMN and TS_DEPENDENT_OBJECT.
The tricky part in my opinion is that for one row in TS_METADATA_OBJECT there are multiple entries in TS_METADATA_COLUMN (as each object can have one or more columns obviously) and for each row in TS_METADATA_COLUMN I would find multiple records in TS_DEPENDENT_OBJECT.

However, by using a distinct I was hoping to create a 1-1 relationship, as each Worksheet has a fixed set of columns and I just need to find the way from an answer to the worksheet (going through the COLUMN_GUID).
So I came up with below query, which should tell me, which worksheet is being used by a TS_METADATA_OBJECT:

select distinct
    ts_dependent_object.cluster_guid,
    ts_dependent_object.dependent_guid, --as answer_book_guid,
    ts_metadata_column.object_guid,     --as parent_guid,
    ts_metadata_object.name               as parent_name,
    ts_metadata_object.object_subtype     as parent_subtype
from ts_dependent_object
left join ts_metadata_column
on ts_dependent_object.cluster_guid = ts_metadata_column.cluster_guid
and ts_dependent_object.column_guid = ts_metadata_column.column_guid  
left join ts_metadata_object
on ts_metadata_column.cluster_guid = ts_metadata_object.cluster_guid
and ts_metadata_column.object_guid = ts_metadata_object.object_guid

this I joined to TS_METADATA_OBJECT through DEPENDENT_GUID to see the worksheet below. however I seem to miss something, as the results do not tie out.

Is there anything I missed?

Thanks
Michael

[boonhapus]

Hey @eckhami

However it is not possible to see, which Worksheet is being used by certain users, through Liveboards and Answers.

So this is definitely possible -- but it does come with some caveats. As you said, the relationships exist between DEPENDENT > COLUMN > OBJECT. A table is a collection of columns , and columns can be used in many different visualizations. So we find that there are many dependents (Answer, Liveboards, Worksheets) for a given Column, and there are many Columns for a given Object (Table, View, Worksheet).

The TS_BI_SERVER table contains a column called ANSWER_BOOK_GUID , which can be related to DEPENDENT on the dependent_guid. Really the dependents table is a subtype of METADATA_OBJECT. We've built it this way so it's simpler to consume.

Now, for the caveats. 😄

However it is not possible to see, which Worksheet is being used by certain users, through Liveboards and Answers.

TS_BI_SERVER as a data source (from the ThoughtSpot platform) does not attribute Users Actions to a Worksheet. It only attributes actions to Users on a given Visualization + Visualization "parent" (Answer/Liveboard). So while the Liveboard we offer in CS Tools shows Searches, which may be filtered by Worksheets.. what's really being shown is searches on an Answer (who has a parent Worksheet).

If I wanted to show the relationships between this information (Worksheet -> Dependents) , this is the kind of query I would write.

SELECT 
  "ta_1"."OBJECT_TYPE" "ca_1", 
  "ta_1"."NAME" "ca_2", 
  "ta_2"."COLUMN_NAME" "ca_3", 
  "ta_2"."IS_FORMULA" "ca_4", 
  count(DISTINCT "ta_3"."NAME") "ca_5"
FROM "SNOWFLAKE_DB"."CS_TOOLS_V150"."TS_DEPENDENT_OBJECT" "ta_3"
  RIGHT OUTER JOIN "SNOWFLAKE_DB"."CS_TOOLS_V150"."TS_METADATA_COLUMN" "ta_2"
    ON (
      "ta_3"."COLUMN_GUID" = "ta_2"."COLUMN_GUID"
      AND "ta_3"."CLUSTER_GUID" = "ta_2"."CLUSTER_GUID"
    )
  RIGHT OUTER JOIN "SNOWFLAKE_DB"."CS_TOOLS_V150"."TS_METADATA_OBJECT" "ta_1"
    ON (
      "ta_2"."CLUSTER_GUID" = "ta_1"."CLUSTER_GUID"
      AND "ta_2"."OBJECT_GUID" = "ta_1"."OBJECT_GUID"
    )
WHERE LOWER("ta_1"."NAME") = '(sample) retail - apparel'
GROUP BY 
  "ca_1", 
  "ca_2", 
  "ca_3", 
  "ca_4"

[boonhapus]

Ah -- one more thing. I didn't realize you asked about Users. Here's the above query with Users as well.

SELECT 
  "ta_1"."OBJECT_TYPE" "ca_1", 
  "ta_1"."NAME" "ca_2", 
  "ta_2"."COLUMN_NAME" "ca_3", 
  "ta_2"."IS_FORMULA" "ca_4", 
  count(DISTINCT "ta_3"."NAME") "ca_5", 
  count(DISTINCT "ta_4"."USER_ID") "ca_6"
FROM "SNOWFLAKE_DB"."CS_TOOLS_V150"."TS_BI_SERVER" "ta_4"
  LEFT OUTER JOIN "SNOWFLAKE_DB"."CS_TOOLS_V150"."TS_DEPENDENT_OBJECT" "ta_3"
    ON (
      "ta_4"."CLUSTER_GUID" = "ta_3"."CLUSTER_GUID"
      AND "ta_4"."ANSWER_BOOK_GUID" = "ta_3"."DEPENDENT_GUID"
    )
  RIGHT OUTER JOIN "SNOWFLAKE_DB"."CS_TOOLS_V150"."TS_METADATA_COLUMN" "ta_2"
    ON (
      "ta_3"."COLUMN_GUID" = "ta_2"."COLUMN_GUID"
      AND "ta_3"."CLUSTER_GUID" = "ta_2"."CLUSTER_GUID"
    )
  RIGHT OUTER JOIN "SNOWFLAKE_DB"."CS_TOOLS_V150"."TS_METADATA_OBJECT" "ta_1"
    ON (
      "ta_2"."CLUSTER_GUID" = "ta_1"."CLUSTER_GUID"
      AND "ta_2"."OBJECT_GUID" = "ta_1"."OBJECT_GUID"
    )
WHERE LOWER("ta_1"."NAME") = '(sample) retail - apparel'
GROUP BY 
  "ca_1", 
  "ca_2", 
  "ca_3", 
  "ca_4"

[eckhami]

Thanks for all the information!
Unfortunately I did not yet have time to go through all of it yet, but there might be a slight misunderstanding though.
I am not so much interested in the worksheets being used, but rather I want to filter the User Adoption Liveboards on those data only, which are going back to certain Worksheets. Meaning to see all the Liveboards created, but filtered on specific underlying Worksheets...

I will still go through your code and see, if this helps me out.

Thanks

[boonhapus]

Makes sense @eckhami , this is exactly what the Worksheet Column Utilization worksheet is intended to solve.

Here's the schema from that Worksheet.

Let me know how I can help!

[eckhami]

Hi,
with some help from the Thoughtspot team I found a possible solution to my question:
I need to be able to filter 'CS Tools ~ BI Server Advanced' to see results based on a specific subset of Worksheets.

First step was to see the relation of "Parent Worksheet" to Liveboards and answers associated with it. this was done through worksheet "CS Tools ~ Worksheet Column Utilisation"

From here I could extract the underlying SQL, which I then transferred into this code and saved as a view in ThoughtSpot:

SELECT 
  ta_2.cluster_guid,
  ta_2.dependent_guid,
  ta_1.object_guid,
  ta_1.NAME parent_name, --WORKSHEET
  CASE
    WHEN ta_2.OBJECT_TYPE = 'QUESTION_ANSWER_BOOK' THEN 'ANSWER'
    ELSE CASE
      WHEN ta_2.OBJECT_TYPE = 'PINBOARD_ANSWER_BOOK' THEN 'LIVEBOARD'
      ELSE null
    END
  END object_subtype, --DEPENDENT_TYPE, 
FROM TS_DEPENDENT_OBJECT ta_2
  RIGHT OUTER JOIN TS_METADATA_COLUMN MTA_0
    ON (
      ta_2.COLUMN_GUID = MTA_0.COLUMN_GUID
      AND ta_2.CLUSTER_GUID = MTA_0.CLUSTER_GUID
    )
  RIGHT OUTER JOIN TS_METADATA_OBJECT ta_1
    ON (
      MTA_0.CLUSTER_GUID = ta_1.CLUSTER_GUID
      AND MTA_0.OBJECT_GUID = ta_1.OBJECT_GUID
    )
WHERE (
  ta_2.object_type in ('QUESTION_ANSWER_BOOK', 'PINBOARD_ANSWER_BOOK') 
  AND (
    ta_1.OBJECT_TYPE = 'LOGICAL_TABLE'
    AND ta_1.OBJECT_SUBTYPE IN (
      'WORKSHEET', 'MODEL'
    )
  ) IN (TRUE)
)
GROUP BY all 

In (a copy of) "CS Tools ~ BI Server Advanced" I added the view with below Link definition:

Now I am able to filter the content of a Liveboard on specific Worksheets (in my case I called it "Parent Name"):

Please let me know if I missed anything.
And thanks for all the help.

[boonhapus]

Excellent! Glad it worked out in the end @eckhami !