Chapter 5. Warehousing for Analysis and Reporting

In this chapter, you'll look at multidimensional data sources, often called warehouses, and their role in Business Intelligence. You will learn to build a data warehouse environment, thus structuring data for analysis.

As you will see in this chapter, data warehousing is a huge topic. Being an important Business Intelligence topic, warehousing deserves a book in its own right. A good example of such a book is the Packt's Oracle Warehouse Builder 11g: Getting Started. For details, you can visit the book's page on the Packt website at the following link: https://www.packtpub.com/getting-started-with-oracle-warehouse-builder-11g/book.

Although this chapter does not pretend to give a complete coverage of the warehousing topic, it provides a quick-paced guide covering how to build a data warehouse with Oracle Warehouse Builder. Following the instructions given in the chapter, you will learn how to perform the following:

• Obtaining and installing...

Data organization in multidimensional data sources

As you might recall from the discussion in the Aggregating Dimensional Data section in Chapter 1, Getting Business Information from Data, a multidimensional data model is often used to perform complex analysis of historical data. For effective analysis, data should be organized along dimensions that can be then used for building cubes.

Dimensions included in a cube define its dimensionality, or in other words, its edges. For example, a cube can be organized along the Time, Store, and Product dimensions.

A dimension in turn is defined by a set of levels, each of which represents the level of data aggregation. For example, a store dimension may aggregate data at the following levels: Region, Country, State (Province), and Store.

Aside from links to dimensions, as you'll learn in this chapter, cubes contain measures representing usually numerical data that can be aggregated. Cost, quantity, and profit are good examples of measures.

Getting started with Oracle Warehouse Builder

Oracle Warehouse Builder offers a set of graphical user interfaces allowing you to implement a data store, either relational or dimensional, integrating and consolidating data from a variety of data sources. During building such a data store, also called a warehouse, you create a set of metadata objects within a workspace hosted on an Oracle database.

Oracle Warehouse Builder provides broad data integration facilities, making it a breeze to create Business Intelligence metadata for a wide range of data sources. You might want to use this powerful tool if, for example, you need to derive data from different database systems such as DB2, Informix, SQL Server, and Sybase, as well as flat files, XML, Web, and other disparate sources.

Once the data is derived, it will be automatically transformed into the data structures you have defined. It's interesting to note that you can use the Oracle Warehouse Builder's import facilities to make those data...

Building dimensional data stores with Oracle Warehouse Builder

In the following sections, you'll learn how to build a sample warehouse with Oracle Warehouse Builder. In particular, you will learn how to perform the following:

• Working with Design Center, the primary tool of Warehouse Builder

• Creating a project for a warehouse

• Defining source and target modules

• Creating dimensions and cubes

• Populating dimensions and cubes with data

Launching Design Center

On Windows, to launch Design Center, you can select Start | Programs | OWB_ORACLE_HOME | Warehouse Builder | Design Center from the Windows Start menu. On a Linux platform, run the following script OWB_ORACLE_HOME/owb/bin/unix/owbclient.sh.

As a result, you should see the Design Center Logon dialog prompting for a workspace User Name and Password as well as the underlying database connection details. The dialog is shown in the following screenshot:

Looking at the previous screenshot, you may notice that owbuser is used as the User Name here...

Summary

Of course, after reading this chapter, you may still have a lot of questions about warehousing. As you no doubt have realized, the purpose here was to give you a practical look at warehousing, without looking into details or concepts. Therefore, the chapter provided you just with quick-paced instructions on how you might build a warehouse with Oracle Warehouse Builder.