Retrieve the list of universe objects (dimensions and measures) used in WebI queries - WebI SDK?

[former_member341172]

Hi Experts,

Can you please share the information how to retrieve the list of WebI documents with all the queries and the list of dimensions and measures used in those queries?

Having this information would help to identify the objects in the universe that are not used or calculate the impact when changing the object.

I would like to get a list similar as shown below (WebI report, Query, Query Objects):

*IDs could be used instead of names.

Do you know a way how to do that? I believe the new CMS DB driver (from BO 4.2 SP03 onwards) is not able to retrieve this kind of information and SAP BO WebI SDK should be used instead. However, it would be nice to know what the SAP BI experts thinks about it.

Please share your comments, links to blogs, threads, notes or anything else that would be relevant.

It would be great if you could share your experience (SDK code, etc.) how did you implement the solution for the following question.

I believe this might be relevant for many SAP BI admins.

Thank you very much in advance,

Vytautas Thienel

[Joe_Peters]

If you have auditing enabled, then this information will be captured for Refresh events. It will be in ads_event_detail, with an event detail type of "Universe Object Name".

It's not perfect, in that it only contains the object name, and not its full path. But if you're looking for activity for a specific object, it can be handy.

[former_member230921]

Using REST SDK you will get it.

Please try this using any REST client (Browser plugins like: Postman, RestClient...).

Please refer this blog for taking session token:

https://blogs.sap.com/2017/04/21/session-management-in-bi-platform-rest-sdk-rws/

Get data providers of a WebI document

This URL returns data provider list of a given WebI document specified in the URL parameter.

URL:

[GET] <url>/documents/{documentId}/dataproviders

Response type:

application/xml or application/json

Parameters:

documentId: a valid identifier of a WebI document

Example (JSON format):

[GET] <url>/documents/7007/dataproviders

Response

{"dataproviders":{"dataprovider":[{"id":"DP0","name":"Requête 1","dataSourceId":6187,"dataSourceType":"unv","updated":"2014-04-23T09:08:20.000+02:00"},
{"id":"DP1","name":"Requête 2","dataSourceId":6191,"dataSourceType":"unx","updated":"2014-04-23T09:08:20.000+02:00"},
{"id":"DP2","name":"Requête 3","dataSourceId":6120,"dataSourceType":"bex","updated":"2014-04-23T09:08:20.000+02:00"}]}}

Get details of a data provider of a WebI document

This URL returns the detail of a data provider of a given WebI document specified in the URL parameter.

URL:

[GET] <url>/documents/{documentId}/dataproviders/{dataproviderId}

Response type:

application/xml or application/json

Parameters:

documentId: a valid identifier of a WebI document
dataproviderId: a valid identifier of a data provider

Example (JSON format):

[GET] <url>/documents/7007/dataproviders/DP2

{"dataprovider":{"id":"DP2","name":"Requête 3","dataSourceId":6120,"dataSourceType":"bex","updated":"2014-04-23T09:08:20.000+02:00",
"duration":1,"isPartial":false,"rowCount":175,"flowCount":1,"dictionary":{"expression":[{"@dataType":"String","@qualification":"Hierarchy",
"id":"DP2.DO5","name":"Region","description":"Region","dataSourceObjectId":"HZ_REGION","formulaLanguageId":"[Region]"},{"@dataType":"String",
"@qualification":"Attribute","id":"DP2.DOa","name":"Order Amount Currency","description":"","dataSourceObjectId":"MDCZO1XT12V8KC6LFFGN1WEL3E.Currency",
"formulaLanguageId":"[Order Amount Currency]"}]},"query":
"CgASGQoIWl9SRUdJT04SDQoJSFpfUkVHSU9OEAIaVAoZRENaTzFYVDEyVjhLQzZMRkZHTjFXRUwzRRIMT3JkZXIgQW1vdW50GAIiJwojTURDWk8xWFQxMlY4S0M2TEZGR04xV0VMM0UuQ3VycmVuY3kQCCAAKAAwAA=="}}

If this is fulfilling your requirement, then I can help you to run same thing from some script to make it for multiple webi Documents.

More Info : https://help.sap.com/viewer/58f583a7643e48cf944cf554eb961f5b/4.2.4/en-US/45f881a36e041014910aba7db0e...

-

Bharath

[DellSC]

This is NOT a simple task. but it is possible. You can't use the old Rebean SDK to do this like you would have in 3.1 and older. Instead, you have to use the Webi RESTful Web Services SDK, which means that (prior to 4.2 SP5 which is due out next month) you will need to have a Web Application Container Server running. RESTful runs in the WACS, not on Tomcat.

