Chapter 3

Challenges of Data Warehousing

• To identify information objects

2. Convert the noun to singular. Generalize, if necessary.

List the nouns that were converted

Product, employee, customer, shipper, time [day, month, year], promotional campaign, (sale) total, unit price, discount, freight, region

• To identify events

2. Convert the verbs to present tense. Combine verbs with similar meanings.

List the verbs that were converted.

Order, sale [purchase], ship

• To identify key performance indicators

1. Review the interview to identify key performance indicators.

List the nouns or noun pairs that imply a process measurement (such as liquidity index, ROI, and breakeven point).

Units sold, sales, revenue, product gross profit

• To identify dimensions

1. Review the report and provide answers.

List the lowest-level dimension for the column headings.

Customer (such as Customer 1, Customer 2, Customer 3)

List the lowest-level dimension for the row headings.

Product Name (such as Tailspin Toys)

List the dimensions in the report heading

Time (such as May 1998); Location (such as WA, U.S.)

• To identify facts

1. Review the table and provide an answer.

List the intersection of the lowest-level dimension column and row headings.

Quantity sold

• To identify hierarchies for the dimensions

1. Review the table and provide answers.

List all aggregated column headings.

City (such as Kirkland)

City Totals

Product Sold Totals

List all aggregated row headings.

Product (such as Cascade Coffee Roasters, Mightyflight Toys)

Category (such as Beverages, Toys)

List all higher-level hierarchies in the report heading for the Time dimension.

Year (such as 1998)

Review Questions

1. Status on an individual order is best kept on which type of system, analytical or operational?

Operational

2. How are key performance indicators derived from roles during the requirements definition and architecture analysis phase?

Roles yield analytical processes, analytical processes yield information objects and events, and events yield KPIs.

3. What types of information does an analysis of the data lifecycle provide the process?

Periodicity (how often is the data changed), volume (how much data is there), data quality/integration (how reliable is the data), and complexity (how difficult is it to get at the data).

4. What activities are used to collect information during the opportunity evaluation phase?

Facilitated sessions with senior-level business and technical representatives

Review of the enterprise-level documentation and related materials

5. Your organization wants to set up a data warehouse. This warehouse will have inputs from seven different operational systems and will ultimately contain many subject areas. It is a big project. How do you make it manageable?

Prioritize the subject areas and limit the scope of the initial iteration. After the initial iteration is successful, implement the remaining subject areas in a series of phased projects.

Or:

Break up the subject areas into data marts that roll up into an enterprise data warehouse. Coordinate the data mart design by taking an enterprise-level perspective on design. After the data mart designs are coordinated, treat each as a separate project.

6. What are some challenges unique to data warehousing?

Dependency on other systems for input

Longer time frames for implementation

7. To satisfy the needs of your own organization, consider whether you would use a centralized data warehouse, data marts, or a combination of both. List the three main reasons that support your decision.