Suppose that one of the sources for our Oracle database comes from our Order entry system, which is in an Oracle database. This source would be defined by right-clicking on Oracle under Databases and selecting Create Oracle Module. Figure 13.3 appears, where the module is named and we specify that it is a data source.

Figure 13.3. Warehouse Builder—Creating a Module

Since we specified that our data source is an Oracle Database, Figure 13.4 appears, where we must specify how to connect to this database. At this time a database link can be created, if one does not already exist, by clicking on the New DB Link button. At this time the link will be tested, so make sure that the database is accessible.

Figure 13.4. Warehouse Builder—Creating an Oracle Database Source

Next, we must specify where this data will be deployed; we have chosen the EASY_DATAW location. By clicking on the Finish button the source and target are now created.

We also have to define the target for our design, which is where the design will be deployed. To achieve this the same process is used as for defining our sources. Therefore, this time in Figure 13.3, we would specify THE_EASYDW_DW, which is the name we want to give to our design, and click the radio button for Warehouse Target.

If there are any file data sources that will be input to our data warehouse, these can also be defined at this stage. Suppose that customers can also place orders via PDA devices. These orders are captured from the device and are currently stored in a file for subsequent processing into the system. This data source can be defined by right-clicking on Files in Figure 13.5 and then selecting Create Flat File Module. The details of this flat file are entered into the wizard, and the data source, PDA_SALES, has now been defined.

Figure 13.5. Warehouse Builder—Defined Data Sources and Targets

This process is repeated for every data source in our system, and OWB provides for several standard data sources, including, flat files, SAP, Oracle database, or other databases such as DB2.

After doing all of this work, it is extremely important to save it, because OWB does not automatically save your work. Therefore, whenever you are satisfied with the state of the objects, you must Commit the changes by either clicking on the commit icon or selecting Commit from the Project option on the strip menu at the top.

A mapping is defined from the OWB client by right-clicking on Mappings, in Figure 13.5; selecting Creating Mappings gives the mapping a name and then the Mapping Editor appears, as shown in Figure 13.10. The first mapping that we are going to define is extracting information from our ORDER_ENTRY_SYSTEM and moving it into our data warehouse. OWB can also take information from flat files or from SAP, but we won’t be showing here how that is done.

Figure 13.10. Mapping Editor

When the blank Mapping Editor appears, click on the Mapping Table icon (which is top left in the floating toolbar) and drag it onto the Editor. It now asks you where your table is to come from, and we are going to select the ORDER_LINE table from our source ORDER_ENTRY_SYSTEM, which we defined earlier. Then click on the Mapping Cube icon and select PURCHASES. Now in our Editor we have two tables: ORDER_LINE and PURCHASES.

The next step is to specify which items are to be moved from each table. First, we are going to move the item PRODUCT_ID from the ORDER_LINE table to the PURCHASES table. This is achieved by dragging a line from PRODUCT_ID in ORDER_LINE to PRODUCT_ID in PURCHASES. In Figure 13.10, we can see the line that OWB has drawn between the two items.

The next column to be defined in PURCHASES is the value of the order, and this can only be obtained by computing its value from two columns in the ORDER_ITEM table. To compute this value an intermediate expression box must be created, as illustrated in Figure 13.10.

Click on the Expression icon and drag that onto the Mapping Editor. An empty box appears with empty input and output groups. Take the two columns from ORDER_LINE, unit_price and quantity, and drag them over to the input group box. Now we have to use the right mouse button to add a new attribute in our output group, which we will call Total_Price. Do this by choosing Edit from the right mouse menu, and under the Output Attribute tab add the new attribute, called Total Price.

Then you select that item’s properties and click on the expression box and the expression builder will appear, where you can specify how the attribute is to be computed, which in our case is to multiply the two numbers together. Then drag a line from TOTAL_PRICE to the column SUM_PURCHASES in PURCHASES to complete the mapping operation.

