DMV Column Output Definitions and Options

[ACraig08]

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. 

[v-sgandrathi]

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_DEPENDENCY

This 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 Community

If 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.

[maruthisp]

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 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[ACraig08]

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.