Microsoft SQL Server

To create a PDM with support for features specific to the MS SQL Server DBMS family, select the appropriate version in the DBMS field of the New Model dialog. To view these extensions to the PowerDesigner metamodel in the Resource Editor, select Database > Edit Current DBMS and expand the Profile node.

In addition to the extensions listed below, PowerDesigner supports the following features for SQL Server 2005 and higher:

  • User Schemas – Use the schema stereotype to specify that a user is actually a schema, belonging to another user (the "principal").

  • WithOption – Use the withoptions type to enable access to additional physical options when working with views.

  • Support for multiple databases during live database reverse engineering.

The following sections list the extensions provided for MS SQL Server.

Abstract Data Types

The following extensions are available on the Microsoft tab:

Name

Description

Assembly

Specifies the assembly to bind with the abstract data type.

Scripting name: Assembly

Abstract Data Type Attributes

The following extensions are available on the Microsoft tab:

Name

Description

Nullable

Specifies that the type column allows null value.

Scripting name: Nullable

Computed

Specifies that the type column is computed.

Scripting name: Specifies that the type column is computed.

Identity

Specifies that the new column is an identity column.

Scripting name: Identity

Expression

Specifies an expression that defines the value of a computed column.

Scripting name: Expression

Persisted

Specifies that the SQL Server Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated.

Scripting name: Persisted

Seed

Specifies the value used for the very first row loaded into the table.

Scripting name: Seed

Increment

Specifies the incremental value added to the identity value of the previous row loaded.

Scripting name: Increment

Default

Specifies the value provided for the column when a value is not explicitly supplied during an insert.

Scripting name: Default

Row GUID

Specifies that the new column is a row GUID column

Scripting name: RowGuidCol

Collation

Specifies the collation for the column.

Scripting name: Collate

Columns

The following extensions are available on the Microsoft tab:

Name

Description

Row global unique identifier

[v2000 and higher] 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

Sparse

[v2008 and higher] Specifies that the column is a sparse column. The storage of sparse columns is optimized for null values. Sparse columns cannot be designated as NOT NULL.

Scripting name: Sparse

Filestream

[v2008 and higher] Specifies that when the FILESTREAM storage attribute is specified for a column, all values for that column are stored in a FILESTREAM data container on the file system

Scripting name: Filestream

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

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

Collation name

[v2005 and higher] A single string that specifies the collation name for a SQL collation.

Scripting name: ExtCollation

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

XML schema collection

[v2000 and higher] Applies only to the XML data type for associating an XML schema collection with the type.

Scripting name: XMLSchemaCollection

Content type

[v2005 and higher] - CONTENT:

Specifies that each instance of the XML data type in column_name can contain multiple top-level elements. CONTENT applies only to the XML data type and can be specified only if xml_schema_collection is also specified. If not specified, CONTENT is the default behavior.

- DOCUMENT:

Specifies that each instance of the XML data type in column_name can contain only one top-level element. DOCUMENT applies only to the XML data type and can be specified only if xml_schema_collection is also specified.

Scripting name: ContentType

Cubes

The following extensions are available on the Microsoft tab:

Name

Description

Options
[v2000] 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

Storage mode

[v2005 and higher] Specifies the storage mode for the cube.

Scripting name: StorageMode

Visible

[v2005 and higher] Determines the visibility of the Cube.

Scripting name: Visible

Dimensions

The following extensions are available on the Microsoft tab:

Name

Description

Hidden

[v2000] Indicates whether the dimension is hidden from clients.

Scripting name: IsHidden

Options
[v2000] 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
[v2000] 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

[v2000] Contains a template string that is used to generate captions for system-generated data members.

Scripting name: Template

Time
[v2000] 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

Type

[v2005 and higher] Provides information about the contents of the dimension.

Scripting name: Type

Storage mode

[v2005 and higher] Determines the storage mode for the parent element.

Scripting name: StorageMode

AttributeAllMemberName

