MS SQL Server extended attributes

Chapter 14 DBMS-Specific Features

MS SQL Server extended attributes

The following extended attributes are defined by default in the MS SQL Server DBMS.

Columns

The following extended attributes are available on the Microsoft tab:

Name	Internal Code	Description
Do not validate check constraints during replication	ExtCkcNotForReplication	Specifies that "NOT FOR REPLICATION" keywords are used to prevent the CHECK constraint from being enforced during the distribution process used by replication.
Default constraint name	ExtDeftConstName	Contains the name of the constraint that is used to apply a default value to the column. If empty, the "constraint" keyword is not generated.
Identity seed and increment	ExtIdentitySeedInc	Is a string composed of two integer values separated by a comma. First value is the seed value of the identity column, meaning the value to be assigned to the first row in the table. Second value is the increment to add to the seed value for successive rows in the table.
Identity value not replicated	ExtIdtNotForReplication	Indicates that the IDENTITY property should not be enforced when a replication login inserts data into the table.
Not null constraint name	ExtNullConstName	Contains the name of the constraint that is used to apply a mandatory property of the column. If empty, the "constraint" keyword is not generated.
Row global unique identifier	ExtRowGuidCol	Indicates that the new column is a row global unique identifier column. Only one unique identifier column per table can be designated as the ROWGUIDCOL column.
Collation name	ExtCollation	A single string that specifies the collation name for a SQL collation.

Cubes

The following extended attributes are available on the Microsoft tab:

Name	Internal Code	Description
Options	Options	You can choose between the following: PASSTHROUGH: causes the SELECT clause to be passed directly to the source database without modification by PivotTable Service. If PASSTHROUGH is not specified, PivotTable Service parses the query and formulates a set of queries equivalent to the original that is optimized for the source database and index structures. This set of queries is often more efficient than the specified. DEFER_DATA: causes the query to be parsed locally and executed only when necessary to retrieve data to satisfy a user request. DEFER_DATA is used to specify that a local cube has to be defined in the ROLAP storage mode. ATTEMPT_DEFER: causes PivotTable Service to attempt to parse the query and defer data loading if successful, or, if the query cannot be parsed, to process the specified query immediately as if PASSTHROUGH had been specified. ATTEMPT_ANALYSIS: causes PivotTable Service to attempt to parse the query and formulate an optimized set of queries. If the query cannot be parsed, PivotTable Services processes the query immediately as if PASSTHROUGH had been specified.

Dimensions

The following extended attributes are available on the Microsoft tab:

Name	Internal Code	Description
Hidden	IsHidden	Indicates whether the dimension is hidden from clients.
Options	Options	Dimension options to manage member uniqueness and specify their storage. You can choose between: UNIQUE_NAME: Member names are unique within the dimension. UNIQUE_KEY: Member keys are unique within the dimension. NOTRELATEDTOFACTTABLE: Indicates that non-leaf members cannot be associated with fact table data. ALLOWSIBLINGSWITHSAMENAME: Determines whether children of a single member in a hierarchy can have identical names.
Subtype	SubType	Indicates the subtype of a dimension. You can choose between: PARENT_CHILD:Indicates that the dimension is a parent-child dimension. LINKED: Indicates that the cube is linked to another cube on a remote Analysis server. MINING: Indicates that the dimension is based on the content of an OLAP data-mining model that has been processed for a cube.
Template	Template	Contains a template string that is used to generate captions for system-generated data members.
Time	TimeDef	Indicates that a dimension refers to time (year, month, week, day, and so on). You can choose between: TIME: Indicates that a dimension refers to time (year, month, week, day, and so on). The only valid levels in a time dimension are those defined in the LevelTypes enumeration. The following values post-fixed by an asterisk () are additional values that can be used by the add-in but do not exist in the MDX syntax. You can choose between: ACCOUNT: () Describes a dimension that contains an account structure with parent-child relationships. BILLOFMATERIALS (): Describes a dimension that represents a material/component breakdown. The parent-child relationship implies a parent composed of its children. CHANNEL (): Describes a dimension that contains information about a distribution channel. CURRENCY (): Describes a dimension that contains currency information. CUSTOMERS (): Describes a dimension that contains customer information. The lowest level represents individual customers. GEOGRAPHY (): Describes a dimension that contains a geographic hierarchy. ORGANIZATION (): Describes a dimension that represents the reporting structure of an organization. PRODUCTS (): Describes a dimension that contains product information. The lowest level represents individual products. PROMOTION (): Describes a dimension that contains information about marketing and advertising promotions. QUANTITATIVE (): Describes a dimension that contains quantitative elements (for example, income level, number of children, and so on). RATES (): Describes a dimension that contains different types of rates (for example, buy, sell, discounted. and so on). SCENARIO (*): Describes a dimension that contains different business scenarios.

