D56261GC10 Appendix A DW

Appendix A:
Practices and Solutions
Oracle University and Egabi Solutions. use only
THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS
COMPUTER IS STRICTLY PROHIBITED
Table of Contents
Practices for Lesson 1 ......................................................................................................... 3
Practice 1-1: Identifying the Available SQL Developer, Oracle Analytic Workspace
Manager (AWM) 11g, Oracle Warehouse Builder (OWB) 11g, Oracle Application
Express 3.1, and Oracle Database 11g Documentation Resources................................. 4
Practice 1-2: Creating and Using New SQL Developer Database Connections........... 11
Practice 1-3: Browsing Your sh and dm Schemas Tables ........................................... 12
Practices for Lesson 2 ....................................................................................................... 15
Practice 2-1: Data Warehousing and Business Intelligence ......................................... 16
Practice 2-2: Exploring the Analytic Workspace Manager (AWM) 11g ..................... 19

Practice 2-3: Accessing OLAP Data Using APEX....................................................... 26
Practice 3-1: Defining Data Warehouse Concepts and Terminology........................... 31
Practice 4-1: Reviewing the RISD Scenario................................................................. 34
Practice 4-2: Business, Logical, Dimensional, and Physical Modeling ....................... 35
Practice 4-3: Creating the CHANNEL1_DIM and TIME1_DIM Dimensions in
Oracle Warehouse Builder......................................................................................... 39
Practice 4-4: Creating the SALES_CUBE Cube Using the Newly Created
Dimensions and the PRODUCT_DIM and CUSTOMER_DIM
Prebuilt Dimensions................................................................................................... 70
Practice 5-1: Database Sizing, Storage, Performance, and Security Considerations.... 81
Practice 6-1: The ETL Process: Extracting Data .......................................................... 84
Practice 6-2: Creating Maps for the CHANNEL1_DIM Dimension and the
SALES1_CUBE Cube................................................................................................... 86
Practices for Lesson 7 ..................................................................................................... 114
Practice 7-1: The ETL Process: Transforming Data................................................... 115
Practice 8-1: The ETL Process: Loading Data ........................................................... 118
Practice 9-1: Refreshing the Warehouse Data ............................................................ 120
Practices for Lesson 10 ................................................................................................... 123
Practice 10-1: Materialized Views.............................................................................. 124
Practices for Lesson 11 ................................................................................................... 127
Practice 11-1: Leaving a Metadata Trail..................................................................... 128
Oracle Database 11g: Data Warehousing Fundamentals A - 2

Practices for Lesson 1
This is the first of many practices in this course. The solutions (if you require them) can
be found in Appendix A: Practices and Solutions. Practices are intended to cover most
of the topics that are presented in the corresponding lesson.
In practice 1-1, you review the available SQL Developer, Oracle Analytic Workspace
Manager (AWM) 11g, Oracle Warehouse Builder (OWB) 11g, and Oracle
Application Express 3.1 resources.
In practice 1-2, you create and use new SQL Developer database connections.
In practice 1-3, you browse some of the tables in the dm schema using SQL
Developer.

Practice 1-1: Identifying the Available SQL Developer, Oracle
Analytic Workspace Manager (AWM) 11g, Oracle Warehouse
Builder (OWB) 11g, Oracle Application Express 3.1, and Oracle
Database 11g Documentation Resources
In this practice, you review the available SQL Developer, Oracle Analytic Workspace
Manager (AWM) 11g, Oracle Warehouse Builder (OWB) 11g, and Oracle Application
Express 3.1 resources.
1) Familiarize yourself with Oracle SQL Developer, as needed, using Appendix C:
Using SQL Developer.

2) Access the online SQL Developer home page on OTN at:
http://www.oracle.com/technology/products/database/sql_developer/index.html
The SQL Developer home page is displayed as follows:
3) Bookmark the page for future access.

No formal solution
4) Access the online SQL Developer tutorial at:
http://st-curriculum.oracle.com/tutorial/SQLDeveloper/index.htm

Database 11g Documentation Resources (continued)

5) Preview and experiment with the available links and demonstrations in the tutorial as
needed, especially the Creating a Database Connection and Accessing Data links.
To review the section on how to create a database connection, click the plus +
sign next to the What to Do First link to display the Creating a Database
Connection link. To review the Creating a Database Connection topic, click the
topics link. To review the section on accessing data, click the plus + sign next
to the Accessing Data link to display the list of available topics. To review any
of the topics, click the topics link.
6) Access the online Oracle OLAP home page on OTN at:
http://www.oracle.com/technology/products/bi/olap/olap.html

The Oracle OLAP home page is displayed as follows:

7) To view the demonstration on the Analytic Workspace Manager, click the Analytic
Workspace Manager 10.2.0.3.0 link in the Demonstration section of the page.
No formal solution
No formal solution
9) Access the online Oracle Warehouse Builder page on OTN at:

http://www.oracle.com/technology/products/warehouse/index.html
The Oracle Warehouse Builder page is displayed as follows:

10) To access the Warehouse Builder Oracle by Example (OBE) tutorials, click the
Oracle Warehouse Builder 11g By Example Course Now Available link.


11) To view any of the Oracle Warehouse Builder tutorials, click the tutorial link in the
List of Tutorials section of the page.
No formal solution
12) Access the Oracle Database 11g Release documentation Web page at:
http://www.oracle.com/pls/db111/homepage

The documentation page is displayed as follows:

No formal solution
14) Display the complete list of books available for Oracle Database 11g Release 1.

Click the Master Book List link in the left frame of the page. The page is
displayed as follows:

15) Make a note of the following documentation references that you use in this course as
needed:
a) Oracle Database 2 Day + Data Warehousing Guide 11g Release 1 (11.1)
b) Oracle Database Data Warehousing Guide 11g Release 1 (11.1)
c) Oracle Database SQL Developer Users Guide Release 1.2
d) Oracle Database Reference 11g Release 1 (11.1)
e) Oracle Database New Features Guide 11g Release 1 (11.1)
f) Oracle Database SQL Language Reference 11g Release 1 (11.1)
g) SQL*Plus Users Guide and Reference Release 11.1
h) Oracle Database SQLJ Developers Guide and Reference 11g Release 1 (11.1)
i) Oracle Database Concepts 11g Release 1 (11.1)
j) Oracle Database Sample Schemas 11g Release 1 (11.1)

Practice 1-2: Creating and Using New SQL Developer Database
Connections
In this practice, you create two new database connections for the sh and dm schemas.
1) Start up SQL Developer.
2) Create a database connection by using the following information:
Step Property Value
a. Connection Name SH Schema
b. Username sh
c. Password sh
d. Hostname Enter the host name as provided by the instructor.

e. Port 1521
f. SID orcl
3) Test the new connection. If the status is Success, connect to the database by using the
new connection.
a) Double-click the SH Schema icon on the Connections tabbed page.
b) Click the Test button in the New/Select Database Connection window. If the
status is Success, click the Connect button.
4) Create a new database connection named DM Schema.
a) Right-click the SH Schema connection in the Object Navigation tree and select
the Properties menu option.
b) Enter DM Schema as the connection name and dm as the username and oracle
as the password, and then click Save. This creates the new connection.
c) Repeat step 3 to test the new DM schema connection.

Practice 1-3: Browsing Your sh and dm Schemas Tables
In this practice, you browse some of the tables in the dm schema.
1) Browse the structure of the SALES_FACT table in the dm schema and display its
data.
a) Double-click the DM Schema database connection.
b) Expand the Tables node by clicking the plus sign next to it.
c) Display the structure and data of the SALES_FACT table.
Double-click the SALES_FACT table. The Columns tab displays the columns in
the SALES_FACT table as follows:

To display the SALES_FACT data, click the Data tab. The data is displayed as
follows:

(continued)
2) Use the SQL Worksheet to select the SALES_FACT rows, where the SALES is
greater than $10,000. Use both the Execute Statement (F9) and the Run Script (F5)
icons to execute the SELECT statement. Review the results of both methods of
executing the SELECT statements on the appropriate tabbed pages.
Note: Familiarize yourself with the data or consult Appendix B: Table
Descriptions, which provides the description and data for all tables in the HR and SH
schemas that you use in this course.
If the SQL Worksheet is not displayed, click the Open SQL Worksheet (Ctrl +
Shift + W) icon in the SQL Developer toolbar. Enter the code as displayed in the

