mercoledì 18 aprile 2012

MSTR metadata: extracting objects info


MSTR:_ADM is the schema where the metadata are stored.
Accessing the schema you can extract some info.
We used them to build a map with tags for the user.

--Query PROJECT
select 
a.OBJECT_NAME, a.OBJECT_ID, a.DESCRIPTION , a.*
from MSTR_ADM.DSSMDOBJINFO  a 
where a.SUBTYPE in ('8192')


--Query REPORT

select 
a.OBJECT_NAME, a.OBJECT_ID, a.DESCRIPTION 
from MSTR_ADM.DSSMDOBJINFO  a 
where a.SUBTYPE in ('768','769')
and a.PROJECT_ID = '0EDCC01C4F4704248627968B3DD51238'



--Query ATTRIBUTES
select 
a.OBJECT_NAME, a.OBJECT_ID, a.DESCRIPTION , a.*
from MSTR_ADM.DSSMDOBJINFO  a 
where a.SUBTYPE in ('3072')
and a.PROJECT_ID = '0EDCC01C4F4704248627968B3DD51238'


--Objects used by a Report
select 
 a.*,
 d.*
from MSTR_ADM.DSSMDOBJDEPN  a
join MSTR_ADM.DSSMDOBJINFO  d on a.DEPN_OBJID = D.OBJECT_ID and a.PROJECT_ID = D.PROJECT_ID
where a.PROJECT_ID = '0EDCC01C4F4704248627968B3DD51238'
and a.OBJECT_ID = '219330A34F97C354FE1FC287EC8A6324'
order by D.SUBTYPE, D.OBJECT_NAME





DSSMDOBJINFO is the main table you'll want.  It has the Name of every object as well as the Object Type, so you'll be joining to this guy a lot.  For Object Type, you can build your own lookup table by just checking what a few objects are stored as.  I think there is a bigger list somewhere, but in the MD they only store the number.  You'll also find Parent_ID which is the Folder an object is in.  Very useful for building tree structures and filtering out for only objects in Public Objects.
DSSMDOBJDEPN is the Object Dependency table.  Here you'll find Object_ID and DEPN_OBJID.  There is the table you can use to climb up from a Column to a Report.
DSSMDOBJDEFN (or *DEF2 in 9.0.1) sadly, this table contains the real gold which is the definition of each object.  It's encoded a few layers deep (binary encoded, compressed, ascii encoded) for performance, so you can't get any useful information out of it.  But if you're ever trying to query for a specific bit of information you can't find anywhere else about an object, good change its there.
DSSMDOBJSECU is the security table.  Trust_ID is the User/Group who has access, and RIGHTS is a hash of the kind of access they have (View, Full, Custom).  Trial and error can decode the Rights for you.
DSSCSSUBINST has your Distribution Services Subscriptions.  Pretty straight forward table.
DSSMDOBJLOCK locks table
DSSMDOBJPROP properties table, generico
DSSMDOBJTRNS traductions
DSSMDOBJSECU
DSSMDOBJCMNT comments




2 commenti:

  1. Hi Bowman,

    I am a bit confused and hope you can clarify. This table DSSMDOBJDEPN stores data of an Object and it's components and the column names are the ones which have confused me. The DEPN_OBJID should be something COMP_OBJID.
    I want to find all the reports which are dependent or affected by a table(s). If I try the following SQL, the result set is empty but if I check through desktop, I get a bunch of reports when I search for dependents of a table recursively:

    SELECT * FROM
    (select I.PROJECT_ID PROJECT_ID,D.DEPN_OBJID OBJECT_ID
    from DSSMDOBJINFO I
    join DSSMDOBJDEPN D
    ON ( I.OBJECT_ID = D.OBJECT_ID
    AND I.PROJECT_ID = D.PROJECT_ID)
    where I.PROJECT_ID = '8E0D81C647128556F0BF6EB0812AB739'
    AND I. OBJECT_ID = '3CDF74D446F4BA9D209D2185DC74A35F'
    AND D.DEPNOBJ_TYPE NOT IN (11,26,53)
    ) A
    JOIN DSSMDOBJDEPN D1
    ON (A.PROJECT_ID = D1.PROJECT_ID
    AND A.OBJECT_ID = D1.OBJECT_ID)
    WHERE D1.DEPNOBJ_TYPE = 3

    I am missing something hope you can help.

    TIA!
    Ashish

    RispondiElimina