InfoSphere Warehouse Design studio
- Design Studio
- Administration
- SQL Tool- selected tab,
- Mining
- OLAP
SQL Warehousing Tool (SQW)
To support end-to-end business intelligence solutions, InfoSphere Warehouse provides an infrastructure for warehouse building and maintenance, including tools for application design, deployment, execution, and administration.
The SQL Warehousing Tool solves data movement and integration problems in a InfoSphere warehouse environment. Users can model logical flows of higher-level operations, which generate units of code that are organized inside execution plans. The tool provides a metadata system and an IDE to create, edit, and manage these flows, and a code generation system that translates the flow models into optimized SQL code for execution. When flow development is complete, the second part of the system comes into play: the packaging of the generated code and associated artifacts into a data warehouse application that can be deployed into various target runtime systems.
Control flow processes can be executed and managed, with InfoSphere Warehouse serving as the runtime engine and IBM WebSphere Application Server software providing control and scheduling. WebSphere Application Server provides the run-time deployment environment for data warehouse applications. Administrators access WebSphere by running the browser-based DB2 Warehouse Administration Console from a Web client computer.
Features:
- Drag-and-drop GUI for design-time modeling of flows for data movement, including tight integration with:
- Rational Data Architect physical data modeling tools, including impact analysis, reverse engineering, and DDL generation
- WebSphere® DataStage® ETL systems, providing import and export capabilities between the two design environments
- Modeling of data mining flows
- Extensive palette of standard SQL transforms (join, order by, distinct, group by, where condition) and specialized warehousing operators (SCD, key lookup, pivot)
- Broad range of source and target operators, including file import and export, DB2 bulk load, and DB2 merge
- Ability to define key properties as variables (file names, tables, schemas, database sources and targets)
- Code generation optimized for in-database execution by the DB2 data server
- Web-based interface for runtime management and monitoring
The following features are new for InfoSphere Warehouse V9.5:
- Delta deployment of data warehouse applications (for efficient modifications to existing deployed applications)
- Support for parallel processing of control flow activities
- Control flow operators that take advantage of DB2 9 features, including row compression and roll-in/roll-out
- Subprocesses in control flows to define reusable sets of activities
- Step-wise debugging of control flows
- Easy import/export of proprietary physical data models
Benefits:
- Simple and efficient design of flexible, reusable data flows
- Integrated DB2-based environment for life cycle of warehouse building and maintenance activities
- Rapid deployment of data movement processes
Components and Utilities
Design Studio
SQW developers use the Design Studio to create:
- Physical data models for source and target databases and staging tables
- Data flows that represent the movement of data from sources through transformation steps and into target databases:
- SQL data flows that use the SQL-processing power of DB2 V9.5 for warehouse building operations that work on data from relational tables and flat files
- Mining flows that integrate key data mining operations into a SQL-based model
- Control flows that order a set of related data flows and define parallel processing and conditional paths for data flow execution
- Deployment packages that contain all of the files and parameters that form a complete application that is ready for deployment and execution.
Administration Console
The InfoSphere Warehouse Administration Console is a Web-based application for managing and monitoring data warehouse applications. SQW users access the console to create and manage data sources, deploy applications, and schedule and monitor processes.
Sample data
Sample projects in the Design Studio and a sample database and tutorial are available to help you learn. Two detailed modules in the tutorial are dedicated to SQW tasks.
