REVIEW QUESTIONS 1. What are the essential differences between defining requirements for operational systems and for data warehouses? In providing information about the requirements for an operational system, the users are able to give you precise details of the required functions, information content, and usage patterns. In striking contrast, for a data warehousing system, the users are generally unable to define their requirements clearly. They cannot define precisely what information they really want from the data warehouse, nor can they express how they would like to use the information or process it. For most of the users, this could be the very first data warehouse they are being exposed to. The users are familiar with operational systems because they use these in their dailywork, so they are able to visualize the requirements for other new operational systems. They cannot relate a data warehouse system to anything they have used before. If, therefore, the whole process of defining requirements for a data warehouse is so nebulous.
2. Explain business dimensions. Why and how can business dimensions be useful for defining requirements for the data warehouse? Even though the users cannot fully describe what they want in a data warehouse, they can provide you with very important insights into how they think about the business. They can tell you what measurement units are important for them. Each user department can let you know how they measure success in that particular department. The users can give you insights into how they combine the various pieces of information for strategic decision making. Managers think of the business in terms of business dimensions. Figure 5-1 shows the kinds of questions managers are likely to ask for decision making. The figure shows what questions a typical marketing vice president, a marketing manager, and a financial controller may ask.
If the users of the data warehouse think in terms of business dimensions for decision making, you should also think of business dimensions while collecting requirements. Although the actual proposed usage of a data warehouse could be unclear, the business dimensions used by the managers for decision making are not nebulous at all. The users will be able to describe these business dimensions to you. You are not totally lost in the process of requirements definition. You can find out about the business dimensions.
3. What data does an information package contain? We will now introduce a novel idea for determining and recording information requirements for a data warehouse. This concept helps us to give a concrete form to the various insights, nebulous thoughts, and opinions expressed during the process of collecting requirements. The information packages, put together while collecting requirements, are very useful for taking the development of the data warehouse to the next phases. Let us look at an information package for analyzing sales for a certain business. Figure 5-4 contains such an information package. The subject here is sales. The measured facts or the measurements that are of interest for analysis are shown in the bottom section of the package diagram. In this case, the measurements are actual
sales, forecast sales, and budget sales. The business dimensions along which these measurements are to be analyzed are shown at the top of the diagram as column headings. In our example, these dimensions are time, location, product, and demographic age group. Each of these business dimensions contains a hierarchy or levels. For example, the time dimension has the hierarchy going from year down to the level of individual day. The other intermediary levels in the time dimension could be quarter, month, and week. These levels or hierarchical components are shown in the information package diagram.
4. What are dimension hierarchies? Give three examples. When a user analyzes the measurements along a business dimension, the user usually would like to see the numbers first in summary and then at various levels of detail. What the user does here is to traverse the hierarchical levels of a business dimension for getting the details at various levels. The dimension hierarchies are the paths for drilling down or rolling up in our analysis. For example, the user first sees the total sales for the entire year. Then the user moves down to the level of quarters and looks at the sales by individual quarters. After this, the user moves down further to the level of individual months to look at monthly numbers.
5. Explain business metrics or facts with five examples. What exactly are the users analyzing? What numbers are they analyzing? The numbers the users analyze are the measurements or metrics that measure the success of their departments. These are the facts that indicate to the users how their departments are doing in fulfilling their departmental objectives. Examples: Actual sale price, Options price, Full price, Dealer add-ons, Dealer credits, Dealer invoice, Amount of down payment, Manufacturer proceeds, Amount financed. Occupied rooms, Vacant rooms, Unavailable rooms, Number of occupants, Revenue.
6. List the types of users who must be interviewed for collecting requirements. What information can you expect to get from them? The types of users of the data warehouse as follows:
Senior executives (including the sponsors)
Key departmental managers
Business analysts
Operational system database administrators (DBAs)
Others nominated by the above
Executives will give you a sense of direction and scope for your data warehouse. They are the ones closely involved in the focused area. The key departmental managers are the ones who report to the executives in the area of focus. Business analysts are the ones who prepare reports and analyses for the executives and managers. The operational system DBAs and IT applications staff will give you information about the data sources for the warehouse.
7. In which situations can JAD methodology be successful for collecting requirements? Groups of 20 or fewer persons at a time Use only after getting a baseline understanding of the requirements Not good for initial data gathering Use when free flow of ideas is essential Useful for confirming requirements Efficient when users are scattered across locations
Need to be very well organized
8. Why are reviews of existing documents important? What can you expect to get out of such reviews? Although most of the requirements gathering will be done through interviews, group sessions, and questionnaires, you will be able to gather useful information from the review of existing documentation. Review of existing documentation can be done by the project team without too much involvement from the users of the business units. Scheduling of the review of existing documentation involves only the members of the project team. Documentation from User Departments: What can you get out of the existing documentation? First, let us look at the reports and screens used by the users in the business areas that will be using the data warehouse. You need to find out everything about the functions of the business units, the operational information gathered and used by these users, what is important to them, and whether they use any of the existing reports for analysis. You need to look at the user documentation for all the operational systems used. You need to grasp what is important to the users. The business units usually have documentation on the processes and procedures in those units. How do the users perform their functions? Review in detail all the processes and procedures. You are trying to find out what types of analyses the users in these business units are likely to be interested in. Review the documentation and then augment what you have learned from the documentation prepared from the interview sessions. Documentation from IT: The documentation from the users and the interviews with the users will give you information on the metrics used for analysis and the business dimensions along which the analysis gets done. But from where do you get the data for the metrics and business dimensions? These will have to come from internal operational systems. You need to know what is available in the source systems. Where do you turn for information available in the source systems? This is where the operational system DBAs and application experts from IT become very important for gathering data. The DBAs will provide you with all the data structures, individual data elements, attributes, value domains, and relationships among fields and data structures. From the information you have gathered from the users, you will then be able to relate the user information to the source systems as ascertained from the IT personnel.
Work with your DBAs to obtain copies of the data dictionary or data catalog entries for the relevant source systems. Study the data structures, data fields, and relationships. Eventually, you will be populating the data warehouse from these source systems, so you need to understand completely the source data, the source platforms, and the operating systems. Now let us turn to the IT application experts. These professionals will give you the business rules and help you to understand and appreciate the various data elements from the source systems. You will learn about data ownership, about people responsible for data quality, and how data is gathered and processed in the source systems. Review the programs and modules that make up the source systems. Look at the copy books inside the programs to understand how the data structures are used in the programs.
9. Various data sources feed the data warehouse. What are the pieces of information you need to get about data sources? This piece of information is essential in the requirements definition document. Include all the details you have gathered about the source systems. You will be using the source system data in the data warehouse. You will collect the data from these source systems, merge and integrate it, transform the data appropriately, and populate the data warehouse. Typically, the requirements definition document should include the following information:
Available data sources
Data structures within the data sources
Location of the data sources
Operating systems, networks, protocols, and client architectures
Data extraction procedure
Availability of historical data
10. Name any five major components of the formal requirements definition document. Describe what goes into each of these components. 1) Introduction. State the purpose and scope of the project. Include broad project justification. Provide an executive summary of each subsequent section.
2) General Requirements Descriptions. Describe the source systems reviewed. Include interview summaries. Broadly state what types of information requirements are needed in the data warehouse. 3) Specific Requirements. Include details of source data needed. List the data transformation and storage requirements. Describe the types of information delivery methods needed by the users. 4) Information Packages. Provide as much detail as possible for each information pack-age. Include this in the form of package diagrams. 5) Other Requirements. Cover miscellaneous requirements such as data extract frequencies, data loading methods, and locations to which information must be delivered. 6) User Expectations. State the expectations in terms of problems and opportunities. Indicate how the users expect to use the data warehouse. 7) User Participation and Sign-Off. List the tasks and activities in which the users are expected to participate throughout the development life cycle. 8) General Implementation Plan. At this stage, give a high-level plan for implementation.
EXERCISES 1. Indicate if true or false: A) Requirements definitions for a sales processing operational system and a sales analysis data warehouse are very similar. (False) B) Managers think in terms of business dimensions for analysis.
(True)
C) Unit sales and product costs are examples of business dimensions.
()
D) Dimension hierarchies relate to drill-down analysis.
(False)
Correct: To drill-down analysis or rolling up analysis E) Categories are attributes of business dimensions.
()
F) JAD is a methodology for one-on-one interviews.
(False)
G) Questionnaires provide the least interactive method for gathering requirements. (True) H) The departmental users provide information about the company’s overall direction. ()
I) Departmental managers are very good sources for information on data structures of operational systems. () J) Information package diagrams are essential parts of the formal requirements definition document. (True)
2. You are the vice president of marketing for a nation-wide appliance manufacturer with three production plants. Describe any three different ways you will tend to analyze your sales. What are the business dimensions for your analysis? 3. BigBook, Inc. is a large book distributor with domestic and international distribution channels. The company orders from publishers and distributes publications to all the leading booksellers. Initially, you want to build a data warehouse to analyze shipments that are made from the company’s many warehouses. Determine the metrics or facts and the business dimensions. Prepare an information package diagram. 4. You are on the data warehouse project of AuctionsPlus.com, an Internet auction company selling upscale works of art. Your responsibility is to gather requirements for sales analysis. Find out the key metrics, business dimensions, hierarchies, and categories. Draw the information package diagram. 5. Create a detailed outline for the formal requirements definition document for a data warehouse to analyze product profitability of a large department store chain.