following screen capture, and then click the Execute statement (F9) icon. The
resulting rows are displayed on the Results tabbed page as follows:

(continued)
Click the Run Script (F5) icon. The resulting rows are displayed on the Scripts
Output tabbed page as follows:
Note: Make sure the cursor is on any of the code lines.

In practice 2-1, you answer some questions about what you learned in this lesson.
In practice 2-2, you perform the following tasks:
Start AWM.
Explore the SALESTRACK workspace.
Identify the dimensions and cubes in the workspace.
In practice 2-3, you log in to APEX as a developer and review an interactive report

Practice 2-1: Data Warehousing and Business Intelligence
Answer the following questions:
1) Online transaction processing (OLTP) databases hold up-to-the-minute information
and are most commonly designed as read-only databases.
a) True
b) False
The correct answer is False because OLTP databases are not read-only
databases.
2) State whether the following scenario refers to an operational system or an analytical

processing system:
Show me how a specific brand of printer is selling throughout different parts of the
United States and how this specific brand of printer is selling since it was first
introduced into my stores.
This scenario refers to:
a) An operational system
b) An analytical processing system
The correct answer is b) because comparing sales between the different
territories within the United States can provide a certain type of analytical
information.
3) Are the following statements true or false?
a) Operational systems display the following qualities:
i) Good performance True
ii) Static data contents False
iii) High availability True
iv) Unpredictable CPU use False
b) Identify the reasons why business analysis is not easy with operational systems.
i) Data is not structured for drill-down capability. True
ii) The system is not designed for querying. False
iii) Data analysis can be CPU intensive. True
iv) Data is not integrated between systems. True

(continued)
4) Match the following Oracle technology or tools with their feature or function:
a. Analytic Workspace Manager 1. Integrated online analytical

processing (OLAP) and data mining
b. Oracle Database 11g 2. Apart from performing the complete
extraction, transformation,
and loading (ETL) tasks, creates EUL
c. Oracle Warehouse Builder 3. A tool that is specifically designed to

create and manage OLAP cubes. It

assumes that the source data for the
cube is of high quality, and is often
used in combination with other ETL
and Data Quality processes.
The correct matches are as follows:

a) 3.
b) 1.
c) 2.
5) In groups of three or four, discuss the following questions and present your points to
the class at the end of the discussion.
a) List some of the reasons that your company is considering implementing a data
warehouse or a data mart.
b) What are some of the business problems that your company is trying to answer?
c) Why is the business community in your organization unable to find the answers to
their business questions based on the existing information systems?

(continued)
There is no formal solution to this question. However, some of the generic
reasons or answers are listed on the next page.
Some Generic Answers to Question 5:

IT organizations are under tremendous pressure to provide better quality
decision-making information in forms that are easy to access and
manipulate. Business users are reacting to their own mission-critical needs
for better information due to rapidly changing, increasingly volatile, and
competitive markets, as well as ever-shortening product life cycles.
Enterprises must become more competitive and get closer to their customers
to survive. Some of the reasons as to why existing information systems are

unable to provide the answers to business questions are:
Much of the enterprise data is locked up in data jailhouses.
Operational systems are unable to provide a consolidated view of data.
Answering some of the business questions requires analyzing data patterns
and trends over time. This often requires large volumes of historical data.
Operational systems do not keep historical data. Therefore, such type of
analysis cannot be done in an operational system.

Practice 2-2: Exploring the Analytic Workspace Manager (AWM)
11g
1) Start the AWM tool by clicking the AWM icon on your desktop.
Click the AWM icon on your computer desktop to start the tool. The Analytic
Workspace Manager screen is displayed as follows:
2) Expand the Databases node. Expand oracle11g. The Connect to database dialog
box is displayed as follows:

11g (continued)

3) Enter dm as the username and oracle as the password. Ensure that OLAP11g is
selected in the Cube type drop-down list. Next, click OK.
4) Expand the Schemas > DM > Analytic Workspaces > SALESTRACK (attached
RW) nodes as follows:

11g (continued)

5) Explore the SALESTRACK workspace in the DM schema, and answer the following
questions:
a) What are the nodes available under the SALESTRACK workspace?
b) What are the dimensions and cubes available in the SALESTRACK workspace?
Expand the Dimensions node. You can see the CHANNEL, GEOGRAPHY,
PRODUCT, and TIME dimensions. Also, expand the Cubes node to see BUDGET
and SALES_MEASURES.

11g (continued)
The dimensions and

the cubes in the DM
schema
6) Observe the data in the dimensions and cubes, and answer the following questions:
a) What are the members of the GEOGRAPHY dimension?
Right-click the GEOGRAPHY dimension, and then select the View data
GEOGRAPHY option from the pop-up menu. Click the Expand All
icon to view all members of the GEOGRAPHY dimension. There are 252
Geography values.
What are the levels and attributes of the GEOGRAPHY dimension?

11g (continued)
Expand the node for the GEOGRAPHY dimension. Next, expand the Levels
and Attributes nodes in the dimension.

Click the GEOGRAPHY
dimension in the
Dimensions node. 1
Select View Data

GEOGRAPHY from the
pop-up menu.
2

11g (continued)
What are the Measures and Calculated Measures in the SALES_CUBE cube?
Expand the node for the SALES_CUBE cube. Expand the Measures and
Calculated Measures nodes as follows:

11g (continued)

Practice 2-3: Accessing OLAP Data Using APEX
1) Start your Web browser and navigate to the Application Express (APEX) login page:
http://localhost:8080/apex
2) The Application Express Login page is displayed. Log in using the following details
and then click Login.
a) Workspace: salestrack
b) Username: dm
c) Password: dm

Practice 2-3: Accessing OLAP Data Using APEX (continued)
3) Click the Application Builder icon. A list of the available applications is displayed.

4) Click the OLAP ILT 100 icon to start the prebuilt application. The application is
displayed as follows:


5) The Application: 100 OLAP ILT page is displayed. Click the Run Application
icon. The Login page is displayed.
6) Enter dm as the username and password, and then click Login. The report is
displayed.


7) Experiment with the report as desired. To get additional information about using an
APEX interactive report, review the following OBE:
http://www.oracle.com/technology/obe/hol08/apex31nf/apexusr31/apexusr31_otn.htm

In this practice/task, you do the following:

Answer questions regarding the data warehousing concept and terminology.
Discuss some of the data warehouse concepts and terminology.
Discuss the case study to understand the requirements of the system.

Practice 3-1: Defining Data Warehouse Concepts and
Terminology
1) Indicate whether the following statements about warehouse data are true or false.
Statement True or False?

a. Data is organized by time. True
Data exists in the data warehouse specifically for analysis

by time.

b. Data is always stored in a relational database. False
It is not imperative that the data be stored in a relational

database, although it is more common.
c. Data relates to business-specific areas. True
The data warehouse may be enterprisewide but the way the

data is organized within the database is by departmental
need, subject need, and functional need.
d. Data is sometimes integrated. False
Data must always be cleaned and integrated into the

warehouse.
e. Data is replaced according to a refresh cycle. False
Data is added and not replaced.

f. Data warehouses may contain any type of data. True
If the database server supports any type of data, the

warehouse is capable of holding any type of data.
2) What are the typical components of a data warehouse and what is their significance?

Practice 3-1: Defining Data Warehouse Concepts and
Terminology (continued)
The following are the typical components of a data warehouse:
Source systems
Metadata repository
Staging area
Presentation area
Data access tools
Also refer to pages 3-20 and 3-21.
3) An online bookstore has customers in their Sales Order System and Marketing
System. These customers do not match between systems because the marketing staff
do not always update the Marketing System with current and complete customer data.

Also, they want to develop profiles of their customers according to buying patterns
and summarize product sales to get the feedback necessary to improve marketing
programs and promotions. Which of the following are you going to design?
a) Operational data store (ODS)
b) Data warehouse
The correct answer is a) because the organization needs current and integrated
customer data.
4) Discussion: With the groups formed earlier, discuss the following:

