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




15 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
  2. Hi Bowman,

    We have developed a new gen developer friendly BI framework with some extremely unique features. Would like to give you early access & love to hear your opinion. Please do let me know of how to reach out to you. Would be launching product in 3 weeks from now.

    Also could you please share your email details for further communication.

    Regards,
    Anugraha

    RispondiElimina
  3. Questo commento è stato eliminato dall'autore.

    RispondiElimina
  4. Truely a very good article on how to handle the future technology. After reading your post,thanks for taking the time to discuss this, I feel happy about and I love learning more about this topic. keep sharing your information regularly for my future reference. This content creates a new hope and inspiration with in me. Thanks for sharing article like this. The way you have stated everything above is quite awesome. Keep blogging like this. Thanks.

    Tooth Braces In Chennai

    Dental Hospital in Chennai

    RispondiElimina
  5. Very informative ..i suggest this blog to my friends..Thank you for sharing
    MicroStrategy Online Training

    RispondiElimina
  6. There is a amazing explanation..many things gather about it your self...i really enjoy it....
    seo company in chennai

    RispondiElimina
  7. Really Good blog post.provided a helpful information about mstr metadata extracting objects.I hope that you will post more updates like this.
    MSBI Training in Chennai

    RispondiElimina
  8. Interesting blog which attracted me more.Spend a worthful time.keep updating more.
    Digital marketing company in Chennai

    RispondiElimina
  9. Questo commento è stato eliminato dall'autore.

    RispondiElimina
  10. Interesting blog post.This blog shows that you have a great future as a content writer.waiting for more updates...

    Vmware Training in Chennai

    Web Designing Training in Chennai

    AWS Training in Chennai

    Linux Training in Chennai

    Microsoft Azure Training in Chennai





    RispondiElimina
  11. Questo commento è stato eliminato dall'autore.

    RispondiElimina
  12. thanks for giving that type of information. ielts coaching in gurgaon

    RispondiElimina