# Presentation: 480517

## Data Warehousing Information2/24/2000 TEAS

- Walt Moleski/588
- 301-286-7633
- Walt.Moleski@gsfc.nasa.gov

## Agenda

- Introduction
- Choosing the Right Data Warehousing Tools
- Instructor: Pieter R. Mimno/Independent Consultant (pmimno@cove.com)
- A Real World Approach: Building a Warehouse with Microsoft Technologies
- Instructor: Diogenes Torres/Knowledge Management Solutions
- Conference Day Presentations
- Conclusion

## Introduction

- Objective
- Provide you with general information about Data Warehousing.
- Where did this information come from?
- Attended The Data Warehousing Institute’s Fifth Annual Leadership Conference in Orlando, FL  11/15-19.
- Why did I attend this conference?
- Intelligent Information Infrastructure (I3) task funded by the Cross-Enterprise Technology Development Program (CETDP) Next-Generation Infrastructure Systems (NGIS) thrust area.

## Choosing the Right Data Warehousing Tools

- Goals
- Data Warehousing Strategies, Architectures, and Components
- Data Cleansing, Extraction, Transformation, and Load (ETL) Tools
- Database Selection
- Data Access & Analysis Tools
- Data Mining & Visualization Tools
- Future Trends in Data Warehousing

## Goals

- Assist you in selecting the appropriate architectures, strategies, and tools for your data warehouse.
- Help you avoid making common mistakes.
- Provide you with objective information on specific data warehousing tools.
- Provide an overview of tools & trends in the industry.

## Definitions

- Replication
- Copy data from source databases, without resolving logical inconsistencies in source data
- Extraction
- Copy data from multiple sources, with resolution of data quality and consistency issues
- Transformation
- Data refinement through aggregation, summarization, partitioning, and reorganization to make it usable
- Metadata
- Source definitions, data models, calculations, transformation, and conversion rules

**Notes:**
Central metadata repository is the heart of the data warehouse. Used as a “single version of the truth”.

## Definitions (continued)

- Aggregation
- Accumulation of related data into a single pre-joined table containing multiple dimensions
- Summarization
- Consolidated data summarized on one or more dimensions, or axes
- Granularity
- Degree of summarization of data. One of the most important issues of the DW design
- Individual transactions (atomic data or detail data)
- Lightly summarized and highly summarized data
- Partitioning
- Splitting of target data into smaller units

**Notes:**
Central metadata repository is the heart of the data warehouse. Used as a “single version of the truth”.

## Mistakes to Avoid inBuilding Data Warehouses

- Failure to define business drivers
- Use of the wrong architecture
- Development of “virtual” data warehouses
- Development of “stovepipe” data marts that are not integrated across business areas
- Populating the warehouse with “dirty data”
- Implementing the DW as a large, top-down development effort
- Failure to anticipate scalability & performance issues

**Notes:**
Key & Most common mistake. Need to involve end users as early & as much as possible.
The data warehouse fails and must be rebuilt.
 “Virtual” data warehouses do not incorporate a target database.
“Stovepipe” data marts do not integrate with a central metadata repository. They do not support shared definitions of data entries, business rules, and semantics across the organization.
“Dirty” source data often contains more than 10% missing, inconsistent, or erroneous data. Dirty source data must be cleansed prior to loading it into the data warehouse.
Top-down is the correct way to build a data warehouse. Bottom-up can be used if you are very careful by using metadata & data modeling tools.
The Challenge of Data warehousing efforts is to show a Return on Investment in 90 days.

## Architectures for Data Warehousing 

- Human Resources

- Virtual Data Warehouse – Direct Access to Operational Data

**Source**
**Databases**

- Retail Data

- Financial Data

- Legacy Systems

- Text

- External

**End-User**
**DW Tool**

**No Target**
**Database**

## Virtual Data Warehouse

- Benefits
- Preserves investment in operational system hardware, software, packages, databases, and skill sets.
- Issues
- Users given direct access to source databases
- Data is not moved from source databases
- No target database -> No data warehouse
- Provides single, unified front end to multiple hosts
- Hides the complexity of host systems
- Risks
- Not a data warehouse
- No historical data
- Queries may disrupt operational systems

## Architectures for Data Warehousing 

- Packaged Data Mart (Data Mart in a Box)

- Human Resources

**Source**
**Databases**

- Retail Data

- Financial Data

- Legacy Systems

- Text

- External

**Sales**
**Data Mart**

- RDBMS

**Target**
**Database**

- Local
- Metadata

## Packaged Data Mart

- Benefits
- Packaged product sold by many vendors integrating source data replication, target database, and end-user tool
- Issues
- Data is replicated from source database, stored in target database, and accessed by end-users
- Local metadata is stored in the target database
- Risks
- Stovepipe data mart populated with dirty data
- No data cleansing
- No integration at metadata level with tools from other vendors
- Does not support common business rules, semantics, and definitions across business areas

