How to get list of reports who are using Learning Managerment Real Time subject area.

Sohel.IN-Oracle

As oracle has replaced Learning Management Real Time subject area with Learning Records Real Time subject area and in future release Learning Management Real Time subject area will not be available. we need to move existing OTBI Reports on to new subject area Learning Records Real Time. Anyone know how I can fetch list of custom reports and their path which are using Learning Management Real Time subject area.

Praveen Kumar Akkala-Oracle

Hello @Sohel.IN-Oracle ,

There is no audit report / table that holds the list of custom reports and their path which are using any specific subject area.

You can try using the "OTBI Performance Real Time" subject area to create a report as below which gives report name (not the path), subject area name etc.

SELECT
0 s_0,
"OTBI Performance Real Time"."OTBI Report"."Report Name" s_1,
"OTBI Performance Real Time"."OTBI Report"."Subject Area Name" s_2
FROM "OTBI Performance Real Time"
WHERE
("OTBI Report"."Subject Area Name" = 'Workforce Learning - Learning Management Real Time')

Thanks,

Praveen

Nathan CCC

Sohel,

As suggested by Praveen for analysis (blue) in OTBI (not reports (yellow)) the method using the usage tracking tables S_NQ_ ACCT/DB_ACCT in the 2 usage tracking subject areas will work if all the analysis were executed by someone at least once. It has the path to the analysis/dashboard prompt etc and the subject area used along with the logical sql. For more information see Usage tracking https://docs.oracle.com/middleware/12213/biee/BIESG/GUID-9F8FCB4B-EA17-4302-813A-BEF84F295A6A.htm

But this bi server metadata repository database usage tracking does NOT include reports in OTBI. For reports (yellow) in OTBI that you have built using a data model with data source obiee with subject areas that use the logical sql rather than a reference to an analysis then you will need to look elsewhere for which reports use a subject area. If you did indeed mean a report then you need to enable AuditViewDB for usage tracking of reports. But I dont think this will have subject area information. But I have a suspicion that when you said report in otbi you probably actually meant as analysis in otbi so that is moot.

BTW There is a client tool in BI called catalog manager that you can run from the host server or your local machine to read the catalog either online or offline to get reports of which analysis (not report) uses which subject area (whether someone has executed it or not). But because OTBI is SaaS you are not allowed to connect to the host middleware server nor connect to that server as administrator from your local machine to use it. But as a workaround you could archive your whole catalog out of OTBI then put it into an empty standalone platform OBI that you have lying around like on a VM or something - then use catalog manager to report on that snapshot of your catalog. For more information see About Catalog Manager https://docs.oracle.com/middleware/12213/biee/BIESG/GUID-FF29B794-97F5-4975-A924-ED40319B9150.htm#GUID-5D4B18E5-DD56-4319-994E-F612C34CF7B5

GMD01

@Praveen Kumar Akkala-Oracle is there a way to use something similar to the above sql to access subject areas folders that are a level down or two levels down

for example a list of all OTBI reports that use Workforce Management - Person Real Time - Person - Person extra information - population ID

Nathan CCC

GDunlap,

Here is a query to get are all the query that were executed from subject areas in the metadata repository database from analysis or prompts etc that included in their query a particular presentation column from a particular subject area.

select all 0 s_0

, "OTBI Report"."Report Name" as object_name

, "OTBI Report"."Report Path" as catalog_path

, "OTBI Report"."Subject Area Name" as subject_area

, DESCRIPTOR_IDOF("OTBI Report"."Query Text") as query_code

, "OTBI Report"."Query Text" as query_text

from "OTBI Usage Real Time"

where "Subject Area Name" = 'Workforce Management - Person Real Time'

and "OTBI Report"."Query Text" LIKE '%"Worker"."Person ID"%'

fetch first 7 rows only

BTW These are NOT reports in OTBI. Reports in OTBI are yellow. .

GMD01

@Nathan CCC

I thought the yellow were BIP reports? It is the other way around? Good to know-I MAY have been saying that wrong for 6 months now lol

Either way THANK YOU!! I see where I was going wrong trying to string it all together. That last line was the magic bullet. I appreciate it.

Nathan CCC

Yes OTBI report = BIP report. A report in OTBI is yellow. It is a yellow file in the catalog / on the create menu in OTBI. A report in OTBI is a business intelligence publisher (BIP) report. BIP is just one of the suite of products in platform OBI in OTBI in Oracle Fusion Cloud Applications. The other products in the suite of products include answers (analysis) and dashboards and prompts (blue), agents (green) etc.

GMD01

I generally call them analysis but thank you for that explanation-I need to get a better understanding of that. I usually only work in the analysis area but we have been relying on another group to provide us with this type of report when we need to make updates based on existing fields in an analysis.

Praveen Kumar Akkala-Oracle

Hello @GDunlap,

In Fusion SaaS Environments,

Yellow are Fusion BIP Reports

Blue are Fusion OTBI Reports called OTBI Analysis.

Thanks,

Praveen

Nathan CCC

Hi Praveen,

Please refer to the oracle part numbers and licencing documents etc.

• Yellow are reports in OTBI. The object you create in the catalog in OTBI is a "report". Yellow are reports in OTBI from product Oracle Business Intelligence Publisher (BIP) which is one of the many products in suite of products in platform Oracle Business Intelligence suite Enterprise Edition in Oracle Transactional Business Intelligence (OTBI) in Oracle Fusion Cloud Applications (SaaS). BIP is part of OTBI.

• Blue are analysis in OTBI. The object you create in the catalog in OTBI is an "analysis". Blue are analysis in OTBI from product Oracle Business Intelligence Answers which is one of the many products in suite of products in platform Oracle Business Intelligence suite Enterprise Edition in Oracle Transactional Business Intelligence (OTBI) in Oracle Fusion Cloud Applications (SaaS). An OTBI analysis is NOT an OTBI report.