a) Discuss how the pieces of the classic Inmons definition of a data warehouse, A
data warehouse is a subject-oriented, integrated, time-variant, nonvolatile
collection of data in support of managements decision-making process, apply to
the Roy Independent School District (RISD) case study.
b) Discuss the scope, background, and objectives of the project.
c) Discuss the various requirements for the system and identify the important
reporting systems for the RISD.
Note: You can refer to the information in the M1_RISD_PMP_V1.doc
template.
There is no formal solution. However, student groups are advised to go
through the project management plan of the RISD, each student in a group
can read a portion of the plan, and the groups can present their observations
to the class.

In this practice, you do the following:

In practice 4-1, you review the RISD scenario.
In practice 4-2, you identify the facts, measures, hierarchies, slowly changing
dimensions (SCD), and the probable attributes for the dimensions and facts for the
RISD scenario.
If you have time, go through the Oracle Warehouse Builder guided practice 4-3. In
this guided practice task, you practice some of the basic functionality of Oracle Data
Warehouse Builder. You perform the following tasks:
Log in to Oracle Warehouse Builder.

Create two dimensions: CHANNELS1_DIM and TIME1_DIM.
Create a cube that is dimensioned by the newly created dimensions.

Practice 4-1: Reviewing the RISD Scenario
Consider a simplified portion of the Roy Independent School District (RISD) scenario.
You use this information in the next practice in this lesson:
The RISD wants to build a Data Warehouse environment that will provide decision
makers throughout the school district with information to help improve student
achievement. School districts are a form of special-purpose districts, which help to
operate the local public primary, middle, and high schools. Public education is a function
of these districts serving one or more towns. Data that is currently processed
independently in multiple applications will be integrated into a single environment to
facilitate data reporting and analysis across test areas.

You are hired as a consultant by the RISD to design and develop the Data Warehouse
environment. From your initial interviews with different levels of people, you obtained
the following information:
Report data will be available online and can be downloaded into local applications
where appropriate (for example, spreadsheets and PC databases) to perform
additional analysis or for integration with local data.
User groups, such as students, parents, teachers, district officials, and school staff
(principals and school administrators), will be restricted to accessing information
associated with their responsibilities, needs, and skills.
The majority of end users will view simple reports. Dashboard reports will provide
summarized tabular or graphical information, or both, to various user groups. For
example, students will be able to view their own data. For the first phase, test results
will be kept only for the primary state mandated assessments.
Data will be kept from the year 2000. Each Year falls within a Time Period. Each
Time Period is associated with Year and Semester. There are two semesters: Semester
1 and Semester 2.
Students from grades 3 through 12 will take these assessment tests in Mathematics
and English.
Reports will show students scores (out of 100) in Mathematics and English for a
particular grade. Aggregate reports will show Time, Grade, and Location.
There are three levels of location: District, Location type (elementary, middle, high),
and Campus.

Practice 4-2: Business, Logical, Dimensional, and Physical
Modeling
1) In this practice, you identify the facts, measures, hierarchies, slowly changing
dimensions, and the probable attributes for the dimensions and facts for the RISD
scenario given.
Consider a simplified portion of the RISD scenario that you reviewed in practice 4-1:
The RISD wants to build a Data Warehouse environment that will provide decision
makers throughout the school district with information to help improve student
achievement. School districts are a form of special-purpose districts, which help to
operate the local public primary, middle, and high schools. Public education is a

function of these districts serving one or more towns. Data that is currently processed
independently in multiple applications will be integrated into a single environment
to facilitate data reporting and analysis across test areas.
You are hired as a consultant by the RISD to design and develop the Data Warehouse
environment. From your initial interviews with different levels of people, you
obtained the following information:
Report data will be available online and can be downloaded into local applications
where appropriate (for example, spreadsheets and PC databases) to perform
additional analysis or for integration with local data.
User groups, such as students, parents, teachers, district officials, and school staff
(principals and school administrators), will be restricted to accessing information
associated with their responsibilities, needs, and skills.
The majority of end users will view simple reports. Dashboard reports will
provide summarized tabular or graphical information, or both, to various user
groups. For example, students will be able to view their own data. For the first
phase, test results will be kept only for the primary state mandated assessments.
Data will be kept from the year 2000. Each Year falls within a Time Period. Each
Time Period is associated with Year and Semester. There are two semesters:
Semester 1 and Semester 2.
Students from grades 3 through 12 will take these assessment tests in
Mathematics and English.
Reports will show students scores (out of 100) in Mathematics and English for a
particular grade. Aggregate reports will show Time, Grade, and Location.
There are three levels of location: District, Location type (elementary, middle,
high), and Campus.
2) Use the preceding information about the RISD to help determine the answers to the
following questions:
a) What measures (facts) can be identified for the reports?
Student scores in Mathematics and English, and also Average scores

Modeling (continued)
b) What dimensions and facts can you identify? Fill in the appropriate names in the
following diagram:
D.Access D.Time
F.Student
Scores
D.Student D.Location
Note: The most probable dimensions and facts are indicated in the diagram. You can
think of adding more dimensions or facts.

c) Identify a hierarchy for (3) of the dimensions.
Location: District > Location type > Campus
Campus Type: Elementary > middle > high
Time: Year > Semester > Month > Day
d) Can you identify any slowly changing dimensions (SCDs)? Name them, and
suggest a method to maintain the history for the SCDs.
Among the probable dimensions depicted in question 2b of this exercise,
D.Student maintains the address of the students, and this field can change
over a period of time. So, D.Student is a slowly changing dimension (SCD).
Out of the three methods suggested for SCDs (lesson 3, page 37-38), the third
method of using additional fields (such as Date of Joining and Date of
Leaving along with Address field), and surrogate keys will suit the scenario
better. Also, it is a good idea to maintain a separate D.Student History
dimension with these attributes.
3) Based on the scenario and information given in Practice 4-1, fill in the most
appropriate attributes for the following dimensions that you have already identified.
You can add more rows if you need to add more attributes. Follow these rules:
i) List columns inside the box, with primary keys listed first.
ii) Label primary keys with _PK and (* #).
iii) Identify the format and length of columns: n for number, c for character, v
for variable character, and d for date.
iv) Label unique keys with (#).
v) Label column with NULL(o) or NOT NULL(*) constraints.
vi) Label foreign keys with _FK and (* #) if part of the PK.

D. Access F. Student Scores D. Location

Access_Id_PK ID (*#) n Id_PK (*#) n
Role (*) v15 Student_Id_FK (*#) District_Id (#) n
Access_Level Time_Id_FK (*#) n District_Name v20
Access_Id_FK (*#) n District_Descr v30
Location_Id_FK (*#) Loc_Id (*) (#) n

English_Score (*#) Loc_Name v 20
Maths_Score (*#) n Loc_Descr v30
Campus_Id (#) n
D. Time D. Student
Student_Id_PK(*#) n Campus_Name v20
Year_Id (*#) n
First_Name v20 Campus_Descr v30
Year n
Semester_Id n Middle_Name v20 D. Student History
Semester_Name v10 Last_Name v20 Student_Id_FK (*#)
Semester_Descr v10 DOB (*) d Date_Of_Joining d
Cal_Year v6 Address v30 (*) Date_Of_Leaving d
Cal_month n Zip_Code v10 (*) Address v30(*)
Cal_Day v10 Date_Of_Joining d (*) Zip_Code v10 (*)
Gender v1 (*)
4) Explore the viewlet-based demonstrations on Warehouse Builder modeling

capabilities, which can be accessed from the index.htm page available in the
following path on your system: /oracle/home/viewlets.
Explore the following viewlets available in the Dimensional Modeling section and
answer the interactive questions in the viewlets:
a) Run the dim_model1_viewlet_swf.html overview viewlet in the
/oracle/home/viewlets/dim_model1 folder. This viewlet provides you with an
overview of Oracle Data Warehouse Builder.
b) Run the dim_model2_viewlet_swf.html overview viewlet in the
/oracle/home/viewlets/dim_model2 folder. This viewlet demonstrates how to
create a dimension.

c) Run the dim_model3_viewlet_swf.html overview viewlet in the
/oracle/home/viewlets/dim_model3 folder. This viewlet demonstrates how to
create a cube.
No formal solutions

Practice 4-3: Creating the CHANNEL1_DIM and TIME1_DIM
Dimensions in Oracle Warehouse Builder
Note: If you have time, go through the following guided practice using Oracle
Warehouse Builder.
In this guided practice, you experiment with some of the basic functionality of the Oracle
Data Warehouse Builder. In this practice, you log in to Oracle Warehouse Builder and
create two dimensions: CHANNELS1_DIM and TIME1_DIM.

Creating the CHANNEL1_DIM Dimension
1) Log in to Oracle Warehouse Builder (OWB) Design Center by double-clicking the
Oracle Warehouse Builder shortcut on your desktop. The Design Center Logon
window is displayed as follows.
2) Before logging in with the username (if not already displayed) and password, you
must establish connect information. Click Show Details. The Design Center Logon
window expands as follows:

Dimensions in Oracle Warehouse Builder (continued)

3) Provide (or confirm that fields display the following information) the password, host
name, port number, and service name as provided by your instructor.
User Name: rep_owner
Password: rep_owner
Host: localhost
Port: 1521
Service Name: orcl.us.oracle.com


4) Click OK. The Design Center window is displayed. The Design Center is divided
into three panels: Project Explorer, Connection Explorer, and Global Explorer. In
the Project Explorer, you see MY_PROJECT, an empty default project created when
Warehouse Builder is installed.

1 2

3
5) Create a new dimension named CHANNEL1_DIM using the Create Dimension

Wizard, by performing the following steps in the SH_HANDSON project:
In the Project Explorer panel of the Design Center, expand the nodes in the
following order:
SH_HANDSON > Databases > Oracle > TARGET_MODULE
Right-click Dimensions, and then select New > Using Wizard from the pop-up
menu. The Create Dimension Wizard is displayed.
Click Next in the Create Dimension Welcome window. The Create
Dimension Step 1 of 8: Name and Description window is displayed.
6) Enter CHANNEL1_DIM as the name of the new dimension in the Name box and add
a suitable description for the dimension such as Channels Dimension
created by XX, where XX are your initials. Compare your results with the
following screen capture.


7) Click Next. The Create Dimension Step 2 of 8: Storage Type window is
displayed. Accept the default selection ROLAP: Relational Storage. Compare your
results with the following screen capture.


8) Click Next. The Create Dimension Step 3 of 8: Dimension Attributes window is
displayed. In the Dimension Attributes window, you find three predefined attributes:
ID, NAME, and DESCRIPTION. Observe that for the ID attribute, the identifier is set
as Surrogate and for the NAME attribute, the identifier is set as Business. Compare
your results with the following screen capture.


