Data Warehouse Features and Architectures

Features of DWH

A Data Warehouse is a subject oriented, integrated, nonvolatile, and time variant collection of data in support of management’s decision.

1. Subject oriented : data is not stored by operational applications, but by business subjects.
2. Integrated : Data inconsistencies are removed, data from diverse operational applications is integrated (naming convention, codes, data attributes, measurements)
3. Nonvolatile : data in the data warehouse is not updated or deleted.
4. Time variant : contain historical data, not just current values related to a specific date, week, month, or quarter
5. Data granularity : levels of detail of the data

ARCHITECTURES OF DWH

Person-view Perspective of Data warehouse

To design an effective and efficient data warehouse, we need to understand and analyze the business needs and construct a business analysis framework. Each person has different views regarding the design of a data warehouse.

• The top-down view − This view allows the selection of relevant information needed for a data warehouse.
• The data source view − This view presents the information being captured, stored, and managed by the operational system.
• The data warehouse view − This view includes the fact tables and dimension tables. It represents the information stored inside the data warehouse.
• The business query view − It is the view of the data from the viewpoint of the end-user.

Bottom tier

Middle tier

Top tier

1. BOTTOM TIER

• The bottom tier of the architecture is the data warehouse database server.
• It is the relational database system. Data is feed into bottom tier by some back-end tools and utilities.

Function performed by back end tools and utilities :

• Data Extraction
• Data Cleaning
• Data Transformation
• Load
• Refresh

​

Data source layer

• The data source layer of data warehouse architecture is where original data, collected from a variety internal and external sources, resides in the relational database.
• Examples of source data types include but are not limited to: These stores disparate data types including:
• Operational Data — Product data, inventory data, marketing data, or HR data.
• Social Media Data — Web site hits, content popularity, contact page completion.
• Third-party data — Demographic data, survey data, census data.
• While most data warehouse architecture deals with structured data, consideration should be given to the future use of unstructured data sources, such as voice recordings, scanned images, and unstructured text. These streams of data are valuable silos of information and should be considered when developing your data warehouse.

Data Staging Layer

• The data staging layer resides between data sources and the data warehouse.
• In this layer, data is extracted from different internal and external data sources.
• Because source data comes in many different formats, the data extraction layer will utilize multiple technologies and tools to extract the required data. Once the extracted has been loaded, it will be subjected to high-level data quality checks.
• The final result will be clean and organized data that you will load into your data warehouse. The staging layer contains the following components:
• Landing Database and Staging Area
• Data Integration Tool (ETL)

Landing Database and Staging Area

• The landing database stores the data retrieved from the data source.
• Staging is used to apply quality checks on the data before moving it to the data warehouse.
• Staging is an essential step in data warehouse architecture.
• Poor data will amount to inadequate information and result is poor business decision making.
• The staging layer s also where you want to make adjustments to the schema to handle unstructured data sources.

​

Data integration tool

• Extract, Transform and Load tools (ETL) are the data integration tools used to extract data from source systems, transform and prepare data and load into the data warehouse.
• Panoply.io product provides this entire process, easily and quickly.
• All you need to do is point it to your data source(s).

​

Data Storage Layer

• The data storage layer is where data that was cleansed in the staging area is stored as a single central repository. Depending on your business and your data warehouse architecture requirements, your data storage may be a data warehouse, data mart (data warehouse partially replicated for specific departments), or an Operational Data Store (ODS).

​

2. MIDDLE TIER

• The OLAP Server that can be implemented in either of the following ways.
• By Relational OLAP (ROLAP), which is an extended relational database management system. The ROLAP maps the operations on multidimensional data to standard relational operations.
• By Multidimensional OLAP (MOLAP) model, which directly implements the multidimensional data and operations.

​

3. TOP TIER (DATA PRESENTATION LAYER)

• This tier is the front-end client layer.
• This layer holds the query tools and reporting tools, analysis tools and data mining tools.
• The presentation layer is where users interact with the cleansed and organized.
• This layer of the data warehouse architecture provides users with the ability to query the data for product or service insights, analyze the information to conduct hypothetical business scenarios, and develop automated or ad-hoc reports.

ARCHITECTURAL TYPES

1

2

3

4

5

DWH MODELS

