The Microsotrategy Metadata is a repository which stores all the definitions of MicroStrategy objects. We can get an overview of all these objects and their relationships using queries against the tables of this repository.

In internet, the information about MicroStrategy metadata is very scarce , so, I want to share with you some information which I found interesting and queries which could be useful. Although Metedata is composed by multiple tables deeply interrelated , I will highlight four of these which I consider most useful

DSSMDOBJINFO – I think that is 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.

Here you have a query which translates the type of the objects of your 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

As we see, the object_type field indicates the type of each object in the table.
I attached more complete list :

Constant	Object Type	Object Description
DssTypeUnknown	-1	Type of object is not specified
DssTypeReserved	0	Never used, and all functions should check that zero(0) never pass for a type.
DssTypeFilter	1	A restriction on the data in the warehouse (Filters)
DssTypeTemplate	2	The layout of a report (Templates)
DssTypeReportDefinition	3	Describes a report that can be executed (Reports)
DssTypeMetric	4	A formula to be evaluated at a specific dimensionality (Metrics)
Unused	5	None
DssTypeAutostyles	6	A custom or predefined formatted layout for a report (Autostyle)
DssTypeAggMetric	7	A formula to be evaluated at any dimensionality (Base Formulas)
DssTypeFolder	8	A collection of objects (Folders)
Unused	9	None
DssTypePrompt	10	A method to prompt the user for information (Prompts)
DssTypeFunction	11	A function that can be used to build expressions (Functions)
DssTypeAttribute	12	A natural grouping of data (Attributes)
DssTypeFact	13	A value that can be read directly from the warehouse (Facts)
DssTypeDimension	14	A grouping of related attributes (Hierarchies)
DssTypeTable	15	A logical table (Logical Table)
Unused	16	None
DssTypeFactGroup	17	A collection of related facts (Fact groups)
DssTypeShortcut	18	Contains a reference to another DSS Object (Shortcuts)
DssTypeResolution	19	A collection of answers to prompts (Prompt answers)
Unused	20	None
DssTypeAttributeForm	21	Represents a value on elements of an attribute (Attribute forms)
DssTypeSchema	22	Collection of objects that define the warehouse structure (Schema)
DssTypeFindObject	23	(Deprecated) Describes a simple search (Search definition)
DssTypeCatalog	24	List of (relevant) tables in a database (Catalogs)
DssTypeCatalogDefn	25	Describes how to construct a catalog (Catalog definitions)
DssTypeColumn	26	The properties needed to define a column of a DB table (Columns)
DssTypePropertyGroup	27	Used to cache lists of property sets (Internal Object)
DssTypePropertySet	28	Holds the definition of a property set (Properties)
DssTypeDBRole	29	Represents a logical database (Database Instance)
DssTypeDBLogin	30	Describes how to login to a database (Database Login)
DssTypeDBConnection	31	Describes how to connect to a database (Database Connection)
DssTypeProject	32	A environment in which to perform OLAP analysis (Projects)
DssTypeServerDef	33	A description of a configuration of a DSS Server (Server Definitions)
DssTypeUser	34	Represents a user or group of users of the system (Groups / Users)
Unused	35	None
DssTypeConfiguration	36	Top level object – represents a Castor installation (Castor Server Config.)
DssTypeRequest	37	Describes a DSS Server request (Scheduled requests)
Unused	38	None
DssTypeSearch	39	Describes criteria that specify a search for objects (Searches)
DssTypeSearchFolder	40	Folder-like object used to store result of a search (Search Folders)
Unused	41	None
DssTypeFunctionPackageDefinition	42	Describes a package of functions (Functions Definitions)
DssTypeRole	43	Placeholder to describe way of extending an attribute (Transformations)
DssTypeSecurityRole	44	Holds privileges of a user that plays a certain role (Security Roles)
DssTypeInBox	45	Shortcut to a folder (Shortcuts to folders)
DssTypeInBoxMsg	46	Describes an uncollected report on a DSS Server (DSS Server Reports)
DssTypeConsolidation	47	Collection of consolidation elements (Consolidations)
DssTypeConsolidationElement	48	Arithmetic expression over elements (Consolidation elements)
DssTypeScheduleEvent	49	An event that can trigger a scheduled report (Scheduled events)
DssTypeScheduleObject	50	Something that can be triggered (Scheduled objects)
DssTypeScheduleTrigger	51	Binding between schedule event and object (Scheduled triggers)
DssTypeLink	52	Holder of a property that spans objects (Links)
DssTypeDBTable	53	A physical table in a database (Physical tables)
DssTypeTableSource	54	Physical tables with same suffix and prefix (Same suffix and prefix tables)
DssTypeDocumentDefinition	55	An document built from reports and HTML text (Report Services Documents)
DssTypeDrillMap	56	Describes a set of possible drill paths (Drill Maps)
DssTypeDBMS	57	Holds information about a type of physical database (DBMS definition)
DssTypeMDSecurityFilter	58	Holds filter restrictions that can be applied to a user (Security Filters)
DssTypePromptAnswer	59	Holds single prompt answer information
DssTypePromptAnswers	60	Holds multiple prompt answer information
DssTypeGraphStyle	61	Holds grapth style information for report templates
DssTypeChangeJournalSearch	62	Holds information about Change Journal search objects
DssTypeBlob	63	Holds information about Blob images
DssTypeDashboardTemplate	64	Describes templates used in dashboards

