Successful database implementations do not mean the end of change for database designs. Change happens both for normalized operational databases and for business intelligence databases sporting star schema arrangements. When examining new requirements and their ensuing changes, pressures mount from users and management for “quick fixes” and "workarounds” that minimize disruptions and still satisfy current needs. Quick fixes may bear with them the costs of either immediate or future lost flexibility. As an example, imagine a functional star schema in an organization that encounters a new requirement for adding household information to already existing data. The household information may contain dynamic new traits added or dropped quickly, effectively described within the source data by an account identifier, a code identifying a trait, and a value associated with the code (such as account number = “1111;” code = “HH_Income;” and value = “50K-75K”).

Developers might argue for simply adding in a table with these three elements “hanging off” a fact or dimension table containing the account number. Then assume another request from the developers occurs for a "decode” table providing more detailed names or descriptions of the code values. Adding in the one or two tables keeps everything else unchanged and would allow developers to create output using the new household information. Developers see such changes as an easy answer because they can add new values without altering data structures and program easily against such reference tables. The resulting set of structures provides all the requested data, unfortunately with the new household data not as well integrated into the pre-existing star schema. The tools used for reporting against an expected multidimensional design may have some challenges to overcome. Despite the truth of the ease of change and use that typifies the value of relational structures with such ease-of-handling methods, implementing these ease-of-use approaches abandons the star schema and subsequently ends up attaching normalized components to the star.

A star schema offers flexibility of changes by expanding the schema in star-like ways, through additional columns on a dimension and/or on the fact, and supporting easily created aggregates at various levels. Anticipating potential changes in the overall architecture can facilitate the management of other changes. A drastic change results from the need to rebuild a star entirely, because an additional dimension or column changes the fundamental granularity of the fact. While hopefully rare, the potential for fact granularity changes exist within any organization, which means that following the practice of keeping fact data at the lowest possible granularity helps avoid such later changes. But when faced with the need for a drastic change in fact granularity, then either the architecture must have stored copies of useful staged or source data to rebuild everything as far back as necessary, or everyone must have agreed previously to only populate such drastic changes on a going-forward basis. Properly integrating the newly requested household data in a star schema may occur by adding in a household dimension, with each code and value driving a single column so ten different traits would result in ten different columns. Depending on the level of aggression in the rate of turnover on the kinds of traits, then a designer may explore the use of a bridge or helper table to manage the household traits complexity in relating to the fact rows.

As a powerful tool, the star schema provides for much end-user flexibility in online analytics. Multidimensional designs effectively aid users by allowing quick insights to and easy manipulation of the data, which explains the pervasiveness of star structures. But adding in non-star elements actually corrupts the star structure and results in the loss of the star’s internal power and ease of facilitating end-user play. Each data modeler should consider the possibility of a change adversely impacting the star’s natural simplicity before agreeing on the implementation of any changes to the schema. Designers responsible for star schema structures must maintain the integrity of a multidimensional approach.

 

About the Author:

Todd Schraml is senior data architect and manager of ETL at Innovative Health Strategies, Inc. He can be reached at tschraml@ihsiq.com.