Chapter 15 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 Scripting Name 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 Scripting Name 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 Scripting Name 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: 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 a dimension that contains:

  • ACCOUNT: (*) an account structure with parent-child relationships.
  • BILLOFMATERIALS (*): a material/component breakdown. The parent-child relationship implies a parent composed of its children.
  • CHANNEL (*): a distribution channel.
  • CURRENCY (*): currency information.
  • CUSTOMERS (*): customer information. The lowest level represents individual customers.
  • GEOGRAPHY (*): a geographic hierarchy.
  • ORGANIZATION (*): the reporting structure of an organization.
  • PRODUCTS (*): product information. The lowest level represents individual products.
  • PROMOTION (*): marketing and advertising promotions.
  • QUANTITATIVE (*): quantitative elements (such as example, income level, number of children, and so on).
  • RATES (*): different types of rates (for example, buy, sell, discounted. and so on).
  • SCENARIO (*): different business scenarios.

Dimension Attributes

The following extended attributes are available on the Microsoft tab:

Name Scripting Name 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: a level that refers to years (Time dimension only).
  • QUARTER: a level that refers to (calendar) quarters (Time dimension only).
  • MONTH: a level that refers to months (Time dimension only).
  • WEEK: a level that refers to weeks (Time dimension only).
  • DAY: a level that refers to days (Time dimension only).
  • DAYOFWEEK: a level that refers to days of the week (Time dimension only).
  • DATE: a level that refers to dates (Time dimension only).
  • HOUR: a level that refers to hours (Time dimension only).
  • MINUTE: a level that refers to minutes (Time dimension only).
  • SECOND: Indicates that a level refers to seconds (Time dimension only).

Dimension Hierarchies

The following extended attributes are available on the Microsoft tab:

Name Scripting Name 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 Scripting Name 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 Scripting Name 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 Scripting Name 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 Scripting Name Description
Encryption option WithOption This extended attribute defines the encryption option of the view, respecting the view creation syntax.

 

Copyright (C) 2006. Sybase Inc. All rights reserved.