Hi guys,
How are you?
In this post, I would like to write about querying Analysis Services catalog views, either with queries through SQL Server or using the SQL Server Management Studio 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 do these queries by SQL Server using Linked Server “INTERFACE_CUBOS”, as if they were normal queries to the database, which I think is more practical, see how in the post Analysis Services (SSAS) - How to query information and process command line (XLMA) commands through SQL Server.
The full link to the Analysis Services DMV's can be found in this link.
Management Studio Query Example> New DMX Query
Management Studio Query Example> New MDX Query
Query Example in Management Studio> New Query (Transact-SQL)
Querying Catalog View Information
After this short introduction, let's now start our queries.
Retrieve information from all instance cubes
|
1 2 3 4 5 6 7 8 9 10 11 |
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 cube dimension information
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 cube information
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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 calculated measures information using cube expression
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 dimension information and its Measure Group
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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, guys.
I hope you enjoyed this post, which was just an introduction to using Analysis Services catalog view queries.
A hug and see you next.
