Forum Discussion
DMV Column Output Definitions and Options
Hello, I am finding it impossible to find any documentation or help regarding column definitions and even column value options/definitions when running DMV functions. Just one example, I ran the
select * from $SYSTEM.TMSCHEMA_ANNOTATIONS and it outputs this:
It's hard to know what I am looking at and how to understand the output when I can't find any information on column definitions and even more important, the definitions of value options. Like with Name having different values like SummarizationSetBy, UnderlyingDateTimeDataType, PBI_FormatHint. I have no idea what those actually indicate. I mean I can make assumtions for some based on what they say but that doesn't really help.
Or with SELECT * FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY
What are the different options for OBJECT_TYPE and what do they mean.
If anybody knows where I can find this information, specifically, it would be SO helpful. I have tried looking at the [MS-SSAS-T] and [MS-SSAS] and downloaded all 32 pdf's they have from the SQLServerProtocols.zip. But I have no idea how they can help with something like this.
Hi ACraig08,
Thank you for the question
To assist you, I will clarify both the meaning of the columns and how to interpret the values presented.
$SYSTEM.TMSCHEMA_ANNOTATIONS
This DMV provides annotations, which are custom metadata linked to model objects. Below is an explanation of the columns and their significance:
Below are the Column and Descriptions:
ID --- Unique identifier for the annotation.
ObjectID --- ID of the object the annotation is attached to.
ObjectType --- Type of object (e.g., Column, Table, Measure).
Name --- The annotation's name, such as SummarizationSetBy or PBI_FormatHint.
Value --- The actual metadata value — often a string or JSON.
Examples of Name values:
SummarizationSetBy: Indicates how summarization was set (e.g., user or system).UnderlyingDateTimeDataType: Specifies the original data type (e.g., DateTime64).
PBI_FormatHint: Power BI-specific formatting hints.
$SYSTEM.DISCOVER_CALC_DEPENDENCYThis DMV showsdependencies between calculated objects (like measures, calculated columns).
Below are the Column and Descriptions:OBJECT_ID --- ID of the calculated object (e.g., a measure).
OBJECT_TYPE --- Type of the object (e.g., 1 = Table, 2 = Column, 3 = Measure, etc.).
REFERENCED_OBJECT_ID --- ID of the object it depends on.
REFERENCED_OBJECT_TYPE --- Type of the referenced object.
Common OBJECT_TYPE values:
1: Table
2: Column
3: Measure
4: Hierarchy
5: KPI
6: Relationship
7: Role
8: Calculation Group
9: Calculation Item
These values are not always documented in one place, but they are inferred from community knowledge and tools like DAX Studio or Tabular Editor.
You can refer to the official Microsoft documents below, which you may find helpful.
Dynamic Management Views (DMVs) in Analysis Services | Microsoft Learn
[MS-SSAS-T]: SQL Server Analysis Services Tabular Protocol | Microsoft Learn
Solved: Re: Querying DMV Data Types in DAX Studio - Microsoft Fabric CommunityIf my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!
Thank you,
Sahasra
Community Support Team.
6 Replies
Hi ACraig08 ,
Can you try any external tools to explore metadata like Tabular Editor, Power BI Helper and Bravo for Power BI.In DAX Studio, you can write queries to get metadat information. For example,
-- 1. Column Metadata
SELECT
[TableID],
[Name] AS ColumnName,
[TableName],
[ExplicitDataType],
[InferredDataType],
[IsHidden],
[IsKey],
[IsNullable]
FROM
$SYSTEM.TMSCHEMA_COLUMNS
ORDER BY
[TableName], [ColumnName];-- 2. Measure Metadata
SELECT
[Name] AS MeasureName,
[TableName],
[Expression],
[IsHidden]
FROM
$SYSTEM.TMSCHEMA_MEASURES
ORDER BY
[TableName], [MeasureName];-- 3. Relationship Metadata
SELECT
[FromTableID],
[FromColumnID],
[FromTable],
[FromColumn],
[ToTableID],
[ToColumnID],
[ToTable],
[ToColumn],
[IsActive],
[CrossFilteringBehavior]
FROM
$SYSTEM.TMSCHEMA_RELATIONSHIPS
ORDER BY
[FromTable], [ToTable];Please let me know if there is any questions..
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
Thank you for your response. Unfortunetly none of that actually provides me with column definitions and what the provided values within those columns represent. I understand the explicit ones like Table or object name when they are specifically representing a table or column in my schemas, and I understand that the DMV is providing me with that metadata. Unfortunetly none of those helpers actually explain anything different than DAX Studio because it is all just the same information just provided in a different format. I really need help understanding the output.
Hi ACraig08,
Thank you for the question
To assist you, I will clarify both the meaning of the columns and how to interpret the values presented.
$SYSTEM.TMSCHEMA_ANNOTATIONS
This DMV provides annotations, which are custom metadata linked to model objects. Below is an explanation of the columns and their significance:
Below are the Column and Descriptions:
ID --- Unique identifier for the annotation.
ObjectID --- ID of the object the annotation is attached to.
ObjectType --- Type of object (e.g., Column, Table, Measure).
Name --- The annotation's name, such as SummarizationSetBy or PBI_FormatHint.
Value --- The actual metadata value — often a string or JSON.
Examples of Name values:
SummarizationSetBy: Indicates how summarization was set (e.g., user or system).UnderlyingDateTimeDataType: Specifies the original data type (e.g., DateTime64).
PBI_FormatHint: Power BI-specific formatting hints.
$SYSTEM.DISCOVER_CALC_DEPENDENCYThis DMV showsdependencies between calculated objects (like measures, calculated columns).
Below are the Column and Descriptions:OBJECT_ID --- ID of the calculated object (e.g., a measure).
OBJECT_TYPE --- Type of the object (e.g., 1 = Table, 2 = Column, 3 = Measure, etc.).
REFERENCED_OBJECT_ID --- ID of the object it depends on.
REFERENCED_OBJECT_TYPE --- Type of the referenced object.
Common OBJECT_TYPE values:
1: Table
2: Column
3: Measure
4: Hierarchy
5: KPI
6: Relationship
7: Role
8: Calculation Group
9: Calculation Item
These values are not always documented in one place, but they are inferred from community knowledge and tools like DAX Studio or Tabular Editor.
You can refer to the official Microsoft documents below, which you may find helpful.
Dynamic Management Views (DMVs) in Analysis Services | Microsoft Learn
[MS-SSAS-T]: SQL Server Analysis Services Tabular Protocol | Microsoft Learn
Solved: Re: Querying DMV Data Types in DAX Studio - Microsoft Fabric CommunityIf my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!
Thank you,
Sahasra
Community Support Team.
Hi ACraig08,
We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.
If you need any further assistance, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!
HI ACraig08,
Just wanted to check regarding your question. We haven’t heard back and want to ensure you're not stuck. If you need anything else or have updates to share, we’re here to help!
Thank you.
