MicroStrategy Metadata Queries

Otros Artículos:

MicroStrategy Metadata Queries

Share on linkedin
LinkedIn
Share on facebook
Facebook
Share on twitter
Twitter
Share on whatsapp
WhatsApp

Nueva Semana, nuevo artículo: ¡Bienvenidos nuevamente a #BestInMicro! Un espacio donde iré escribiendo sobre distintos tópicos relativos a MicroStrategy, su uso, mejores prácticas y funcionalidades para poder aprovechar al máximo la herramienta.

MicroStrategy-Metadata-Queries-1

Hoy les traigo un artículo escencial para cualquier administrador. Hoy estaré hablando sobre la Metadata de MicroStrategy y algunas queries que son muy útiles a la hora de administrar la herramienta. ¡Empecemos! Y como cada semana, primero lo primero…

¿Qué es la Metadata de MicroStrategy?

La Metada es donde se almacena toda la definición de MicroStrategy. Es una base de datos donde se encuentra todo el “esqueleto” de la herramienta. Y con esto me refiero a todos los objetos: Atributos, tablas, nombres, usuarios, estados, información de los objetos, etc. 

Yendo a las bases, aquí debajo tenemos el esquema básico de la arquitectura de MicroStrategy:

MicroStrategy-Metadata-Queries-2

La Metadata de MicroStrategy almacena la información de todos los objetos que tenemos en nuestro entorno. Al conectarse a una Metadata, el Intelligence server ya tiene la capacidad de mostrarnos toda la estructura de nuestro entorno. Es por esto que, si recuerdan mi artículo sobre ScanMD y sus riesgos, ahora entenderán un poco más es tan importante llevar la Metadata con suma precaución.

Quizás muchos de ustedes estén familiarizados con herramientas como Enterprise manager o Platform analytics. Estos son proyectos que se utilizan para monitorizar el entorno de MicroStrategy. Si pensamos juntos un minuto, nos permiten armar reportes que brindan información de dos tipos: Qué objetos existen y sus estadísticas… ¿y de donde piensan que se obtiene la información de los objetos? ¡Acertaron, son consultas contra la Metadata! Entonces, si ya disponemos de estos proyectos para extraer información, se preguntarán…

¿En qué situaciones es necesario hacer una Query directo contra la Metadata?

Pues si bien estas son herramientas útiles y nos facilitan mucho esta tarea, la necesidad de tener que consultar directamente la Metadata es un escenario relativamente frecuente. Hay veces que las consultas requieren una precisión muy exacta, o incluso consultar algunas tablas que no existen en el modelo de los proyectos mencionados anteriormente.

MicroStrategy-Metadata-Queries-3

Estructura de la Metadata

Como dijimos anteriormente, la Metadata es una base de datos, y, por tanto, está conformada por tablas. Si bien pueden consultar en detalle de las tablas de Metadata, aquí abajo explico las más comunes.

  • DSSMDOBJDEPN: Esta tabla almacena la relación entre objetos. Por ejemplo, en esta tabla encontraremos si una carpeta se encuentra dentro de otra, si un usuario pertenece a un grupo o si un hecho se utiliza en un indicador.
  • DSSMDOBJINFO: Esta tabla nos detalla los objetos, su tipo, sus IDs, sus nombres y toda la información relevante de cada uno. Es seguramente la tabla que más consulten.
  • DSSMDUSRACCT: aquí están los usuarios y el tipo de autentificación que utilizan.
  • DSSMDOBJSECU: Contiene la seguridad de cada objeto.

Using UniqueIdentifier

A pesar de que aquí estamos hablando exclusivamente de las tablas de Metadata, en muchas situaciones querrán hacer join con las tablas de estadísticas, precisamente para saber de qué objetos son las estadísticas que se extraen. En estos casos, notarán que el ID de los objetos entre las distintas bases de datos son distintos, y tienen distinto formato. Para estos casos, por ejemplo en SQLServer (dependiendo la base de datos que se utilice irá variando), se utilizan las funciónes fn_CharToUniqueidentifier o fn_UniqueidentifierToCharMSTR.  Estas funciones, como bien indica el nombre, “castean” los IDs de objetos cambiándole su estructura para permitirnos hacer el join. Aquí debajo les dejo el código de las funciones. Como podrán ver, es básicamente un conjunto de subStrings ordenados o separados por guiones.

