Data Warehouse Architecture Review Analysis
NEERAJ NATHANI DATA WAREHOUSE ARCHITECTURE DESIGNER
What is your understanding of data warehouse architecture?
An architecture of a data warehouse gives us the idea of the complete life –cycle of the data how a “data” is changed to a “information” through its movement from various stages. The data warehouse architecture defines the halting point and the status of the data at each of these milestones. For eg: a raw data which can occur from various source systems : RDBMS, Netezza, websites, files,OLTPs etc. and dumped in tables called the “Landing tables”. This is actually the first halting point in a data warehouse architecture. The next halt point in the architecture comes where we need to integrate all the source data, apply transformation on them & store in an area called the “Staging” (We can have pre-staging before that depending on the complexity of the business rules). Once the data is cleaned in staging we move the data in the “ODS” (Operational data source). An operational data store (or "ODS") is a database designed to integrate data from multiple sources for additional operations on the data. The data is then passed back to operational systems for further operations and to the data warehouse for reporting.Because the data originates from multiple sources, the integration often involves cleaning, resolving redundancy and checking against business rules for integrity. An ODS is usually designed to contain low-level or atomic (indivisible) data (such as transactions and prices) with limited history that is captured "real time" or "near real time" as opposed to the much greater volumes of data stored in the data warehouse generally on a less-frequent basis.
One of the most effective techniques for ensuring quality in the operational environment is the design review. Through a design review, errors can be detected and resolved prior to coding. The cost benefit of identifying errors early in the development life cycle is enormous.
In the operational environment, a design review is usually done on completion of the physical design of an application. The types of issues around which an operational design review centers are the following:
Done properly in the operational environment, a design review can save significant resources and greatly increase user satisfaction. Most importantly, when a design review has been properly executed, major pieces of code do not have to be torn
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
up and rewritten after the system has gone into production.
A design review is as applicable to the data warehouse environment as it is to the operational environment, with a few provisos. have to be torn up and rewritten after the system has gone into production.
A design review is as applicable to the data warehouse environment as it is to the operational environment, with a few provisos.
One proviso is that systems are developed in the data warehouse environment in an iterative manner, where the requirements are discovered as a part of the development process. The classical operational environment is built under the well-defined system development life cycle (SDLC). Systems in the data warehouse environment are not built under the SDLC. Other differences between the development process in the operational environment and the data warehouse environment are the following:
In the operational environment, transaction response time is a major and burning issue. In the data warehouse environment, transaction response time had better not be an issue. DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
· There are, then, some substantial differences between the operational and data warehouse environments, and these differences show up in the way the design review is conducted.
|
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS
A design review in the data warehouse environment is done as soon as a major subject area has been designed and is ready to be added to the data warehouse environment. It does not need to be done for every new database that goes up. Instead, as whole, new major subject areas are added to the database, a design review becomes an appropriate activity.
|
The attendees at the design review include anyone who has a stake in the development, operation, or use of the DSS subject area being reviewed. Normally, this includes the following parties:
Of this group, by far the most important attendees are the end users and the DSS analysts.
One important benefit from having all the parties in the same room at the same time is the opportunity to short-circuit miscommunications. In an everyday environment where the end user talks to the liaison person who talks to the designer who talks to the programmer, there is ample opportunity for miscommunication and misinterpretation. When all the parties are gathered, direct conversations can occur that are beneficial to the health of the project being reviewed.
|
The subject for review for the data warehouse environment is any aspect of design, development, project management, or use that might prevent success. In short, any obstacle to success is relevant to the design review process. As a rule, the more controversial the subject, the more important it is that it be addressed during the review.
The questions that form the basis of the review process are discussed later in this chapter.
|
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS
|
A data warehouse design review has three results:
Administering the review
The review is led by two people -- a facilitator and a recorder. The facilitator is never the manager or the developer of the project being reviewed. If, by some chance, the facilitator is the project leader, the purpose of the review -- from many perspectives -- will have been defeated.
To conduct a successful review, the facilitator must be someone removed from the project for the following reasons:
|
If part of the data warehouse, in fact, does exist outside the data warehouse environment, there should be a plan to bring that part of the DSS world back into the data warehouse environment.
6. Have the major subjects that have been identified been broken down into lower levels of detail?
- Have the keys been identified?
- Have the attributes been identified?
- Have the keys and attributes been grouped together?
- Have the relationships between groupings of data been identified?
- Have the time variances of each group been identified?
ISSUE: There needs to be a data model that serves as the intellectual heart of the data warehouse environment. The data model normally has three levels -- a high-level model where entities and relationships are identified; a midlevel where keys, attributes, and relationships are identified; and a low level, where database design can be done. While not all of the data needs to be modeled down to the lowest level of detail in order for the DSS environment to begin to be built, at least the high-level model must be complete.
7. Is the design discussed in question 6 periodically reviewed? (How often? Informally? Formally?) What changes occur as a result of the review? How is end-user feedback channeled to the developer?
ISSUE: From time to time, the data model needs to be updated to reflect changing business needs of the organization. As a rule, these changes are incremental in nature. It is very unusual to have a revolutionary change. There needs to be an assessment of the impact of these changes on both existing data warehouse data and planned data warehouse data.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
8. Has the operational system of record been identified?
- Has the source for every attribute been identified?
- Have the conditions under which one attribute or another will be the source been identified?
- If there is no source for an attribute, have default values been identified?
- Has a common measure of attribute values been identified for those data attributes in the data warehouse environment?
- Has a common encoding structure been identified for those attributes in the data warehouse environment?
- Has a common key structure in the data warehouse environment been identified? Where the system of record key does not meet the conditions for the DSS key structure, has a conversion path been identified?
- If data comes from multiple sources, has the logic to determine the appropriate value been identified?
- Has the technology that houses the system of record been identified?
- Will any attribute have to be summarized on entering the data warehouse?
- Will multiple attributes have to be aggregated on entering the data warehouse?
- Will data have to be resequenced on passing into the data warehouse?
· DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
ISSUE: After the data model is built, the system of record is identified. The system of record normally resides in the operational environment. The system of record represents the best source of existing data in support of the data model. The issues of integration are very much a factor in defining the system of record.
9. Has the frequency of extract processing — from the operational system of record to the data warehouse environment — been identified? How will the extract processing identify changes to the operational data from the last time an extract process was run?
- By looking at time-stamped data?
- By changing operational application code?
- By looking at a log file? An audit file?
- By looking at a delta file?
- By rubbing "before" and "after" images together?
ISSUE: The frequency of extract processing is an issue because of the resources required in refreshment, the complexity of refreshment processing, and the need to refresh data on a timely basis. The usefulness of data warehouse data is often related to how often the data warehouse data is refreshed.
One of the most complex issues — from a technical perspective — is determining what data is to be scanned for extract processing. In some cases, the operational data that needs to pass from one environment to the next is straightforward. In other cases, it is not clear at all just what data should be examined as a candidate for populating the data warehouse environment.
10. What volume of data will normally be contained in the DSS environment? If the volume of data is large:
- Will multiple levels of granularity be specified?
- WIll data be compacted?
- Will data be purged periodically?
- Will data be moved to near-line storage? At what frequency?
ISSUE: In addition to the volumes of data processed by extraction, the designer needs to concern himself or herself with the volume of data actually in the data warehouse environment. The analysis of the volume of data in the data warehouse environment leads directly to the subject of the granularity of data in the data warehouse environment and the possibility of multiple levels of granularity.
11. What data will be filtered out of the operational environment as extract processing is done to create the data warehouse environment?
ISSUE: It is very unusual for all operational data to be passed to the DSS environment. Almost every operational environment contains data that is relevant only to the operational environment. This data should not be passed to the data warehouse environment. DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
12. What software will be used to feed the data warehouse environment?
- Has the software been thoroughly shaken out?
- What bottlenecks are there or might there be?
- Is the interface one-way or two-way?
- What technical support will be required?
- What volume of data will pass through the software?
- What monitoring of the software will be required?
- What alterations to the software will be periodically required?
- What outage will the alterations entail?
- How long will it take to install the software?
- Who will be responsible for the software?
- When will the software be ready for full-blown use?
ISSUE: The data warehouse environment is capable of handling a large number of different types of software interfaces. The amount of breakin time and "infrastructure" time, however, should not be underestimated. The DSS architect must not assume that the linking of the data warehouse environment to other environments will necessarily be straightforward and easy. DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
13. What software interface will be required for the feeding of DSS departmental and individual processing out of the data warehouse environment?
- Has the interface been thoroughly tested?
- What bottlenecks might exist?
- Is the interface one-way or two-way?
- What technical support will be required?
- What traffic of data across the interface is anticipated?
- What monitoring of the interface will be required?
- What alterations to the interface will there be?
- What outage is anticipated as a result of alterations to the interface?
- How long will it take to install the interface?
- Who will be responsible for the interface?
- When will the interface be ready for full-scale utilization?
14. What physical organization of data will be used in the data warehouse environment? Can the data be directly accessed? Can it be sequentially accessed? Can indexes be easily and cheaply created? DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
ISSUE: The designer needs to review the physical configuration of the data warehouse environment to ensure that adequate capacity will be available and that the data, once in the environment, will be able to be manipulated in a responsive manner. DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
15. How easy will it be to add more storage to the data warehouse environment at a later point in time? How easy will it be to reorganize data within the data warehouse environment at a later point in time?
ISSUE: No data warehouse is static, and no data warehouse is fully specified at the initial moment of design. It is normal to make corrections in design throughout the life of the data warehouse environment. To construct a data warehouse environment either where midcourse corrections cannot be made or are awkward to make is to have a faulty design.
16. What is the likelihood that data in the data warehouse environment will need to be restructured frequently (that is, columns added, dropped, or enlarged, keys modified, and so on)? What effect will these activities of restructuring have on ongoing processing in the data warehouse?
ISSUE: Given the volume of data found in the data warehouse environment, restructuring it is not a trivial issue. In addition, with archival data, restructuring after a certain moment in time often becomes a logical impossibility.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
17. What are the expected levels of performance in the data warehouse environment? Has a DSS service-level agreement been drawn up either formally or informally?
ISSUE: Unless a DSS service-level agreement has been formally drawn up, it is impossible to measure whether performance objectives are being met. The DSS service-level agreement should cover both DSS performance levels and downtime. Typical DSS service-level agreements include such information as the following:
- Average performance during peak hours per units of data
- Average performance during off-peak hours per units of data
- Worst performance levels during peak hours per units of data
- Worst performance during off-peak hours per units of data
- System availability standards
One of the difficulties of the DSS environment is measuring performance. Unlike the operational environment, where performance can be measured in absolute terms, DSS processing needs to be measured in relation to the following:
- How much processing the individual request is for
- How much processing is going on concurrently
- How many users are on the system at the moment of execution
· DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
18. What are the expected levels of availability? Has an availability agreement been drawn up for the data warehouse environment, either formally or informally?
ISSUE: (See the issue for question 17.)
19. How will the data in the data warehouse environment be indexed or accessed?
- Will any table have more than four indexes?
- Will any table be hashed?
- Will any table have only the primary key indexed?
- What overhead will be required to maintain the index?
- What overhead will be required to load the index initially?
- How often will the index be used?
- Can or should the index be altered to serve a wider use?
ISSUE: Data in the data warehouse environment needs to be accessed efficiently and in a flexible manner. Unfortunately, the heuristic nature of data warehouse processing is such that the need for indexes is unpredictable. The result is that the accessing of data in the data warehouse environment must not be taken for granted. As a rule, a multitiered approach to managing the access of data warehouse data is optimal:
- The hashed or primary key should satisfy most accesses.
- Secondary indexes should satisfy other popular access patterns.
- Temporary indexes should satisfy the occasional access.
- Extraction and subsequent indexing of a subset of data warehouse data should satisfy infrequent or once-in-a-lifetime accesses of data.
In any case, data in the data warehouse environment should not be stored in partitions so large that they cannot be indexed freely.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
20. What volumes of processing in the data warehouse environment are to be expected? What about peak periods? What will the profile of the average day look like? The peak rate?
ISSUE: Not only should the volume of data in the data warehouse environment be anticipated, but the volume of processing should be anticipated as well. DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
21. What level of granularity of data in the data warehouse environment will there be?
- A high level?
- A low level?
- Multiple levels?
- Will rolling summarization be done?
- Will there be a level of true archival data
- Will there be a living-sample level of data?
ISSUE: Clearly, the most important design issue in the data warehouse environment is that of granularity of data and the possibility of multiple levels of granularity. In other words, if the granularity of the data warehouse environment is done properly, then all other issues become straightforward. If the granularity of data in the data warehouse environment is not designed properly, then all other design issues become complex and burdensome.
22. What purge criteria for data in the data warehouse environment will there be? Will data be truly purged, or will it be compacted and archived elsewhere? What legal requirements are there? What audit requirements are there?
ISSUE: Even though data in the DSS environment is archival and of necessity has a low probability of access, it nevertheless has some probability of access (otherwise, it should not be stored). When the probability of access reaches zero (or approaches zero), the data needs to be purged. Given that volume of data is one of the most burning issues in the data warehouse environment, purging data that is no longer useful is one of the more important aspects of the data warehouse environment. DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
23. What total processing capacity requirements are there?
- For initial implementation
- For the data warehouse environment at maturity?
ISSUE: Granted that capacity requirements cannot be planned down to the last bit, it is worthwhile to at least estimate how much capacity will be required, just in case there is a mismatch between needs and what will be available.
24. What relationships between major subject areas will be recognized in the data warehouse environment? Will their implementation:
- Cause foreign keys to be kept up-to-date
- Make use of artifacts
What overhead is required in the building and maintenance of the relationship in the data warehouse environment?
ISSUE: One of the most important design decisions the data warehouse designer makes is that of how to implement relationships between data in the data warehouse environment. Data relationships are almost never implemented the same way in the data warehouse as they are in the operational environment.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
25. Do the data structures internal to the data warehouse environment make use of:
- Arrays of data?
- Selective redundancy of data?
- Merging of tables of data?
- Creation of commonly used units of derived data?
ISSUE: Even though operational performance is not an issue in the data warehouse environment, performance is nevertheless an issue. The designer needs to consider the design techniques listed previously when they can reduce the total amount of I/O consumed. The techniques listed previously are classical physical denormalization techniques. Because data is not updated in the data warehouse environment, there are very few restrictions on what can and can't be done.
The factors that determine when one or the other design technique can be used include the following:
- The predictability of occurrences of data
- The predictability of the pattern of access of data
- The need to gather artifacts of data
· DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
26. How long will a recovery take? Is computer operations prepared to execute a full data warehouse database recovery? A partial recovery? Will operations periodically practice recovery so that it will be prepared in the event of a need for recovery? What level of preparedness is exhibited by:
- Systems support?
- Applications programming
- The DBA?
- The DA?
For each type of problem that can arise, is it clear whose responsibility the problem is?
ISSUE: As in operational systems, the designer must be prepared for the outages that occur during recovery. The frequency of recovery, the length of time required to bring the system back up, and the domino effect that can occur during an outage must all be considered.
Have instructions been prepared, tested, and written? Have these instructions been kept up-to-date? DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
27. What level of preparation is there for reorganization of restructing of:
- Operations?
- Systems support
- Applications programming?
- The DBA?
- The DA?
Have written instructions and procedures been set and tested? Are they up-to-date? Will they be kept up-to-date?
ISSUE (See the issue for question 26.) DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
28. What level of preparation is there for the loading of a database table by:
- Operations>
- Systems support?
- Applications programming?
- The DBA
- The DA?
Have written instructions and procedures been made and tested? Are they up-to-date? Will they be kept up-to-date?
ISSUE: The time and resources for loading can be considerable. This estimate needs to be made carefully and early in the development life cycle.
29. What level of preparation is there for the loading of a database index by:
- Operations?
- Systems support>
- Applications programming?
- The DBA?
- The DA?
ISSUE: (See issue for question 28.) DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
30. If there is ever a controversy as to the accuracy of a piece of data in the data warehouse environment, how will the conflict be resolved? Has ownership (or at least source identification) been done for each unit of data in the data warehouse environment? Will ownership be able to be established if the need arises? Who will address the issues of ownership? Who will be the final authority as to the issues of ownership?
ISSUE:Ownership or stewardship of data is an essential component of success in the data warehouse environment. It is inevitable that at some moment in time the contents of a database will come into question. The designer needs to plan in advance for this eventuality. DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
31. How will corrections to data be made once data is placed in the data warehouse environment? How frequently will corrections be made? Will corrections be monitored? If there is a pattern of regularly occurring changes, how will corrections at the source (that is, operational) level be made?
ISSUE: On an infrequent, nonscheduled basis, changes made need to be made in the data warehouse environment. If there appears to be a pattern to these changes, then the DSS analyst needs to investigate what is wrong in the operational system.
32. Will public summary data be stored separately from normal primitive DSS data? How much public summary data will there be? Will the algorithm required to create public summary data be stored?
ISSUE: Even though the data warehouse environment contains primitive data, it is normal for there to be public summary data in the data warehouse environment as well. The designer needs to have prepared a logical place for this data to reside. DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
33. What security requirements will there be for the databases in the data warehouse environment? How will security be enforced?
ISSUE: The access of data becomes an issue, especially as the detailed data becomes summarized or aggregated, where trends become apparent. The designer needs to anticipate the security requirements and prepare the data warehouse environment for them.
34. What audit requirements are there? How will audit requirements be met?
ISSUE: As a rule, a system audit can be done at the data warehouse level, but this is almost always a mistake. Instead, detailed record audits are best done at the system-of-record level.
35. Will compaction of data be used? Has the overhead of compacting and decompacting data been considered? What is the overhead? What are the savings in terms of DASD for compacting and decompacting data?
ISSUE: On one hand, compaction or encoding of data can save significant amounts of space. On the other hand, both compacting and encoding data require CPU cycles as data is decompacted or decoded on access. The designer needs to make a thorough investigation of these issues and a deliberate trade-off in the design.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
36. Will encoding of data be done? Has the overhead of encoding and decoding been considered? What, in fact, is the overhead?
ISSUE: (See the issue for question 35.)
37. Will metadata be stored for the data warehouse environment?
ISSUE: Metadata needs to be stored with any archival data as a matter of policy. There is nothing more frustrating than an analyst trying to solve a problem using archival data when he or she does not know the meaning of the contents of a field being analyzed. This frustration can be alleviated by storing the semantics of data with the data as it is archived. Over time, it is absolutely normal for the contents and structure of data in the data warehouse environment to change. Keeping track of the changing definition of data is something the designer should make sure is done. DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
No comments:
Post a Comment