MicroStrategy - Metadata Tables

mstr

Important:
https://community.microstrategy.com/t5/Architect/TN16048-List-of-all-object-types-and-object-descriptions-in/ta-p/176416 - done reading
https://community.microstrategy.com/t5/Administration/IS-there-any-technote-or-document-which-gives-the-list-of/td-p/202460 - done reading, look near the bottom of the page

http://bipostit.com/en/2014/12/28/queries-metadata-i/ - done reading
http://bipostit.com/en/2015/03/21/microstrategy-metadata-statistics-tables-ii/ - done reading
http://sankaran-microstrategy.blogspot.com/2012/01/useful-metadata-queries.html - done reading
http://bowmanmstr.blogspot.com/2012/04/mstr-metadata-extracting-objects-info.html - done reading
http://businessintelligence.ittoolbox.com/groups/technical-functional/microstrategy-l/retrieving-information-abt-mstr-objects-from-the-dssmdobjinfo-metadata-repository-table-2343728 - done reading
http://bowmanmstr.blogspot.com/ - done reading

Queries

What are the tables in the metadata database?

  1. DSSCSADDRESS (DSS-CS-ADDRESS): Distribution service. Store the contact ID, name and email address
  2. DSSCSCONTACT (DSS-CS-CONTACT): Distribution service. Contains contactID, MSTRUSER_ID
  3. DSSCSPSNLZTN (DSS-CS-PSNLZTN): Distribution service. Not sure
  4. DSSCSRCOLCON (DSS-CS-RCOLCON) - Distribution service. Not sure.
  5. DSSCSRINSTRG (DSS-CS-RINSTRG): Distribution service. Contains INST_ID, TRIGGER_ID
  6. DSSCSSYSPROP (DSS-CS-SYSPROP): Distribution service. Contains a few system properties
  7. DSSCSBADGETB (DSS-CS-BADGETB): Empty
  8. DSSCSDEVCKEY (DSS-CS-DEVC-KEY): Empty
  9. DSSMDJRNINFO: Change journal. Contains some sort of transaction, perhaps related to change journal
  10. DSSMDJRNLNKS: Change journal. Not sure, perhaps related to change journal
  11. DSSMDJRNOBJC: Change journal. Not sure, perhaps related to change journal
  12. DSSMDJRNOBJD: Change journal. Not sure, perhaps related to change journal
  13. DSSMDJRNOBJS: Change journal. Not sure, perhaps related to change journal
  14. DSSMDLNKITEM - Not sure
  15. DSSMDLNKPROP - Not sure
  16. DSSMDOBJBLOB - Empty
  17. DSSMDOBJCMNT (DSS-MD-OBJ-CMNT): Store comments
  18. DSSMDOBJDEF2 - Not sure
  19. DSSMDOBJDEFN - Empty
  20. DSSMDOBJLOCK (DSS-MD-OBJ-LOCK): Table containing locks
  21. DSSMDOBJPROP (DSS-MD-OBJ-PROP): properties table
  22. DSSMDOBJTRNS (DSS-MD-OBJ-TRNS): Hold various translations
  23. DSSMDSYSPROP - Hold various properties
  24. DSSMDUSRACCT - Hold the MSTR user LOGINID

What is the purpose of the DSSMDOBJINFO (DSS-MD-OBJ-INFO)?

This may be the most interesting table because it includes the name and type of the objects in Microstrategy. With this table as support, we can find most of the elements in the other tables of Metadata.

  SELECT o.project_id,
         CASE o.object_type
            WHEN 1 THEN 'filter (1)  '
            WHEN 2 THEN 'template (2)'
            WHEN 3 THEN 'report (3)'
            WHEN 4 THEN 'metric (4)'
            WHEN 6 THEN 'autostyle (6)'
            WHEN 8 THEN 'folder (8)'
            WHEN 10 THEN 'prompt (10)'
            WHEN 11 THEN 'function (11)'
            WHEN 12 THEN 'attribute (12)'
            WHEN 13 THEN 'fact (13)'
            WHEN 14 THEN 'hierarchy (14)'
            WHEN 15 THEN 'table (15)'
            WHEN 21 THEN 'attribute id (21)'
            WHEN 22 THEN 'schema (22)'
            WHEN 24 THEN 'warehouse catalog (24)'
            WHEN 25 THEN 'warehouse catalog definition (25)'
            WHEN 26 THEN 'table column (26)'
            WHEN 28 THEN 'property sets (28)'
            WHEN 34 THEN 'users/groups (34)'
            WHEN 39 THEN 'search (39)'
            WHEN 42 THEN 'package (42)'
            WHEN 47 THEN 'consolidations (47)'
            WHEN 52 THEN 'link (52)'
            WHEN 53 THEN 'table (53)'
            WHEN 56 THEN 'drill map (56)'
            WHEN 58 THEN 'security filter (58)'
            ELSE 'OTHERS'
         END
            AS tipo,
         o.object_name,
         o.object_id
    FROM dssmdobjinfo o
ORDER BY tipo

See http://bipostit.com/en/2014/12/28/queries-metadata-i/

What is the purpose of the DSSMDOBJDEPN (DSS-MD-OBJ-DEPN) table?

This table is the table of dependencies between objects ( Indicates the relationship: object – dependent object ). With this table for example , we can get the relationship user-group which belongs:

