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 Description
Do not validate check constraints during replication Specifies that "NOT FOR REPLICATION" keywords are used to prevent the CHECK constraint from being enforced during the distribution process used by replication.

Scripting name: ExtCkcNotForReplication
Default constraint name 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.

Scripting name: ExtDeftConstName
Identity seed and increment 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.

Scripting name: ExtIdentitySeedInc
Identity value not replicated Indicates that the IDENTITY property should not be enforced when a replication login inserts data into the table.

Scripting name: ExtIdtNotForReplication
Not null constraint name 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.

Scripting name: ExtNullConstName
Row global unique identifier 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.

Scripting name: ExtRowGuidCol
Collation name A single string that specifies the collation name for a SQL collation.

Scripting name: ExtCollation

Cubes

The following extended attributes are available on the Microsoft tab:

Name Description
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.


Scripting name: Options

Dimensions

The following extended attributes are available on the Microsoft tab:

Name Description
Hidden Indicates whether the dimension is hidden from clients.

Scripting name: IsHidden
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.


Scripting name: Options
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.


Scripting name: SubType
Template Contains a template string that is used to generate captions for system-generated data members.

Scripting name: Template
Time 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.


Scripting name: TimeDef

Dimension Attributes

The following extended attributes are available on the Microsoft tab:

Name Description
Rollup expression Contains a Multidimensional Expressions (MDX) expression used to override the default roll-up mode.

Scripting name: CustomRollupExpr
Format key Name of the column or expression that contains member keys.

Scripting name: FormatKey
Format name Name of the column or expression that contains member names.

Scripting name: FormatName
Hide values 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


Scripting name: HideValues
Hidden Indicates whether the level is hidden from client applications.

Scripting name: IsHidden
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>.


Scripting name: Options
Root values 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.


Scripting name: RootValues
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).


Scripting name: Type

Dimension Hierarchies

The following extended attributes are available on the Microsoft tab:

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

Scripting name: IsHidden

Fact Measures

The following extended attributes are available on the Microsoft tab:

Name Description
Format Format used to display the values of the cube measure.

Scripting name: Format
Cube measure function type A value corresponding to the type of aggregate function used by the cube measure.

Scripting name: Function
Hidden Indicates whether the measure is visible to the client.

Scripting name: IsHidden
Member calculating order Order in which the calculated member will be solved when calculated members intersect each other.

Scripting name: SolveOrder
Source column data type Returns a OLE DB enumeration constant that identifies the SourceColumn (in the fact table) data type.

Scripting name: Type

References

The following extended attributes are available on the Microsoft tab:

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

Scripting name: ExtFkNotForReplication

Tables

The following extended attributes are available on the Microsoft tab:

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

Scripting name: ExtCktNotForReplication

Views

The following extended attributes are available on the Microsoft tab:

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

Scripting name: WithOption

 

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