META DATA:
THE KEY TO DATA WAREHOUSE DESIGN
( A SYSTEMS ENGINEERING APPROACH )

AUTHORS:
[Patrick Burton] [Stephanie Green] [ENSE623]

---

[Project Proposal] [Related Links]

Table of Contents

I. Introduction II. Data Warehouse Life-Cycle System Planning System Requirements & Analysis System Design System Integration System Verification and Maintenance III. Conclusion IV. Case Study IV. Case Tool V. Bibliography

---

INTRODUCTION

"The data warehouse concept sprang from the growing competitive need to quickly analyze business information." Typical relational databases which were designed for on-line transactional processing (OLTP) do not meet the requirements for effective on-line analytical processing (OLAP). As a result, data warehouses are designed differently then traditional relational databases. As the value of data warehouses and their associated OLAP capabilities have increased, comprehensive meta data management has proven to be a vital element to the success or failure of data warehouses. Without accurate meta data, a data warehouse rapidly becomes unmanageable and ineffective.

Meta data is literally "data about data". It describes the kind of information in the warehouse, where it stored, how it relates to other information, where it comes from, and how it is related to the business. The topic of standardizing meta data across various products and applying a systems engineering approach to this process in order to facilitate data warehouse design is what this project intends to address.

A data warehouse stores current and historical data from disparate operational systems (i.e. transactional databases) into one consolidated system where data is cleansed and restructured to support data analysis. We intend to create a design process for creating a data warehouse through the development of a metadata system. The meta data system we intend to create will provide a framework to organize and design the data warehouse. Using a systems engineering approach, this process will be used initially to help define the data warehouse requirements and it will them be used iteratively during the life of the data warehouse to update and integrate new dimensions of the warehouse. We will illustrate our process with a case study using an advertising/product-based business as the data warehouse application.

In order to be effective, the user of the data warehouse must have access to meta data that is accurate and up to date. Without a good source of meta data to operate from, the job of the analyst is much more difficult and the work required for analysis is compounded significantly.

Understanding the role of the enterprise data model relative to the data warehouse is critical to a successful design and implementation of a data warehouse and its meta data management. Some specific challenges which involve the physical design tradeoffs of a data warehouse include:

• Granularity of data - refers to the level of detail held in the unit of data
• Partitioning of data - refers to the breakup of data into separate physical units that can be handled independently.
• Performance issues
• Data structures inside the warehouse
• Migration

For years, data has aggregated in the corporate world as a by-product of transaction processing and other uses of the computer. For the most part, the data that has accumulated has served only the immediate set of requirements for which is was initially intended. The data warehouse presents an alternative in data processing that represent the integrated information requirements of the enterprise. It is designed to support analytical processing for the entire business organization. Analytical processing looks across either various pieces of or the entire enterprise and identifies trends and patterns otherwise not apparent. These trends and patterns are absolutely vital to the vision of management in directing the organization.

Meta data itself is arguably the most critical element in effective data management. Effective tools must be used to properly store and use the meta data generated by the various systems. This paper outlines a Systems Engineering approach to designing and maintaining a data warehouse, emphasizing the key element of metadata. This approach is then used as a template for a case study of the development of an actual data warehouse in a product based business. Finally, a metadata system is created and explained as a tool for designing and updating a data warehouse.

[Menu]


SYSTEM PLANNING
The Systems Planning Phase of the life cycle communicates an overall vision for the data warehouse activity and its role in the organization's daily and weekly life. Decisions made during this phase have significant impact on the implementation, scope, and size of the effort. It begins with the identification of a need and then defines timeliness, tasks and deliverables. Then it proceeds through the following key planning decisions:

• Select an Implementation Strategy
  Generally, a Top Down approach is useful for projects where the technology is mature and well understood, as well as where the business problems that must be solved are clear and well-understood. With this approach, the business requirements to be met by the proposed data warehouse solution are identified first. These are the primary drivers for the implementation of the data warehouse.
  
  A Bottom Up approach, on the other hand, is useful in making technology assessments and is a good technique for organizations that are not leading-edge technology implementors. This approach is used when the business objectives that are to be met by the data warehouse are unclear, or when the current or proposed business process will be affected by the data warehouse.
  
  In most business environments today, the capabilities and usefulness of a data warehouse is still being established. As managers and decision-makers learn the value of the data warehouse as an OLAP resource, data warehouses tend to grow very rapidly as the users require data from more and more different sources which must then be incorporated into the warehouse. Furthermore, as more applications (reports and analysis) proliferate from the warehouse, the primary users begin to better understand their OLAP needs and they can then define their requirements for the warehouse more clearly. Decision-makers who use the data warehouse often are not able to succinctly articulate the specific OLAP capabilities they desire until they learn more about data warehouses as they use them. In this situation, a Bottom Up approach is the only way to begin the design process of a data warehouse. You must attempt to assess and define the users OLAP needs at the start of the design process with the understanding that as the warehouse grows, the needs will also change.
  
  
