Viz Id Missing in CS Tools Data Extract

[tdasmoh]

Hi team,

We're trying to work on a linkage between each query fired into Google BQ each time a TS interaction is made and link it back to the exact visualisation which triggered it. Our approach to this is that we currently have the bi_server data available to us via CS tools. We are also being provided an information schema for all the queries with data populated by TS. If we could link bi_server data to the information schema data, then we believe it will give us the traceable linkage we need.

However, the bi_server data extracted using does not have the viz_id column populated. We are under the assumption that the cs_tools bi server gives us the copy of the actual BI server maintained by TS. If this is the case, then is it also Null in the TS bi server? If not then would it be possible to have it exposed in the cs tools data?

Looking forward to your response
Thanks!

[boonhapus]

That's strange @tdasmoh -- the Viz GUID column in the Model seems to be populating on our internal clusters. Do you find the same issue on the column in TS: BI Server Worksheet vendored by ThoughtSpot?

One thing to note is that the searchable bi-server command runs in --compact mode by default, which will skip User Action = invalid and User Action = {null}. So it will not identically reflect what's in ThoughtSpot itself. These are unclassified events / actions and most customers over the years have added these filters automatically.

---

However, what you're trying to do is going to be very difficult. This is because TS: BI Server is an activity log of API calls (system, private, and public ones made by users), and will not align well to the query data you find in BQ.

If the goal is traceability between ThoughtSpot and BQ, ThoughtSpot has an alternate Model that should align identically to BQ, called Query Stats. With the extractor tool, you could extract data from this table much like we do in the Searchable model.

I wrote a gist for this in Snowflake a few years back, the principles are the same as this method -- as this Query Stats model in ThoughtSpot is the table version of this query comment.

[tdasmoh]

Hi @boonhapus
Thanks for your response!
Had a look at the viz id column in TS: BI Server Worksheet and on our end only some of the viz ids are populated per answer book guid.
Also had a look at Query Stats and looks like the information schema i was talking about is actually populated from Query Stats data.
The main issue our engineering team is facing is that they are having access to the viz id from each query being fired but they have no link back to the exact answer or chart visualisation in the liveboards which is what they want.

Is there a way to link the data given in query stats to the object/dependent object metadata files we are extracting using the searchable tool?

Ideal end goal is that from the Query Stats data we have access to, we can identify the answer search or chart which fired the query by name or id.
Having this observability is important at this grain for us because if we run into an instance of our allocated compute slots on BigQuery being filled up (causing the platform to come slow to a crawl) with bad self-service queries it becomes far easier to identify root cause and address it quickly.

[boonhapus]

Totally makes sense @tdasmoh -- at the moment, the Searchable data model does not track down to the viz_id level. TS_METADATA_OBJECT can track to the object level (Answer, Liveboard) but visualizations are a sub-object level.

There are a few options you can try.

1. Use extractor to mirror the TS: Query Stats Worksheet over to BQ , much like we do for searchable bi-server. This should give you the viz-id to object id (context.id = TS_METADATA_OBJECT.object_guid) lookup you're seeking, which you can then join to TS_METADATA_OBJECT to get an audit trail.

2. Parse the BQ logs directly (similar to this gist) and begin snapshotting TML with --format-type JSON with the new searchable tml command, which will give you an ability to parse out all the viz ids using JSON functions.

3. Call POST /api/rest/2.0/metadata/search ? include_visualization_headers = true , convert this to a tabular result and store it in BQ to join to the rest of the searchable data model ala 1 or 2. This would still require you to parse the BQ logs in order to match that query data directly to the rest of the ThoughtSpot object graph.

Unfortunately each of these are pretty involved, but at the moment ThoughtSpot doesn't not provide any straightfoward ways to tie this lineage together.

[tdasmoh]

Thanks for your help @boonhapus!
These methods look quite helpful. I'll look into these more when I get a chance and reach out