BusinessObjects Board

Querying the CMS

sdk development
#1

Just wanted to clear up a few things in this thread:

The query builder is a very simple web ui which allows the user to enter a query string which will be passed to the IInfoStore.Query function.

The query string passed to the InfoStore is not real SQL and you don’t reference real table names, but its use is very similar and well documented. The reason Crystal Decisions went for this sort of syntax was because most people are familiar with SQL so there’s no huge learning curve and it’s very flexible.

The entire product is built on top of this SDK, all the services in BOXI use the same SDK that 3rd party developers use - this architecture was based on lessons learned in the predecessor of Crystal Enterprise (Seagate Info).

The properties of each Plugin object are just large nested Strings stored in BLOB fields in the database hence the need to use the Infostore to retrieve meaningful data versus querying directly against the CMS database.

I’m no BO expert, but from what I’ve heard, querying the old BO repository was fairly common place and a lot of people therefore try the same when migrating to BOXI, but it’s no longer an option - always use the InfoStore (BOE sdk).

JGreg
www.zoom-software.com
BOXI TOOLS
(Ex Seagate Software, Crystal Decisions and BO)

JGreg (BOB member since 2007-11-21)

Creating reports from CMS Repository XIR2
where exactely CI_INFOOBJECTS is located
Need help in Add-in BOXI
Losing Row-Level Security When Cloning the Universe
BO Repository table relationship
#2

I split the previous post from here, because it had wandered from the original topic, but was still a very valuable post.

The CMS repository, and getting information out of that repository, is quite a mystery. A mystery to most, but especially to those with a background in the “classic” (v5 / v6) repository structure. The previous post does an excellent job of explaining some of that mystery. It’s a blending of (at least) three things … a database, a SDK, and a SQL-like query language.

OK, a database and a SQL-like query language … what could be easier, right? At least for those BusObj professionals who are at least somewhat familiar with relational databases and SQL. Oh, a SDK! What could be easier, at least for BusObj professionals with at least some programming experience. Unfortunately, it just isn’t that straightforward for either group of people {sigh}.

The CMS design is rather innovative, but unfortunately as stated, not very straightforward. The information is stored in a database, but as we have all learned it is stored in compressed, encrypted BLOBs. Not usable by traditional SQL, so traditional SQL folks are out of luck. Enter the SDK, a series of objects and properties that “understand” the contents of the BLOBs in the database. All well and good, but the objects and properties are also not accessible directly, so traditional programmers are out of luck as well.

To query the CMS using the SDK, you start with more of a “programming” approach, but very basic … instantiate a session, login, and get a handle to the IInfostore. This is basically what the Query Builder web application does for you, although you can do it for yourself in your own application. That’s where the “programming” approach stops, and the “SQL-like” language kicks in. Essentially, the language re-interprets the objects and properties of the SDK into conditions and fields of a SELECT statement. The key of course is learning what objects and properties are available, and how to re-interpret those into the conditions and fields for the SELECT statement.

There you have it. The SQL-like statement you write is really referencing objects and properties of the SDK, and the SDK knows how to interpret the BLOBs of information stored in the CMS repository. Simple huh!

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Object creation date
#3

Quite a mys(t)ery and I have a hard time figuring out why BusinessObjects does not provide a better interface to retrieve information from the CMS database, in particular as XI R2 is an Enterprise application.

Andreas :de: (BOB member since 2002-06-20)

#4

I have built a utility that extracts the XI CMS repository and loads to a series of RDBMS tables for reporting. Let me know if you have any questions and I’ll be happy to help.

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

#5

Does this utility get just the Repository, or do you mean the whole Infostore? Is this utility available or for sale? If it is the Infostore, I would very much like to have it.

jbullman (BOB member since 2008-01-09)

#6

Does anyone have a tool that dumps the repository info out to a db already written?

Thanks.

GigaGuy :us: (BOB member since 2007-02-13)

#7

