Home

	Expand Search

WarehouseArchitect - User's Guide

Part 2 Conceptual Data Model

Chapter 10: Generating a WarehouseArchitect Model from a Conceptual Data Model

Chapter 10
Generating a WarehouseArchitect Model from a Conceptual Data Model

About this chapterThis chapter describes how to generate a Physical Data Model (WAM) from a Conceptual Data Model (CDM).

Generating objects

Object in a CDM	Generated object in a WAM
Entity	Table
Entity attribute	Table column
Identifier	Primary or foreign key depending on independent or dependent relationship
Relationship	Reference

Changing the name of columns automatically

Two columns in the same table cannot have the same name.

If column names conflict due to foreign key migration, PowerDesigner automatically renames the migrated columns:

• The new name is composed of the first three letters of the original entity name followed by the code of the attribute
• During generation, the Messages window signals column being renamed, for example: Column EMPNUM of table EMPLOYEE has been renamed to: EMP_EMPNUM

Generating keys from identifiers

Identifiers generate primary and foreign keys in the WAM. The type of key depends on relationships in the CDM.

A primary key is a column or columns whose values uniquely identify a row in a table.

A foreign key is a column or columns that depend on and migrate from a primary key column in another table.

Independent one-to-many relationships

In independent one-to-many relationships, the identifier of the entity on the one side of the relationship becomes a:

• Primary key in the table generated by the entity on the one side of the relationship
• Foreign key in the table generated by the entity on the many side of the relationship

The CDM below shows an independent relationship. Each division contains one or more employees.

The CDM above generates two tables:

Table	Primary key	Foreign key
Division	Division number	--
Employee	Employee number	Division number

The following WAM results from generation.

Dependent one-to-many relationships

In dependent relationships, the identifier of the nondependent entity becomes a primary/foreign key in the table generated by the dependent entity.

The migrated column is integrated into the primary key index if it already exists. In this case, no new index is generated.

The CDM below shows a dependent relationship. Each task must have a project number.

The CDM above generates two tables:

Table	Primary key	Foreign key
Project	Project number	--
Task	Project number/Task number	Project number

The following WAM results from generation.

Independent one-to-one relationships

In independent one-to-one relationships, the identifier of one entity migrates as a foreign key to the table generated by the other.

If the specified threshold value is attained or if no threshold is given, a foreign key index is generated for each migrated column.

Independent many-to-many relationships

In independent many-to-many relationships, the identifiers of both entities migrate to a join tables as primary/foreign keys. A single index is generated for the join table.

The CDM below shows an independent relationship. Each employee can be a member of one or more teams, and each team can have one or more employees as members.

The CDM above generates three tables:

Table	Primary key	Foreign key
Team	Team number	--
Employee	Employee number	--
Member	Team number/Employee number	Team number/Employee number

The following WAM results from generation.

Generating tables from entities with inheritance links

Two properties influence the generation of tables from entities with inheritance links.

Object	Property	When selected generates
Entity	Generate table	Table for the entity (parent or child)
Inheritance	Generation mode	Parent and/or children as indicated

Translating data types

Using the DEF file to define data type translations

The DEF file contains the rules for data type translation. In the List of Data Types section, it lists the exact correspondence between the PowerDesigner data type and the physical data type for the target DBMS.

You can modify the definition of an existing physical data type by making modifications directly in the DEF file.

Saving DEF file during upgrade When you upgrade PowerDesigner, existing DEF files are replaced. However, you can save the modifications that you have made to an existing DEF file by saving it under a different name before starting the upgrade. In the DEF file, set the constant DbmsName equal to the name of the copied file.

Translating conceptual data types

The following table lists the conceptual data types to which the DEF file assigns translations:

Numeric data types

Conceptual data type	Code in DEF file	What it stores	Translation example for SQL Anywhere
Integer	I	32-bit integer	integer
Short Integer	SI	16-bit integer	smallint
Long Integer	LI	32-bit integer	integer
Byte	BT	256 values	smallint
Number	N	Numbers with a fixed decimal point	numeric
Decimal	DC	Numbers with a fixed decimal point	decimal
Float	F	32-bit floating decimal numbers	float
Short Float	SF	Less than 32-bit floating decimal number	real
Long Float	LF	64-bit floating decimal numbers	double
Money	MN	Numbers with a fixed decimal point	numeric
Serial	NO	Automatically incremented numbers	numeric
Boolean	BL	Two opposing values (true/false; yes/no; 1/0)	numeric(1)