[v2005 and higher] Contains the caption, in the default language, for the All member of the dimension.

Scripting name: AttributeAllMemberName

WriteEnabled

[v2005 and higher] Indicates whether dimension writebacks are available (subject to security permissions).

Scripting name: WriteEnabled

Dimension Attributes

The following extensions are available on the Microsoft tab:

Name

Description

Rollup expression

[v2000] Contains a Multidimensional Expressions (MDX) expression used to override the default roll-up mode.

Scripting name: CustomRollupExpr

Format key

[v2000] Name of the column or expression that contains member keys.

Scripting name: FormatKey

Format name

[v2000] Name of the column or expression that contains member names.

Scripting name: FormatName

Hide values
[v2000] 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

[v2000] Indicates whether the level is hidden from client applications.

Scripting name: IsHidden

Options
[v2000] 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
[v2000] 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
[v2000 and higher] 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

MembersWithData

[v2005 and higher] Determines whether to display data members for non-leaf members in the parent attribute.

Scripting name: MembersWithData

OrderBy

[v2005 and higher] Describes how to order the members contained in the attribute.

Scripting name: OrderBy

MemberNamesUnique

[v2005 and higher] Determines whether member names under the parent element must be unique.

Scripting name: MemberNamesUnique

IsAggregatable

[v2005 and higher] Specifies whether the values of the DimensionAttribute element can be aggregated.

Scripting name: IsAggregatable

AttributeHierarchyEnabled

[v2005 and higher] Determines whether an attribute hierarchy is enabled for the attribute.

Scripting name: AttributeHierarchyEnabled

AttributeHierarchyVisible

[v2005 and higher] Determines whether the attribute hierarchy is visible to client applications.

Scripting name: AttributeHierarchyVisible

Databases

The following extensions are available on the Microsoft tab:

Name

Description

Primary

Specifies that the associated file specification list defines the primary file.

Scripting name: Primary

File

Gets or sets the file specification.

Scripting name: FileListFileSpec

Filegroup

Gets or sets the first filegroup name.

Scripting name: FilelistFilegroup

File (filegroup)

Gets or sets the Filegroup specification.

Scripting name: FileGroupFileSpec

Log on

Gets or sets the log file specification.

Scripting name: LogOnFileSpec

Collation name

[v2000 and higher] Specifies the default collation for the database. Collation name can be either a Windows collation name or a SQL collation name.

Scripting name: Collate

Attach

Specifies that a database is attached from an existing set of operating system files.

Scripting name: ForAttach

With

[v2005 and higher] Controls Service Broker options on the database.

Service Broker options can only be specified when the FOR ATTACH clause is used.

  • ENABLE_BROKER: Specifies that Service Broker is enabled for the specified database.

  • NEW_BROKER: Creates a new service_broker_guid value in both sys.databases and the restored database and ends all conversation endpoints with clean up. The broker is enabled, but no message is sent to the remote conversation endpoints.

  • ERROR_BROKER_CONVERSATIONS: Ends all conversations with an error stating that the database is attached or restored. The broker is disabled until this operation is completed and then enabled.

Scripting name: ForAttachWith

Attach rebuild log

[v2005 and higher] Specifies that the database is created by attaching an existing set of operating system files.

Scripting name: ForAttachRebuildLog

Database chaining

[v2005 and higher] When ON is specified, the database can be the source or target of a cross database ownership chain.

When OFF, the database cannot participate in cross database ownership chaining. The default is OFF.

Scripting name: WithDbChaining

Trust worthy

[v2005 and higher] When ON is specified, database modules (for example, views, user-defined functions, or stored procedures) that use an impersonation context can access resources outside the database.

When OFF, database modules in an impersonation context cannot access resources outside the database. The default is OFF.

Scripting name: WithTrustworthy

Snapshot of

[v2005 and higher] Specifies the name of the new database snapshot.

Scripting name: AsSnapshotOf

Load

[up to v2000] Indicates that the database is created with the "dbo use only" database option turned on, and the status is set to loading.

