Data Warehouse Design
Practical Approach For Production-Quality Data Warehouses
In their own practice, WhereScape's founders discovered that the optimal methodology - one that ensures repeatable success in design, construction and operation of data warehouses and decision support systems environments, emphasizes five key phases, as described below.
The Design Phase
Dimensional models are, because of their inherent legibility, the essence of end user interaction with the data warehouse.
Normalized data models future proof data warehouses and are the key component for large scale enterprise data warehouses.
But models require a base - a place from which to start. While adhering theoretically to the notion that designs begin with an understanding of the decision-makers' informational needs, data warehouse practitioners often begin design work, in practice, by focusing on the source systems' data elements, operating on the assumption that the data elements in the source systems form the outer bounds of the design problem: what data can be made available to decision-makers?
The Prototype Phase
Prototyping a data warehouse design permits data warehouse designs to test, with some degree of accuracy, three important sets of assumptions:
- Assumptions about the adequacy of the inputs that led to the logical data model design - does this design provide decision-makers with the data elements they need in a form in which they can understand those elements?
- Assumptions about the adequacy of the extraction strategy - can the extraction and load be executed in a timely manner with minimal impact to the production systems?
- Assumptions about the business drivers and socio-technical factors associated with the project - will decision-makers accept the environment that has been designed? Will they be drawn into the project and want to make use of the system that will result from it?
Read More >>
The Deployment Phase
Traditionally there has been a high and hard wall between design and deployment tasks in data warehouse projects. Often, that wall has been institutionalized by consultants who, while capable of producing paper-based logical designs and architectures, lack the skill or the will to see those designs into production. And, as often, the toolsets used in the design process - crude combinations of spreadsheets, Entity Relationship (ER) diagramming tools and personal database management systems cobbled together because no commercial alternatives were available - have been unable to make the transition from logical designs and toy prototypes to real-world production systems, because they have no real understanding of the deployment environment at the technology or code levels.
Seen properly, deployment - or, rather, the ability to deploy - is implicit in every design decision made in a data warehouse project, from day one forward, and a data warehouse design tool that does not also prepare for deployment as designs change, grow and gain user acceptance is doing little more than setting up its users for a hard and swift fall.
Read More >>
The Operational Phase
Once in deployment, successful data warehouse and business intelligence systems quickly become embedded in the work practices of decision-makers: they simply can't do without their data. This translates directly into a more-or-less constant expectation that, whenever it is needed, the data warehouse will be up and running, fully-populated, and accurate within whatever time window decision-makers operate: the week, the day, even in some cases the hour.
Meeting the demand for the always-on data warehouse has traditionally resulted in huge, hidden labor costs - sometimes as much as 3 to 4 times the cost of the data warehouse project - as teams of data warehouse professionals who ought to be spending their time either enhancing the data warehouse or building new decision support environments instead spend that time tending one or a few existing systems: managing creaky scheduling infrastructure (or scheduling and executing extractions manually), debugging repeated failures in complex ETL logic that seemed so easy to build but is virtually impossible to manage in any reasonable way, or monitoring the database environment under the data warehouse for signs of trouble.
Read More >>
The Enhancement Phase
Rarely are the enhancements required by decision-makers technical in nature. Aside from performance-related issues that translate pretty directly into the need for new or modified indexing or aggregation strategies, the vast majority of enhancements are, in practice, requests for new data elements, modifications to existing data elements, or the creation of new, related analytical areas within the data warehouse.
Without detailed knowledge about the original designs of the data warehouse - knowledge embedded not just in rich meta data describing every object in the current data warehouse, but also knowledge about data movement strategies and operational requirements maintained in the project documentation - these kinds of requests are often difficult for data warehouse professionals to satisfy, because the finished, operational data warehouse is brittle: unable to change and morph as decision-makers requirements change.
Read More >>
The Right Choice, Now
WhereScape RED provides data warehousing professionals, database administrators and technically-savvy business professionals with a single product for designing, constructing and operating data warehouses and business intelligence environments - the most complete integrated suite available today.
WhereScape is so sure that its suite of tools will compare favorably with the tools you're using now that we'll give you a working copy of the software, free, for you to test in a real data warehousing project.
And, when you're ready to purchase WhereScape RED for yourself, your project team, or your organization, you'll be pleasantly surprised to find that WhereScape's prices, like its capability, are practical - easily within the reach of small organizations and departments as well as large companies.