Character data types

Conceptual data type	Code in DEF file	What it stores	Translation example for SQL Anywhere
Characters	A	Character strings	char
Variable Characters	VA	Character strings	varchar
Long Characters	LA	Character strings	varchar
Long Var Characters	LVA	Character strings	long varchar
Text	TXT	Character strings	long varchar
Multibyte	MB	Multibyte character strings	char
Variable Multibyte	VMB	Multibyte character strings	varchar

Time data types

Conceptual data type	Code in DEF file	What it stores	Translation example for SQL Anywhere
Date	D	Day, month, year	date
Time	T	Hour, minute, and second	time
Date & Time	DT	Date and time	timestamp
Timestamp	TS	System date and time	timestamp

Other data types

Conceptual data type	Code in DEF file	What it stores	Translation example for SQL Anywhere
Binary	BIN	Binary strings	binary
Long Binary	LBIN	Binary strings	long binary
Image	PIC	Images	long binary
Bitmap	BMP	Images in bitmap format (BMP)	long binary
OLE	OLE	OLE links	long binary
Other	*	User-defined data type	char(10)
Undefined	<UNDEF>	Not yet defined data type	<undefined>

Generating a WAM

• Global CDM
• Submodel of a CDM

Generation parameters

To generate a WAM, you indicate the following WAM generation parameters:

Required parameters

Parameter	Description
Database name	Target database management system (DBMS) for the resulting WAM
WAM filename	Filename for the resulting WAM

Options

Parameter	Description
Preserve modifications	Do not overwrite certain modifications in a previously generated WAM
Generate submodels	When generating from a global CDM, generate a WAM submodel corresponding to each CDM submodel
Generate description	Copy descriptions from CDM objects to corresponding WAM objects
Generate annotation	Copy annotations from CDM objects to corresponding WAM objects
Generate synonyms	For each entity synonym symbol, generate a table synonym symbol
Check model	Check the model before generating the WAM, and stop generation if an error is found
Display warnings	Include warning messages in the Message box during WAM generation

Check model before generation If you select the Check Model option, the procedure to generate a WAM starts by checking the validity of the CDM or submodel. A WAM results when no errors are found. You can set check options by selecting Dictionary-->Check Model.

Table, index, and reference parametersThe following parameters define naming conventions and defaults for tables, indexes, and references:

Parameter	Description
Table prefix	Prefix for the names of tables generated from entities in the CDM
PK index name	Naming convention for primary keys, by default %TABLE%_PK
FK index name	Naming convention for foreign keys, by default %REFR%_FK
Index threshold	Minimum number of estimated occurrences of an entity necessary to create an index on a foreign key
Update rule	Default update constraint for referential integrity
Delete rule	Default delete constraint for referential integrity

If you enter a value for the index threshold, indexes on foreign keys are only generated if the number of estimated occurrences is greater than the threshold. The estimated number of occurrences is an entity property that you can include in the entity definition.

If the entity has no specified number of occurrences, the foreign key indexes are generated by default.

PK index name variablesYou can use the following variables in the PK index name field:

Variable	Value
%TABLE%	Table code
%DATABASE%	Model code
%USER%	Model author

FK index name variablesYou can use the following variables in the FK index name field:

Variable	Value
%REFR%	Reference code
%TABLE%	Table code
%DATABASE%	Model code
%USER%	Model author
%NO%	Reference number

Preservation options

If you select Preserve Modifications as a generation parameter, you can further specify what to update in the WAM by selecting any of the following preservation options:

Creation

What you created in the WAM	To preserve creation, select options
New WAM objects (business rules, domains, tables, columns, references, indexes, keys, views, triggers, extended attributes)	No options needed (Preserve Modifications is sufficient)
Symbols for new tables, view, and references	Preserve graphics
Shapes and lines	Preserve graphics

