Visual Warehouse: Server and Agent

Visual Warehouse is an integrated product for building and maintaining a data warehouse or data mart in a LAN environment. Visual Warehouse does not simply create a data warehouse or an informational database; it provides the processes to define, build, manage, monitor, and maintain an informational environment. It integrates many of the business intelligence component functions into a single product. It can be used to automate the process of bringing data together from heterogeneous sources into a central, integrated, informational environment.

Visual Warehouse can be managed either centrally or from the workgroup environment. Therefore, business groups can meet and manage their own information needs without burdening information systems resources, thus enjoying the autonomy of their own data mart without compromising overall data integrity and security in the enterprise.

3.3.1 Data Sources Supported

Visual Warehouse provides the capability to extract and transform data from a wide range of heterogeneous data sources, either internal or external to the enterprise, such as the DB2 family, Oracle, Sybase, Informix, Microsoft SQL Server, VSAM, IMS, and flat files (for example, from spreadsheets). Data from these sources is extracted and transformed based on meta data defined by the administrative component of Visual Warehouse. The extract process, which supports full refreshes of data, can run on demand or on an automated scheduled basis.

3.3.2 Data Stores Supported

The transformed data can be placed in a data warehouse built on any of the DB2 UDB platforms, including DB2 for Windows NT, DB2 for AIX, DB2 for HP-UX, DB2 for Sun Solaris, DB2 for SCO, DB2 for SINIX, DB2 for OS/2, DB2 for OS/400, and DB2 for OS/390, or on flat files. Visual Warehouse provides the flexibility and scalability to populate any combination of the supported databases.

Visual Warehouse also supports Oracle, Sybase, Informix, and Microsoft SQL Server using IBM DataJoiner.

3.3.3 End User Query Tools

Once the data is in the target data warehouse, it is accessible by a variety of end user query tools. Those tools can be from IBM, such as Lotus Approach, or QMF for Windows, or from any other vendors whose products comply with the DB2 Client Application Enabler (CAE) or the Open Database Connectivity (ODBC) interface, such as Business Objects, Cognos Impromptu, and Brio Query. The data can also be accessed using a popular Web browser with additional Web infrastructure components.

3.3.4 Meta Data Management

Visual Warehouse stores all the meta data in its control database and is integrated with DataGuide, IBM’s meta data management tool, which is part of the Visual Warehouse solution. The data warehouse model, which defines the structure and contents of the data warehouse, is stored in the meta data repository. For each data source to be accessed, Visual Warehouse first extracts the meta data that describes the contents of the data source and places it in the meta data repository. This meta data is then used to extract, filter, transform, and map the source data to the data warehouse.

The meta data of Visual Warehouse can then be transferred to the Information Catalog managed by DataGuide. With DataGuide, users can create an Information Catalog, which contains graphical representations of the meta data. DataGuide can be integrated with DB2 CAE entitled decision support tools, which can be used to view the meta data specific to an object of interest in the DataGuide Information Catalog.

3.3.5 The Architecture of Visual Warehouse

The Visual Warehouse architecture provides a fully distributed Client/Server system that lets users reap the benefits of network computing. The

architecture consists of the following major components: • Server

• Administrative Clients • Agents

• Control Database • Target Databases

3.3.5.1 Visual Warehouse Server

Visual Warehouse Server, which runs on a Windows NT workstation or server, controls the interaction of the various data warehouse components

scheduling facility, which allows calendar-based scheduling as well as event-based scheduling. The server component monitors and manages the data warehousing processes. It also controls the activities performed by the Visual Warehouse agents.

3.3.5.2 Visual Warehouse Administrative Clients

The Administrative Client, which also runs on a Windows NT workstation or server, provides an interface for administrative functions, such as defining the business views, registering data resources, filtering source data, defining the target data warehouse databases, managing security, determining the data refresh schedules, and monitoring the execution of the data warehouse processes. Visual Warehouse can support an unlimited number of administrative clients and provides comprehensive security facilities to control and manage client access to the administrative functions.

3.3.5.3 Visual Warehouse Agents

Visual Warehouse agents handle access to the source data, filtering, transformation, subsetting, and delivery of transformed data to the target warehouse under the direction of the Visual Warehouse Server.

Visual Warehouse agents run on Windows NT, OS/2, AS/400, AIX, and Sun Solaris. Visual Warehouse supports an unlimited number of agents. Because multiple agents can participate in the population of a data warehouse, the throughput can significantly increase when multiple agents act

simultaneously. The agents primarily use ODBC drivers as the means of communicating with different data sources and targets.

The Visual Warehouse agents architecture is a key enabler for scalable business intelligence solutions.

3.3.5.4 Visual Warehouse Control Database

A control database must be set up in DB2 to be used by Visual Warehouse to store control information used by the Visual Warehouse Server. The control database stores all the meta data necessary to build and manage the warehouse. The information in the control database includes the mappings between the source and target data, the schedules for data refresh, the Business Views, and operational logs. The control database is managed by the Visual Warehouse Administrator and used by the Visual Warehouse agents. When a request for service is made to the Visual Warehouse Server, the control information pertinent to that request is retrieved from the control database and sent to the appropriate agent that actually provides the service. Note that different warehouses could use different control databases.

Advanced DB2 features, such as triggers and stored procedures, can be used in conjunction with the Visual Warehouse control data to provide an advanced operating environment. For instance, DB2 triggers can be used to monitor log inserts and to send out alert signals through DB2 stored

procedures when a certain event occurs.

3.3.5.5 Visual Warehouse Target Databases

Target databases in a data warehouse contain the Visual Warehouse data stored in structures defined as Business Views (BVs). When Visual

Warehouse populates a BV, data is extracted from the source, transformed according to the rules defined in the BV, and then stored in the target database. Multiple databases could be used as target databases for a data warehouse.