Query Builder - Find WEBI reports that are using a table

[Former Member]

I am trying to find list of WEBI reports using a specific table. This specific table is used in only 1 universe.

Though I could successfully find list of reports that use this universe, I am unable to find drill down further and find the subset of reports using this table/object.

SELECT si_name, SI_KIND, si_id, SI_PARENTID,SI_AUTHOR,SI_OWNER FROM CI_INFOOBJECTS, CI_APPOBJECTS

WHERE PARENTS("SI_NAME='WEBI-UNIVERSE'","SI_NAME ='MyUniverse'")

Is it possible to find the information I am looking for using query builder.

Also, for a given WEBI report I am trying to find the list of report names and the folder they reside in:

Select SI_NAME, SI_ID, SI_PATH, SI_KIND, SI_OWNER From CI_INFOOBJECTS, CI_APPOBJECTS WHERE

PARENTS("SI_NAME='Folder Hierarchy'",

"PARENTS('SI_NAME=''WEBI-UNIVERSE'' ', 'SI_NAME=''MyUniverse'' ')" )

The above query just gives me the folder names. I am looking to find the report names as well the the folder (and preferably the path) in a single query. Is that possible.

[Former Member · Accepted solution]

Hi Sri,

In Query Builder is not possible to get table used in reports or objects used in queries, you can use SDK or some products like SAP BO Information Steward or something similar like this http://www.snapspace.pt/products_SBOPMetadataExplorer.html

Regards,

Jorge Sousa

[Former Member · Accepted solution]

Jorge, thanks for the response. We are using XI 3.1. I am not familiar with Information Steward. Thanks for the pointer, I am going to check it out. Is the SBOPMetadataExplorer tool a freeware? There is no information on their site for download or licensing fee.

[Former Member · Accepted solution]

Hi Sri,

Yes, until now I think it is free because I'm using both tools for free: the metadataexplorer and the API connector for online exploration. You can contact with them.

Regards,

Jorge Sousa

[former_member211284]

Hi,

Did you or any other people get the real solution on this question, which has not been solved for one and half years?  We now have the same issue, any further hint will be appreciated.

Best regards,

Jian

[jorge_sousa]

Dears,

Here you have a free version to query CMS repository online using WebI:

Regards,

Jorge Sousa

[Former Member]

Hi Jorge,

  Am unable to access the URL which you have provided. Could you guide me.

Thank you.

Maruthi

[jorge_sousa]

Hi Maruthi,

You can see more on http://blog.snapspace.pt/

Regards,

Jorge Sousa

[Former Member]

Hi Experts,

Is there any way can we get the List of reports were not used from last 90 days? by using Audit unvierse it is not possible by directly.  is there any other alternative way to do this?

Thank you .

Regards,

Maruthi

[jorge_sousa]

Hi Maruthi,

You can use similar approach than (the second option) but with reports.

Regards,

Jorge Sousa

[jorge_sousa]

Hi again,

Sorry, I forgot that I published that you are looking for on

Regards,

Jorge Sousa

[Former Member]

Hi Jorge,

Thank you so much for your response. But here problem is Repository Explorer Universe. Which is not provided by SAP. So i need to work with management to get this approval.  Seems to be there is no other alternative apart from that.

Regards,

Maruthi

[jorge_sousa]

Hi Maruthi,

Ok, then you have basically two options:

- Use the fantastic SAP BO Information Steward (SAP Information Steward 4.2 – SAP Help Portal Page) and compare with Audit DB

- Or create your own SAP BO SDK to collect this information and compare with Audit DB

I hope help you.

Regards,

Jorge Sousa

[Former Member]

Thank you Jorge.

Regards,

Maruthi

[former_member182521]

To give a detailed background about this, All the report level and Universe level information are being stored in File Repository rather than CMS database. Using Query builder you can always get the information stored in CMS database rather thanFilestore. In order to get the report /Universe level information you should go for a SDK code which acts similar to your WebI/DeskI/Designer workflow (Import from FRS/Opening/Refreshing). The general rule of thumb is things that are not present in CMS Database you should go for Query builder. If not SDK. Regards Mani

[Former Member]

Sri,

With respect to your 1st requirement:

In BusinessObjects 6.5 it was possible. However, post BOXIR2 this information could not be retrieved from CMS via query builder.  I agree with Jorge Sosua's comment above.

With respect to your 2nd requirement:

The field 'SI_PATH' will retrieve Filestore path(physical path). If you were referring logical path(Public folder path or psuedo-reference path) will you please refer my respone to Kelly Stone at , If this may assist you.

Regards,

Sandeep

[Former Member]

Hi Rana,

Thanks for the reply. With regards to question #1, is the report SQL related information stored in the CMC database? I was just wondering if QB lets us query so many different kinds of meta data why not report SQLs.

With regards to question #2, yes I was trying to file the pseudo reference path and not the Filestore path. With the query #2 above though I could get the immediate pseudo parent folder, I am trying to see if there is any query that would give me the report name and full pseudo path or at least the immediate pseudo parent folder - for a given universe name. I guess not and have to resort to JSP/SDK method that you had outlined in the other post.

[Former Member]

Sri,

Good question! However, report's SQL related information too is not stored in the system database.

From architectural view, i believe, its not required to store SQL or its related information of each report object on BI Platform keeping in mind the high transactional nature and disk I/O CMS generates. The below reason also holds true.

Reason: If you visualize the information workflow, the query is generated at runtime(actions: viewing or schedule) by the "QT component or QT.dll file" of the report engine in BI Platform.

Hope this clears.

Regards,

Sandeep

[atul_chowdhury2]

This message was moderated.

[Former Member]

Atul,

I am not able to find your contact information in the Bio. Can you send me your email id privately or point me how to send you an email? I did a "follow" on you hoping to see I can contact you, but didn't help.

[atul_chowdhury2]

Hi Sri -

I've sent a private message. Looking forward to connecting with you.

[CdnConnection]

Sri,

     Query Builder can't give you this details because it is NOT within the CMS database because Query details is only captured at run-time.  The best to get this to use the Audit UNX and get details of the Query run by WebI.

Regards,

Ajay

[Former Member]

Ajay,

Thanks for the reply.

The main reason I am trying to do this, is to find out if a given table is used anywhere in the reports. Since the client have a large and wide spread user base, we cannot directly check with them.

That is why we cannot use Auditor either because they may not have refresh the said reports in the past few months and since Auditor is historical, it is not going to work out for us. We want this information now and not based on some past historical auditor data.

So trying to find this detail using QB. At WEBI report design time they should have build the report query. Though prompts etc are supplied with the values during the refresh run time, is this SQL stored anywhere in the CMC database that can be retrieved using QB instead of resorting to SDK?