Capítulo 8. Analizar Estructuras de Datos

Comparing Data Models

You can compare two data models to view the differences and similarities. You can compare a database

to a Power*Architect project or to another database.

The data model comparison provides you with a description of the two data models, highlighting their

differences and similarities, which you can copy into a document or save to a text file. You can also use

the data model comparison to generate and run a SQL script that will update the older database to match

the newer data model.

To compare two data models:

1.

Click on the top toolbar, or select Tools » Compare DM. The Compare Data Models dialog box

appears.

2. In the Compare Older and With Newer sections, select the data models you want to compare.

• Select Current Project - Include an open Power*Architect project in the comparison. The data model

currently in the playpen will be used.

• Physical Database - Include an existing database in the comparison. You must also select the

connection Power*Architect will use to connect to the database. For more information, see the section

called “Setting up Database Connections” .

• From File - Include an existing Power*Architect project in the comparison. Click Choose and select

the project.

Note: If you want to switch the items you've selected in the Compare Older and With New sections,

click Swap.

Analyzing Data Structures

46

3. In the Output Format area, select whether you want to create a SQL script or an English comparison.

4. Select the Suppress similarities check box if you want to include only the differences in the output.

5. Click Start. The data model comparison is created.

Note: The Start button is only available if both data models in the comparison are valid.

See the following sections for details on the information shown in the data comparison.

Data Model Comparison with English

Descriptions

If you chose English descriptions as the output format, the older and newer data models are shown sideby-

side. You can copy the results to the clipboard or save them to a text file.

The comparison includes descriptions to make the older data model the same as the newer data model.

The components are also colour coded to indicate similarities and differences.

The following table summarizes the meaning of the colour codes used in the data model comparison:

Colour Description

Black The component exists in both data models.

Green The component exists in this data model only.

Red The component does not exist in this data model but

does exist in the other data model.

Blue The component is a column and is on different keys

in the two data models.

Data Model Comparison in SQL Script

If you chose SQL script as the output format, a script is created to make the older data model the same as

the newer data model. You can copy the script to the clipboard or save it to a text file.

To run the script and apply the changes to the older database, click Execute.

Note: The Execute button is only available if the older database has a valid database connection. For more

information, see the section called “Setting up Database Connections” .

Profiling Data

Profiling allows you to view a summary of the data in a database. You can use profiles to quickly learn

the characteristics of data in an unfamiliar database. You can also use profiles for activities such as

database optimization and data migration. When you create a profile, the results are saved as part of the

Power*Architect project.

Note: Power*Architect contains two different menu items related to profiling. Use Profile » Profile only

when you want to create a new profile. If you want to view existing profiles, use Window » Profile

Manager. (The profile manager window is similar to the download manager window in a web browser.)

Analyzing Data Structures

47

Setting the Profile Mode

You can select the mode used to create a profile.

1. Select File » Project Settings.

2. In the Profile Creator Mode list, select one of the following options:

• Remote Database - This mode sends a query to the database and the database calculates all of the

statistics. This system works well over a large network because very little data is transferred.

Warning: Profiling moderate-to-large tables (for example, with over 250,000 rows) remotely will put

a significant demand on the database server's resources and may impact the database performance

for other users.

• Local Reservoir - This mode transfers all of the data to the local computer and then samples and

processes the data there. This works well over a fast network. This option is still experimental and

may cause an out of memory error when profiling large tables.

3. Click OK.

Creating a Profile

To create a profile:

1. Connect to the database you want to profile. For more information, see the section called “Setting up

Database Connections” .

2. In the database tree, select the tables you want to profile. (You can also select a column. If you do, a

profile will be created for the entire table.)

3.

Click in the top toolbar.

Alternate methods:

• Select the tables you want to profile, then select Profile » Profile.

• Right-click a table or column in the database tree, then click Profile.

The Table Profiles window opens. The new profile is listed in the window, along with previous profiles

you've created for the project.

Analyzing Data Structures

48

4. You can view details about each profile in the Table Profiles window. For more information, see the

section called “Viewing Profile Details” .

Note: To create a new profile of the same table, select the table in the Table Profiles window and click

. The previous profile will be retained as well. (Power*Architect will connect to the source database

to create the new profile, regardless of the profile mode you're using.)

Viewing Profile Details

To view profile details:

1. If the Table Profiles window is not already open, select Window » Profile Manager.

2. You can use the Search box and Order by options to find a profile.

3. To view details for all profiles, click View All.

4. To view details for some profiles only, select one or more profiles in the window, then click View

Selected.

You can view the profile details as a graph or table. For more information, see the section called “Using

Profile Graph View” and the section called “Using Profile Table View” .

Using Profile Graph View

To view the profile results in a graph:

• Click the Graph View tab.

Analyzing Data Structures

49

• On the left side of the window, select a column.

The column statistics are shown in the centre of the window. The most common values and their

frequency within the table are also shown.

The pie chart on the right side of the window shows the frequency of the most common values in the

column.

Note: You can set the number of common values to include in the comparison. For more information,

see the section called “Defining Project Settings” .

• To save the profile results in CSV, PDF or HTML format, click Save.

Using Profile Table View

To view the profile results in a table format:

1. Click the Table View tab.

2. To narrow the results, use the Search box in the top-right corner.

3. To sort a column in ascending or descesending order, click the column header.

4. In the Most Frequent column, hover over a cell to view the value and frequency of the most common

items in the column.

Analyzing Data Structures

50

5. To save the profile results in CSV, PDF or HTML format, click Save.

Deleting Profiles

To delete a profile:

1. If the Table Profiles window is not already open, select Window » Profile Manager.

2.

To delete a profile, click beside the profile.

3. To delete all the profiles, click Delete All.

Saving Your Profile Results in a PDF

You can easily create a PDF document that presents your profile results in an attractive format.

1. Create one or more profiles (see the section called “Creating a Profile” ).

2. Select Window » Profile Manager.

3. In the Table Profiles window, select the profiles you want to include in the PDF, then click View

Selected (see the section called “Viewing Profile Details” ). Or click View All to include all of the

profiles in the PDF.

4. Click Save.

5. Select PDF as the file type and enter a filename, then click Save.

Creating a Visual Mapping Report

When you create a data model using reverse engineering, you can create a report listing the source tables

used for the tables in the data model. You can export this report to a CSV (comma-separate values) file.

1. Select ETL » Visual Mapping Report.

2. To save the report to a CSV file, click Export to CSV.

Exporting Column Mappings

When you create a data model using reverse engineering, you can export a CSV (comma-separated values)

file describing the source-to-target column mappings between the original database and the data model

you created in Power*Architect.

1. Open the project containing the data model you want to use.

2. Select ETL » Export CSV. The Save dialog box appears.

3. Select the location and filename for the CSV file, then click Save.