I have done this using a combination of the BI Platform Java SDK and RESTful Webi SDK. High level, here's the logic:

1. Log in to CMS using the Java SDK and get an IEnterpriseSession
2. Use the IEnterpriseSession to get a logon token.
3. Use the logon token from step 2 to log in to the RESTful web services.
4. Use the Java SDK to get a list of all of the Webi reports - SI_ID and SI_NAME values.
5. For each Webi report in the list from step 4, get the list of Data Sets it uses using the RESTful Webi SDK
6. For each Data Set found in step 5, get the universe and the list of the objects used in the report. Output in whatever format you need.
7. Repeat steps 5 and 6 until all of the information has been found in all of the reports.
8. Log out of BOTH the RESTful web services and the IEnterpriseSession.

Doing it this way you don't have to worry about missing reports that have not been used, so they have no audit data.

-Dell

[former_member341172]

Thank you for the quick replies.

Yes auditing would be a good solution to retrieve the list of used objects. In fact, Audit DB even offers a sample report "USA4 – Usage – Universe Objects usage". However it does not work and I cannot retrieve any rows. I have also noticed on Audit DB blog that this report is "Partly fixed in BI 4.2 SP2. Still working on it.".

Using the Audit universe I have found out the reason for getting 0 rows - Derived table "DerivedDetailUniverseObjectNameAndQuery". This derived table retrieves 0 rows on my BI platform.

"DerivedDetailUniverseObjectNameAndQuery" SQL code:

SELECT  DetailUniverseObjectName.Event_ID,  DetailUniverseObjectName.Bunch,  DetailUniverseObjectName.Event_Detail_Value as UniverseObjectName,  DetailQuery.Event_Detail_Value as Query
FROM  ADS_EVENT_DETAIL  DetailQuery 
INNER JOIN ADS_EVENT_DETAIL  DetailUniverseObjectName ON 
(DetailUniverseObjectName.Bunch = DetailQuery.Bunch  
AND  DetailUniverseObjectName.Event_ID = DetailQuery.Event_ID  
AND  DetailQuery.Event_Detail_Type_ID=25  
AND  DetailUniverseObjectName.Event_Detail_Type_ID=31)

DerivedDetailUniverseObjectNameAndQuery Description copied from BO Audit universe: (This is a derived table associating The UniverseObjectName and the Query by the Event ID and Bunch. It joins the Event Detail table by EventID and Bunch and applies restrictions on the Event_Detail_Type_ID 25 for 'Universe Object Name' and 31 for 'Query'. )

When I run the query where Event_Detail_Type_ID=25 I retreive 0 rows.

SELECT  DetailUniverseObjectName.Event_ID,  DetailUniverseObjectName.Bunch,  DetailUniverseObjectName.Event_Detail_Value as UniverseObjectName
FROM ADS_EVENT_DETAIL  DetailUniverseObjectName 
Where Event_Detail_Type_ID=25

When I change the query from Event_Detail_Type_ID=25 to = 31, I retreive many rows showing Universe objects. Possible from the queries that have been run and captured by Audit DB.

Do you have a working SQL code or anything else that could help me to start retrieving the "Universe Object Name" used by the SAP BI users? Currently using Audit I cannot get any valuable information about Universe Objects usage.

[Joe_Peters]

Detail Type 25 is associated with query SQL; type 31 is the object name. You must have Query unchecked in Auditing -> Set Event Details. If you change the INNER JOIN in the derived table to a RIGHT JOIN, you should get results, just with a blank Query.

[Joe_Peters]

I wouldn't expect a performance impact, as the audit flushing process is low priority. Worst that would happen would be you'd run out of space on the database. I would suggest keeping an eye on the size of ads_event_detail if you enable it.

For what it's worth, I created a mini ETL process that moves values from ads_event_detail into dimension tables, so the overall size is reduced by maintaining distinct values only.

[former_member230921]

https://blogs.sap.com/2015/07/15/unlock-the-auditing-database-with-a-new-universe-and-web-intelligen...

[former_member341172]

I have been trying to use the "USA4 – Usage – Universe Objects usage" before and I could not retrieve any information - "No data to retrieve in Query Universe Objects over time". In addition, I have also noticed issues with the following report - "Partly fixed in BI 4.2 SP2. Still working on it.".

I have tried to modify the query and still have not got any results. Do you have more information about it? Is this only me not able to retrieve the data or is this a general issue on the latest Audit universe?

[former_member185603]

I would suggest to go with 3rd party partner solutions like GB & Smith products like 360 eyes for impact analysis. Unless you have developers to develop the code using SDK and maintain it later.