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
Do not be confused by the use of the word Time to refer to this dimension. In this case, it does not refer to the time of day but to time in general which can span days, weeks, months, and so on. We are using it because the Warehouse Builder uses the word Time for this type of dimension to signify a time period. So when referring to a Time dimension here, we will be talking about our time period dimension that we will be using to store the date. We will give the name Date to be clear about what information it contains.
Every dimension, whether time or not, has four characteristics that have to be defined in OWB:
- Levels
- Dimension Attributes
- Level Attributes
- Hierarchies
The Levels are for defining the levels where aggregations will occur, or to which data can be summed. We must have at least two levels in our Time dimension. While reporting on data from our data warehouse, users will want to see totals summed up by certain time periods such as per day, per month, or per year. These become the levels. A multidimensional implementation includes metadata to enable aggregations automatically at those levels, if we use the OLAP feature. The relational implementation can make use of those levels in queries to sum the data. The Warehouse Builder has the following Levels available for the Time dimension when using the Time Dimension Wizard, which we’ll discuss in a moment:
- Day
- Fiscal week
- Calendar week
- Fiscal month
- Calendar month
- Fiscal quarter
- Calendar quarter
- Fiscal year
- Calendar year
The Dimension Attributes are individual pieces of information we’re going to store in the dimension that can be found at more than one level. Each level will have an ID that identifies that level, a start and an end date for the time period represented at that level, a time span that indicates the number of days in the period, and a description of the level.
Each level has Level Attributes associated with it that provide descriptive information about the value in that level. The dimension attributes found at that level and additional attributes specific to the level are included. For example, if we’re talking about the Month level, we will find attributes that describe the value for the month such as the month of the year it represents, or the month in the calendar quarter. These would be numbers indicating which month of the year or which month of the quarter it is.
We must also define at least one Hierarchy for our Time dimension. A hierarchy is a structure in our dimension that is composed of certain levels in order; there can be one or more hierarchies in a dimension. Calendar month, calendar quarter, and calendar year can be a hierarchy. We could view our data at each of these levels, and the next level up would simply be a summation of all the lower-level data within that period. A calendar quarter sum would be the sum of all the values in the calendar month level in that quarter, and the multidimensional implementation includes the metadata to facilitate these kinds of calculations. This is one of the strengths of a multidimensional implementation.
The good news is that the Warehouse Builder contains a wizard that will do all the work for us—create our Time dimension and define the above four characteristics – just by asking us a few questions.
We could use the regular dimension wizard to create a dimension to use as a time dimension and could define our own levels but in our case, it’s much simpler to make use of the special Time Dimension Wizard to create it for us.
Creating a Time dimension with the Time Dimension Wizard
Let’s start creating our Time dimension by launching Design Center if it’s not already running. In the Project Navigator window, we’re going to expand the Databases node under ACME_DW_PROJECT, and then our ACME data warehouse node ACME_DWH. We will right-click on the Dimensions node, and select New to display a dialog that will show the list of options for creating a new dimension. An example of that screen is shown next since it is a new feature of this release:
We’ll select the Time Dimension and click OK to launch the Create Time Dimension Wizard.
The New Gallery window that we just saw is actually available from the pop-up menu on any of the nodes by selecting the New… menu entry. Most of the nodes, such as tables and dimensions, also contain a New menu entry (New Table, New Dimension, and so on.) that launches right into the wizard as if the New Gallery were displayed and the first option selected. Up until now we’ve been able to use that default New option to create our objects. In the previous release of the Warehouse Builder, if there were multiple options for the New menu option they appeared as sub-menus. This release combines those options into a list in this New Gallery window.
The Time Dimension Wizard will walk us through a six-step process to define the characteristics of our Time dimension. The first screen will describe these steps for us, which is shown here so we can see what it will be asking us:
- The first step of the wizard will ask us for a name for our Time dimension. We’re going to call it DATE_DIM. If we try to use just DATE, it will give us an error message because that is a reserved word in the Oracle Database; so it won’t let us use it.
- The next step will ask us what type of storage to use for our new dimension, shown as follows:
Here we get to designate whether we want a relational physical implementation in the database or a multidimensional implementation. This is what was referred as checking a box to switch between the two. Simply select one or the other, and this is how our design will be implemented in the database with no changes by us required at all.
New in release 11gR2 is the support for cube materialized views in the database for the ROLAP option so there are actually two ROLAP options to choose from but either one results in a relational implementation in the database. Oracle OLAP 11.1 introduced the concept of materialized views for cubes for query performance improvement and this new release of the Warehouse Builder now supports the ability to use them. See the Oracle OLAP Users Guide for more information on using cube materialized views at the following URL: http://download.oracle.com/docs/cd/E11882_01/olap.112/e17123/toc.htm.
We’re going to implement our data warehouse using the pure relational option and not implement any OLAP features so we’re going to select ROLAP: Relational Storage, as shown in the image above and will not select the materialized view option. Both the pure relational implementation and the relational OLAP option are available by selecting the ROLAP option here. We can set a deployment configuration option that defaults to deploying data objects only. But this can be changed to deploy the OLAP metadata to the OLAP catalog also. In both cases, this will result in the generation of relational database objects in a star schema. However, if that option is selected, it will only store the OLAP metadata in the OLAP catalog in the database. We’ll see where to set that option when we look at the data object editor and configuring a dimension.
- Now this brings us to step 3, which asks us to specify the data generation information for our dimension. The Time Dimension Wizard will be automatically creating a mapping for us to populate our Time dimension and will use this information to load data into it. It asks us what year we want to start with, and then how many total years to include starting with that year. The numbers entered here will be determined by what range of dates we expect to load the data for, which will depend on how much historical data we will have available to us. We have checked with the DBAs for ACME Toys and Gizmos Company to get an idea of how many years’ worth of data they have and have found out that there is data for 2007, 2008, and 2009 available to us. Based on this information, we’re going to set the start year to 2007 with the number of years set to three to bring us up to 2009.
The other option available to us on the data generation step is the type of Time dimension to create. It can be based on a calendar year or fiscal year. This provides us with the flexibility to define our Time dimension based on what our company actually uses for its financial year. ACME Toys and Gizmos Company operates on a calendar-year basis, so we’ll leave it set at calendar.