9) Accept the defaults for the ID attribute as the Surrogate key and the data type as
NUMBER. Make sure that for the NAME attribute, from the Identifier drop-down list,
Business is selected as the identifier. Make sure that the data type for this attribute is
set to VARCHAR2, and enter the length as 50. Similarly, for the DESCRIPTION
attribute, ensure that the data type is set to VARCHAR2, and enter the length as 50.
Compare your results with the following screen capture:


10) Click Next. The Create Dimension Step 4 of 8: Levels window is displayed. On
the Levels page, create the following three levels:
TOTAL
CLASS
CHANNEL

Note: Enter the first level TOTAL and then press the Tab key to move to the
Description field. Press the Tab key again to add the remaining two levels.

11) Click Next. The Create Dimension Step 5 of 8: Level Attributes window is
displayed. Make sure all the attributes you have defined in the previous step are
available for all levels. (Select each level and see the attributes available.)


12) Click Next. The Create Dimension Step 6 of 8: Slowly Changing Dimension
window is displayed. Accept the default option Type 1: Do not keep history. Note
that OWB offers the support for maintaining the history of slowly changing
dimensions. Compare your results with the following screen capture:

13) Click Next. The Create Dimension Step 7 of 8: Pre Create Settings window is
displayed. Observe the Pre Create Settings that you have selected.

14) Click Next. The Create Dimension Step 8 of 8: Dimension Creation Progress
window is displayed. When the dimension is created successfully, a success message
is displayed. Compare your results with the following screen capture:


15) Click Next. The Create Dimension - Summary window is displayed. Review the
summary information. Compare your results with the following screen capture:

16) Click Finish. The Design Center window is redisplayed. Note that the newly created
dimension CHANNEL1_DIM is displayed under the Dimensions node in the Project
Explorer pane. Compare your results with the following screen capture:

17) Right-click the CHANNEL1_DIM dimension, and then select Configure from the
pop-up menu. The Configuration Properties: DEFAULT_CONFIGURATION
window for the newly created dimension is displayed as follows:

18) In the Configuration Properties: DEFAULT_CONFIGURATION window:
Select Deploy Data Objects only from the Deployment Options drop-down list.
This is required if you do not have an OLAP environment. Next, click OK. The
Design Center window is redisplayed.

2
3
19) Right-click the CHANNEL1_DIM dimension, and then select Validate from the pop-
up menu as follows:

20) Close the Validation Results window. Select Close from the File menu.

Creating the TIME1_DIM Dimension
Create a new dimension named TIME1_DIM using the Create Dimension Wizard, by
performing the following steps:
1) In the Project Explorer panel of the Design Center window, expand the nodes in the
following order:
SH_HANDSON > Databases > Oracle > TARGET_MODULE

2) Right-click Dimensions, and then select New > Using Time Wizard from the pop-up
menu. The Create Time Dimension Wizard is displayed as follows:


3) Click Next in the Create Time Dimension Welcome window. The Create Time
Dimension Step 1 of 6: Name and Description window is displayed.
4) Enter TIME1_DIM as the name of the new dimension in the Name box and
optionally add a suitable description for the dimension such as Time Dimension
created by XX, where XX are your initials. Compare your results with the
following screen capture.


5) Click Next. The Create Time Dimension Step 2 of 6: Storage window is
displayed. Accept the default selection ROLAP: Relational Storage, and then click
Next. The Create Time Dimension Step 3 of 6: Data Generation window is
displayed. Compare your results with the following screen capture.



6) On the Data Generation page, you specify the range of time data that is required for
your warehouse. This information will be used to generate a mapping that will
populate the TIME dimension. Within this mapping, the dates you enter are added as
parameters, enabling you to rerun this mapping with dates at a later stage:
In the Start Year list box, select 1999.
In the Number of years list box, select 2.
Ensure that the Calendar option is selected (not Fiscal).


2
3
7) Click Next. The Create Time Dimension Step 4 of 6: Levels window is displayed
as follows:


8) On the Levels page, select the Normal Hierarchy option if not already selected, and
then select the following levels:
Calendar Year
Calendar Quarter
Calendar Month
Day

Compare your results with the following screen capture.

9) Click Next. The Create Time Dimension Step 5 of 6: Pre Create settings
window is displayed as follows:


10) On the Pre Create Settings page, observe the details, and then click Next. A progress
bar will show the progress as the wizard creates the necessary objects.


11) Upon successful completion, click Next. The Create Time Dimension Summary
page is displayed. Examine the details again, and then click Finish. Compare your
results with the following screen capture.


12) The Design Center window is redisplayed. Note that the newly created dimension
TIME1_DIM is displayed under the Dimensions node in the Project Explorer pane.


13) Right-click the TIME1_DIM dimension, and then select Configure from the pop-up
menu. The Configuration Properties: DEFAULT_CONFIGURATION window
for the newly created dimension is displayed as follows:


14) In the Configuration Properties: DEFAULT_CONFIGURATION window, select
Deploy Data Objects only from the Deployment Options drop-down list. Click OK.
The Design Center window is re-displayed.
15) Right-click the TIME1_DIM dimension, and then select Validate from the pop-up
menu to validate the Dimension as follows:


16) The Validation Results window is displayed as follow:


Note: In the Project Explorer, note that the wizard has generated four objects
necessary for a fully functional TIME dimension: the TIME1_DIM dimension object
(found under the Dimensions node of TARGET_MODULE of the SH_HANDSON
project), the TIME1_DIM_SEQ sequence that populates the surrogate ID of the
TIME dimension levels (found under the Sequences node), the TIME1_DIM table to
support the relational implementation of the TIME dimension that will physically
store the time data (found under the Tables node), and the TIME1_DIM_MAP
mapping to populate the TIME dimension (found under the Mappings node).
18) Save your changes. Click the Save All icon from the toolbar. The following
warning window is displayed as follows.


