Power Pivot Principles: The A to Z of DAX Functions – INFO.COLUMNS
2 July 2024
In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at INFO.COLUMNS.
The INFO.COLUMNS function
Dynamic Management Views (DMVs) are specialised queries provided by SQL Server Analysis Services (SSAS), Azure Analysis Services (AAS), and Power BI that offer an administrative view into the internal state of these systems. DMVs are used to retrieve metadata, monitor health and performance, and diagnose problems within the database or data model. They serve as a powerful tool for administrators and developers to gain insights into the workings of the database engine and the tabular data model, covering aspects like performance metrics, configuration settings, and the structure of database objects.
The $System schema DMVs in SQL Server Analysis Services (SSAS), Azure Analysis Services (AAS), and Power BI are categorised into four [4] types, each serving specific purposes:
- DISCOVER: requires admin privileges and provides information about the model, including details on connected sessions and environment configuration
- DMSCHEMA: focused on data mining, offering insights for predictive analytics and pattern recognition, mainly used in SSAS/AAS
- MDSCHEMA: targets multidimensional models, delivering metadata and structure from an MDX perspective, relevant for OLAP cubes and dimensions
- TMSCHEMA: designed for tabular models, it provides detailed metadata about tables, columns, measures, etc., using Tabular Model Scripting Language (TMSL) information, crucial for Power BI and tabular SSAS/AAS models.
In the past, if we wanted to query those $System schema DMVs, we used external tools like Tabular Editors or DAX Studio to query them:
Now, we just need to write a simple DAX syntax to query those TMSCHEMA directly in DAX query View. In this instance, we are using INFO.COLUMNS function to query the TMSCHEMA_COLUMNS.
The INFO.COLUMNS function is known as one of the system functions. It employs the following syntax:
INFO.COLUMNS()
i.e. this function has no parameters.
Based upon the ‘[MS-SSAS-T]: SQL Server Analysis Services Tabular Protocol’ from Microsoft (which you may access here),the Column object represents a column in a Table. It is a child of a Table object. Each column has a number of properties defined on it that influence how client applications visualise the data in the column.
We can write this INFO.COLUMNS function on DAX query view to get the same information on the TMSCHEMA_COLUMNS:
It will query $SYSTEM.TMSCHEMA_COLUMNS and return an entire table with 41 columns:
- ID: this represents a reference to the object
- TableID: this represents an ID-based reference to a Table object
- ExplicitName: this represents the user-specified name for the column. This element must be specified for calculated columns and columns that are bound to data. If a column in a calculated table leaves this unspecified, the name is inferred from the expression
- InferredName: this specifies the engine-generated name for the column. It is valid only for columns of type CalculatedTableColumn
- ExplicitDataType: this is the user-specified data type to be enforced on
the contents of the column. The possible values are as follows:
- Automatic (1) – when calculated columns or calculated table columns set the value to Automatic, the type is automatically inferred
- String (2)
- Int64 (6)
- Double (8)
- DateTime (9)
- Decimal (10)
- Boolean (11)
- Binary (17)
- Unknown (19) – this value cannot be set on the ExplicitDataType field. It is set automatically by the engine on the InferredDataType field of a calculated column that is in a semantic error state
- InferredDataType: this specifies the engine-generated data type for this column. It is valid only for columns of the type CalculatedTableColumn or Calculated
- DataCategory: values in the following enumeration PropertyType are
automatically mapped to the LEVEL_TYPE column. All other strings map to ExtendedType (248) and are returned as-is in the Contents property of ‘conceptual schema
definition language’ (CSDL) for the column:
- Invalid (-1)
- All (1)
- Regular (2)
- Image (3)
- ImageBMP (4)
- ImageGIF (5)
- ImageJPG (6)
- ImagePNG (7)
- ImageTIFF (8)
- ImageURL (9)
- Id (10)
- RelationToParent (11)
- Sequence (12)
- OrgTitle (13)
- Caption (14)
- ShortCaption (15)
- CaptionDescription (16)
- CaptionAbbreviation (17)
- WebURL (18)
- WebHTML (19)
- XMLOrXSL (20)
- WebmailAlias (21)
- Address (22)
- AddressStreet (23)
- AddressHouse (24)
- AddressCity (25)
- AddressStateOrProvince (26)
- AddressZIP (27)
- AddressQuarter (28)
- AddressCountry (29)
- AddressBuilding (30)
- AddressRoom (31)
- AddressFloor (32)
- AddressFax (33)
- AddressPhone (34)
- GeoCentroidX (35)
- GeoCentroidY (36)
- GeoCentroidZ (37)
- GeoBoundaryTop (38)
- GeoBoundaryLeft (39)
- GeoBoundaryBottom (40)
- GeoBoundaryRight (41)
- GeoBoundaryFront (42)
- GeoBoundaryRear (43)
- GeoBoundaryPolygon (44)
- PhysicalSize (45)
- PhysicalColor (46)
- PhysicalWeight (47)
- PhysicalHeight (48)
- PhysicalWidth (49)
- PhysicalDepth (50)
- PhysicalVolume (51)
- PhysicalDensity (52)
- PersonFullName (53)
- PersonFirstName (54)
- PersonLastName (55)
- PersonMiddleName (56)
- PersonDemographic (57)
- PersonContact (58)
- QtyRangeLow (59)
- QtyRangeHigh (60)
- FormattingColor (61)
- FormattingOrder (62)
- FormattingFont (63)
- FormattingFontEffects (64)
- FormattingFontSize (65)
- FormattingSubtotal (66)
- Date (67)
- DateStart (68)
- DateEnded (69)
- DateCanceled (70)
- DateModified (71)
- DateDuration (72)
- Version (73)
- Years (74)
- Quarters (75)
- Months (76)
- Weeks (77)
- Days (78)
- Hours (79)
- Minutes (80)
- Seconds (81)
- UndefinedTime (82)
- OrganizationalUnit (83)
- BomResource (84)
- Quantitative (85)
- Account (86)
- Customers (87)
- CustomerGroup (88)
- CustomerHousehold (89)
- Product (90)
- ProductGroup (91)
- Scenario (92)
- Utility (93)
- Person (94)
- Company (95)
- CurrencySource (96)
- CurrencyDestination (97)
- Channel (98)
- Representative (99)
- Promotion (100)
- Continent (101)
- Region (102)
- Country (103)
- StateOrProvince (104)
- County (105)
- City (106)
- PostalCode (107)
- Point (108)
- AccountType (109)
- AccountName (110)
- AccountNumber (111)
- ProjectName (112)
- ProjectCode (113)
- ProjectStartDate (114)
- ProjectEndDate (115)
- ProjectCompletion (116)
- CurrencyName (117)
- CurrencyIsOCode (118)
- PercentOwnership (119)
- PercentVoteright (120)
- Project (121)
- RateType (122)
- Rate (123)
- ProductSKU (124)
- ProductCategory (125)
- ProductBrand (126)
- DeletedFlag (127)
- ScdStatus (128)
- ScdEndDate (129)
- ScdOriginalID (130)
- ScdStartDate (131)
- DayOfMonthOrPeriod (132)
- WeekOfQuarter (133)
- WeekOfMonthOrPeriod (134)
- MonthOrPeriodOfQuarter (135)
- MonthOrPeriodOfYear (136)
- Trimesters (137)
- Halfyears (138)
- Tendays (139)
- DayOfWeek (140)
- DayOfTendays (141)
- DayOfMonth (142)
- DayOfQuarter (143)
- DayOfTrimester (144)
- DayOfHalfyear (145)
- DayOfYear (146)
- WeekOfYear (147)
- TendayOfMonth (148)
- TendayOfQuarter (149)
- TendayOfTrimester (150)
- TendayOfHalfyear (151)
- TendayOfYear (152)
- MonthOfTrimester (153)
- MonthOfQuarter (154)
- MonthOfHalfyear (155)
- MonthOfYear (156)
- TrimesterOfYear (157)
- QuarterOfHalfyear (158)
- QuarterOfYear (159)
- HalfyearOfYear (160)
- FiscalDate (161)
- FiscalDayOfWeek (162)
- FiscalDayOfMonth (163)
- FiscalDayOfQuarter (164)
- FiscalDayOfTrimester (165)
- FiscalDayOfHalfyear (166)
- FiscalDayOfYear (167)
- FiscalWeeks (168)
- FiscalWeekOfYear (169)
- FiscalWeekOfHalfyear (170)
- FiscalWeekOfQuarter (171)
- FiscalWeekOfTrimester (172)
- FiscalWeekOfMonth (173)
- FiscalMonths (174)
- FiscalMonthOfTrimester (175)
- FiscalMonthOfQuarter (176)
- FiscalMonthOfHalfyear (177)
- FiscalMonthOfYear (178)
- FiscalTrimesters (179)
- FiscalTrimesterOfYear (180)
- FiscalQuarters (181)
- FiscalQuarterOfYear (182)
- FiscalQuarterOfHalfyear (183)
- FiscalHalfyears (184)
- FiscalHalfyearOfYear (185)
- FiscalYears (186)
- ReportingDate (187)
- ReportingDayOfWeek (188)
- ReportingDayOfMonth (189)
- ReportingDayOfQuarter (190)
- ReportingDayOfTrimester (191)
- ReportingDayOfHalfyear (192)
- ReportingDayOfYear (193)
- ReportingWeeks (194)
- ReportingWeekOfYear (195)
- ReportingWeekOfHalfyear (196)
- ReportingWeekOfQuarter (197)
- ReportingWeekOfTrimester (198)
- ReportingWeekOfMonth (199)
- ReportingMonths (200)
- ReportingMonthOfTrimester (201)
- ReportingMonthOfQuarter (202)
- ReportingMonthOfHalfyear (203)
- ReportingMonthOfYear (204)
- ReportingTrimesters (205)
- ReportingTrimesterOfYear (206)
- ReportingQuarters (207)
- ReportingQuarterOfYear (208)
- ReportingQuarterOfHalfyear (209)
- ReportingHalfyears (210)
- ReportingHalfyearOfYear (211)
- ReportingYears (212)
- ManufacturingDate (213)
- ManufacturingDayOfWeek (214)
- ManufacturingDayOfMonth (215)
- ManufacturingDayOfQuarter (216)
- ManufacturingDayOfHalfyear (217)
- ManufacturingDayOfYear (218)
- ManufacturingWeeks (219)
- ManufacturingWeekOfYear (220)
- ManufacturingWeekOfHalfyear (221)
- ManufacturingWeekOfQuarter (222)
- ManufacturingWeekOfMonth (223)
- ManufacturingMonths (224)
- ManufacturingMonthOfQuarter (225)
- ManufacturingMonthOfHalfyear (226)
- ManufacturingMonthOfYear (227)
- ManufacturingTrimesters (228)
- ManufacturingTrimesterOfYear (229)
- ManufacturingQuarters (230)
- ManufacturingQuarterOfYear (231)
- ManufacturingQuarterOfHalfyear (232)
- ManufacturingHalfyears (233)
- ManufacturingHalfyearOfYear (234)
- ManufacturingYears (235)
- WinterSummerSeason (236)
- IsHoliday (237)
- IsWeekday (238)
- IsWorkingDay (239)
- IsPeakDay (240)
- ISO8601Date (241)
- ISO8601DayOfWeek (242)
- ISO8601DayOfYear (243)
- ISO8601Weeks (244)
- ISO8601WeekOfYear (245)
- ISO8601Years (246)
- RowNumber (247)
- ExtendedType (248).
- Description: this is just the description of the object
- IsHidden: a Boolean that indicates whether a column is treated as hidden by client visualisation tools. If the column is treated as hidden by client visualisation tools, it is TRUE; otherwise, it is FALSE
- State: this is a value that provides information about the
state of the column:
- Ready (1) – the column is queryable and has up-to-date data
- NoData (3) – the column is queryable but has no data. This state is applicable only to columns of the type Data
- CalculationNeeded (4) – the column is not queryable and needs to be refreshed (that is, recalculated) to become functional. This state applies only to columns of the type Calculated or CalculatedTableColumn
- SemanticError (5) - the column is in an error state because of an invalid expression. The column is not queryable. This state applies only to columns of the type Calculated or CalculatedTableColumn
- EvaluationError (6) – the column is in an error state because of an error during expression evaluation. The column is not queryable. This state applies only to columns of the type Calculated or CalculatedTableColumn
- DependencyError (7) – the column is in an error state because some of its calculation dependencies are in an error state. The column is not queryable. This state applies only to columns of the type Calculated or CalculatedTableColumn
- Incomplete (8) - some parts of the column have no data, and the column needs to be refreshed to bring the data in. The column is queryable. This state applies only to columns of the type Data
- SyntaxError (9) – the column is in an error state because of a syntax error in its expression. The column is not queryable. This state applies only to columns of the type Calculated
- IsUnique: a Boolean that indicates whether the column can contain duplicate values. If it is TRUE, the engine validates that this column cannot contain duplicate values; otherwise, it is FALSE
- IsKey: a Boolean that indicates whether the column is a key of the table. If it is TRUE, the column is a key of the table; otherwise, it is FALSE
- IsNullable: a Boolean that indicates whether null values are allowed in the column. If it is TRUE, null values are allowed in the column; otherwise, it is FALSE
- Alignment: specifies the text alignment of the
column in report visualisations. It is
returned as part of CSDL. The possible
values are as follows:
- Default (1)
- Left (2)
- Right (3)
- Center (4)
- TableDetailPosition: provides the ability to place this column in the DefaultDetails collection of the Table. This collection is an ordered set of Column types. A positive value indicates participation in the collection. The collection is sorted in ascending order of this element. The DefaultDetails collection is returned as part of the CSDL metadata returned by the DISCOVER_CSDL_METADATA operation
- IsDefaultLabel: a Boolean that indicates whether this column is included in the DisplayKey element in CSDL
- IsDefaultImage: a Boolean that indicates whether this column is returned as the DefaultImage property in CSDL
- SummarizeBy:
a value that indicates the default function, if any, used to aggregate this
field. Values include:
- Default (1)
- None (2)
- Sum (3)
- Min (4)
- Max (5)
- Count (6)
- Average (7)
- DistinctCount (8)
- if this value is omitted, "Default" is assumed for numeric fields and "None" is assumed for all other fields
- ColumnStorageID: an ID-based reference to a ColumnStorage object. The ColumnStorage object is reserved for internal use only
- Type: the type of column. The possible values
are :
- Data (1) – The contents of this column come from a data source
- Calculated (2) – the contents of this column are computed by using an expression after the Data columns have been populated
- RowNumber (3) – the column is an internal column that represents the row number
- CalculatedTableColumn (4) – the tables are built based on a calculated expression that is automatically inferred and generates the columns in the table
- SourceColumn: the name of the column from which data is retrieved. The name MUST match a column returned by the execution of the partition's QueryDefinition against the data source
- ColumnOriginID: an ID-based reference to a ColumnOrigin object
- Expression: the DAX expression that is evaluated for the calculated column
- FormatString: a string that specifies the format of the column contents. For a description of the FormatString content
- IsAvailableInMDX: a Boolean that indicates whether the column can be excluded from usage in ‘Multidimensional Expressions’ (MDX) query tools. If it is FALSE, the column can be excluded from usage in MDX query tools; otherwise, it is TRUE
- SortByColumnID: indicates that the column defining this property is to be sorted by the values of the column referenced by this property
- AttributeHierarchyID: an ID-based reference to an AttributeHierarchy object
- ModifiedTime: the time that the object was last modified
- StructureModifiedTime: the time that the structure of the object was last modified
- RefreshedTime: the time that the object was last refreshed
- SystemFlags: a bitmask that is used to identify the type of object. The possible values are as follows:
- Bit 0 is set to 1: the object is a column that belongs to a system table
- Bit 1 is set to 1: the object is a column that belongs to a calculated table of the type CalculatedTableColumn
- KeepUniqueRows: a Boolean that indicates the grouping of rows. If FALSE, client applications can group by
this column. If TRUE, client
applications are encouraged to group by a more unique key for the column. These semantics correspond to the following
behavior:
- FALSE: return the values of MD_GROUPING_BEHAVIOR_ENCOURAGE in the GROUPING_BEHAVIOR column of the MDSCHEMA_HIERARCHIES schema rowset and GroupOnValue in the GroupingBehavior field of the Property element in the result of DISCOVER_CSDL_METADATA
- TRUE: returns MD_GROUPING_BEHAVIOR_DISCOURAGE and GroupOnEntityKey
- DisplayOrdinal: indicates the visual position of the column, defined as a relative ordering rather than a strict ordering. It allows client applications to maintain a consistent column position. The DisplayOrdinal property is reserved for future use
- ErrorMessage: a string that explains the error state associated with the current object. It is set by the engine only when the state of the object is one of these three values: SemanticError, DependencyError, or EvaluationError. It is applicable only to columns of the type Calculated or CalculatedTableColumn. It is empty for other column objects
- SourceProviderType: the original data type of the column as defined in the language of the data source. This data type is used to generate queries directly against the data source, for example in Direct Query mode
- DisplayFolder: defines the display folder in which the column is displayed by the client applications
- EncodingHint: the encoding mechanism that
is used for the column. Compatibility
level 1400 or higher is required. The
possible values are:
- Default (0) – the server automatically determines which encoding mechanism to use
- Hash (1) – hash encoding is used
- Value (2) – value encoding is used.
- RelatedColumnDetailsID: this is not mentioned yet in the documents MS-SSAS-T (4/10/2023) as it has only been added in recently
- AlternateOfID: this is not mentioned yet in the documents MS-SSAS-T (4/10/2023) as it is has only been added in recently
- LineageTag: an optional tag that can be used to define the lineage of a column across different versions of a model
- SourceLineageTag: an optional tag that can be used to define the lineage of a referenced column across different versions of a model. As opposed to LineageTag, SourceLineageTag can be used to define the lineage of a referenced column rather than a column itself. SourceLineageTag is useful when a model references other models by using a Direct Query connection. Compatibility level 1600 or higher is required.
It should be noted that:
- it is used for querying the DMV (Dynamic Management Views) from the $System schema called TMSCHEMA where TM stands for ‘Tabular model’ and TMSCHEMA provides information from the tabular model
- sometimes querying DMVs may fail if we do not have the appropriate permission.
Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past