## Architectures for Data Warehousing 

- Multiple Packaged Data Marts

- Human Resources

**Source**
**Databases**

- Retail Data

- Financial Data

- Legacy Systems

- Text

- External

**Data Access**
**And Analysis**

**Data**
**Marts**

**Sales**

**Financial**

**Human**
**Resources**

- Local
- Metadata

- RDBMS

- MDB

- RDBMS

## Multiple Packaged Data Marts

- Benefits
- Addresses short-term demands of individual business units
- Issues
- Multiple data replication processes access source data
- Data marts are not integrated
- No single, clean, consistent source of information
- Multiple, inconsistent views of source data
- Risks
- Chaotic environment, difficult to manage & maintain
- Stovepipe data marts populated with dirty data
- Source data is replicated at different times, leading to inconsistent reports across data marts
- Multiple, inconsistent local metadata repositories

## Architectures for Data Warehousing 

- ETL Tool with Stovepipe Data Marts

- Legacy

**Source**
**Databases**

- Relational

- Appl. Package

- External

**Data Access**
**And Analysis**

**Independent**
**Data Marts**

**Sales**

**Financial**

**Human**
**Resources**

- Local
- Metadata

- RDBMS

- MDB

- RDBMS

- Data
- Cleansing
- Tool

- Warehouse
- Admin. Tools

- Extract,
- Transform
- And Load

- Central
- Metadata

- Data
- Modeling
- Tool

**Data Extraction,**
**Transformation, Load**

**Notes:**
Goal -> Not to write a single line of procedural code.
Minimum ETL engine = 4 CPUs and 4 GB RAM.
ETL tools are proprietary. Thus, so are the central metadata repositories.

## ETL with Stovepipe Data Marts

- Benefits
- Solves the dirty data problem
- Central metadata is generated
- Administration & data modeling tools are available
- Issues
- This architecture is a stovepipe data mart
- Local metadata is not integrated with central metadata
- Risks
- No common business rules, semantics, or data definition
- Leads to implementation of non-integrated, non-architected data marts. Could cause DW to fail.

## Architectures for Data Warehousing 

- ETL Tool with Architected Data Mart

- Legacy

**Source**
**Databases**

- Relational

- Appl. Package

- External

**Data Access**
**And Analysis**

**Architected**
**Data Marts**

- Local
- Metadata

- RDBMS

- Data
- Cleansing
- Tool

- Warehouse
- Admin. Tools

- Extract,
- Transform
- And Load

- Central
- Metadata

- Data
- Modeling
- Tool

**Data Extraction,**
**Transformation, Load**

- End-user
- Tools

## ETL with Architected Data Marts

- Benefits
- Solves integration problem – architected data marts share common business rules, semantics, and data definitions
- Integration is at the central metadata repository level
- All components of the DW are driven by the central metadata. Data marts all plug into a common framework.
*Your first pilot project should be an architected data mart!*
- Issues
- IT organization must enforce the use of common architectural standard for development of all data marts.
- “Best-of-Breed” components from multiple vendors can be selected, if components integrate with central metadata.
- Risks
- More complex environment
- Presupposes existence of central metadata repository
- Few vendors support metadata exchange architecture.

**Notes:**
Pick ETL Tool and then pick end-user tool that can read ETL tool’s metadata.

## Architectures for Data Warehousing 

- Enterprise Data Warehousing Architecture

- Legacy

**Source**
**Databases**

- Relational

- Appl. Package

- External

**Data Access**
**And Analysis**

**Architected**
**Data Marts**

- Local
- Metadata

- Data
- Cleansing
- Tool

- Warehouse
- Admin. Tools

- Extract,
- Transform
- And Load

- Central
- Metadata

- Data
- Modeling
- Tool

**Data Extraction,**
**Transformation, Load**

- Mid-
- Tier

- Mid-
- Tier

- MDB

- Central
- Data
- Warehouse

- Data
- Mart

- Data
- Mart

- Local
- Metadata

- Local
- Metadata

- Metadata
- Exchange

## ETL with Architected Data Marts

- Benefits
- Allows flexibility, modularity and scalability
- Provides a “single version of the truth” via central metadata
- Consolidates detail data in central data warehouse and stores aggregated data in individual data marts
- ETL tool loads the central DW, multiple data marts, and computes aggregates in a single pass of the source data
- Issues
- Large central data warehouse driving multiple data marts
- Central data warehouse stores detail data. Supports requirement to access central store of atomic-level data across business areas
- Multiple architected data marts
- Risks
- Central coordination and management is required, based on access to central metadata repository
- Complex environment, higher development cost and risk

