Data Warehousing: 'Conceptual Architecture'

Design

Tailor Data Warehousing conceptual design subject areas to specific reporting and analytical requirements of each business unit.

When attempting to build a Data Warehouse for optimal performance, there are a few key areas to consider: hardware configuration, logical and physical data models, and workload management. The Business Intelligence environment will include analytical, reporting and ETL (Extraction, Transformation and Load) tools as well as a Data Warehouse with one or more data marts to create a working business intelligence system. 

The conceptual design is to have each business unit have its own subject area tailored to their reporting and analytical requirements.  The simple diagram below shows the basic design for how source data is extracted, transformed and loaded into the data warehouse on a daily basis through an ETL process.

Each subject area is updated with the recently loaded data and made available to the users. Reports that have been scheduled to be distributed and delivered on a predefined schedule are released, and last, users can perform ad-hoc reporting and custom analysis by using the front end tool. 

One of the great functions that a Data Warehouse provides is to develop reports that are used monthly, weekly, quarterly, and yearly and have them automatically pushed to your e-mail inbox at a scheduled time.  No longer is it necessary to continually re-create or re-run these types of standard reports.

The diagram below also provides an example of how the conceptual architecture should be built.

The Data Warehouse is built on a three-tier architecture. Each tier can be managed and scaled independently, increasing flexibility.  As a result, individual components can scale and be made highly available with ease.

The three-tier architecture is made of the following layers:

• Data Tier

o   Stores the Data Warehouse data

o   Handles the Data Warehouse ETL process where all the source data is extracted, scrubbed, transformed and loaded in the Data Warehouse.

• Application Tier

o   This is where all the business intelligence is built.

o   Handles all user requests.

• Presentation Tier

o   Graphical user interface allowing:

- Reporting

- Analysis

- Query

- Score carding

- Dash boarding

- Event Management

- User Administration

The diagram below depicts the design for the Application Tier, Presentation Tier, and Data Tier.  The three layers required for building the Data Warehouse are intended to show not only how to build the infrastructure, but also show the flow.  The business user or “analyst” is the person that needs to perform queries, run reports, and view dashboards that will show statistical data necessary to make business decisions.  All of this is shown in the “Presentation Tier”.

“Application Tier” is intended to show where the Business Intelligence tools and hardware reside.  Business Intelligence tools consist of front end reporting tools such as Cognos, Microsoft Data Warehousing tools, Microstrategy, or other tools.

The “Data Tier” is intended to show where the sources of data reside.  This can be internal or external sources that are fed into the Data Warehouse.  The data base servers and ETL servers are also located in this tier.  The ETL tools are used to take the data that is fed into the system and transform the data into meaningful information.  It is important to understand the volume of data that will be processed as well as the percentage of data growth per year because all of this will impact system performance.

Laura Paoletti holds a Bachelor of Science Degree in Computer Information Systems and has been the Vice President of Information Technology at NBC-Universal and Disney ABC Television.  She has also held positions at Ernst & Young LLP in the Technology practice.  In her role she has been responsible for Applications, Infrastructure and Digital Media.  Some of her notable accomplishments include the implementation of applications for Digital Media, Marketing, Finance, Manufacturing (supply chain), Sales and Consumer Products; Implementation of Enterprise Data Warehousing/Business Intelligence systems; Data Center management, including hardware, storage strategies, digital libraries and data center expansion; Implementation of a Project Management office; and Business transformation from a tape to a tapeless environment (digital media). See  here for all of Laura's Tom's IT Pro articles.