Chapter 4. Creating the Target Structure in OWB

Now it's time to actually start creating objects in the Warehouse Builder for our target structure. In the previous chapter, we decided what our cube and dimensions were going to be in our logical design and now we are at the point where we can implement that design in OWB. We'll create the objects using the wizards that the Warehouse Builder provides for us to simplify the task of building cubes and dimensions. We'll look at the Data Object Editor in a little more detail than we saw in Chapter 2. Let's begin with creating the dimensions.

Creating dimensions in OWB

The Warehouse Builder provides a couple of ways to create a dimension. One way is to use the wizards that it provides, which will automatically create a dimension for us. The other way is to manually create it. We have identified three dimensions that we are going to need—a Date dimension, a Product dimension, and a Store dimension. The Date dimension, as we've seen, is our time/date dimension for providing a time series for our data. That kind of dimension is common to most data warehouses and the information it contains is very similar from warehouse to warehouse. So, recognizing this commonality, the Warehouse Builder provides us a special wizard to use just for time dimensions. Let's begin with that one.

Note

Let's talk a bit about "creating". Throughout this chapter we'll discuss creating objects, but what we're really creating is the metadata that describes the objects. Nothing will be actually created in the database yet. We won't actually do that until Chapter...

Creating a cube in OWB

Now that we have our dimensions defined, we have one last step to cover and our design for our data warehouse will be complete. We need to define our cube, which is where our measures will be stored—the facts that users will want to query. We discussed the design of our cube and agreed that we would store two measures, namely the sales amount and the number of items sold. We have already designed our three dimensions, and their links and measures will go together to make up the information stored in our cube.

There is a wizard available to us for creating a cube that we will make use of to ease our task. So let's start designing the cube with the wizard.

Creating a cube with the wizard

We will start the wizard in a similar manner to how we started up the Dimension wizard. Right-click on the Cubes node under the ACME_DWH module in Project Explorer, select New, and then Using Wizard... to launch the cube-creation wizard. The first screen will be the welcome screen, which...

Using the Data Object Editor

We've mentioned the Data Object Editor previously. We used it in Chapter 2 to create our source metadata definitions for the ACME_POS transactional database, so let's take this opportunity to look a little closer at it. The Data Object Editor is the manual editor interface that the Warehouse Builder provides for us to create and edit objects. We did not have to use it to create a dimension, but more advanced implementations would definitely need to make use of it; for instance, to edit the cube to change the aggregation method that we just discussed. We'll take a brief look at it here before moving on to get an idea of some of the features it provides. We can get to the Data Object Editor from the Project Explorer by double-clicking on an object, or by highlighting an object (by selecting it with a single click), and then selecting Edit | Open Editor from the menu. Let's open the DATE_DIM dimension in the Data Object Editor and examine it as shown here:

All of...

Summary

In this chapter we dove right in to creating our three dimensions and a cube using the Warehouse Builder Design Center. We used the Wizards available to help us out, as well as investigated the flexibility to manually create, view, and edit objects using the Data Object Editor. In a relatively short amount of time, we were able to design a data warehouse structure that could be used as is, or expanded to support more detailed information.

Now that we have our sources defined and our targets designed, it's time to start thinking about loading that target. Next, we'll look at some Extract, Transform, and Load (ETL) basics to lay the groundwork for designing the ETL we'll use to actually load data into our data warehouse.