Data Modeling – Dimension Table Effective Practices

Database Table

Database Table

I’ve had these notes laying around for a while, so, I thought I consolidate them here.   So, here are few guidelines to ensure the quality of your dimension table structures.

Dimension Table Effective Practices

  • The table naming convention should identify it as a dimension table. For example:
    • Suffix Pattern:
      • <<TableName>>_Dim
      • <<TableName>>_D
    • Prefix Pattern:
      • Dim_<TableName>>
      • D_<TableName>>
  • Have Primary Key (PK) assigned on table surrogate Key
  • Audit fields – Type 1 dimensions should:
    • Have a Created Date timestamp – When the record was initially created
    • have a Last Update Timestamp – When was the record last updated
  • Job Flow: Do not place the dimension processing in the fact jobs.
  • Every Dimension should have a Zero (0), Unknown, row
  • Fields should be ‘NOT NULL’ replacing nulls with a zero (0) numeric and integer type fields or space ( ‘ ‘ ) for Character type files.
  • Keep dimension processing outside of the fact jobs

Related References

 

 

Common Information Integration Testing Phases

Over the years I have seen a lot of patterns for Information integration testing process and these patterns will not be an exhaustive list of patterns a consultant will encounter over the course of a career.

However, two most common patterns in the testing process are:

The Three Test Phase Pattern

In the three test phase pattern, normally, the environment and testing activities of SIT and SWIT are combined.

The Three Test Phase Pattern

The Three Test Phase Pattern

The Four Test Phase Pattern

In the four test phase pattern, normally, the environment and testing activities of SIT and SWIT are performed separately and, frequently, will have separate environments in the migration path.

The Four Test Phase Pattern

The Four Test Phase Pattern

Testing Phases

Unit Testing:

Testing of individual software components or modules. Typically done by the programmer and not by testers, as it requires detailed knowledge of the internal program design and code. may require developing test driver modules or test harnesses.

 System Integration Testing (SIT):

Integration testing – Testing of integrated modules to verify combined functionality after integration. Modules are typically code modules, individual applications, client and server applications on a network, etc. This type of testing is especially relevant to client/server and distributed systems. Testing performed to expose defects in the interfaces and in the interactions between integrated components or systems. See also component integration testing, system integration testing.

 Software Integration Test (SWIT)

Similar to system testing, involves testing of a complete application environment, including scheduling, in a situation that mimics real-world use, such as interacting with a database, using network communications, or interacting with other hardware, applications, or systems if appropriate.

 User Acceptance Testing (UAT):

Normally, this type of testing is done to verify if the system meets the customer specified requirements. Users or customers do this testing to determine whether to accept the application.  Formal testing with respect to user needs, requirements, and business processes conducted to determine whether or not a system satisfies the acceptance criteria and to enable the user, customers or other authorized entity to determine whether or not to accept the system.

Related References

 

 

Information Management Unit Testing

Information Management Unit Testing, UT, Unit Test

Information Management Unit Testing

 

Information management projects generally have the following development work:

  • Data movement software;
  • Data conversion software;
  • Data cleansing routines;
  • Database development DDL; and
  • Business intelligence and reporting analytical solutions.

Module testing validates that each module’s logic satisfies requirements specified in the requirements specification.

Effective  Practices

  1. Should focus on testing individual modules to ensure that they perform to specification, handle all exceptions as expected, and produce the appropriate alerts to satisfy error handling.
  2. Should be performed in the development environment.
  3. Should be conducted by the software developer who develops the code.
  4. Should validate the module’s logic, adherence to functional requirements and adherence to technical standards.
  5. Should ensure that all module source code has been executed and each conditional logic branch followed.
  6. Test data and test results should be recorded and form part of the release package when the code moves to production.
  7. Should include a code review, which should:
  • Focus on reviewing code and test results to provide additional verification that the code conforms to data movement best practices and security requirement; and
  • Verify that test results confirm that all conditional logic paths were followed and that all error messages were tested properly.

Testing Procedures

  1. Review design specification with designer.
  2. Prepare test plan before coding.
  3. Create test data and document expected test results.
  4. Ensure that test data validates the module’s logic, adherence to functional requirements and adherence to technical standards.
  5. Ensure that test data tests all module source code and each conditional logic branch.
  6. Conduct unit test in personal schema.
  7. Document test results.
  8. Place test data and test results in project documentation repository.
  9. Check code into code repository.
  10. Participate in code readiness review with Lead Developer.
  11. Schedule code review with appropriate team members.
  12. Assign code review roles as follows:
  • Author, the developer who created the code;
  • Reader, a developer who will read the code during the code review—The reader may also be the author; and
  • Scribe, a developer who will take notes.

Code Review Procedures

  1. Validate that code readiness review has been completed.
  2. Read code.
  3. Verify that code and test results conform to data movement best practices.
  4. Verify that all conditional logic paths were followed and that all error messages were tested properly.
  5. Verify that coding security vulnerability issues have been addressed.
  6. Verify that test data and test results have been placed in project documentation repository.
  7. Verify that code has been checked into code repository.
  8. Document action items.

Testing strategies

  1. Unit test data should be created by the developer and should be low volume.
  2. All testing should occur in a developer’s personal schema.

Summary

Unit testing is generally conducted by the developer who develops the code and validates that each module’s logic satisfies requirements specified in the requirements specification.