Hello guys,
How are you?

In this post, I would like to write about how to query Analysis Services catalog views, either with SQL Server queries or using SQL Server Management Studio's MDX or DMX query interface.

My intention in this post is just to demonstrate the catalog views (DMV) of SQL Server Analysis Services (SSAS). If you want to understand how I can perform these queries through SQL Server using the Linked Server “INTERFACE_CUBOS”, as if they were normal database queries, which I think is more practical, see how in the post Analysis Services (SSAS) – How to query information and process commands via command line (XLMA) through SQL Server.

The complete link to the Analysis Services DMV’s can be found in this link.

Query example in Management Studio > New DMX Query

Query example in Management Studio > New MDX Query

Query example in Management Studio > New Query (Transact-SQL)

After this short introduction, we will now begin our consultations.

Retrieve information from all cubes in the instance

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

Retrieve information about the dimensions of a cube

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

Retrieve attribute information from a cube

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

Retrieve measurement information from a cube

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

Retrieve information from measures calculated using a cube expression

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

Retrieve information about dimensions and their respective 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

That's it, folks.
I hope you enjoyed this post, which was just an introduction to using queries for Analysis Services catalog views.

A hug and see you next time.