**Notes:**
Pick ETL Tool and then pick end-user tool that can read ETL tool’s metadata.

## Architectures for Data Warehousing 

- Distributed Data Warehousing Architecture

**Source**
**Databases**

**Data Access**
**And Analysis**

- Data
- Mart

**Independent**
**Data Marts**

- Mid-
- Tier

- ETL
- Tool

- Division 1

- ETL
- Metadata

**Multiple**
**ETL Tools**

- Central
- Data
- Warehouse

- Local Metadata

- ETL
- Tool

- Division 2

- ETL
- Metadata

- ETL
- Tool

- Division 3

- ETL
- Metadata

- Data
- Mart

- Local
- Metadata

- Data
- Mart

- Global Metadata

- Mid-
- Tier

- Mid-
- Tier

- Mid-
- Tier

## Distributed Data Warehousing Architecture

- Benefits
- Supports multiple, geographically distributed business divisions
- Permits each business division to have its own ETL tool and multiple data marts
- The central data warehouse stores corporate-wide data
- All components are synchronized across the distributed DW environment using a global metadata repository
- Issues
- Most ETL tools do not support a geographically distributed ETL environment with a global metadata repository
- Expensive, complex ETL product
- Risks
- Central coordination and management is required, based on access to a global metadata repository
- Complex environment, higher development cost and risk

**Notes:**
Point out the Issues

## Proof of Concept

- Document Organization
- Section 1 – Describe the data sources (10-12 tables)
- Section 2 – Describe the target data model on paper (3-4 dimensions)
- Section 3 – Describe source to target mappings and transformations
- Section 4 – Describe how to move the data
- Section 5 – Specification of at least 3 aggregates
- Section 6 – Specification of representative reports to be generated by the end-user tool
- How does this help you?
- Shows the vendor that you are serious about building a DW
- Allows you to believe the ability of the ETL tool to use _*your*_ data
- Lets you learn a great deal about the ETL & end-user tools

**Notes:**
Be very skeptical of vendor claims
Give the document to vendor -> most times they will provide the proof of concept free of charge

## Proof of Concept (continued)

- Phase 1
- Tests the ability of the ETL too to access one or more data sources and populate a single target database.
- Architecture consists of multiple data sources, an ETL tool, a data modeling tool, and a target database.
- Phase 2
- Tests the ability of end-user tool to access the star schemas generated in Phase 1 and produce the specified user reports.

**Notes:**
Phase 1 functions tested:
	ETL tool to extract from multiple source databases, using native interfaces, flat-file interfaces, and an ftp interface
	Implement specified data cleansing & data transformation rules
	Populate the relational target database with both detailed transaction data and aggregated data
	Generate, manage, and maintain a central metadata repository.
	Support an integrated interface to external data modeling tools used to define the logical & physical organization of data in the target data mart

Phase 2 functions tested:
	Specification of reports to be generated by the end-user tool.
	Generation of specified reports based on direct access to the summarized and aggregated data.
	Intuitive user interface. The end-user tool will be evaluated on its ability to support an interface that enables end users to initiate queries to data in the data warehouse, analyze the data, and generate reports.
	Metadata integration. The end-user tool will be evaluated on its ability to integrate with central metadata generated by the ETL tool.

## Accessing Data WarehousesVia the Internet

- Internet enables users in a distributed organization to access information, at low cost, anywhere in the world
- Web-enabled data warehousing tools extend analytical processing capability to a large number of users worldwide
- Web-enabled reporting cuts cost of document distribution
- Server-based applications greatly reduce software distribution, training, and technical support requirements
- Thin clients and dynamically downloadable applets eliminate hardware dependence and need for software on the client (fat client).

## Phases in the Evolution ofWeb-Enabled Products

**Phase 1:** Static Report Distribution (2-tier, thin-client architecture)
- DW tool stores static reports in HTML format on web server
- Reports are viewed with a standard thin-client browser
**Phase 2:** Static Results Set (4-tier, thin-client architecture)
- Users interact with HTML form, submits query, and receives a static HTML result set.
**Phase 3:** Dynamic Publishing (4-tier, thin- or fat-client)
- Dynamic download of Java applets, ActiveX components, or plug-ins on demand. Supports dynamic interaction with user
- Vendors of web-enabled data warehousing products should be at Phase 3.

## Recommended ImplementationTechniques

- Define structure of enterprise DW and Architected data marts
- Build data warehouse incrementally, one business area at a time
- Buy only components that integrate with central metadata repository
- Ensure that data warehouses are not populated with dirty data
- Support mix of RDBMS, MDB, and hybrid target databases
- Provide easy-to-use tools that don’t require IS assistance
- Ensure tools provide the same functions on a LAN and on the Web
- Use the Web to reduce cost of communications, HW, SW, support
- Support mobile users with off-line query, reporting, OLAP functions
- Ensure system is scalable to increases in users and database size
- Provide powerful security and warehouse administration functions