1. Virtual Warehouse
1. The view over an operational data warehouse is known as a virtual warehouse.
2. It is easy to build a virtual warehouse.
3. Building a virtual warehouse requires excess capacity on operational database servers.

​

2. Data mart

• Data mart contains a subset of organization-wide data. This subset of data is valuable to specific groups of an organization.
• Data marts contain data specific to a particular group.
• For example, the marketing data mart may contain data related to items, customers, and sales. Data marts are confined to subjects.

​

Data Mart

• They are implemented on low-cost servers.
• The implementation data mart cycles is measured in short periods of time, i.e., in weeks rather than months or years.
• The life cycle of a data mart may be complex in long run, if its planning and design are not organization-wide.
• Data marts are small in size.
• Data marts are customized by department.
• The source of a data mart is departmentally structured data warehouse.
• Data mart are flexible.

3. Enterprise Warehouse

• An enterprise warehouse collects all the information and the subjects spanning an entire organization
• It provides us enterprise-wide data integration.
• The data is integrated from operational systems and external information providers.
• This information can vary from a few gigabytes to hundreds of gigabytes, terabytes or beyond.

​

DWH COMPONENTS

1. Load Manager

• This component performs the operations required to extract and load process.
• The size and complexity of the load manager varies between specific solutions from one data warehouse to other.

Load Manager Architecture

The load manager performs the following functions :

• Extract the data from source system.
• Fast Load the extracted data into temporary data store.
• Perform simple transformations into structure similar to the one in the data warehouse.

​

Extract Data from Source

• The data is extracted from the operational databases or the external information providers.
• Gateways is the application programs that are used to extract data.
• It is supported by underlying DBMS and allows client program to generate SQL to be executed at a server. Open Database Connection(ODBC), Java Database Connection (JDBC), are examples of gateway.

Fast Load

• In order to minimize the total load window the data need to be loaded into the warehouse in the fastest possible time.
• The transformations affects the speed of data processing.
• It is more effective to load the data into relational database prior to applying transformations and checks.
• Gateway technology proves to be not suitable, since they tend not be performant when large data volumes are involved.

​

Simple Transformations

• While loading it may be required to perform simple transformations.
• After this has been completed we are in position to do the complex checks.
• Suppose we are loading the EPOS sales transaction we need to perform the following checks:
• Strip out all the columns that are not required within the warehouse.
• Convert all the values to required data types.

​

2. Warehouse Manager

• A warehouse manager is responsible for the warehouse management process.
• It consists of third-party system software, C programs, and shell scripts.
• The size and complexity of warehouse managers varies between specific solutions.

​

Warehouse Manager Architecture�

• The controlling process
• Stored procedures or C with SQL
• Backup/Recovery tool
• SQL Scripts

​

Operations Performed by Warehouse Manager

• A warehouse manager analyzes the data to perform consistency and referential integrity checks.
• Creates indexes, business views, partition views against the base data.
• Generates new aggregations and updates existing aggregations. Generates normalizations.
• Transforms and merges the source data into the published data warehouse.
• Backup the data in the data warehouse.
• Archives the data that has reached the end of its captured life.

​

3. Query Manager

• Query manager is responsible for directing the queries to the suitable tables.
• By directing the queries to appropriate tables, the speed of querying and response generation can be increased.
• Query manager is responsible for scheduling the execution of the queries posed by the user.

​

Query Manager Architecture

The following screenshot shows the architecture of a query manager.

• Query redirection via C tool or RDBMS
• Stored procedures
• Query management tool
• Query scheduling via C tool or RDBMS
• Query scheduling via third-party software

​

4. Detailed Information

• Detailed information is not kept online, rather it is aggregated to the next level of detail and then archived to tape.
• The detailed information part of data warehouse keeps the detailed information in the starflake schema.
• Detailed information is loaded into the data warehouse to supplement the aggregated data.
• The following diagram shows a pictorial impression of where detailed information is stored and how it is used.

​

5. Summary Information

• Summary Information is a part of data warehouse that stores predefined aggregations.
• These aggregations are generated by the warehouse manager. Summary Information must be treated as transient. It changes on-the-go in order to respond to the changing query profiles.

​

The points to note about summary information are

• Summary information speeds up the performance of common queries.
• It increases the operational cost.
• It needs to be updated whenever new data is loaded into the data warehouse.
• It may not have been backed up, since it can be generated fresh from the detailed information.