Implementing the

Redland Genstar Data Mart

by Anissa
Stevens, AVANCO International, Inc.

Data
Marts, departmental versions of a Data Warehouse, have increased in popularity
as corporate America
has come to realize that they serve as low cost analysis tools for improving
specific corporate business processes. Oracle's Data Mart Suite is a
non-complex bundling of software products which includes Oracle Enterprise
Server 7.3, Oracle Designer, Oracle Data Mart Builder, Discoverer 3.0 and
Oracle Web Server. It is designed to provide all the tools needed to easily
implement a data mart on the Windows NT platform. Avanco
participated in the development and deployment of the first Data Mart Suite
sold by Oracle Corporation and we have a unique insight into the best methods
for deploying an Oracle Data Mart.

This
case study begins with a discussion of data marts in a decision support
analysis environment and some reasons why data marts are so popular. This
technical discussion will center on data mart theory, architecture, and a
design methodology which will lay the groundwork for a discussion of specific
methods for developing a data mart using each of the Oracle Data Mart Suite
tools. The context for this "How To" discussion will be lessons learned
in the actual design, development, and deployment of the first Oracle Data Mart
Suite at Redland Genstar.

Data Mart Overview

A
data mart is a simpler form of a data warehouse focused on a single subject (or
functional area) such as sales, finance, or marketing. Data marts are not
differentiated from data warehouses based on size, but on use and management.
However, data marts are typically smaller and less complex than data warehouses
and therefore are typically easier to build and maintain. Data marts can be
built as a "proof of concept" step toward the creation of an
enterprise-wide warehouse. A data mart can be built in a 3-6 month time frame
where a data warehouse will take from 2-3 years to complete.

The
fundamental tasks in data mart development are designing, feeding, storing,
accessing, and managing the data environment. Using the components of the
Oracle Data Mart Suite, each tool is used as appropriate to perform the steps
in the process to build a data mart.

Redland Genstar Background

We
deployed the first Oracle Data Mart Suite at Redland Genstar.
In business for more than 80 years, Redland Genstar
Inc., of Towson, Maryland,
is one of the oldest stone, blacktop, and concrete companies in the United States. Genstar has been undergoing a transition since 1999, moving
its financial systems from an IBM ES/9000 mainframe to an IBM AS/400
client/server environment. To help its sales and marketing representatives
analyze the customer base of construction and pavement companies and to
eventually assess the efficiency of its distribution network, Genstar is building a data mart that pulls data from the
ES/9000 and AS/400 systems and integrates it, utilizing Oracle Data Mart Suite
for Windows NT.

Genstar's goal in building the data mart was to provide graphical
ad hoc access to information without involving IT every time someone requires a
report.

Steps in Building a Data
Mart

We
followed a series of steps in the design, build and implementation of the Genstar data mart to ensure successful deployment.

1. Identify Business
Drivers

Before
implementing any Data Mart, it is imperative to have a strong business driver
for the project. A business driver answers the questions, what is the business
need and Return on Investment (ROI) that fuels the need for the effort?

In
Genstar's case, they had a short window to get
reports on an as-needed basis to check their profitability status. The old
process of running reports from the AS/400 machine took approximately one week.
The Data Mart reduced this time tremendously and allowed for quicker analysis
and improved decision support within the enterprise.

"The
data mart will enable us to analyze trends and to ask a variety of questions we
haven't been able to ask very well," Claudia Serfass,
Genstar's manager of Information Technology said
recently. "These are simple but important questions such as 'Who are we
selling to? In what volumes? Where are our customers
located? And when do they buy?' When we find out who our best customers are or
where we're lacking, we'll use the information to expand that market. We also
want to know if we're distributing our goods as efficiently as possible.
Loading distribution data into the data mart will give us those answers."

2. Identify Sponsorship

Data
marts in general require approval at a much lower level of the corporate
sponsorship chain than a Data Warehouse, for example at the manager level. Data
Warehouses require a very high level of sponsorship, usually CEO and/or board
level. On the other hand, the higher the sponsorship level is for a data mart,
the more likely the project will become successful.

Genstar had a highly committed sponsor to the project, the
divisional manager for Redland both in San Antonio
and Maryland.
There was a lot of interest in having this project up and running in short time. It is important to make sure the sponsor has influence
over the organization. The idea is for their testimony for the data mart to
carry weight and therefore keep the interest going, and the resources
committed.

3. Conduct Readiness
Assessment/Site Check