Another common task that our warehouse designer has to perform is joining data from two or more tables to extract information that is used as input to another table. In Figure 13.11, we see a join that we have created between ORDER and ORDER_LINE to enable us to store one record in our data warehouse for the total value of the order. Note that the expression that we created in Figure 13.10 has now been used as input to the join.

Figure 13.11. Mapping Editor with Joins

Previously in this book we have described surrogate keys, where the natural keys used in the sources for your data warehouse are transformed into a key used by the data warehouse. In OWB we can define exactly how that transformation should occur by using the key lookup feature.

In Chapter 5, we described the process of converting the product code used in out OLTP system to a surrogate key in the data warehouse. In Figure 13.12, this has been implemented in OWB by showing that the column PRODUCT_ID from the join between ORDER and ORDER_LINE is input to the key lookup process. Although not visible, a mapping has been defined that states that PRODUCT_ID is to be matched to PRODUCT_ CD. The resulting output is the column PRODUCT_ID, which is sent to the PURCHASES table.

Figure 13.12. Product Key Lookup

When data is being extracted from sources such as our OLTP system, there may be times when we do not want all of that data to be sent across to our data warehouse. In OWB, this is not a problem, because it allows us to filter the incoming data in a variety of ways.

In Figure 13.13, we are filtering the items from the ORDER_LINE table according to criteria that we have specified. For example, we could say that we are only interested in certain products. Also see in our example how, after we have filtered the data, we are then applying the expression we defined in Figure 13.10 to the data that has passed through the filter.

Figure 13.13. Filtering Source Data

There may be times when, rather than extracting data from a source, the data must be automatically generated. OWB has this capability, and it can automatically create:

In Figure 13.13, we can see the system date being used to set the date for our order. This was achieved by dragging the Data Generator icon onto the Mapping Editor, and then selecting the item required and attaching it to PURCHASES.

Over these few pages, we have barely touched the surface on the types of transformations and mappings that are possible with Oracle Warehouse Builder. All of the icons shown in the toolbox in Figure 13.13 can be used to define your warehouse. Also, in these examples we have kept them simple, but in a real warehouse, they would be connected; we did start to show this in Figures 13.12 and 13.13 where the expression was being used as input to another stage in the loading process.

Although we have generated a logical design, it is most likely that it does not include all of the physical aspects of our design, such as whether a table is partitioned and which indexes are needed. These physical components can be configured in OWB by selecting the module to be configured, right-clicking on the mouse, and selecting Configure. Figure 13.21 is displayed, which lists all of the properties that you can configure. In this example, for our COUNTRY table, we can see that an index has been defined, and the table is going to be stored in tablespace USERS.

Figure 13.21. Configuring the Physical Design

When defining indexes, OWB has the ability to automatically recommend bitmapped indexes; clicking on the Generate button shows these recommendations.

Normally, the physical design for the objects would be done prior to deployment. However, in our example we have created the EASYDW warehouse and then returned to OWB, defined more physical design attributes, and then redeployed.

Hopefully, you now have an appreciation of what OWB can do. This is a very powerful product and, over these few pages, we have only been able to highlight some of its features. What we have not covered here is how OWB can be managed using a browser and integrated into Oracle Portal.

There are many benefits from using tools such as Oracle Warehouse Builder. It provides a visual representation of your warehouse and, by using the various wizards that are available, it is easy to complete the tasks needed for building the warehouse. In addition when changes to the environment occur, you can visually see the impact of them and OWB can easily incorporate them into the environment. The Deployment Manager enables you to clearly see the state of all modules in the system and view the code for those modules.

Now that you have deployed the physical design to the database, you may want to enable this for a query tool. OWB has the capability of transferring the design metadata into a Discoverer environment, which is described in the next section. By doing this you can save yourself time and effort in creating the reporting solution on top of your logical design. A second benefit is the concentration of metadata in a single place, allowing you to reduce the maintenance efforts for your integrated solution.