## Data Cleansing Process

- Identify authoritative data sources
- Measure data quality
- Use business rule discovery tools to identify data with inconsistent, missing, incomplete, duplicative, or incorrect values
- Use data cleansing tools to clean data at the source
- Load only clean data into the data warehouse
- Identify and correct the cause of data defects
- Schedule periodic cleansing of the source data

**Notes:**
Most data cleansing tools are poorly integrated with data warehousing tools.
Organizations generally do not purchase separate data cleansing tools -> Use ETL tools to perform limited data cleansing
Future -> Data cleansing tools will be increasingly integrated with ETL tools

## Examples of Data Cleansing Tools

- Integrity Data Reengineering Tool from Vality Technology, Inc.
- Most powerful & expensive tool ($300K)
- Trillium Software System from Harte-Hanks Data Technologies
- Integrated with Informatica & soon with Ardent  ($150K)
- Enterprise/Integrator from Carleton Corporation
- Centrus Desktop from Qualitative Marketing Software, Inc
- Innovative Directory, Match, Analyzer from Innovative Systems
- NADIS from Group 1 Software, Inc.
- Migration Architect from DB Star
- id Centric from Postalsoft, Inc.
- dfPower Series from DataFlux Corporation
- Online tool requiring manual interaction
- WizRule from WizSoft, Inc.

## Data Extraction, Transformation & Load (ETL) Tools

- Support data extraction, cleansing, aggregation, reorganization, transformation, and load operations:
- Access data from multiple operational data sources
- Re-map source data into a common format
- Standardize data to enable load to conformed target databases
- Validate data to check content & range of field values
- Filter data, convert codes, calculate derived values, aggregates
- Load cleansed data to the target data mart or central DW
- Produce audit and operational reports for each data load
- Centralized generation and storage of metadata
- Automatic generation of data extract programs

**Notes:**
ETL Tool is the hub of the hub-and-spoke architecture not the DW.
An important function of these tools is to generate & maintain centralized metadata.
Strongly recommends use of commercially available off-the-shelf ETL tools.

## First-Generation ETL Tools

- Extract program is generated automatically as source code
- Source code is compiled, scheduled, and run in batch mode
- Extraction/Transformation/load process runs on server or host
- Data is extracted from source files and processed on server or host
- Data written to intermediate files throughout transformation process
- Program is single threaded and cannot utilize parallel processors
- GUI interface is used to define extraction/transformation process
- Detailed transformations may require coding in COBOL or C
- Often, less metadata is generated automatically by first-generation ETL tools than second-generation ETL tools

## Strengths & Limitations

- Strengths
- Tools are mature (many are over 20 yrs old)
- Tools are good at extracting data from old legacy sources
- Limitations
- High cost of products and complex training requirements
- Extract programs must be compiled from source code
- Single-threaded applications do not use parallel processors
- Use of intermediate files limits throughput
- Requirement to manage code, files, programs, JCL, scripts
- Many transformations must be coded by hand
- Significant amount of metadata must be manually generated
- Have technology limitations with data warehousing

## Examples of First-Generation ETL Tools

- Prism Executive Suite – Ardent Software, Inc.
- Will not be around for much longer
- Passport - Carleton Corporation
- Withdrawn from the market
- ETI-EXTRACT Tool Suite – Evolutionary Technologies International
- High cost, complex product
- SAS/Warehouse Administrator – SAS Institute, Inc.
- Nearly complete end-to-end data warehousing product
- Does not support “best-of-breed” approach.

## Second-Generation ETL Tools

- Extract program is generated automatically as executable code
- Extraction/transformation/load process runs on a server platform
- Data is extracted directly from source files and processed on server
- Data is transformed in memory & written directly to target database
- Functions supported include monitoring, scheduling, extraction, scrubbing, transformation, bulk load, index, aggregation, metadata
- In-memory processing, multithreading, data streaming, and on-the-fly calculation of aggregates make efficient use of parallel CPUs
- High throughput because data is not written to intermediate files
- Higher percentage of metadata is generated automatically.

## Strengths & Limitations

- Strengths
- Lower cost suites, platforms, and environments
- Engine-driven technology is fast, efficient, and multithreaded
- Products are easy to learn and require little training
- ETL functions are highly integrated and automated
- Population and maintenance of open, extensible metadata
- Designed specifically for data warehousing applications
- Limitations
- Many tools are not mature (4 1/2 years old)
- Some ETL tools are limited to RDBMS source databases
- Many recent ETL tools do not support integration at the metadata level with end-user tools

