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

Visualizações: 1.243 views

Neste artigo

Tempo de Leitura: 3 minutos

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')

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]
')

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]
')

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

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

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

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.

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

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

Consultando informações das views de catálogo

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

Recuperar informações das dimensões de um cubo

Recuperar informações dos atributos de um cubo

Recuperar informações das medidas (measures) de um cubo

Recuperar informações das medidas (measures) calculadas utilizando expressão de um cubo

Recuperar informações das dimensões e suas respectivas Measure Group

You may also like...

Deixe uma respostaCancelar resposta

Consultando informações das views de catálogo

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

Recuperar informações das dimensões de um cubo

Recuperar informações dos atributos de um cubo

Recuperar informações das medidas (measures) de um cubo

Recuperar informações das medidas (measures) calculadas utilizando expressão de um cubo

Recuperar informações das dimensões e suas respectivas Measure Group

You may also like...

Como eliminar todas as conexões de um database no SQL Server

Como validar inscrição estadual usando função T-SQL no SQL Server

SQL Server – Como gerar um script com todos os índices do banco de dados

Deixe uma respostaCancelar resposta