Skip to Content

Edit: So I finally got around to fixing up the format for this blog for the current SCN web site – it seems a few people find the content pretty useful 🙂

I work for SAP Business Objects, in Technical Customer Assurance.  My speciality is the Software Development Kits (SDKs) that we provide with our Business Intelligence products – BusinessObjects Enterprise, Web Intelligence, Desktop Intelligence, Crystal Reports and Crystal Xcelsius.  </p><p>In my blog, I discuss subjects that I personally find interesting – little known or not-well-documented corners of the SDK, new functionality or new SDKs, or interesting issues that I’ve come across in a SAP Incident or SAP Developer Network forums.

You’re more than welcome to suggest any topic (SAP Business Objects SDK related, of course…) that you’d like me to discuss – I have a dozen or so items on my blog to-do list, but I’m always on the hunt for anything interesting with our SDKs.

Are you managing content on a BusinessObjects Enterprise deployment?


Are you a developer creating an application using the BusinessObjects Enterprise SDK?


If you’ve answered yes to either question, then this blog entry may be of interest to you. Specifically, it’s of interest if you’ve ever had the requirement to query the BusinessObjects Enterprise Central Management Server (CMS) for information concerning repository objects. 


Here’s a few of the tasks you’d be able to complete after reading this article:

  • Retrieve all Users belonging to two or more specified User Groups.
  • Retrieve a document in a specified Folder path using just one query.


You’ve likely noticed something common among all three tasks – they all involve queries across relationships between objects managed by the Enterprise CMS: between a document and a Universe, a User and a UserGroup, or a document and a Folder.

What I’m going to cover here is a little-known corner of the BusinessObjects Enterprise administrative and SDK toolkit – Relationship Queries.  These aren’t part of the public API and won’t be documented, but do come in handy at times.

BusinessObjects Enterprise Query Language

If you’ve developed applications that integrate with BusinessObjects Enterprise, Crystal Reports Server or BusinessObjects Edge, or if you’ve worked with the Query Builder you’re familiar with the Enterprise Query Language.

The Query Language is a SQL-like language used to query for repository objects called InfoObjects.  An InfoObject may be a document, a document Instance, User, UserGroup, Publication,  Server, etc. – any object managed by the Enterprise Central Management Server (CMS).


Here’s a simple example – log onto the Query Builder as administrator, and enter the following query:


Select SI_ID, SI_NAME, SI_USERGROUPS From CI_SYSTEMOBJECTS Where SI_KIND=’User’ And SI_NAME=’Administrator’

that queries the CI_SYSTEMOBJECTS table for the SI_ID, SI_NAME and SI_USERGROUPS properties of the ‘Administrator’ User.  You’ll see that the SI_USERGROUPS property is a list of numbers – it should be ‘1’, and ‘2’.  This property specifies the SI_ID values for all UserGroups to which the User is a member. 


To find which UserGroups these SI_ID numbers represent, you can run the query:


Select SI_ID, SI_NAME From CI_SYSTEMOBJECTS Where SI_KIND=’UserGroup’ And SI_ID In (1, 2)

and you’ll see that they’re the ‘Everyone’ and ‘Administrators’ UserGroups.


It’s a bit inconvenient to run two queries, one to to find the SI_IDs of all UserGroups that a User belongs to, and another to translate the SI_IDs to UserGroup names. You can, in fact, accomplish the task using just one query:

Select SI_ID, SI_NAME From CI_SYSTEMOBJECTS Where PARENTS(“SI_NAME=’UserGroup-User'”, “SI_NAME=’Administrator'”)

This query looks up the User with name ‘Administrator’, and finds the UserGroup associated with the User using the PARENTS relationship function, without the need to have an intermediate query for the SI_USERGROUPS property.

The SI_USERGROUPS property is an example of a Relationship between InfoObject types, here between UserGroup and User types, and the PARENTS query function is an example of a Relationship Query, here based on the ‘UserGroup-User’ relationship.