Dimension Attributes

The following extended attributes are available on the Microsoft tab:

Name	Internal Code	Description
Rollup expression	CustomRollupExpr	Contains a Multidimensional Expressions (MDX) expression used to override the default roll-up mode.
Format key	FormatKey	Name of the column or expression that contains member keys.
Format name	FormatName	Name of the column or expression that contains member names.
Hide values	HideValues	Options to hide level members. You can choose between: BLANK_NAME: Hides a level member with an empty name. PARENT_NAME: Hides a level member when the member name is identical to the name of its parent. ONLY_CHILD_AND_BLANK_NAME: Hides a level member when it is the only child of its parent and its name is null or an empty string. ONLY_CHILD_AND_PARENT_NAME: Hides a level member when it is the only child of its parent and is identical to the name of its parent
Hidden	IsHidden	Indicates whether the level is hidden from client applications.
Options	Options	Options about member uniqueness, ordering and data source. You can choose between: UNIQUE: Indicates that the members of a level are unique. UNIQUE_NAME: Indicates that their member name columns uniquely identify the level members. UNIQUE_KEY: Indicates that their member key columns uniquely identify the level members. NOTRELATEDTOFACTTABLE: Indicates that the level members cannot be associated with fact table data. SORTBYNAME: Indicates that level members are ordered by their names. SORTBYKEY: Indicates that level members are ordered by their keys. SORTBYPROPERTY <property names>: Indicates that members are ordered by their property <property names>.
Root values	RootValues	Determines how the root member or members of a parent-child hierarchy are identified. You can choose between: ROOT_IF_PARENT_IS_BLANK: Only members with a null, a zero, or an empty string in their parent key column are treated as root members. ROOT_IF_PARENT_IS_MISSING: Only members with parents that cannot be found are treated as root members. ROOT_IF_PARENT_IS_SELF: Only members having themselves as parents are treated as root members. ROOT_IF_PARENT_IS_BLANK _OR_SELF_OR_MISSING: Members are treated as root members if they meet one or more of the conditions specified by ROOT_IF_PARENT_IS_BLANK, ROOT_IF_PARENT_IS_SELF, or ROOT_IF_PARENT_IS_MISSING.
Type	Type	Identifies the specific type of level. . You can choose between: ALL: Indicates the top (All) level of a dimension (the one that precalculates all the members of all lower levels). YEAR: Indicates that a level refers to years. It must be used in a dimension which type is Time dimension. QUARTER: Indicates that a level refers to (calendar) quarters. It must be used in a dimension which type is Time dimension. MONTH: Indicates that a level refers to months. It must be used in a dimension which type is Time dimension. WEEK: Indicates that a level refers to weeks. It must be used in a dimension which type is Time dimension. DAY: Indicates that a level refers to days. It must be used in a dimension which type is Time dimension. DAYOFWEEK: Indicates that a level refers to days of the week. It must be used in a dimension which type is Time dimension. DATE: Indicates that a level refers to dates. It must be used in a dimension which type is Time dimension. HOUR: Indicates that a level refers to hours. It must be used in a dimension which type is Time dimension. MINUTE: Indicates that a level refers to minutes. It must be used in a dimension which type is Time dimension. SECOND: Indicates that a level refers to seconds. It must be used in a dimension which type is Time dimension.

Dimension Hierarchies

The following extended attributes are available on the Microsoft tab:

Name	Internal Code	Description
Hidden	IsHidden	Indicates whether the hierarchy is hidden from client applications.

Fact Measures

The following extended attributes are available on the Microsoft tab:

Name	Internal Code	Description
Format	Format	Format used to display the values of the cube measure.
Cube measure function type	Function	A value corresponding to the type of aggregate function used by the cube measure.
Hidden	IsHidden	Indicates whether the measure is visible to the client.
Member calculating order	SolveOrder	Order in which the calculated member will be solved when calculated members intersect each other.
Source column data type	Type	Returns a OLE DB enumeration constant that identifies the SourceColumn (in the fact table) data type.

References

The following extended attributes are available on the Microsoft tab:

Name	Internal Code	Description
Do not validate foreign key constraint during replication	ExtFkNotForReplication	Specifies that "NOT FOR REPLICATION" keywords are used to prevent the FOREIGN KEY constraint from being enforced during the distribution process used by replication.

Tables

The following extended attributes are available on the Microsoft tab:

Name	Internal Code	Description
Do not validate check constraints during replication	ExtCktNotForReplication	Specifies that "NOT FOR REPLICATION" keywords are used to prevent the TABLE CHECK constraint from being enforced during the distribution process used by replication.

Views

The following extended attributes are available on the Microsoft tab:

Name	Internal Code	Description
Encryption option	WithOption	This extended attribute defines the encryption option of the view, respecting the view creation syntax.