In
order to have a working knowledge of the systems, data, and people who will be
working on the data mart, it is essential to spend the time up front completing
a readiness assessment. It is important to have people on the design team who
understand the source system. At Genstar, it was
necessary to have someone who understood the billing system, so we could
understand the calculations going into the data mart, and why.

Establish
the type of business that the data mart will deal with, the size of the budget,
the level of urgency to get the product up and running and the size of the user
group community. Who is going to use the product? Without understanding the
targeted business environment, fulfilling user requirements becomes difficult
at best. It is essential to establish the working environment and server type
that the Data Mart Suite will be loaded on. The server should be robust enough
to handle the Data Mart Suite.

In
Genstar's case, they had purchased a server, a Compaq
Proliant 6000 server, with 256Mb of RAM, and a single
200Mhz processor, with the capability of upgrading to
four. There were four 4.3GB SCSI hard drives, which were striped for load
balancing. It was more than sufficient for the initial stages of the data mart,
and their initial database of 1 1/2 years worth of data. Genstar
has current plans to add three more processors and double the hard drive space
as more data is added.

4. Survey User Needs

The
next vital step in creating a data mart, before the design phase, is to answer
some basic questions concerning the customer that will drive your design. Since
a data mart is based on a single subject area, it is important to clearly
define a subject that is measurable.

For
instance Genstar identified profitability as their
primary subject area. There were very discrete measures for this report, such
as revenue, total cost, and item quantity. The subject area needed to be clear
from the beginning. If it is not apparent from the start, find out from the
customer what types of reports they would like to see, or are currently trying
to see. From these answers, the subject area can be finalized. In our case, we
found that having the client choose a series of reports which they had never been
able to produce on the AS/400 resulted in immediate client interest for the
project and greater cooperation for the prototype.

In
working with the subject area, find the level of informational detail the
customer will want to see on the reports. Focusing on the degree of data
granularity will have an impact on the design of the fact table. Genstar wanted to add a lot of detail to their fact table,
which could have caused a lot of overhead if numbers were not used
realistically. It is important to get from the customer what they NEED to see
in their reports, not what they WANT to see. It may be hard to differentiate
between the two, but it is a necessary step. It is very easy to add as much
detail as possible, because the client will see it as an open opportunity to
get to all of the data. In reality they will not use the majority of the
information and the subject area will be poorly focused.

The
immediate obstacle, as with any Data Mart, was the process of learning Genstar's business model to the degree that we could
understand the calculations going into the system. Their older billing system
did not make calculations for profitability, but had all of the calculations
necessary to get to profitability. We designed the tables so that profitability
would be pre-calculated and stored in the fact table, to avoid calculations on
the fly in the Discoverer browser, which could slow down queries. These
calculations were an ongoing learning process. It is not necessary to know
everything about the business to procede with the
project. It is necessary to have someone available on the project that can
answer business related questions quickly and has the authority to do so.

It
is also important to find out what the required business dimensions are for the
reports. For instance, Genstar gave our team a
prototype report of profitability by customer and by product line which
included Concrete, Blacktop and other manufactured products. The dimensions
derived from these specs determined the level of detail in the star schema.

It
is also necessary to determine from the customer the length of time they would
like to have data stored in the initial database. Will the Data Mart store one
year of data or many? The time dimension is a vital piece in the star schema.

The
answers to all of these questions will allow for a much more focused data mart
design that the end users will be able to get answers from quickly and without
frustration. It is also necessary to determine the source systems from which
the final data mart will be populated. Genstar
downloaded files from the AS/400 machine to comma delimited flat files for load
into the Oracle database.

5. Install Oracle Data
Mart Suite

The
Data Mart Suite installation is very straightforward. It installs the data mart
database, DMDB, as well as the enterprise manager database, EMDB. It sizes and
tunes the database to a sample size data mart, which should be customized to
the specific environment. It does not install the Data Mart Designer product by
default and must be installed during a custom install.

The
following is a list of the sample tablespaces after
the installation:

Tablespace_Name

ROLLBACK

SYSTEM

TEMP

USERDATA

It
is a good idea to add another rollback segment tablespace
for large transactions during the load process. Since this is not an OLTP
database, large rollback segments are not needed during production, just during
the monthly download procedure. In order to perform the initial load, it is
extremely important to have this rollback segment available. The USERDATA tablespace can be used for the data that will be loaded
into the data mart, but it is probably better to create another data tablespace that will hold all of the target data, and an
index tablespace for all of the indexes on the data.
Make the temporary tablespace size about one half the
size of the data tablespace, since there will be a
lot of sorting and querying of tables.

6. Design Target System
for Data Mart

