ABC Business Scenario Goal

In document oracle bi 11g r1 build repositories ed 1.1 activity guide.pdf (Page 30-35)

Overview of Practices for Lesson 3 Lesson Overview

Practice 3-1: ABC Business Scenario Goal

To read the business scenario for the fictitious company used throughout the course Time

10 minutes Background

ABC sells food and nonfood items to restaurant chain customers throughout the United States.

ABC’s product line includes food, condiments, cookware, clothing, and other miscellaneous restaurant supplies.

ABC employees currently rely on reports generated by their IT department to analyze sales and shipment data. These reports are generated monthly and, therefore, contain static information.

ABC is looking for a way to generate dynamic, interactive reports to analyze this data in order to effectively manage its orders, monitor sales performance, and increase overall customer

satisfaction. ABC recognizes the importance of a business intelligence solution that will provide its employees with the data and tools that they need to query large data sets, generate reports, analyze data, identify trends, and monitor business performance.

To achieve these goals, ABC has decided to implement an Oracle Business Intelligence (BI) solution. The solution will enable its sales executives and operations managers to answer business questions that are important to running the part of the business for which they are responsible. The desired system will provide timely, up-to-date sales order data and provide sales and shipment performance monitoring, which will allow ABC to improve its customer service efforts. ABC also has the vision of making this data directly available to customers, so that they can analyze their purchase history better and compare it with what is being purchased nationally or regionally. This initiative is consistent with ABC’s commitment to deliver a high-quality business intelligence solution that will set it apart from its competitors, and result in additional sales and revenue.

Business Requirements

You are a consultant hired by ABC to implement a business intelligence solution. From your initial interviews with the managers at ABC, you obtain the following information about ABC:

• Managers want to use their own information to ask and answer questions about the sales history of their products, the buying history of their customers, the order fulfillment performance of their operations group, and the selling performance of the sales force.

• The company has little experience in data analysis and does not expect to hire any data analysts in the future. Therefore, the managers want to be able to ask relevant questions and analyze the results themselves using an intuitive user interface.

• The database has approximately 350,000 invoice-level records that span the period from January 2, 2008 to April 21, 2009.

To make this kind of information widely available within the enterprise, the business intelligence solution must have a structure that is consistent with the way employees think about the

business. From further interviews and an examination of the existing sample reports presenting invoice-based data, you determine the following:

• ABC employees think about their business in terms of sales, products and product hierarchies, time periods, and relationships between customers and the sales force.

Oracle University and 9189 4972 Quebec Incฺ DBA Montreal CollegeฺCA use onlyฺ

Development Program (WDP) eKit materials are provided for WDP in-class use onlyฺ Copying eKit materials is strictly prohibited and is in violation of Oracle copyrightฺ All WDP students must receive an eKit watermarked with their name and emailฺ Contact OracleWDP_ww@oracleฺcom if you have not received your personalized eKitฺ

• ABC employees measure product data at five levels. The product levels from the top level to the bottom (most detailed) level are:

− Total

− Type

− Subtype

− Generic Product

− Specific Product

• ABC employees measure sales organization data at five levels. These levels mirror organizational management responsibilities. They are (in descending order):

− Total

− Region

− District

− Sales Representative

− Customer

• ABC employees measure time data at five levels:

− Total time

− Year

− Quarter

− Month

− Day

• Many existing reports contain data presented at these levels. An example of a report containing product data levels is:

− Total Product sales

− Product Type sales: Cheese

− Product Subtype sales: American Cheese

− Generic Product sales: American Cheese Slices

− Specific Product sales: 2 Pack American Cheese Slices 16 Slices

In addition to these hierarchies, ABC wants to group and analyze customers using geographical attributes such as region, city, and state. They also want to analyze products by characteristics such as diet type and suppliers.

The most common measures used in reports are dollar sales, units ordered, units shipped, and net weight shipped. ABC would like to analyze these measures at all levels of the hierarchies, by the important product and customer attributes, by the various time periods, and be able to compare performance with previous years.

