Executive summary
Implementing an industry model can help accelerate projects in a wide variety of industry sectors by reducing the effort required to create a database design optimized for data warehousing and business intelligence.
IBM Industry Models cover a range of industries which include banking, healthcare, retail, and telecommunications. The example that is chosen in this document is from a subset of the IBM Insurance Information Warehouse model pertaining to Solvency II (SII) regulations.
Many of the most important partitioned database design decisions are dependent on the queries that are generated by reporting and analytical applications. This paper explains how to translate reporting needs into database design decisions.
This paper guides you through the following recommended process for transforming the logical data model for dimensional warehousing into a physical database design for production use in your environment. The key phases of this process are:
- Create a subset of the data model subset from the supplied logical data model
- Prepare the physical data model for deployment as a partitioned DB2 database
- Refine the physical data model to reflect your reporting and analytics needs
- Optimize the database architecture and design for a production environment
Implement a database architecture that is aligned with best practices for warehousing before tuning your database design to reflect performance needs for reports and queries. When you have created and populated the test database can you further optimize the database design to reflect the anticipated query, ingest, and maintenance workload.
A poorly designed database and architecture can lead to poor query performance and a need for outages to accommodate maintenance operations. Using the recommendations in this paper can help you transform an IBM Industry Model dimensional warehouse solution into a partitioned database that is ready for production use.
Introduction
IBM Industry Models provide you with an extensive and extensible data model for your industry sector. Use the logical data model as provided by IBM to build a physical model that is customized for your reporting requirements then deploy and populate a best-practice partitioned‐database production environment.
This paper does not discuss data modeling concepts but instead focuses on what you must do to transform a non‐vendor‐specific logical data model into a best‐practice production DB2 partitioned database.
This paper is targeted at people involved in transforming the dimensional data warehouse logical data model into a production partitioned database that is based on DB2® Database for Linux®, UNIX®, and Windows® software v10.1.
The goal of the IBM Insurance Information Warehouse model in addressing Solvency II is to facilitate reporting in line with European Union directives and internal business requirements. A subset of tables from the dimensional layer, together with a sample SII‐ based Cognos report, is referenced throughout the paper. The test environment used is described in Appendix A.
The first section of this paper looks at the deployment pattern and the main design challenges that you must address when you implement an industry model. The process of identifying and manipulating the components of the industry model that are relevant to your business is outlined.
The second section of the paper looks at transforming the logical data model into a physical data model that is aligned with best practices for a partitioned DB2 database.
The third section of the paper shows how to translate reporting requirements into database design choices that help shape the physical data model.
The fourth section of the paper describes how to optimize the database design to reflect the specific needs of your production environment. The temporal feature and continuous data ingest utility, both introduced in DB2 Version 10.1, are described in the context of how they might influence your database design decisions.
The IBM PureData for Operational Analytics System implements best practices for data warehouse architecture that uses DB2 software. The shared‐nothing architecture of the IBM PureData for Operational Analytics System provides a platform that emphasizes performance, scalability, and balance. The paper “Best Practices: Physical database design for data warehouse environments”, referenced in the Further reading section, covers the recommendations for data warehouse database design in detail.
Download the full report for more on transforming IBM Industry Models into a production data warehouse.
Download the report to get started!#Db2