Function to show Universe Objects used by reports

[Former Member]

Is there a function in XI3.1 to see all of the reports in which a particular Object is being used?

I don't see any of the canned audit reports that would show this.

Example: I have a Universe Object called "Ins. Proper Name'' and I want to see how many of our reports are using this object.

Thanks,

Mike

[Former Member · Accepted solution]

Michael,

Are you looking for a list of all reports that use a specifc (eg. a table/column) object in a universe or all reports that use a specific universe.

I think Anil's query will list all reports attached to a specific universe.

Best,

Srinivas

[Former Member · Accepted solution]

Srinivas,

Yes, we want to produce a list of all Webi reports that are using a specific Universe Object/Table column.

Currently we manually look at each report using the Universe to determine if the Object is used.

Thanks for your response,

Mike

[Former Member · Accepted solution]

Mike,

What you need is possible via the Activity Universe presuming you have auditing enabled and (at least) the following 'Events to Audit' selected on all Webi Processing Servers:

- Universe Selected

- SQL Generated

I use such a report to determine which tables/colums are used in which reports.

Let me know if you'd like details on creating the report.

Best,

Srinivas

[Former Member · Accepted solution]

Srinivas,

I do use the Activity Universe and produce some Auditing reports. I'm not sure which table/column in the Activity Universe to use that will show the report Object used in the reports. I will need to check as you mentioned, to see if Auditing is enabled for all services.

Thanks,

Mike

[Former Member · Accepted solution]

Mike,

Here is what I use:

Result Objects:

-Universe Name from Actions/Action Details

-Action Type from Actions

-Document Name from Actions/Document Operation

-Action Time from Actions

-Action Type Detail from Action/Action Type

Query Filters:

-Action Time Between <Begin Date> and <End Date>

Report Filters:

-Action Type In List: SQL Value

Note: Action Type Detail contains the SQL for every query that is run within the date range provided. While the SQL contains the name of every query in every report, it does not use reference a universe object by name. i,e, to check whether a specific universe object exists in the report, you will have to search for the object's sql.

Also, I use an additional another query filter to return only objects that match a specific pattern.

Let me know if you need anything else.

Best,

Srinivas

[Former Member · Accepted solution]

Srinivas,

Thanks you so much for the detailed information. I will give this a try.

Thanks again,

Mike

[Former Member · Accepted solution]

You're quite welcome.

[Former Member · Accepted solution]

Hello, Srinivas.

I'm using SAP BI Platform 4.1.

I can't find the object  Action Type In List: SQL Value, you use in BO 3.1

Which is the equivalent object I have to use instead of it, please?

[Former Member]

Is there is any way to get report level filter details for all reports ? I need to get list of all reports where there is either block level /table level / report level filter is applied.

- Thanks

[Former Member]

Query the Universeu2019s Reports

SELECT si_id, si_name, si_webi, si_cuid FROM CI_AppObjects WHERE si_name = 'UNIVERSE_NAME' AND si_kind = 'Universe'

Take that list of report object IDs from the previous step and parse the list so that each ID is separated by a comma. Then substitute that list for the string u201C111111,222222,33333 below in the SQL:

SELECT si_id, si_name, si_universe, si_cuid FROM CI_InfoObjects WHERE si_id IN (111111,222222,33333) AND si_kind = 'WebI' AND si_instance = 0

Thanks,

Anil

[Former Member]

Anil,

Thank you for the reply.

Where can I find the CI_AppObjects and CI_InfoObjects tables, Universe's Reports?

Are they located on the CMS database?

Mike

[Former Member]

Micheal,

If its 3.1 , right click on Universe and Click ok "Check Relattionships" , it takes you to Query Result...

Thanks,

Anil

[Former Member]

OR else..go to admin tools and execute the above commands...

http://<servername>:<port no>/AdminTools

Thanks,

Anil

[Former Member]

Anil,

I was able to get to the Query Builder from the http path. I will run the queries to get results.

Thank you for the quick reply and answers,

Mike