19) Click Yes. The Design Center window is redisplayed.

Practice 4-4: Creating the SALES_CUBE Cube Using the Newly
Created Dimensions and the PRODUCT_DIM and CUSTOMER_DIM
Prebuilt Dimensions
Warehouse Builder.
In this guided practice, you create a new relational SALES1_CUBE cube and dimension
it using the CHANNEL1_DIM , TIME1_DIM, and the PRODUCT_DIM and
CUSTOMER_DIM prebuilt dimensions in the SH_HANDSON project.

1) Right-click the TARGET_MODULEs Cubes node, and then select New > Using
Wizard from the pop-up list as follows:
2) The Create Cube Welcome window is displayed as follows. Click Next.

Created Dimensions and the PRODUCT_DIM and
CUSTOMER_DIM Prebuilt Dimensions (continued)

3) The Create Cube Step 1 of 4: Name and Description window is displayed. Enter
SALES1_CUBE as the name. Optionally, add a description such as SALES_CUBE
created by XX, where XX are your initials, and then click Next.


4) The Create Cube Step 2 of 4: Storage Type window is displayed. Ensure that the
ROLAP: Relational storage option is selected, and then click Next.


The Create Cube Step 3 of 4: Dimensions window is displayed. Select
CHANNEL1_DIM, TIME1_DIM, PRODUCT_DIM, and CUSTOMER_DIM from the
Available Dimensions list as follows and, and then click Next.
Note: To add a dimension, select a dimension from the Available Dimensions: list, and
then click the Add button. The dimension is now displayed in the Selected
Dimensions: list.


5) The Create Cube Step 4 of 4: Measures window is displayed. Enter two measures
named QUANTITY and AMOUNT that will default to the NUMBER data type on the
Measure Definition page. Accept the defaults for the new measures. Compare your
results with the following screen capture, and then click Next.


6) The Create Cube Summary window is displayed as follows. Review the summary
information, and then click Finish.


7) The Design Center window is redisplayed as follows.


8) In the Design Center, right-click the SALES1_CUBE cube, and then select
Configure. In the Configuration Properties DEFAULT CONFIGURATION
window, select Deploy Data Objects only from the Deployment Options drop-
down list as follows. Click OK. The Design Center window is redisplayed.


9) Right-click SALES1_CUBE, and then select Validate from the pop-up list. The
Validation Results window is displayed as follows:


11) Save your changes. Click the Save All icon from the toolbar. The following
warning window is displayed as follows.
12) Click Yes. The Design Center window is redisplayed.

In this practice, you identify the suitable indexes for the attributes and answer questions
related to indexing and parallelism. You also identify the best strategy to implement
security for the RISD scenario. Finally, you review the other concepts explained in the
lesson.

Practice 5-1: Database Sizing, Storage, Performance, and
Security Considerations
1) Review the following physical model that you completed in practice 4-2.
No formal solutions
2) You have the historical data from the year 2000 for students and a huge facts table
called Students Scores. What type of partitioning do you think would best suit this
scenario, and why?
In this scenario, range partitioning would suit the best because the data can be
partitioned based on the year.

3) Suggest the indexes suitable for the following columns:
a) D.Student.Gender
b) D.Time.Time_ID
c) D.Location.Location_type
d) D.Student.Student_ID
e) F.Student_Scores.Location_ID
The indexes suitable are given below:
a. D.Student.Gender: Bitmap index
b. D.Time.Time_ID: B-tree index
c. D.Location.Location_type: Bitmap index
d. D.Student.Student_ID: B-tree index
e. F.Student_Scores.Location_ID: B-tree index
Note: The preceding indexes are suggested based on the cardinality of these
columns. Also, note that for all primary and unique key columns, the Oracle
database generates B-tree indexes automatically.
4) For a query to use star transformation, which of the following conditions should be
met?
a) B-tree indexes should be created on foreign key columns of the fact table.
b) The STAR_TRANSFORMATION_ENABLED parameter should be set to TRUE.
c) Rule-based optimizer should be used.
d) Bitmap indexes should be created on foreign key columns of the fact table.
e) Cost-based optimizer should be used.
f) All of the above

Practice 5-1: Database Sizing, Storage, Performance, and
Security Considerations (continued)
The correct answers are b), d), and e).
5) Consider the following SQL statement:
CREATE BITMAP INDEX GENDER_IDX ON
SH.CUSTOMERS(CUST_GENDER) no logging PARALLEL 5;
The values of the PARALLEL_MIN_SERVERS and PARALLEL_MAX_SERVERS
parameters are set to 2 and 4, respectively.
Based on this information, answer the following question:
How many parallel execution servers are created for executing the given SQL
statement?
a) 2

b) 4
c) 5
d) The SQL statement will not be executed and will throw an error.
The correct answer is b) because PARALLEL_MAX_SERVERS is set to 4, though
the degree of parallelism in the statement is 5.
6) Based on the scenario presented in practice 4-1, find out the best way to implement
the data access security. What are the user roles that you can think of?
The best way to implement the data access security in this case is using a
combination of role-based security and virtual private database (VPD). Some of
the possible roles to restrict access are:
Student
Parent
Teacher
Admin
Principal or Management

In this practice, you identify the tools and techniques that will aid in the ETL process for
the RISD. You also answer questions based on the given scenario.
Note: If you have time, go through the Oracle Warehouse Builder guided practice 6-2. In
this guided practice, you create maps for the following:
CHANNEL1_DIM dimension that you created in practice 4-3

SALES1_CUBE cube that you created in practice 4-4

Practice 6-1: The ETL Process: Extracting Data
1) The RISD currently has huge data in an operational system, which has to be extracted
and placed in the data warehouse. What do you think is a good choice among the
following for extracting data into the data warehouse?
a) Full and online extraction
b) Incremental and online extraction
c) Incremental and offline extraction
d) Full and offline extraction

The correct answer is d). This is because this is the first time that the data is
being extracted, and also the RISD has data available in flat files.
2) Which of the following features of Oracle Database11g do you think will aid the ETL
process for the RISD? (Select any two options.)
a) Change Data Capture
b) Query rewrite
c) Partition pruning
d) External tables
The correct answers are a) and d). (Other options are also useful for data
warehousing, but not for the ETL process.)
3) It is decided that the RISD Data Warehouse (RISD DW) project will support Student
Information System (SIS). The RISD will supply annual flat files for each subject
area such as State Developed Alternative Assessment (SDAA II), and will also
provide periodic supplementary data files for new students. To support the scenario,
which of the following features of OWB can you take advantage of?
a) OWB offers easy-to-use wizards to define the dimensional model.
b) OWB supports both Third Normal Form (3NF) and star schema designs.
c) SQL*Loader control files for loading data from flat files.
d) OWB supports both relational and multidimensional designs.
e) All of the above

Practice 6-1: The ETL Process: Extracting Data (continued)
The correct answer is c). Other options are also features of OWB, but the feature
that is more useful for the scenario here is c).
4) Explore the perf_etl1_viewlet_swf.html viewlet in the
/oracle/home/viewlets/perf_etl1 folder. This viewlet demonstrates the ETL
capabilities of OWB. Answer the interactive questions in the viewlet.
No formal solution

Practice 6-2: Creating Maps for the CHANNEL1_DIM Dimension
and the SALES1_CUBE Cube
Warehouse Builder.
In this practice, you create the maps for the CHANNEL1_DIM dimension and the
SALES1_CUBE cube that you created in practices 4-3 and 4-4 respectively.
Creating the CHANNEL1_DIM Dimension Map

1) Log in to Oracle Warehouse Builder as rep_owner if you are not already logged in.
2) Expand the Mappings node of TARGET_MODULE in the SH_HANDSON project in
the Project Explorer pane of the Design Center window using the following
navigation path: SH_HANDSON > Databases > Oracle > TARGET_MODULE >
Mappings. Note that you already have a TIME1_DIM_MAP created by the Time
Dimension Wizard. In addition, you will note the other precreated maps for the other
dimensions and cube as follows:
3) Right-click the Mappings node, and then select New from the pop-up menu as
follows:

