Consultas útiles metadata de MicroStrategy

Aquí os dejo unas consultas útiles metadata de MicroStrategy almacena toda la información relativa a cada proyecto en la metadata. A través de ella podemos obtener de forma rápida y sencilla información sobre nuestros proyectos. A continuación os dejo algunos ejemplos donde debes sustituir el ID del proyecto XXXXXXX por el vuestro:

Listado de proyectos:

SELECT *
FROM DSSMDOBJINFO
WHERE OBJECT_TYPE = 32

Carpeta a las que tiene acceso un usuario (contiene un filtro para no mostrar ciertas carpetas como ‘mis objetos’ y algunos usuarios como el everyone):

select  distinct A.OBJECT_NAME, 
    case when c.SUBTYPE=8705 then 'GRUPO' else 'USUARIO' end "USUARIO/GRUPO",
    C.OBJECT_NAME "NOMBRE"
from DSSMDOBJINFO A,DSSMDOBJSECU B,DSSMDOBJINFO C
WHERE A.PROJECT_ID = B.PROJECT_ID AND
   A.OBJECT_ID=B.OBJECT_ID AND
   B.TRUST_ID=C.OBJECT_ID and
   A.OBJECT_TYPE=8 and --folder
   C.OBJECT_NAME not in ('Administrator','Everyone','Public / Guest')
  and c.SUBTYPE in(8704,8705) --subtype=8704 usuarios, 8705=grupos
   and a.project_id='XXXXXXX'
   and A.OBJECT_NAME not in ('Mis Resultados', 'Mis Objetos', 'Mis dosieres', 'Mis dosieres compartidos','Mis Informes','Mis Favoritos',
                             'My Answers','Mis segmentos','My Favorites','My Objects', 'My Reports', 'My Segments')
   and substr(A.OBJECT_NAME,1,10) = substr(C.OBJECT_NAME,1,10)
   order by 1,2,3

Listado de usuarios de un grupo concreto:

select GROUPNAME.object_name GRUPO ,USERNAME.object_name USUARIO
from DSSMDOBJINFO GROUPNAME
      join
            (select object_name,OBJDEPN.depn_objid from DSSMDOBJINFO b
                   join
                    (select distinct Object_id,depn_objid from DSSMDOBJDEPN
                     where object_id in (select object_id from DSSMDUSRACCT where isgroup=0)
                     and depn_objid = (select object_id from DSSMDOBJINFO
                                        where object_name ='Everyone'
                                        )) OBJDEPN
             on OBJDEPN.object_id = b.object_id) USERNAME
on USERNAME.depn_objid =GROUPNAME.object_id
where GROUPNAME.object_name = 'Everyone'
order by GROUPNAME.object_name;

Objetos modificados recientemente por proyecto:

select 
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 'OTROS'
end AS TIPO_OBJETO
, o.object_name "USUARIO/GRUPO",
mod_time FECHA_MOD,
create_time FECHA_CREA
from dssmdobjinfo o
where o.project_id='XXXXXXX'
order by mod_time desc, create_time desc;

Objetos de una carpeta en concreto:

SELECT
         D.OBJECT_NAME AS NOMBRE_PROYECTO,
         C.OBJECT_NAME AS CARPETA_PADRE,
         B.OBJECT_NAME AS CARPETA_BUSCADA,
         A.OBJECT_NAME AS NOMBRE_OBJETO
    FROM (SELECT *
            FROM DSSMDOBJINFO
           WHERE PARENT_ID IN
                    (SELECT OBJECT_ID
                       FROM DSSMDOBJINFO
                      WHERE     OBJECT_TYPE = 8
                            AND SUBTYPE = 2048
                            AND OBJECT_NAME LIKE 'Nombre_Carpeta')) 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;

Listado del login de los usuarios:

select distinct USUARIO
from DSSMDUSRACCT
where isgroup=0;

Nº de objetos por proyecto:

SELECT C.OBJECT_NAME AS PROYECTO,
         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;

Objetos modificados en los últimos 7 días:

SELECT DISTINCT B.OBJECT_NAME AS NOMBRE,
                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);

Objetos de los que se compone un objeto. Especialmente útil si en el object_id pones el id de un informe o documento:

SELECT OBJECT_NAME NOMBRE_INFORME
FROM DSSMDOBJINFO
WHERE OBJECT_ID IN(SELECT DEPN_OBJID
                    FROM DSSMDOBJDEPN
                    WHERE PROJECT_ID= 'XXXXXXX'
                    AND OBJECT_ID = 'XXXXXXX');