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;

Dallas Marks

Dallas Marks

I am an analytics and cloud architect, author, and trainer. An AWS certified blogger, SAP Mentor Alumni and co-author of the SAP Press book SAP BusinessObjects Web Intelligence: The Comprehensive Guide, I prefer piano keyboards over computer keyboards when not blogging or tweeting.

8 thoughts on “Querying the Business Objects 5.x/6.x Repository with SQL

  1. Hi Dallas!
    Great sql queries!! They helped me to get a grip on what they actually contained…
    Like you with these queries, I am currently ‘stuck’ with BusObj V5. My current task is looking for a way to track which reports have been run and by who. It appears that is possible if the Audit is turned on. BO is hosted on Win2K box. Do you know how I can turn Audit on?
    Thanks in advance.
    Bryan

  2. Hi Bryan,
    Thanks for the SQLs. I’m new to the world of BO & I’m wondering if I could run the SQLs if I access BO v5 via remote server. I could probably contact the admin of the server for this, but could you please throw light on what would be the schema to check these tables? The underlying database is Oracle in our case.

  3. Oops, Sorry for the miscredit!

    Dallas, My post is intended to you. Thanks in advance.

  4. Does that mean that there is a separate “audit” option to be purchased along with what is available in the standard Repository objects OBJ_M_*?
    Is there a way to just retrieve when a universe was last accessed (not modified or saved) with the help of available repository tables? Thanks very much for all inputs.

    1. I’m not sure what kind of dates are in those tables, sorry. It’s been several years since I’ve worked with them. You may simply need to move everything to Xi 3.1 and then look at its audit tables after a few months of usage to figure out which content is actually used.

  5. hey dallas, great queries. Can these queries be run via sql builder in XI R2 system or do we need to use native DB tools like sqlplus ? thanks

    1. Thanks for writing. For “classic” SAP BusinessObjects 5.x/6.x (pre-XI and XI R2), these are the actual tables in the repository database, so you can use SQL Plus with Oracle or your favorite SQL tool.

Comments are closed.