Source Data

By interviewing ABC’s database administrators and examining the documents that they provide, you learn that the source data resides in an invoice system on an Oracle database. The core data containing the business measures is stored in an invoice (orders) table.

In addition to the invoice information, there are various tables providing information about customers, the product lines, the time periods, and the relationship between customers and the company’s sales organization.

Oracle University and 9189 4972 Quebec Incฺ DBA Montreal CollegeฺCA use onlyฺ

Development Program (WDP) eKit materials are provided for WDP in-class use onlyฺ Copying eKit materials is strictly prohibited and is in violation of Oracle copyrightฺ All WDP students must receive an eKit watermarked with their name and emailฺ Contact OracleWDP_ww@oracleฺcom if you have not received your personalized eKitฺ

Copyright © 2011, Oracle and/or its affiliates. All rights reserved.

Practices for Lesson 3: Building the Physical Layer of a Repository

The following screenshots show examples of data in some of the tables:

D1_PRODUCTS

D1_CUSTOMER2

D1_CALENDAR2

Oracle University and 9189 4972 Quebec Incฺ DBA Montreal CollegeฺCA use onlyฺ

Development Program (WDP) eKit materials are provided for WDP in-class use onlyฺ Copying eKit materials is strictly prohibited and is in violation of Oracle copyrightฺ All WDP students must receive an eKit watermarked with their name and emailฺ Contact OracleWDP_ww@oracleฺcom if you have not received your personalized eKitฺ

D1_ORDERS2

Training Objective

The primary objective of this training is to build the metadata and administer Oracle BI Server to support the business requirements of ABC. The metadata will allow ABC employees to build interactive reports and dashboards that they can use to better analyze, monitor, and manage their business, and improve overall customer satisfaction.

The recommended strategy for building metadata is to use an iterative approach. You begin by building a relatively simple repository:

• Minimize the number of source tables.

• Expose only stored measures with simple aggregation rules.

• Use the query log to check query results.

• Create presentation objects and test with Oracle BI Analysis Editor.

After the initial repository is built and tested, you expand the business model:

• Import additional physical tables needed to support the business model.

• Add calculated measures that involve operations on existing columns.

• Add more complex calculated measures (for example, level-based measures and share measures).

• Add time series calculations (for example, percentage change in a measure compared to that in the same period in the previous year).

• Add security information.

• Add aggregate table data sources to improve performance.

• Localize Oracle BI metadata and data.

• Configure many-to-many relationships.

• Configure implicit fact columns.

Oracle University and 9189 4972 Quebec Incฺ DBA Montreal CollegeฺCA use onlyฺ

Development Program (WDP) eKit materials are provided for WDP in-class use onlyฺ Copying eKit materials is strictly prohibited and is in violation of Oracle copyrightฺ All WDP students must receive an eKit watermarked with their name and emailฺ Contact OracleWDP_ww@oracleฺcom if you have not received your personalized eKitฺ

Copyright © 2011, Oracle and/or its affiliates. All rights reserved.

Practices for Lesson 3: Building the Physical Layer of a Repository

In the lessons that follow, you follow this strategy to build the business model to support the business requirements of ABC. At first, the repository metadata includes only the basic set of physical tables needed to support the initial business model. You add more tables and complexity in future iterations.

Oracle University and 9189 4972 Quebec Incฺ DBA Montreal CollegeฺCA use onlyฺ

Development Program (WDP) eKit materials are provided for WDP in-class use onlyฺ Copying eKit materials is strictly prohibited and is in violation of Oracle copyrightฺ All WDP students must receive an eKit watermarked with their name and emailฺ Contact OracleWDP_ww@oracleฺcom if you have not received your personalized eKitฺ

Practice 3-2: Gathering Information to Build an Initial Business Model

In document oracle bi 11g r1 build repositories ed 1.1 activity guide.pdf (Page 30-35)