Caso estudio modelamiento dimensional.pdf

8

Chapter 8.

Case Study: Analyzing a dimensional model
In this chapter we review an existing design of a dimensional model and provide guidelines to improve the model. So, the flow of this chapter is different because we typically describe how to design a new model from the beginning. In short, in this chapter the discussions focus on the following topics: Study the business of afictitious company. Understand the business needs for creating a dimensional model. Review the draft dimensional model. Discuss guidelines for reviewing the existing dimensional model. Make improvements to the draft dimensional model, and discuss the reasons for doing so.

© Copyright IBM Corp. 2006. All rights reserved.

425

8.1 Case Study - Sherpa and Sid Corporation
In this section, we brieflydescribe the fictitious Sherpa and Sid Corporation and its business. In doing so, we describe the reason that the corporation has submitted a request to build a data mart.

8.1.1 About the company
Sherpa and Sid Corporation started as a manufacturer of cellular telephones, and then quickly expanded to include a broad range of telecommunication products. As the demand for, and size of, its suiteof products grew, Sherpa and Sid Corporation closed down the existing distribution channels and opened its own sales outlets. In the past year Sherpa and Sid Corporation opened new plants, sales offices, and stores in response to increasing customer demand. With its focus firmly on expansion, the corporation put little effort into measuring the effectiveness of the expansion. Sherpa and Sid’sgrowth has started to level off, and management is refocusing on the performance of the organization. However, although cost and revenue figures are available for the company as a whole, little data is available at the manufacturing plant or sales outlet level regarding cost, revenue, and the relationship between them. To rectify this situation, management has requested a series of reports from theInformation Technology (IT) department. IT responded with a proposal to implement a data mart. After consideration of the potential costs and benefits, management agreed.

8.1.2 Project definition
Senior management and IT put together a project definition consisting of the following objective and scope:

Project objective
To create a data mart to facilitate the analysis of cost and revenuedata for products manufactured and sold by Sherpa and Sid Corporation.

Project scope
The project will be limited to direct costs and revenues associated with products. Currently, Sherpa and Sid Corporation manufacturing costs cannot be allocated at the product level. Therefore, only component costs can be included. At a future time, rules for allocation of manufacturing and overhead costs may becreated, so the data mart should be flexible enough to accommodate future changes. IT

426

Dimensional Modeling: In a Business Intelligence Environment

created a team consisting of one data analyst, one process analyst, the manufacturing plant manager, and one sales region manager for the project.

8.2 Business needs review
In this section, we review the requirements gathering metadata. In other words, we review what the project team defined, and felt they needed to investigate, in order to understand the business need for the Sherpa and Sid Corporation. The understanding of the business will greatly help in the review of the data mart. The team identified the following areas of interest: Life Cycle of a product Anatomy of a sale Structure of the organization Defining costand revenue What do the users want?

8.2.1 Life cycle of a product
The project team first studied the life cycle of a product. Each manufacturing plant has a research group that tests new product ideas. Only after the manufacturing process has been completely defined and approval for the new product has been obtained is the product information added to the company's records. Once the product...