Analysis Services - Consultando as views de catálogo pelo SQL Server

Olá galera,
Como vocês estão?

Neste post, gostaria de escrever sobre como consultas as views de catálogo do Analysis Services, seja com consultas pelo SQL Server ou utilizando a interface de consultas MDX ou DMX do SQL Server Management Studio.

O meu intuito neste post é apenas demonstrar as views de catálogo (DMV) do SQL Server Analysis Services (SSAS). Caso você queira entender como eu faço para realizar essas consultas pelo SQL Server utilizando o Linked Server “INTERFACE_CUBOS”, como se fossem consultas normais à base de dados, o que eu acho mais prático, veja como no post Analysis Services (SSAS) – Como consultar informações e processar comandos via linha de comando (XLMA) pelo SQL Server.

O link completo das DMV’s do Analysis Services pode ser encontrada nesse link.

Exemplo de consulta no Management Studio > New DMX Query

Exemplo de consulta no Management Studio > New MDX Query

Exemplo de consulta no Management Studio > New Query (Transact-SQL)

Consultando informações das views de catálogo

Depois dessa pequena introdução, vamos agora começar as nossas consultas.

Recuperar informações de todos os cubos da instância

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 informações das dimensões de um 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 informações dos atributos de um 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 informações das medidas (measures) de um 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 informações das medidas (measures) calculadas utilizando expressão de um cubo

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 informações das dimensões e suas respectivas Measure Group

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

É isso aí, pessoal.
Espero que tenham gostado desse post, que foi apenas uma introdução ao uso de consultas à views do catálogo do Analysis Services.

Um abraço e até a próxima.