Helen Yelluas, Corporate Applications Manager
Coherent (http://www.cohr.com) is the recognized world leader in the design, manufacture and marketing of lasers and laser-based systems for medical, scientific and commercial applications.
The Applications Group underwent a comprehensive review of available data access technologies for Coherent's Compaq/Alpha - Oracle CODASYL DBMS (CA/MANMAN) databases. The technology selection criteria were: stability, accuracy, high performance and WEB enablement. The following the selection process, technology selection and implementation plan.
DATA ACCESS METHODS. The online analytical processing (OLAP) database is a system whose purpose is to provide to end-users general and statistical information about data. OLAP tools access this data to "tell a story" about business-related events. There is a direct correlation between the data "story" and these business-related events. Data is usually combined with data from various sources and when analyzed using OLAP tools, still-other business facts are uncovered. Not all databases are ideally suitable for use with OLAP tools, however. The MANMAN VAX/AXP application uses a non-relational transactional database in which to store data. The database, Oracle Codasyl DBMS, architecture is ideal for transactional updates but is extremely inefficient for reporting information. The transfer of MANMAN data to databases suitable for OLAP reporting can be done via direct access ODBC/OLEDB gateways, 4 G/L, 3 G/L data extractions or the eNVy Systems Warehouse Suite tools. Each of these transfer methods can be characterized as follows:
Direct access ODBC/OLEDB, 4 G/L and 3 G/L programs extract the data directly from the MANMAN database into relational tables. During the data extraction process, the Direct Access ODBC driver navigates the transactional database and returns a result-set (records) to the client PC. Depending on the number of records in the host database and the complexity of the query, data extraction can take from a few minutes to many hours to complete. Most Direct Access queries are in-efficient as all data must be processed for each extract. Using this method for data extraction, the user must also organize (de-normalize) the returned-data into data mart tables. The test: when moving sales, customer and product data to the warehouse it consistently took many hours to complete. The processing time limited our ability to update the data warehouse each day. I have found, the access methods and processing limitations used by ODBC/OLEDB, 4 G/L and 3 G/L technologies to be the same.
The eNVy Systems data warehouse is a pure relational database. It is highly normalized and structured, a requirement of OLAP applications. The process used for building and maintaining the data warehouse is: extract data once from the host database to a relational database, update any data incremental changes from the host database’s After Image Journal log file. Beyond the initial extraction, it takes only few minutes to update any data changes.
We decided on the eNVy Systems data warehouse solution. A data warehouse was built on a SUN/UNIX ORACLE 8 system for each DBMS/CA-MANMAN database. To use with OLAP tools with eNVy Systems data warehouse, very little data organization is required. There is no impact to host applications when updating the data warehouse from the CA-MANMAN host database and as such, the data warehouse is updated frequently throughout the day. Data warehouse tables are exposed to the users as raw data tables or data view/mart "fact" objects. There are no restrictions on the format and structure of the data warehouse fact objects.
THE STORY. The OLAP tool can uncover significant facts, each fact has a direct correlation to a business event. The OLAP tool enables the user to select any level of detail desired about the fact and permits navigation to adjacent data (business dimension) to uncover still-other facts. This can be illustrated using the 1980’s toy, the RUBIC Cube as an example. The six sided cube contains three vertical and three horizontal dimensions. Each side of the cube is a different color. By twisting a dimension, a different color pattern is displayed. We can apply this example to an OLAP-business model. A sales order may contain many dimensions e.g. products, customers, territories etc. To analyze sales, the OLAP tool may navigate any sales order dimension to uncover a fact about the sale. Facts from other dimensions might uncover other information about the sale, each dimension displays information from a different point of view. In the end, a story is told about the sale, which might include information about customers (who are they and where are they) and products (what and how many). USER LAYER. When exposing data warehouse tables to the end-user, it is wise to create a second "presentation" layer in the data warehouse. This layer is known as the user layer. It consists of pre-joined views and tables (data-mart objects). These objects expose the underlying data warehouse tables as fact objects. A collection of related fact objects are presented in the data warehouse as a data mart, e.g. the MANMAN/OMAR data mart might contain a sales order object, a product object, a customer object, a credit memo object and an invoice object among others. Each of these data mart objects contain common fields which make linking objects extremely easy. Data mart objects can be optimized for virtually any form of end-user/OLAP query.FRUSTRATION LAYER. My previous experience with an ODBC gateway and extraction-generated data warehouses was met with frustration and at times, disappointment. Data Extracts: Our previous data warehouse was generated solely via nightly extracts using a 4 G/L report writer. This data warehouse consisted of a selected group of MANMAN/OMAR records. It required over 4 hours to generate the extract and the extract had to execute as an exclusive job, locking out concurrent users. User reporting requirements changed frequently, thus requiring the data extract and operations procedures to change. These changes usually lengthened the extraction time and required some programmer intervention. Using a gateway to build the data warehouse also had its problems. Direct Access ODBC: The results generated by the gateway application contained data that was "triple" redundant. Due to data redundancy, relationships where not always clear. Some queries had orphaned records "child with no parent" or at times, did not transfer at all. Using the gateway, we could not trust the results and the data was consistently in-correct. Overall, these extraction methods where extremely painful for the end-user and the I.T. applications group alike.
WHY eNVy SYSTEMS WAREHOUSE SUITE: Given this experience, we opted to use a canned approach to data warehousing. We selected the eNVy Systems data warehouse tool-set. We have found the data warehouse to be easy to administer and accurate. Once created, we are able to implement our fact table data mart objects to additional MANMAN warehouse databases in just a few hours. We have plans to extend our fact-table object model to include data from transactional databases at all divisions (MANMAN and non-MANMAN) as well. The plan includes a single consolidated Oracle 8 data warehouse with data from all corporate and division transactional databases.
SOLUTION. Implementing the eNVy Systems data warehouse has simplified information access, decision support and it makes OLAP reporting easy for our users to do. We have found the eNVy-generated data objects to be re-usable and a good foundation on which other objects can be built. Although there are alternative ways to create a data warehouse for OLAP reporting. We are convinced that using any other method would have been too inflexible, limited and costly for us to administer. The eNVy Systems warehouse solution has proven to be reliable, cost effective and technically capable of meeting our diverse information needs.
Helen Yelluas holds a Masters Degree from the Polytechnical University of Leningrad, RUSSIA. Helen holds the position of Enterprise, Application Manager at Coherent. She holds a credential as a Certified Master ORACLE DBA and is an instructor of Basic SQL and Relational Database Concepts at WinTest School in Los Alto, CA. She can be reached at helen_yelluas@cohr.com or by phone at (408) 764-4317.