i3’s Data Warehousing projects typically follow the following process.
Discovery involves the study of the client’s IT landscape, business goals and integration needs.
A pilot phase may be introduced to evaluate products in order to identify the best match for the client’s unique situation.
Data Modeling / Data Warehouse Design
The Data Warehouse design involves the entire gamut of tasks involved in building the warehouse. Activities done in this phase include:
- Identification of the facts and dimensions, and the warehouse schema (star, snowflake or part star-part snowflake)
- Defining summary tables for aggregation, archival process, metadata definition, etc.
- Defining metadata repository
- Creating and maintaining data marts
Extraction, Transformation and Loading (ETL) Process
The Extract process takes the data from various sources including operational OLTP systems and external sources, if any. The Transform process processes the data and makes it ready for the data warehouse. The Load process takes the extracted and transformed data and populates it into the warehouse.
Activities done as a part of this phase include:
- Derivation of exact data sources from the findings of the Analysis stage
- Defining the data transformation mappings for converting raw data in the sources to useful data for the warehouse
- Identify data cleansing requirements, if any
- Development and verification of data extraction and transformation routines
- Automating ETL process by using scheduled jobs
- Ongoing maintenance of ETL routines and automation scripts
- Application integration at data level using ETL tools
OLAP Cube Design
OLAP models such as MOLAP, ROLAP are used to analyze data. The different hierarchies of the organization processes are defined. This design should take care of the available update window, during which the OLAP can be refreshed on a day-to-day basis.
Data Cleansing resolves discrepancies in the source data by:
- Identifying inconsistencies in a given source and between sources
- Defining and executing corrective actions
- Verifying corrected data before loading into the data warehouse
The information in the data warehouse is made available in the form of analytical reports, cubes, or predictive trends. The following activities are typically performed in this phase:
- Standard two dimensional reporting
- OLAP cube building for slicing-dicing of data
- Analytical reporting of key performance metrics; analytics dashboard
- Data mining and predictive analysis
- Ad hoc queries and reporting
Deployment and Training
i3 undertakes training of both the end users as well as the technical staff.
Maintenance and Tuning
i3’s maintenance services include continuously monitoring usage patterns, load metadata and user requirements in maintaining and tuning the warehouse.
Administration and Management
A typical data warehouse requires more than usual system administration effort for the following reasons:
- Use of a variety of tools for various functions (ETL, Cleansing, Design, Reporting, OLAP)
- Large volumes of data demanding tight control on storage and performance
- Unstable operation due to problems from inconsistent data to time-consuming report generation