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:
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:
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.
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:
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.
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…