Scripting name: ForLoad

For information about the extended attributes available on the Mirroring tab, see Database mirroring.

Data Sources

The following extensions are available on the OLE DB tab:

Name

Description

Data provider
Specifies the data provider. You can choose between:

  • .NET Framework Data Provider for Microsoft SQL Server

  • .NET Framework Data Provider for Oracle

  • Native Data Provider for OLE DB

Scripting name: DataProvider

Connection string

Specifies the connection string.

Scripting name: ConnectionString

The following extensions are available on the Configuration tab:

Name

Description

Server name

Specifies the server name.

Scripting name: ServerName

Authentication

[only for SQL Server] Specifies the Windows Authentication and SQL Server Authentication types.

Scripting name: AuthenticationType

User name

Specifies the User name.

Scripting name: UserName

Password

Specifies the password.

Scripting name: Password

Initial catalog

[only for SQL Server and OLE DB] Specifies the Initial catalog.

Scripting name: InitialCatalog

Database File

[only for SQL Server] Specifies a Microsoft SQL Server database file if you select an MSSQL connection.

Scripting name: MSSQLDatabaseFile

Logical name

[only for SQL Server] Specifies the logical name of the selected database file.

Scripting name: LogicalName

Data providers

[only for OLE DB] Specifies the data provider.

Scripting name: DataProvider

Location

[only for OLE DB] Specifies the location for OLEDB.

Scripting name: Location

Persist security info

[only for OLE DB] Specifies that security information be persistent.

Scripting name: PersistSecurityInfo

Use Windows NT Integrated Security

[only for OLE DB] Specifies whether to use windows NT Integrated Security or not.

Scripting name: UseNTIntegratedSecurity

Dimension Hierarchies

The following extensions are available on the Microsoft tab:

Name

Description

Hidden

[v2000] Indicates whether the hierarchy is hidden from client applications.

Scripting name: IsHidden

AllMemberName

[v2005 and higher] Contains the caption in the default language for the All member of a Hierarchy element.

Scripting name: AllMemberName

MemberNamesUnique

[v2005 and higher] Determines whether member names under the parent element must be unique.

Scripting name: MemberNamesUnique

AllowDuplicateNames

[v2005 and higher] Determines whether duplicate names are allowed in a Hierarchy element.

Scripting name: AllowDuplicateNames

Fact Measures

The following extensions are available on the Microsoft tab:

Name

Description

Format

[v2000] Format used to display the values of the cube measure.

Scripting name: Format

Cube measure function type

[v2000] A value corresponding to the type of aggregate function used by the cube measure.

Scripting name: Function

Hidden

[v2000] Indicates whether the measure is visible to the client.

Scripting name: IsHidden

Member calculating order

[v2000] Order in which the calculated member will be solved when calculated members intersect each other.

Scripting name: SolveOrder

Source column data type

[v2000] Returns an OLE DB enumeration constant that identifies the SourceColumn (in the fact table) data type.

Scripting name: Type

AggregateFunction

[v2005 and higher] Defines the common prefix to be used for aggregation names throughout the associated parent element.

Scripting name: AggregateFunction

BindingType

[v2005 and higher] Defines the binding type for the measure.

Scripting name: BindingType

Visible

[v2005 and higher] Determines the visibility of the Fact Measure.

Scripting name: Visible

FormatString

[v2005 and higher] Describes the display format for a CalculationProperty or a Measure element.

Scripting name: FormatString

Indexes

Note: For additional information about special SQL Server index types, see XML Indexes (SQL Server) and Spatial Indexes (SQL Server).

The following extensions are available on the Microsoft tab:

Name

Description

Filegroup

Specifies the name of the filegroup.

Scripting name: FileGroup

Partition scheme

[v2005 and higher] Specifies the name of the partition scheme.

Scripting name: PartitionScheme

Column

[v2005 and higher] Specifies the partitioned column.

Scripting name: PartitionSchemeColumn