Oracle Warehouse Builder provides a comprehensive environment for building and managing your data warehouse that could significantly reduce your development time and costs when its full potential is exploited.

Many organizations run Discoverer independently, and Discoverer Viewer provides a user with the ability to access predefined reports and have the capability to modify those reports in a limited way. The advantage of this approach is that the user only needs a PC and a browser to access the data in the warehouse and the DBA can rest assured that users cannot change the data.

You can start Discoverer viewer from your browser using a URL such as:

http://easydw.com:7777/discoverer/viewer

A connection is made to this database, and a list of the available workbooks that we can attach to is presented. We select our workbook, EASYDW and the query contained within is immediately executed. In Figure 13.23, we can monitor the progress of our executing query, and when it has completed our report is displayed, as shown in Figure 13.24.

Figure 13.23. Discoverer Viewer—Executing Our Query

Figure 13.24. Discoverer Viewer—Category Sales by Country

Since we defined this report as both a table and a graph, both versions are displayed in Figure 13.24. Note that at any time we can hide the graph or table by clicking on the Data or Chart option. The report shown here is the default generated by Discoverer, and I am sure you will agree that it is a very nice format, which is presented well and is easy to understand.

Inside a workbook there can be many reports. In Figure 13.24, we can see a list of those reports in the top left. Any of these reports can be run by simply clicking on the report name.

One of the advantages of using Discoverer Viewer is that is the user is not able to change the data in the warehouse or generate new reports. However, there are limited customizations that can be done to this report, such as changing the sort order or table layout, or, by clicking on the Presentation Options items, as illustrated in Figure 13.25, changing how our report looks.

Figure 13.25. Discoverer Viewer—Presentations Options

One of the features that makes Discoverer nice to use is the ability to group together a number of reports that can be easily run and customized to your own requirements. In the example shown here, we have a workbook called EASYDW and, referring to Figure 13.24, at the top left of the screen we can see a tab, with two entries on it, for each of the reports that are available:

In Figure 13.24, we see both a table and a graph generated from data in our warehouse. When this report is run, the user is prompted to specify his or her criteria for which years the sales are to be displayed.

When a report is produced, someone reading it may say that this is very interesting, but I need to know more about how this data is derived. For example, suppose the user is viewing the report shown in Figure 13.24 and sees that we sold many items in the United States, but how does that break down by state and is it more than we sold in the United Kingdom?

By clicking on Country, a drill-down list is displayed and State was selected; Figure 13.26 shows us the report with the data by state. Now we can see that we sold across a number of states, rather than in just one state, and U.K. sales are equivalent to selling in over four U.S. states.

Figure 13.26. Discoverer Viewer—Drill Down

Now that we have caught a glimpse of the types of reports, Discoverer can produce, we must return to setting up Discoverer, because, before you can use it, that setup we mentioned earlier must be performed.

The first time the Discoverer Administrator is started, you will be asked to create an end-user layer (EUL), which consists of all the metadata that is needed by Discoverer; therefore, this task will be performed only once. Figure 13.27 shows the screen where you can manage the end-user layer, including creating a new one or deleting an existing one.

Figure 13.27. Discoverer Administrator—Create an EUL

The wizard will then ask who will own the EUL. In our example, it would be EASYDW. Now click on the Finish button and the end-user layer is created. This process may take a few minutes, depending on the complexity of the schema that it has to analyze.

Once the definition of the end-user layer is complete, a Business Area is named, where all of the information needed to query the warehouse must be defined. The purpose of the business area is to group information into business-oriented categories, such as Sales or Finance, which are familiar to end users. Business areas are the unit of access control and a user can be assigned to one or more business areas. Users will then have access to all of the objects within the assigned business area.

Within this business area, you will specify exactly which data a user may access, how that data may be joined, and describe data aggregations and new data items based on calculations on existing data items. Creating the business area will take some time, but it will reap significant benefits later.

