Data Modeling and Partitioning

Data Modeling

• Data modeling is a process that requires quality control, and continuous improvement practices should be employed.
• Techniques such as time-to-value, support costs, and data model quality validators can be used to evaluate the model for correctness, completeness, and consistency.

Partitioning for Performance

• Partitioning on a date key is recommended, especially when facts contain many optional dimensional keys (sparse).
• When partitioning on a date key is not possible, a study is required based on profiled results and workload analysis to propose and refine the subsequent partitioning model.

Creating Views

• Views can be used to control access to certain data elements, or to embed common join conditions or filters to standardize common objects or queries.
• Views themselves should be requirements-driven, and in many cases, they will need to be developed via a process that mirrors the development of the LDM and PDM.

Reverse Engineering

• Reverse engineering is the process of documenting an existing database.
• The PDM is completed first to understand the technical design of an existing system, followed by an LDM to document the business solution that the existing system meets, and then the CDM to document the scope and key terminology within the existing system.

Data Model Patterns

• Data model patterns are reusable modeling structures that can be applied to a wide class of situations.
• There are elementary, assembly, and integration data model patterns.
• Elementary patterns are the ‘nuts and bolts’ of data modeling, and include ways to resolve many-to-many relationships, and to construct self-referencing hierarchies.
• Assembly patterns represent the building blocks that span the business and data modeler worlds.
• Integration patterns provide the framework for linking the assembly patterns in common ways.

Industry Data Models

• Industry data models are data models pre-built for an entire industry, such as healthcare, telecom, insurance, banking, or manufacturing.
• These models are often both broad in scope and very detailed, and can be purchased through vendors or obtained through industry groups.
• Any purchased data model will need to be customized to fit an organization, as it will have been developed from multiple other organizations’ needs.

Data Modeling and Data Models

• Data modeling is most frequently performed in the context of systems development and maintenance efforts, known as the system development lifecycle (SDLC).
• Data modeling is about the process of definition, and it is important to understand the vocabulary that supports the practice.

Entities and Attributes

• Dependent entities have at least one identifying relationship, where the primary key of the parent (the entity on the one side of the relationship) is migrated as a foreign key to the child’s primary key.
• In non-identifying relationships, the primary key of the parent is migrated as a non-primary foreign key attribute to the child.

Domain

• In data modeling, a domain is the complete set of possible values that an attribute can be assigned.
• A domain may be articulated in different ways, and provides a means of standardizing the characteristics of the attributes.
• All values inside the domain are valid values, and those outside the domain are referred to as invalid values.

Conformed Dimensions and Facts

• Conformed dimensions are built with the entire organization in mind, allowing these dimensions to be shared across dimensional models, due to containing consistent terminology and values.
• Conformed facts use standardized definitions of terms across individual marts, and different business users may use the same term in different ways.

Object-Oriented (UML)

• The Unified Modeling Language (UML) is a graphical language for modeling software.
• The UML class model specifies classes (entity types) and their relationship types.
• The UML class model has a variety of notations, of which one concerns databases.