BusinessObjects Lineage analysis – Harnessing capabilities of Web Intelligence & CMS Database Access driver

Background

Lineage analysis on BI content – This is one among the most requested feature in BI platform area for long time, I think for almost more than a decade.

Post Crystal architecture, the CMS database is encrypted and you cannot directly query the CMS database like you used to do it in legacy Business Objects (remembering ManagerO universe for Document/Universe/Security domains, It’s a good old Business Objects Deski days!!!!). From XIR2 onwards, Query builder is the only direct/out of box feature to query your metadata information. I have seen customers who extensively used SDK to pull metadata from CMS database and push it to a relational database on top of which they performed all their user access provisioning/ Security role assignment as well as metadata lineage analysis from the extracted information.

In this blog we are going to see how to perform Business Objects Lineage analysis to some level of extend. If you are in BI 4.2 SP3+ already, you are so lucky and SAP already made things so easy for you to do the lineage analysis through CMS database access driver.

The whole idea here is to make use of CMS data access driver/ SDK scripts for metadata extraction in a Webi report and perform a search functionality in Webi report to find the lineage.

I inspired to write this after reading excellent blog by Koen Hesters  here https://blogs.sap.com/2017/08/18/how-to-create-a-search-engine-in-webi/

Approach 1:

Use SDK script for metadata extraction and push the data in Excel spreadsheet managed by BI Platform and create Webi on top of managed Excel for search functionality.

Approach 2:

Use SDK script for metadata extraction in a relational database and create universe/Custom SQL to create Webi and implement the search functionality.

Approach 3:

If you are in BI 42 SP3, make use of CMS DB access driver to extract the metadata directly in your Webi report and implement the search functionality.

All 3 in pictorial representation

I am going to show you how to achieve the search capability in Webi report created from a managed Excel. My COM SDK code will extract the metadata like below and I will be managing the Excel in my BI Platform.

Webi- Data source lineage

To get the webi & datasource relationship, I am going to perform text search on Webi report’s SI_WEBI_DOC_PROPERTIES property

Here is my   QB query

SELECT SI_ID, SI_NAME , SI_WEBI_DOC_PROPERTIES FROM CI_INFOOBJECTS

WHERE SI_KIND = ‘Webi’   AND SI_INSTANCE=0

To find reports based on eFashion Universe

To find Reports based on my HANA view VW_WHR

User group-User lineage

If you are in BI 4.2 SP3+, you can straightaway create Webi reports with search functionality.

I created similar Webi reports in the blog below to make it as a reference and starting point.

https://blogs.sap.com/2016/09/02/my-first-look-at-cms-database-data-access-driver-bi-42-sp3-new-feature .

Lineage in Web Intelligence using CMS DB access driver

1. Webi & Data source

2. Usergroup/User association

With all the object-object relationship in hand you should be able to perform lineage analysis to get end to end analysis

Thanks for reading and please don’t hesitate to post your findings & ideas back. Watch out for few more Query builder queries in my next blog!

References

https://blogs.sap.com/2016/09/02/my-first-look-at-cms-database-data-access-driver-bi-42-sp3-new-feature/

https://blogs.sap.com/2017/08/18/how-to-create-a-search-engine-in-webi/

https://blogs.sap.com/2013/06/17/businessobjects-query-builder-basics/

https://blogs.sap.com/2013/09/13/businessobjects-administration-relationship-queries/