The Data Warehouse Architect works with your company’s Database Administrator (DBA) throughout this process to ensure compatibility with corporate standards and to ensure the DBA will be comfortable supporting the databases into the future.

Data Extract, Transform and Load (ETL)

This service is delivered by an ETL Specialist with assistance from IT and user staff who are very familiar with the data sources and the business rules.

The ETL products are configured to deliver the data from the legacy sources to the data warehouse environment.

Wherever possible, the ETL rules will be stored in a single repository for consistency of definition and ease of maintenance. This is best accomplished with an ETL software tool. In some cases, it may be necessary to use custom business logic, that should be implemented as Stored Procedures and Triggers in the RDBMS. As a last resort, unique requirements may have to be met using a programming language such as C++.

The bulleted items below summarize the five major steps in creating a data warehouse. Although every project possesses some distinctive characteristics, and we reflect those considerations in the project plan, our basic methodology remains consistent.

• Meet with client personnel to identify immediate and long–term data requirements.
• Produce a Requirements Specification for the client’s approval.
• Assemble a Design Specification, beginning with recommendations for the necessary hardware and Business Intelligence software. The Design Specification also includes a data model and mock–ups of the key user interface screens. Work with the client’s project team to refine the design for client approval.
• Develop, test, and document the software described in the Design Specification. Throughout this process, transfer knowledge about system support and long–term maintenance to the client’s IT staff.
• Prior to the production roll–out of the data warehouse, provide “train the trainer” instruction for end users of the system.

There is a range of other data warehouse concepts and methodologies including:

• Logical data model
• Conceptual data model
• Physical data model
• Star Schema
• Data mining
• Staging area
• Multi-dimensional analysis
• Snowflake schema
• OLAP tools

Business Analysis

Working from the results of the Opportunity Assessment, and using the Data Warehouse Strategy as a framework, the Senior Business Analyst identifies the detailed requirements for the solution.

The Senior Business Analyst works with end users to capture their intended use of the information for business growth, determine what information they need to see, and how they need to see it:

• Identify the Key Performance Indicators (KPIs) which will be used to track objectives.
• Online analysis processing requirements (analytics).
• Security requirements.
• Review current reporting systems that report on related data.
• Describe reports that need to be provided.
• Span of history to be kept in the data mart.
• Facts and Dimensions required to support the analysis and reports.
• Frequency of generating analytics reports.
• Frequency of refreshing the data mart.
• Geographic location of users.
• Method of distributing reports.
• Estimate how much data will be processed and stored for facts, dimensions, summaries and indexes.

Outputs from this service include:

• Documentation of objectives, KPIs, requirements, reports, estimates, assumptions and findings.
• High level Logical Data Model.
• Scenarios describing how the analytics and reports will be used in the business context.

Based on findings from the Business Analysis service, the Data Warehouse Architect works with the company’s Data Administrator to determine the data sources that are required to support the business needs, and the quality of the data in those sources.

Sources may be internal or external. Internal data sources typically belong to the company and reside in operational databases such as an order processing database. External data sources may be acquired from outside the organization, and often are used for demographic analyses or database marketing.

If similar data exists in more than one source, we work with the Data Administrator and business user to determine the single authoritative source.

Data quality issues may include:

• Redundant data sources
• Duplicate rows within a data source
• Dirty data (invalid values)
• Data Integrity problems (inconsistent meanings and usage of fields)
• Referential Integrity problems (missing links, invalid links)

We will recommend solutions for addressing data quality issues. It is imperative that the best quality data be used to drive your analytics.

Data quality improvement may involve one-time and ongoing activities such as removing duplicates, standardizing names and addresses, deriving implied values, and improving data validation or logic in the operational system which generates the data.

By helping you define strategies, integrate new processes, leverage best practices and proven resources, we help you make well-informed decisions, improve operational efficiencies and create new revenue channels. Clients of Avenues enjoy the following benefits:

• Defined Business Intelligence Strategy – Solutions are developed and deployed that address the strategic goals and business objectives of your organization
• Project completion on-time and on-budget – Best practices, strong project management and solutions architecture allow your project to start strong, stay strong and end strong
• Customer satisfaction and retention – We utilize resources and methodologies to build BI solutions that are an integral part of extending and improving customer relationships throughout the value chain
• Measurable ROI – Enterprise intelligence methods greatly improve the speed and quality of your decisions, resulting in greater return on your information assets

This service gives you a business-level assessment of the opportunity to use Business Intelligence (BI) analytics and Data Warehousing (DW) methods. Conducted by a Data Warehouse Architect, the outputs of the service include:

• Definition of business problem or opportunity
• Benefits of deploying a BI/DW solution and the metrics for measuring those benefits
• Business risks, IT risks and risk-mitigation options
• High-level plan outlining time, resources and costs