**Notes:**
3rd strength could be very deceiving – may not be true
Do as much simulation of performance in Proof-of-Concept
Buy about 5-10 hours of consulting to figure out how to use the tool effectively
Don’t believe vendor claims about performance – No benchmarks exist

## Examples of Second-Generation ETL Tools

- Powermart 4.5 – Informatica Corporation
- Pioneer due to market share
- Ardent DataStage – Ardent Software, Inc.
- General-purpose tool oriented to data marts
- Sagent Data Mart Solution 3.0 – Sagent Technology
- Progressively integrated with Microsoft
- Ab Initio 2.2 – Ab Initio Software
- A kit of tools that can be used to build applications
- Tapestry 2.1 – D2K, Inc
- End-to-end data warehousing solution from a single vendor

## What to look for in ETL tools

- Use optional data cleansing tool to clean-up source data
- Use extraction/transformation/load tool to retrieve, cleanse, transform, summarize, aggregate, and load data
- Use modern, engine-driven technology for fast, parallel operation
- Goal: define 100% of the transform rule with point and click interface
- Support development of logical and physical data models
- Generate and manage central metadata repository
- Open metadata exchange architecture to integrate central metadata with local metadata.
- Support metadata standards
- Provide end users access to metadata in business terms

**Notes:**
Recommendation:
	Look at 2nd generation tools first. If not satisfied, look at 1st generation tools.

## Target Database Selectionfor a Data Warehouse

- Conventional RDBMSs
- May be used to support most data warehousing requirements, and can handle very large target databases.
- The central data warehouse is almost always a conventional RDBMS.
- Multidimensional databases (MDBs)
- Provide faster response to analytical queries and OLAP computations than RDBMSs, but they have size limitations.
- Hybrid databases
- Combines relational & multidimensional technologies into a single database. Relational technology is used to store large database segments; multidimensional technology is used to provide fast response to analytical queries.
- Likely to be widely used in the future.

## Strengths of Conventional RDBMSs

- No size limit on target database.
- Standard corporate database can be used for building the data warehouse. No need for proprietary databases & tools.
- RDBMSs have been enhanced to support the specialized requirements of data warehouses.
- Ad hoc queries can be directed to any data in the warehouse.
- Detailed data and aggregated data can be stored in the same database.
- High-speed query processing using symmetric multiprocessors (SMP), massively parallel processors (MPP), and clustered multiprocessors.

**Notes:**
Additional strengths: in book pg 110.

## Limitations of Conventional RDBMSs

- Originally optimized to support on-line transaction processing (OLTP)
- Typically slower that MDB for OLAP calculations & ad hoc analysis of data in multiple dimensions
- SQL was not designed for calculations and OLAP functions:
- SQL cannot calculate variances between rows, or perform multi-level hierarchical roll-ups
- SQL cannot perform ratios, comparisons, what-if functions, rankings, consolidations, or cross dimensional calculations
- Sophisticated techniques are required to generate SQL, process query, and perform aggregation and calculations in order to overcome SQL limitations (e.g., multi-pass SQL, temp tables)
- Read-only. Does not support read/write operations to avoid the problem of having to recompute aggregates if data in the target database changes

## Examples of RDBMSs

- Oracle 7.3, 8.x Parallel Server
- Popular choice due to its ubiquity & wide range of specialized functions supporting data warehousing.
- Not optimized for running on MPPs.
- IBM DB2 Universal Database for MVS, AS/400, AIX, NT, OS/2, HP/UX, Sun Solaris, SCO. DB2 Parallel Edition runs on IBM SP2
- Matches most of Oracle features and provides good support for data warehousing.
- Parallel Edition is optimized for running on MPPs.
- Informix Dynamic Server, Extended Parallel Option (was XPS), Universal Data Option (was Universal Server)
- Well suited for high-end data warehousing.
- Teradata from NCR – runs on NCR WorldMark 5100M (MPP)
- Well suited for high-end data warehousing.
- Does not integrate with anything.

## Widely used RDBMSs (continued)

- Sybase Adaptive Server Enterprise, Adaptive Server IQ
- Microsoft SQL Server 6.5, 7.0 (SQL Server OLAP Services)
- Incorporates data warehousing components & hybrid database technology at no extra cost.
- Not as scaleable as Oracle on UNIX by a factor of 4-1.
- Likely to sweep the market for small databases due to its exceptional price/performance ratio and hybrid OLAP capability
- RedBrick Warehouse from Informix Software
- Excellent technology.
- Regulated to data marts (great for retail applications).
- Will likely be squashed by Microsoft.

## When should you use a Relational Target Database?

