Dynamic Management Views (DMVs) are special system views that expose internal server state for monitoring and troubleshooting. You can join DMVs together, but there are some limitations depending on the context:
Here’s a breakdown of what Analysis Services DMVs are and how they're useful:
Understanding and executing queries on DMVs can greatly enhance your ability to manage your data effectively and improve your data model's performance.
To query Dynamic Management Views (DMVs) in DAX Studio, you can follow these steps:
sp_configure command as outlined in the course.Zde jsou nejčastější dotazy na DMV:
-- Query to retrieve all tables in a model
select * from $SYSTEM.TMSCHEMA_TABLES
-- Query to get all columns (note the SortByColumnId column)
Select * From $SYSTEM.TMSCHEMA_COLUMNS
-- Query to get all calculated columns
Select * From $SYSTEM.TMSCHEMA_COLUMNS Where [Type] = 2
-- Query to get all measures
select * from $SYSTEM.TMSCHEMA_MEASURES
-- Query to get all dependencies
select * from $system.discover_calc_dependency
-- Get unique row counts for all tables and columns
select * from $SYSTEM.DISCOVER_STORAGE_TABLES order by rows_count desc
-- Query to get all the roles, associated permissions and role memberships defined in the model
select * from $SYSTEM.TMSCHEMA_Roles
select * from $SYSTEM.TMSCHEMA_TABLE_PERMISSIONS
select * from $SYSTEM.TMSCHEMA_Role_Memberships
-- Query to get all the KPIs defined in the model
select * from $SYSTEM.TMSCHEMA_KPIS
-- Query to get session information
select * from $SYSTEM.DISCOVER_SESSIONS
-- Query to get all relationships
select * from $SYSTEM.TMSCHEMA_RELATIONSHIPS
-- Queries to get hierarchy information
select * from $SYSTEM.TMSCHEMA_ATTRIBUTE_HIERARCHIES
select * from $SYSTEM.TMSCHEMA_ATTRIBUTE_HIERARCHY_STORAGES
-- Query to get information about each model:
select * from $SYSTEM.TMSCHEMA_MODEL
-- Query to get information about each partition:
select * from $SYSTEM.TMSCHEMA_PARTITIONS
-- Query to get perspective information:
select * from $SYSTEM.TMSCHEMA_PERSPECTIVES
-- Query to get catalog information (especially compatibility level):
select * from $SYSTEM.DBSCHEMA_CATALOGS
To use Dynamic Management Views (DMVs) in SQL Server Management Studio (SSMS), follow these steps:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
This approach allows you to use JOIN to connect, for example, a view with columns and a view with tables:
------------------------------------------------------------------------------------------------------
-- This query establishes a connection to the SSAS server represented in the OPENROWSET connection
-- string field and the catalog references in the connection string. It then pulls table and column
-- information
------------------------------------------------------------------------------------------------------
-- It may be necessary to execute these statements first.
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
-- Query the table and column data in the model or models.
;WITH [Tables] AS
(
SELECT
ID as TableId
,ModelId
,[Name] as TableName
,DataCategory
,[Description] as Description
,IsHidden -- Is the table treated as hidden by a client visualization tool
,ModifiedTime -- The time the table was last modified
FROM OPENROWSET('MSOLAP','DATASOURCE= localhost:60921; Initial Catalog=4f655bd0-716b-4bf6-9424-727ec45e4b47;','SELECT * FROM $SYSTEM.TMSCHEMA_TABLES')
)
,[Columns] AS
(
SELECT
ID as Id
,TableId
,ExplicitName as ColumnName
,InferredName -- Engine generated name (Calculated columns only)
,CASE
When ExplicitDataType = 1 Then 'Automatic'
When ExplicitDataType = 2 Then 'String'
When ExplicitDataType = 6 Then 'Int64'
When ExplicitDataType = 8 Then 'Double'
When ExplicitDataType = 9 Then 'DateTime'
When ExplicitDataType = 10 Then 'Decimal'
When ExplicitDataType = 11 Then 'Boolean'
When ExplicitDataType = 17 Then 'Binary'
When ExplicitDataType = 19 Then 'Unknown'
Else 'N/A'
END as DataType
,DataCategory
,Description
,IsHidden -- Treated as hidden by a client visualization tool?
,IsUnique -- Can the column contain duplicate values?
,IsKey -- Is the column a key of the table?
,IsNullable -- Can the column contain null values?
,CASE
WHEN T.Type = 1 Then 'From data source'
WHEN T.Type = 2 Then 'Calculated'
WHEN T.Type = 3 Then 'Row number'
ELSE 'N/A'
END as ColumnType
,SourceColumn -- Source column name
,Expression -- The calculated column DAX expression
,FormatString -- String controlling the formatting of the column
,SortByColumnId -- Specifies the column that is controlling the sorting of this column
,AttributeHierarchyId -- A reference to an AttributeHierarchy object
,ModifiedTime -- The time the column was last modified
,ErrorMessage -- A string explaining the error state of the column
FROM OPENROWSET('MSOLAP','DATASOURCE= localhost:60921; Initial Catalog=4f655bd0-716b-4bf6-9424-727ec45e4b47;','SELECT * FROM $SYSTEM.TMSCHEMA_COLUMNS') T
)
Select
T.TableName,
T.ModelId,
C.*,
ISNULL(C2.ColumnName,'') as SortByColumnName
From Tables T
Inner Join Columns C ON T.TableId = C.TableId
Left Join Columns C2 ON C2.Id = C.SortByColumnId
Of course, when selecting from OPENROWSET, you must replace DATASOURCE for your model.