Build a Data Warehouse fast using DW Architect
‘DW Architect’ is an Integrated Development Environment for Agile Data Warehouse Development. Easily Design, Generate and Deploy a Data Warehouse and its ETL on your prefered database platform. ‘DW Architect’ makes development easier, and allows you to implement a best practice Data Warehouse in a matter of days.
Use DW Architect to:
- Rapidly Design your Data Warehouse Data Model.
- Design the ETL and generate the Code.
- Sync the Data Warehouse Database Schema with the latest design.
- Deploy the Data Warehouse Code to the Server.
- Execute the ETL Batch.
Rapidly Design your Data Warehouse Data Model.
‘DW Architect’ is a dedicated Data Warehouse Data Modelling tool. It allows you to quickly define the Star Schema of the Data Warehouse database, by importing meta data from Source Systems.
Why is a dedicated Data Warehouse modelling tool like ‘DW Architect’ better than just a standard Data Modelling tool? Because, its Faster, guides the Modeller in Star Schema design, and encourages Best Practice!
With ‘DW Architect’ you model Facts and Dimensions in a Star Schema along with their Attributes and Measures. In a standard data modelling tool, it’s up to you to understand how to model tables and columns, and relate them in a Star Schema. “DW Architect’ also caters for Data Warehouse specific constructs like Role play dimensions, Many to Many dimensions, Bridges, Conforming dimensions, Slowly changing Dimension attribute types etc.
‘DW Architect’ allows you to connect to Source Systems, and use table definitions to quickly import and define the attributes and measures of Fact and Dimension tables.
A Data Warehouse relies on an intermediary database called a Staging Database. ‘DW Architect’ supports modelling a Staging Database as well.
Design the ETL
‘DW Architect’ is an ETL Design environment. The ETL Designer and Data Warehouse Modelling Tool are integrated in a single Development Environment. This means the ETL designer can leverage the Data Model Meta Data to rapidly define mapping between Source, Staging and Data Warehouse databases.
‘DW Architect’ makes designing ETL quick and easy. In the simple case just select the source table(s) for the Extract or Transformation, auto match columns from source to destination, choose the ETL pattern, and it’s done. ‘DW Architect’ also supports more complex scenarios, with query sources, expressions, custom Meta data, and derived tables.
How is ‘DW Architect’ different? DW Architect is an ETL Designer, with the emphasis on Design. The design is saved as XML Meta Data that feeds into the Code Generation engine. Conceptually this means you design once, and generate in any language for any platform you choose. See the Code Generator section for more details.
‘DW Architect’ is not, in itself, another ETL execution platform. It’s designed to generate code that runs on your existing Database server technology, leveraging the power of your existing investment in a modern Database Management system.
‘DW Architect’ ETL design is Pattern based. Patterns define common ETL code scenarios that can be applied over and over again to similar Extracts and Transformations. Much of the logic is defined in the pattern, which simplifies design, development and testing, and ensures best practice standards are followed. ‘DW Architect’ comes with a common set of design Patterns and Generation templates out of the box. Data Warehouse Architects are able to define their own patterns, and the code generation templates that go with them.
Generate the Code.
‘DW Architect’ generate ETL code for any platform. It comes with a set of Standard generation templates, that can be extended and added to by your Data Warehouse Architect.
ETL code is generated from Meta Data defined in the Design Environment. The platform includes a user interface, engines and templates to generate the entire suite of Data Warehouse Data Model Definition and ETL code artefacts required to implement your data warehouse. Code can be generated via a management console or by command line.
Included are templates and engines to generate:
- Staging Tables DDL synchronization.
- Data Warehouse Tables DDL synchronization.
- Extract, Transform and Load (ETL) procedures.
- Table Management procedures.
- Data Dictionary.
Supported Platforms.
Sync the Data Warehouse Database Schema with the latest Design.
DW Architect generates Non-Destructive Data Model Definition (DDL) code.
How is ‘DW Architect’ different? It is important that data is not destroyed, when the Data Warehouse schema is updated. Existing schema synchronisation tools implement a column name change as a drop and create column pair destroying data in the existing column as they do so.
DW Architect advanced DDL generation methods preserves data, recognizes name changes, and generates scripts which test the existing state of your data warehouse, creating and modifies tables and columns as required.
Deploy the Data Warehouse Code to the Server.
DW Architect makes code deployment simple. ‘DW Architect’ includes a code deployment platform to deploy generated code to local, test and production environments. In most cases, deployment is a few simple clicks.
A configuration file is created for each target environment. Within the configuration file connection strings and other variables specific for the environment are defined. The deployment process utilizes this configuration file to deploy the code to the target environment, setting variables as required.
The platform includes a user interface and deployment providers to deploy code to various target servers. The code can be deployed via the management console or by command line.
The code generation platform is extensible allowing you to develop your own deployment providers to target custom server environments.
Execute the ETL Batch.
DW Architect includes a server-side batch execution platform which executes and manages ETL batches. Code generated by DW Architect is designed to be executed by this platform.
The Batch Manager orchestrates ETL tasks on your existing BI/Database platform, executing tasks in the correct order, and managing status updates and failures. The Batch execution framework uses an internal batch status database to record Batch progress.
The Batch execution component:
- Employs dynamic Server utilization with multi-threading.
- Handles Batch failure.
- Stores Batch statistics.
- Handle multiple batch schedules i.e. Daily, Weekly, Monthly.
The batch management platform is extensible. Users can create their own phase and task execution providers.