Analysis Services - Consultando las vistas de catálogo desde SQL Server

Hola, chicos,
¿Cómo estás?

En esta publicación, me gustaría escribir sobre cómo consultar las vistas del catálogo de Analysis Services, ya sea con consultas de SQL Server o utilizando la interfaz de consulta MDX o DMX de SQL Server Management Studio.

Mi intención en esta publicación es simplemente demostrar las vistas de catálogo (DMV) de SQL Server Analysis Services (SSAS). Si quieres entender cómo puedo realizar estas consultas a través de SQL Server usando el Servidor Enlazado “INTERFACE_CUBOS”, como si fueran consultas normales a bases de datos, lo cual creo que es más práctico, mira cómo en el post Analysis Services (SSAS): cómo consultar información y procesar comandos a través de la línea de comandos (XLMA) a través de SQL Server.

Puede encontrar el enlace completo al DMV de Analysis Services en este enlace.

Ejemplo de consulta en Management Studio > Nueva consulta DMX

Ejemplo de consulta en Management Studio > Nueva consulta MDX

Ejemplo de consulta en Management Studio > Nueva consulta (Transact-SQL)

Consultar información desde vistas de catálogo

Después de esta breve introducción, ahora comenzaremos nuestras consultas.

Recuperar información de todos los cubos de la instancia.

SELECT 
    [CATALOG_NAME],
    [CUBE_NAME],
    [LAST_SCHEMA_UPDATE],
    [LAST_DATA_UPDATE],
    [IS_DRILLTHROUGH_ENABLED],
    [IS_LINKABLE],
    [IS_WRITE_ENABLED],
    [IS_SQL_ENABLED]
FROM 
    OPENQUERY(INTERFACE_CUBOS, 'SELECT * FROM $system.MDSchema_Cubes WHERE [CUBE_SOURCE] = 1')

Recuperar información sobre las dimensiones de un cubo.

SELECT 
    [CATALOG_NAME],
    [CUBE_NAME],
    [DIMENSION_NAME] AS [DIMENSION_ID],
    [DIMENSION_MASTER_NAME] AS [DIMENSION_NAME],
    [DIMENSION_CAPTION],
    [DIMENSION_UNIQUE_NAME],
    [DEFAULT_HIERARCHY],
    [DIMENSION_ORDINAL],
    [DIMENSION_IS_VISIBLE],
    [IS_VIRTUAL],
    [IS_READWRITE]
FROM 
    OPENQUERY(INTERFACE_CUBOS, '
SELECT * 
FROM $system.MDSchema_Dimensions 
WHERE [CATALOG_NAME] = ''Cubo de Vendas''
AND [DIMENSION_CAPTION] <> ''Measures''
AND LEFT([CUBE_NAME], 1) <> ''$''
ORDER BY [DIMENSION_CAPTION]
')

Recuperar información de atributos de un cubo

SELECT 
    *
FROM 
    OPENQUERY(INTERFACE_CLR_ANALYSIS_SERVICES, '
SELECT 
    [CATALOG_NAME],
    [CUBE_NAME],
    [DIMENSION_UNIQUE_NAME] AS [DIMENSION],
    [HIERARCHY_DISPLAY_FOLDER] AS [FOLDER],
    [HIERARCHY_CAPTION] AS [DIMENSION ATTRIBUTE],
    [HIERARCHY_IS_VISIBLE] AS [VISIBLE]
FROM 
    $system.MDSchema_hierarchies
WHERE
    HIERARCHY_ORIGIN = 2
ORDER BY 
    [DIMENSION_UNIQUE_NAME]
')

Recuperar información de medidas de un cubo

SELECT
    [CATALOG_NAME],
    [CUBE_NAME],
    [MEASURE_NAME],
    [MEASURE_CAPTION],
    [MEASURE_UNIQUE_NAME],
    [EXPRESSION],
    [MEASUREGROUP_NAME],
    B.[TYPE_NAME],
    B.[COLUMN_SIZE],
    (CASE [MEASURE_AGGREGATOR]
        WHEN 1 THEN 'Sum'
        WHEN 2 THEN 'Count'
        WHEN 3 THEN 'Min'
        WHEN 4 THEN 'Max'
        WHEN 8 THEN 'Distinct Count'
        WHEN 9 THEN 'None'
        WHEN 10 THEN 'AverageOfChildren'
        WHEN 11 THEN 'FirstChild'
        WHEN 12 THEN 'LastChild'
        WHEN 13 THEN 'FirstNonEmpty'
        WHEN 14 THEN 'LastNonEmpty'
        WHEN 15 THEN 'ByAccount'
        WHEN 127 THEN 'Calculated measure'
        ELSE 'N/A'
    END) AS [MEASURE_AGGREGATOR],
    [NUMERIC_PRECISION],
    [NUMERIC_SCALE],
    [DEFAULT_FORMAT_STRING] 
FROM OPENQUERY(INTERFACE_CUBOS, '
SELECT
    *
FROM 
    $SYSTEM.MDSCHEMA_MEASURES
WHERE 
    LEFT([CUBE_NAME], 1) <> ''$''
ORDER 
    BY [MEASUREGROUP_NAME]
') A
JOIN OPENQUERY(INTERFACE_CUBOS, '
SELECT 
    DATA_TYPE, 
    TYPE_NAME, 
    COLUMN_SIZE, 
    IS_FIXEDLENGTH
FROM 
    $SYSTEM.DBSCHEMA_PROVIDER_TYPES
') B ON A.DATA_TYPE = B.DATA_TYPE

Recuperar información de medidas calculadas usando una expresión cúbica

SELECT *
FROM OPENQUERY(INTERFACE_CLR_ANALYSIS_SERVICES, '
SELECT 
    [CATALOG_NAME],
    [CUBE_NAME],
    [MEMBER_UNIQUE_NAME] AS [CALCULATED_MEASURE],
    [MEMBER_CAPTION] AS [CAPTION],
    [EXPRESSION]
FROM 
    $system.MDSCHEMA_MEMBERS
WHERE 
    [MEMBER_TYPE] = 4
') A

Recuperar información sobre dimensiones y su respectivo grupo de medida.

SELECT *
FROM OPENQUERY(INTERFACE_CUBOS, '
SELECT  
    [CATALOG_NAME],
    [CUBE_NAME],
    [MEASUREGROUP_NAME] AS [MEASUREGROUP],
    [MEASUREGROUP_CARDINALITY],
    [DIMENSION_UNIQUE_NAME],
    [DIMENSION_GRANULARITY],
    [DIMENSION_CARDINALITY],
    [DIMENSION_IS_VISIBLE],
    [DIMENSION_IS_FACT_DIMENSION]
FROM 
    $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE
    LEFT([CUBE_NAME], 1) <> ''$''
') A

Eso es todo, amigos.
Espero que haya disfrutado de esta publicación, que fue solo una introducción al uso de consultas para vistas de catálogo de Analysis Services.

Un abrazo y hasta la próxima.