SELECT info_b.OBJECT_NAME AS GRUPO, info_a.OBJECT_NAME AS USUARIO
  FROM DSSMDOBJDEPN dpn
       JOIN DSSMDOBJINFO info_a
          ON dpn.OBJECT_ID = info_a.OBJECT_ID
       JOIN DSSMDOBJINFO info_b
          ON dpn.DEPN_OBJID = info_b.OBJECT_ID
 WHERE     info_a.OBJECT_TYPE = 34
       AND info_a.SUBTYPE = 8704
       AND info_b.OBJECT_TYPE = 34
       AND info_b.SUBTYPE = 8705
DSSMDOBJINFO.SUBTYPE = 8704 -> USERS
DSSMDOBJINFO.SUBTYPE = 8705 -> GROUPS

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.

What is the purpose of the DSSMDOBJSECU (DSS-MD-OBJ-SECU)table?

This is the security table . Trust_ID is the user / group access , and RIGHTS is a hash access type (View, Full, Custom).

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.

What is the purpose of the DSSCSSUBINST (DSS-CS-SUB-INST) table?

DSSCSSUBINST has your Distribution Services Subscriptions.

Which tables contain the scheduled reports and their emails?

Not sure. Need research.

How can we determine the project name given a project number?

When looking at the DSSErrors.log file, sometimes MSTR display an error message with a project number instead of the project name. We can determine the project name given the project number by looking at the top of the log file. When the Intelligence server is started, it writes the followings lines:

PrintServerDef: ---------- Info for Project 18: end  ---------
PrintServerDef: ---------- Info for Project 19: begin ---------
PrintServerDef: Project Info - Name: QPrecision, Project load flag:Active, Metadata version:6.
PrintServerDef: Project Info - Cache information: Object cache file size:10240KB; Element cache file size:1024KB; Report cache file path:.\Caches\qmimsrdev01, Size:100MB; Document cache file path:, Size:256MB
PrintServerDef: Project Info - Cache information: Disable report caching: true, Report cache duration: 24, Max report cache count: 10000
PrintServerDef: Project Info - Cache information: Enable document caching: 0, Document cache duration: 24, Max document cache count: 100000
PrintServerDef: Project Info - Cube information: cube file path:, size:500MB, max cube cache count: 1000, cube DB match flag: 0, publish flag: 0
PrintServerDef: Project Info - Cache/Cube encryption option: 0, exclude fields: 1
PrintServerDef: Project Info - Max Report execution time: 3000, Max Scheduled Report execution time: -1, Max prompt waiting time: -1, Max Result Rows: -1
PrintServerDef: Project Info - Max Jobs per user account: 100, Max jobs per user connection: 100, Max jobs per project: 1000.
PrintServerDef: Project Info - Max user sessions per project: 500
PrintServerDef: Project Info - File Compression settings: Enabled: 1, Compression Type: 0, Compression Level: 6, File Extension: zip
PrintServerDef: Project Info - Email Compression settings: Enabled: 1, Compression Type: 0, Compression Level: 6, File Extension: zip
PrintServerDef: Project Info - Subscription governing settings: HistoryList: -1, Cache: -1, Email: -1, File: -1, Print: -1, Mobile: -1, PersonalView: -1
PrintServerDef: Project Info - Web Server Location .
PrintServerDef: Project Info - Deliveries Error Handling settings: Error: 0, No Data Returned: 1, Partial results: 0
PrintServerDef: Project Info - Deliveries Error Handling Notification settings: No Data Returned: 1, Partial results: 0
PrintServerDef: Project Info -Distribution Service Email Footer: Enabled: 0, Footer 
PrintServerDef: Project Info - History List: Keep Document available for manipulation when scheduling in PDF or Excel format: 1
PrintServerDef: Project Info -Distribution Service Printing Enable print range: 1
PrintServerDef: Project Info -Statistics SQL Timeout: 10
PrintServerDef: Project Info -Statistics Purge Timeout: 10
PrintServerDef: Project Info - Enable Mobile Push Update: 1
PrintServerDef: Project Info - Data Upload Size Limit (MB): 30
PrintServerDef: Project Info - Cube Quota (MB): 100
PrintServerDef: Project Info - Max Memory Per Data Fetch(MB): -1
PrintServerDef: Project Info - Cube Index Growth Check Frequency: every 30 minutes
PrintServerDef: Project Info - Cube Index Growth Upperbound: 500%
PrintServerDef: Project Info - Report Cache LKUP MaxRAM (MB): 100
PrintServerDef: Project Info - Document Cache LKUP MaxRAM (MB): 256
PrintServerDef: Project Info - Enable Search: 0
PrintServerDef: Project Info - Enable URL File Upload from MSTR Server: 0
PrintServerDef: Project Info - Enable URL File Upload from MSTR Server via Http Https: 0
PrintServerDef: Project Info - Enable URL File Upload from MSTR Server via ftp ftps: 0
PrintServerDef: Project Info - Enable URL File Upload from MSTR Server via File: 0
PrintServerDef: Project Info - Max msgs per project for one user: -1
PrintServerDef: Project Info - History List: Save RWD's export result when interactively sending to HL: 1
PrintServerDef: Project Info - History List: Save RWD's child msg to HL: 1
PrintServerDef: Project Info -Statistics Mobile Client Location: 0
PrintServerDef: Project Info -Statistics Max DB Connection Try: 5
PrintServerDef: ---------- Info for Project 19: end  ---------

to the DSSErrors.log file. We can do this provided that DSSErrors.log file has not been rotated. Otherwise, we may need to restart the Intelligence server which is a bit of a pain just to get this information. I HOPE THAT THERE IS A WAY TO QUERY THE METADATA TABLES FOR THIS INFORMATION.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License