Skip to Content

My First look at CMS database data access driver – BI 4.2 SP3 New feature

It has been long time since I wrote blog in SCN due to professional commitments. But I tempted to write a blog after exploring the new features of BI 4.2 SP3.


Thanks to https://scn.sap.com/people/thomas.jentsch  for writing this introduction document to explain the new feature CMS database access driver via BO Open connectivity here https://scn.sap.com/docs/DOC-74580 and thanks for your mention of my Query builder blogs too.


Here are few of my findings and observation after exploring this. I hope this should be useful for few other to start building their own custom metadata reports going forward. I will continue to explore this and help others in case of any questions. To those who are looking for more information about BusinessObjects Open connectivity and driver details, the below information will be beneficial.

/wp-content/uploads/2016/09/1_1028320.png

Yeah. It is easy for me to extract the user list from the repository with out running the QB query or the Excel based macro now. This is impressive. I can keep track of my licence usage easily by simply schedule this webI report to my Inbox every month.


/wp-content/uploads/2016/09/2_1028334.png


Here is your Query

/wp-content/uploads/2016/09/3_1028335.png

Now it’s time to concentrate on my BI content. I am going to try for my Universe/Report relationships and Universe/Connection relationships here.


/wp-content/uploads/2016/09/4_1028339.png

My Query for UNV-WEBI relationship

/wp-content/uploads/2016/09/5_1028340.png

Similarly you can generate reports for multiple combinations of relationships something like below.

/wp-content/uploads/2016/09/6_1028341.png

/wp-content/uploads/2016/09/7_1028342.png

Now it’s time to dig in to properties of each BI content individually. Here you go for WebI.

/wp-content/uploads/2016/09/8_1028343.png

Finally the most interesting and the essential information for every BusinessObjects Administrator – The security, though it is not showing each granular rights this should help us to build the Security matrix at high level with a clear cut Custom Access Level definition.

/wp-content/uploads/2016/09/9_1028344.png

Few of my observations:

1. The Current Data foundation has only one table to capture all the metadata properties. You may require creating multiple Aliases as on when you required getting the detailed information about multiple interrelated BI contents.

/wp-content/uploads/2016/09/10_1028345.png

2. The way How Class/Objects organized in the Business layer is so confusing .In order to understand the current model we may need to do multiple iterative steps to capture/understand the underlying metadata.

/wp-content/uploads/2016/09/11_1028346.png

3. You don’t need to install the specific driver required for BO OC if you are in BI 4.2 SP3.

4. The number of relationships listed is very limited and might not be useful for complete analysis of all the Repository objects something like User- Inbox & User-Favorites

/wp-content/uploads/2016/09/12_1028347.png

You can get the list of almost all type of BI content here https://scn.sap.com/docs/DOC-42952#start=100 as listed by Dell Stinnett-Christy in Comments section

I understand this is the one among the big leap towards the metadata reporting in BusinessObjects by SAP, Let us add all our thoughts/comments here for enhancement of this feature to make everyone’s life easy.


Thanks for reading and Have a nice Long weekend everyone.

Thanks

Mani

