Skip to end of metadata
Go to start of metadata

One of the mostly requested feature by BI platform administrators is an integrated and easy to use Reporting & Analysis solution for the CMS database. As of now this is provided by the BI platform through the Query Builder, an HTML application to query the metadata (see BusinessObjects Query builder - Basics).

With the new CMS data access driver introduced in SAP BI 4.2 SP3 (see SAP BI 4.2 SP3: What’s New In Semantic Layer) you can use a universe and native reporting clients to query the metadata of the CMS repository database.

 

More Information
Notes (Component: BI-BIP-ACP)

Latest Activities

TopicDescriptionDate
Sample content version 2Enhanced universe and more Web Intelligence documents
(download see Get Started)
21 Dec, 2016

 

While the driver is installed with the product, a universe and sample Web Intelligence documents will be available for download on this site. This will allow us to provide release independent updates for the first version of the universe and samples. Its integration as samples in the installation is available since support package BI 4.2 SP4.

  

Introduction

Most of the data access drivers are using SQL and database middleware to run queries against a data source. However, for the CMS database direct access using SQL drivers is not possible, metadata queries are managed by the Central Management Server (CMS). The CMS controls the database connection and can execute queries using its own query language.

Fundamentals

To use the BI Repository Analysis solution, you should at least have a basic understanding of the CMS system database, how to query InfoObjects metadata, its properties and relationships.

Requirements

  • BI 4.2 Support Package 3

Get started

  1. Download the content (universe, Web Intelligence samples and videos).
    Starting with BI 4.2 SP4, the universe and samples can be found in the samples folder of the installation (..\SAP BusinessObjects Enterprise XI 4.0\Samples\bionbi).
  2. Import the universe from the LCMBIAR into your BI 4.2 system using Promotion Management
  3. Watch the videos to get help in getting started using the universe

Planned Activities

TopicDescriptionDate
SAP TechEd

 

Session ID: ANP266

Meet us at Teched to practice / train on this new driver, talk about the universe, create complex reports and share your feedback.
ANP266: Explore Your System in Real Time with the Latest BI-on-BI features
SAP TechEd Las Vegas, SAP TechEd Bangalore, SAP TechEd Barcelona

September, October, November 2016
ASUG + BI Conference, New Orleans PLT6083: BI 4.2: The New BI-on-BI Capabilities in BI 4.2 SP03 Release October 18, 2016
Git Hub repositoryWe plan to create an open source project on the SAP Git Hub platform to collaborate on Universes and Web Intelligence documents open

 

Top Contributors

A special thanks goes to the top contributors leading the development of the sample content (universe, Web Intelligence documents and videos):

Former Member

Sylvain RIBOUD

  • No labels

