Chapter Eight. Step 8: Database Design

Chapter Overview

This chapter covers the following topics:

• Things to consider about database design

• The differences in database design philosophies and in best design practices for operational databases and for BI target databases

• The multidimensional design premise of aggregation and summarization

• Basic explanations of a star schema and a snowflake schema

• Aspects of physical database design, including implementation options (such as free space and buffer space), physical dataset placement, partitioning, clustering, indexing, reorganizations, backup and recovery, and parallel query execution

• Brief descriptions of the activities involved in database design, the deliverables resulting from those activities, and the roles involved

• The risks of not performing Step 8

Things to Consider

Reports and Queries

What common reporting patterns exist across departments?

What level of detailed data will the business people require for drill-down queries?

How much ad hoc querying against detail data do we project will occur?

How many reporting dimensions should we consider? What are they?

How many new dimensions may have to be added in the future?

Design Considerations

Should we store aggregated and summarized data?

How much concurrent usage of the data should we expect?

How big will the BI target databases be? What are the projected data volumes and growth factors?

How much historical data will we keep?

What will be the frequency of loads?

Will the databases need to be distributed?

What are the availability requirements?

Performance Considerations

What are the performance requirements?

How will we cluster the tables? By the date column or by other columns ?

What tables should be co-located?

How will we partition the tables? Will we partition by date?

What types of indexing algorithms should we use (B-tree, hash, inverted file, sparse, binary)?

Can we run multiple operations (queries, loads) in parallel?

Selection of Database Management System

Which database management system (DBMS) are we using for our existing applications? Will we use the same DBMS for the BI target databases?

Will our current DBMS scale to the expected size?

Are we satisfied with the current DBMS? If not, what are we doing about it?

Will we need to license (buy) another DBMS?

Staffing

What skills do we have available to design the BI target databases?

Do we have enough database administrators?

Can one database administrator be dedicated to this project full-time ?

Does he or she have multidimensional design skills? If not, how soon can he or she receive training?

Will we need to hire a consultant to mentor the database administrator and the team?

BI decision-support requirements for aggregated and summarized data have introduced a new type of database design and a new way of storing data. This new multidimensional database design schema, coupled with new BI technology, supports the ability to "slice and dice" information in myriad ways for reporting and analysis purposes. In order to implement the capabilities of slicing and dicing, database administrators and developers must learn new design techniques and must acquire a new way of working with the databases. They must begin by understanding the ways in which the data will be accessed. Data can be accessed either in a conventional way (usually detailed records retrieved with Structured Query Language [SQL] queries) or in a multidimensional way (usually summarized records retrieved with an online analytical processing [OLAP] tool). Multidimensional data storage and data access techniques, which support slicing and dicing, allow information to be viewed from a variety of perspectives, such as Products by Factory by Market Segment and Market Segments by Product by Factory.