nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

DV Workbooks Usage Tracking

Subhakara Netala-Oracle

Hi,

We are trying to track DV Workbooks Usage. When searched with a workbook name we can see some extra names/characters after the name of the workbook. Please see below screenshot. Is there a way to get the only workbook name/ path. Before also raised a request with product team on this issue, till now there is no update. Is there a way we can track usage with workbook name (ex: BI Usage Tracking Report - testing). Not able to apply Sub-String as characters are very random.

Please let me know if anyone is tracking their DV Workbooks usage with Workbook name.

Find more posts tagged with

Analysis and Data Visualization

Accepted answers

Subhakara Netala-Oracle

This is the formula we use in RPD to get the workbook name,

CASE WHEN "WA UsageTracking".""."UT SA"."LOGICAL_QUERIES"."QUERY_SRC_CD" = 'Visual Analyzer' THEN

left(SUBSTRING("WA UsageTracking".""."UT SA"."LOGICAL_QUERIES"."SAW_SRC_PATH" FROM LOCATE('/', "WA UsageTracking".""."UT SA"."LOGICAL_QUERIES"."SAW_SRC_PATH" ,-1)+1), locate('"',SUBSTRING("WA UsageTracking".""."UT SA"."LOGICAL_QUERIES"."SAW_SRC_PATH" FROM LOCATE('/', "WA UsageTracking".""."UT SA"."LOGICAL_QUERIES"."SAW_SRC_PATH" ,-1)+1),1)-1)

END

Sharing it here, so that who wants they can take this as a reference for their requirement.

All comments

Gianni Ceresa

Hi,

It is maybe all obvious to you as you are looking at that thing for hours, but there isn't much context given.

What are you trying to do? Where? How? What tracking are you talking about?

PS: those names they look to me very well structured and easy to split to get just the right piece of info, therefore it sounds like something else is missing in your question to understand where the issue is.

Subhakara Netala-Oracle

This is for Usage Tracking. We want to create a report in Usage Tracking Subject area, however the Webcat path(SAW_SRC_PATH) has extra characters at the end in LOGICAL_QUERIES table as shown in the previous screenshot. This issue is only for DV related objects, no issue on Classic BI reports/dashboards paths. These are random, not sure why these are coming into the data. We want to remove those extra characters in the report as well as in the prompt so that users can view their workbooks usage details correctly.

Gianni Ceresa

Those extra info you see in the path is because a DV workbook is a bit the equivalent of a "dashboard": it has "pages" (canvases) and contains visualizations. Each single one generate different queries.

Therefore the path identify them in a unique way going deeper than just the workbook itself (a workbook could contains 100+ visualization generating queries, but if they are split in various pages only a fraction of them is accessed and executed and recorded in the usage tracking).

Just do a simple string manipulation to get the workbook path part and ignore the rest.

In what you posted, despite being truncated on the left, it looks like a the paths are very well separated from the rest: everything is quoted, just use that as rule to split the string.

Subhakara Netala-Oracle

We are trying to use string functions but not getting results as expected. We are working on that and keep posted here once we get proper solution.

Thanks.

BrandonH

Go directly to, and Index if necessary on start_dt: s_nq_acct

example substr replace:

SELECT

start_dt,

REPLACE (SUBSTR (saw_src_path, INSTR (saw_src_path, '/', -1) + 1),

'\n') report_name,

COUNT (*) total_run,

saw_src_path

FROM #########.s_nq_acct s

WHERE s.start_dt = TO_DATE ('2024-02-13', 'YYYY-MM-DD')

GROUP BY saw_src_path, start_dt

ORDER BY COUNT (*) DESC;

If you are looking at DV stuff specifically, consider specifically: "query_src_cd". There are a few to key in on depending what you are looking for:

and query_src_cd ='Visual Analyzer'

and query_src_cd ='data-prep'

Also, with the addition of DV, at some point it will become necessary to monitor #####_biplatform.BI_SERVER_ASYNC_JOB_STATUS similar to the rest of usage tracking.

Subhakara Netala-Oracle

This is the formula we use in RPD to get the workbook name,

CASE WHEN "WA UsageTracking".""."UT SA"."LOGICAL_QUERIES"."QUERY_SRC_CD" = 'Visual Analyzer' THEN

END

Sharing it here, so that who wants they can take this as a reference for their requirement.

Sumanth V -Oracle

@Subhakara Netala-Oracle

Thanks for sharing, so here you are picking the workbook name from SAW_SRC_PATH value directly from the Logical SQL query stored in Usage tracking tables. This is really helpful.

Subhakara Netala-Oracle

@Sumanth V -Oracle , we have a column SAW_SRC_PATH in LOGICAL_QUERIES(S_NQ_ACCT in-case of 0BIEE) Table. You can use that column to get the DV Workbook or Report name.

Michal Zima

This discussion is quite funny (but not that much, to be honest):

It is about finding/developing workarounds for something, which should be implemented in a product as such. Meaning correct tracking of DV logical queries in correct columns (designed for that purpose) - instead of trying to parse/decompose the correct information from "cryptic" content.

Just to give examples:

Have a separate column in UT table, which will give me just full path (in BI catalog) for DV workbook (instead having it to "extract" from SAW_SRC_PATH column) , potentially other columns should be used to store Canvas name + Visualization name (for which Logical SQL has been executed)
Have separate column in UT table for storing Data Set Name (ideally along with ID consisting of <<Namespace>>.<<Original/First Data Set Name>> also accompanying with Display Data Set name - which is seen in DV UI and can be completelly different from this Unique ID for DS) instead of having it extract from SUBJECT_AREA_NAME column

Is someone from Product Development watching this discussion ?

Gianni Ceresa

How to make something very different fit into something old.

There are generally 2 ways to maintain a kind of backward compatibility: extend the existing to add the new pieces of info needed in new columns, maintaining the existing ("classic") behavior as much as possible, or force things to fit in what is available in a way or another.

That's how a column that was meant to be a string with the path of a catalog object turned into a JSON object containing more than that.

It isn't a fully wrong approach to use JSON, but consistency is required: turn the "classic" usage tracking into JSON objects as well, even if they just have the "path" key and nothing else (because in "classic" each piece executing a single query is stored independently in the catalog).

The current solution is a half-half that doesn't really satisfy anyone: it does break backward compatibility because queries from DV requires a different processing that queries from "classic", and therefore adding new columns just for DV would have been easier and cleaner. But maybe more complicated for upgrades because somebody has to run an ALTER statement on their database?

By the way, the cleaner solution for the usage tracking data would be a view in the database on top of the usage tracking table making a union between "classic" usage tracking rows and DV usage tracking rows extracting the JSON values into separate columns. The processing of the JSON shouldn't be done in a logical column in the RPD or in an analysis/workbook. The database speaks JSON perfectly, the view handling the job is the easier, safer and better "long term" approach (until the usage tracking data change structure).

with s_nq_acct_json as (
  select saw_src_path
  , treat(saw_src_path as json) as saw_src_path_json
  from s_nq_acct
  where query_src_cd = 'Visual Analyzer'
)
select s.saw_src_path_json
, s.saw_src_path_json.path
, s.saw_src_path_json.viewID
, s.saw_src_path_json.currentCanvas
from s_nq_acct_json s;