and the SALES1_CUBE Cube (continued)

4) The Create Mapping dialog box is displayed. Enter CHANNEL1_MAP as the
mapping name. Optionally, you can enter the description. Next, click OK.
5) The Mapping Editor: CHANNEL1_MAP window is displayed as follows:


6) Select the Dimension Operator from the Palette and drag it onto the canvas, the
Mapping pane. Alternatively, you can expand the TARGET_MODULE and select
the dimension from there too.


7) The Add Dimension Operator window is displayed. Select CHANNEL1_DIM from
TARGET_MODULE, and then click OK. CHANNEL1_DIM is dropped onto the
canvas.



8) Next, drag the Table Operator from the Palette pane to the Mapping panel
(canvas). In the Add Table Operator window, select the CHANNELS object from
SOURCE_MODULE, and then click OK.



9) In the Mapping Editor window, click the Auto-Layout icon from the toolbar to
bring the mapping to its default size. Maximize both objects by clicking the
Maximize icon. By using the drag-and-drop feature, move the CHANNELS table to
the left side of the canvas and the CHANNEL1_DIM dimension to the right.

10) In the Mapping Editor window, click the Auto-Layout icon from the toolbar to
bring the mapping to its default size. Maximize both objects by clicking the
Maximize icon. By using the drag-and-drop feature, move the CHANNELS table to
the left side of the canvas and the CHANNEL1_DIM dimension to the right. After you
finish, close the Mapping Editor window. Compare your results with the following
screen capture:


11) Drag the connection lines from the table to the dimension connect objects columns
Map the CHANNELS source table to the CHANNEL1_DIM target as follows:

INOUTGRP1.CHANNEL_ID to CHANNEL.NAME
INOUTGRP1.CHANNEL_DESC to CHANNEL.DESCRIPTION
INOUTGRP1.CHANNEL_CLASS to CLASS.DESCRIPTION
INOUTGRP1.CHANNEL_CLASS_ID to CLASS.NAME
NOUTGRP1.CHANNEL_CLASS_ID to CHANNEL.CLASS_NAME
NOUTGRP1.CHANNEL_TOTAL to TOTAL.DESCRIPTION
INOUTGRP1.CHANNEL_TOTAL_ID to TOTAL.NAME
INOUTGRP1.CHANNEL_TOTAL_ID to CLASS.TOTAL_NAME
Hint: You can double-click CHANNEL_MAP from the Mappings node to open the

CHANNEL_MAP, which is precreated for you, and use it as reference for creating
CHANNEL1_MAP. After completion, CHANNEL1_MAP looks like this:

12) Next, you need to validate and generate the mapping in the Design Center window.
In the Design Center window, right-click CHANNEL1_MAP in the Mappings node,
and then select Validate from the pop-up menu to validate the mapping that you have
created. The Validation Results dialog box is displayed. Ignore the warnings (if any)
when validating the mapping. After you finish, close the Validation Results dialog
box.


13) Next, right-click CHANNEL1_MAP and select Generate from the pop-up menu to
generate this mapping. The Generation Results dialog box is displayed. Ignore the
warnings (if any) when generating the mapping. After you finish, close the
Generation Results dialog box.


Creating the Map for SALES1_CUBE
In this practice, you create a map between the SALES source table and the
SALES1_CUBE cube that you have created in practice 4-4.
1) In the Project Explorer pane, navigate to the Mappings node: Expand Databases >
Oracle > TARGET_MODULE > Mappings.
2) Right-click the Mappings node, and then select New from the pop-up menu. The
Create Mapping dialog box is displayed. Enter SALES1_MAP as the mapping name,
and then click OK. The Mapping Editor: SALES1_MAP window is displayed as
follows:

3) From the Palette panel, select the Cube operator and drag it onto the Mapping panel.
The Add Cube Operator dialog box is displayed. Select SALES1_CUBE and then
click OK. SALES1_CUBE is displayed in the Mappings panel. Compare your results
with the following screen captures.


4) Next, drag the Table Operator from the Palette panel and drop it onto the
Mappings panel. In the Add Table Operator dialog box, select SALES from the
SOURCE_MODULE, and then click OK. Arrange the layout and resize the tables as
desired. Compare your results with the following screen captures.



5) In the Mapping Editor: SALES1_MAP window, drag the Expression
Operator from the Palette panel onto the Mappings panel. In this example, the
Expression Operator is used to transform the TIME_ID attribute in the SALES
table from a date data type to a number data type. The target attribute in the cube is a
number data type; therefore, this data type conversion is required to match the data
types.


6) Drag a connection line from SALES.INOUTGRP1.TIME_ID to
EXPRESSION.INGRP1.TIME_ID as follows:
7) Right-click the expression operator EXPRESSION header, and then select Open
Details from the pop-up menu. In the Expression Editor window, click the Output
Attributes tab and then click Add.




8) Change the OUTPUT1 attribute to TIME_ID. Ensure that the data type is NUMBER.
Click OK.


9) In the Mapping Editor: SALES1_MAP window, click the Expression Operator
EXPRESSION header, and then click OUTGRP1.TIME_ID.
Click the EXPRESSION

header to select the
EXPRESSION object.
1
Click
OUTGRP1.TIME_ID.
2
10) In the Attribute Properties panel displayed on the left side of the Mapping Editor:
SALES1_MAP window, click the field next to the Expression property. Next, click
the ellipsis button to the right of the field that you clicked. The Expression Builder:
Expression for TIME_ID window is displayed.

Click the field next to the

EXPRESSION property.

11) In the Expression for TIME_ID text field, enter the following statement, and then
click OK:
TO_NUMBER(TO_CHAR(INGRP1.TIME_ID , 'YYYYMMDD'),
'99999999')

12) Connect the source table to the target cube as follows:
INOUTGRP1.PROD_ID to SALES1_CUBE.PRODUCT_DIM_NAME
INOUTGRP1.CHANNEL_ID to SALES1_CUBE.CHANNEL_DIM_NAME
INOUTGRP1.CUST_ID to SALES1_CUBE.CUSTOMER_DIM_NAME
INOUTGRP1.QUANTITY_SOLD to SALES1_CUBE.QUANTITY
INOUTGRP1.AMOUNT_SOLD to SALES1_CUBE.AMOUNT

Also drag a line from EXPRESSION.OUTGRP1.TIME_ID to
SALES1_CUBE.TIME1_DIM_DAY_CODE.
Hint: Double-click SALES_MAP from the Mappings node to open SALES_MAP,
which is precreated for you, and use it as reference for creating SALES1_MAP.

13) Validate and generate the map that you have created from the pop-up menu options of
SALES1_MAP in the Design Center window.
To validate the map, use SH_HANDSON > TARGET_MODULE > Mappings
> right-click SALES1_MAP > Validate. The Validation Results dialog box is
displayed. Ignore the warnings, and then close the dialog box.
To validate the map, use SH_HANDSON > TARGET_MODULE > Mappings
> right-click SALES1_MAP > Generate. The Generation Results dialog box is
displayed. Ignore the warnings, and then close the dialog box.
Note
Practices on deploying objects in OWB are not included here as they are beyond the
scope of the fundamentals course. However, the COMPLETED_BI_DEMO project is
complete in all respects, and you can explore the project and view the data. Similarly, the
SH_HANDSON project also has the cubes and dimensions already deployed so that you
can view the data.
Also, when you want to view data, OWB may display a warning saying Connection
Failed to location <Loc> and will prompt you to edit the details for the database

location. Click Yes, enter the appropriate password, and test the connection. This is an
additional security mechanism introduced in Oracle Warehouse Builder10g, Release 2.

In this practice, you identify the suitable staging model for the RISD data warehouse.
You also identify the problems, and the best-suited transformation techniques for the
RISD data based on the given scenario. Answer the questions based on the given
scenario:

Practice 7-1: The ETL Process: Transforming Data
1) By now, you know that the RISD has huge data in its operational system. It is also
agreed that the RISD will be responsible for data cleansing before data is extracted
from the operational data store (ODS) to load the data warehouse. Which of the
staging models do you think would be best suited for this scenario?
a) Remote staging model
b) Model without any staging area
c) Onsite staging model