Once
all of the information is gathered, it is time to start designing the target
database. Use the Data Mart Designer to create the target database. The Data
Mart Designer is a scaled down version of the Designer/2000 Oracle product. The
Data Diagrammer (Fig. 1) within the Designer tool
will allow the creation and definition of entities.

Figure 1. Data Mart Diagrammer
(click to
enlarge)

The
central focus of the data mart is the star schema. The star schema consists of
a main fact detail table with dimension tables branched off of the fact table.
The detailed fact table contains a key to each dimension table as well as any
metrics used to quantify the subject area, such as profitability was for Genstar. The dimension tables hold the key to the fact
table and any information regarding the particular dimension. The Data Mart
Designer will create the DDL necessary to create the tables as well as the
constraints on the tables.

Instead
of using the direct codes from the dimension table, we recommend using a
synthetic key as both the primary key to the dimension table, and the foreign
key to the fact table. The key can be generated via the Oracle Data Mart
Builder. The size in bytes of the synthetic keys as compared to the actual OLTP
keys can be significantly smaller and more efficient if there is a lot of data
in the fact table. It is also important to use the synthetic keys in order to
keep uniqueness on data that is brought into the target system. It is essential
to understand the data that is being migrated and to ensure that duplicate
information is not stored uniquely in the fact table. Remember, this is a very
"data driven" process.

The
Genstar fact table was built from tickets and
invoices of stone products, etc. that were both purchased and hauled to a
certain site. Fields in that fact table included a key to each dimension, as
well as calculations for that ticket, such as item cost, freight cost, total
cost, revenue, profit, and item quantity. The dimension tables included a
customer table, product table, plant table, hauler table, sales rep table,
delivery zone table and a time dimension table. For example, the customer table
contained a customer key, customer code, customer name
and customer type field. As you can see (Fig. 2), the customer code and name
are in the same table, in a denormalized fashion to
avoid branching out to another table containing simply a customer code and
customer name. The key to each of the dimension tables is in the main fact
table, Ticket_Invoice, as well as the measurable
fields for profitability.

Figure 2. Sample Star Schema

The
time dimension is a vital piece in the star schema. The key is usually a Julian
date field that is based on a date in the main fact table. The Data Mart
Builder (Fig. 3) will create and load the time dimension table, which creates
the Julian date id that will represent every day of a year for the amount of
time specified in the creation of the time table. As you can see we chose the
start date to be January 1, 1993 and the amount of days to be 1461, which is
the equivalent of 4 years plus one day for a leap year.

Figure 3. Data Mart Builder Time Generation Transform
(click
to enlarge)

The
table will automatically be created and populated in the schema. In the main
fact table it was necessary to make a particular date the field to drill down
in order to get information. We used the invoice date in the Oracle date format
as the drill down field for information in the fact table. We had to create a date_id field in the fact table, which was the key to the
dimension table, and populate that field with the Julian date of the invoice
date field. This allowed for any information grouped by that particular invoice
date to be drilled down to using the time dimension link.

7. Data Mapping,
Extraction, Transformation and Scrubbing

Once
the star schema is designed, it is time to load the target system.
Oracle’s Data Mart Builder is used for this process. Within the tool,
create a baseview, a pictorial look at the database
tables, for the target system. It is also possible to create a baseview for the source system if the data is loading from
another database, either from an Oracle database or any other database
management system. Other relational databases connect either natively or by
using an ODBC driver.

With
the Data Mart Builder, several functions are performed with the data before it
is actually loaded into the target database. Genstar
exported their AS/400 data down to a flat file, comma delimited, separated by
double quotes. To load this data from the builder, we navigate to the transform
window and choose the CSV(comma separated variable)
Source transform by dragging it into the top right hand of the screen. Double
click on the CSV transform and type in the datafile
name of the flat file. It is important to remember that when creating flat
files, the first line in the file must correspond to the columns of the table
to be loaded. It is possible to load one flat file to several database tables.
It is also possible at this time to do any cleansing of the data. By using the
search and replace transform it is possible to transpose some of the data that
is coming in from the flat file. Dragging the grid transform over and clicking
the update button allows the user to see what the data will look like when it
is loaded before it actually goes into the database. Once the data is correct,
the SQL*Loader transform is added to replace the view Grid. It is also an
option to use the Batch Loader transform, but the SQL*Loader handles larger
amounts of data more rapidly by using the direct path loader as unrecoverable.
If that option is used, make sure a bad file and a log file location are put in
the properties of that transform. Also at the same time the data is loading
from the flat file, it is possible to generate the synthetic keys with the key
generation transform. Figure 4 demonstrates the search and
replace transform and the key generation transform of the customer
table, which automatically generates the synthetic key, cust_key.
It is also possible to load the table through the CSV transform. Once the star
schema is populated, it is time to build aggregations.