- Requirement to support ad hoc (unpredictable) queries to any portion of a very large target database
- Constraint to use only standard corporate RDBMS
- Support for dynamic, not precomputed, query environment
- Requirement to store both detailed and summary data in the same physical database
- Support for standards, including SQL, ODBC, OLE DB
- Portability among many platforms
- Compatibility with large number of 3rd party vendors

## Strengths of MultidimensionalTarget Databases

- High performance, sophisticated multidimensional calculations
- Optimized for OLAP. Not constrained by limitations of SQL
- Appropriate for many DSS applications such as:
- Multi-user read/write applications, e.g., budget analysis
- Support of complex, cross-dimensional calculations
- Drill-down for iterative queries, trend analysis, what-if analysis
- Features supporting high performance for DSS applications:
- High-speed multidimensional analysis & calculations, e.g., aggregations, matrix calculations, cross dimensional calculations, OLAP-aware functions, procedural calculations
- Proprietary, extended features, such as row-level or matrix calculations, that are not supported by SQL

**Notes:**
MDB -> 3 or more dimensions
Store data in multidimensional arrays
Components of a MDB:
Relational DBMS used to store detail data
MDB used to store aggregated data
Metadata
RDBMS & MDB have separate, local metadata repositories
Multidimensional viewer (end-user access tool)

Microsoft is bundling this technology with Office 2000 -> will make technology ubiquitous

## Limitations of MultidimensionalTarget Databases

- Requires yet another proprietary database solution, which increases cost of training & support
- Maximum size of about 3 GB of raw data, which may expand by a factor of 10 to 100 for calculated data.
- Limit due to the time required to pre-compute large multidimensional arrays that hold the aggregates
- Most MDBs cannot load data incrementally
- Some MDBs require pre-computation of all data. No on-the-fly calculations. May result in data explosion for sparse data
- Static, precomputed dimensions and calculations. Lack of support for dynamic changes in dimensional structure
- Performance degradation if database size increases over 30 GB
- Lack of standard multidimensional model or access method
- Most MDBs do not integrate seamlessly with RDBMSs.

## Examples of Multidimensional Databases

- Oracle Express OLAP from Oracle Corp.
- Very old (27 yrs), complex product that has been greatly improved by Oracle and is integrated with Oracle 8.
- Relatively steep learning curve.
- Essbase from Hyperion Solutions Corp.
- New product specifically designed for data warehousing applications
- Exceptionally easy to use and incorporates many advanced features
- TM1 from APPLIX, Inc.
- Excellent MDB that performs calculations on-the-fly, but has limited scalability since it is RAM-based (all active cubes must fit into RAM)
- Commander Decision from ComShare
- Holos from Seagate Software, Inc.
- SAS Multidimensional Database Server (MDDB)
- GentiaDB from Gentia Software
- Previous 3 are application development tools that are expensive, difficult to use, and require knowledge of a proprietary fourth-generation language associated with each product.
- Violate a basic objective of data warehousing -> Buy, not build DSS tools.

**Notes:**
Oracle & Essbase are the only 2 that have significant market share.

## When should you use a Multidimensional Target Database?

- Sub second response to queries and OLAP analysis
- Moderate size target database (< 100 GB)
- Multi-user read/write budgeting, financial reporting, financial consolidation, etc.
- Complex cross-dimensional OLAP calculations, variances, and what-if calculations not supported by SQL
- Requirements for intuitive operation, short training period, minimal database administration
- Efficient database storage – less space than RDBMS
- Fast browsing through members of dimensions

## Hybrid Databases

- Goal is to make the best use of both Relational and Multidimensional technology
- RDBMS used for detailed data stored in large databases
- MDB used for fast, read/write OLAP analysis and calculations
- Microsoft SQL Server OLAP Services is the only true hybrid database product on the market (Plato - 1/99)

## When should you use a Hybrid Target database?

- Requirement to support a wide range of applications:
- Ad hoc queries to large target databases
- Rapid response for complex, read/write OLAP analysis and calculations, i.e., financial budgeting
- Seamless integration of data in multidimensional and relational form
- Support for multiple, independent physical cubes, each with multiple data partitions, stored as ROLAP, MOLAP, or hybrid data structures

## Recommendations on Target Databases for Data Warehouses

- Use RDBMS, MDB, and hybrid databases to meet the specialized requirements of groups of end users
- Use RDBMSs and ROLAP tools to provide multidimensional views of large relational target databases. Use RDBMS features, such as parallelization, and bit-mapped indexes, to provide acceptable performance
- Use MDB for high-performance analysis of moderate size databases. MDBs are often used for data marts
- Use hybrid approach for applications that require access to detail data and fast OLAP computations

**Notes:**
Products using this technology should be utilized as soon as they become available