General modification

What you modified in the WAM	To preserve modification, select options
Names and codes of tables, views, references, and columns	No options needed (Preserve Modifications is sufficient)
Use of business rules by objects	No options needed (Preserve Modifications is sufficient)
Display preferences	Preserve graphics
Shapes and lines	Preserve graphics
Labels	Preserve label

Descriptions and annotations

What you modified in the WAM	To preserve modification, select options
Descriptions of most WAM objects	Preserve description
Descriptions of domains	Preserve description Preserve domain
Descriptions of business rules	Preserve description Preserve business rule
Annotations of most WAM objects	Preserve annotation
Annotations of domains	Preserve annotation Preserve domain
Annotations of business rules	Preserve annotation Preserve business rule

Specific properties

What you modified in the WAM	To preserve modification, select options
Column data types (including length and precision)	Preserve data type
Domain data types	Preserve data type Preserve domain
Check parameters for columns	Preserve check
Check parameters for domains	Preserve check Preserve domain
Mandatory parent property for references	Preserve cardinality
Cardinality for references	Preserve cardinality
Mandatory option for columns	Preserve cardinality
Mandatory option for domains	Preserve cardinality Preserve domain
Domain names and codes	Preserve domain
Attachment of columns to domains	Preserve domain

Preserving a modified primary key If you modify a primary key in a WAM, then regenerate the WAM from a CDM, the modified primary key is not preserved. If you want to preserve a modified primary key, you need to modify the identifier in the CDM before regeneration.

Business rules

What you modified in the WAM	To preserve modification, select options
Names and codes of business rules of all types	Preserve business rule
Names and codes of a particular types of business rule	Preserve business rule Definition rule, Fact rule, Formula rule, and/or Validation rule
Types of business rules	Preserve business rule
Expressions of business rules of all types	Preserve business rule
Expressions of particular types of business rule	Preserve business rule Definition rule, Fact rule, Formula rule, and/or Validation rule
Descriptions of business rules	Preserve description Preserve business rule
Annotations of business rules	Preserve annotation Preserve business rule

Deletion

What you deleted from the WAM	To preserve deletion, select options
WAM objects (business rules, domains, tables, columns, references, indexes, keys, views, triggers, extended attributes)	Preserve graphics

Generating a WAM from a global CDM

• To generate a WAM from a global CDM:
  1. Open a CDM.

  2. Select Dictionary-->Generate WAM.

     A Generation dialog box appears.

     

  3. Select a target database from the Database Name dropdown listbox.

  4. Type a WAM filename.

     or

     Click the File button to select a file from a standard Windows Open dialog box.

  5. Select Generation parameters.

  6. If you select Preserve Modifications, click the Preservation tab.

     Select preservation options.

     

  7. Click OK.

     If you selected Check Model, the CDM is checked. A Message window displays the results of the generation.

  8. Click OK.

     If you selected Check Model and the CDM contains errors, you cannot generate the WAM and the CDM remains in the model window. In all other cases, a WAM appears in the model window.

Generating a WAM from a CDM submodel

If a CDM contains submodels, you can limit WAM generation to a single submodel. This option is useful when different designers own submodels of the same CDM, because designers can generate their submodels independently from others.

The result is an independent WAM.

• To generate a WAM from a CDM submodel:
  1. Open a CDM submodel.

  2. Select Dictionary-->Generate WAM.

     A dialog box asks you to select a generation option.

     

  3. Click Generate the Current Submodel.

     A Generation dialog box appears.

     

  4. Select a target database from the Database Name dropdown listbox.

  5. Type a WAM filename.

     or

     Click the File button to select a file from a standard Windows Open dialog box.

     By default, PowerDesigner takes the name of the CDM and adds a WAM extension.

  6. Select Generation mode parameters.

  7. If you select Preserve Modifications, click the Preservation tab.

     Select preservation options.

  8. Click OK.

     If you selected Check Model, the CDM is checked. A Message window displays the results of the generation.

  9. Click OK.

     If you selected Check Model and the CDM contains errors, you cannot generate the WAM and the CDM remains in the model window. In all other cases, a WAM appears in the model window.