Query for Universes using ‘efashion-webi’ and/or ‘club-webi’ DataConnections and owned by ‘Administrator’
Select
    SI_ID, SI_NAME, SI_OWNER
From      
    CI_APPOBJECTS
Where
    CHILDREN(“SI_NAME=’DataConnection-Universe’ “, “SI_NAME=’efashion-webi’ OR SI_NAME=’club-webi'”)    
    And SI_OWNER=”Administrator”

I’ll note a quirk with the Enterprise Query Language that you can see in the above example.  All string literals must be delimited by matching double-quote (“) or single-quote (‘) characters, where double-quote characters may only be used top-level, so any nested string literals must be delimited by single-quotes.   You’ll see in later examples that string literals nested in single-quote delimiters must be escaped by an additiona single-quote character.  So you’d use double-quote delimiters at top-level, then single-quote delimiters at the next level, then double single-quote (”) delimiters after that (and not single double-quotes), then quadruple single-quotes (””) at the next level, etc.  It does get a bit difficult to read for more complex queries…

Another quirk – the Query Builder tool doesn’t like return characters, the above example was formatted for readability, but make sure you edit the statement to be on a single line if you want to run it in Query Builder.



Multiple Relationships

You can combine two or more relationships to create quite complex queries, and this is where the power of using relationship queries pays off.  Such complex queries go beyond what’s available in the Central Management Console (CMS).  Furthermore, it simplifies application development when using the Enterprise SDK – instead of running multiple queries and doing set intersection/union computations, you can offload much of the work to the CMS by using a more refined query.


Here’s an example.  Say you’re tasked with finding all Users belonging to the UserGroup ‘UserGroup1’ who also have Query as a Web Service (QaaWS) creation/modification rights (i.e., member of the ‘QaaWS Group Designer’ UserGroup in XI 3).  If you have thousands of users, it’ll take quite an effor to compare the list of Users in the two UserGroups.


Using relationship queries, you’d merely execute the following:


Query for Users in ‘UserGroup1’ and ‘QaaWS Group Designer’
Select   
    SI_ID, SI_NAME
From      
    CI_SYSTEMOBJECTS
Where     
    CHILDREN(“SI_NAME=’UserGroup-User’ “, “SI_NAME=’UserGroup1′ “)   
    And CHILDREN(“SI_NAME=’UserGroup-User’ “, “SI_NAME=’QaaWS Group Designer’ “)’

and there you have it.  The set intersection of Users belonging to ‘UserGroup1’ and belonging to ‘QaaWS Group Designer’ is done within the CMS.


Nested Relationship Queries

Even greater power comes from nesting multiple relationship functions.  This allows you to bridge a relationship between two InfoObject types that do not have a direct relationship defined.


Here’s an example.  You’re tasked with finding all other UserGroups that Users in the ‘Administrators’ UserGroup belongs. You can use a relationship query to query for all Users in the ‘Adminstrators’ UserGroup, then use another relationship query to query for all UserGroups those Users belong. Or, you can combine the two queries using nested relationship functions:


Query for UserGroups of Users in ‘Administrators’ UserGroup
Select   
    SI_ID, SI_NAME
From   
    CI_SYSTEMOBJECTS
WHERE   
    PARENTS(“SI_NAME=’UserGroup-User'”,  “CHILDREN(‘SI_NAME=”UserGroup-User” ‘, ‘SI_NAME=”Administrators” ‘)”)

(note that use of double single-quotes to delimit the name string literals). 


Building up a Nested Relationship Query using Query Builder

The Query Builder tool comes in handy when you have to build up complex queries – you can iteratively refine and add to a query, until you obtain the desired result.


I’ll walk through an example here.  Say I’m tasked with finding all Folders that contain Web Intelligence or Desktop Intelligence documents that report off a Universe  using the ‘efashion-webi’ DataConnection.


I log onto Query Builder, and construct a query that returns me all Universes connected to the ‘efashion-webi’ DataConnection:


Universes using ‘efashion-webi’ Data Connection
Select   
    SI_NAME, SI_KIND
From   
    CI_APPOBJECTS
Where   
    CHILDREN(“SI_NAME=’DataConnection-Universe'”,”SI_NAME=’efashion-webi'”)

Query Builder shows me that the query is returning the correct results.  Now I nest this query into one tha returns me all Web Intelligence and Desktop Intelligence documents the reports off the Universes:


Documents using ‘efashion-webi’ Data Connection
Select   
    SI_NAME, SI_KIND
From   
    CI_APPOBJECTS, CI_INFOOBJECTS
Where   
    PARENTS(“SI_NAME=’Webi-Universe'”,
            “CHILDREN(‘SI_NAME=”DataConnection-Universe” ‘,’SI_NAME=”efashion-webi” ‘)”)

Note that I alter the string delimiters when nesting, changing double-quotes to single-quotes, single-quotes to double single-quotes, etc.  Note also that, since I’m querying for documents and Universes, I add the CI_INFOOBJECTS to the search tables.


Finally, I write the relationship that gives me the parent folder for the documents (this won’t work in XI Release 2, since the ‘Folder Hierarchy’ relationship type was introduced with XI 3.0):

Folders containing documents using ‘efashion-webi’ Data Connection

Select   
    SI_NAME, SI_KIND
From   
    CI_APPOBJECTS, CI_INFOOBJECTS
Where   
    PARENTS(“SI_NAME=’Folder Hierarchy'”,           
            “PARENTS(‘SI_NAME=”Webi-Universe” ‘,                     
                     ‘CHILDREN(”SI_NAME=””DataConnection-Universe”” ”,
                               ”SI_NAME=””efashion-webi”” ”)’)”)


and there you have it!


Summary


I hope you found this article on Relationships Queries interesting, and that their use will simply administrative tasks and lessen programmatic effort.


To report this post you need to login first.

40 Comments

You must be Logged on to comment or reply to a post.

    1. Ted Ueda Post author
      Hello Fred,

      Thanks for the feedback!

      If you encounter any interesting or useful queries, please do post here to help others.

      Sincerely,

      Ted Ueda

      (0) 
        1. Christian Reed
          I can tell you that I am aware of the following relationship functions:

          Ancestors
          Descendants
          Connected Components
          Parents
          Children
          Members
          Groups

          However, it seems to me that Ancestors, Parents, and Groups all do the same thing.  Well Groups may not do what “Parents” does, but it seems to me that “Parents” and “Ancestors” are interchangeable and do what “Groups” does (are are there corresponding colleague functions).

          So I would like to see an explanation for a need for more than just the following relationship functions:

          Connected Components
          Parents
          Children

          What can’t be done with one of these three?

          (0) 
          1. Graham Sturmy

            descendents and ancestors are useful when the output of a relationship can by put into the input.  The Folder Hierarchy relationship is an example of this:

            select SI_NAME from CI_INFOOBJECTS,CI_APPOBJECTS,CI_SYSTEMOBJECTS where ancestors(“SI_NAME=’Folder Hierarchy'”, “SI_NAME=’Chart Demo'”)

            In this case I give the name of a webi report and it gives me all the parents, but, because the parent is a folder and this could ALSO be put as an input to the relationship we also get the parent of that folder – ancestors is giving us the complete folder tree that the Chart Demo report is in (its direct parent, its parents parent and so on.

            If you give this relationship a folder and use the descendents function then it will give all the objects that existing, not only directory in the folder, as children does, but also the objects in all the sub folders too.

            (0) 
          2. Ted Ueda Post author

            My grandfather is an ancestor but not my parent.

            My grandchild is a descendant but not my child.

            I’m a member of my work group but they’re not family.

            I’m connected to some colleagues by a dashed line but they’re not part of my work group.

            An ever-widening circle of my relationships 🙂

            Regards,

            Ted Ueda

            (0) 
  1. Ryan Gordon
    Ted,

    This is an excellent article.  One of my biggest gripes with the SDK/Query Builder is performing my own set algebra.  I came across the “PARENTS” function sometime ago, but I couldn’t find any documentation about the parameters.  Do you plan to cover any of the other relationship functions in the future?  Thanks for the info.

    (0) 
    1. Ted Ueda Post author
      Hello Ryan,

      Thanks for your comments!

      I intend to cover more of the relationship functions in a future blog – it’s just a matter of finding the bandwidth…

      Sincerely,

      Ted Ueda

      (0) 
  2. Mathieu Ouellet
    SELECT SI_NAME, SI_ID FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS WHERE CHILDREN(“SI_NAME=’Universe-UserGroup'”,”SI_ID=170876″)

    Correct me if I’m wrong, but this query will return all ‘UserGroup’ objects related to universe where it’s id is 170876.

    So I expect to receive a list of groups that have access rights set on this particular universe. If it’s the case, than why the list returned by this query doesn’t match at all with what I see in Business Objects CMC when I click on the “rights” tab for this universe?

    Thank you

    (0) 
      1. Ted Ueda Post author
        I think that relationship denotes the SI_USERGROUPS_ORDER value for a Universe – i.e., given a User belonging to multiple UserGroups, each UserGroup defining a different Access Restriction (Overload InfoObject) on the Universe, which one should take precedence.

        Access rights are a different matter – that indicates what UserGroup has rights associated with the Universe itself.

        The relationship function for accessing objects with rights is the IsAllowed(…) function, that I haven’t had time to blog yet 🙁

        Sincerely,

        Ted Ueda

        (0) 
  3. Raju Bendalam
    Hi Ted Ueda,

    Thanks for the posting. It helped us in executing one of the challenging and crucial thing in a project.

    btw, Is there any query we can run to get CMS Server port number. I guess CI_SYSTEMOBJECTS gives server details and SI_PATH holds the server port number for various servers like CMS, WebI Reporting server etc. But how can we get port number for CMS Server?

    Using SDK, if we want to change the port number, what would be the best way?

    Thanks,

    Raju

    (0) 
  4. Geert Van Der Streeck
    Hello,
    we are in a situation where we have +1000 webi-reports for which we find no longer the universe via the Query Builder.  We also got the message ‘Universe not found’ when deleting a universe…  Nobody wants to migrate any universe anymore because of this.  Everything is blocked.  I followed your advice and create a Note at the Support-site (0000191467 2009).  But unfortunatly, the ‘we know how’ to fix the problem is not known at SAP-Support :-(.  What can I do to have a fix for this issue?
    (0) 
    1. Ted Ueda Post author
      Hello Geert,

      An outline of resolution for the issue is described in SAP Note 1204258 “Error: “Universe Not Found” when opening reports where the universe has been deleted or been moved”.

      In the situation you’re in, you have to be EXTREMELY CAREFUL not to delete any Universes until it’s resolved.

      Support engineers with SAP Business Objects should be familiar with assisting you with 1204258.  I recommend you referencing that note in your Incident.

      Sincerely,

      Ted Ueda

      (0) 
  5. Andrea Cozzani
    It’s necessary read the file of var.properties and memorize the content of the variable var_bo.defaultFolder that in the following step I will name PARAM1.

    After authentication and log on to BO, we can execute the following query:
    sql.append(“SELECT SI_CUID FROM CI_INFOOBJECTS “);

            sql.append(“WHERE “);

            sql.append(“SI_KIND = ‘” + CeKind.Folder + “‘ AND “);

            sql.append(“SI_KEYWORD = ‘” + PARAM1 + “‘ AND “);

            sql.append(“SI_INSTANCE = 0 AND “);

            sql.append(“SI_NAME = ‘” + PARAM1 + “‘ “);            

    The returned value is the id of the folder that contains the report to open: in the following step I will name PARAM2.

    At this time, to retrieve the BO report, I must execute the following query:
    sql.append(“SELECT * FROM CI_INFOOBJECTS “);

            sql.append(“WHERE “);

            sql.append(“SI_KIND = ‘” + CeKind.FullClient + “‘ AND “);

            sql.append(“SI_PARENT_CUID = ‘” + PARAM2 + “‘ AND “);

            sql.append(“SI_INSTANCE = 0 AND “);

            sql.append(“SI_NAME = ‘” + Reportname + “‘ “);                 

    (0) 
    1. Community User
      Where to write code and how BO will connect with the code.

      I am developing WEBi and scheduled thru Infoview. Mail will be send to the client in Xls/Pdf format. When user will open the file it will ask for the password. How to implement this.
      Is it possible from BO directly. If yes then how and if No then is it possible thru JSP. Then Please guide me the steps.

      (0) 
  6. Jack Surma
    Ted,

    I read this post with great interest and got very excited that this type of information could be retreived so easily.  I am using 3.1 SP1 and I started experimenting but ran into some problems.  I tried this query to attempt to pull folders accessible to the Adminitrators group and received zero rows:

    SELECT SI_NAME FROM CI_SYSTEMOBJECTS WHERE CHILDREN(“SI_NAME=’UserGroup-Folder'”,”SI_NAME=’Administrators'”)

    Is there something I am doing wrong?  Another query I am intrested in is ‘CrystalReport-CrystalReport(Loc)’.  What does it return?  I could not figure how to get this one to work either.

    I searched the Web and could only find your post on Relationship Queries.  Is there other documentation available?

    Thanks!
    Jack Surma

    (0) 
  7. Norm Rosen
    Thanks for this blog; it’s been very helpful.

    In replying to an earlier comment you stated *The relationship function for accessing objects with rights is the IsAllowed(…) function*.  So if I want to run a query using the UserGroup-Folder relationship I need to use IsAllowed().  Correct?

    If I can use UserGroup-Folder with Parent or Children, what is it telling me?

    (0) 
  8. Lyubisha Mirkov
    Hi Ted

    I find this post very useful. I am java developer and pretty new to BO. I have the following task:

    Have to find all reports that use the universe’s objects from the particular table . The reason for that is that the table is going to be delete and the new one with different fields will be created, so all reports that use the fields from the table will be broken. My approach was to find all he universes for particular data source (data connection) and then to find all reports for identified universes. I was able to do all this but am struggling to filter out all reports that use the objects defined from the  table.

    Any help would be highly appreciate.

    Than you

    Lyubisha

    (0) 
    1. Ed McSweeney
      I don’t think you need to use a relationship query to get that. The following should work:

      SELECT SI_NAME FROM CI_SYSTEMOBJECTS, CI_APPOBJECTS WHERE SI_KIND=’Universe’ AND IsAllowed([Group or User SI_ID], 3)

      where [Group or User SI_ID] denotes the SI_ID of the group or user obtained with the following query:

      SELECT SI_ID FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’UserGroup’ AND SI_NAME='[Group Name]’

      or

      SELECT SI_ID FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’User’ AND SI_NAME='[User Name]’

      The value for [Right ID] can be any of the following values:

      Right,Decimal Value
      ceRightAdd,1
      ceRightCopy,61
      ceRightDelete,22
      ceRightDeleteInstance,38
      ceRightEdit,6
      ceRightEditPassword,39
      ceRightModifyRights,8
      ceRightOwnerAdd,536870913
      ceRightOwnerCopy,536870973
      ceRightOwnerDelete,536870934
      ceRightOwnerDeleteInstance,536870950
      ceRightOwnerEdit,536870918
      ceRightOwnerEditPassword,536870951
      ceRightOwnerModifyRights,536870920
      ceRightOwnerPauseResumeSchedule,536870978
      ceRightOwnerReschedule,536870987
      ceRightOwnerScheduleOnBehalfOf,536870988
      ceRightOwnerSecuredModifyRights,536870985
      ceRightOwnerSetDestination,536870974
      ceRightOwnerView,536870915
      ceRightOwnerViewInstance,536870977
      ceRightPauseResumeSchedule,66
      ceRightPickMachines,23
      ceRightReplicate,81
      ceRightReschedule,75
      ceRightSchedule,21
      ceRightScheduleOnBehalfOf,76
      ceRightSecuredModifyRights,73
      ceRightSetDestination,62
      ceRightView,3
      ceRightViewInstance,65

      If you wanted to get the container folders of universes that match your security query I think the following relationship query will give you that:

      SELECT SI_NAME FROM CI_SYSTEMOBJECTS, CI_APPOBJECTS, CI_INFOOBJECTS WHERE PARENTS(“SI_NAME=’Folder Hierarchy'”, “SI_KIND=’Universe'”) AND IsAllowed([Group ID],[Right ID])

      (0) 
  9. friend friend
    Hi Ted Ueda 
    Your blog helped me a lot in generating required details from CMS tables using Querybuilder.

    I am trying to retrive only certain columns from the table but these columns are part of third level of detail Ex: Select SI_PROCESSINFO.SI_LOGON_INFO.SI_LOGON1.SI_SERVER from CI_INFOOBJECTS

    table but could not generate any data. Can you please let me know if there is anyway to get this detailed information through querybuilder tool. Thank you!!

    (0) 
  10. Carlos Martín Domínguez
    Hi Ted,

    Congratulations for this blog! It’s very helpful.

    I try to get the reports which are in “Public Folders”. I don’t want get the reports which are in “Personal Folders”. Is it possible?

    I try to get the reports which patern is a Folder but I don´t know if this query get the reports that are in a Folder created in a Personal Folder. Do you understand me?

    Thanks in advance!

    (0) 
  11. Ravi Yarlagadda

    Hi Ted,

    I have a situation in which i know the Universe name and i have to find all Users who can access that Universe.

    Can you please provide us the solution for this. If there is any Query for Query Builder than it will be great?

    I am using 3.1.

    Thanks

    (0) 
  12. Allen Alloway

    Hi Ted,

    The blog is very helpful.

    I’m having a problem though with using the PARENT-CHILD / CHILD-PARENT that you identify. For instance, the following is used…

    To retrieve all Web Intelligence reports connected to a Universe

    SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
    WHERE PARENTS(“SI_NAME=’WEBI-UNIVERSE'”,”SI_NAME =’
    EFASHION‘”)


    However, our universes and connections are not the same as eFashion universe and its eFashion-Webi connection.


    Our universes are ‘DSL.MetaDataFile’ objects and our connections are ‘DataFederator.DataSource’ objects. When I replace ‘EFASHION’ with our universe ‘Day 10’ in the above query, it fails. I assume it because of the SI_NAME = ‘WEBI-UNIVERSE’ because our universe object is not ‘UNIVERSE’ but ‘DSL.MetaDataFile’


    Please help resolve this.


    Thanks,


    Allen

    (0) 
    1. Ted Ueda Post author

      Hello Allen,

      There’s a type distinction between the old UNV Universe and new UNX Universe, as you saw.

      The correct relationship would be ‘Document-DSL.Universe’

      Regards,

      Ted Ueda

      (0) 
      1. Allen Alloway

        Thanks, Ted.

        I’ve had to include the .unx extension (not required with the .unv universe)

        So here’s what works:

        SELECT SI_ID, SI_NAME, SI_AUTHOR, SI_OWNER, SI_SIZE, SI_CREATION_TIME, SI_PARENT_FOLDER, SI_UNIVERSE, SI_INSTANCE

        FROM CI_INFOOBJECTS, CI_APPOBJECTS

        WHERE PARENTS(“SI_NAME=’DOCUMENT-DSL.UNIVERSE'”,”SI_NAME = ‘DAY 10.UNX'”)

        ORDER BY SI_PARENT_FOLDER, SI_NAME

        (0) 
  13. Allen Alloway

    There seems to be some bug or anomaly or misunderstanding on my part in regard to the SI_KIND values in CI_SYSTEMOBJECTS and CI_INFOOBJECTS

    For instance, using this query for CI_SYSTEMOBJECTS:

    SELECT SI_KIND FROM CI_SYSTEMOBJECTS

    I determine that there are 97 unique values for SI_KIND

    However, using the query:

    SELECT SI_ID, SI_NAME, SI_OBTYPE, SI_KIND FROM CI_SYSTEMOBJECTS ORDER BY SI_KIND, SI_NAME

    I find there are only 9 unique values

    All the alphabetical values after ‘AlertNotification’ are missing

    Similarly, for CI_INFOOBJECTS

    SELECT SI_KIND FROM CI_INFOOBJECTS 

    yields 15 unique values

    But

    SELECT SI_ID, SI_NAME, SI_KIND FROM CI_INFOOBJECTS ORDER BY SI_KIND, SI_NAME

    yields only 11, missing the alphabetically values after ‘Pdf’

    Any idea what’s happening here?

    (0) 
    1. Ted Ueda Post author

      What differences in the result do you see if you run instead:

      SELECT TOP 10000 SI_ID, SI_NAME, SI_OBTYPE, SI_KIND FROM CI_SYSTEMOBJECTS ORDER BY SI_KIND, SI_NAME

      Regards,

      Ted Ueda

      (0) 
      1. Allen Alloway

        I see over 6k records now.

        So Query Builder is truncated the results over 1000 records (shortcoming)

        If you think the records will exceed 1000, you’ll have to include this TOP function, or just include it as a general rule.

        (0) 
  14. Allen Alloway

    Query Builder seems to have a problem bringing a record back that I know exists:

    This query brings back the indicated record:

    SELECT TOP 10000 SI_ID, SI_NAME, SI_OBTYPE, SI_KIND FROM CI_SYSTEMOBJECTS

    where si_id = 4955

    ORDER BY SI_KIND, SI_NAME

    Record:

    Properties
    SI_NAME CrystalEnterprise.WebIntelligence
    SI_ID 4955
    SI_OBTYPE 377
    SI_KIND WebIntelligence

    This query which attempts to bring back that single record, does not work:

    SELECT TOP 10000 SI_ID, SI_NAME, SI_OBTYPE, SI_KIND FROM CI_SYSTEMOBJECTS

    where si_id = 4955 AND SI_KIND = ‘WebIntelligence’

    ORDER BY SI_KIND, SI_NAME

    Why doesn’t QueryBuilder bring back this record?

    It seems that QB drops records, which could present problems.

    (0) 
    1. Ted Ueda Post author

      That’s a quirk with the InfoStore Enterprise Query language.

      That object is a plugin – it defines the metadata for the Web Intelligence type.  There’s an internal reason why the SI_KIND (or SI_PLUGINID) search needs to know that the filter is specifying a type for a plugin rather than for a different type of object.

      So something like this would work:

      Select SI_ID, SI_NAME, SI_KIND, SI_PROGID From CI_SYSTEMOBJECTS WHERE  SI_KIND=’WebIntelligence’ and SI_PLUGIN_OBJECT=1

      There’s a few quirks like that in the language.

      Regards,

      Ted Ueda

      (0) 
        1. Ted Ueda Post author

          To be honest, I don’t really remember them till I stuble onto them again (like your SI_PUGIN_OBJECT example)  – I should have written them down…

          Here’s one:  if you try searching by prepend pattern matching a name, SI_NAME LIKE ‘%abcd’, it’s very slow, while postpend matching a name, SI_NAME LIKE ‘abcd%’ is pretty fast.

          That’s because the CMS db stores the first N characters of a SI_NAME in an indexed column, and the rest in a BLOB.  So trying to match ‘%abcd’ would have to bring all records matching regardless of SI_NAME, then filter each according to what’s in the BLOB.  But filtering ‘abcd%’ will only bring back records from the db where the name starts ‘abcd’.

          Regards,

          Ted Ueda

          (0) 

Leave a Reply