## What to look for inData Access and Analysis Tools

- Wide choice in the selection of data access tools
- High performance & scalability to large numbers of users
- Ease of use – intuitive, consistent interface to all functions of tool
- Short learning curve – less than 1 hour for desktop OLAP tools
- Specification of queries and reports without help from IS
- Semantic layer that maps business terms to database schema
- Support for query/reporting, and OLAP analysis in same tool
- Dynamic OLAP analysis capability over the Web
- Support for mobile computing, including off-line OLAP analysis
- Wide range of price points (high-cost products for developers, moderate cost for LAN users, low-cost for Web-enabled products)

## What to look for in Data Access and Analysis Tools (continued)

- Aggregate navigation – ability to access pre-computed aggregates
- Rapid slicing across dimensions and attributes
- Support for ad hoc queries to any data in the data warehouse
- Support for complex OLAP calculations, and calculations on-the-fly
- Access to many data sources (RDBMSs, spreadsheets, ERP, etc.)
- Support of standards (GUI, SQL, ODBC, OLE, etc.)
- Tight integration with central metadata repository
- Limitation of many data access & analysis tools
- Hybrid products that utilize the best features of both relational and multidimensional technologies
- Support “drill anywhere” and “drill through” to detailed data.

## Categories of Data Access & Analysis Tools

- Desktop OLAP tools
- Recommended for the majority of end users that require simple query, reporting, and drill-down functions
- Relational OLAP (ROLAP) tools
- Appropriate to support the more complex and demanding requirements of power end users.
- Multidimensional OLAP (MOLAP) tools
- Recommended to support financial analysts who perform budgeting, statistical analysis, and complex multidimensional calculations
- Hybrid OLAP (HOLAP) tools
- Tools that contain the best from ROLAP & MOLAP technologies and combine these into a single tool.
- Data Mining & Data Visualization tools
- Used to detect patterns or trends in the data.

**Notes:**
There is no single end-user tool that can meet the needs of all categories of end-users.

## Desktop OLAP Tools

- Tools extract subsets of relational data and store them in multidimensional structures
- Hypercube/cache may be stored on PC or local server
- Data in hypercube is static – queries are limited to precomputed dimensions and summary data
- Representative tools:
- Brio enterprise – Brio Technology, Inc.
- BusinessObjects – Business Objects, Inc.
- Impromptu/PowerPlay – Cognos
- Seagate Info – Seagate Software
- Hummingbird BI/Suite – Hummingbird Communications Ltd.

## What to look for in anIdeal Desktop OLAP Tool

- Integrated query, reporting, and OLAP functionality in one product
- Ease of use and intuitive drag-and-drop interface for all functions
- Short learning curve. Less than 1 hour for business users
- Automatic aggregate navigation, independent of the database
- Ad hoc queries to very large RDBMS databases
- Rapid response to ad hoc queries, reports, and OLAP functions
- Scalability to support large results sets
- Generation of MDX language to perform OLAP calculations
- Dynamic microcubes created on-the-fly on the client for each query. Supports off-line analysis and mobile users
- OLAP engine and persistent MDB cache on the mid-tier server
- Flexible report generation using intuitive report outline

**Notes:**
Desktop OLAP tools should be low cost, exceptionally easy to learn, and support an intuitive user interface.
In instructor’s experience, Brio Enterprise is the easiest desktop OLAP tool to learn, BusinessObjects is second, and PowerPlay is third.

## What to look for in an Ideal Desktop OLAP Tool (continued)

- Integrate data from RDBMS and MDB sources in a single report
- No assistance from IS personnel to perform queries and reports
- Report-level caching on mid-tier server
- “Pull”-mode report creation/distribution via Web, email, FTP, files
- “Push”-mode personal information broadcasting
- Read/write access to database
- Local metadata or semantic layer to define custom interface
- Administrative tool and multi-level, repository-based security
- Integration with ROLAP tools, MOLAP tools, and ERP packages
- Application building facilities for custom screens, workflow, etc.
- Efficient generation of SQL, including multi-pass SQL
- Open metadata exchange architecture for metadata integration
- Support for industry metadata standard – OLE DB for OLAP, OIM

**Notes:**
Desktop OLAP tools:
Must be scalable to support the demands of an increased number of end-users, much larger target dbs, and more complex queries
have only limited scalability on the LAN due to their reliance on performing all calculations on the desktop

One of the most important functions that must be supported is integration with central metadata via an open metadata exchange architecture.
Unfortunately, this is poorly supported by most tools
Consistent access to a central metadata repository is required for a “best-of-breed” approach

## High-end ROLAP Tools