• Select an Development Methodology
  A Development Methodology describes the expected evolution and management of the engineering system. One of the most important principles of Systems Engineering is evaluating a system from a Life-Cycle perspective. Establishing a methodology will also provide a strategy for the project manager and the project team as they execute the data warehouse project throughout all phases of development.
  
  
  • Waterfall Model
    The waterfall model is a linear sequence comprised of the following basic stages:
    
    • Requirements Definition
      
    • System Design
      
    • Detailed Design
      
    • Integration and Testing
      
    • Operations and Maintenance
      
    This model is used when the system requirements and objectives are known and clearly specified.
    
    
  • Spiral Model
    The Spiral model is a sequence of waterfall models which corresponds to a risk oriented iterative enhancement, and it recognizes that requirements are not always available and clear when the system is first implemented.
  
  Since designing and building a date warehouse is an iterative process, the spiral method is the best development methodology. The diagram below shows one waterfall series in a recommended spiral model of a data warehouse life-cycle:
  
  
  
  
• Develop Business Objectives
  Develop a list of business objectives that the system must fulfill using the following questions as a checklist:
  
  
  • Who is the potential audience?
    
  • What are the currently used of planned platforms?
    
  • What are the planned capabilities in terms of features and functions?
    
  • What data sources can and/or must be integrated into the data warehouse?
    
  • When is the system needed?
    
  • What is the expected life-span of the data warehouse?
    
  
  This step is often the most difficult in the planning phase because the potential users of the data warehouse cannot specifically describe the type of information they will want out of the warehouse. A good approach in this circumstance is to determine what kind of ad hoc analytical processing they do now and what data sources they use to generate such analytical reports. Using that information as a starting point, question the decision-makers about what type of additional information they would find useful. With these "wish-lists" from the users, the physical design of the warehouse becomes more clear as the certain elements of data are requested more than others.
  
  
• Collect Metadata
  The final part of the planning phase is the need to initially capture the various items of design relating to metadata. Metadata will actually serve as a blue-print for constructing the data warehouse. The Metadata is collected during the planning phase from the following sources:
  
  • Enterprise Models based on E-R (Entity Relationship) Diagrams.
  • Repositories and data dictionaries of the data sources.
  • Syndicated data - i.e. Dow Jones/3rd party information sources.
  
  
  Collecting metadata early in the planning phase is important for building a data warehouse. Unlike typical databases which usually have one coherent homogeneous data source which is structured with input rules and integrity constraints, a data warehouse is combining many different data sources which each have there own associated set of "business rules" which govern the database. It is at this stage where you begin to establish the patterns which trace data from the Source, to the Warehouse, to the Applications. Capturing and documenting this metadata is the only way to logically track this pattern which becomes critical later in the life-cycle when data elements at the sources change over time. When such changes occur, these links from Source to Warehouse to Application will allow the warehouse to be more readily maintained and updated, thereby ensuring its viability as a reliable OLAP resource.
  

[Menu]


SYSTEM REQUIREMENTS & ANALYSIS
This Phase identifies the requirements/functions that the data warehouse will deliver. In addition to the features and functions needed, the requirements will clearly describe the operating environment in which the data warehouse will be delivered.

The amount of requirements gathering depends on the implementation approach that you take. The goal is to get an understanding of the core use of initial data and to identify other users who may need to leverage access to the data.

The primary purpose of collecting end user requirements for a data warehouse is to understand how users conduct their business, what data they currently use, and what they would like to do in the future. You should be able to further decompose this information into Business entities and their attributes, relationships between the entities, and hierarchies.

The requirements can be gathered through a series of interviews with the different users. Answers to the following questions from the different users will generate the requirements needed for further development of the data warehouse.

