BusinessObjects Board

universe connections

sdk development
#1

I need to retrieve all the universe connections present in the CMS database. Any pointer how i can write the query for the same.

Regards,
Abhay

abhay.singh (BOB member since 2006-04-20)

Querying the CMS
#2

I have come to the conclusion that it is a waste of time querying the cms tables. Even if you found out what type_id’s are for which types, there are no other descriptions to tell you name of the object.

From waht I can see using XI, the only place to query is the auditor database. This simple query here will get you started.

select Object_CUID, Detail_Type_Description, Event_Type_Description,Start_Timestamp,Detail_Text
from
BOXI_Audit.AUDIT_DETAIL,BOXI_Audit.AUDIT_EVENT,BOXI_Audit.DETAIL_TYPE,BOXI_Audit.EVENT_TYPE
where
BOXI_Audit.AUDIT_DETAIL.Event_ID = BOXI_Audit.AUDIT_EVENT.Event_ID
and
BOXI_Audit.DETAIL_TYPE.Detail_Type_ID = BOXI_Audit.AUDIT_DETAIL.Detail_Type_ID
and
BOXI_Audit.EVENT_TYPE.Event_Type_ID = BOXI_Audit.AUDIT_EVENT.Event_Type_ID

jonathanstokes (BOB member since 2004-09-17)

#3

Hi,

Thanks a lot for your reply.

Actually I am able to access the universes,Folders and other type of objects from CMS Database using the following queries.

Dim query As String = "Select SI_NAME, SI_ID FROM CI_APPOBJECTS WHERE SI_KIND=‘Universe’ "

Dim query As String = "Select * From CI_SYSTEMOBJECTS " _
& "Where SI_KIND=‘UserGroup’ "

Dim query As String = "Select SI_NAME, SI_ID From CI_INFOOBJECTS " & “Where SI_KIND=‘Folder’”

Similar to these objects I need to access the universe connections. But I am not able to retrieve the universe connections. I need to get access to universe connections then I have to modify their security rights.Basically I need to add some user groups to the universe connections and assign some rights like i have done for Folders and Universes.

Public Shared Sub AddRightsFolder(ByVal myInfoStore As InfoStore, ByVal FolderID As String, ByVal GroupID As String, _
ByVal Role As String)

    Dim query As String = "Select SI_NAME, SI_ID From CI_INFOOBJECTS " _
                        & "Where SI_ID=" & FolderID

    Dim myInfoObjects As InfoObjects = myInfoStore.Query(query)
    Dim myInfoObject As InfoObject = myInfoObjects(1)
    Dim myReport As Folder = CType(myInfoObject, Folder)

    Dim mySecurityInfo As SecurityInfo = myReport.SecurityInfo
    Dim myObjectPrincipals As ObjectPrincipals = mySecurityInfo.ObjectPrincipals

    If Role = "No Access" Then
        mySecurityInfo.ObjectPrincipals.Add(CInt(GroupID)).Role = CeRole.ceRoleNoAccess
    ElseIf Role = "View" Then
        mySecurityInfo.ObjectPrincipals.Add(CInt(GroupID)).Role = CeRole.ceRoleView
    ElseIf Role = "Schedule" Then
        mySecurityInfo.ObjectPrincipals.Add(CInt(GroupID)).Role = CeRole.ceRoleSchedule
    ElseIf Role = "Full Control" Then
        mySecurityInfo.ObjectPrincipals.Add(CInt(GroupID)).Role = CeRole.ceRoleFullControl
    ElseIf Role = "View on Demand" Then
        mySecurityInfo.ObjectPrincipals.Add(CInt(GroupID)).Role = CeRole.ceRoleViewOnDemand
    ElseIf Role = "Advanced" Then
        mySecurityInfo.ObjectPrincipals.Add(CInt(GroupID)).Role = CeRole.ceRoleAdvanced
    End If

    myInfoStore.Commit(myInfoObjects)
End Sub

Please help me out. I am in big big trouble.

Regards,
Abhay

abhay.singh (BOB member since 2006-04-20)

#4

I don’t have any of the tables that you stated.

I am using XIR2.

Only tables I have are5 all with a prefix of CMS_

jonathanstokes (BOB member since 2004-09-17)

#5

Jonathan,

You won’t see those tables named “CI_xxxxx” in the repository. These tables are some XI specific things which you can query against, but they are not physically under the same name in the repo. So don;t worry if you don;t see them in your CMS DB. Try using the Query Builder tool to see if these queries work out for you!

Cheers!
-SD

sdeshpan :us: (BOB member since 2005-06-28)

#6

You’ll get to search in the System Objects, try this :

select * from CI_SYSTEMOBJECTS where SI_KIND='Connection'

jp.golay :switzerland: (BOB member since 2002-06-17)

#7

My understanding of the query builder is that its an sdk itself that can access the object model for particular information about objects. The information is not is a traditional database format at all.

maverick976 :us: (BOB member since 2004-07-06)

#8

More or less it permit to query the basic infoobjects, regrettably it’s not possible to explore further in the plugins properties.

jp.golay :switzerland: (BOB member since 2002-06-17)

#9

This looks like an old thread, but in case anyone is still interested…

[quote:bd4d6bbfae=“jp.golay”]You’ll get to search in the System Objects, try this :

select * from CI_SYSTEMOBJECTS where SI_KIND='Connection'

[/quote]

That query actually lists the number of active connections, i.e. the users that are currently logged into the CMS. To get a list of the universe connections, run this in Query Builder:


select si_id, si_name from ci_appobjects where si_kind = 'metadata.dataconnection'

BoB LoblaW :us: (BOB member since 2007-10-23)

#10

Excellent tip. Has anyone found a decent reference for the query builder pseudo-sql command language? Specifically, metadata for all of the objects, possible values for ids and codes, etc.

Example questions

Which universes use the connection named ‘mybigdatabase’?
What reports have no category?
What connections are refrerenced by no universe?

Bill Jones (BOB member since 2002-10-11)

#11

Bill,

I haven’t found one all-inclusive source for Query Builder, but I find most of my answers from the developer library at the BzO website. I check this site on almost every project I work on and I find it to be an invaluable resource although it can be tricky to find exactly what you want.

Pro Crystal Enterprise/Business Objects XI Programming by Carl Ganz Jr. is my second recommendation. (Moderators, please correct me if I’m violating the no advertising policy) The book does not specifically focus on Query Builder and it’s geared towards .NET (I use Java), but it is littered with good tips if you have the patience to leaf through it. This is where I discovered the ‘hidden gem’ that is SI_ANCESTOR and that alone has saved me from writing many lines of unnecessary code. :lol:

BoB LoblaW :us: (BOB member since 2007-10-23)

#12

I’d like to be able to answer this question. (And also, generically, “Which universes are using what connectons?”) Is there a CMS query or Auditor query that would tell us this?

Edit: This download may help…

dnewton :us: (BOB member since 2004-01-30)

#13

I think I field these questions:

select * from ci_appobjects where si_kind = 'metadata.dataconnection' and SI_CONNUNIVERSE.si_total = 0
select * from ci_appobjects where children("si_name = 'dataconnection-universe'", "si_name = 'mybigdatabase'")
select * from ci_appobjects where si_kind = 'metadata.dataconnection' and SI_PERSONAL_CATEGORIES.si_total = 0 and SI_CORPORATE_CATEGORIES.si_total = 0

The above queries work in XI 3.x; I’m not sure about R2.

BoB LoblaW :us: (BOB member since 2007-10-23)