Querying the Business Objects 5.x/6.x Repository with SQL

BusinessObjects XI R1 was first shipped in early 2005.  The real action didn’t begin until XI R2 shipped in November of that year.  My first 5.1.x to XI R2 migration started the following January 2006.  I thought my migration days were behind me, but I find myself helping an OEM vendor that bundles BusinessObjects upgrade their customers from 5.1.8 to XI R2.  One of the reasons I keep this blog is just so I can easily find stuff I need wherever I go, even if it’s stuff that I don’t think anyone else cares about.  So since I’ve had to dust off these 5.1.x repository queries, I thought I would share the goodness.   There are only minor changes to the 6.x repository structure, so they should work there, too.

If anyone else out there is still on version 5, you’re not alone…  But I’m working on it, one customer migration at a time.

The following queries were created using MS Access and may need revision to run native in SQL Server, Oracle, etc.
See http://www.forumtopics.com/busobj/repository5/rpindex.htm as a reference to the repository schema.

Universe & Connection
SELECT OBJ_M_CONNECTION.M_CNTN_C_NAME, OBJ_M_CONNECTION.M_CNTN_N_TYPE, OBJ_M_CONNECTION.M_CNTN_C_NETNAME, OBJ_M_CONNECTION.M_CNTN_C_RDBMSNAME, UNV_UNIVERSE.UNI_FILENAME, UNV_UNIVERSE.UNI_LONGNAME, UNV_UNIVERSE.UNI_FIRSTAUTHOR, UNV_UNIVERSE.UNI_LASTAUTHOR, UNV_UNIVERSE.UNI_CREATEDATE, UNV_UNIVERSE.UNI_MODIFYDATE
FROM OBJ_M_CONNECTION INNER JOIN UNV_UNIVERSE ON OBJ_M_CONNECTION.M_CNTN_N_ID = UNV_UNIVERSE.UNI_CONNEXION
ORDER BY OBJ_M_CONNECTION.M_CNTN_C_NAME;

User Profile
SELECT OBJ_M_ACTOR.M_ACTOR_C_NAME AS UserName, (DateAdd("s",[OBJ_M_ACTOR.M_ACTOR_N_LAD],"12/15/1970")) AS UserLastActionDate, OBJ_M_ACTORLINK.M_ACTL_N_ACTORTYPE, IIf([M_ACTL_N_ACTORTYPE]=2,"General Supervisor",IIf([M_ACTL_N_ACTORTYPE]=4,"Supervisor","N/A")) AS UserProfileName
FROM OBJ_M_ACTORLINK INNER JOIN OBJ_M_ACTOR ON OBJ_M_ACTORLINK.M_ACTL_N_ACTORID = OBJ_M_ACTOR.M_ACTOR_N_ID;


Inbox
SELECT OBJ_M_ACTOR.M_ACTOR_C_NAME AS InboxUser, (DateAdd("s",[OBJ_M_ACTOR.M_ACTOR_N_LAD],"12/15/1970")) AS UserLastActionDate, OBJ_M_DOCUMENTS.M_DOC_C_NAME AS DocName, OBJ_M_ACTOR_1.M_ACTOR_C_NAME AS DocCreator, OBJ_M_DOCUMENTS.M_DOC_N_SIZE AS DocSize, OBJ_M_DOCUMENTS.M_DOC_N_TYPE AS DocType
FROM (OBJ_M_DOCUMENTS INNER JOIN (OBJ_M_ACTORDOC INNER JOIN OBJ_M_ACTOR ON OBJ_M_ACTORDOC.M_ACTDC_N_ACTORID = OBJ_M_ACTOR.M_ACTOR_N_ID) ON OBJ_M_DOCUMENTS.M_DOC_N_ID = OBJ_M_ACTORDOC.M_ACTDC_N_DOCID) INNER JOIN OBJ_M_ACTOR AS OBJ_M_ACTOR_1 ON OBJ_M_DOCUMENTS.M_DOC_N_USERID = OBJ_M_ACTOR_1.M_ACTOR_N_ID
ORDER BY OBJ_M_ACTOR.M_ACTOR_C_NAME, OBJ_M_DOCUMENTS.M_DOC_C_NAME;