31 Comments
You must be Logged on to comment or reply to a post.
  • Great write-up, Mani!

    I too am impressed with what Thomas’ and the team has put together – a long awaited addition to the BI platform toolset to avoid wasting time “finding stuff” in the landscape.  Our company has written similar realtime OC-based tools in the past (2009, CMS/Connect) and I can be the first to attest to how challenging doing this is.

    I wasn’t adventurous enough like you to play around with the data foundation much – I’ll try doing the additional levels as you mentioned.

    Security access levels is probably the most significant feature of the driver – pretty cool.

    Were you able to get FOLDERPATH to work correctly? We could not – and that for us is a deal-breaker, especially for larger enterprise customers. – nevermind, Thomas showed the right approach for this in a follow-up comment – thanks!

    Also found the driver itself extremely fragile – you have to know just what combinations to use in the report and error message are cryptic java errors that likely should be more graceful.

    A good start indeed though!

    • Indeed even the driver makes it easier, technical knowledge is still needed, the InfoObjects can hold every information and this is challenging mapping this to a universe.

      For the Folder question: take a look at the sample query Sample-FolderPath (Universe) in the provided universe. This shows the relationship from an InfoObject to it’s folder.

    • /
      /wp-content/uploads/2016/09/9_1028809.png
  • Hi, thank you for your post.
    I have a question it’s may technical: is the driver SAP BI platform CMS system database work on all kind of data base or only for SQLAnywhere

    Regards

  • Hi, Is it possible to mix CMS Univers with Audit univers ? to get more complete Audit reports !!

    like the list of user group/user/number of refresh/documents/path

    I tried to do that but no success 🙁 In IDT I couldn’t mix the two univers Audit and CMS.

     

     

  • Hi Mani,

    Very nice doc for the beginners. We have mostly crystal reports being used in my environment and so there’s always a question of how many reports are tagged to each DSN.

    Is there a way to filter DSN and then get the reports associated with the DSN?   I know in queryquilder, the result set is always an array. we are interested on how to use this universe to get through that array result.

    Query: select * from CI_INFOBJECTS WHERE SI_KIND=’crystalreport’ and si_name=’****’

    Please help on how to get that SI_CUSTOM_SERVER or SI_SERVER detail. this gives us the desired results of associated reports if possible.

     

    Thanks,

    Satya

     

     

     

    /
    • You cannot jump till the level 3 of a property bag.

       

      In your QB query you can use SI_LOGON_INFO.SI_LOGON1 not Like SI_LOGON_INFO.SI_LOGON1.SI_SERVER or SI_LOGON_INFO.SI_LOGON1.SI_CUSTOM_SERVER

       

      Thanks

      Mani

  • Hi Manikandan!

    I am working developing reports based on the CMS database. Is the datamapping of CMS database available in the SAP Help/forum ? For example, I would like to decode the information of Schedule type and scheduling status. I am looking for information on what does the schedule status 9 represent?

     

  • Hi Mani,
    ist it possible to get all custom attributes in the universe?
    I tried it, but it doesn’t work. There is <NULL> in the result column of the query.

    Thanks,
    Bernd

    /
  • Hello All, We have been play around with the “data Access driver” a bit and noticed an oddity I guess.

    We are finding that when an Administrator logs in runs a Report using the “data Access driver”, All users are returned as expected. But when a NON-Administrator runs the same Report only the Users that are designated as Administrators and the User that runs the report are returned in the report.

    We have also been toying with this: https://blogs.sap.com/2012/10/11/businessobjects-query-builder-queries/comment-page-1/

    And we are finding that when an Administrator logs in and uses the Query Builder, All users are returned as expected. But when a NON-Administrator runs the same query in Query Builder only the Users that are designated as Administrators and the User that runs the query are returned.

    Does anyone have any input on this behavior, as we would like to have Non-Administrators Run some of these Reports.

    Thank you..

     

  • Hi Mani,

     

    Firstly i would like you Appreciate you on sharing these quality materials on CMS Universe Reporting and also on your detailed Query Builder notes. These has helped me tremendously and i am sure there would be Hundreds and Thousands more like me.

     

    I am looking for 2 details in one of my reports that i build on CMS DB Driver Universe

    I have mapped Universe to Webi Reports like you did in one of the above screenshots

    • I would like to know if there is any way to add webi Report folder location in this report
    • If there is any way (anywhere) to find out last Run date of reports on Adhoc basis (Manual Run not the scheduled)
  • Hello Mani,

    Thank you for putting this together.

    Once we load the lcmbiar file for the first time, we need to update the connection details to point to the current CMS database…Correct? or use the connection that came with the package?

    I am unable to find this information and getting RDMS error (attached) when I try either of the options.

    I appreciate your response.

    Thanks

     

     

    /
    • Hello llyas,

      Did you find the solution of above problem?

      I am also getting the same error after importing lcmbiar file. I appreciate your response.

       

      Thanks

       

       

       

  • Hi – Can i create setup Universe and Reports in my DEV box and create a new SAP Connection to CMS Database to point to PRODUCTION environment?

     

    When trying this i get

     

    Could not reach CMS ‘*****************’. Specify the correct host and port and check for network issues. (FWM 20030)

     

    The same credentials are used to create an ODBC Connection on PROD BO Server to connect to CMS Database and it tests successfully. I am trying to setup this universe in DEV and have the reports schedule to run during off peak hours with connection pointed to Production.

     

    Regards,

    Farha