2.2.5.4 Column Object

  • Article

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 visualize the data in the column.

The Column object has the following properties.

Name

Type

Description

ID

unsignedLong

A reference to the object.

TableID

unsignedLong

An ID-based reference to a Table object.

ExplicitName

string

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

string

Specifies the engine-generated name for the column. It is valid only for columns of type CalculatedTableColumn.

ExplicitDataType

enumeration

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

enumeration

Specifies the engine-generated data type for this column. It is valid only for columns of the type CalculatedTableColumn or Calculated.

DataCategory

string

The values in the following enumeration PropertyType are automatically mapped to the LEVEL_TYPE column that is defined in [MS-SSAS] section 3.1.4.2.2.1.3.8.1.

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)

  • WebXMLOrXSL (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

string

The description of the object.

IsHidden

boolean

A Boolean that indicates whether a column is treated as hidden by client visualization tools.

If the column is treated as hidden by client visualization tools, it is "true"; otherwise, it is "false".

State

enumeration

A value that provides information about the state of the column. The possible values and their interpretation are as follows:

  • 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

boolean

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

boolean

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

boolean

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

enumeration

Specifies the text alignment of the column in report visualizations. It is returned as part of CSDL. The possible values are as follows:

  • Default (1)

  • Left (2)

  • Right (3)

  • Center (4)

TableDetailPosition

int

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 (see [MS-SSAS] section 3.1.4.2.2.1.3.61).<35>

IsDefaultLabel

boolean

A Boolean that indicates whether this column is included in the DisplayKey element in CSDL.

IsDefaultImage

boolean

A Boolean that indicates whether this column is returned as the DefaultImage property in CSDL.

SummarizeBy

enumeration

A value that indicates the default function, if any, used to aggregate this field. The possible values are as follows:

  • 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

unsignedLong

An ID-based reference to a ColumnStorage object. The ColumnStorage object is reserved for internal use only.

Type

enumeration

The type of Column. The possible values are as follows:

  • 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. See section 2.2.5.6 for setting the type of partition to Calculated.

SourceColumn

string

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

unsignedLong

An ID-based reference to a ColumnOrigin object.

Expression

string

The DAX expression that is evaluated for the calculated column.

FormatString

string

A string that specifies the format of the column contents. For a description of the FormatString content, see [MSDN-FSCMDX].

IsAvailableInMDX

boolean

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

unsignedLong

Indicates that the column defining this property is to be sorted by the values of the column referenced by this property.

AttributeHierarchyID

unsignedLong

An ID-based reference to an AttributeHierarchy object.

ModifiedTime

dateTime

The time that the object was last modified.

StructureModifiedTime

dateTime

The time that the structure of the object was last modified.

RefreshedTime

dateTime

The time that the object was last refreshed.

SystemFlags

long

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. See SystemFlags on the Table object defined in section 2.2.5.3.

  • Bit 1 is set to 1: The object is a column that belongs to a calculated table of the type CalculatedTableColumn.

KeepUniqueRows

boolean

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. For an example, see [MS-CSDLBI] section 2.1.16.3.

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: Return MD_GROUPING_BEHAVIOR_DISCOURAGE and GroupOnEntityKey.

DisplayOrdinal

int

Indicates the visual position of the column, defined as a relative ordering rather than a strict ordering (example: 10, 20, 40, 50). It allows client applications to maintain a consistent column position.

The DisplayOrdinal property is reserved for future use.

ErrorMessage

string

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

string

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

string

Defines the display folder in which the column is displayed by the client applications.

EncodingHint<36>

enumeration

The encoding mechanism that is used for the column. The possible values are as follows:

  • Default (0) – The server automatically determines which encoding mechanism to use.

  • Hash (1) – Hash encoding is used.

  • Value (2) – Value encoding is used.

Compatibility level 1400 or higher is required.

LineageTag<37>

string

An optional tag that can be used to define the lineage of a column across different versions of a model.

Compatibility level 1600 or higher is required.

SourceLineageTag<38>

string

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.