Figure 4. Data Mart Builder With
Transforms (click
to enlarge)

8. Build Aggregation

Summary
tables are a very important consideration for data mart design. Summary tables
are built in order to reduce the amount of processing time to gather data that
is frequently used from the detailed fact table. For example, Genstar has a fact table of about 1.5 million records. They
frequently gather and execute reports by month, quarter and year. Instead of
running a particular query from the main fact table to produce a report that
took approximately 8 minutes to run, we built a monthly summary table and the
query executed in 30 seconds. The number of records in the monthly summary
table was 200000, much smaller than the 1.5 million records in the main fact
table. The yearly table had approximately 140000 records. The basic idea behind
building the summary table in our case was to gather data off of the main
detailed fact table, and roll it up to a month level, a quarter level and a year level. Be sure to have the summaries built
only from the detail fact table and dimension tables. Never build an
aggregation or summary that contains data from a source that is not represented
in the data mart system fact and dimension tables.

There
are several methods which can be used to build the summary tables, either with
the Discoverer tool or independently from the Discoverer tool. Both require you
to register them in the tool for query purposes. The Genstar
aggregate tables were created outside of the Discoverer tool, and registered
within. This allowed for greater flexibility in the download procedure as well
as the naming conventions of the tables.

9. Build Oracle
Discoverer End User Layer

Once
all of the Data Mart tables are built and registered, it is time to work with
the Discoverer Administration product and create the End User Layer. The end
user layer will be customized to fit the types of reports that the customer
needs to see. Change the names of the columns so that they are more user
friendly, as well as create drill down relationships on columns, such as date
fields, and region to city. The purpose of the end user layer is to make
running reports as painless to the user as possible, without a large learning
curve. See Discoverer 4.1 - Strategies for Proactive End User Layer
Administration for more information about creating a Discoverer EUL.

10. Create Discoverer
Workbooks For Users

If
there are users who have not used the Discoverer product before and are not
familiar with creating workbooks, it is a good idea to create workbooks and
have users run them, instead of having them create the reports from scratch.

With
Genstar, managers wanted to run profitability reports
by month/year/quarter. We created several workbooks for monthly profitability
with sheets in the workbook corresponding to a particular month. That way,
users of the Decision Support System could run one workbook and view each
corresponding sheet in order to do a comparison by months. We did the same
thing for year and for quarter. This also allowed quicker analysis of data for
the end users, which in this case were sales staff and managers trying to meet
their sales quota numbers for the year. They did not have to spend the time
creating the workbooks themselves. End users could also edit the workbooks if
they chose and save them on their own machine or in the database to get more or
less detail in their reports and personalize the reports for their own needs.

11. Create Recurring
Download Procedures

As
a last development step, we scheduled download procedures for source to target
data loads. It is essential to decide the frequency of downloads for the data
mart. At Genstar, we implemented a monthly download
procedure. There were a lot of factors to consider with implementing this
download. Genstar wants one executable that kicks off
monthly in a batch mode, without any user intervention. We created an
executable within Visual Fox Pro. Our first step in this procedure was to
download to a CSV file from Visual Fox Pro 5.0, separated by commas and
delimited by a ‘|’.

For
the load, we opted not to use the Data Mart Builder for several reasons. First
of all, the Builder was having trouble processing the key lookup transform. The
client at Genstar had more familiarity with FoxPro
and had a number of transforms already built in a particular FoxPro
application. This ended up being our source for Staging Table data. The second
reason was because we were loading item descriptions from an item table that
had many items that contained a double quote,",
which was standing for inches, such as ï¿½" ST. The
builder required the file to be separated by commas, delimited with the ".
When it was separated with the double quotes, it was loading incorrectly, so we
had to use SQL*Loader for that particular file as well as the remainder of the
files, using the | as the delimiter. The files get loaded into temporary
tables, and then through scripts were added to the owner of the star schema.

Within
the executable, we kicked off a series of PL/SQL procedures that read from the
temporary tables and checked the live tables for duplicates. If a record
existed, the procedure updated the live tables, and deleted that record from
the temporary tables. If the record did not exist, it added the record to the
live table, and increased the synthetic key by one.

