Data Warehouse Review Analysis (Part-2)
NEERAJ NATHANI DATA WAREHOUSE ARCHITECTURE DESIGNER
38. Will reference tables be stored in the data warehouse environment?
ISSUE: (See the issue for question 37.)
39. What catalog or dictionary will be maintained for the data warehouse environment? Who will maintain it? How will it be kept up-to-date? To whom will it be made available?
ISSUE: Not only is keeping track of the definition of data over time an issue, but keeping track of data currently in the data warehouse is important as well.
40. Will update (as opposed to loading and access of data) be allowed in the data warehouse environment? (Why? How much? Under what circumstances? On an exception-only basis?)
ISSUE: If any updating is allowed on a regular basis in the data warehouse environment, the designer should ask why. The only update that should occur should be on an exception basis and for only small amounts of data. Any exception to this severely compromises the efficacy of the data warehouse environment.
When updates are done (if, in fact, they are done at all), they should be run in a private window when no other processing is done and when there is slack time on the processor.
41. What time lag will there be in getting data from the operational to the data warehouse environment? Will the time lag ever be less than 24 hours? If so, why and under what conditions? Will the passage of data from the operational to the data warehouse environment be a "push" or a "pull" process? DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
ISSUE: As a matter of policy, any time lag less than 24 hours should be questioned. As a rule, if a time lag of less than 24 hours is required, it is a sign that the developer is building operational requirements into the data warehouse. The flow of data through the data warehouse environment should always be a pull process, where data is pulled into the warehouse environment when it is needed, rather than being pushed into the warehouse environment when it is available.
42. What logging of data warehouse activity will be done? Who will have access to the logs?
ISSUE: Most DSS processing does not require logging. If an extensive amount of logging is required, it is usually a sign of a lack of understanding of what type of processing is occurring in the data warehouse environment.
43. Will any data other than public summary data flow to the data warehouse environment from the departmental or individual level? If so, describe it.
ISSUE: Only on rare occasions should public summary data come from sources other than departmental or individual levels of processing. If much public summary data is coming from other sources, the analyst should ask why.
44. What external data (that is, data other than that generated by a company's internal sources and systems) will enter the data warehouse environment? Will it be specially marked? Will its source be stored with the data? How frequently will the external data enter the system? How much of it will enter? Will an unstructured format be required? DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
What happens if the external data is found to be inaccurate?
ISSUE: Even though there are legitimate sources of data other than a company's operational systems, if much data is entering externally, the analyst should ask why. Inevitably, there is much less flexibility with the content and regularity of availability of external data, although external data represents an important resource that should not be ignored. DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
45. What facilities will exist that will help the departmental and the individual user to locate data in the data warehouse environment?
ISSUE: One of the primary features of the data warehouse is ease of accessibility of data. And the first step in the accessibility of data is the initial location of the data.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
46. Will there be an attempt to mix operational and DSS processing on the same machine at the same time? If so:
- Why?
- How much processing
- How much data?
ISSUE: For a multitude of reasons, it makes little sense to mix operational and DSS processing on the same machine at the same time. Only where there are small amounts of data and small amounts of processing should there be a mixture. But these are not the conditions under which the data warehouse environment is most cost-effective. See my previous book, Data Architecture: The Information Paradigm (Wellesey, MA: QED/Wiley, 1992) for an in-depth discussion of this issue.
47. How much data will flow back to the operational level from the data warehouse level? At what rate? At what volume? Under what response time constraints? Will the flowback be summarized data or individual units of data?
ISSUE: As a rule, data flows from the operational to the warehouse level to the departmental to the individual levels of processing. There are some notable exceptions. As long as not too much data "backflows," and as long as the backflow is done in a disciplined fashion, there usually is no problem. If there is a lot of data engaged in backflow, then a red flag should be raised.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
48. How much repetitive processing will occur against the data warehouse environment? Will precalculation and storage of derived data save processing time?
ISSUE: It is absolutely normal for the data warehouse environment to have some amount of repetitive processing done against it. If only repetitive processing is done, however, or if no repetitive processing is planned, the designer should question why.
49. How will major subjects be partitioned? (By year? By geography? By functional unit? By product line?) Just how finely does the partitioning of the data break the data up?
ISSUE: Given the volume of data that is inherent to the data warehouse environment and the unpredictable use of the data, it is mandatory that data warehouse data be partitioned into physically small units that can be managed independently. The design issue is not whether partitioning is to be done. Instead, the design issue is how partitioning is to be accomplished. In general, partitioning is done at the application level rather than the system level.
The partitioning strategy should be reviewed with the following in mind:
- Current volume of data
- Future volume of data
- Current use of data
- Future use of data
- Partitioning of other data in the warehouse
- Use of other data>
- Volatility of the structure of data
· DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
50. Will sparse indexes be created? Would they be useful?
ISSUE: Sparse indexes created in the right place can save huge amounts of processing. By the same token, sparse indexes require a fair amount of overhead in their creation and maintenance. The designer of the data warehouse environment should consider their use.
51. What temporary indexes will be created? How long will they be kept? How large will they be?
ISSUE: (See the issue for question 50, except as it applies to temporary indexes.)
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
52. What documentation will there be at the departmental and individual levels? What documentation will there be of the interfaces between the data warehouse environment and the departmental environment? Between the departmental and the individual environment? Between the data warehouse environment and the individual environment?
ISSUE: Given the free-form nature of processing in the departmental and the individual environments, it is unlikely that there will be much in the way of available documentation. Documentation of the relationships between the environments is important for the reconcilability of data.
53. Will the user be charged for departmental processing? For individual processing? Who will be charged for data warehouse processing?
ISSUE: It is important that users have their own budgets and be charged for resources used. The instant that processing becomes "free," it is predictable that there will be massive misuse of resources. Achargeback system instills a sense of responsibility in the use of resources.
54. If the data warehouse environment is to be distributed, have the common parts of the warehouse been identified? How are they to be managed?
ISSUE: In a distributed data warehouse environment, some of the data will necessarily be tightly controlled. The data needs to be identified up-front by the designer and metadata controls put in place.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
55. What monitoring of the data warehouse will there be? At the table level? At the row level? At the column level?
ISSUE: The use of data in the warehouse needs to be monitored to determine the dormancy rate. Monitoring must occur at the table level, the row level, and the column level. In addition, monitoring of transaction needs to occur as well.
56. Will Class IV ODS be supported? How much performance impact will there be on the data warehouse to support Class IV ODS processing?
ISSUE: Class IV ODS is fed from the data warehouse. The data needed to create the profile in the Class IV ODS is found in the data warehouse.
57. What testing facility will there be for the data warehouse?
ISSUE: Testing in the data warehouse is not the same level of importance as in the operational transaction environment. But occasionally there is a need for testing, especially when new types of data are being loaded and when there are large volumes of data.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
58. What DSS applications will be fed from the data warehouse? How much volume of data will be fed?
ISSUE: DSS applications, just like data marts, are fed from the data warehouse. There are the issues of when the data warehouse will be examined, how often it will be examined, and what performance impact there will be for the analysis.
59. Will an exploration warehouse and/or a data mining warehouse be fed from the data warehouse? If not, will exploration processing be done directly in the data warehouse? If so, what resources will be required to feed the exploration and/or data mining warehouse?
ISSUE: The creation of an exploration warehouse and/or a data mining data warehouse can greatly alleviate the resource burden on the data warehouse. An exploration warehouse is needed when the frequency of exploration is such that statistical analysis starts to have an impact on data warehouse resources.
The issues here are the frequency of update and the volume of data that needs to be updated. In addition, the need for an incremental update of the data warehouse occasionally arises.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
60. What resources are required for loading data into the data warehouse on an ongoing basis? Will the load be so large that it cannot fit into the window of opportunity? Will the load have to be parallelized?
ISSUE: Occasionally there is so much data that needs to be loaded into the data warehouse that the window for loading is not large enough. When the load is too large, there are several options:
- Creating a staging area where much preprocessing of the data to be loaded can be done independently
- Parallelizing the load stream so that the elapsed time required for loading is shrunk to the point that the load can be done with normal processing
- Editing or summarizing the data to be loaded so that the actual load is smaller
· DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
61. To what extent has the midlevel model of the subject areas been created? Is there a relationship between the different midlevel models?
ISSUE: Each major subject area has its own midlevel data model. As a rule, the midlevel data models are created only as the iteration of development needs to have them created. In addition, the midlevel data models are related in the same way that the major subject areas are related.
62. Is the level of granularity of the data warehouse sufficiently low enough to service all the different architectural components that will be fed from the data warehouse?
ISSUE: The data warehouse feeds many different architectural components. The level of granularity of the data warehouse must be sufficiently low to feed the lowest level of data needed anywhere in the corporate information factory (CIF). This is why it is said that the data in the data warehouse is at the lowest common denominator.
63. If the data warehouse will be used to store eBusiness and clickstream data, to what extent does the Granularity Manager filter the data?
ISSUE: The Web-based environment generates a huge amount of data. The data that is generated is at much too low a level of granularity. To summarize and aggregate the data before entering the data warehouse, the data is passed through a Granularity Manager. The Granularity Manager greatly reduces the volume of data that finds its way into the data warehouse.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
64. What dividing line is used to determine what data is to be placed on disk storage and what data is to be placed on alternate storage?
ISSUE: The general approach that most organizations take in the placement of data on disk storage and data on alternate storage is to place the most current data on disk storage and to place older data on alternate storage. Typically, disk storage may hold two years' worth of data, and alternate storage may hold all data that is older than two years.
65. How will movement of data to and from disk storage and alternate storage be managed?
ISSUE: Most organizations have software that manages the traffic to and from alternate storage. The software is commonly known as a cross-media storage manager (CMSM).
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
66. If the data warehouse is a global data warehouse, what data will be stored locally and what data will be stored globally?
ISSUE: When a data warehouse is global, some data is stored centrally and other data is stored locally. The dividing line is determined by the use of the data.
67. For a global data warehouse, is there assurance that data can be transported across international boundaries?
ISSUE: Some countries have laws that do not allow data to pass beyond their boundaries. The data warehouse that is global must ensure that it is not in violation of international laws.
68. For ERP environments, has it been determined where the data warehouse will be located — inside the ERP software or outside the ERP environment?
ISSUE: Many factors determine where the data warehouse should be placed:
- Does the ERP vendor support the data warehouse?
- Can non-ERP data be placed inside the data warehouse?
- What analytical software can be used on the data warehouse if the data warehouse is placed inside the ERP environment?
- If the data warehouse is placed inside the ERP environment, what DBMS can be used?
· DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
69. Can alternate storage be processed independently?
ISSUE: Older data is placed in alternate storage. It is often quite useful to be able to process the data found in alternate storage independently of any consideration of data placed on disk storage.
70. Is the development methodology that is being used for development a spiral development approach or a classical waterfall approach?
ISSUE: The spiral development approach is always the correct development approach for the data warehouse environment. The waterfall SDLC approach is never the appropriate approach.
71. Will an ETL tool be used for moving data from the operational environment to the data warehouse environment, or will the transformation be done manually?
ISSUE: In almost every case, using a tool of automation to transform data into the data warehouse environment makes sense. Only where there is a very small amount of data to be DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
loaded into the data warehouse environment should the loading of the data warehouse be done manually.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
72. Will unstructured data be entered into the data warehouse?
ISSUE: Unstructured data can be very valuable in the data warehouse. But integrating unstructured data is difficult to do. To be useful, unstructured data must be edited and organized before it can be entered into the data warehouse. The editing includes the removal of stop words and the stemming of words. In addition, identifiers must be found. There are two kinds of useful identifiers: identifiers and close identifiers. Identifiers are those terms which serve to specifically identify an account. Typical identifiers are Social Security number, license number, and employee number. Typical close identifiers are name, address, and other descriptive information.
In addition to text being edited, unstructured data needs to be screened. Much of text is what can be termed "blather." Blather does not contribute to the business intelligence and does not belong in the data warehouse.
73. Will corporate communications be organized before being placed in the data warehouse?
ISSUE: Corporate communications can be very valuable to the data warehouse. Typically, corporate communications are useful for the purpose of complementing CRM data. However, in order for corporate communications to be useful, it must be edited and organized according to identifiers. In addition, it helps to sort through the communications, identifying which ones are critical and which ones are noncritical.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
74. Is there ever a need for referring back to the unstructured environment?
ISSUE: On occasion, there is the need to refer back to the unstructured environment. The problem is that there is no guarantee that the data found in the unstructured environment will be there when referenced. Emails are deleted. Text files are erased. Data changes location. What is the contingency plan for finding data once displaced in the unstructured environment?
75. Unstructured data in the data warehouse environment can take up large amounts of space. How can the space required for unstructured data in the structured environment be minimized?
ISSUE: Data warehouses grow large with no help from anyone. But when unstructured data is added to a data warehouse, the potential for very large volumes of data grows. Several techniques can be employed to minimize the space required for unstructured data, including the following:
- The use of simple indexes. If a unit of unstructured data is not required online, simply create an index that points to where the unstructured data is found.
- Use the first n bytes of an unstructured document to allow the user to look at what the document look like from the beginning. In doing so, it may not be necessary to store the entire document.
- Carry context surrounding keywords. The context surrounding key words tells the user little about the document and a lot about the context within which keywords are found and used.
· DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
76. Is the data warehouse regularly monitored in terms of use?
ISSUE: As data warehouses grow large, the volumes of data contained inside them starts to exhibit different patterns of use. Some data is used infrequently; other data is used frequently. It is very important that the data administrator knows when this division in the patterns of use of data starts to occur.
77. Is data monitored by column for use?
ISSUE: It is not enough to monitor the use of data by rows. Occasionally, columns of data that are never used will be included in a data warehouse. It makes sense to remove those columns.
78. How many resources does the data monitor consume?
ISSUE: Typically, the monitor provided by the DBMS vendor uses huge amounts of resources. A common practice is to turn off the monitor during peak-period processing. Unfortunately, the peak period is the very moment when you want the monitor to be turned on. Third-party software is usually much more efficient in monitoring the data warehouse than vendor-supplied software.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
79. Is data monitored by row for use?
ISSUE: The most common way of measuring use is by row. Rows of data that are not being accessed should be removed from the data warehouse to near-line storage or archival storage.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
80. How will data be managed in terms of transmitting the data from disk storage to near-line storage or from disk storage to archival storage?
ISSUE: Will a CMSM be used? Will the transportation be done by hand? How long will the transportation be done by hand? What sort of operating window is required?
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
81. What signals the system that a query may be accessing data from nearline storage or archival storage?
ISSUE: If the system waits until a query goes into execution, then the system has only awkward choices as to accessing data from near-line storage or archival storage. It is much better if the end user submits a queuing request before the query is submitted. An alternative is to parse and queue requests that will look at data from the near-line environment.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
82. What rate of growth will there be for the data?
ISSUE: Not only is a large amount of data needed for the data warehouse, but the data's growth is an issue as well. It is wise to anticipate growth and make sure that there is space enough before the space is needed.<.p>
83. Will a miltidimensional database design be used for the data warehouse?
ISSUE: Miltidimensional design is for data marts and other analytical structures. Miltidimensional design does not fit with the data warehouse.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
84. Will statistical analysis be done to any extent in the data warehouse?
ISSUE: If statistical analysis will be done to any extent in a data warehouse, strong consideration should be given to an exploration warehouse.
85. Will external data be entered into the data warehouse for the purpose of use in statistical analysis?
ISSUE: If external data is being placed in the data warehouse for the purpose of statistical analysis, it may make sense to build a separate exploration warehouse and put the external data in the exploration warehouse.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
86. Are data marts being placed on the same physical processor as the data warehouse?
ISSUE: For a variety of reasons, it makes almost no sense to place data marts on the same physical processor as the data warehouse. Because of the differences in work load, the cost of the machine cycles, and the volumes of data that are collected there, moving data marts to a separate processor always makes sense.
In fact, moving data marts to separate processors from other data marts usually makes sense. By moving data marts to separate processors, different organizational units can be assigned to a processor and can take ownership of the data and the processing that occurs inside the data mart.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
87. Has the data velocity been calculated for a data warehouse? Does there need to be a higher velocity?
ISSUE: Data has velocity as it passes through the data warehouse. On occasion, it is necessary to push the data through the data warehouse as quickly as possible. When that is the case, the cost of high velocity can be quite high. The business case for high velocity needs to be examined. On occasion, the need for high velocity may be a symptom of the building of operational systems in the data warehouse environment. If that is the case, the operational processing needs to be placed elsewhere.
88. Is clickstream data being entered into the data warehouse? If so, is the clickstream data being passed through the Granularity Manager?
ISSUE: Clickstream data is data that is generated by the Web environment. Approximately 90 percent of clickstream data needs to be eliminated or consolidated before it is entered into the data warehouse.
89. Is clickstream data entered directly into the data warehouse without first passing through a Granularity Manager?
ISSUE: Data should never enter the data warehouse directly from the Web environment without first passing through the Granularity Manager. There is enough data in the data warehouse without contaminating the data there with massive amounts of irrelevant detailed data.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
90. Does data flow directly from the data warehouse to the Web environment?
ISSUE: The proper flow of data is from the data warehouse to the ODS environment. Once in the ODS environment, data is collected and then is available to the Web-based environment. In almost every case, the flow of data from the data warehouse environment to the Web is a very bad idea. The data warehouse is not geared to support the response times needed by the Web environment.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
91. Is "real-time" data warehousing being done in the data warehouse?
ISSUE: Real-time data warehousing is best done in the ODS environment. The ODS environment is physically separate from the data warehouse environment. While it is true that the data warehouse environment can withstand some small amounts of real-time processing at certain slow periods of the day, to make a real-time processing environment out of the data warehouse is a strategic mistake.
92. Are profile records being collected and created in the ODS environment?
ISSUE: One of the best uses of the ODS environment is to collect detailed transaction data in the data warehouse and then use that detailed data for the creation of a profile record in the ODS. Once the profile record is created in the ODS, the profile data is ready in milliseconds for access, from the Web environment or elsewhere.
93. Is data being used directly from the data warehouse?
ISSUE: Over time, the use of data from the data warehouse shifts from direct use to indirect use. If, after five years, there is still a lot of direct use of data from the data warehouse, then it may be time to ask the question, "Should we build some data marts of other analytical applications?"
94. Are end users being prevented from using data in the data warehouse?
ISSUE: While it is true that data from the data warehouse has few users, it is a bit extreme to say that no one should directly use the data in the data warehouse. Even in the mature days of a data warehouse, there will be a few cases where direct access to the data warehouse is needed and is a good thing to do.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
95. Is data being monitored to see if patterns of use are starting to appear?
ISSUE: If patterns of use are starting to appear in the data warehouse, it is time to ask the question, "Should data marts or other forms of analytical processing be created?"
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
96. What kind of training are your end users receiving in the usage data warehouse?
ISSUE: In many cases, training your user on the capabilities of the data warehouse can pay very big dividends.
97. How are your users being kept abreast of the changes that are being made in the data warehouse?
ISSUE: The data warehouse changes over time. New data and new features of the data warehouse may produce big benefits for your users, but not if your users don't know about what's new.
DATA WAREHOUSE ARCHITECTURE DESIGN QUESTIONS BY NEERAJ NATHANI
98. Is the data warehouse being used primarily by farmers or by explorers?
ISSUE: In the well-rounded data warehouse environment, there normally is a mixture of both farmers and explorers who use the data warehouse. If you are not using the data warehouse in multiple ways, then you are not getting your full money's worth from your data warehouse.
