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.
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.
Here is your Query
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.
My Query for UNV-WEBI relationship
Similarly you can generate reports for multiple combinations of relationships something like below.
Now it’s time to dig in to properties of each BI content individually. Here you go for WebI.
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.
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.
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.
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
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.
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.
Hi Thomas -
Good to know - yes, I see the results now. Thank you.
Thanks for the comments Atul. I think the Folder path is working already. Refer my screen for Security information. I might have strike-off the path in my screen.
Thanks for the article Mani! Rated*****
Thanks Toby Johnston .
I continue to evaluate this and found this BO OC driver needs to be extended to Lumira Desktop as well. Here are few things that are missing while connecting from Lumira Desktop.
While Generating .lums by selecting few objects
and while applying filter on Universe objects
This is very interesting and useful! Thank you for your post.
However, I am wondering if you can point me in the right direction to fulfill a client's need to upgrade from BOXI 3.1 to SBOP 4.2.
- what is the minimum version requirement to upgrade to 4.2?
- what are the requirements or steps to migrate BOXI SDK code to BO 4.0 (Java and/or .Net)?
- where can I get more info?
Any help will be most appreciated.
You will get answers for all your queries here
How to Upgrade to BI4.0 - Business Intelligence (BusinessObjects) - SCN Wiki
SAP BusinessObjects BI 4.1 Upgrade – Overview
Basically you need to do a thorough assessment in your current environment based on which you can test your each of your current component before actual migration.
I would suggest you to come back with specific set of questions.
Thank you for the response.
And there are some major changes to the SDKs, so I suggest you as that part of the question in either the appropriate "space" for the SDK that you're using.
.NET: .NET SDK Application Development
Java: Java SDK Application Development
Since SDK is not listed in the things that the Upgrade Management Tool can upgrade, how can the existing code be upgraded? Where I can find the steps to do it?
Please post this as a question in the correct forum that I linked to above.
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
The CMS database driver is based on Open connectivity (BOOC) and is independent of the CMS database.
Ok thank you for your answer
Is it possible to get count of objects we are retrieving? We are not able to use count(si_id)
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.
I tried something similar long back. Check this. it should give you an idea.
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.
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
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?
Here it is https://archive.sap.com/discussions/thread/3287154
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.
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.
Is there a way we can fetch data provider query or report query?
when I pull a report with unx to webi I cannot see the path. Does anybody know why I cannot see the path of the document?
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
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.
Did you find the solution of above problem?
I am also getting the same error after importing lcmbiar file. I appreciate your response.
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.