- Three-tiered hardware/software architecture:
- GUI on client; multidimensional processing on mid-tier server; target database on database server
- Processing split between mid-tier & database servers
- Ad hoc queries to any portion of very large databases
- Representative tools:
- DSS Agent, DSS Server – MicroStrategy, Inc.
- MyEureka! ROLAP – Information Advantage
- MetaCube – Informix Software
- InfoBeacon – Platinum Technology

## What to look for in anIdeal ROLAP Tool

- Ease of use, intuitive interface, rapid response to ad hoc queries
- Competitive price curve, including low-cost per user for the Web
- Scalable architecture. Support for large numbers of concurrent end users and scalable to Terabyte-size databases
- Efficient distribution of functions across a three-tier architecture
- OLAP calculation engine and persistent, multidimensional cache on mid-tier server. Local data cache on client
- Automatic determination by server where processing should be performed – on OLAP engine, and/or on the RDBMS
- Support for business intelligence portals as the front-end interface
- Fully interactive OLAP capability over the Web via dynamically downloadable components

## What to look for in anIdeal ROLAP Tool (continued)

- Generation of SQL tuned to the functionality of specific RDBMSs
- Support for high-speed, on-the-fly OLAP calculations, aggregate optimization, and join optimization on the mid-tier server, independent of the limitations of SQL
- Avoidance of creation of temp tables on the database server
- Integration with desktop OLAP tools to support mobile users
- Integration with central metadata
- Utilization of intelligent agents running continuously in background to generate alerts on the occurrence of specific events
- Multi-level security at the repository, database, object, table, field, and value level

**Notes:**
ROLAP tools are beginning to merge with Hybrid OLAP tools.
Most current ROLAP tools lack a persistent, multidimensional cache on the mid-tier server that could be used to implement a HOLAP capability

## Multidimensional OLAP (MOLAP) Tools

- Used by business professionals to drill down and analyze summarized data to uncover market trends, support decision making, and management reporting
- Applications include financial reporting and consolidation, business profit reporting, enterprise budgeting, and management reporting

## What to look for in an Ideal MOLAP Tool

- Ease of use, intuitive interface, rapid response to ad hoc queries
- Competitive price curve, including low-cost per user on the Web
- Scalable architecture supporting large numbers of end users
- Efficient distribution of functions across a three-tier architecture
- OLAP calculation engine based on MDX, and persistent, multi-dimensional cache on mid-tier server, Local data cache on client
- Support for both broadcasting and exception reporting
- Multi-user, concurrent write capability
- Ability to load data from a wide variety of sources
- Close integration with RDBMSs to support hybrid architecture
- Handle sparse and dense data without data explosion

**Notes:**
MOLAP tools designed for “power” users who need rapid response to complex queries that perform extensive multidimensional calculations

## What to look for in an Ideal MOLAP Tool (continued)

- Support for on-the-fly calculations and efficient data storage
- Complex cross-dimensional calculations not supported by SQL
- Implementation of dimensional hierarchies
- Support for databases greater than 100 GB of calculated data
- Minimal loss of speed for databases grater than 30 GB
- Transparent drill-down to detail data; drill-through to source data
- Integration with spreadsheets as front-end tools
- Dynamic OLAP capability over the Web
- Integration with central metadata repositories
- Support for metadata exchange using OLE DB for OLAP API
- Good performance with OLAP Council APB-2 benchmark

## Data Mining Applications

- Improve knowledge of customers
- Profile and segment customer groups
- Analyze customer buying patterns
- Predict buying patterns
- Predict what customers will be a bad credit risk
- Target customers for sales promotions
- Forecast demands on inventory
- Manage inventories across geographical locations

## Data Visualization Applications

- Help users visualize, understand, and display complex multidimensional data
- Gives users a practical way to gain an understanding of the meaning of the data

## Recommendations on Selection of Data Access & Analysis Tools

- Use mix of tools to support multiple types of users:
- Desktop OLAP tools (support 70-80% of users)
- High-end ROLAP tools (support 10% of users)
- MOLAP Tools (support 5-10% of users)
- Data Mining & Data Visualization tools (support 5% of users)
- Start with simple desktop OLAP tools. Gradually add more complex ROLAP, MOLAP, & data mining tools

## Future Trends

- Rapid proliferation of analytical applications and data marts
- Increasing use of ETL tools as the hub of hub-and-spoke architecture
- Dominance of Oracle, IBM, and Microsoft in target databases
- Dominance of MS SQL Server 7 to build data marts and analytical applications due to low cost, integration of bundled DSS components from one vendor, extended SQL for OLAP, multidimensional functionality using MDX language, and parallelism
- Widespread acceptance of Microsoft standards (MS Repository, OIM metamodel, OLE DB for OLAP API)
- Integration of DSS system components with central metadata
- Widespread use of Internet/Intranet/Extranet to distribute reports