When creating the business area, we must first specify the schemas from where data is to be made available to the end user, as illustrated in Figure 13.28. We can select any number of users from the list and also request that it only selects items from the list that match the pattern specified in the box. In our example, we have selected only the user, or schema EASYDW.

Figure 13.28. Discoverer Administrator—Select the Schema

Once we know the schema from which the data will be available, we can then explicitly state from which tables or views we can retrieve data, as shown in Figure 13.29. Using our Easy Shopping Inc. example, we have given the users access to all of the five tables in this data warehouse.

Figure 13.29. Discoverer Administrator—Select the Tables and Views

One extremely useful feature in Discoverer is the ability of the wizard to automatically create joins based on primary and foreign keys and create hierarchies from the data, as illustrated in Figure 13.30. By allowing the wizard to perform these tasks, there will be less setup work for you to do, and by default these options are already selected. The advantage to the end users is that when they are constructing queries they won’t need to know how to join the data, because Discoverer will know this from what was done at this stage. Therefore, end users require minimal computer design knowledge in order to construct reports in the database.

Figure 13.30. Discoverer Administrator—Automatic Joins

There is one final screen before the first stage in creating the business area is complete. In Figure 13.31, we must name the EUL and optionally add a description to describe what is being held. Discoverer will also generate names for all of the objects, and in Figure 13.31 you can see the options available, such as capitalize or replace underscores with spaces.

Figure 13.31. Administrator—Naming the Business Area

Clicking on the Finish button, completes the first stage in creating our business area. A task list will appear, as shown in Figure 13.32, to help remind us of the steps to follow.

Figure 13.32. Discoverer Administrator—Task List

In Figure 13.33, we can see that part of the basic EUL screen in Discoverer from where everything can be managed. Discoverer has already identified that the table PURCHASES can be joined to the customer, product, and time table and that some computation may be applied to the column purchase price.

Figure 13.33. Discoverer Administrator—Setting up the Business Area

With the basic EUL is place, this is where the real work begins, since we can now set up the rest of the business area; although we have shown only the creation of one business area, you could create any number of business areas in your environment, each with its own unique set of data requirements.

By default, when access is given to a table, all the columns in that table are accessible. What is nice about Discoverer is that users can only access data via the business area. If the table and column are not given visibility by Discoverer, then the user will never even know that this data existed.

In Figure 13.33, we can see all the tables from the database that we have access to. Note that our table TODAYS_SPECIAL_OFFERS is now called Todays Special Offer, as a result of the naming change requested earlier. By default, the end user will have access to every column in those tables. If you click on the table name to expand it, all the columns in that table will appear. To remove any of those columns, simply click on the item using the right mouse button, and a drop-down list will appear. One of the items in that list is Delete Item. Simply select that option, and the item will be removed from the business area but not from the database.

Before moving on, there is some terminology that you should familiarize yourself with. In Discoverer, a table or view is known as a folder, and a column from the table is called an item. A folder can be one of two types:

An item corresponds to a column in a relational database. A simple item is based on a single column in the database, but an item can also be calculated or derived based on a formula using other items, functions, or operators.

The attributes of any of the items that you have selected may be modified by selecting that item, clicking on the right mouse button, and then selecting Properties. The window illustrated in Figure 13.34 will appear, and you can then modify whichever properties you like. In this example, we have changed the item’s name from Supplier to Main Supplier, which means that our users can be presented with friendly meaningful names, rather than computer format names.

Figure 13.34. Discoverer Administrator—Change the Item Details

When the business area is first created, if the database has primary and foreign keys defined, then Discoverer will automatically create joins between those constraints. However, you can specify your own joins by selecting Insert from the strip menu and then Joins.

Another feature that many DBA’s may require is the ability to create new columns or items in the database by calculating their results from other columns.

A calculation creates a new item in the end-user layer. It will not add underlying columns to database tables and is used to create a new item where there is no underlying database column that contains the data required. Calculations can be simple, such as weight * 4.54 or they can be complex mathematical or statistical expressions. For example, in Figure 13.35, a new column called Total Cost is created in the PURCHASES table by adding together the columns purchase price and shipping charges. You can create as many of these types of calculations as you require, and they will appear as an item for that table.