We can get interesting facts,  such as, the number of schema/public objects by project:

SELECT C.OBJECT_NAME AS PROJECT_NAME,OP.COUNT_OBJETOS_PUBLICOS,OE.COUNT_OBJETOS_DE_ESQUEMA
FROM
(SELECT PROJECT_ID,COUNT(OBJECT_ID) AS “COUNT_OBJETOS_DE_ESQUEMA”
FROM DSSMDOBJINFO
WHERE OBJECT_TYPE IN (14,12,13,11,15,43) AND SUBTYPE IN (3585,3072,3328,2816,3840,11009)
GROUP BY PROJECT_ID) OE
JOIN
(SELECT PROJECT_ID
,COUNT(OBJECT_ID) AS “COUNT_OBJETOS_PUBLICOS”
FROM DSSMDOBJINFO
WHERE OBJECT_TYPE IN (3,55,6,47,1,56,1,4,10,39,2)
AND SUBTYPE IN
(14081,1536,12032,257,14336,256,1024,2556,2544,9984,512,768,769,770,774,777,776)
GROUP BY PROJECT_ID) OP ON OE.PROJECT_ID=OP.PROJECT_ID
JOIN
DSSMDOBJINFO C ON C.OBJECT_ID=OE.PROJECT_ID
ORDER BY 1;

Objects modified the last 7 days

SELECT DISTINCT B.OBJECT_NAME AS NOMBRE,A.USER_ID,C.OBJECT_NAME as CAMBIADO_POR,D.CREATE_TIME AS F_CREACION,D.MOD_TIME AS F_MODIFICACION

FROM DSSMDJRNINFO A

JOIN
DSSMDJRNOBJD B ON A.TRANSACTION_ID=B.TRANSACTION_ID
JOIN
DSSMDOBJINFO C ON A.USER_ID = C.OBJECT_ID
JOIN
DSSMDOBJINFO D ON B.OBJECT_NAME=D.OBJECT_NAME
AND (SYSDATE-7) <= (D.MOD_TIME -0);

List of objects in a folder, for example: in ‘MY REPORTS’

SELECT C.OBJECT_NAME AS “USUARIO”,D.OBJECT_NAME AS “NOMBRE_PROYECTO”,A.OBJECT_NAME AS “NOMBRE_OBJETO”,B.OBJECT_NAME AS “CARPETA”
FROM
(SELECT * FROM DSSMDOBJINFO WHERE PARENT_ID IN
(SELECT OBJECT_ID FROM DSSMDOBJINFO WHERE OBJECT_TYPE=8 AND SUBTYPE=2048 AND OBJECT_NAME LIKE ‘MY REPORTS’)) A
JOIN
DSSMDOBJINFO B ON A.PARENT_ID=B.OBJECT_ID
JOIN
DSSMDOBJINFO C ON B.PARENT_ID=C.OBJECT_ID
JOIN
DSSMDOBJINFO D ON C.PROJECT_ID=D.OBJECT_ID
ORDER BY 1;

DSSMDOBJDEPN – 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

DSSMDOBJDEFN – contains the definition of each object. Unfortunately this information is encoded ( binary encoding , ASCII ) so I have not found a way to make it useful .

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

In future posts, I hope to expand information about Metadata and tables.

You can get more information in the follow post:
Microstrategy Metadata & Statistics Tables (II)