You are previewing Data Modeling Made Simple with PowerDesigner®.
O'Reilly logo
Data Modeling Made Simple with PowerDesigner®

Book Description

Data Modeling Made Simple with PowerDesigner will provide the business or IT professional with a practical working knowledge of data modeling concepts and best practices, and how to apply these principles with PowerDesigner. You'll build many PowerDesigner data models along the way, increasing your skills in first the fundamentals and later in the book the more advanced features of PowerDesigner. This book combines real-world experience and best practices to help you master the following ten objectives:

  1. You will know when a data model is needed and which PowerDesigner models are the most appropriate for each situation

  2. You will be able to read a data model of any size and complexity with the same confidence as reading a book

  3. You will know when to apply and how to make use of all the key features of PowerDesigner

  4. You will be able to build, step-by-step in PowerDesigner, a pyramid of linked data models, including a conceptual data model, a fully normalized relational data model, a physical data model, and an easily navigable dimensional model

  5. You will be able to apply techniques such as indexing, transforms, and forward engineering to turn a logical data model into an efficient physical design

  6. You will improve data governance and modeling consistency within your organization by leveraging features such as PowerDesigner's reference models, Glossary, domains, and model comparison and model mapping techniques

  7. You will be able to use PowerDesigner to make your data model(s) the center of all your data management initiatives: MDM, Data Warehousing, Business Intelligence, ERP, Data Governance, SOA/XML, Agile development

  8. You will know how to integrate your PowerDesigner models with externally-managed files, including the import and export of data using Excel and Requirements documents

  9. You will know where you can take advantage of the entire PowerDesigner model set, to increase the success rate of corporate-wide initiatives such as business intelligence and enterprise resource planning (ERP)

  10. You will understand the key differentiators between PowerDesigner and other data modeling tools you may have used before

