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)
Fantastico. Muchas Gracias.
Hi Miguel,
The blog is very informative!!
Do we have any documentation of all metadata tables and its description including the column description fro reference. We need to query metadata to find ‘image objects’ used across projects under a project source.It would be helpful if we have some reference for creating query for this. Have you came across any of this kinda scenarios?
Thanks in advance.
Hello, Thank you for you triks.
I need a SQL script that allow me to get from Metadata DB the Free Form SQL definition of the Freeform reports of my project.
Can you help me please?
Thank you.