• Executive's/Owner's Requirements:
  
  • Why are we building a data warehouse? What business problem will it address?
  • How much will it cost?
    
  • When will it be ready?
    
  • What is the impact on people? Skills? Organization?
    
  • What does it do to our current computer investment?
    
  • Do we have the skills to do it?
    
  • What are the risks?
    
  
  
• Architect's Requirements:
  The architect views the data warehouse in the abstract and lays out it various components.
  
  • What analyses do you wish you could get more frequently?
    
  • What functions and features will be offered?
    
  • What platforms are needed for implementation?
    
  • How will standards and open interfaces be used?
    
  • How much flexibility is there for adding enhancements?
    
  
  
• Developer's Requirements:
  The developer view the warehouse in more specific detailed terms.
  • Request a further break down of the Architect's requirements into specific applications, interfaces, computers, databases, communication protocols, and user-interface screens.
    
  • What are the Deployment Requirements?
    
    • Access and delivery methods?
      
    • Access tools?
      
    • Connectivity requirements?
      
    • Client Platform requirements?
      
  
  
• End User Requirements:
  
  • How does the data warehouse's functionality fit the end user's daily workflow?
    
  • What are your Query Requirements?
    
    • What types of ad hoc analysis do you do?
      
    • What level of detail is included?
      
  • What are your reporting requirements?
    
    • What reports do you create?
      
    • How often do you perform this analysis?
      
    • Who gets the information?
      
    • How is it used?
      
    • Where do you get the information from?
      
  At the end of each interview, ask each candidate to develop a wish list which would include things that you would want to be able to do if there were no financial or time or technical constraints in your way. Next we develop a cost requirement.
  
  
• Cost Requirements:
  Traditional cost assessment is not applicable in developing a data warehouse because it uses an iterative development process, therefore making it impossible to predict end-user's summaries and changes.
  
  However, project managers can determine percentages and priorities of work involved in the development. A percentage of at least 10% should be allocated to metadata management throughout the life-cyucle of the warehouse. A table of recommended cost allocation percentages follows:
  Budget Allocation Guidelines

  Platform(process/disk) DBMS Warehouse Management Metadata Management Log Processing Software Analysis Platforms Analysis Software Middleware Selection/Discovery Data Models CASE/dd/Repository Interface Multi-Dimensional DBMS

  15%-50% 5%-25% 5%-10% 5%-10% 0%-10% 10%-20% 1%-5% 1%-15% 1%-5% 0%-1% 0%-1% 0%-10%

  
  

[Menu]


SYSTEM DESIGN
The systems design phase is important because it determines how the requirements will be met. The main focus of this phase is to convert the systems requirements into a set of system-level specifications through deriving logical and physical data models for the data warehouse (i.e. E-R Diagrams and MetaModels).

The specifications are then used to generate the data warehouse extractors and transformation, integration, summarization, and aggregation software. Next, a "Build versus Buy" trade-off analysis is performed. Decisions are complicated by technical, economic, and political considerations. A poor decision can ruin an otherwise successful analysis and design. On the other hand, an appropriate selection of vendor-supplied component or incorporation of existing investment can make it possible to build a quick and effective data warehouse.

Finally, processes are identified to connect the data sources, the data warehouse, and the end user access tools together.

[Menu]


SYSTEM INTEGRATION
The System Integration phase is started in conjunction with the design phase. This phase encompasses locating the source of the data in the operational systems doing analysis to understand what types of data transformations may be needed, and mapping the source data to the target data within the warehouse design. The challenge during this phase is to understand how to incorporate existing investments in platforms, technology, and know-how. Systems integration capabilities tie vendor systems together with existing data sources and existing and proposed access tools. Proper selection and evaluation of vendor supplied components and Metadata Management will prove invaluable in successfully integrating the disparate sources of data into the warehouse.

The main focus of this phase is developing procedures to extract and move data in a form that can then be loaded into the warehouse. Programs and transformation tools should be used to reduce the customized programming required to transform and integrate the data. Finally, the data must be analyzed to determine whether or not certain elements should be cleansed prior to putting it into the warehouse. Business rules should be established to standardize the data formats within the warehouse if possible.

[Menu]