CREATE FUNCTION dbo.fn_UniqueidentifierToCharMSTR(@charid CHAR(36))
RETURNS CHAR(32)
AS
BEGIN
DECLARE @tpid VARCHAR(32)
SET @tpid = SUBSTRING(@charid, 1, 8)
SET @tpid = @tpid + SUBSTRING(@charid, 15, 4)
SET @tpid = @tpid + SUBSTRING(@charid,  10, 4)
SET @tpid = @tpid + SUBSTRING(@charid, 27, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 25, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 22, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 20, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 35, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 33, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 31, 2)
SET @tpid = @tpid + SUBSTRING(@charid, 29, 2)
RETURN @tpid
END

CREATE FUNCTION dbo.fn_CharToUniqueidentifier(@charid CHAR(32))
RETURNS UNIQUEIDENTIFIER
AS
BEGIN
         DECLARE @tpid VARCHAR(36)
         DECLARE @uid      UNIQUEIDENTIFIER
         SET @tpid = SUBSTRING(@charid, 1, 8) + ‘-‘
         SET @tpid = @tpid + SUBSTRING(@charid, 13, 4) + ‘-‘
         SET @tpid = @tpid + SUBSTRING(@charid,   9, 4) + ‘-‘
         SET @tpid = @tpid + SUBSTRING(@charid, 23, 2)
         SET @tpid = @tpid + SUBSTRING(@charid, 21, 2) + ‘-‘
         SET @tpid = @tpid + SUBSTRING(@charid, 19, 2)
         SET @tpid = @tpid + SUBSTRING(@charid, 17, 2)
         SET @tpid = @tpid + SUBSTRING(@charid, 31, 2)
         SET @tpid = @tpid + SUBSTRING(@charid, 29, 2)
         SET @tpid = @tpid + SUBSTRING(@charid, 27, 2)
         SET @tpid = @tpid + SUBSTRING(@charid, 25, 2)
         SET @uid = CAST(@tpid AS UNIQUEIDENTIFIER)
         RETURN @uid
END

¡Y Ahora sí, con estas funciones, podrán hacer queries entre la Bases de estadísticas y la Metadata! Por ejemplo, a continuación, convertimos el Project id de la Metadata en el GUID de la base de estadísticas. ¿se animan a hacerlo a la inversa?

select  MD.PROJECT_ID,
        EM.IS_PROJ_GUID,
        EM.IS_PROJ_NAME 
from METADATA.dbo.Dssmdobjinfo MD, Statistics.stats.IS_proj EM
where METADATA.dbo.fn_UniqueidentifierToCharMSTR (MD.PROJECT_ID) = EM.IS_PROJ_GUID;

MicroStrategy-Metadata-Queries-4

Top Metadata Queries

A medida que avanzan las versiones de MicroStrategy, la Metadata se va actualizando, y nuevas tablas van apareciendo o se van modificando por lo que, si alguna de las siguientes queries no resulta bien, quizás es cuestión de cambiar el nombre de un campo o una tabla (¡un buen ejercicio para familiarizarse con la creación de consultas!) 

Aquí está un listado de las queries más útiles o las que utilizo con más frecuencia:

select  o.project_id,
        o.OBJECT_TYPE type_code
,
        case o.object_type 
                when 1 then ‘filter’
                when 2 then ‘template’ 
                when 3 then ‘report’
                when 4 then ‘metric’
                when 6 then ‘autostyle’
                when 8 then ‘folder’
                when 10 then ‘prompt’
                when 11 then ‘function’
                when 12 then ‘attribute’
                when 13 then ‘fact’
                when 14 then ‘hierarchy’
                when 15 then ‘table’
                when 21 then ‘attribute ID’
                when 22 then ‘schema’
                when 24 then ‘warehouse catalog’
                when 25 then ‘warehouse catalog definition’
                when 26 then ‘table column’
                when 28 then ‘property sets’
                when 34 then ‘users/groups’
                when 39 then ‘search’
                when 42 then ‘package’
                when 47 then ‘consolidation’
                when 52 then ‘link’
                when 53 then ‘table’
                when 56 then ‘drill map’
                when 58 then ‘security filter’
                else ‘Other’

        end AS type
        o.object_name,
        o.object_id
