5 min read

In this article, we will begin to see the real power and flexibility the Warehouse Builder provides us for loading a data warehouse. When we complete the mappings in this article, we will have a complete collection of objects and mappings. We can deploy and run these to build and load our data warehouse.

The basic procedure to build a mapping is the same—start with adding a source and a target, and then include any operators in between needed for data flow and transformation.

Let’s start this article with the STORE dimension and we’ll see some new operators that are involved in transformations.Let’s begin by creating a new mapping called STORE_MAP. In the Design Center, we will right-click on the Mappings node of the ACME_DW_PROJECT | Databases | Oracle | ACME_DWH database and select New…. Enter STORE_MAP for the name of the mapping and we will be presented with a blank Mapping Editor window. In this window, we will begin designing our mapping to load data into the STORE dimension.

Adding source and target operators

Once the POS_TRANS_STAGE staging table is loaded with data, this can be used to load data into our dimensions and cube. We’ll now use this POS_TRANS_STAGE table as our source table. Let’s drag this table onto the mapping from the Explorer window.

The target for this mapping is going to be the STORE dimension, so we’ll drag this dimension from Databases | Oracle | ACME_DWH | Dimensions onto the mapping and drop it to the right of the POS_TRANS_STAGE table operator. Remember that we build our mappings from the left to the right, with source on the left and target on the right. We’ll be sure to leave some space between the two because we’ll be filling that in with some more operators as we proceed.

Now that we have our source and target included, let’s take a moment to consider the data elements we’re going to need for our target and where to get them from the source. Our target for this mapping, the STORE dimension, has the following attributes for the STORE level for which we’ll need to have source data:

  • NAME
  • STORE_NUMBER
  • ADDRESS1
  • ADDRESS2
  • CITY
  • STATE
  • ZIP_POSTALCODE
  • COUNTY
  • REGION_NAME

For the REGION level, we’ll need data for the following attributes:

  • NAME
  • DESCRIPTION
  • COUNTRY_NAME

For the COUNTRY level, we’ll need data for the following attributes:

  • NAME
  • DESCRIPTION

The complete and fully expanded STORE dimension in our mapping appears like the following screenshot:

Oracle Warehouse Builder 11g

We might be tempted to include the ID fields in the above list of data elements for populating, but these are the attributes that will be filled in automatically by the Warehouse Builder. The Warehouse Builder fills them using the sequence that was automatically created for us when we built the dimension. We don’t have to be concerned with connecting any source data to them.

Now that we know what we need to populate in our STORE dimension, let’s turn our attention over to the POS_TRANS_STAGE dimension for the candidate data elements that we can use. In this table, we see the following data elements for populating data in our STORE dimension:

  • STORE_NAME
  • STORE_NUMBER
  • STORE_ADDRESS1
  • STORE_ADDRESS2
  • STORE_CITY
  • STORE_STATE
  • STORE_ZIPPOSTALCODE
  • STORE_REGION
  • STORE_COUNTRY

It is easy to see which of these attributes will be used to map data to attributes in the STORE level of the STORE dimension. They will map into the corresponding attributes in the dimension in the STORE group. We’ll need to connect the following attributes together:

  • STORE_NAME to NAME
  • STORE_NUMBER to STORE_NUMBER
  • STORE_ADDRESS1 to ADDRESS1
  • STORE_ADDRESS2 to ADDRESS2
  • STORE_CITY to CITY
  • STORE_STATE to STATE
  • STORE_ZIPPOSTALCODE to ZIP_POSTALCODE
  • STORE_REGION to REGION_NAME

There is another attribute in our STORE dimension that we haven’t accounted for yet—the COUNTY attribute. We don’t have an input attribute to provide direct information about it. It is a special case that we will handle after we take care of these more straightforward attributes and will involve the lookup table that we discussed earlier in the introduction of this article.

We’re not going to directly connect the attributes mentioned in the list by just dragging a line between each of them. There are some issues with the source data that we are going to have to account for in our mapping. Connecting the attributes directly like that would mean the data would be loaded into the dimension as is, but we have investigated the source data and discovered that much of the source data contains trailing blanks due to the way the transactional system stores it. Some of the fields should be made all uppercase for consistency.

Given this additional information, we’ll summarize the issues with each of the fields that need to be corrected before loading into the target and then we’ll see how to implement the necessary transformations in the mapping to correct them:

  • STORE_NAME, STORE_NUMBER: We need to trim spaces and change these attributes to uppercase to facilitate queries as they are part of the business identifier
  • STORE_ADDRESS1, ADDRESS2, CITY, STATE, and ZIP_POSTALCODE: We need to trim spaces and change the STATE attribute to uppercase
  • STORE_REGION: We need to trim spaces and change this attribute to uppercase

All of these needs can be satisfied and we can have the desired effect by applying pre-existing SQL functions to the data via Transformation Operators.

Packt

LEAVE A REPLY

Please enter your comment!
Please enter your name here