User Capabilities
SELECT OBJ_M_ACTOR.M_ACTOR_C_NAME, OBJ_M_ACTOR.M_ACTOR_N_TYPE, OBJ_M_ACTOR.M_ACTOR_N_LEVEL, OBJ_M_ACTOR.M_ACTOR_N_STATUS, OBJ_M_GENPAR.M_GENPAR_N_USERID, OBJ_M_GENPAR.M_GENPAR_N_ID, OBJ_M_GENPAR.M_GENPAR_N_APPLID, OBJ_M_GENPAR.M_GENPAR_N_TYPE, OBJ_M_GENPAR.M_GENPAR_N_VALUE, OBJ_M_GENPAR.M_GENPAR_C_LABEL
FROM OBJ_M_GENPAR RIGHT JOIN OBJ_M_ACTOR ON OBJ_M_GENPAR.M_GENPAR_N_USERID = OBJ_M_ACTOR.M_ACTOR_N_ID
ORDER BY OBJ_M_GENPAR.M_GENPAR_N_USERID;

Global User Capabilities
SELECT OBJ_M_GENPAR.M_GENPAR_N_USERID, OBJ_M_GENPAR.M_GENPAR_N_ID, OBJ_M_GENPAR.M_GENPAR_N_APPLID, OBJ_M_GENPAR.M_GENPAR_N_TYPE, OBJ_M_GENPAR.M_GENPAR_N_VALUE, OBJ_M_GENPAR.M_GENPAR_C_LABEL
FROM OBJ_M_GENPAR LEFT JOIN OBJ_M_ACTOR ON OBJ_M_GENPAR.M_GENPAR_N_USERID = OBJ_M_ACTOR.M_ACTOR_N_ID
ORDER BY OBJ_M_GENPAR.M_GENPAR_N_USERID;


Document, Category, Universe
SELECT OBJ_M_DOCUMENTS.M_DOC_C_NAME AS Document, (DateAdd("s",[OBJ_M_DOCUMENTS.M_DOC_N_DATE],"12/15/1970")) AS ExportDate, OBJ_M_ACTOR.M_ACTOR_C_NAME AS Creator, OBJ_M_CATEG.M_CATEG_C_NAME AS Category, OBJ_M_DOCATVAR.M_DOCATV_C_DPNAME, OBJ_M_DOCATVAR.M_SRC_C_NAME, (DateAdd("s",[OBJ_M_DOCATVAR.M_DOCATV_N_REF],"12/15/1970")) AS LastRefreshDate, OBJ_M_UNIVERSES.M_UNI_C_FILENAME, OBJ_M_UNIVERSES.M_UNI_C_LONGNAME
FROM OBJ_M_UNIVERSES RIGHT JOIN ((((OBJ_M_DOCCATEG RIGHT JOIN OBJ_M_DOCUMENTS ON OBJ_M_DOCCATEG.M_DOC_N_ID = OBJ_M_DOCUMENTS.M_DOC_N_ID) LEFT JOIN OBJ_M_CATEG ON OBJ_M_DOCCATEG.M_CATEG_N_ID = OBJ_M_CATEG.M_CATEG_N_ID) LEFT JOIN OBJ_M_ACTOR ON OBJ_M_DOCUMENTS.M_DOC_N_USERID = OBJ_M_ACTOR.M_ACTOR_N_ID) INNER JOIN OBJ_M_DOCATVAR ON OBJ_M_DOCUMENTS.M_DOC_N_ID = OBJ_M_DOCATVAR.M_DOC_N_ID) ON OBJ_M_UNIVERSES.M_UNI_C_FILENAME = OBJ_M_DOCATVAR.M_SRC_C_NAME
ORDER BY OBJ_M_DOCUMENTS.M_DOC_C_NAME, OBJ_M_ACTOR.M_ACTOR_C_NAME, OBJ_M_CATEG.M_CATEG_C_NAME, OBJ_M_DOCATVAR.M_DOCATV_C_DPNAME;