from dssmdobjinfo o
order by type

  • Descripción del ACL: Verán que si algún dia quieren extraer la seguridad de los distintos objetos, verán que hay unos codigos que poco describen el ACL de cada objeto. Es por esto que es útil la siguiente query. Esta nos brinda un código un poco mas descriptivo de que permisos tiene cada objeto. Como se imaginarán, los custom indican, utilizando las iniciales, los permisos que tiene basándose en los siguientes:
MicroStrategy-Metadata-Queries-5

Por tanto, si dice Custom E1U0C0D0W0R0 es que podrá hacer Execute, pero no el resto. Por último, el ?0BX indica si es un permiso heredado por defecto (B0) o asignado manualmente (B1).

Para entender un poco mas, ¡no se olviden de pasar por el artículo de seguridad en MicroStrategy!

select  DISTINCT RIGHTS,
        CASE RIGHTS
                WHEN 1 THEN » — b00000001
                WHEN 5 THEN ‘Custom E0U0C0D0W0R1?1B1’ — b00000011
                WHEN 13 THEN » — b00001101
                WHEN 128 THEN ‘Custom E1U0C0D0W0R0?0B0’ — b10000000
                WHEN 132 THEN » — b10000100
                WHEN 133 THEN » — b10000101
                WHEN 198 THEN ‘Custom E1U1C0D0W0R1?1B0’ — b11000110
                WHEN 199 THEN ‘View’ — b11000111
                WHEN 207 THEN ‘Custom E1U1C0D0W1R1?1B1’ — b11001111
                WHEN 223 THEN ‘Modify’ — b11011111
                WHEN 239 THEN » — b11101111
                WHEN 251 THEN » — b11111011
                WHEN 255 THEN ‘Full Control’ — b11111111
                WHEN 268435504 THEN ‘Custom E1U1C0D0W1R1?1B1’ — B010000000000000000000000110000 ?? 0==grant
                WHEN 268435505 THEN ‘Custom E1U1C0D0W1R1?1B0’ — B010000000000000000000000110001 ?? 0==default
                WHEN 268435512 THEN ‘Custom E1U1C0D0W0R1?1B1’ — B010000000000000000000000111000
                WHEN 268435513 THEN ‘Custom E1U1C0D0W0R1?1B0’ — B010000000000000000000000111001
                WHEN 268435579 THEN ‘Custom E1U0C0D0W0R1?0B0’ — B010000000000000000000001111011
                WHEN 268435583 THEN ‘Custom E1U0C0D0W0R0?0B0’ — B010000000000000000000001111111
                WHEN 268435711 THEN ‘Denied All’ — B010000000000000000000011111111
                WHEN 536870913 THEN ‘Child Custom E0U0C0D0W0R0?0B1’ — B100000000000000000000000000001
                WHEN 536870917 THEN ‘Child Custom E0U0C0D0W0R1?0B1’ — B100000000000000000000000000101
                WHEN 536871110 THEN ‘Child Custom E1U1C0D0W0R1?1B0’ — B100000000000000000000011000110
                WHEN 536871111 THEN ‘Child View’ — B100000000000000000000011000111
                WHEN 536871119 THEN ‘Child Custom E1U1C0D0W1R1?1B1’ — B100000000000000000000011001111
                WHEN 536871135 THEN ‘Child Modify’ — B100000000000000000000011011111
                WHEN 536871167 THEN ‘Child Full Control’ — B100000000000000000000011111111
                WHEN 805306369 THEN ‘Child Custom E1U1C1D1W1R1?1B0’ — B110000000000000000000000000001
                WHEN 805306400 THEN ‘Child Custom E1U1C0D1W1R1?1B1’ — B110000000000000000000000100000
                WHEN 805306416 THEN ‘Child View’ — B110000000000000000000000110000
                WHEN 805306424 THEN ‘Child Custom E1U1C0D0W0R1?1B1’ — B110000000000000000000000111000
                WHEN 805306425 THEN ‘Child Custom E1U1C0D0W0R1?1B0’ — B110000000000000000000000111001
                WHEN 805306623 THEN ‘Child Denied All’ — B110000000000000000000011111111
                WHEN 1610612736 THEN » — B1100000000000000000000000000000
                ELSE ‘Unknown’
        END DESCRIPTION
