Oracle Warehouse Builder 11g R2: Getting Started 2011

Extract, Transform, and Load data to build a dynamic, operational data warehouse with Oracle Warehouse Builder 11g R2 with this book and eBook

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 the Project Navigator, select New Cube to launch the cube-creation wizard. The first screen will be the welcome screen, which will summarize the steps it will lead us through as shown in the following image of the main part of the welcome dialog box:

The following are the steps in the creation process:

1. We proceed right to the first step where we give our cube a name. As we will be primarily storing sales data, let’s call our cube SALES and proceed to the next step.
2. In this step, we will select the storage type just as we do for the dimensions. We will select ROLAP: Relational Storage to match our dimension storage option, and then move to the next step.
3. In this step, we will choose the dimensions to include with our cube. We have defined three, and want them all included. So, we can click on the double arrow in the center to move all the dimensions and select them. If we had more dimensions defined than we were going to include with this cube, we would click on each, and click on the single right arrow (to move each of them over); or we could select multiple dimensions at one time by holding down the Ctrl key as we clicked on each dimension. Then click the single right arrow to move those selected dimensions. This step looks like the following after we’ve made our selections:

   
4. Moving on to the last step, we will enter the measures we would like the cube to contain. When we enter QUANTITY for the first measure with precision and scale set to zeros and SALES_AMOUNT with precision 10 and scale 2 for the second one, we end up with a screen that should look similar to this with the dialog box expanded to show all the columns:

   

   Clicking on Next in step 4 will bring us to the final screen where a summary of the actions it will take are listed. Selecting Finish on this screen will close the dialog box and place the cube in the Project Navigator.

The final screen looks like the following when scrolled all the way to the bottom:

This dialog box works in a slightly different way than the dimension wizard. This final screen is the second-to-last screen when creating a dimension. The dimension wizard will present us with the progress screen as the final step. For cubes, the process is not quite as involved. That’s because at this point, the cube is basically done with nothing left to do afterwards. So we may think we missed a step, but not to worry. Clicking on Next on this screen will exit the dialog box, and the cube will be created and will be accessible in the Project Navigator window.

Just as with the dimension wizard, we get to see what the cube wizard is going to create for us in the Warehouse Builder. We gave it a name, selected the dimensions to include, and specified the measures. The rest of the information was included by the wizard on its own. The wizard shows us that it will be creating a table named SALES for us that will contain the referenced columns, which it figured out from the dimension and measures information we provided. At this point, nothing has actually been created in the database apart from the definitions of the objects in the Warehouse Builder workspace. We can verify that if we look under the Tables entry under our ACME_DWH database node. We’ll see a table named SALES along with tables named PRODUCT, STORE, and DATE_DIM. These are the tables corresponding to our three dimensions and the cube.

You may have a slightly different table name. The wizard will not create a table with the same name as one already created, so it will append a unique number to the end to keep the table names from conflicting. This could happen if you’ve previously created a dimension with the same name, and then removed it and recreated it. It may not remove the associated table when you delete a cube or dimension object. The tables will appear in the Project Navigator under the Tables node. Expand that and you’ll see the list of tables. Right-click a table and select Delete. The Warehouse Builder will ask if you really want to delete it, and will provide a checkbox to put the object in the recycle bin. Leave it checked just to be safe and click on OK, and the table will be removed.

The foreign keys we can see in the previous image are the pointers to the dimension tables. They will make the connection between our cube and our dimensions when they are deployed to the database.

When we view the region amounts, they will automatically be summed up from the amounts of the various stores in the region without us having to do anything extra. This is a nice feature the multidimensional implementation gives us, but aggregations are not created for the pure relational storage option. As we can generate either a relational or a multidimensional implementation, this had to be specified anyway and so it defaulted to sum. If we install the OLAP option or use a separate OLAP database in the future, we can change that aggregation method. But for now, we do not need it. It is possible to use aggregations with a pure relational implementation by creating separate summing tables, and there are OLAP data mining applications that can make use of them for more advanced implementations.

We click on the Finish button on this final screen and our sales cube is created. We’ll save our work with the Ctrl+S key combination or from the design main menu. Our cube and dimensions are now complete. Let’s take a look next at data object editors where we can view and edit our objects.

Using the data object editors

The object editors are the manual editor interfaces that the Warehouse Builder provides for us to create and edit objects. We did not have to use one 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 can get to a data object editor from the Project Navigator by double-clicking on an object, or by highlighting an object (by selecting it with a single click), and then right clicking and selecting Open from the menu. Editors in this latest release are now integrated into the main Design Center interface instead of popping open in a separate window. When editing any object now, a window appears in the Design Center containing the details to edit for the object. Let’s open the DATE_DIM dimension and examine the overall interface as shown here:

(Move the mouse over the image to enlarge.)

Your screen may look differently depending on what windows are open. The previous image depicts the Navigator window on the left which is displaying the Project Navigator, the main Editor window in the middle displaying the DATE_DIM dimension we just opened and the Property Inspector window on the right displaying properties for the DATE_DIM dimension. Any of these windows can be opened, closed, minimized, or relocated offering tremendous flexibility in laying out our working area. If a window is taking up space and we don’t need it at the moment, just minimize it by clicking the minimize icon in the upper right corner of the window if that option is available. We can also close any window we want by hovering the mouse over the window title and clicking the X that appears or by right clicking over the window title and selecting Close from the popup. The main Editor window cannot be minimized but can be closed.