d) None of the above
The correct answer is c) because the RISD is responsible for data cleansing of
the ODS, and remote staging model is not suitable for this scenario.
2) Assume that the following are some of the problems encountered with the extracted
operational data. Identify the type of problems and recommend solutions:
a) One of the relational tables, Locations, has a key named Location_code.
The following is a sample Location_code. Identify the type of problem and
recommend a solution.
Student_code: L011D01C02, where:
L011: Location Code
D01: District Code
C02: Campus Code
This is the multipart keys problem. Choose a tool that identifies the parts of
the Student_code key, or develop a program that can extract the parts of
this field.
b) There are two tables: Student and Student_History. Both these tables
have a Student_Id. You found that some of the past records for the existing
students are missing from the Student_History table.
This is the referential integrity problem. Some records are missing from the
Student_history table. This can be solved by adding the past records for
the existing students, and establishing a master-detail relationship between
Student and Student_history tables using referential integrity
constraint.
c) Each of the assessment systems such as TEKS, SDAA, RPTE, and so on maintain
Student tables for the students enrolled for that assessment, such as
Student_TEKS, Student_SDAA, Student_RPTE, and so on. Each of these
tables has a date of birth (DOB) column, and DOB values are not entered for
some students.

Practice 7-1: The ETL Process: Transforming Data (continued)
This is the missing values problem. Identify whether the DOB column can
contain null values. If nulls are not allowed in this field, use an integrity
constraint to make the DOB columns not null columns.
3) Consider this scenario: You have the Student_TEKS table that has information
about the students enrolled for the TEKS assessment. You received a delta table,
Student_Source, from the RISD. This delta table has the changed addresses for
some students. Also, some students who enrolled in that semester for this assessment
must be reflected in the Student_TEKS table.
Which of the following transformation mechanisms is best suited for this and why?
a) PL/SQL and CTAS

b) Multitable conditional FIRST INSERT
c) MERGE statement
d) Multitable unconditional INSERT
The correct answer is c) because the MERGE statement performs the UPDATE for
changed records and INSERT of new student records.
4) You want to insert the data into the Student_TEKS, Student_SDAA, and
Student_RPTE tables from a Student table that maintains the data of all the
students. You want to insert into the tables based on which assessment they have
enrolled for. Select the technique that best suits this process:
a) MERGE statement
b) Multitable unconditional INSERT
c) Multitable conditional FIRST INSERT
d) Multitable conditional ALL INSERT
The correct answer is d).
5) If you have time, run the following viewlets and answer the interactive questions:
Run the perf_etl2_viewlet_swf.html viewlet in the
/oracle/home/viewlets/perf_etl2 folder. This demonstration illustrates mapping a
cube.
Run the data_quality_viewlet_swf.htm viewlet in the
/oracle/home/viewlets/data_quality folder. This demonstration illustrates how to
use the Name and Address operators to improve data quality by using OWB.
No formal solutions


Identify the fastest way to move the metadata between the staging area and
warehouse for the RISD DW.
Identify the issues and suitable loading techniques based on the RISD scenario.
Explore the viewlet-based demonstrations on the ETL features of OWB.

Practice 8-1: The ETL Process: Loading Data
Answer the questions based on the given scenario:
1) Consider this scenario: For the staging, the RISD maintains its data mainly in two
tablespaces: STBS_DATA and STBS_INDX. The data warehouse is designed to have
two tablespaces: DWD and DWI (one for data and the other for indexes). What do you
think is the fastest way to move data and metadata from the staging to the data
warehouse? Also, list the limitations that you may have to consider.
In this case, the fastest way to move the data is to use transportable tablespaces,
and the limitations that have to be considered in case you have different versions
of Oracle databases are operating systems, data block size, and character set.

2) According to the contract, it is agreed that the RISD will provide annual flat files for
each subject area, such as State Developed Alternative Assessment (SDAA II), and
will also provide periodic supplementary data files for new students.
However, the year 2005 has a huge number of enrollments of students for all subjects
areas, and therefore, the data file size is huge. You want to load this data into target
tables by using SQL* Loader. Which of the following technologies do you think will
complement this process?
a) OCI and direct path APIs
b) Direct path loading
c) External table load
d) Conventional path loading
The correct answer is c) because an external table load creates an external table
(which acts as a view) for data in a data file. (Refer to pages 8-23 and 8-24 in this
lesson.
3) If you have time, run the following viewlet demonstrations, which demonstrate the
deploying (loading) capabilities of OWB, and answer the interactive questions:
Run the deploy1_viewlet_swf.html viewlet in the
/oracle/home/viewlets/deploy1 folder.
Run the deploy_processflow_viewlet_swf.html in the
/oracle/home/viewlets/deploy_processflow folder.
No formal solutions


Identify the possible refresh strategy for the RISD data warehouse.
Identify the strategy for archiving and purging data for the RISD data warehouse.
Answer a series of questions based on the RISD scenario.

Practice 9-1: Refreshing the Warehouse Data
1) Select the factors that you would consider while planning the refresh strategy for the
RISD data warehouse:
a) The volume of historical data (The RISD has large volumes of historical data.)
b) The type of mechanism used to capture changes in the data
c) The frequency of refresh
d) The network traffic (Traffic is low during the nights after 9 PM.)
The correct answers are b), c), and d).

2) Which of the following statements regarding the load window are true?
a) Planning the load window and keeping it to a minimum is an important
factor while implementing a data warehouse.
b) User access requirements play an important role in planning the load
window.
c) While scheduling the load window, both hot and cold backups should be
planned for the warehouse.
d) Load window is dependant on historical data and not the change data.
The correct answers are a), b), and c).
3) The RISD has large volumes of change data and some of the relational tables such as
Location do not have any date field. Choose the techniques that would be useful for
the Change Data Capture of the RISD data warehouse with a low maintenance.
a) Using the database log to analyze before and after images of the change
data
b) Using a combination of wholesale data replacement and time-stamping
c) Comparing database instances by using database triggers to maintain delta file
d) Using the Change Data Capture mechanism of Oracle Database 11g

Practice 9-1: Refreshing the Warehouse Data (continued)
The correct answers are a) and d) because the other two methods are not
suitable for this scenario. Time-stamping cannot be used because some of the
tables do not have any date field, and triggers increase the maintenance
overhead.
4) Historical data in the data warehouse is very important for business analysis. It is not,
however, practical to keep detailed data in a large data warehouse forever.
Eventually, old data needs to be archived and then removed from the data warehouse
based on the business needs of the end users. So it is decided that the RISD data
warehouse will contain historical data for up to three years depending on the
component. (Refer to the Backup and Recovery and Purge, Archival, and Restore
sections of the M6_RISD_DW_architecture_v3.0.doc template of the RISD

case study.) Data older than this will be purged from the database. Suggest the
techniques that you think would be useful for archiving and purging data.
There is no formal solution for this question. You can go through the suggested
case study document, and discuss the possible archive and purge strategies.
5) Sequence the following ETL tasks in the order of execution:
a) Index the data.
b) Summarize the data.
c) Extract data from the source systems.
d) Detect the changes made to the source data for refreshing the warehouse.
e) Transform and clean up the data.
f) Restructure keys.
g) Refresh the warehouse with updated data.
h) Load data into the warehouse.
The correct sequence is:
c. Extract data from the source systems.
e. Transform and clean up the data.
a. Index the data.
b. Summarize the data.
h. Load data into the warehouse.
d. Detect the changes made to the source data required for the warehouse.
f. Restructure keys.
g. Refresh the warehouse with updated data.
6) The following are a set of Oracle Database features useful for the entire ETL process.
Group them into Extraction, Transformation, Loading, and Refresh features
respectively.
a) OCI and direct path APIs
b) MERGE
c) Oracle Change Data Capture (CDC)

Practice 9-1: Refreshing the Warehouse Data (continued)
d) External tables
e) SQL*Loader
f) Multitable INSERT
g) DBMS_MVIEW package
h) CTAS
i) Export/Import, and Data Pump Export/Import
j) Table functions
k) Summary advisor