Table of Contents

  1. Contents at a Glance
  2. Contents
  3. Acknowledgements
    1. From George
    2. From Steve
  4. Foreword
  5. Read me first!
    1. How to get the Most out of this Book
    2. Icons Used in this Book
    3. Conventions Used in this Book
    4. In Summary
  6. SECTION I
  7. Data Modeling Introduction
  8. CHAPTER 1
  9. What is a data model?
    1. Wayfinding Explained
    2. Data Model Explained
    3. Fun with Ice Cream
    4. Fun with Business Cards
    5. EXERCISE 1: Educating Your Neighbor
  10. CHAPTER 2
  11. Why do we need a data model?
    1. Communication
      1. Communicating During the Modeling Process
      2. Communicating After the Modeling Process
    2. Precision
    3. Data Model Uses
    4. EXERCISE 2: Converting the Non-Believer
  12. SECTION II
  13. Data Model Components
  14. CHAPTER 3
  15. What are entities?
    1. Entity Explained
    2. Entity Types
    3. EXERCISE 3: Defining Subject Areas
  16. CHAPTER 4
  17. What are data elements?
    1. Data Element Explained
    2. Data Element Types
    3. Domain Explained
    4. EXERCISE 4: Assigning Domains
  18. CHAPTER 5
  19. What are relationships?
    1. Relationship Explained
    2. Relationship Types
    3. Cardinality Explained
    4. Recursion Explained
    5. Relationship Descriptions
    6. Subtyping Explained
    7. EXERCISE 5: Reading a Model
  20. CHAPTER 6
  21. Why are names and definitions important?
    1. Clarity
    2. Completeness
    3. Accuracy
    4. Lack of Ambiguity
    5. Not Only Entities and Data Elements
    6. Especially Relationships
    7. The Need for Naming Standards
  22. CHAPTER 7
  23. What are keys?
    1. Key Explained
    2. Candidate Key Explained
    3. Primary and Alternate Keys Explained
    4. Surrogate Key Explained
    5. Foreign Key Explained
    6. EXERCISE 6: Clarifying Customer Id
  24. SECTION III
  25. Data Modeling in PowerDesigner
  26. CHAPTER 8
  27. Why do we need a data modeling tool?
    1. Why not use a drawing tool for data modeling?
    2. Key Features Needed in a Data Modeling Tool
      1. Core Modeling
      2. Usability
      3. Interfaces and Integration
      4. Tool Management and Communication
      5. Collaboration
    3. EXERCISE 7: Examining Your Current Data Modeling Tool
  28. CHAPTER 9
  29. What can PowerDesigner do for me?
    1. What is metadata?
    2. PowerDesigner metadata scope
    3. PowerDesigner Data Models
    4. Data Modeling Feature Comparison
    5. Core Modeling
      1. ***
        1. Support For Multiple Types Of Data-Related Models
        2. Linking and Syncing Models
        3. One File Per Model
        4. Multiple Notations Supported
        5. Subsets Of A Data Model
        6. Model Validation
        7. Naming Standards
        8. Spreadsheet-like editing
        9. Comprehensive Database Support
        10. Dependencies Between Model Objects
        11. Internal and External Dependencies
        12. Versioning
        13. Automation
        14. Assertion Statements
        15. Reference Models
    6. Usability
      1. ***
        1. Diagram Layout
        2. Flexible Facilities For Editing Objects
      2. Role-based User Interface
    7. Interfaces And Integration
      1. Importing Existing Data Models
      2. Round-trip Engineering
      3. Supporting Bulk Updates Via Excel
      4. Generating New Models
      5. Comparing and Merging Models
      6. Generating Test Data
      7. Displaying Data from a Database
      8. Integration With Development Environments
      9. Generate Data Replication Scripts
    8. Tool Management And Communication
      1. Extending PowerDesigner’s Capabilities
      2. Reporting Facilities
      3. Access via a Portal
      4. Viewing Models for Free
    9. Collaboration
      1. Access Management
      2. Sharing Best Practices
  30. CHAPTER 10
  31. How can we work within the PowerDesigner environment?
    1. Fundamentals of PowerDesigner
    2. The Welcome Page
    3. The Building Blocks
    4. Not Just a Load Of Symbols
    5. Names and Codes in PowerDesigner Objects
    6. Interface Overview
    7. Toolbars
    8. Organizing Views
      1. Mouse-clicking
      2. Dragging Views
      3. Promoting a View
      4. Restoring a Window
      5. Resizing a Window
      6. The View Toolbar
    9. Changing Focus
    10. Re-arranging the Canvas
    11. Working with Objects
    12. Looking at the Demo Workspace
    13. The Toolbox
    14. Working in the Browser
    15. Contextual Menus
    16. Object Property Sheets
      1. Property Types
      2. More Tabs for an Entity
    17. Common Properties
      1. Do not generate me
    18. Many Ways of Doing Things
    19. Object Lists
      1. Opening a List Of Objects
      2. Organizing a List of Objects
      3. The Toolbar
      4. Customizing a List of Objects
      5. Working With a List of Objects
      6. Creating Objects in a List of Objects
    20. Moving, Copying and Deleting Objects
    21. Undoing Things
    22. Working with Diagrams
      1. Creating a Diagram
      2. Creating a Diagram from a Selection
      3. Opening Diagrams
      4. Viewing Diagrams
      5. Identifying Models
      6. Changing the Default Diagram
      7. Getting Ready To Print
      8. Printing a Diagram
      9. Deleting a Diagram
    23. Working with Symbols
      1. Creating and Drawing Symbols
      2. Automatic Link Routing
      3. Selecting, Editing, and Resizing Symbols
      4. Rerouting Link Symbols Manually
      5. Bending and Straightening Link Symbols
      6. Connecting a Link Symbol to a Different Object
    24. Renaming Objects and Symbols
      1. The Two-click Method for Renaming an Object
      2. The Three-click Method for Renaming an Object
      3. The <F2> Method for Renaming an Object
      4. The Property Sheet Method for Renaming an Object
      5. The Two-handed Method for Renaming an Object
    25. Accessing Objects from a Diagram
    26. Showing and Hiding Symbols
    27. Communicating Your Message
      1. Changing the Content of Symbols
      2. Formatting Symbols
      3. Get and Apply Format
    28. Laying Out Diagrams
    29. Graphical Synonyms
    30. Menus Depend on Models
    31. Workspaces
    32. Models
      1. Creating a Model
      2. Removing an Unsaved Model
      3. GUIDs
      4. Saving Models
      5. Backup Files
      6. Deleting a Model
    33. Projects
      1. Creating a Project
      2. Adding Models to a Project
      3. Project Diagrams
      4. Using Frameworks
    34. Opening a Model, Project, or Workspace
    35. Moving Models Around
      1. Absolute vs. Relative File Shortcuts
    36. Partitioning a Model
      1. Packages
      2. When to use Packages in Data Models
    37. Model Extensions
      1. Attaching Extensions to an Existing Model
    38. Finding Things Again
      1. Searching Local Models
      2. Searching the Repository
      3. Searching via the Web Portal
    39. EXERCISE 8: Creating your own Workspace, Project and Models
  32. CHAPTER 11
  33. How do I create entities in PowerDesigner?
    1. Drawing New Entities
    2. Changing Model Notation
    3. Creating Entities in the Browser
    4. Editing via the List of Entities
    5. Exporting the Diagram Image
    6. Object Property Sheets
    7. Importing Entities from Excel Files
    8. EXERCISE 9: Creating LDM Entities in PowerDesigner
  34. CHAPTER 12
  35. How do I create data elements in PowerDesigner?
    1. Standard Properties for Data Elements
    2. Domains in PowerDesigner
      1. Enforcing Non-divergence from a Domain in a Data Model
      2. The Result of our Edits
    3. Data Items in PowerDesigner
      1. Controlling Uniqueness and Reuse of Data Items
      2. Should we reuse Data Items within a model?
      3. When to Use Domains instead of Data Items
      4. Incorporating Data Items in Governance
      5. Model Checks
    4. Attributes and Columns in PowerDesigner
      1. Creating a Conceptual Attribute
      2. Creating a Logical Attribute
      3. Replica Attributes
      4. Replica Entities
      5. Replica Data Items
      6. Replica Data Items in one Model
      7. Listing Replicas
      8. Display Preferences for Attributes and Columns
      9. Viewing Generation Links
      10. See “Creating Objects in a List of Objects”
      11. Domain Dependencies
    5. The PowerDesigner Glossary
    6. Transferring Properties
      1. Synchronization
      2. Generation
    7. Managing Allowable Values
    8. Dependency Matrices
      1. Advanced Dependencies
    9. EXERCISE 10: Creating a new Conceptual Data Model in PowerDesigner
  36. CHAPTER 13
  37. How do I create relationships in PowerDesigner?
    1. Creating Relationships in PowerDesigner
      1. Creating a Relationship from the Palette
      2. Creating a Relationship from a List
      3. Creating a Relationship in the Browser
    2. Relationship Properties
      1. The Cardinalities Tab
      2. The Importance of Role Names
      3. Assertion Statements
    3. An Entity’s Relationships
    4. Display Preferences for Relationships
    5. Routing Relationships
    6. Graphical Synonyms
    7. Many-to-many relationships
    8. Resolving Many-to-many Relationships
    9. Recursive (Reflexive) Relationships
    10. Subtypes in PowerDesigner
      1. Inheritance Properties
      2. Subtypes in Barker Notation
    11. Including Missing Relationships
    12. EXERCISE 11: Creating Relationships in PowerDesigner
    13. EXERCISE 12: Naming an Associative Entity
  38. CHAPTER 14
  39. How do I create keys in PowerDesigner?
    1. Creating Candidate Identifiers
      1. In the Entity Property Sheet
      2. In the Browser
      3. In an Entity Symbol
      4. In a Relationship Property Sheet
      5. Via an Excel Import
    2. Entity Identifier Properties
    3. Object Lists
    4. Identifiers on Entity and Table Symbols
    5. Changing Identifier Content
    6. Identifier Migration Along Relationships
    7. Attribute Migration Settings
    8. Migrating a Different Identifier
    9. Naming Your Identifiers
    10. Attributes and Columns in Identifiers and Keys
    11. EXERCISE 13: Constructing Identifiers
      1. Create Identifiers via the Entity Property Sheet
      2. The Quickest Way to Create a Primary Identifier
      3. Convert a Candidate Identifier into a Primary Identifier
      4. Make a Relationship Dependent
      5. Change the Name of a Migrated Attribute
      6. Make a Migrated Attribute ‘primary’
      7. Extending a Composite Identifier
      8. Editing the Identifier via the Entity Symbol
      9. Creating a Candidate Identifier via the Entity Symbol
      10. The Finished Diagram
  40. SECTION IV
  41. Subject Area, Logical, and Physical Data Models
    1. The Data Model Pyramid
    2. Which PowerDesigner model types should I use?
  42. CHAPTER 15
  43. What are subject area models?
    1. Subject Area Explained
    2. Subject Area Model Explained
    3. Types of Subject Area Models
      1. Business Subject Area Model (BSAM)
      2. Application Subject Area Model (ASAM)
      3. Comparison Subject Area Model (CSAM)
    4. How to Build a Subject Area Model
    5. Subject Area Models in PowerDesigner
      1. CDM Settings
      2. Approaches for Creating a SAM
      3. Presenting the SAM
      4. Linking the SAM to Other Data Models
        1. Searching the Links
      5. Relationship Names in the SAM
      6. Useful SAM Model Checks
        1. All Objects
        2. Entity
        3. Relationship
        4. Inheritance
      7. Additional Information in the SAM
      8. Promoting and Sharing the SAM
    6. EXERCISE 14: Building a Subject Area Model in PowerDesigner
    7. EXERCISE 15: Building a Subject Area Model
  44. CHAPTER 16
  45. What are logical data models?
    1. Logical Data Model Explained
    2. Comparison of Relational with Dimensional Logical Models
    3. Normalization Explained
      1. Initial Chaos
      2. First Normal Form (1NF)
        1. Resolve Repeating Data Elements
        2. Resolve Multi-valued Data Elements
      3. Second Normal Form (2NF)
      4. Third Normal Form (3NF)
    4. Abstraction Explained
    5. Dimensional Modeling FAQ
    6. Logical Data Models in PowerDesigner
      1. LDM Settings
      2. Approaches for Creating a LDM
      3. Presenting the LDM
      4. Linking the LDM to Other Data Models
      5. Dimensional LDM Notation
      6. Relationship Names in the LDM
      7. Normalization in the LDM
      8. Useful LDM Model Checks
        1. Domain
        2. Entity
        3. Attribute
        4. Relationship
        5. Inheritance
      9. Additional Information in the LDM
      10. Promoting and Sharing the LDM
    7. EXERCISE 16: Creating a Logical Data Model in PowerDesigner
    8. EXERCISE 17: Modifying a Logical Data Model in PowerDesigner
    9. EXERCISE 18: Modifying and Normalizing a Logical Data Model in PowerDesigner
      1. First Normal Form
      2. Second Normal Form
      3. Third Normal Form
      4. Abstraction
    10. EXERCISE 19: Creating a Dimensional LDM in PowerDesigner
      1. Complete the Relational LDM
      2. Transform the Relational LDM into a Dimensional LDM
      3. Add Entity and Attribute Stereotypes
  46. CHAPTER 17
  47. What are physical data models?
    1. Physical Data Model Explained
    2. Denormalization Explained
      1. Standard
      2. FUBES
      3. Repeating Groups
      4. Repeating Data Elements
      5. Summarization
      6. Star Schema
    3. Views Explained
    4. Indexing Explained
    5. Keys and Indexes in the PDM
    6. Partitioning Explained
      1. Snowflake
    7. When Reference Data Values Change
    8. Physical Data Models in PowerDesigner
      1. PDM Settings
      2. The Database Menu
      3. The Tools Menu
      4. Approaches for Creating a PDM
      5. The Physical Diagram Palette
      6. Creating a Reference from the Palette
      7. Reference Optionality & Cardinalities
      8. Creating a View
      9. Display Preferences
      10. SQL Preview
      11. Viewing Data
    9. Denormalization in PowerDesigner
      1. Collapsing Subtypes
    10. Reverse-engineering Databases
    11. Keeping the Model and Database Synchronized
    12. EXERCISE 20: Getting Physical with Subtypes in PowerDesigner
    13. EXERCISE 21: Denormalizing a PDM in PowerDesigner
  48. CHAPTER 18
  49. How can we connect these models in PowerDesigner?
    1. Generating Models
      1. Step 1
      2. Step 2
    2. Generating Model Objects
    3. Traceability Links
    4. The Mapping Editor
  50. SECTION V
  51. Consolidating What We’ve Learned
  52. CHAPTER 19
  53. How do we put all of the pieces together?
    1. Your Tasks
    2. Produce Subject Area Model
    3. Produce Relational LDM
    4. Produce Relational PDM
    5. Generate a SQL File
    6. Reverse Engineering
  54. SECTION VI
  55. More Great Stuff in PowerDesigner for Data Modelers
  56. CHAPTER 20
  57. What other data modeling features exist in PowerDesigner?
    1. Distribution of Reference Models
    2. Deploying an Enterprise Glossary
      1. Creating a Glossary From Existing Documentation
      2. Creating an Object in the Glossary
      3. How can we use the Glossary?
      4. Linking the Glossary to Existing Objects
    3. Managing Names and Codes
      1. Model Generation
    4. The Lineage of Data Elements
    5. Test Data
    6. Multidimensional Diagrams
    7. Generating Extraction Scripts
    8. Data Movement Modeling
  58. CHAPTER 21
  59. How do we get information in and out of PowerDesigner?
    1. Model Import
    2. Importing Requirements
    3. Excel Export
    4. Excel Import
      1. Exporting and Importing Lists of Values
      2. Using Excel Import to Create Surrogate Keys
    5. Exporting via the Clipboard
    6. Reporting
      1. Report
      2. List Report
      3. Multi-Model Report
    7. Exercise 22: Importing from Excel
    8. Exercise 23: Creating a List Report
  60. CHAPTER 22
  61. How can PowerDesigner help me improve data model quality?
    1. Data Model Scorecard
    2. Scorecard Categories
  62. CHAPTER 23
  63. What other components in PowerDesigner
  64. can be leveraged by data modelers?
    1. Collaboration and Standardization
      1. Repository
      2. Sharing Resources
        1. Deploying Shared Resource Files
      3. User Profiles
      4. Project and Model Templates
        1. Model Templates
    2. Linking with Business Analysis
      1. Capturing Requirements
      2. Business Rules in PowerDesigner
    3. Checking Spelling
    4. Repository Portal
    5. Automation and Simplification of Common Tasks
      1. Compare and Merge
      2. Checking a Model
    6. Impact and Lineage Analysis
      1. Impact Analysis Model
    7. Customization and Extension
      1. Extend the Scope of Model Information
        1. Automatically Attaching Extensions to New Models
      2. Customizing Menus, Toolbars, and Toolboxes
      3. Modifying DBMS Support
      4. Role-based UI
      5. Scripting
  65. SECTION VII
  66. Beyond Data Modeling in PowerDesigner
  67. CHAPTER 24
  68. What's different about the XML Physical Data Model?
  69. CHAPTER 25
  70. What other models are available in PowerDesigner?
  71. Bibliography
  72. APPENDIX A
  73. Answers to Exercises
    1. EXERCISE 1: Educating Your Neighbor
    2. EXERCISE 4: Assigning Domains
      1. Email Address
      2. Gross Sales Value Amount
      3. Country Code
    3. EXERCISE 5: Reading a Model
    4. EXERCISE 6: Clarifying Customer Id
      1. Document Uniqueness Properties
      2. Document the Characteristics of the Identifier
      3. Define the Customer
    5. EXERCISE 8: Creating your own Workspace, Project, and Models
    6. EXERCISE 12: Naming an Associative Entity
    7. EXERCISE 17: Modifying a Logical Data Model
      1. Option 1
      2. Option 2 (a bit more abstract)
    8. EXERCISE 20: Getting Physical with Subtypes in PowerDesigner
      1. Identity
      2. Rolling Down
      3. Rolling Up
  74. APPENDIX B
  75. PowerDesigner Quick Reference for Data Modelers
    1. Memory Joggers
    2. Standard Generation Options
    3. Shortcuts
    4. How to find out more about PowerDesigner, or get help from others
  76. APPENDIX C
  77. Glossary
    1. Data Modeling Terms
    2. PowerDesigner Terms
  78. Index