SYSTEM VERIFICATION & MAINTENANCE
The Verification phase is relatively straightforward but it also requires a lot of diligence. The best methods for verifying the data in the warehouse is to formulate applications (reports) on the data in the warehouse and compare it to figures based on the data prior to being put in the warehouse which are known to be correct. Often the users themselves are the best people to verify the data because they are often more familiar with the specific type of data they are looking for. Finally, Maintenance is imperative at every stage of the life-cycle. Primarily this entails documentation of programs, applications and most importantly, metadata. Although most warehouse managers realize that maintaining metadata is of critical importance to the viability of the data warehouse, documentation is also the task which most often gets pushed to the side when more urgent tasks for the users arrive. This is a dangerous precedent to set, because once a growing data warehouse gets behind in documenting metadata, it is on its way to being a useless tool which will become irrelevant because no one will have any faith in the data which it stores.


[Menu]


CONCLUSION
As the case study below illustrates in detail, following a coherent methodology which emphasizes the importance of metadata is a critical factor which often determines the success or failure of a data warehouse project. By being diligent and insisting that proper documentation be maintained, you will ensure that the data warehouse will have a better design overall, and the pattern of waterfall sequence of the warehouse life-cycle will become more habitual.

[Menu]


CASE STUDY

• Background
  
  We conducted a research case study of an actual data warehouse which exists at a major Fortune 500 product-based company. This company is part of the media industry whose business includes producing a product in a factory environment as well as focusing heavily on advertising sales. An organization with such a diverse work force also creates and stores many disparate types of data. However, the way this data relates together is often difficult to determine and requires careful study to integrate the data into a coherent data warehouse which can yield benefitial on-line analytical processing.
  
  Our assignment from the project manager was to "backward engineer" the data warehouse named "Ad History" using systems engineering principles and documenting the lessons learned from the development of this warehouse. After 18 months of implementation, the project manager had become quite concerned because the team had lost track of much of the warehouse's metadata which had resulted in difficulties updating OLAP applications as the source data changed periodically. Furthermore, the lack of metadata had caused the original warehouse design to become so confusing that the integration of new data sources had become too complicated. As we studied this warehouse, we determined that the most important principle in the design as well as management of a data warehouse is always tracking the metadata from source, to warehouse, to application. As a result we devised a metadata management system which was customized for the "Ad History" database.
  
  We will walk through the data warehouse Life-Cycle phases and provide the information which was relevant at the time this warehouse was being designed as well as provide current information which illustrates the importance of the systems engineering approach to this task.
  
  