from dssmdobjsecu

  • Listar todos los objetos: Esta query va en combinación con la que vimos en el primer paso. Tomando de base la primera, donde vimos que cada objeto tiene su tipo. Con esta query podremos extraer todos los objetos que tenemos en un proyecto:

WITH Parent AS (SELECT        OBJECT_ID, PROJECT_ID AS project_id, PARENT_ID AS Parent_ID, OBJECT_TYPE AS object_type, CAST(OBJECT_NAME AS varchar(MAX)) AS OBJECT_NAME

             FROM            dbo.DSSMDOBJINFO

             WHERE        (PARENT_ID = ‘00000000-0000-0000-0000-000000000000’)  and object_name =’MicroStrategy Tutorial’

             UNION all

             SELECT        TH.OBJECT_ID, TH.PROJECT_ID, TH.PARENT_ID, TH.OBJECT_TYPE, CONVERT(varchar(MAX), Parent_2.OBJECT_NAME + ‘/’ + TH.OBJECT_NAME) AS Path

             FROM            dbo.DSSMDOBJINFO AS TH INNER JOIN

                                      Parent AS Parent_2 ON Parent_2.OBJECT_ID = TH.PARENT_ID AND Parent_2.project_id = TH.PROJECT_ID)

SELECT  distinct    d.OBJECT_NAME AS Project_name, b.OBJECT_NAME, b.OBJECT_TYPE,

      CASE WHEN b.object_type = – 1 THEN ‘Unknown’ WHEN b.object_type = 0 THEN ‘Reserved’ WHEN b.object_type = 1 THEN ‘Filter’ WHEN b.object_type = 2 THEN ‘Template’ WHEN b.object_type = 3 THEN ‘ReportDefinition’

       WHEN b.object_type = 4 THEN ‘Metric’ WHEN b.object_type = 5 THEN ‘Unused’ WHEN b.object_type = 6 THEN ‘Autostyles’ WHEN b.object_type = 7 THEN ‘AggMetric’ WHEN b.object_type = 8 THEN ‘Folder’ WHEN
       b.object_type = 9 THEN ‘Unused’ WHEN b.object_type = 10 THEN ‘Prompt’ WHEN b.object_type = 11 THEN ‘Function’ WHEN b.object_type = 12 THEN ‘Attribute’ WHEN b.object_type = 13 THEN ‘Fact’ WHEN b.object_type
       = 14 THEN ‘Dimension’ WHEN b.object_type = 15 THEN ‘Table’ WHEN b.object_type = 16 THEN ‘Unused’ WHEN b.object_type = 17 THEN ‘FactGroup’ WHEN b.object_type = 18 THEN ‘Shortcut’ WHEN b.object_type
       = 19 THEN ‘Resolution’ WHEN b.object_type = 20 THEN ‘Unused’ WHEN b.object_type = 21 THEN ‘AttributeForm’ WHEN b.object_type = 22 THEN ‘Schema’ WHEN b.object_type = 23 THEN ‘FindObject’ WHEN b.object_type
       = 24 THEN ‘Catalog’ WHEN b.object_type = 25 THEN ‘CatalogDefn’ WHEN b.object_type = 26 THEN ‘Column’ WHEN b.object_type = 27 THEN ‘PropertyGroup’ WHEN b.object_type = 28 THEN ‘PropertySet’ WHEN
       b.object_type = 29 THEN ‘DBRole’ WHEN b.object_type = 30 THEN ‘DBLogin’ WHEN b.object_type = 31 THEN ‘DBConnection’ WHEN b.object_type = 32 THEN ‘Project’ WHEN b.object_type = 33 THEN ‘ServerDef’
       WHEN b.object_type = 34 THEN ‘User’ WHEN b.object_type = 35 THEN ‘Unused’ WHEN b.object_type = 36 THEN ‘Configuration’ WHEN b.object_type = 37 THEN ‘Request’ WHEN b.object_type = 38 THEN ‘Unused’
       WHEN b.object_type = 39 THEN ‘Search’ WHEN b.object_type = 40 THEN ‘SearchFolder’ WHEN b.object_type = 41 THEN ‘Unused’ WHEN b.object_type = 42 THEN ‘FunctionPackageDefinition’ WHEN b.object_type
       = 43 THEN ‘Role’ WHEN b.object_type = 44 THEN ‘SecurityRole’ WHEN b.object_type = 45 THEN ‘InBox’ WHEN b.object_type = 46 THEN ‘InBoxMsg’ WHEN b.object_type = 47 THEN ‘Consolidation’ WHEN b.object_type
       = 48 THEN ‘ConsolidationElement’ WHEN b.object_type = 49 THEN ‘ScheduleEvent’ WHEN b.object_type = 50 THEN ‘ScheduleObject’ WHEN b.object_type = 51 THEN ‘ScheduleTrigger’ WHEN b.object_type = 52
       THEN ‘Link’ WHEN b.object_type = 53 THEN ‘DBTable’ WHEN b.object_type = 54 THEN ‘TableSource’ WHEN b.object_type = 55 THEN ‘DocumentDefinition’ WHEN b.object_type = 56 THEN ‘DrillMap’ WHEN b.object_type
       = 57 THEN ‘DBMS’ WHEN b.object_type = 58 THEN ‘MDSecurityFilter’ WHEN b.object_type = 59 THEN ‘PromptAnswer’ WHEN b.object_type = 60 THEN ‘PromptAnswers’ WHEN b.object_type = 61 THEN ‘GraphStyle’
       WHEN b.object_type = 62 THEN ‘ChangeJournalSearch’ WHEN b.object_type = 63 THEN ‘Blob’ WHEN b.object_type = 64 THEN ‘DashboardTemplate’ WHEN b.object_type = 65 THEN ‘Flag’ WHEN b.object_type
       = 66 THEN ‘ChangeJournal’ WHEN b.object_type = 67 THEN ‘ExternalShortcut’ WHEN b.object_type = 68 THEN ‘ExternalShortcutTarget’ WHEN b.object_type = 69 THEN ‘Reconciliation’ WHEN b.object_type = 70
       THEN ‘Layer’ WHEN b.object_type = 71 THEN ‘Palette’ WHEN b.object_type = 72 THEN ‘Thresholds’ WHEN b.object_type = 73 THEN ‘PersonalView’ WHEN b.object_type = 74 THEN ‘FeatureFlag’ ELSE ‘Unknown’
       END AS object_type_desc, Parent_1.OBJECT_NAME AS Name_with_path, REPLACE(Parent_1.OBJECT_NAME, b.OBJECT_NAME, ‘’)
      AS Path_name, b.HIDDEN, dbo.fn_UniqueIdentifierToCharMSTR(a.OBJECT_ID) as OBJECT_ID
