How to list SAP BO BI documents in public folders never used

This post shows a possible way to list SAP BO BI documents never used by users in the SAP BO BI platform using SAP BO BI Universe in SBOPRepositoryExplorer combining with SAP BO BI Audit Universe.

Prerequisites:

Some required components:

1. SAP BO BI 4.x Platform;
2. SAP BO BI Audit DB collecting document actions;
3. SAP BO BI Audit Universe configured and pointing to Audit DB;
4. Excel to save list of “documents�? used from Audit Report;
5. IDT (Information Design Tool) to configure SBOPRepositoryExplorer connection and the universe;
6. WebIntelligence to create the document.

Creating Report with Document-Activity from Audit DB:

Using WebIntelligence and Audit universe:

- For result objects:

- Filter [Event Type] with “In List�?or “Equal�? to “View�? and [Object Folder Path] with “Different from pattern�? to “/User Folders%�? because we are searching in public folders and not in personal folders:

- At the end you have next query:

- After execute the query we must take in consideration that some documents like “Charting Samples�? can has different CUID by upgrades installations:

- To solve the situation we can show the last view and the associated CUID, creating two variables:

 

- And using the variables the result now is like:

- Save the document for a possible future use.

 

Export Report to Excel File:

 

Export report to Excel (XLS or XLSX):

Remove in the Excel all blank rows before head and all blank columns before “Object Folder Path�?, remove also any special character different than [a-Z][0-9].

You also can use SAP BO Live Office to retrieve data using Audit universe and schedule periodically.

Rename report name to the final table name in the universe:

Save to a visible path by SAP BO BI Client Tools (IDT and WRC) and by SAP BO BI WeIProcessingServer, you can use UNC format (\\..\..\..)

 

Retrieve SBOPRepositoryExplorer universe to IDT:

Create in IDT (Information Design Tool) a Local Project and from Repository Resources:

Configure Universe Connection attaching the Excel File:

To attach our Excel file definition to our universe we must create an universe connection in IDT into a project, for example:

 

Test Data from Excel in Connection:

Before continue with next steps is important to check if Excel data can be read where path is correctly defined and also the structure:

 

 

Import new Table (Excel) into Universe:

Now we can import the table into the Data Foundation and insert Join between EXCEL’s table and DOCUMENTS table:

Configure Join:

Save the Data Foundation.

Define new objects in the Business Layer:

Here we can define in the Business Layer, into the “Documents�? folder the new measure coming from the new XLA_AUDIT_PUBDOCS_VIEW table:

for example, with next content:

Before publish the universe, create a query to test results with all documents and documents never viewed (at less recorded by Audit):

It means that we have 832 documents in the CMS system DB. Now we can filter by [Number of Audit Views] equal to 0 to show documents never viewed or never recorded in Audit database:

So, in our test environment we have 832 – 545 = 287 documents viewed and recorded in Audit DB.

In the Excel (from Audit) also we can have many documents that actually do not exist in CMS DB System Repository because maybe were deleted from the system.

Another possibility is not use the CUID and do the checks using [Document Name] and [Folder Name] combination for the comparison with Audit data, but this is not the best option. The best option is to use the CUID.

This universe can be published and consumed, for example, from WebI.

Thanks for your time!

Jorge Sousa