• Data Warehouse Life-Cycle
  
  As described before, the Spiral Model is the best methodology to use when creating a data warehouse. Maintaining a data warehouse is an iterative process which requires special attention to routine upkeep throughout the Life-Cycle. The recommended steps of Requirements Analysis, Design, Integration, and Verification & Maintenance can easily become stuck at the stage of creating OLAP applications. Users soon demand so many customized reports and models that the following iterations of re-design never gets started. Then as new sources are forced into the warehouse without sufficient documentation and analysis, the schema becomes convoluted and disjointed which defeats the purpose of the data warehouse.
  
  
  • System Planning
    Ad History began with a bottom up approach by addressing the immediate needs of the advertising systems in the organization. There are two main transactional databases (sources) used in the advertising called Admarc and SII. Therefore, the primary users who were polled for needs were in advertising. While they were able to provide the old reports produced by ad hoc methods, they were not able to fully understand all the applications which they would like until later in the life-cycle when the power of the database would be realized. Similarly, other user groups and departments such as personnel, accounting, engineering and production were able to provide old requirements but could not foresee the types of needs they would have when the warehouse was implemented.
    
    The business objectives were primarily focused on the advertising department as the name "Ad History" implies. However, the data warehouse manager also new that the other groups mentioned before would also soon become interested users of "Ad History". The platform is an Oracle 7.2 dbms on a Windows NT network. The planned functionality was that eventually all customers would be able to access and do their own querying against the warehouse. However, as OLAP applications quickly proliferated in the first year, it became clear that queries on the data warehouse must be limited to approved models and reports. Some users would make unnecessary ad-hoc queries which would monopolize the system and the server for several hours because the user had no knowledge of the index schema on the data. Therefore, the idea of user access to the warehouse was abandoned until better system security could be implemented to prevent inefficient use of the warehouse.
    
    The data sources which are planned for integration into the warehouse grows as time goes on. Originally, the two main advertising systems, personnel management system, payroll system, administrative and accounting systems, as well as contract and sales data was all planned for integration into "Ad History". However, other departments have expressed interest in integrating data into the warehouse such as production systems, circulation systems, and maintenance systems. The most challenging aspect of designing this warehouse was the immediate demand for the system's data. Once management had decided to fund the data warehouse, they set deadlines within several months of the projects beginning to produce relevant OLAP applications. In this case, the warehouse design was sacrificed significantly in order to meet the demands placed on the systems so early in the life-cycle. This caused serious problems which are difficult to overcome, and passing time only makes such design flaws more pronounced. The data warehouse was intended to last at least ten years, and when serious design flaws are allowed during the first integration, the warehouse has a tendency to grow out of control before the problems can be corrected. One example is the SII data which was scheduled to be on-line by January of '95. Unfortunately, in the urgency of the moment the data was loaded into one giant table called with over 200 columns. Originally, this was thought to be a temporary design simply to get the data into the system. However, before long, the table had grown so large that re-designing it became a monumental task. Furthermore, the queries against pertinent data in that table were slowed significantly because the table was too big. It was the attempts at re-designing this table that brought the importance of metadata to the analysts' attention. While the analyst who created the table and was familiar with the data could explain the metadata, all the other analysts who needed to created applications based on the data in that table were slowed by the need to determine and verify the exact definitions of the data.
    
    Much of the time which should have been spend creating E-R Diagrams and a Metadata Framework was sacrificed to the "tyranny of the urgent" as users demanded data before the warehouse was correctly designed and implemented. These sacrifices caused worse problems as time went on because applications became very slow and the overall warehouse schema became more confusing.
    
    
  • System Requirements & Analysis
    In retrospect, many of the system requirements for the warehouse were unreasonable. While the executive's had a good idea in mind about why the warehouse was needed and what problems it would address, they also put unreasonable time demands on the project which caused design considerations to be sacrificed. The organization believed they had the necessary skills to design and implement the warehouse, but an experienced consultant would have been invaluable to the project by helping to create a more sound design. Recently, the project team had to hire a consultant to add on another warehouse structure on top of the original warehouse in order to better organize the date and automate OLAP output.
    
    The architects attempted to emphasize output when considering the design. However, because they became caught up in a application building phase of the life-cycle they were never able to re-assess the design and make changes as time went on. The development tools were selected early in the process which also contributed to a disproportionate amount of attention placed on output rather than design.
    
    Finally, the End Users, as stated before, were limited in their ability to explain their data warehouse needs. The momentum of the project can tend to cause users to have unrealistic expectations after they learn the potential of the warehouse, when in the beginning they could not articulate their needs.
    
    
  • System Design
    At the time that we began to analyze the 18-month-old data warehouse, no high level entity-relationship diagrams or meta models existed to map out the warehouse design or to illustrate the business rules associated with the different data sources. During the process of trying to establish an E-R Diagram which reflected the current state of the warehouse, we again discovered the criticality of accurate metadata. Several of the warehouse architects and analysts knew the warehouse very well, but they were hard pressed to diagram or even describe the overall data warehouse schema. At that point in the data warehouse life-cycle, the project is very vulnerable because without an accurate map and corresponding metadata about the warehouse, the absences or relocation of certain key individuals could jeopardize the entire project because you are now left with a huge mound of data which no one knows what it is.
    
    As part of the E-R Diagram process, it becomes very evident that a data warehouse is designed fundamentally differently then its transactional processing database brothers. While transactional databases are highly normalized for speedy real-time querying and transaction processing, analytical processing often dictates that a de-normalized approach be taken to emphasize analytical processing. The star schema is a commonly used term to describe these type of table structures. In such a design, the central small table contains the most important data which is most often reported and analyzed. By deliberately keeping this table small based on user needs rather that normalization principles, most of the columns in the core table can be indexed which results in faster analytical processing. The outer tables of the star schema usually have some redundancy with the core table, and they generally contain obscure data grouped into categories for future reference if it is ever needed. This type of research is often called "data mining". However, since the data in these tables is rarely referenced, it is simply related to its core table as a logical connection rather than a normalized relationship.
    
    The 200 column table in "Ad History" which was hastily loaded all together into the warehouse early in the design process is a perfect example of a group of data which should have been configured into a star schema. The E-R Diagram we developed for the warehouse incorporated this design (note the "Classified Ad Cluster" below").
    
    
    
    
  • System Integration
    The integration process can be very labor intensive, but if you have a good design and you document your programs well, the integration programs can be relatively maintenance free. This warehouse generally takes data from the source in comma delimited text files which are then dissected and stored properly using a logical SQL loader script. These files are usually loaded weekly and then verified immediately after loading to ensure that no data has been corrupted. The final step in automating the loading process is to write Unix shell programs which automatically search for the dated text files at set intervals which triggers the running of the loader script.
    
    
  • System Verification & Maintenance
    Verification for this warehouse is easy because so many applications are generated from the warehouse that small inconsistencies or corrupted data is readily identified and corrected. The biggest drawback to this benefit is that there is a lot of labor overhead associated with maintaining applications which takes time away from the necessary tasks of documenting metadata and continually re-evaluating the warehouse schema to integrate the new sources of data.
    
    
  
  
• Lessons Learned and Recommended Improvements
  The main lesson learned in this case study is to insist on taking the necessary time to create a sound data warehouse design before proceeding to the Integration and Maintenance phases. Furthermore, the lack of a well defined metadata framework early on in the design phase will become a problem in later phases of the life-cycle. The metadata schema we developed for "Ad History" is found below.
  
  
  
  
• Using the Metadata System
  We created a Metadata system for "Ad History" using Microsoft Access as the front-end GUI design. The purpose behind the system was to trace the relationships from source to warehouse to application. The application may be joined directly to the Oracle data dictionary using ODBC, or for simplicity it may be used as a stand alone system to store and track all the pertinent metadata of "Ad History". The files for this metadata system may be downloaded through FTP. The files are named the following:
  
  /software/isrwww/html/Courses/ENSE623/DataWarehouse/metacomp.mdb /software/isrwww/html/Courses/ENSE623/DataWarehouse/metacomp.ldb
  
  
  The main menu of the metadata system provides user-friendly buttons to either browse or update the data warehouse metadata. The simplicity is important because many different user groups may find this system useful, including managers, sales reps, decision-makers, data analysts, applications developers, and warehouse supervisors. Also note that the E-R Diagrams of the "Ad History" as well as the metadata schema may be accessed to clarify the metadata displays.
  
  
  
  After pressing the "Open Table Dictionary" button, the Table Dictionary opens to display all of the tables which exist in the warehouse. By using the drop down list where it says, "select a table" you can choose a specific table based on its category (such as display ads, classified ads, or metadata). After selecting a specific table, every source field which is put into that table will appear in a sub-list to help trace the pattern from source to warehouse.
  
  
  
  When a specific field is selected with the dropdown menu in the field dictionary, a sub-list of usages appear which relates each field to all of the specific application uses.
  
  
  
  Likewise, the source dictionary traces the pattern through the warehouse fields and tables to the application usages. This is a key capability because it allows analysts to quickly and easily identify all the applications which must be changed or modified when on of the source elements changes for some reason.
  
  
  
  Finally, the usage dictionary maps the applications back to the tables and columns.
  
  
  
  In order to update any part of the metadata during the life-cycle of the warehouse, the individual forms may be used to quickly and easily update and document pertinent metadata. The drop-down lists in the table dictionary help you to find the warehouse schema categories with having to look them up.
  
  
  
  Updating the fields is also easily accomplished with the drop down lists.
  
  
  
  Finally, the Source Update form....
  
  
  
  ...and the Usage Update form.
  
  
  
  This metadate system can provide an immediate framework which is easy to use to keep metadata updated.
  
  

[Menu]


CASE TOOl

SLATE (System Level Automation Tool for Engineers) by TD Technologies is a case tool that allows one to build a system design model in the form of a hierarchy of trees. An object oriented database is used where each tree is made up of blocks diagrams. Attached to each diagram are objects which represent Requirements, Documents, Abstraction Hierarchies, Traceability Relationships, Budgets and other information related objects used by engineers to develop systems. Slate, with its flexible design approaches, provides engineers with to create optimal designs from a global perspective.

Due to SLATE's explicit line of functions & features, we chose to utilize SLATE to demonstrate how important it is to begin any design with a High-Level Hierarchical view followed with bottom-up composition starting with MetaData.

The Data Warehouse Architecture divides into the following Abstraction Blocks: Data Source, Data Warehouse, Transportor, Access & Usage, and MetaData. Below are screen captures of the Hierarchical Design and further descriptions of each abstraction block.



Hierarchical tree structure (view)



Hierarchical outline structure (view)

Data Source Block
This block represents the many different types of data that can be integrated into the Data Warehouse. Data comes from on-line transactional databases, historical data, and syndicated sources such as the Dow Jones Retrieval. MetaData must be developed on each pieces of data coming from the selected sources. This will provide information on the name of the data captured and extracted from the sources, the definition of the contents, the date of creation, and the source of the data.

Data Warehouse Block
This block consist of a Re-Engineering abstraction and Modeling abstraction. The Re-Engineering component is responsible for conditioning the data to meet the analysis needs of the user. This involves Integrating (Integrator) the many different data types from multiple systems into the the Target system. Next, the Translator formats the data so it can be combined uniformly and consistently. It also re-maps the data to the original source to enable refreshing of the newly created data. The Modeling component derives data models from the enterprise or E-R models, if one exits, or creates new models. This is also where design issues concerning data partitions, subject areas, and granularity are handled. MetaData is further obtained in each of the above abstraction.

Transport Block
This block addresses the task of transporting the data through the system. It addresses the necessary communications bridges between the hardware/software platforms, network protocols, network management systems, security, and middleware components. MetaData also must be provided in this block.

Access & Usage Block
This block consist of two abstraction blocks: Access & Retrieval, and Analysis and Reporting. This block provides the payoff and the value to the entire data warehouse implementation. The Access & Retrieval abstraction is responsible for transforming the retrieved data into views for subsequent analysis. The Analysis & Reporting abstraction is responsible for the family of tools and applications needed to get value from the data warehouse. These tools can be classified into reporting tools, analysis and decision-support tools, and data mining tools. This abstraction is also responsible for developing navigation tools for metadata in order to understand and report on the contents of the data warehouse. Metadata development is also included in both abstraction. Metadata here can assist with verifying the reliability of the data, determining the location of the data ,and figuring out how to access and use the actual data.

MetaData Block
This block provides assistance to the designer in understanding, monitoring, and managing the data in the data warehouse. It provides a road map of data to the user. Without metadata, the data warehouse becomes and abyss of meaningless scraps of information.

The Hierarchical Architecture is designed to meet the requirements of all stakeholders of the data warehouse. It presents a unified and common way of looking at the components of a data warehouse solution while providing a firm foundation for planning, analysis, estimation, risk assessment, and a blueprint for implementation. The entire Architecture is built on the concept of data definitions, or Metadata. Metadata which is captured from each block pervades every activity of the data warehouse.


BIBLIOGRAPHY

Poe Vidette, "Building A Data Warehouse for Decision Support"
....Prentice Hall, New Jersey, 1995.

Herb Edelstein, "Planning & Designing the Data Warehouse"
...Prentice Hall, New Jersey, 1996.

John Makulowich, "Data Warehouse Market Draws a Crowd"
...Washington Technology, Oct. 24, 1996.

Wayne Eckerson, "Metadata Hullabaloo"
...Oracle Magazine, Mar/Apr 1996.

Dave Menninger, "Designing a Database for OLAP"
...Oracle Magazine, Mar/Apr 1996.

Douglas Hackney, "Metadata Maturity"
...Data Management Review, Mar 1996.

Larry Greenfield, "Don't Let Data Warehousing GOTCHAS Getcha"
...Datamation, Mar 1,1996.

Satya Sachdeve, "Metadata: Guiding Users Through Disparate Data Layers"
...Application Development Tools, Dec 1995.

Kevin Strehlo, "Data Warehousing: Avoid Planned Obsolescence"
...Datamation, Jan 15, 1996.

Craig S. Mullins, "Dealing with Data Outhouses"
...Data Management Review, Mar 1996.

Mark Austin, "ENSE623: Systems Engineering Design Projects, Volumes I & II"
...Institute for Systems Research, 1995-1996.


[Menu]


RELATED LINKS
[Menu]

• What is a Data Warehouse?
• Data Warehouse Terminology
• The Metadata Coalition
• Converge - A Data Warehousing Consulting Service
• Briefing Paper: What is Meta Data
• White Paper: Developing a Data Warehouse
• Metadata: Guiding Users Through Disparate Data Layers
• Data Warehouse Case Study
• The Metadata Key

---

[Return to Menu]