The grouping for these features is:
Extraction: Correct option is: c)
Oracle Change Data Capture (incremental extraction)
Transformation: Correct options are b), f), h), and j)
MERGE, Multitable INSERT, CTAS, table functions
Loading and Refresh: Correct options are a), c), d), e), g), i), and k).
SQL*Loader, OCI and Direct path APIs, Export/Import and Data Pump
Export/Import, DBMS_MVIEW package, External tables, Oracle Change Data
Capture (can be considered as part of the Refresh cycle), Summary Advisor.


Identify the importance of summary management for the RISD data warehouse.
Identify the refresh strategy for the RISD materialized views.

Practice 10-1: Materialized Views
Fill in the blanks:

1) You can improve query response time by using partitioning, ____________, and
___________.
You can improve query response time by using partitioning, indexing, and
creating summaries.
2) Oracle data warehouses use _______ to store summarized data.
Oracle data warehouses use materialized views to store summarized data.

Answer the following questions:
3) Observe the following code for creating a materialized view and answer the following
questions:
Create materialized view monthly_sales
(SELECT t.CALENDAR_MONTH_DESC "Month",
p.PROD_CATEGORY "Product Category",
SUM(s.AMOUNT_SOLD) "Sales Revenue" FROM Times t,
PRODUCTS p, Sales s
WHERE ( p.PROD_ID = s.PROD_ID )
AND ( t.TIME_ID = s.TIME_ID )
GROUP BY t.CALENDAR_MONTH_DESC, p.PROD_CATEGORY);
a) What do you think is missing in the code?
(Hint: Compare with the materialized view code example given in the lesson.)
Some of the important things missing in the code are:
Query rewrite is not enabled for the materialized view.
Build and refresh modes are not specified.
(Though not very crucial) Indexing options are not specified.
b) What is the summary data contained by this materialized view? Convert the code
given into a statement to check your understanding.
This materialized view consists of monthly sales revenues by product category.
The statement can be Create a materialized view monthly_sales which consists
of monthly sales revenues by product categories. (This question is aimed at
checking your understanding on materialized view creation.)
According to the agreement, it is decided that the summary data for the RISD data
warehouse will be maintained in the materialized views (leveraging Oracle Database 11g
features). It is decided that materialized views will be built on the facts tables of each
assessment system. (Refer to the M9_RISD DW Operations Guide_V1.2.doc
template of the RISD case study.) These are used for the students performance summary
reports, and students will be able to see data pertaining to them (a combination of Virtual
Private Database [VPD] and role-based security). Summaries for storing student data are
maintained at three different levels: Current Month (lowest level), Quarter (or
intermediate level), and Year (highest level). For example, for the SAT assessment, the
summaries will be FS_SAT_STUDENT_RESULTS, FS_SAT_RESULTS, and

Practice 10-1: Materialized Views (continued)
FS_SAT_SUMMARY for the three levels of summarization. What kind of refresh
mechanism would you recommend for the materialized views (MVs) of the RISD
warehouse (which will also ensure faster refresh)?
c) COMPLETE, ON DEMAND refresh for year-level MVs, FAST, ON DEMAND
refresh for quarterly MVs, and FORCE, ON COMMIT refresh for lowest (monthly)
MVs
d) FORCE, ON DEMAND refresh for year-level MVs, FORCE, ON DEMAND
refresh for quarterly MVs, and FORCE ON COMMIT refresh for lowest
(monthly) MVs
e) COMPLETE, ON COMMIT refresh for year-level MVs, COMPLETE, ON DEMAND

refresh for quarterly MVs, and FORCE, ON COMMIT refresh for lowest (monthly)
MVs
f) FORCE, ON COMMIT refresh for year-level MVs, COMPLETE, ON COMMIT
refresh for quarterly views, and FAST, ON COMMIT refresh for lowest (monthly)
MVs
The correct answer is b) because the year-level and quarterly views should be
refreshed ON DEMAND, whereas the lowest-level monthly views should be
refreshed ON COMMIT (for each transaction). Similarly, the FORCE option will
best suit the requirement of faster refresh (and mandatory refresh) because this
option first tries to update views on an incremental basis; otherwise, a complete
refresh is performed.
4) Which of the following statements are true with reference to the query rewrite
mechanism:
a) The query rewrite activity is transparent to applications, and its use is
similar to the use of an index.
b) End users need the CREATE ANY MATERIALIZED VIEW privilege so that
their query is redirected to the corresponding materialized view.
c) Query rewrite will not happen if only a part (subset) of the results stored in the
materialized view is requested in the query.
d) A materialized view can be enabled or disabled.
The correct answers are a) and d).
5) State whether the following statements about dimensions are True or False.
a) Creating more hierarchies in a dimension is an overhead on the database and
slows down the query response time.
False. Dimensions enable query rewrites and improve query performance.
b) Dimensions are database structures that are created using the CREATE
DIMENSION SQL statement.

Practice 10-1: Materialized Views (continued)
True
c) Dimensions enable additional rewrite possibilities without the use of constraints.
True
d) The CREATE DIMENSION system privilege enables you to create dimensions in
any schema.
False. For creating dimensions in any schema, you require the CREATE ANY
DIMENSION system privilege.

In this practice, you answer questions about some of the things you learned in this lesson.

Practice 11-1: Leaving a Metadata Trail
1) Which of the following statements about the metadata are incorrect? (Select any two
options.)
a) Metadata is descriptive data about the warehouse data and ETL processes.
b) Metadata is useful only for the DBAs and data warehouse administrators.
c) Metadata does not contain information about algorithms used for creating
derived data.
d) Any successful implementation of a warehouse can be accredited to the good

metadata management by administrators.
The correct answers are b) and c).
2) The following are various types of information that metadata contains. Classify them
into extraction, transformation, and loading metadata.
a) Gateway information
b) Temporary storage details
c) Granularity conversions
d) Source data ownership details
e) Routines used for summarization
f) Number of times of transporting data
g) Methods used for data transfer
The types of metadata given here can be broadly classified as follows:
Extraction metadata: a), b), and d)
Transformation metadata: c) and e)
Loading metadata: f) and g)
3) Identify the considerations for framing a good strategy of metadata management.
(Select any two options.)
a) Restructure keys.
b) Identify the target users.
c) Hire a data quality manager.
d) Define the goals and intended use.
The correct answers are b) and d).
4) Which of the following statements about OWB are true?
a) OWB is compatible with the OMG-CWM standard.
b) OWB provides Meta Integration Model Bridges for importing metadata
from many Oracle and non-Oracle tools.
c) OWB does not support multiple-user access.

Practice 11-1: Leaving a Metadata Trail (continued)
d) OWB can be integrated with Oracles CRM applications.
The correct answers are a), b), and d).
5) If you have time, run the following viewlets that demonstrate the metadata
management capabilities of Warehouse Builder and answer the interactive questions:
a) Run the manage_metadata1_viewlet_swf.html viewlet in the
/oracle/home/viewlets/manage_metadata1 folder.
b) Run the manage_metadata2_viewlet_swf.html viewlet in the
c) Run the manage_metadata3viewlet_swf.html viewlet in the

No formal solutions


D56261GC10 Appendix A DW

Uploaded by

Copyright:

Available Formats

D56261GC10 Appendix A DW

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

D56261GC10 Appendix A DW

Uploaded by

Copyright:

Available Formats

Appendix A:

Practices and Solutions

Oracle University and Egabi Solutions. use only

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 2

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 3

Oracle University and Egabi Solutions. use only

3) Bookmark the page for future access.

Oracle Database 11g: Data Warehousing Fundamentals A - 4

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 5

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 6

The Oracle Warehouse Builder page is displayed as follows:

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 7

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 8

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 9

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 10

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 11

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 12

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 13

Note: Make sure the cursor is on any of the code lines.

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 14

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 15

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 16

a. Analytic Workspace Manager 1. Integrated online analytical

c. Oracle Warehouse Builder 3. A tool that is specifically designed to

Oracle University and Egabi Solutions. use only

The correct matches are as follows:

Oracle Database 11g: Data Warehousing Fundamentals A - 17

Some Generic Answers to Question 5:

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 18

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 19

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 20

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 21

The dimensions and

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 22

Oracle University and Egabi Solutions. use only

Select View Data

Oracle Database 11g: Data Warehousing Fundamentals A - 23

Oracle University and Egabi Solutions. use only

Oracle Database 11g: Data Warehousing Fundamentals A - 24