FROM            dbo.DSSMDOBJSECU AS a INNER JOIN
      dbo.DSSMDOBJINFO AS b ON a.OBJECT_ID = b.OBJECT_ID AND a.PROJECT_ID = b.PROJECT_ID  INNER JOIN
      dbo.DSSMDOBJINFO AS c ON a.TRUST_ID = c.OBJECT_ID INNER JOIN
      dbo.DSSMDOBJINFO AS d ON a.PROJECT_ID = d.OBJECT_ID INNER JOIN
      Parent AS Parent_1 ON a.OBJECT_ID = Parent_1.OBJECT_ID AND a.PROJECT_ID = Parent_1.project_id

Conclusiones

¡Y así como los casos que estuve describiendo, las posibilidades son infinitas! Y por supuesto, puede ser una buena idea crear vistas con las consultas, o crear nuevos objetos en nuestro modelo que nos permita jugar y navegar con las distintas consultas o aquellos campos que vayamos personalizando. 

Si bien es posible utilizar command manager para obtener de información, es mucho mas customizable hacerlo con una query.

Ahora les toca a ustedes, ¿conocían estas consultas? ¿Hay alguna que quieran recomendar? ¿Alguna vez habían hecho consultas o navegado por la Metadata?  

Hasta aquí llega el artículo de esta semana. Como siempre, espero que esto les resulte de utilidad, quizás aprender algo nuevo, ¡y los invito a dejar comentarios o temas de los que les gustaría que escriba!

Hasta el próximo #BestInMicro!

Referencias

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *