Your message has been sent.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
In this article by Bob Griesemer, author of Oracle Warehouse Builder 11g R2: Getting Started 2011, we will define our cube, which is where our measures will be stored—the facts that users will want to query. 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. 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 will cover:
- Creating a cube with the wizard
- Using the data object editors
|Read more about this book|
(For more resources on Oracle, see here.)
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:
- 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.
- 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.
- 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:
- 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.
eBook Price: $32.99
Book Price: $54.99
|Read more about this book|
(For more resources on Oracle, see here.)
We'll briefly discuss the windows we can see in the image above as well as some of the additional available windows.
- Navigator Windows: The windows available for navigation, the Projects, Locations, and Global Explorer windows that show on the left in the above image. Each of these windows can be displayed or hidden as needed and if not visible, can be displayed by selecting View from the main menu and then choosing the window to display.
- Canvas: This is not really a separate window but is a feature of some editors such as the Mapping Editor, that provides an area in which the contents are displayed graphically. Each object is displayed in a box with the name of the object as the title of the box and attributes of the object listed inside the box. These boxes can be moved around and resized manually to suit our tastes. We'll see an example next when we look at an editor window.
- Property Inspector: This window is visible in the above image and is available for setting various properties of objects that have been selected in the Navigator windows or in the editors. It is context sensitive and will display or not display any properties depending on whether there are properties pertaining to the selected object.
- Configuration: The configuration window displays configuration information about a selected item. Display the Configuration window by right clicking an object in the Project Navigator and selecting Configure... from the pop-up menu. It is here that we can change the deployment option for the object to deploy OLAP metadata if we want a relational implementation to store the OLAP metadata. With the DATE_DIM dimension selected in the Project Navigator, right click on it and select Configure and in the Configuration window click the plus sign beside the Identification section to expand it. It contains a setting for the deployment option and we can see that it is set to deploy data objects only. For dimensions, the options are to deploy to catalog only (the OLAP catalog), deploy data objects only, or deploy all to do both. For cubes, there is an additional option to deploy aggregations.
- Component Palette: The Palette is primarily applicable to editing mappings and provides a list of the items that can be dragged and dropped to build a mapping. It contains each of the objects that can be used in the mapping. We can use this to create objects on our canvas in the editor by clicking and dragging to the canvas. This will create a new object where clicking and dragging from the Projects Navigator will place an already created object on the canvas.
- Bird's Eye View: This window displays a miniature version of the entire canvas and allows us to scroll around the canvas without using the scroll bars. It is applicable only for editors that display a graphical canvas of objects. We can click and drag the blue-colored box around this window to view various portions of the main canvas, which will scroll as we move the blue box. We will find that in most editors, we will quickly outgrow the available space to display everything at once and will have to scroll around to see everything. This can come in very handy for rapidly scrolling the window.
- Editor: This is the actual editor window that contains details about the object we are currently editing. It is the center window in the above image. Various tabs will appear, which display information for us depending on the object being edited. The tabs from left to right are as follows when editing a dimension some of which we can see in the above image:
- Name: This tab displays the name of the dimension along with some other information specific to the dimension type we are looking at. In this case, it's a Time dimension created by the Time Dimension Wizard and so it displays the range of data in our Time dimension.
- Storage: Here we can see what storage option is set for our dimension object in the database, whether Relational or Multidimensional. If we wanted to switch between the two, this is where we could do it. For a relational implementation, we're able to specify a star or snowflake schema and whether we want to create composite unique keys. A composite key is one made up of more than one column to define uniqueness for a record. In most cases, it is a good idea to have this checked as it enforces uniqueness in the database for our dimension records. It will not make a difference in our particular case for the test data we'll be using. For a dimension, it will use the business identifiers we've specified as the key fields.
- Attributes: The attributes tab is where we can see the attributes that are designed for our dimension. It displays the attributes in a tabular form allowing us to view and/or edit them, including adding new attributes or deleting the existing ones. It is here that we can also change the description of our attributes if we wanted, or add descriptions the wizard did not add.
- Levels: This is where we view and/or edit the levels for our dimension. We are able to edit some of the information on this tab for the Time dimension created by the wizard, but not all. We can check and uncheck boxes to indicate which of the various level types we want to use and which attributes are applicable to which level, but that is it. We are not able to add or remove any levels or attributes. If we were to view one of the other dimensions we created, it would be fully editable. For those other dimensions we could also assign different names and descriptions to the attributes for each level.
- Hierarchies: This tab will let us specify hierarchy information for our dimension and will even let us create a new hierarchy. It's possible that we may have selected more levels on the previous page and now need to assign them to a hierarchy. There is also a Create Map button here that will automatically generate the mapping for us if we modify the hierarchies. This is one of the benefits of the Time dimension created by the wizard. Ordinary dimensions such as our Store and Product dimension will not have this Create Map button displayed on their Hierarchies tab.
- SCD: This tab is for specifying the Slowly Changing Dimension policy to use. The Time Dimension will not show this tab because its not applicable to that type of dimension since the contents are preloaded and won't change.
- Orphan: This tab will also not be available for the Time dimension but will be for the Product and Store dimensions. This is a new feature in the 11gR2 release of the Warehouse Builder that provides an automated way to manage what to do about dimensional records (dimensions and cubes) that can't be loaded because one or more records do not have a parent record. This is also known as Early Arriving Facts in industry and Orphan Management is OWB's answer to addressing this issue. Since we're working with a known set of canned test data, there aren't any orphans to worry about but for actual implementations this is a feature that can greatly improve productivity in loading data. For dimensions we have three main categories of Orphan Management to specify, for loading the dimension, removal of records from the dimension, and whether to deploy an error table for orphans.
There are three main options available for handling orphans:
- No Maintenance: This is the default setting and indicates that orphans will not be detected and nothing will be done about them.
- Default Parent: This setting indicates that a default parent record should be used if one is null or invalid and if a default parent doesn't exist, it will create one to use. There is a Settings button available to set the default level row to use.
- Reject Orphan: This will cause the orphan record to be rejected.
- Physical Bindings: This tab displays a canvas with the dimension represented graphically along with the underlying physical table showing how attributes of the dimension are mapped (or bound) to the table columns.
- Data Viewer: The Data Viewer is a more advanced feature that allows us to actually view the data in an object we are editing. This is only available for an object if it has been deployed to the database and has data loaded into it. It has a query capability to retrieve data and can specify a WHERE clause to get just the data we might need to see. For relational implementations, it will not display the data for a dimension or cube; but we can use it to view the data in the underlying table. It is accessible from the View menu by selecting the Data... menu entry when editing a data object.
- Cube Editor: If we edit the Sales Cube, the editor window has a slightly different set of tabs available to it which we'll cover briefly here:
- Name: It has a name tab like the dimensions to display its name.
- Storage: It has a storage tab as per dimensions. However, we see a different option here under the Relational (ROLAP) option where we can create bitmap indexes. An index is a database feature that allows faster access to data. It is somewhat analogous to the index of a book that allows us to get to a page in the book with the information we want much faster. A bitmap-type index refers to how it is stored in the database and is generally a better option to use for data warehouse implementations (so it is checked by default). There is also a composite unique key checkbox for cubes as there was for dimensions.
For a cube, checking this box will create a unique key out of the foreign keys for the dimensions referenced by the cube. We want to check this box to ensure we can't enter duplicate data into our cube, that is, more than one cube record with the same set of dimension attributes assigned.
- Dimensions: Instead of attributes, the cube has a tab for dimensions. The dimensions referenced by a cube are basically its attributes.
- Measures: The next tab is for the measures of the cube. It is for those values that we are storing in our cube as the facts that we wish to track.
- Aggregations: Instead of hierarchies, a cube has aggregations. There are various methods of aggregation that we can select, as seen in the drop-down box, the most common of which is sum, which is the default. This is where the default aggregation method can be changed. There will be no aggregations in a pure relational implementation, so we will leave this tab set to the defaults and not bother changing it.
- Orphan: This feature is available for cubes also and provides an automated way to manage what to do about cube records that can't be loaded because one or more dimension records cannot be found for it. There are two options to account for here, what to do if a dimension key is null and what to do if a dimension key is found to be invalid. In each case there are three options to choose from for how to handle the orphans:
- Default Dimension Record: This setting indicates that a default dimension record should be used if one is null or invalid and if a default dimension doesn't exist, it will create one to use. Default dimension records can be created from the Orphan tab of the dimension as discussed above.
- Reject Orphan: This will cause the orphan record to be rejected.
For any of these windows we'll discuss, if they are not currently visible, they can be displayed by selecting them from the View main menu so when we discuss any windows below that are not visible, that is how we can display them.
These are the main features of the Design Center interface and editors for dimensions and cubes. We can use it to view the objects the wizards have created for us, edit them, or create brand new objects from scratch. We can start with an empty canvas and drag new objects from the palette, or existing objects from the explorer, and then connect them.
In this article we saw how to create a cube using the Warehouse Builder Design Center. We also investigated the flexibility to manually create, view, and edit objects using the data object editors for dimensions and cubes.
- An Overview of Oracle Advanced Pricing [Article]
- Creating Dimensions in Oracle Warehouse Builder [Article]
- An overview of Oracle Hyperion Interactive Reporting [Article]
- Oracle Warehouse Builder: Data Warehouse Design [Article]
- Oracle Warehouse Builder: Creating the Time Dimension [Article]
- Oracle Warehouse Builder: Designing the Target Structure [Article]
eBook Price: $32.99
Book Price: $54.99
About the Author :
Bob Griesemer has over 27 years of software and database engineering/DBA experience in both government and industry, solving database problems, designing and loading data warehouses, developing code, leading teams of developers, and satisfying customers. He has been working in various roles involving database development and administration with the Oracle Database with every release since Version 6 of the database from 1993 to the present. He has also been performing various tasks, including data warehouse design and implementation, administration, backup and recovery, development of Perl code for web-based database access, writing Java code utilizing JDBC, migrating legacy databases to Oracle, and developing Developer/2000 Oracle Forms applications. He is currently an Oracle Database Administrator Certified Associate, and is employed by the Northrop Grumman Corporation, where he is currently a Senior Database Analyst on a large data warehouse project.