329 Comments

  1. Former Member

    there is no more comments !!

    1. The SCN is reorganized, now called Community and this document was moved to the SCN Wiki. Unfortunately previous comments are not available anymore. I have saved them and we will continue to create more WIKI pages around this topic.

      1. Former Member

        Ok thank you for your answer. 

        So if we need to ask some questions about the universe and the objects  we continue to post in this page ? 

          1. Former Member

            So I have a few questions to understand better the universe:

            1. As there any documentation to explain the objects of the universe ? I know that it's like the query builder but I could not find everything.
            2. As there any new webi reports ? 
            3. How to get values in the object "FolderPath" in the class "InfoObjects"

            Best Regards

             

  2. To get the FolderPath of for example a WebI document or a universe, you have to use the Relationship ParentFolder. The FolderPath will be in Level 1 information. You can find an example query in the universe:

     

    1. Former Member

      Thank you, but the folderpath is empty, Do I need to activate that in CMC (like the Audit) ?

      1. No there is no need to activate anything. Which kind of object in your query results has still no folderpath ?
        You can also use Query Builder to check, if the InfoObject contains the property si_path which is used to build the folderpath (replace 24 with your search id):
        select si_id, si_path from ci_infoobjects, ci_appobjects, ci_systemobjects where si_id=24

        1. Hey Thomas, just trying to do a very simple report of all of the list of Webi reports in our repository and the folders that the reports are in.

          I as well, am getting a blank folder path.

          I ran the example query for si_id 31206, and the query returns no si_path property.

          It seems like all of the objects in L1 are null...?

          1. The FolderPath(L1) will contain the folder hierarchy, using Name(L1) you get the folder of a document. For example if a document is stored in folder 'Web Intelligence Samples', which is a root folder in Public Folders, the FolderPath(L1) will be empty and Name(L1) will return 'Web Intelligence Samples'.
            regards Thomas 

            1. Thomas, I had to open the sample report and take a duplicate of that to figure out what I was doing wrong.

              Here's what I finally found- the order of the objects in your query panel make a difference in the results of your query! (Apologize my ignorance if I overlooked this requirement in using this universe)...

              If I create a query with the objects in this order, the folder path (L1) does not populate:

              Name, FolderPath, ParentId, Id (L1), Name (L1), FolderPath(L1), ParentFolder

               

              If I rearrange the objects in this order, the folder path (L1) DOES populate:

              Name, FolderPath, ParentId, ParentFolder, Id (L1), Name (L1), FolderPath(L1)

              1. thank you for clarification, this seems to be a limitation in SP3, should be fixed in SP4 (the version I've tested)..

  3. I have imported the sample universe and reports. When I create a new WebI document, I'm asked to select a Universe. As soon as I select this Universe and click OK, I immediately get this error:

    Specified RDBMS is invalid : SAP BI platform CMS system database

     

    1. can you please check:

      • BI 4.2 SP3 is installed 
      • in Informtion Design Tool, create a new Relational Connection, SAP BI platform CMS system database should be in the list
      • or in your installation the folder "..\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\cms" exists
        1. 4.2 SP03 Patch 01
        2. If I try to create a new connection in IDT, I do not see that driver listed. However, if I open the connection that was created from the sample universe import, it tests succesfully. Additionally, if I run the sample queries in IDT, the queries run.
        3. This directory does not exist

         

        1. Former Member

          Do you have the same version in your SAP client also ? 

            1. Also, if I try to refresh one of the 3 sample reports that comes with the download, I get errors. The errors are not consistent and usually list a different APS server, and the errors that come up also rotate between the error as originally submitted.

              Unable to retrieve network layer aliases : com.crystaldecisions.sdk.exception.SDKException$OCAFramework: Server BIPROC02.APS.Promotion not found or server may be down (FWM 01003)
              cause:com.crystaldecisions.enterprise.ocaframework.OCAFrameworkException$NotFoundInDirectory: Server BIPROC02.APS.Promotion not found or server may be down (FWM 01003)
              detail:Server BIPROC02.APS.Promotion not found or server may be down (FWM 01003) (Error: INF )

              All servers are up and running. However, the monitoring application isn't working (open issue with support).

              All other reports seem to be working as expected.

              1. SAP Support is unable to figure out the issue and is suggesting a repair of the install on my CMS tiers and Processing tiers in the clustered environment.

                It's very odd that I have this same behavior on two installations.

                Before going through the steps of a repair on my production environment that is set to go live on Sunday, is there any last minute troubleshooting steps the experts on this thread might have?

              2. Former Member

                I get the same behavior is  promotion management.  If I try to update over ride settings then I get the FWM 01003 failure and it is a different adaptive processing server each time.  If I turn the APS's off then it will function with out error.

                1. Do you have a distributed clustered environment, or a standalone environment?

                  I get the same errors, each time showing a new APS.

                  I've had a support incident open for almost 2 months now with no solution and they say there is no issue that it may be related to a distributed environment.

                  1. Former Member

                    I have 2 servers, each server runs all the services, CMS etc.  I just updated the servers from 4.0 to 4.2 sp3 patch 1.  I was migrating some content when I got the errors.  I turned the SIA off on the other server and it still happens.

                    You get it running a report?

                    1. Former Member

                      Hello There is a bug in BI4.2 SP3 patch 1 with APS. 2381082 - Some AdaptiveProcessingServer servers cannot be contacted. In the logs you can find the following information : APS "not found or server may be down"   You have 2 options : 1) having only 1 APS in your system, or 2) install BI4.2 SP3 patch 2 or later.

                      1. Former Member

                        I can't reach the link you provided.

                    2. I did get the APS errors while running reports against this new CMS Universe and performing other various admin actions in CMC.

                      After upgrading to Patch 2, the error while running this report did go away, but the error still persists while performing other functions in CMC.

                      However, I still cannot execute queries (new reports or refresh the reports that came with the install) against ths new CMS Universe, I still get the error:

                      Specified RDBMS is invalid : SAP BI platform CMS system database

                      1. I've tried on a cluster environment now (Windows) and get the error message "Specified RDBMS is invalid..." only if the following file is mising:
                        "..\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\setup\cms.setup"
                        (I removed it manually from both nodes to test).

        2. seems there is a problem with the installation. If the directory doesn't exist, the test of the connection and the quies in IDT should not work. On my system, if this is missing, get always the error: Specified RDBMS is invalid : SAP BI platform CMS system database.
          I have a Windows server with BI 4.2 SP3 Patch 1 installed now also, works fine.
          Can you please open an incident with support.

          Best regards

          1. Do you have a distributed environment that is clustered and split between two different firewalls?

            What component should I use for the incident?

            1. No, I have just a single server. Guess the driver files must be available on all servers the query could be potentially executed:
              ..\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\setup\cms.setup
              ..\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\cms\*.*
              ..\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java\dbd_sapbicms.jar
              ..\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java\dbd_sap_java_drivers_sdk

              For the incident please use the component: BI-BIP-ACP

              1. Former Member

                Hi,

                This is a known defect for distributed set up of BI 4.2 Sp3 and above and can be tracked in following SAP Note:
                 2405568 - When install the BI 4.2 SP03 by “Custom/Expand”, there is no SAP BI platform CMS system database selection

                As workaround you can try the following:

                Copy following files/folders from a working environment to the non-working environment (both should be of same patch)-
                ..\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\setup\cms.setup
                ..\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\cms\*.*
                ..\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java\dbd_sapbicms.jar
                ..\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java\dbd_sap_java_drivers_sdk
                Restart all the nodes
                Files need to be placed only in processing tier.

                We will try to get the SAP Note updated as well with the above mentioned workaround.

                Regards,
                Deepanjan Bhowmik

  4. Former Member

    I just created the connection with success, but related universes are not listed in IDT, how can I get the universe/sample reports?

    1. You can find the Download listed on this page in the section Get Started

      1. Former Member

        Oh I overlooked that, thank you! It worked

  5. Former Member

    Hello David, Sylvain, 

    It's very usefull.

    In the video, we see the "BI on BI - High Level View / Board", is it a sample ? Is it possible to download like the others sample ? 

     

    Thank

    Goulwen

     

     

    1. Came here to ask the same.  High level view board is not in the webi sample biar file.   Would be nice to know where to get it.  Was hoping to show it to others as a way to Fiori-tize webi documents.

      1. The document will be part of version 2, we are currently working on it and the plan is to provide version 2 in December.

        1. Former Member

          Thank Thomas 

  6. Former Member

    HI Thomas,

    When importing the Universe and refreshing the sample report it gives me below error message:

     

    The following Database error occurred(CS): "Open connectivity driver error :No query filter defined".

     

    Thank in advance

     

     

    1. Which sample report did you try ?

      If it is just a new query, please take a look at the Limitations listed under Notes on this page, it is mandatory to define a query filter.

  7. Former Member

    Sorry,Thomas it was not a sample report  what I mean was Sample Universe. I dragged one of the object in object pane and tried to refresh it.

    I got this above error message.

  8. Hi Thomas,

    I'm loading the new set of packages into 4.2 sp3 and am struggling to get the SAP icons to show in WebI in the browser (IE8, Firefox and Chrome, HTML and Java panels).

    They show fine in WebI Rich Client.

    I can see SAP Icons as a font option in the formatting menu, but they just don't display properly on the page.

     

    thanks

    Keith

  9. Hi Keith,

    tried it on a test machine and worked fine following the instruction (on the server had to change fontalias.xml only in ..\win64_x64\fonts).
    Did you restart the tomcat ?

    regards Thomas

    1. it's odd, I can get it to work fine on a test machine too, with IE11 and Chrome. I'm sure I have done it the same on both machines. Will dig further.

      thanks

  10. Former Member

    HI Thomas,

    Is it possible to create our own Universe using Business objects Open connectivity driver. We are looking for server metrics data (CPU Usuage,RAM Used,Open connections etc)

    Thanks in advance

     

     

    1. You would like to use the CMS DB Driver to create your own Universe ?
      Would it be possible to get this information with a Technical Query of the sample universe ?

  11. Former Member

    Sorry Thomas What is required from my side. Well the universe that is currently being supplied does not have Server details,so my question was can we access the CMS DB via CMS DB Driver and include the Server Table(essentially tables that have server related information).

    Is that possible ?

    Thanks in advance

    1. The CMS DB Driver is using the BOE SDK to read the data and is not accessing the database tables. So it is not possible to access the tables using the CMS DB Driver. It should be possible to get the metric informations using a Technical Query, the query Sample-QT-Properties with Filter (Server) in the universe may help.

      regards Thomas

  12. Former Member

    Thanks once again Thomas for your response on this. Where Can i find this Sample-QT-Properties with Filter (Server)  is it in the sample universe provided here or I have to look for it in any other place.


    Thanks in advance

    1. Yes, you can find the queries in the sample universe.

  13. Hi Thomas, that is really great to have this, it is extremely useful I think! We are monitoring and reporting our customers' license usage with the help of the information in the repository, so far with a third party solution I would like to replace. There is one thing I tried to get, but I could not figure out how to. Disabled users. Aliases (we use WinAD and enterprise)  and if they are disabled. We need to report all users and highlight disabled ones, which do not use license but we always have to keep them for some time. Is there a way to build a query which returns all users and whether they have a disabled alias? Many thanks in advance

    1. Hi Imre, thank you for the feedback. The use case with the Aliases is a really interesting one. Guess you can get the information by using a technical query, see the sample in the screenshot. Unfortunately there is a restriction in the current driver version, so you cannot create a proper WebI document to cover your requirement. Objects like ID, CUID, Name cannot be used in a technical query and therefore it is impossible to group the query results for multiple users in WebI. The restriction will be fixed in BI 4.2 SP4, not sure we can also provide a patch for BI 4.2 SP3. regards Thomas

      1. Hi Thomas,

        Many thanks for the information! The query works fine, however - as you mentioned - it does not help us unfortunately. We will wait till the next SP then, however if you happen to have any patch meanwhile, I would be very happy to know about that! (smile) 

        Thank you again, Imre

      2. Hi Thomas. I extracted "SI_DISABLED" technical properties for our 1.946 users and many of them have more than one SI_ALIASES property (usually LDAP users). If I want to make a count of total disabled users, distinct by true or false, I obtain a wrong "number" for my purpose, because if a user has more than one alias the SI_DISABLED property appears more times , instead I need to count it only one time per user.  I work on 4.2 SP4 Patch2 at the moment. Can you give me some advice regarding on it? Thank you!

        1. I found the solution using input controls on false or true property value and counting filtered user's ids which are unique.

          Angelica

          1. Great, really appreciate the passion you have on finding and sharing solutions.
            regards Thomas

            1. Hi Thomas, is there any problem for you if I create a new page, under this yours, to upload and share custom reports created by me to explore cms? David Metser alerted me that "Business Intelligence (BusinessObjects) stage" page will be removed and invited me to move my shared contributes in wiki "stage" to an official page.
              Angy

              1. no problem at all, Thomas

                1. I also wondered how to get the disabled users.

                  But I can't find any other way than scroll through 23 CMC pages.

                  The CMS Data seems to keep entries of users over time so there was once a not disabled AD user X - now the Enterprise is really disabled - but the cms db info for this user gives a disabled true and a disabled not true but which is the true true?

                  In fact he is disabled... how to get this from the actual db?

                   

                  Wobi

                   

                  1. The user is only truly disabled if all authentication aliases are disabled.  If one of the authentication aliases is enabled, the user is still able to login.  It is a bit complicated using the CMS universe to determine if all authentication aliases are disabled for a user but it can be done.  We have a daily document schedule that shows us a list of users who have disabled authentication aliases or are completely disabled due to all authentication aliases being disabled.  The document uses the CMS universe on the CMS database driver to view and analyze the Properties for User objects.

  14. Former Member

    ´Hi All,

    The driver causes always the following warning message on the BO server. Any idea about that?

    "Server Intelligence Agent: server XXX .WebIntelligenceProcessingServer is logging excessively.  Use -traceconsole to investigate."

    Thanks,

    Emre.


    1. Hi Emre,
      thank you for this feedback. The dev team has found the root cause for this and the change is planned for next releases.
      regards, Thomas

  15. We encountered an issue with "Group_To_Members" relationship (in Relationships To-L1 folder). I created a report that extracted all users belonging to every usergroup, through "Group_To_Memebers" relationship. But the query did not retrieve all si_name properties of the users, but gave only a partial result. Instead I correctly obtained all users si_id (given from "Group_To_Members" dimension). I would to attach an image as instance but I have not permissions. Any suggestions?

    1. Just checked this and you hit a restriction in the first version of the driver. The relationship query is currently executed using the 1000 rows limit of the SDK. A tricky workaround splitting this into two queries may help. Create a query to get Id, Name, Kind, Group_To_Members relationships, query filter: kind='usergroup' (do not use L1 objects). Create a second query Id, Name, Kind, query filter: Id inList "results from another query", as object use Group_To_Members from first query. Now you should have the data. Tricky part is now, that you have to merge the two dataproviders in WebI. Merge Group_To_Members (first query) and Id (second query). Final step is to create new variables based on the dimension objects you need from the second query.The variables must be defined using Qualification Detail. In the screenshot you should find all the details.

      1. Hi Thomas, your workaround works fine, thanks! I have another need: I would to insert also the email information for each user. My report table should show the following columns:

        1. UserGroup Name 
        2. Username (= GroupToMember_Name variable)
        3. User Id
        4. Email

        I created a second variable like your "GroupToMember_Name" called "UserEmail" and containing as associated dimension "Email" from InfoObjects/User extracted with the second query (with Id, Name, Email in "Result Objects"). But when I add UserEmail variable as fourth column of the table , I receive a DATASYNC error in every cell. Any suggestion? Thanks in advance!

        1. Did you use Qualification Detail and have you set the Associated dimension correct ?

            1. you mentioned: "UserEmail" and containing as associated dimension "Email". Guess associated dimension should be "Id" ?

              1. You have right......I did not understand well how to create new variables.....now it works!

                So, I can create a new variable, selecting in "Formula" the value of the dimension choosen from query (Email in my case) and in "Associated Dimension" always the merged dimension Id.

                I imagine you defined for our second new variable in example "GroupToMembers_Kind" :

                1. Id (Members) as Associated dimension
                2. =[Members].[Kind] in Formula

                Is it correct?

                1. Correct. As you mentioned the Associated dimension must be the merged dimension, in our sample "Id (Members)".

  16. Former Member

    Hi Thomas 

    First of all a huge thank you for this awesome work!! 

    Is it or will it be possible to list all Webi Documents with freehand SQL?

    Best Regards, 

    Guillaume

    1. Hi Guillaume,

      yes it is possible, just created a WebI document with free-hand SQL and checked in query builder using the following query: select si_id,si_name,SI_FHSQL_RELATIONAL_CONNECTION from ci_infoobjects where si_name='Free-Hand-SQL'.
      To get this information with the CMS DB Driver, you have to create a new relationship object in the universe, using the property container SI_FHSQL_RELATIONAL_CONNECTION. This will allow to check, if free-hand SQL is used and you can also get the connection details just by adding InfoObjects L1 objects to your query.

      regards Thomas

      1. Former Member

        Hi Thomas,

        Thank you, but I also need to get all webi where users modified the generated SQL (query originally based on a Universe).

        Possible? I didn't find anything in query builder results. :-/

         

        Best Regards

        Guillaume

        1. This property is stored in the dataprovider of the document, but not available in the InfoObject properties. This would require the WebI SDK to read the information and this is not in the current scope of the CMS DB Driver.

  17. Former Member

    Hi All,

    I am trying to to add objects to the CMS Universe which provide information about the Data Security Profiles (DSP) and Business Security Profiles (BSP) we put on a Universe.

    So far, I was able to create objects displaying their ID's (relationships((Properties.SI_SL_BSPS)), related Universes (relationships(Properties.SI_SL_BSP_TO_UNIVERSES)) and related User Groups (relationships(Properties.SI_SL_PRINCIPALS)). I was able to find these through the Query Builder.

    I was hoping to find some more details about the DSP's and BSP's, for example about the defined ROWS or Granted Views.

    Does anyone know if this is possible? And if so, how?

     

    Kind Regards,

    Martijn

    1. Hi Martijn,
      looked into this and the InfoObjects you can get with the relationship ids are of SI_SPECIFIC_KIND: DSL.DataSecurityProfile, DSL.DataSecurityOptions, DSL.BusinessSecurityOptions, but you cannot get the details you're searching for. Seems the details are stored in the FRS and the curretn version of CMS DB Driver cannot read this information (also not planned for future releases so far).

      regards Thomas

  18. Hey Thomas,

    thanks for the great work!

    do you know if I can somehow create something like a derived dimension based on for example the schedule_type?

    I tried to create a dimension "schedule type text" where I iterated over schedule_type with a case to set for example 0 to "Once".

    Unfortunately, this did not work. Do you know a solution?

    Best regards

    Philip

    1. Hi Philip,
      thank you for the feedback.
      Unfortunately this is not possible, we have not implemented any kind of functions in the driver. Could be a good candidate for a feature to add.
      Guess currently the only solution would be to define a variable in the WebI document. To make it reusable you can think of create a shared element containing the definition.

      regards Thomas

      1. Hi Thomas,

        thanks for the feedback.

        Creating a shared element is a good idea, I will do that.

        Best regards

        Philip

  19. Hi Thomas,

    is contemplated in future enhancements the possibility of using IsAllowed function in "BI Platform CMS system database" universe? Today if I want to extract which universes are granted to a specific user , I have to extract them with query builder using "IsAllowed (<UserID>, 3 for View general right>)".

    Thanks in advance.

    Angelica

    1. Thank you for posting this idea. We may look into this for BI 4.2 SP5. 

  20. Hi Thomas, I created a custom linked universe to explore CMS DB, as you suggested in I added new dimension "Descrizione_Utente" obtained from "SI_DESCRIPTION" properties extracted with query builder for "user" kind object. I used a report with two queries: - the first extracts all users - the second filters only a certain kind of users from the list obtained by the first query. I tried to add my dimension "Descrizione_Utente"in both queries, but it works fine only in the second query. In the first query the refresh returns this java error: IES 10901 (in attachment all screenshots). If I add in this query the id of a single user as filter, it runs correctly. Why is it necessary to filter also for "si_id" dimension and is it not enough to filter for kind equal to user only?

    Thanks as ever!

    Angelica

     

    1. Hi Angelica,
      can you please check, if the issue is really the new dimension ? My guess it is a special value of one record returned from the query.
      regards Thomas

      1. Hi Thomas, if I leave in "All_Users" Query only ID, followed by my dimension "Descrizione_Utente" the error still appears. If I add in query filter the list of all user ID, the query runs correctly and extract all values for my dimension.

        Have you other suggestions? I don't understand what could be the special value for id field.

        Angelica

         

        1. still not sure that the result set is the same. Special value just means, that one use may have a value which causes the problem.

          1. Ok Thomas, but I have to search the problematic value in "Descrizione_Utente" dimension, correct?

            Angelica

            1. Yes. It may help to turn tracing on and run the report in WebI Rich Client.

              1. Hi Thomas, with the help of my senior colleague Vincenzo Pisano, we found an user who had a description long more than 256 characters (migrated from XI R2). Reducing the lenght, the issue it solved.

                Thank you for your ever precious assistance!

                Angelica

                1. actually this was one of my first thoughts, tried to enter a description for a user with more than 255 characters but this is not possible anymore in the CMC. Great you could find the problem.

                  1. You've right! There was only one user, of 1923 users total, with this problem and belonging to older release!

                    Angelica :-)

  21. Good morning Thomas, is there a way to filter only certain Acls from universe? I made a query who extracts all accesses (Access dimension) related to usergroups (PrincipalName dimension) on any .unv or .unx of my Sap BI 4.0 environment (as you can see in attached screenshot). I would to filter this query also for: "Access" different from pattern %xxxx% , where <xxxx> is a specific ACL who denies access to Everyone, but I can't add the "Access" dimension in Query Filter section.  Thanks in advance!

    Angelica

     

    1. Hi Angelica, it is not possible to filter ACLs. In the current driver implementation, this information is retrieved from a different area of the SDK and not part of the CMS query. The dev team is working on an advanced filter feature, which may be introduced with SP5 (but not confirmed yet).
      regards Thomas

  22. Hi, does somebody know if somewhere in the CMS, and so retrievable through this universe, the values can be found from WEBI-filters.

     

    Example , suppose you have an eFashion WEBI-report-query

    and in the filter of the query, you have : State Equal "California"

     

    In this example, I would like to search for all WEBI-Document in the repository, in which a filter is used on object "State", and where the value "California" is used

     

    Mane thanks for the help

    Eddy

    1. Hi Eddy,
      this information is stored in the file attached to the InfoObject and stored in the FRS but not in the InfoObject itself. So it is not possible to get this information using the CMS DB Driver and a BOE SDK cms query. To get this information, you have to use the WebI SDK which is not in scope for the driver. Also important to know, that the WebI SDK would open each document via WebI Processing Server to be able to retrieve the information.
      regards Thomas

    2. Former Member

      Hi Eddy,
      That case what you asked for, is one of the reasons of existence of a Metadata Manager tool(i.e. Information Steward).
      Regards,
      Emre.

  23. I have another question :

    is it possible to get a list of WEBI-document where a certain universe-object is used ?

    I gave it a try but could not find a solution. All info welcome (smile)

    Thanks, Eddy

     

    1. Hi Eddy,
      same answer as in the thread about WebI-filters.
      regards Thomas

      1. So also only retrievable by an SDK ? This is very annoying : we are now using an external tool (System Profiler) to retrieve this data into a database (with a universe on top of it). I had hoped to be able to get rid of this tool by using this new CMS-Universe. But apparently not (sad)

  24. Former Member

    Hi,

    I'm getting the  "Specified RDBMS is invalid..." error message when trying to create a report based off of CMS DB universe and after checking in DataAccess\Connection server folder  , i do not see the CMS folder listed and i also dont see the CMS System Database when im trying to create new connection. What should i do to have the CMS data access driver installed? Thanks in advance!

  25. Former Member

    Thank you, Thomas!

  26. Former Member

    Can we get the folder path from query builder when we query to get list of reports from a folder which has multiple sub-folders?

    1. Hi Praveen,

      Folderpath is not available from an unique query in query builder, but is extractable in this new Universe. If you read the Thomas post above, on Oct 18 2016, you'll find what you are searching for: "To get the FolderPath of for example a WebI document or a universe, you have to use the Relationship ParentFolder. The FolderPath will be in Level 1 information. You can find an example query in the universe:".

      Bye!

      Angelica

      1. Former Member

        Thank you Angelica! When we create a report based off a universe we are getting a error message, so wanted to check if there is a way we can get from Query Builder. Will work on fixing the universe problem per Thomas SAP note and get the information. Thanks again!

        Praveen

        1. Actually the only way I know from query builder is to obtain the Folder ID (SI_PARENTID property) from a report (one report at a time):

          SELECT SI_PARENTID FROM CI_INFOOBJECTS WHERE SI_ID=<ReportID>

          and then extract its SI_PATH:

          SELECT SI_PATH FROM CI_INFOOBJECTS WHERE SI_ID=<ParentID>

          but it is not very useful in my opinion.....

          Angelica

          1. Former Member

            Yeah each report at a time wont work for us. We have 500+ reports under the main folder with multiple sub-folders. Thank you again Angelica for your help!

            Praveen

    2. Former Member

      Hi Praveen,

      I would have tried that task in Audit-DB, not here.

      https://blogs.sap.com/2015/07/15/unlock-the-auditing-database-with-a-new-universe-and-web-intelligence-documents-for-bi41/

      the folder path information is available but you should be careful if your reports are moving around across folders. If it is the case and you are including that folder path information, you may have more than one row for that particular object in your report over time..

      Regards,

      Emre.

      1. Former Member

        Hi Emre,

        I created a report using Auditing universe however I do see reports that wont exist on the server. I guess it is showing reports that are deleted - I tried filtering by deleted successfully NOT IN LIST event type and they are still showing up. Do you know if there is a way I should filter to just show the reports that are existing on server ? Thanks in advance!

        1. Former Member

          It should work Praveen, all stuff is out of the box, don't worry.(smile). (By the way Auditing would not give you a up to date status of the system, but the tracks of used documents.)

           I would check the configuration at two places:

          1. CMC Audit: Check Auditing is ON and  everything is well configured, like the events you want to track are marked and the ADS Database configuration is correct (In case if you are activating auditing first, you should wait for a while so that some data is collected). 
          2. In IDT at the files you downloaded with the link; by connection parameters > "edit relational connection" Entries are matching with the ADS Database configuration parameters above..
  27. Hi 

    I am working on some reports on CMS data access, using SAP universe (BI Repository Analysis universe/BI Platform CMS system database)

    But getting Error message: Specified RDBMS is invalid : CMS. Same error message in IDT connection level also.   

    Is there any easy-way to fix this error and access SAP universe (BI Repository Analysis universe/BI Platform CMS system database). 

  28. Former Member

    Hi Guys,

    I need to get the parameter list of the objects/reports (as a report).  Is it in a way over this CMS Driver possible? In Audit DB It seems, I can get the entire generated query but not the parameters..

    Emre.

  29. Hi Emre,
    guess this is a similar question like the one adressed in this Discussion
    regards Thomas

    1. Former Member

      Hi Thomas, 

      Thank you, you are right it is a similar issue. 

  30. Former Member

    hi

     

    download link is not working. Can you please enable it .

    1. checked the link and could not find an issue.

  31. Hi Thomas ,

    Fantastic write up and follow ups .

    Can we know what are the functions supported for CMS database reporting . We are using SQL server and trying to get substring from "Name" object but failing .

     

    Thank you

    Sateesh

     

    1. Hi Kumar,
      the CMS DB Driver is independent from the database type (SQL Server, Oracle, ...) cause the queries are executed using the BOE SDK. Therefore it is not possible to use database functions. The introduction of additional functions would need specific implementation in the CMS DB Driver, which is not planned so far.
      regards Thomas

  32. Former Member

    Hello Thomas and Thank you very much for creating and maintaining this blog. Personally, I feel this is one of the best features that I've seen in a while! I'm looking forward to the next version....

    I do have one question, is it possible to create a query that can display all elements of a universe (example: Classes, Objects, Object Details, etc.)?

    Regards,

    Jon

    1. Hi Jon,
      thank you for the feedback, really happy that we could provide a very useful new feature to our customers.
      The universe information is stored in the file attached to the InfoObject and stored in the FRS but not in the InfoObject itself. So it is not possible to get this information using the CMS DB Driver and a BOE SDK cms query. To get this information, you have to use the Universe SDK which is not in scope for the driver (also not planned for future releases so far). We had a similar question around WebI documents in this blog. What we are planning to provide is a feature to develop a 'Java plugin' for the CMS DB Driver. This would allow to use additional SDKs to read information, for example from WebI documents or universes. The CMS DB Driver would use the plugin and provide this as an additional table in the universe.
      regards Thomas

  33. Former Member

    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

    1. It is not possible to create a multisource universe on Audit and CMS DB in IDT, cause CMS DB Driver is not a relational driver.
      A solution could be to merge in a Web Intelligence document using the Audit Universe and the CMS DB Driver universe.

      1. Former Member

        Ok, thank you for your answer.

  34. Former Member

    We've applied the latest patch update 5 on 4.2 SP3 and this driver appears to have been deleted/missed off the build. Has anyone else applied a patch and found the same issue.

    Thanks

     

    1. In my scenario (distrusted environment), after applying the patch I had to modify the base installation and go to the drivers section and add it. There's a note for this within the release.

    2. Former Member

      Hi, 

      The installation of the patch delete the driver. so to install the driver you need to modify your installation from the control panel , and check the box #feature.CMSDBDriver.name# . 

      For information, the latest patch is 6 (wink)

  35. Former Member

    Hi

    I'm trying to get the recently opened documents, In query builder the query is as follows 

    SELECT SI_RECENT_DOCUMENTS
    FROM CI_SYSTEMOBJECTS WHERE SI_KIND='USER' AND SI_NAME= 'myname'

    anyone have idea how this can be done ?

    1. Former Member

      Hi, 

      You can use this query: 

      select top 100000 SI_NAME, SI_DESCRIPTION, SI_UPDATE_TS, SI_CREATION_TIME, SI_AUTHOR 
      from ci_infoobjects where si_kind='webi' and si_instance=0 order by SI_UPDATE_TS
       
      1. Former Member

        I would like to use the CMS universe, I don't have an issue using query builder 

        Thanks

    2. You have to use a Technical Query to get this information, the query Sample-QT-Properties with Filter (Server) in the universe may help to get the solution.
      regards Thomas

      1. Former Member

        Thanks

        What is "Technical Query" ?

        I can change the Unx if needed but don't know  which object to add

        B/R

        Dror


        1. In the universe you can find the a sample for the technical query. Basically the technical query allows you to get all properties in a table with fix set of columns. You will not have universe objects like Id, Name .., just PropertyName, PropertyValue .. it is close to the HTML results you get in query builder.

          1. Former Member

            So It can not be done ?

            I would like to get the last 10 webi si_id that user used ?

            1. You can get the same information like in query builder, but it may not be so easy to get the document detail informations. Attached a screenshot of a query.

              1. Former Member

                Thanks

                I will try it and update

              2. Former Member

                Hi Thomas

                Your solution did not worked

                I'm getting an error. CSError

                Thanks

                Dror

                1. tested on my BI 4.2 SP3 system, working fine. Please make sure you use Equal To for Property Name.

                  1. Former Member

                    How can  I upload my print screen ?

  36. Former Member

    Hi Thomas,

    Thanks for this document.

    What is the function to retrieve a specific array value inside a property. For example.  if I want to retrieve SI_FOLDER_NAME1 from below image, should I create a dimension

     Properties.SI_Path.SI_FOLDER_NAME1. I tried it but and returns blank value. Please suggest if there is any specific function to retrieve any specific property array value.

     

    Regards,

    Animesh Kumar

    1. Hi Animesh,
      guess you have to use a technical query to get the values and you have to filter in the WebI document. I'm not aware of a query syntax for query builder to get this value directly and there is no special function implemented in CMS DB Driver to handle this.
      regards
      Thomas

  37. Former Member

    Hi

    where do I see list of functions the driver support, for example concatenation 

    Thanks

    1. Functions are not supported / implemented, cause this driver is not a standard database driver. See also the reply from this discussion. question on functions

  38. Hi,

    We are getting error : Table or view does not exist.

    Upon checking in the .dfx in IDT it seems there is no "Properties" table in the CMS database.

    We have only the following tables in the BI 4.2 sp3 p6 CMS database:

    CMS_ALIASES7
    CMS_IDNUMBERS7
    CMS_INFOOBJECTS7
    CMS_LOCKS7
    CMS_RELATIONS7
    CMS_SESSIONS7
    CMS_VERSIONINFO
    CMS_CCFRONTIER7
    CMS_FRONTIER7
    CMS_ROOTFRONTIER7

    .

    We have upgraded from BI 4.0 sp6 p4 and we are currently on BI 4.2 sp3 p6.

    Is there any steps to be done after upgradation to 4.2 sp3 to get the "Properties" table ?

     

    Thanks

    Orion

     

    1. Hi,
      the list of tables will be shown, when you use a relation connection to the CMS DB.
      The CMS DB Driver is not a relational connection and you will see only the virtual tables provided exposed from the CMS DB Driver.
      Guess you have to install the CMS DB Driver first and then you should be able to use the sample universe provided.
      regards Thomas

  39. Is there a simple way to get a list of Users, UserGroups AND the UserGroups Description?  I am able to get a list of UserGroups and the UserGroups Description using the Technical Properties objects and then using filters at the report level to get SI_DESCRIPTION.  However, when I attempt to create single a query that shows Users with there UserGroups and the Group Description it comes up with a query error.

    I have also found that when I create a query using the Technical Properties and attempt to create a query filter on PropertyName to select SI_DESCRIPTION, I must use the Equals To comparison. In List yields no results.  The problem with this is that the CUID and NAME come back blank when using a query filter on PropertyName.

    1. I believe I figured out my own question.  I looked at the sample queries in the universe and noticed using Equal To comparison on PropertyName will bring back multiple values when the value entered is a comma delimited list.  No need to use In List.

      Secondly, the objects for InfoObjects I would like to include in the results when also including PropertyName and PropertyValue in the same query should be includedfor in the comma delimited list.

      Since I do not have permission to include images in this post, here is the query script generated by my query:

      SELECT

      int(Properties.si_id),
      Properties.si_cuid,
      Properties.si_kind,
      Properties.si_name,
      PropertyValue(Properties.technical),
      Property(Properties.technical)
      FROM
      Properties
      WHERE
      (
      Properties.si_kind = 'UserGroup'
      AND
      Property(Properties.technical) = 'si_id,si_cuid,si_kind,si_name,si_description'
      )

       

      1. For the technial query you found the solution already.
        Just looked into the standard query to get Users, UserGroups AND the UserGroups Description. Basically this would be possible using a query with User-To-Groups relationship. I have recognized, there is no object in the universe to get the description. My recommendation would be to add the Description object to the universe for the levels you need and use a standard query.
        regards Thomas

        1. Thanks Thomas for the information.  Adding a new object using 'Properties.si_description' works great in IDT queries.  I was also able to add another object using 'Level1.si_description'.  However, I am getting an error in Web Intelligence with the same exact query that works in IDT.

          1. The following query seems to work fine when run as a query in IDT but when creating a Webi document the same query fails.

            The following database error occurred: (CS) "Unexpected behavior : com/rsa/certj/cert/RDN" . For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901) (Error: INF )

            SELECT

            int(Properties.si_id),
            Properties.si_cuid,
            Properties.si_kind,
            Properties.si_name,
            Properties.si_description,
            relationships(Properties.si_usergroups),
            int(Level1.si_id),
            Level1.si_cuid,
            Level1.si_kind,
            Level1.si_name,
            Level1.si_description
            FROM
            Properties,
            Properties Level1
            WHERE
            ( Properties.join=Level1.join )
            AND
            Properties.si_kind = 'User'

            1. Hi Michael,
              can you please check the data type of the new dimension you have created in the universe. Should be 'String' if you have user/group descriptions which are not more than 255 characters. If descriptions in the CMS are more than 255 characters, you should use 'Long Text' (as used in the dimension PropertyValueLongText of the universe).
              The difference between running a query in IDT or in WebI is, that IDT is not considering the data type of an object, while it is important for WebI.
              regards Thomas

              1. I tried changing the Data Type to 'Long Text' but that did not fix the issue.  I had to change all UserGroup description values to be 256 characters or less for this to work in Webi.  Seems to still be a problem with CMSDB Driver and very long values.

                1. Just checked in the CMS, it is possible to enter more than 255 characters for a description. The dimension in the universe should be defined with Type 'Long Text' and the select statement should look like 'text(Level1.si_description,"300")'. This will make sure that the driver will handle the values properly and will not return more than 300 characters in this sample. Also my previous statement to take PropertyValueLongText as reference object was wrong, cause this object doesn't use text().

                  As a rule, whenever a string can be more than 255 characters, you should use Type 'Long Text' and the function text(), with the max. number of characters that should be returned, in the select statement.

                  1. Thanks Thomas.  Your suggestions works great.  I am even able to use "500" instead of "300" and it still works.

  40. @Thomas Jentsch

    Thanks for sharing this information. 

    We have few requirements. Does new CMS will provide with any solution?

    1. We use Analysis for Office workbooks & Lumira documents. Most of these documents are created by users, consuming BEX reports. To do impact analysis for any BEX changes to final AFO workbook or Lumira, we need to have meta=data or link between AFO workbook to BEX and Lumira to BEX. 
      Does CMS database store this meta-data? In case of WEBI, we can get the link between WEBI Report to BEX using SI_WEBI_DOC_PROPERTIES (extract from XML). We are not able to see that for AFO (Our current version is BO 4.1, want to check if 4.2 will solve this)
    2. WEBI reports with multiple tables & datasource - we are finding very hard to get this meta-data link between WEBI reports to multiple BEX. Is there any solution for this?

      Thanks !!

    1. I have no further information, which metadata of AOF or Lumira is stored within the InfoObject in the CMS and which metadata can be retrieved from the file attached to the InfoObject in the FRS only. Currently there is no plan to implement additonal features in the CMS DB Driver, to retrieve metadata from the files attached to an InfoObject. An option may be the development of additiona plugin-tables (see also this blog https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+BI+4.2+SP3?focusedCommentId=464694770#comment-464694770). Hope I can buiild a sample for this approach soon and can share with the community.
      regards Thomas

  41. Hi Thomas. We are on BI 4.2 SP3.2, and we started using this new universe some weeks back. But since a few days it is not working anymore on our PRD-environment. The connection, when testing through IDT, is still working.

    But when refreshing a very small simple test-report which worked before, we got the error

    The following database error occurred: (CS) "Unexpected behavior : while trying to invoke the method com.sap.connectivity.cs.java.drivers.sdk.sqlparser.IColumnDefinition.getResultFieldDef() of a null object loaded from local variable 'resultColumnDef'" . For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901)

    I searched on several of the strings of above error-message, but I cannot find anything that would lead me to a solution.

    Have you any idea ?

    Thanks a lot.

     

     

     

    1. I have no solution so far, can you please create a support incident for the issue.

      1. OK, thanks. I have logged a support-ticket

  42. Hi Thomas.

    In 4.2 Sp3, if I refresh a webi report with more than one query, I get this error:

    If I refresh one query at a time all runs fine.

    Any idea?

    Angelica

     

    1. Hi Angelica,
      can you please check on the connection of your universe, the value for 'Maximum Parallel Queries', should be 0 or 1.
      regards Thomas

      1. Hi Thomas, in my connection I have this settings (4 max par query):

        I use this connection/universe to connect to my customer production environment that uses an older Sap BI 4.0 Sp10 Fp3

        Angelica

        1. that should be ok, please change the the value to 0 or 1 this should solve the issue.

          1. Thanks Thomas! With "1" value all query run fine in the same time!!!!

            Angelica

  43. Hi Thomas,

    We are trying to reproduce a CMS Query to retrive the webi documents with prompts

    SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS,

    SI_PROCESSINFO.SI_WEBI_PROMPTS, SI_PROCESSINFO.SI_FILES,
    SI_PROCESSINFO.SI_PROMPTS FROM CI_INFOOBJECTS
    WHERE SI_KIND = 'WEBI' and SI_INSTANCE = 0 and
    SI_PROCESSINFO.SI_HAS_PROMPTS=1

    Theroretically query seems to be possible using CMS Driver based universe but the query does not provide any row (appears empty) despite that the when CMS query is executed on 

    querybuyilder dozens of rows are returned.

    Any idea?

     

    1. There is a limitation in the current version and SI_PROCESSINFO will not return values. It is planned to fix this in SP5. A workaround could be a technical query.

  44. Hi Thomas, I see in planned activities "Git Hub repository" as Topic with this description "We plan to create an open source project on the SAP Git Hub platform to collaborate on Universes and Web Intelligence documents". Are there news about the start of this activity? I created N reports that I would to share with other people to help us each other.

    Thanks in advance!

    Angelica

    1. Hi Angelica,
      there are some internal processes we have to sort out, so far there is now news when and if we can start this. Will try to get an update. Really would like to support here to get a community in place.
      regards Thomas

      1. Hi Thomas, I created a new page to collaborate on wiki, posting info about reports I'm creating day by day. If you find time to see it and adding you as watcher I would be glad to have, time by time, your opinion and help!

        URL: https://wiki.scn.sap.com/wiki/x/ozTeGw

        Best regards, Angelica

    2. Former Member

      Hi Angelica - is it possible to share the reports that you created?

      1. I would like share them, you have to consider only that I work with an empty 4.2 sp3 installation to explore a full 4.0 sp10  system. I don't know if query builder properties are changed from 4.0 to 4.2 release. Thomas is there a share space where can I upload these reports with short, but necessary, documentations to explain them?

        Angelica

        1. It is legally not possible for me to provide a share space.

          1. Ok! Thanks and good work!

      2. Hi Praveen! I'm trying to share my experience (built with the teachings of "our " precious Thomas (wink)) creating a new space under Wiki in "Business Intelligence (BusinessObjects) stage" at this url: https://wiki.scn.sap.com/wiki/x/5zHeGw

        Please, let me know if there are issues on visibility of this page (it is my first experence of collaboration on sap wiki). The content will be updated every time I'll have  free time to work on it. 

        Angelica 

        1. Angelica, If I go to https://wiki.scn.sap.com/wiki/x/5zHeGw , it leads me to To delete please

          But I do not see any content in it.

           

          Eddy

          1. Hi Eddy, don't you even see the introducion (started with "As explained....") under the title? For the content I have to ask permission to insert links or files, at the moment I can't do it....

            Angelica

            1. No Angelica, I cannot see it. See attachments for a screenshot. Or , if the attachment does not work, see here :

              https://1drv.ms/i/s!Aqp3bTg6bNXihtgGOVDp0K8cLkDc_w

               

              Eddy

              1. Hi Eddy, can you verify this new url: https://wiki.scn.sap.com/wiki/x/ozTeGw ?

                My collegues, with a normal login user, can view all I wrote into it.

                Thank you in advance!

                Angelica

                1. Yes, Angelica. Now it is visible !

                  I had already put a "watch" on it (smile)

                  Thanks a lot

                   

        2. Former Member

          Hi Angela! even i get the same error as E. Ocula. It says page deleted

           

          -Praveen

          1. Yes Praveen, now I'm working on this page: https://wiki.scn.sap.com/wiki/x/ozTeGw , but I'm in contact with sap support team , because this page will be not visible until someone publishes it.

            Maybe if you click on url above, you'll only will see the title but without any other information. I'm sorry but is the first time that I write something of different of a comment on wiki......and I'm Learning day by day how to do!!!!

            Angelica

          2. Hi Praveen, can you verify this new url: https://wiki.scn.sap.com/wiki/x/ozTeGw ?

            My collegues, with a normal login user, can view all I wrote into it.

            Thank you in advance!

            Angelica

            1. Hi Angelica,

              I can view the content now (smile)

              BR,

              Michael

            2. Former Member

              It is working now. Thank you so much, Angelica!

              -Praveen

  45. Hi Thomas, I would to extract additional informations about connections, as "service name", "user name", "password" that we inserted when we created all connections in IDT.

    Is there a property that can give me these information from cms repository?

    Angelica

    1. Hi Angeica,
      just looked into a connection InfoObject for a universe and there are some properties (SI_CONNECTION*) containing information for this connection, but most are encrypted ox XML. With thte CMS DB Driver it is currently not possible to get the same level of details like for example in IDT.
      regards Thomas

  46. Thomas,

    Is there a way to access User Attributes that are mapped to Windows AD or LDAP attributes?  I created a few User Attributes via the CMC in the User Attribute Management area but I have not figured out how to access these values via the BI universe.  I tried defining a Dimension object using Properties.si_[attributename] and defining a Long text object using text(Properties.si_[attributename],"1000") but the values appear as null.  I even tried using @VARIABLE('SI_[ATTRIBUTENAME]') but still null.

    Any thoughts?

    Thanks!

    Mickey

    1. Can you provide a screenshot of the results in Query Builder, so I can see how this is retrieved and displayed there ?

      1. select si_name, si_userfullname, si_custom_mapped_attributes
        from CI_SYSTEMOBJECTS
        where SI_KIND = 'User'
        ORDER BY SI_NAME

        1. Former Member

          Hi Michel,

          You can fetch it using Technical Query. You may refer Universe Query - Sample-QT-Properties with Filter (Server) in IDT.

          In PropertiesName filter mention Properties Name in (si_name, si_userfullname, si_custom_mapped_attributes) AND SI_KIND = 'User' and SI_NAME = 'UserName'

           

          Regards,

          Animesh Kumar

           

           

          1. Thank you Animesh for providing the solution. Indeed this is currently only possible using a Technical Query. Having this in an object definition within the universe would require additional functionality, which is not available yet, could be a candidate for an enhancement.

  47. Hi Thomas,

    I just imported the sample universe and reports , when i try to run the sample reports they are working fine in Launch pad ,but when i try to change the prompt values am getting error "open connectivity driver error"  In IDT if i test the connection it is showing Test successful but when i try to parse the objects in IDT getting the error "No query filter defined".

    Could you please help me with the next steps to be followed or checked.

    Regards,

    Vijetha S

     

     

     

    1. Parsing the objects in IDT is not possible, cause the CMS DB Driver has just a virtual data model using the SDK and parsing is not implemented. Would need more details which query and prompts are not working ? It may be usefule to create an incident, so our support colleagues can help.

  48. Thomas, me again... trying to identify the "VIP" of reports in our deployment, 1) those that have been viewed recently, 2) modified recently, 3) large in size, 4) scheduled, 5) number of times viewed, 6) number of times modified, 7) days in production, etc. Anyway-

    I'm working on #4, scheduled reports, and when I open the 'Scheduling' report from the samples pack, I see the 'Recurring' tab. When I refresh the report, I choose Ancestor of 18 and 23 to get personal and public folders. I don't put any date criteria in. On the Recurring tab, there's only a few reports. If I go into CMC, Instance Manager, and do a Find on those with a status of Recurring, I get 471 items. It's proving very difficult to find this list of 471 items within a Report from the CMS driver.... Am I helpless? Or is it expected to be this difficult? (smile)

    I found one report example where it is scheduled, and status is currently Recurring (per CMC Instance Manager), but in the Sample Scheduling\Recurring report, it is not being listed because the New Job ID is NULL_VALUE for this report, and the Recurring page filters out any reports where New Job ID is not equal to NULL_VALUE?

    1. Have to look into this, may be checking this with New Job ID is not the only / right indicator. But can do this earliest in August.

    2. Hi Brian,

      in the url below you can find how I built two queries to extract, in a custom report, all recurrences and their linked objects, with a 4.2 Sp3 release.  I hope this can help you!

      Angelica

      Recurrences_list_queries

      1. Thank you, this was very very helpful!!! You seem to be correct, the select statement of the "Recurring" object in the sample universe appears to be wrong. I created a new universe linked off of the sample universe so I could add the corrected "Recurring" dimension. I really appreciate your help and the great document with screenshots!!!

        1. You are very kind...

          I'm trying to collaborate in Wiki through new space with a collection of simple custom reports that I often use to explore CMS, for my working needs, at this url: https://wiki.scn.sap.com/wiki/x/ozTeGw

           

  49. Hi Thomas, 

     I have been following this wiki for some time now. Thanks for publishing it.

    We have been trying to get information about : Derive a query from the CMS DB driver Universe which gives the list of reports a user has "view" access ( reports which user can see in BI launchpad) .   Is it possible/workaround  using this universe ?

    Something similar to  query builder  SQL query : Select TOP 10 SI_ID, SI_NAME, SI_CUID, SI_KIND From CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS Where SI_KIND='webi' And IsAllowed(1067216, 3)  . But this doesn't provide list of reports from inbox and favorites. So, we want to derive this information from the new CMS universe. Can you please let me know if this is possible using new CMS universe.

    Thanks,

    Srinivas Perumandla

    1. Hi Srinivas,

      if you scroll up this blog, you'll find my same question for edit right on universe posted on Feb 22, 2017. Unfortunately Thomas replied me : "Thank you for posting this idea. We may look into this for BI 4.2 SP5. "

      So I think that it is not still possible.

      Angelica

    2. Former Member

      Hi,

      Seems like I am looking into the same here - I basically need a table that gives me users, folders, documents - have you been able to create that using the Universe?

      1. Hi Andreas, all what you can do is to create a query that extract the security defined on any object (webi, folder, universe, etc...) as you can see defined on "user security" tab on CMC. The dimensions that extract these information are: PrincipalName and Access under "Infoobjects-Security" folder. "PrincipalName" retrieves the name of the user or of the usergroup and "Access" retrieves the ACL or specified right granted to that user or usergroup.

  50. Former Member

    Hi,

    Has anyone had any luck retrieving the Who, When , Where and What from default Publication information. The bundled BI on Bi Publication report is failing unfortunately.

    I have tried using the technical properties class but nothing uselful exposed sadly.

    Thanks.

    1. Hi Darren, If I run bundled "BI on BI Publication" report,  I obtain all correct information. What error are you receiving?

  51. Former Member

    When I try refreshing the query I get the error message below.

    The following database error occurred: (CS) "Unexpected behavior : while trying to invoke the method com.sap.connectivity.cs.java.drivers.cms.data.Relationship.getIds(com.crystaldecisions.sdk.occa.infostore.IInfoObject, java.util.Set) of a null object returned from java.util.Map.get(java.lang.Object)" . For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901) (Error: INF )

    If I remove L1 objects Name and Kind it works but limited info.

    1. L1 Name and Kind are referred to Name&Kind of published objects (a report for example). If you remove them, you obtain only publication information, without any reference to them source objects. Please try to remove from query "Publication-to-Principals" dimension, because often two relationships in the same query cause some issue (in my little experience in these months).

       

  52. Hi. I'm looking for an example to produce a list of WEBI-Documents which are build on a certain .unv or .unx universe. I found a way to produce it, with 2 queries : one of the .unv universes, one on all the WEBI-Docs. And then an Input-control of the universe-ID on the data-set of the WEBI-docs. I had to do it this way as apparently it's not possible to use the "webi-to-unv" objects as query-filters (sad)

    But I hope there is a better way to accomplish this. Has somebody accidently a good example ?

     

    Many thanks

     

    1. Hi Eddy, you can make these two queries:

      1) UNV_to_Webi: Id, SpecificKind, Name, Unv-to-Webi, Kind (L1), Name (L1) with Id equal to <universe's Id to which you are interested> as query filter

      2) UNX_to_Webi: Id, SpecificKind, Name, Unx-to-Webi, Kind (L1), Name (L1) with Id equal to <universe's Id to which you are interested> as query filter

      Relationship dimensions cannot be used as query filters.

      Angelica

      1. Thanks Angelica. This works. Thanks a lot.

        Now I want to have the FolderPath of the WEBI-Doc as well added. But when I'm using "FolderPath (L1)" I get an error. I see some other post on this (about the order of the objects when FolderPath is used). I tried several orderings of the objects but I still have the error.

        Maybe I'm not using  the latest version of the universe. The one I'm using is the original download as described in this wiki-page (Dec-2016). Is there already a newer, improved, one available somewhere ?

        Eddy

        1. Hi Eddy, you have to substitute in both queries with this dimensions: Id, SpecificKind, Name, Unv-to-Webi, Kind (L1), Name (L1), ParentFolder (L1), Kind (L2), FolderPath (L2), Name (L2) with Id equal to <universe's Id to which you are interested> as query filter

          So you'll obtain:

          1) Id = Universe_ID

          2) SpecificKind = KindOfUnivserse

           3) Name =NameOfUniverse

          4) Unv-To-Webi or Unx-to-Webi = LinkedWebiID

          5) Kind (L1) =LinkedObjectKind

          6) Name (L1) =LinkedObjectName

          7) ParentFolder (L1) =LinkedObjectParentFolderID

          8) Kind (L2) =LinkedObjectParentFolderKind

          9) FolderPath (L2) =LinkedObjectParentFolderPath

          10) Name (L2) =LinkedObjectParentFolderName

          Entire webi's path is given from FolderPath (L2) + Name (L2). I usually create a variable "EntireFolderPath" that is composed from this formula: =[<queryname>].[FolderPath (L2)] + Char(92) + [<queryname>].[Name (L2)]

          The only problem (I don't know why) is that this query extracts duplicate rows for each report and only one of them contains information about linked webi's name, parentfolder and folderpath. So you have to add a filter at report level that shows, for entire block, only rows in which "ParentFolder (L1)" dimension is not null.

          Let me know if it works also for you! Thanks a lot!

          Angelica

          1. Hi Angelica. This works perfect !

            Many thanks !

            Eddy

             

            1. Thanks to you for the try! Good work!

              Angy

              1. great work, thank you for the support and knowledge sharing.

                1. Welcome back Thomas! Do you know why this query extracts duplicate rows for each report and only one of them contains information about linked webi's name, parentfolder and folderpath? I did something of wrong in your opinion?

                  1. This is by design, preparing the relationship information in memory is quite complex and especially with more than one relationship level, this will happen.
                    regards Thomas

                    1. Thank you for the explanation!

  53. Hi Thomas,

    Its really a great work, which make most of the admin work easy. am following this post from quite a long time now.

    We are using Multitenancy Manager in our organization as we have more number of tenants.

    I would like to know from any way Can we pull the Tenant wise data in to the report using this Universe.

    Like No: of reports, universes, users for each Tenant.

    1. I'm not a real tenant expert, but should be possible as the tenant_id is stored within the InfoObjects. You can define a query filter for the tenant_id or you should include the tenant_id in your query results and filter / calculate in WebI.
      hope this helps, regards Thomas

  54. Hi Thomas! Last week we upgraded ou sandbox system from 4.2 Sp3 to Sp4 Fp2. Now some query filters and security dimensions have problems. SP4 installation (without FPs) gives the same issues. Below the details:

    1. Query filters “Matches pattern” and “Different from pattern” don’t work more and return a generic “OpenConnectivity driver error : Exception parsing query” error
    2. Security dimensions “PrincipalName” and “Access” don’t work more. They don’t return an error executing queries but don’t extract more any result for whatever kind of object
     Example of a query with “Matches pattern” query filter: Issues

    SELECT

      Properties.si_cuid,

      Properties.si_name,

      Properties.SI_EMAIL_ADDRESS,

      flag(Properties.si_passwordexpire),

      Properties.SI_DESCRIPTION,

      flag(Properties.SI_FORCE_PASSWORD_CHANGE),

      datetime(Properties.si_lastlogontime),

      datePart(Properties.si_creation_time),

      datePart(( datetime(Properties.si_update_ts) )),

      relationships(Properties.si_usergroups)

    FROM

      Properties

    WHERE

      Properties.SI_EMAIL_ADDRESS  LIKE  @Prompt('Insert partial address (eg: %gdf%):','A',,Mono,Free,Persistent,,User:0)

     The following database error occurred: (CS) "OpenConnectivity driver error : Exception parsing query: SELECT Properties.si_cuid, Properties.si_name, Properties.SI_EMAIL_ADDRESS, flag(Properties.si_passwordexpire), Properties.SI_DESCRIPTION, flag(Properties.SI_FORCE_PASSWORD_CHANGE), datetime(Properties.si_lastlogontime), datePart(Properties.si_creation_time), datePart(( datetime(Properties.si_update_ts) )), relationships(Properties.si_usergroups) FROM Properties WHERE Properties.SI_EMAIL_ADDRESS LIKE '%gdf%' " . For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901) (Error: INF )

     

     Example of a query with "Security dimensions" in Result Objects: Issue
     

    SELECT

      Properties.si_name,

      ValueSecurityInfo(Properties.principal),

      ValueSecurityInfo(Properties.access)

    FROM

      Properties

    WHERE

      (

       Properties.si_kind  =  'universe'

       AND

       ( TOP 10  )

      )

     

    Returns only name for top 10 universes and nothing else.

      1. I'm not aware of this issue, can you please create an incident, so the dev/support team can work on this.
      2. This is a known issue, planned to be fixed with Patch 3.

      regards Thomas

      1. I have created the incident for topic 1, dev team has already fixed and it will be delivered within one of the next patches (have no pach no so far)
        regards Thomas

      2. Hi Thomas, I warn you that in 4.2 relase SP4 Patch 4 both issues exposed above still remain.

        Thanks for your attention!

        Angelica

      3. Hi Thomas, today I created new incident 444268 / 2017 in chat with expert to solve the issue on security information missing in SP4 upgrade (and still persisting in patch4). 

        Angy

  55. Former Member

    I am getting the usual "Specified RDBMS is invalid : SAP BI platform CMS system database" error.
    I am already familiar with  "2405568 - When install the BI 4.2 SP03...". That only applies if you have a "working environment" from the same patch.
    Since we just upgraded from SAP BusinessObjects BI Platform 4.1 Support Pack 5 Patch 3 to SAP BusinessObjects BI Platform 4.2 Support Pack 3 Patch 8,
    we do not have a working source to copy from.

    I am missing the folder D:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\cms

    How could the CMS system database driver be missing if it is installed by default? 
    Since the install for both the Server and Client do not have any configurable options to choose to install drivers or not, how do I get the missing
    driver installed?

     

     

    1. When you installed, did you confirm that under Custom \ Expand, Database Access, that the following driver was selected: #feature.CMSDBDriver.name# 

      (yes, that is really the name of the driver that shows in the installation wizard)

  56. Former Member

    Brian, thank you for the reply.

    But no, I was not presented a "Custom/Expand" opportunity. I captured every screen as part of the 4.2 SP3 install, and it went from:
    WinRAR self-extracting archive to Check Prerequisites to the Installation Wizard Welcome screen to License Agreement screen to
    New License Key Agreement screen to Existing CMS Deployment screen to Select Installation Mode (Standard vs. Phase-wise),
    I chose Standard, next was the Web Application Deployment screen (Deploy now vs. later), I chose now, and then the 
    Start Installing screen came up...and that was about it.

    This was also the case for the Server install, the Client install on the server, and the Patch 8 installation. 

    Besides choosing the Installation Mode and Web Application choice, there were no other opportunities to customize or expand 
    any of the the installation components. 

    Here are the exact SAP install files used:
    BIPLATS4203_0-80001044_P1.EXE
    BIPLATSVR4203P_8-80001044.EXE
    BIPLATCLNT4203_0-70000090.EXE
    BIPLATCLNT4203P_8-70000090.EXE

    1. Instead of Standard you could have chosen Custom to review all the components installed.

      I don't know if this is exactly your issue or not, but it's quick to check. In Add\Remove programs, click Modify on the install, and go into Custom Installation and see if this driver is selected. If not, it's easy to add it and run the modify on installation.

      If it is already checked, then you can cancel out of the Modify installation without it hurting anything. Then someone smarter than me will have to chime in.

  57. Former Member

    Again, thank you your reply.
    Just to make sure it is clarified - the options were not Standard vs. Custom, they were Standard vs. Phase-wise. Here is the exact text from the SAP
    installation:
    "Standard Installation: Installation is performed in single-phase."
    "Phase-wise Installation: Installation is performed in two phases - caching and Installation after caching. During Caching the system downtime is eliminated.
    Installation after caching is the actual installation process where you experience downtime."

    To me, Phase-wise would not have given an option to review components. If that was the path I should have taken, the help text was way off.

    1. Ok- my terms were off then. Somewhere during the installation it allows you to select the components that you want to install. I doubt this was missing from the installation, but I don't know what what point and where, what you need to click to review the components you are installing. If, in Programs and Features, you click on the Uninstall/Change (may be different due to your OS), and click Modify, you should see where it allows you to select the features that are being installed. If you scroll all the way to the bottom, you should see the option for the CMS driver.

  58. Former Member

    Brian - thank you. I will dig some more.

    1. Hi Brian&Mike! I had the same problem. When upgraded my Sap 4.2 Sp3 to Sp4 all reports developed to explore cms worked fine but, when I applied Fp1 of Sp4, the cms driver was removed by patch!!! This happened in server installation but in client tools installation also! I had to modify both installations of full Sp3 to select again "CMS DB DRIVER" under "Database Access and Security" feature to correct the situation. After modified them, I did not obtain more the message "Specified RDBMS is invalid : SAP BI platform CMS system database", because the "CMS DB DRIVER" has been reinstalled!

      Surely SP3's Patch 8 cause the same fault of the Sp4's Fp1: both remove the initial default installation of new "CMS DB DRIVER".

       

      1. Former Member

        Angelica - thank you for your reply. This proves there is a flaw relating to this driver in one or more patches. I should be able to modify and add this driver.
        Hopefully this will get caught and fixed by SAP in the future. 

        1. Hi Mike,
          unfortunately the CMS DB Driver was not a separate item in the installer in the first versions we delivered this new feature. Changing this for the new version introduced a restriction we know, but still was a good and necessary step to take. And yes, this should be fixed for the future. Appreciate your feedback and support to improve this.
          regards Thomas

          1. Thank you Thomas for the explanation. 

            Angelica

          2. I have exactly the same problem as Angelica or Mike described above. However, the BI platform runs on UNIX environment and ./modifyOrRemoveProducts.sh allows me only to uninstall the patch once I select the latest patch or SP installed on the BI server. Meaning I have no possibility to install the missing drivers when selecting the latest patches or SP's. "Unistall Confirmation. This will remove SAP BusinessObjects BI platform 4.2 SP4 Patch 4 from your machine." Continue or Back.

            I would like to share that the only way I can select "Modify" is when I select the initial (old) installation version of SAP BI Platform. SAP BI experts please correct me if I am wrong and advice if there is another way to install missing drivers on UNIX.

            I have also tried to update to the latest BO 4.2 SP04 Patch 04 and it did not install the missing drivers when making update installation. So currently the only solution is to do the "Modify".

            OS: Red Hat Enterprise Linux Server release 6.8 (Santiago)

            SAP BI: BO 4.2 SP04 Patch 01

            In addition to Brian Kudera replies above, the update install of a SP or a Patch does not allow to select "custom" installation on the BI server. You can only select "custom" installation once making the initial install of SAP BI plartform on the BI server. I have done some testing on BI Server and also found it described in SAP BI platfrom installation/update documentation. Below you can see the step by step screens when making the UPDATE installation on BI Server:

            1. Choose Language, for e.g. English
            2. Select Destination Folder
            3. Check if all the prerequisites succeeded:        
            4. Warning – Copy rights
            5. Software License Agreement
            6. Existing CMS Deployment Information screen is displayed. Enter your CMS credentials and press Enter.
            7. Select Standard or Phase-Wise Installation
            8. Deploy web application now (or later)
            9. Are you ready to begin the installation? (Back, Quit, Install)
  59. Hello, I am getting error message "Access to share expired. Share is no longer available" when trying to follow Download link in "Get started" section above.

    Full URL is: https://mdocs.sap.com/mcm/public/v1/open?shr=EqWHuiXKyPlBYsHQUM4qjjwj3BRxNmiZAFLYQuzFqFE

    I did not find anyone else reporting the same problem on this page.

    Thank you in advance for your help!

    1. I was trying to download and encountering the same issue, seems like they have removed the files from the share directory. Thanks Arun

      1. Link should work again, now.

        1. Thanks Thomas for sharing the files.  We are currently on 4.2 SP3, is it possible for you to share the files from 4.2 SP4 version. I have no idea when we will be upgrading and don't want to make an hassle of having two different versions of Query Builder Universes and Webi Reports. Please let me know. Thanks Arun

          1. Hi Arun,
            it is not supported to use the SP4 version of the driver and universe on SP3. When upgrading, you may have to remap the universes in some documents from SP3 universe to SP4 universe. But for most documents it should just work (if I remember correcly, this is how it worked for the sample documents).
            regards Thomas

  60. Former Member

    Does anyone know if there is support for the bira universe and sample webi reports on 4.2 SP4?  Looks like the table names have changed from SP3 to SP4?  If anyone has any insight to this topic is appreciated. 

     

    This link on this page has expired fyi...

    Download the content (universe, Web Intelligence samples and videos)

    1. Indeed there is a change between SP3 and SP4 implementation. In SP4 the universe and samples can be found in the samples folder of the installation (..\SAP BusinessObjects Enterprise XI 4.0\Samples\bionbi).
      regards Thomas

  61. Former Member

    Hi

    I am trying to determine the recurrence details of a Publication Schedule. I have selected on Kind=Publication which isn't in the drop down of Kind types.

    I have then used all the objects in the Scheduling class but none of them seem to be the start and end time set in the Recurrence section of a publication.

    Are these properties exposed in the Universe ?

    I tried using the technical properties to see if they are held in the property bag without any luck.

    Thanks

     

    1. Hi Darren,

      can you try query builder to identify the information you need ?
      Finding the properties with query builder would help to see if they are in the universe already, must be added or a technical query should be used.
      I'm not an expert in Publications, but would assume that the information is stored in the InfoObjects and not in FRS.

      regards
      Thomas

    2. Hi Darren, to obtain recurrences details of Publications you have to search first only recurrences and then all instances.

      In Recurrences query you can extract:

      • as "Result Objects": Cuid, Name, Schedule Status and Next Runtime Timestamp
      • as "Query Filters": Kind equal to Publication and Recurrence equal to 1 (for "Recurrence" you have to create a custom dimension in the universe as I explained to Brian in the post of 6th of July above, because the native "Is_Recurring" dimension is not correct).

      Recurrence object gives you only "Next Runtime Timestamp" as useful information. Startime and Endtime that you find in the History of a scheduled publication in CMC, are information related to every instance object that a recurrence object creates.

      So you have to make another query for Instances to obtain them, extracting:

      • as "Result Objects": Cuid, Name, Schedule Status and "Instance_Starttime" and "Instance_Endtime". Both these last two dimensions have to be created as custom dimensions in the universe:  "Instance_Starttime" as "SELECT datetime(Properties.si_starttime)" - DATATYPE: datetime and "Instance_Endtime" as "SELECT datetime(Properties.si_endtime)" - DATATYPE: datetime
      • as "Query Filters": Kind equal to Publication and "Instance_object" equal to 1. "Instance_object" have to be created as custom dimension as "SELECT flag(Properties.SI_INSTANCE_OBJECT) - DATATYPE: Boolean"

      I hope to have been useful!

      Angelica

  62. Hi Thomas,

    I review your earlier post regarding the flattening of hierarchy in CMS database.  We upgraded to BI4.2 SP3.8 and the my CMS queries are failing in 2 areas.  1) FolderPath returns blank.  Thank you for your post.  I can resolve this.  2) Query users and groups.  My query the CMS database and returns the number of users in each group for each folder in the hierarchy, it is failing due to the flattening of the hierarchy.  I can expand on user to group to folder relationship in BI4.2 SP3.8?  Agiain, very informative and help.  Thank you.

    regards

    D. "yome"

  63. Hi all.

    is there accidently somebody who has an example to produce a list of all report-schedules for a particular ServerGroup ?

    Many thanks,

    Eddy

  64. Hi all, I can get the universe and path by linking using Folder Name but is there a way to get the Description of the universe?

    1. Just recognized, that there is no Universe Object, so you have to create a new universe object to get the description.

      regards Thomas

  65. Hi all,

    does anyone know if there's a possibility to change the language of the results aquired using the new ACL-query-functionality?

    Thanks in advance and best regards

    Philip

  66. Hi all, hi Thomas,

    it is really great to have this new data access driver for BI 4.2 SP3 - it is extremely useful.

    I'm just about to extend it by creating a custom linked universe to explore CMS DB, as you suggested.

    At the moment I would like to add a dimension object displaying the SI_MAIL_ADDRESSES in Scheduling Info - SI_DESTINATIONS - ... Unfortunately I don't get rid of that. Could you please let me know if that's possible, and if yes, how to do that?

    Thanks in advance
    Norbert

    1. Hi Norbert,

      the only way to get this kind of intormation is to use a technical query. In a technical query you get all properties of the InfoObjects. It is not that easy to use them in reporting, very often you have to define several variables to be able to filter on the information you're interested in. But at least it is possible to get all the informations into a report.
      Please look into the samples in the universe queries or WebI samples to see how technical queries are working.

      regards Thomas

  67. Former Member

    All,

     

    Thread is very helpful.  Can anyone point me to a resource that explains the universe in more detail?  Not sure what the different levels represent.

    1. One of the biggest challene when running queries on the CMS is to handle relationships. For example a universe is used in WebI Documents. When you run a query to get universe details, you get also a list of ids which are the WebI documents using this universe. This is called relationship. In Query Builder it is not possible to get the details of the WebI documents like name, description ..., you can get only get the ids. Looking into this sample, the universe is the root level and the WebI documents are level 1 of the relationship (can be seen as join or link between InfoObjects).
      Based on the example, you will find the information for the universe in the class 'InfoObjects'. To get the relationship to the Web Intelligence documents, you would use objects from the class 'Relationships to L1' (this would give you the list of WebI Ids). Finally you will use the objects in class 'InfoObjects L1' to get the details of the WebI documents.

      Basically the levels are providing more detailed information using the relationship Ids.

      hope this helps.
      regards Thomas

  68. Former Member

    Hello

    I have this query in Query builder

    SELECT SI_ML_NAME  FROM CI_INFOOBJECTS WHERE SI_KIND = 'Webi' and SI_CUID ='AWL29i.r4ydCgaujjTTX.7c'

    The result is array of all available translation of the report name

    SI_ML_NAME

    ET_EE

    Zones Report

     

    EN_GB

    Zones Report

     

    FI_FI

    Zones Report

     

    It_LT

    Zones Report

     

    ES_ES

    SPS_Zones Report

     

    ZH_CN

    Zones Report

     

     I would like to convert this to CMS universe and to be able to retrieve only one language at a time for example ES_ES

    How can I do it ????

    Thanks

    Dror

    1. Hello Dror,
      you can query the information using a technical query of the CMS DB Drvier univers (Class: Technical Properties). You will get the results back in the dimensions PropertyLevel, PropertyName,.... Now you have to create variables in your WebI document and work with filters to get the information you need.
      This is the only way to report on all kind of property bags.
      regards Thomas

      1. Former Member

        Hi Thomas

        The problem is that I can not get the language in order to filter Property bag 

        1. yes, the query result will contain all the languages and you have to filter in the WebI document.

          1. Former Member

            It will contain all values except the language property (e.g EN_GB) value that I need to filter by

            P.S how can I upload a picture to demonstrate what I see

  69. Has anyone experienced an issue attempting to do a Query Filter on 'Name' in which the search value contain parenthesis "("?  I wrote a simple request to get UserGroups and the Members of the group with a filter on the Group Name for a group that has parenthesis in the name.  It runs but brings back no records.

    SELECT

    Properties.si_cuid,
    int(Properties.si_id),
    Properties.si_name,
    Properties.si_kind,
    relationships(Properties.si_group_members),
    Level1.si_cuid,
    int(Level1.si_id),
    Level1.si_name,
    Level1.si_kind
    FROM
    Properties,
    Properties Level1
    WHERE
    ( Properties.join=Level1.join )
    AND
    (
    Properties.si_kind = 'UserGroup'
    AND
    Properties.si_name = 'Group Name (Sample)'
    )

    1. I have tested this also and it is not working. Can you please create an incident.

      regards Thomas

      1. It's strange.....query runs fine only if there isn't any space in front of parentheses : (tongue)

        Angelica

        1. I opened a ticket with Tech Support.  I wasn't sure how to categorize the ticket:

          586887 / 2017 CMS Database Driver Name Field Values with blank parenthesis

          1. Have changed the incident to component BI-BIP-ACP, you can find it on the top of the page: Notes (Component: BI-BIP-ACP)

            1. Thomas, could you contact the Tech Support individual working this ticket and discuss with them?  They have some questions that would be better answered by the product manager. ~ Thanks!  Mickey

  70. Hi, I tried to execute a sample query from the Universe "Sample-Members-to-Group" and encountered the following error. Please let me know if anyone figured it out, i need to find all the users in each user group. 

    Syntax error in SQL query : [line 1:265 no viable alternative at input ')'][line 1:265 missing EOF at ')']

    Error:
    Exception parsing query: SELECT
    int(Properties.si_id),
    Properties.si_name,
    Properties.si_kind,
    relationships(Properties.si_usergroups),
    int(Level1.si_id),
    Level1.si_name,
    Level1.si_kind
    FROM
    Properties,
    Properties Level1
    WHERE
    ( Properties.join=Level1.join )
    AND ( ( Properties.si_kind ) )
    AND
    Properties.si_kind IN ( 'User' )

     

    Thanks

    Arun

     

    1. seems there is something missing in the where condition or si_kind is just duplicated ? AND ( ( Properties.si_kind ) )

      1. Thanks for the response Thomas, it was the default query in the universe and i am not sure how to fix it. Please let me know if you know how to successfully execute the  "Sample-Members-to-Group" default query. Thanks Arun

        1. The names in the universe are different for SP3 and SP4..
          In my universe, the Name of the query is Sample-MemberOfUserGroups, the condition Looks like
          WHERE
            ( Properties.join=Level1.join  )
            AND 
            Properties.si_kind  IN  ( 'user','usergroup'  )

          In the sample webi document Sample-User-To-Group-Relationship-Details , the condition contains a prompt (selected the users Administrator and Guest) and looks like
          WHERE   ( Properties.join=Level1.join  )   AND    (    Properties.si_kind  =  'user'    AND    Properties.si_name  IN  ('Administrator','Guest')   )

  71. Hi all! If someone has issue with empty data for some dimension  (for example with security dimensions) from SP4 to higher, now is available offical Sap Note "2578194 - Some objects do not display data while creating a WebI document based on CMS Database" wihich is under investigation from development team.

    Angelica

    1. The disappeared of security dimensions data will be fixed in SP4 with Patch 6. I received the update today from Sap develepment team. Good work to all!

      Angelica

      1. Thank you very much Angelica.

        Here is the SAP Note ref# https://launchpad.support.sap.com/#/notes/2572277

         

        regards | Lokesh Chouhan

         

         

         

        1. Thank you for warned me!

          Angy

  72. Hi, does somebody of the SAP-Persons know if the BIAR available after installing BI 4.2 SP5, contains the latest versions of the Universe and the sample WEBI-Reports ?

     

    Thanks,

    Eddy

  73. Has anyone else encountered issues with the CMS database driver after patching BOB 42, SP3 with SP5?  After applying the SP5 patch, I am not able to run reports using the BI Platform CMS System Database universe.

  74. Michael,

         This is a know issue with BI 4.2 SP3 Patch 4 or higher.  There are KB2405568 and KB2505827 that explains how to install the driver files manually from working system.

    Ajay

    1. Ajay, Thanks for the information.  I find this odd because the install issue should be fixed in SP5.  I installed SP5 and this no longer works.  When I was running SP3, I followed the REPAIR instructions and was able to fix the issue with SP3.  I will see if i can get this working with SP5 with the same solution.

  75. Michael,

           BI 4.2 SP5 the install should be work fine now but there is a small issue running certain queries.  I discovered this issue during the SP5 beta testing and received manual fix from the team.  I was also told the final fix should be Patch 1 for SP05, which is out now.

    Ajay

    1. Hi Ajay. Thanks for this info. We have also installed BI 4.2 SP5 Patch-1. Is there a new universe delivered with the installer of BI 4.2 SP5.1 ?

       

      Eddy

      1. Since SP4 the universe and samples can be found in the samples folder of the installation (..\SAP BusinessObjects Enterprise XI 4.0\Samples\bionbi).
        The universe is not updated in SP5.1.

        Thomas

        1. Okay so I finally got around to attempting to fix this issue by rerunning the base install and attempting a change.  It says the CMS DB Driver is installed so that didn't work.  Does anyone have any other thoughts as to why this broke after applying the BOB 4.2, SP5 patch and perhaps how to fix it?

          1. I opened a ticket with SAP Tech Support to address this issue.  I will let everyone know what the resolution is once it is fixed.

  76. Thomas,

    I did some experimenting after updating the BI on BI contents in my SP5 install and discovered there is an issue running Webi documents that use the BI platform CMS system database universe when CORBA SSL is configured.  Is there a way to configure CORBA SSL for the CMS Database Driver?  The following is the error message received when CORBA SSL is configured and I attempt to run a Webi document using the BI platform CMS system database universe.  If i remove the CORBA SSL configuration the same Webi document runs fine.

    The following database error occurred: (CS) "OpenConnectivity driver error : Unable to find servers in CMS [fully qualified server name]:6400 and cluster @[fully qualified server name]:6400 with kind cms and service null. All such servers could be down or disabled by the administrator. (FWM 01014)" . For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901) (Error: INF )

    Thanks ~ Mickey

    1. RESOLVED:  The solution is to implement the steps outlined in SAP KB Article #2098067

      1. Hello Michael,

        thank you for your investigations and sharing the solution.

        regards Thomas

  77. Hi,

    has anyone ever tried to report on the email recipients of scheduled reports, and also the email recipients of schedule notifcation emails?

    I can see that there is a common objects of 'kind' that is called 'notifcationemail' or similar - do the associated CUIDs for these objects help in this case?

    thanks

    Keith

    1. Hi Keith,
      There aren't dimensions defined in cms's universe for what you are asking for, because they are sub-properties nested under "Scheduling Info-SI.DESTINATIONS" of an "instance" object. Properties so nested are not searchable in cms's universe at the moment. I hope this will be possible in future service packs.

      Angelica

      1. thanks for the reply Angelica,

        Would they be get-at-able in the old Admin Tools queries then? I will do some digging down that route

         

        thanks

        Keith

         

        1. Certainly!
          You have to query "ci_infoobjects" table with si_id (or si_cuid or si_name) of instances of you interest selecting SI_SCHEDULEINFO.SI_DESTINATIONS as properties for emails. Below an example of the query:

          select
          SI_INSTANCE,SI_ID,SI_CUID,SI_NAME,SI_KIND,
          SI_SCHEDULEINFO.SI_DESTINATIONS
          from ci_infoobjects
          where si_id=8366485

          In this case 8366485 is the id of an instance I have in my repository.

          Angelica

  78. I'm getting the error "Unknown Share:  Share does not exists or has been deleted" when I try to go to the download page.

    -Dell

    1. Never mind - I found the universe in the Samples after installing the system.

      -Dell

      1. great you could get it from the installation. Not sure why the content disappeared, should work now from other location.

        thanks Thomas

  79. I downloaded the content from the link above and installed to ny system from the lcmbiars.  Then I found the lcmbiar file in the samples folder for my 4.2 SP7 installation.  For the latest version do I need to install the version I got from my Samples folder?  Thanks!!

    Rick

    1. Yes, please use the LCMBIAR from the samples folder.

      regards Thomas

  80. Hi Thomas, hi all

    I installed latest release of Sap BI 4.2 Sp7 full but found a bug: "Publication" property kind is missed. In previous release (until Sp4 in my experience) it was possible to search an object filtered by kind as publication selecting it by "values from list". From Sp7 instead it is necessary to insert manually kind='Publication' as constant.

    1. Hi Angelica,

      this LOV is manually maintained in the Data Foundation of the universe, seems there is a difference in the list between the SPs. As workaround, you can just add a row and re-enter the value.

      regards Thomas

  81. Yes Thomas, you have right, as I tested and demostrated in the second attached image, if I insert manually 'publication' as constant value it works and I obtain all publication objects in query panel preview. I posted this comment as advice for all members of this article to help them working with this kind of objects. Thank you very much for your confirm!

    1. thank you for supporting the community !!!

      1. It's only a pleasure! You helped me to improve my knowledge and I try to help anyone wants to work on this new feature of BI4.2! Good work to all!

        Angy

  82. Hi Thomas,

    This feature is like a GEM in a treasure chest ! thanks for unlocking it for us (smile)

    I am trying to build a webi report in BI 4.2 Sp3 patch 8 to find out Crystal report Instance details like SI_PROCESSINFO.SI_PROMPTS,SI_NEXTRUNTIME,SI_NAME,SI_ID,SI_SCHEDULEINFO.SI_DESTINATIONS.SI_MAIL_ADDRESSES

    of which I am not able to figure out a way to fetch values inside SI_PROCESSINFO and SI_SCHEDULEINFO which fall under technical category.

    I can affirm that technical query property works like a charm for displaying block/bag like SI_EXTENDED_PROCESSINFO which exists in main property. However is there a way to fetch block/bag contents inside SI_PROCESSINFO and SI_SCHEDULEINFO 

     

    Example query:

    SELECT
    Property(Properties.technical),
    PropertyValueText(Properties.si_processinginfo.technical,"300")
    FROM
    Properties
    WHERE
    Properties.si_cuid = 'FuGfMF34agsANlcAAACnIWsUAFBWuymJ'

     

    The query executes but returns null 

    -Guru

    1. Hi Guru,

      there was an issue with SI_PROCESSINFO, but it should be fixed. You can find more information in the SAP Note:
      2464995 - BI Platform CMS System database Unx cannot pull SI_ProcessInfo information

      regards
      Thomas

      1. Hi Thomas,

         

        Thanks for your reply. SI_PROCESSINFO.si_objid ( Properties.si_processinfo.si_objid ) works for me but Properties.si_processinginfo.technical is not working.

        Am I using incorrect syntax? please do correct if I am using it wrong.


        Thanks,

        Guruprasad

        1. Is technical a property or must it be si_technical ?
          Properties.si_processinfo seems to be correct, but you mentioned .si_processinginfo.

          1. Hi Thomas,

             

            Thanks for the correction. I have seemingly made the changes to si_processinfo, now the query looks like below

             

            SELECT
            int(Properties.si_processinfo.si_objid),
            Property(Properties.si_processinfo.technical),
            PropertyValueText(Properties.si_processinfo.technical,"300")
            FROM
            Properties
            WHERE
            Properties.si_cuid = 'FuGfMF34agsANlcAAACnIWsUAFBWuymJ'

             

            This only brings empty blocks as a result. However if I try to pull only si_objectid it gets the id from processinfo

             

            Thanks,

            Guruprasad

            1. Can you please check in query builder, if the name of the property is 'technical' or 'si_technical'.

              1. In Query builder what I want is data from SI_PROCESSINFO.SI_PROMPTS,

                Since SI_EXTENDED_PROCESSINFO,SI_FILES can be obtained using Properties.technical, i was trying to fetch si_prompts on similar lines

  83. Good morning Thomas,

    today I found other issue with Sp7 full release, that there was not in Sp4. When I extract all schedule objects (all objects with property SI_SCHEDULING=1) I obtain 5000 max values. But in my system there are 20403 total schedules. I created a custom filter "TOP 200000" to avoid any kind of numeral limitation, but it does not work more.

    If I search with query builder, instead:

    select top 200000 si_id
    from ci_infoobjects
    where SI_INSTANCE=1

    I obtain correctly 20403 objects.

    1. Can you please check the query options in the universe (Limit size of result set to: 5000 rows).

      1. You're right...!!!! Excuse me for the stupid doubt.....unchecking query limit option to 5000 rows in the universe, the issue solved.

        Thank you very much and good work!

        Angelica

  84. Has anyone been able to modify the CMS universe to pull data related to Analysis for OLAP documents (workspaces?)  Specifically, I'm trying to pull the name of the BEx query used by the workspace (kind = MDAnalysis.)

    Using this query in query builder I'm able to get the data, so I know it's available by query.

    SELECT SI_OWNER, SI_NAME, SI_CUID, SI_DATASOURCES_URIS, SI_CREATION_TIME, SI_UPDATE_TS from ci_infoobjects WHERE descendants("si_name='Folder Hierarchy'", "SI_PARENTID = 18") AND si_kind = 'MDAnalysis' and SI_DATASOURCES_URIS is not null order by SI_UPDATE_TS DESC 

    However, I'm not sure how to get the CMS universe to fulfill this request.

    Following, the how-to instruction published here (BIRA universe: 01 - add simple objects) I was able to create a 'simple object' for the BEx query field SI_DATASOURCES_URIS.

    When adding it to a CMS universe query that pulls workspaces the SI_DATASOURCES_URIS is empty.  I think I need to build a relationship object between workspace and the SI_DATASOURCES_URIS object (similar to what is already provided for webi type documents in the universe.)  The process for doing this is not clear to me.

    Can anyone help with this?  Thank you,

    Mike

    1. Hi Mike! I'm trying to help you, but I have no workspaces with SI_DATASOURCES_URIS property to verifying I'm able to obtain this information by Webi report through a custom dimension created in CMS universe. Can you attach here or send me in email a biar with a "test" workspace with related olap connection  and dependant query? 

      Angelica

    2. Hi Mike,

      SI_DATASOURCES_URIS is a property bag and not a single property, this is why it appears empty in your case with a simple object pointing to this property.

      I don't think there is a way to list content of property bags using connectivity of CMS Universe, at least I didn't find a way ...


      Regards,

      Loic

      1. Hi all,

        thank you Loic for the hint, that this is a property bag.
        So it should be possible to get the information using a Technical Query, the query Sample-QT-Properties with Filter (Server) in the universe may help

        regards
        Thomas

  85. Hi,

    Apologies if this has been asked before. I've just downloaded and implemented this into our platform (4.2 SP6 patch 6) and I'm looking forward to exploring.

     

    One thing I've noticed is that the template universe generated contains quite a few objects from the 'Properties' table, but when I look at the universe in IDT, or use the connection in Universe Designer, I can only see 4 columns listed... Is there a way of seeing all the columns in this table?

    Basically, I'm looking to be able report on whether reports are scheduled, and if they are, what email addresses and file locations are scheduled out to?

     

    Thanks

    Paul

    1. Hi Paul,
      please use the universe and samples in the samples folder of the installation (..\SAP BusinessObjects Enterprise XI 4.0\Samples\bionbi), available since SP4.
      The tables in the universe do not contain more columns and are virtual tables for the huge list of properties only.
      For the information you're searching for, you should use a technical query, but we just found an issue in exact this area, but seems the team has already found a solution and hopefully this will be available soon within a fix (see discussion above started from Mike Decker), there is also a note 2846935

      regards
      Thomas

  86. I want to let everyone know about an issue I found, not with the CMS Driver or the universe, but with the CMS objects properties which the CMS driver and universe report from.

    We are running BOB 4.2, SP7, Patch 02.  We have a mix of old UNV universes and new UNX universes.  I have found if I have a Webi document that uses a UNV and a UNX, the SI_DSL_UNIVERSE property, which should contain the SI_IDs of the UNX universes used by the document, will be empty on the Webi document object in the destination CMS when the document is promoted.  In addition, the SI_SL_DOCUMENTS property for the UNX universe used by the Webi document does not contain the Webi document SI_ID in the destination CMS.

    Has anyone else encountered this issue?  Is this fixed in a Patch for SP7?

    1. In my experience, si_universes and si_sl_documents being empty after a promotion is more common that not.  It has been this way since at least XI3.

  87. @Thomas Jentsch - Would you be able to look into this and perhaps help get this issue raised in priority?

    I have a ticket open with SAP (603060 / 2019).  I am getting the impression SAP is not going to fix this issue.  You need to understand this problem makes it impossible to get accurate information using the CMS Data Access Driver and universe.

    Thank you!

    1. Hi Michael,

      will look into this asap. We have already fixed an issue found after WebI change source (unv to unx) and importing the old Document (was also an issue with old information in property bags).

      regards
      Thomas

      1. Thank you very much for looking into this!

  88. Hi Thomas and community,

    I got the following error when I run the "BI - High Level View" against a relatively large CMS repo.  

    The following database error occurred: (CS) "Unexpected behavior : Java heap space (failed to allocate 24 bytes) (max heap: 2048 MB) " . For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901)

    I had APS split, and bumped up the max heap size for APS.WebI, APS.WebIDSLBridge, AJS, etc., varying from 3GB to 8GB.  Also, the WebIPS memory analysis is not enabled, meaning shouldn't run out of memory there, either.  Wondering which service was not able to allocate the required memory?  Here's the more detail from the wicdztrace folder, after the HIGH trace was turned on.

    <?xml version="1.0" encoding="UTF-8"?> <METHODRETURN SERVER_VERSION="RELEASE[psbuild@vcvmlnx251] on Aug 28 2018 12:14:23" NAME="processDPCommandsEx"> <ERRORS> <ERROR COMPONENT="WIS" ERRORCODE="10901" ERRORTYPE="USER" MESSAGE="The following database error occurred: (CS) &quot;Unexpected behavior : Java heap space (failed to allocate 160 bytes) (max heap: 2048 MB)                                                                                              &quot; . For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901)" PREFIX="ERR"> <DEBUGINFO BORESULT="5" FILENAME="kc3cdbtbdacs.cpp" LINEPOSITION="4268" MODULENAME="C3_CS"/> <REQUESTINFO COMMANDID="" COMMANDNAME="" DPID="DP5" DPLONGNAME="Metrics - BI platform CMS system database" DPNAME="Metrics"/> <REASON> <CONTENT> </CONTENT> </REASON> </ERROR> </ERRORS>  <DOCTOKEN VALUE="M0K18" TYPE="memory.current" WIDID="0"/> <PROTOCOLS>  <PROTOCOL GLOBALVERSION="1.0.0"/> </PROTOCOLS>   <METADATA/> <RETURNDATA> </RETURNDATA> </METHODRETURN>

    Thanks in advance for any thoughts/ideas.

     

    1. Ok, was able to figure this out - narrowed it down to the ConnectionServer, which has the default 2GB max heap size.  After bumping it up to 8GB, the report worked without error.  

      Here's the location of the connectionserver configuration file:

      sap_bobj/enterprise_xi40/dataAccess/connectionServer/cs.cfg

      Here's the related change (option for the 64bit processor):

      <JavaVM>
      <!-- The default JVM configuration can be overriden here -->
      <!-- Use an absolute path for the JVM -->
      <!--
      <LibraryName JNIVersion="JNI_VERSION_1_4">ABSOLUTE_PATH/jvm.dll</LibraryName>
      -->
      <Options>
      <Option Processor="64">-Xmx8192m</Option>
      <Option Processor="32">-Xmx512m</Option>
      <Option>-Xrs</Option>
      </Options>
      </JavaVM>

      Hope this helps a bit in case others hit the same issue.

      My Best,

      Dafang

      1. Hi Dafang,

        thank you for your investigation and sharing the solution.

        regards
        Thomas

        1. Hi Thomas -

          Not a problem.  Thank you very much for the wiki, and especially, all efforts from you and others addressing the community's concerns/issues/questions, very helpful! 

          Whenever you have a moment, I have another question - would it possible to pull a list of all webi reports that use a specific connection that is in turn used in all .unx universes' Data Security Profile (DSP), either default or override connection?  (A bit context - This is a multi-tenant deployment, each client is represented by a user group; there're shared universes with DSP tenant to connection mapping; each tenant has their connection to point to their own database schema.)

          Thanks,

          Dafang

          1. First question would be, if you can get all this information via CMS queries or SDK is needed.
            If it is possible with CMS queries, it should be possible to add objects to the universe.
            If this are complex property bags or the SDK is needed, it would be possible to develop a plug-in for the CMS DB Driver to get additional information in a virtual table. Unfortunately the API to develop a plug-in is not documented and so far only used for some special use cases, like the Access Levels. I have a plan to provide this via a sample project, like the plug-ins for Automation Framework (boe-custom-task-template, blog).

            Could be a good idea to create an improvement request on idea place / Customer Influence (Customer Influence).

            regards
            Thomas