BusinessObjects Board

Query builder

general discussion
#1

Hi all ,

is there any way that we can get the list of universes along with their connection info in query builder ?

we are using xir2

Thanks
pavan.

dpavank2007 (BOB member since 2007-05-24)

#2

Hi,

This utility from the BOB’s Download section can help:

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#3

Hi Marek

I followed the link but this gives only the connection info ,wat i need is th universe and connection info there is discussion about what i need but there no solution for that

can you help me out the solution for(universe and its connection info)

Thanks
pavan.

dpavank2007 (BOB member since 2007-05-24)

#4

Hello,

You can pull this information from QB with something like the following:

select si_id, si_name, … from ci_appobjects
where si_kind = ‘universe’

look at the si_dataconnection property. This contains the si_id of the universe connection.

  1. now query for the appropriate connection:

select si_id, si_name, … from ci_appobjects
where si_id = {ID pulled from first query}

Note there is no way to nest these queries within Query Builder. Retrieving all of this information through QB is a manual process. There are 3rd party tools available that provide this kind of data as well. Hope this helps.

crystal01 :us: (BOB member since 2006-08-30)

#5

Hi Crystal,

Thanks for the Post. I am surely very late, but still ask my question.

For the second part, here is what I am doing to retrieve the name of a connection based on its ID retrieved from the Point 1:

String cQuery = "SELECT SI_NAME FROM CI_APPOBJECTS WHERE SI_ID = "+ Properties.getProperty(CePropertyID.SI_DATACONNECTION).getValue()+ " AND SI_KIND='MetaData.DataConnection'";

The problem is that because 1 universe can have more than 1 connection, the format of SI_DATACONNECTION is as follow:

((1 903) (SI_TOTAL 1))

My understanding is that:

‘1’ is for 1st connection
903 is the SI_ID of the connection (which is what I am after)
SI_TOTAL is the total number of connection used by this universe (1 here).

How can I extract the SI_ID? Here is what java tries to execute:

SELECT SI_NAME FROM CI_APPOBJECTS WHERE SI_ID = ((1 903) (SI_TOTAL 1)) AND SI_KIND …

which is not a valid query.

Thanks,
Marty

marty_marius (BOB member since 2011-05-18)

#6

Looks like you’re trying to do this through the SDK rather than the manual process Crystal referred to. I haven’t done exactly this but when you’re dealing with BO’s nested objects, it’s usually best to use the object types available in the SDK. Most likely you can cast your results to a “Universe” object and extract the Connection ID from that. I haven’t worked with Universes that much so I can’t provide an example. I have done some very similar stuff with Groups and Users. Reviewing the SDK documentation might give you some clues to get where you want to go.

ChrisW1204 :us: (BOB member since 2011-04-21)

#7

You can use the DataConnections property, but you have to cast the InfoObject to Universe. Here’s some sample code to get all connections for a universe, using VBA:

    Dim oIOs As InfoObjects
    Set oIOs = xir2Infostore.Query("select top 5 * from ci_appobjects where si_kind= 'universe'")
    
    Dim oIO As Universe
    Dim oConn As InfoObject
    Dim oConnID As Long
    Dim x As Long
    For Each oIO In oIOs
        For x = 1 To oIO.DataConnections.Count
            oConnID = oIO.DataConnections(x)
            Set oConn = xir2Infostore.Query("select si_name from ci_appobjects where si_id = " & oConnID).Item(1)
            Debug.Print oIO.Title & "  " & oConn.Title
        Next
    Next

Joe

joepeters :us: (BOB member since 2002-08-29)