Fill factor

Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild.

Scripting name: FillFactor

Max degree of parallelism

[v2005 and higher] Overrides the max degree of parallelism configuration option for the duration of the index operation. Use MAXDOP to limit the number of processors used in a parallel plan execution. The maximum is 64 processors.

Scripting name: MaxDop

Pad index

Specifies index padding.

Scripting name: PadIndex

Statistics no recompute

Specifies whether distribution statistics are recomputed.

Scripting name: StatisticsNoRecompute

Drop existing

Specifies that the named, preexisting clustered, nonclustered, or XML index is dropped and rebuilt.

Scripting name: DropExisting

Online

[v2005 and higher] Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation.

Scripting name: Online

Sort in temporary database

[v2005 and higher] Specifies whether to store temporary sort results in tempdb.

Scripting name: SortInTempDB

Allow row locks

[v2005 and higher] Specifies whether row locks are allowed.

Scripting name: AllowRowLocks

Allow page locks

[v2005 and higher] Specifies whether page locks are allowed.

Scripting name: AllowPageLocks

Ignore dup key

Specifies the error response to duplicate key values in a multiple row insert operation on a unique clustered or unique nonclustered index.

Scripting name: IgnoreDupKey

If the index is not a cluster index, then the Include tab is displayed, allowing you to specify the columns with which it is associated.

Keys

The following extensions are available on the Microsoft tab:

Name

Description

Filegroup

Specifies the name of the filegroup.

Scripting name: FileGroup

Fill Factor

Specifies how full SQL Server should make each index page used to store the index data.

Scripting name: FillFactor

References

The following extensions 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

Storages

The following extensions are available on the Microsoft tab:

Name

Description

Contains filestream

Specifies that the filegroup stores FILESTREAM binary large objects (BLOBs) in the file system.

Scripting name: FileStream

Tables

The following extensions 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

Table is partitioned

Specifies that the table is partitioned.

Scripting name: PartitionedTable

Filegroup

[unpartitioned tables] Specifies the name of the filegroup.

Scripting name: FileGroup

Text/Image

[unpartitioned tables] Specifies the name of the filegroup where text and image are stored.

Scripting name: TextImageOn

Filestream

[unpartitioned tables] Specifies the name of the filegroup used for filestream.

Scripting name: FilestreamOnFilegroup

Compression

[unpartitioned tables] Specifies the compression type of the table (none, row or page).

Scripting name: TableCompression

Partition scheme

[partitioned tables, v2005 and higher] Specifies the name of the partition scheme. You must also specify the name of the partitioned column

Scripting name: PartitionScheme, PartitionSchemeColumn

Filestream partition scheme

[partitioned tables, v2005 and higher] Specifies the name of the partition scheme.

Scripting name: FilestreamPartitionScheme, FilestreamPartitionSchemeColumn

Compression

[partitioned tables] Specifies the partitions that use the compression.

Scripting name: DataCompression

Triggers

The following extensions are available on the Microsoft tab:

Name

Description

Option

Is a concatenation of the WITH ENCRYPTION (which is illegal for CLR triggers, and which prevents the trigger from being published) and EXECUTE AS (which specifies the security context under which the trigger is executed) options.

Scripting name: Option

An additional property is available for CLR triggers (see CLR Procedures, Functions, and Triggers (SQL Server)).

Users

The following extensions are available on the General tab (v2005 and higher):

Name

Description

Implicit schema

Specifies that the stored procedure sp_grantdbaccess will be used instead of a create user statement during database generation.

Scripting name: ImplicitSchema

Default schema

Specifies the first schema searched to resolve the names of objects for this user. If the Implicit schema option is selected, then the default schema is initialized to the name of the user.

Scripting name: DefaultSchema

Views

The following extensions are available on the Microsoft tab:

Name

Description

Encryption option

Defines the encryption option of the view, respecting the view creation syntax.

Scripting name: WithOption

Parent topic: DBMS Definition Reference