The utility decodes the XI Repository to expose all attributes and the relationship between objects. It writes to it’s own DB (SQL Server or Oracle currently), as well as a set of .txt files that can be imported to the DB of your choice.

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

#8

And are you sharing or selling this?

jbullman (BOB member since 2008-01-09)

#9

The utility is for sale at this time. Please feel free to email me if you have further questions regarding the product, as I don’t want to openly advertise on the forum.

In the meantime, I am happy to help here where I can with specific questions on how to pull various information from the repository.

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

#10

Please let me know how much your tool costs and exactly what it is…

Thanks.

Rita

sturgra (BOB member since 2006-11-08)

#11

For those of you with followup questions, please contact me at the email address within my forum profile. For those of you who cannot view my profile, please post your email contact information.

Thanks.

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

#12

Hi Crystal,

I am trying to pull the no of objects (webi ) in user folder for a specific user, am trying this

select si_id from ci_infoobjects
where si_owner = ‘728526-ASP’
and SI_KIND = ‘FavoritesFolder’

select count(si_id) from ci_infoobjects
where si_owner = ‘728526-ASP’
And SI_PARENT_FOLDER = 2789

is there a better way of doing this instead of firing two queries , does query builder support subqueries.

Thanks,
-Narayana

narainmajji :us: (BOB member since 2006-04-27)

#13

Query Builder does not support subqueries. For subquery-type logic, you’ll need to use the SDK.

If you are dealing with a large volume of users you want to retrieve this information for, you are best off approaching this programatically. Otherwise, running 2 seperate queries can be a quick and dirty approach. You will need to filter for si_kind = ‘Webi’ on your second query in the example you provide.

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

#14

Hi Crystal,

Am trying to get the list of Universes and the respective connection details from CMS using SQL’s. But am not able to get the right result.

Could you please help me out with your inputs?

Thanks.

stvel (BOB member since 2007-10-04)

#15

Start by querying for your universes:

select * from ci_appobjects
where si_kind = ‘universe’

now, take a look at the SI_DATACONNECTION that is exposed. This stores the SI_ID of the universe connection.

Query ci_appobjects for this SI_ID, and you can view the connection properties…

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

#16

Crystal01, Thanks for your reply.
I tried with this SQL in VB macro.

Select SI_NAME,SI_DATACONNECTION from ci_appobjects where si_kind = ‘universe’

I got something like this. All these Universes use different Connection, but only ‘0’ and ‘1’ are repeating for all.
Unv 1 0
Unv 2 1
Unv 3 0
Unv 4 1
Unv 5 1

Please let me know, where it’s going wrong and how to proceed further.
Thanks.

stvel (BOB member since 2007-10-04)

#17

The ID within SI_DATACONNECTION is actually nested, so to see this value you can revert to running select * from ci_appobjects where si_kind = ‘universe’. Verify the query first in QB before you plugin to your VB as well.

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

#18

Hi

Executed first : select * from ci_appobjects where si_kind = ‘universe’
Then used this InfoObject.Properties(“SI_DATACONNECTION”).Value to get the dataconnection.

But again get the same result as before.

Unv_1 0
Unv_2 1
Unv_3 0
Unv_4 1
Unv_5 1

And i dont ahve QB access …, so i have to check it with VB only.

Thanks.

stvel (BOB member since 2007-10-04)

#19

with Java I do:

IProperties prop = (IProperties) iObject.properties().getProperty("SI_DATACONNECTION").getValue();
			for (Object key : prop.keySet()) {
				if (!key.equals(16777248)) {        //to remove SI_TOTAL, the number of connections
					System.out.println(iObject.getTitle() + " - " + prop.getProperty(key));
				}
			}

dbrown24 (BOB member since 2009-07-08)

#20

Hi

could any of you guys tell me how to use the Query Builder or SDK to get details of the groups and their rights that are set up against the Application (Infoview, designer etc) in the CMC.

Thanks

JJ

jane123 :it: (BOB member since 2006-02-22)