For
the main ticket table (fact table), we had a staging area table with both the
dimension table codes as well as fields for the synthetic keys. Since the keys
were not coming down from the flat files we were required to populate these
keys on the main table. This process was the Oracle Data Mart Builder key
lookup transform. We ran scripts that performed key lookups from the main
tables and inserted them into the temporary main ticket table that served as a
staging area. After the keys were all generated for each dimension, we inserted
the data into the live ticket table directly. The procedure then truncated and droped the summary tables and rebuilt them from the main
fact table. After the summary tables were built, the procedure exported the
data and automatically rebuilt the rollback segments that were fragmented by
the load.

At
this point, it is a good idea to size the tables for years of future growth.
This can be done earlier in the process, but with a completed download, it is
possible to see the amount of data coming down in a given month, and plan
accordingly. It is a good idea to rebuild the database according to how much
data that is being loaded. With Genstar, we gave
plenty of room for growth, because they had the room on their drives for it. Be
sure to resize indexes, tablespaces, and rollback
segments.

12.
Test/Train/Deploy/Monitor

At
this point, it is time to put the data mart to the test. Before releasing the
product to the users, fully test the entire system in order to discover any
hidden reporting errors or performance problems before rolling the system out
to users. It is best to have the people who are anxious to use the product be
the first selection for training. User education, input, and hands-on
experience are the best advertisements within the organization. Do not let the
users access the data prior to training, it can lead
to frustration as well as "bad press" if they are not familiar with
the tools. Once the users get hands on with the product and start to see some
positive results, word of mouth will spread on the product, and interest will
be generated. Monitor the product, as well as users reactions
as the product is rolled out. The most important thing to watch is the
download procedure and how it affects the system as far as space and
performance.

As
a final step at Genstar, we gave a training session
to some of the sales managers, and they were very excited about the product.
"Our users have never seen anything this responsive," said CIO
Claudia Serfass. "I think it will make a
dramatic change for them in the way they look at our business. They'll be able
to query the data quickly and easily, and the graphical tools will let them see
enterprise information any way they want. Real-time, interactive access will be
a big change in the way our company looks at our information."

Conclusion

The
Data Mart Suite is a low cost bundling of Oracle tools which when used properly
leads to a successful Data Mart roll-out. We found that, as in almost any early
release of a product, there were a number of workarounds required and issues
which needed resolution internally and with Oracle support with this particular
project. By the year 2000, the Data Mart Suite had certainly come of age and
does not require the workarounds needed when we worked on the Genstar project which was the first Data Mart Suite
deployment performed by any company inside or outside of Oracle. The Data Mart
Suite is a powerful design and build product suite which has great promise for
the future.

About Anissa Stevens

Anissa Stevens is the Director of Technical Consulting
Services and a Certified Oracle DBA. She has been with Avanco
for over 7 years and serves as the senior technical resource for Avanco. Anissa is lead consultant
and architect for all Oracle projects. She also provides high level DBA support
to customers. A 1989 graduate of Towson State University and active volunteer
for the Mid-Atlantic Association of Oracle Professionals (MAOP), she has served
NASA and many commercial clients as a DBA, Oracle software analyst, data
warehouse specialist and task leader, designing and developing large scale
database management and decision support systems. Anissa
has designed and implemented both small and large Data Warehouse systems for
commercial and government clients. Anissa has been a
technical reviewer for Oracle Press as well as contributed to the Oracle
Security Handbook and the Oracle 9iAS Application Portal Handbook both by
Oracle Press.

About Avanco

Avanco International, Inc. is a software consulting company
specializing in business intelligence, data warehousing, business process
improvement, relational data base design and Web-based Portal application
development. As an Oracle Premier Solution Partner, Avanco
has a staff of certified consultants to assist clients in developing solutions
that streamline business practices through innovation and automation. The Web
URL is www.avanco.com.

From the Editor: Some
Questions for Further Analysis and Discussion

What is the purpose of a
Data Mart? How does a Data Mart differ from a Data Warehouse?

Is this a data-driven
Decision Support System? Why or why not?

What is a fact table? What
is a star schema?

What technologies were used?

What is the major benefit of
using a data mart rather than a data warehouse for providing analytical
information to decision makers? Why?

What problems or difficulties
do you anticipate with the use of this type of decision support solution?

Please cite as:

Anissa Stevens,
"Implementing the Redland Genstar Data
Mart", July 2, 2004, at URL DSSResources.COM.

The
data mart project at Redland Genstar was completed in
1999. Anissa Stevens provided permission to use this
case study and screen shots at DSSResources.COM on June 11, 2004. Contact her
at email [email-masked]. This case study was posted at DSSResources.COM on
Friday, July 2, 2004.

This
case study is provided for informational purposes only. DSSResources.COM makes
no warranties, express or implied, about this case summary.