Figure 13.35. Discoverer Administrator—Creating a Calculated Item

When the EUL was first created, Discoverer tried to identify which joins to create from the primary- and foreign-key relationships that had already been defined. However, there may be times when additional joins are required and these can easily be created using the join wizard.

A join is created by clicking on Create Joins in the Discoverer Administrative Task List, which is shown in Figure 13.32, or from the Administration Work Area, shown in Figure 13.34, by clicking on Insert and then Joins; the wizard will then appear, as illustrated in Figure 13.36.

Figure 13.36. Discoverer Administrator—Define a Join

By using the wizard, give the join a name and then, from the drop-down list, select the table and columns to join on and the type of join. In our example in Figure 13.36, we have specified a join between the TODAYS SPECIAL OFFERS table and the PRODUCT table using the column PRODUCT_ID. The advantage of defining these joins now is that when a user writes a query, Discoverer will know how to join the data, so it’s one less piece of information that our user has to supply; in this way we ensure that a meaningful join is being applied.

Click on the Next button to proceed to the second and final join wizard screen, where you can specify some more advanced options about the joins.

We have already seen that hierarchies play an important role in our data warehouse. Although in Oracle we can create dimensions, at the time of writing, these are not used by Discoverer, and we must create our own dimensions, which are known in Discoverer as hierarchies. Hierarchies are very important in Discoverer, because if an item is in a hierarchy, then users can:

This is how we were able to drill on our report shown earlier. A hierarchy is created by clicking on Insert in the menu at the top and then selecting Hierarchy. First, you will be asked about the type of hierarchy you want to create: an item or a date. When the business area is first created, it is quite likely that Discoverer will automatically create a time-based hierarchy. Therefore, you will probably only have to create nontime-based hierarchies.

Figure 13.37 shows how easy it is to create the hierarchy by selecting the items and then defining the hierarchy relationship. In Figure 13.37, we have created a very simple hierarchy between PRODUCT_ID and CATEGORY.

Figure 13.37. Discoverer Administrator—Defining a Hierarchy

When the end users are actually querying the data, there are times when it may be helpful to them if they can see a possible list of values. For example, suppose they want to pick out all of the electrical items. If they know which category they are represented in, then this will facilitate rapid query generation.

An item class can describe the hierarchical relationship between items, a list of values, alternative sort keys for items, and the display methods. Therefore, an item class defines all of the attributes for an item. Once an item class has been defined, it can then be assigned to other items, which share similar properties.

An item class is created by selecting Insert from the menu at the top and then Insert Class from the list or by clicking on the text in the Administration Task List, shown in Figure 13.32, the Item Class Wizard will now appear.

The first step is to define the type of item class, a List of Values, Alternative Sort, or Drill to Details, which is used to drill between a summary and the detail. Then all you have to select is the column containing the values and which tables will use it. When it is complete, click on the tab Item Classes, and the window shown in Figure 13.38 will appear.

Figure 13.38. Discoverer Administrator—Item Class

In this example, a list of values called Categories has been created from our products dimension table. If we expand the entry categories, the data warehouse can be queried, and all the different values will be displayed; in Figure 13.38, we can see some of the different categories for products that are sold. Here we only have a few items, but in the real world, where you may have a number of different values, you can omit this step of displaying the results.

When an item class is defined, you can also define the sort order for the data. In Figure 13.39, we have chosen to use the conventional alphabetical method, but that is not always suitable, so Discoverer will allow you explicitly to specify a logical order, such as N, S, E, W (North, South, East, West) rather than E, N, S, W.

Figure 13.39. Discoverer Administrator—Creating a Summary

We have already seen the importance of creating and using materialized views in our data warehouse in Chapter 7. Discoverer allows you to create your materialized views, which Discoverer calls summaries, from its summary wizard, by:

To create your summary, click on Insert in the menu at the top, and then select Summary and the summary wizard appears. Three types of summaries can be created:

Then the window shown in Figure 13.39 appears, where you select the folders and items from within those tables that are to appear in the summary. In our example, we have selected only the PURCHASES table, but you could select multiple tables. Then we have chosen four of the data items in that table. For the item purchase price, we have asked that this value be aggregated. You will see that Discoverer will automatically supply a range of functions for you to select when a function may be applied to an item. In Figure 13.39, for the item PURCHASE_PRICE, we have asked that the SUM function be applied to this item. Next, you are asked to specify which groups of items you require.

As we have seen, it is very important to ensure that the materialized views or summary contain the latest data. When defining your summary in Discoverer, you can specify how often it is to be refreshed. Remember that, as new data is added to the warehouse, the summaries must be maintained to reflect the latest data. In Figure 13.40, we can see that we have stated that this summary should be refreshed every day.

Figure 13.40. Discoverer Administrator—Refreshing the Summary

If you don’t know which summaries to create, Discoverer can recommend them for you using its own summary wizard. In Figure 13.41, we can see one of the steps from the wizard, where we can select the summaries we require based on our space requirements. This wizard is very similar to summary management’s own SQL Access Advisor, described in Chapter 10, but the recommendation process used by Discoverer is different from the one used by the Oracle SQL Access Advisor.

Figure 13.41. Discoverer Administrator—Summary Wizard

Creating either Discoverer summaries or materialized views is very important if you want to achieve the fastest query response time. Here, we have seen how to create summaries directly in Discoverer, but you can create materialized views, as discussed in Chapter 7, or use the SQL Access Advisor, which was described in Chapter 10. Irrespective of how the materialized view is created, Discoverer will still use it whenever possible.

The final setup task is defining who may access the business area that you have just created. You can start this component by double-clicking on Grant Business Area Access, shown in Figure 13.42. A window will appear that will allow you to state which users can access a business area or which business areas a user can access. In Figure 13.42, we can see that the only user who will be granted access to our business area is EASYDW. Don’t forget that although in this example we have enabled access via the user name, you can also grant access to the business area via the roles that may be given to a user. When you are satisfied that all the relevant access rights have been given, click on the Apply button to complete the changes.

Figure 13.42. Administrator—Granting Access to the Business Area

In Figure 13.43, our complete business area is shown. Expanding just the PURCHASES table, we can see all the items that are available to our users; the new calculation that we created, called Total Cost; and the functions that we can apply to that column. At the bottom of the window, we can see that joins have been created from the PURCHASES table to the PRODUCT and TIME table. Of course, not all the information can be displayed on this one window, so, to see the Hierarchies tab, you will have to click that tab. The same is true for item classes and summary information.

Figure 13.43. Discoverer Administrator—Business Area

We have now completed all of the basic setup tasks for using Discoverer. Don’t forget to save all of your work, and please remember that the tool is much more comprehensive than we have shown in these few pages. For example, we haven’t shown here that Discoverer fully supports the Oracle analytical functions to enable sophisticated analysis of the data. Now we can start using Discoverer Desktop, Viewer, or Discoverer Plus to retrieve data from our data warehouse.

In Figure 13.69, we see an example of a report using the matrix with a group layout, where we can view the total amount that has been purchased for each of our manufacturers for the month of January.

Figure 13.69. Oracle Reports—Matrix Report

The extensive capabilities of Oracle Reports begin to become apparent when we see how defining a condition on our report can be used to highlight when specified conditions have been reached. In Figure 13.70, we have chosen to highlight in red any customer who spent less than $250. We could even go further and specify a number of conditions, all highlighted with a different color. Therefore, customers spending less than $250 are in red, up to $1,000 in yellow, and over $1,000 in black. Unfortunately, in a black and white book we can’t show this, so the red numbers are in italic.

Figure 13.70. Oracle Reports—Conditional Report