A Study About Data Warehouse Components Computer Science Essay

A data warehouse is collection of resources that can be accessed to retrieve information of an organization’s electronically stored data, designed to facilitate reporting and analysis. Definition of the data warehouse focuses on data storage. However, the means to retrieve and analyze data, to extract, transform and load data, and to manage the data dictionary are also considered essential components of a data warehousing system. Many references to data warehousing use this broader context. Thus, an expanded definition for data warehousing includes business intelligence tools, tools to extract, transform and load data into the repository, and tools to manage and retrieve metadata. Data warehousing arises in an organization’s need for reliable, consolidated, unique and integrated analysis and reporting of its data, at different levels of aggregation.

Data warehouse is an essential element of decision support. It aims at enabling the knowledge user to make better & faster daily business decision. In order to supply a decisional database meta- data is need to enable communication between various function areas of the warehouse & an ETL Tool is needed to define the warehousing process.

Data Warehouse Components:

The construction of a data warehouse is divided in to two stages Known as back room & front room. The first ensures the building up of the warehouse database. The second provides the restitution of data from data mart in order to fulfill analyst’s demand. According to standard data warehouse architecture, the data warehouse systems are composed of :

ETL or Warehousing tools

Restitution Tools

Meta Data

ETL Tools (Extract-Transform-Load)

Extract, transform, and load (ETL) is a process in database usage and especially in data warehousing that involves:

Extracting data from outside sources

Transforming it to fit operational needs (which can include quality levels)

Loading it into the end target (database or data warehouse)

Stocking the data warehouse with data is often the most time consuming task needed to make data warehousing and business intelligence a success. Extracting data for the data warehouse includes:

Making ETL Architecture Choices

Data Mapping

Extracting data to staging area

Applying data cleansing transformations

Applying data consistency transformations

Loading data

The extract, transformation and loading process includes a number of steps:

Extract

The first part of an ETL process involves extracting the data from the source systems. Most data warehousing projects consolidate data from different source systems. Each separate system may also use a different data format. Common data source formats are relational databases and flat files. But sometime it includes non-relational database structures as an eg:

Information Management System (IMS)

Virtual Storage Access Method (VSAM)

Indexed Sequential Access Method (ISAM)

The streaming of extracted data source and load on-the-fly to the destination database is another way of performing ETL when no intermediate data storage is required. In general, the goal of the extraction phase is to convert the data into a single format which is appropriate for transformation processing.

Transform

The transform stage applies a series of rules or functions to the extracted data from the source to derive the data for loading into the end target. Some data sources will require very little or even no manipulation of data. In other cases, one or more of the following transformation types may be required to meet the business and technical needs of the target database:

Load

The load phase loads the data into the end target, usually the data warehouse (DW). Depending on the requirements of the organization. Some data warehouses may overwrite existing information with cumulative information. Most of time updating extract data is done on daily, weekly or monthly basis. Other data warehouse may add new data in a historicized form, for example, hourly. To understand this, consider a data warehouse that is required to maintain sales record of last one year. Then the data warehouse will overwrite any data that is older than a year. More complex systems can maintain a history and audit trail of all changes to the data loaded in the data warehouse.

ETL Tool Functionalities

While the selection of a database and a hardware platform is a must, the selection of an ETL tool is highly recommended, but it’s not a must. When you evaluate ETL tools, it pays to look for the following characteristics:

Functional Capability

Ability to read directly from your data source

Metadata support

Microsoft Office Performance Point Server

One of the warehouse tools is Microsoft Office Performance Point Server. It is a software product from Microsoft & for the business intelligence sector. Version 1.0 was released in November 2007. But this version 1.0 product was not officially released until November 2007. The acquisition has enabled Microsoft to add deep analytics for reports created by its Performance Point Monitoring Server. Planning component of Performance Point Server 2007 is to be discontinued in April 1, 2009. Microsoft will discontinue this product as an independent product. It is folding its Dashboard, Scorecard and Analytic Reporting capabilities in to SharePoint Server, signaling a significant change in the company’s business intelligence software strategy. This Microsoft Office Performance Point Server stable release is 1.0SP2/2008 and the operating system is Microsoft Windows. This product license is proprietary EULA.

Performance Point Server 2007 is fully integrated with other Microsoft Office products including Excel, Visio, SQL Server, and SharePoint Server. This server provides a planning and budgeting component which is directly integrated with Microsoft Excel and SQL Server Analysis Services. And this integration allows Performance point to join the dis-jointed systems companies use to in order to keep information accurate within all of their systems. Performance Point uses ‘data cubes’ to manage the information. In 2007, Business intelligence (BI), also known as Business performance management (BPM) or corporate performance management (CPM) is a rapidly growing market owing to the ever-increasing amount of data collected by businesses about their customers. There are three types of components,

Monitoring Server Operation

Planning Sever Operation

Management Reporter

The Monitoring Server Operation has lot of the monitoring and analytical features. It includes Dashboards, Scorecards, LPIs, Strategy Maps, Filters & Reports. Dashboard Designer saves content and security information to a SQL Server 2005. Database is managed through Monitoring Server. Data source connections are also made through Monitoring Server.

The Planning Server is built on a SQL Server stack, with extensive use of Excel for line-of-business reporting and analysis. The Performance Point Planning Server supports a variety of management processes, which include the ability to define, modify, and maintain logical business models integrated with business rules, workflows, and enterprise data.

This component is specifically designed to perform Financial Reporting. It can read the PPS Planning Financial Models directly. A development kit is also available to allow this component to report off other repositories.

Oracle Business Intelligence Suite Enterprise Edition

Another type of warehouse tool is Oracle Business Intelligence Suite Enterprise Edition. This is also known as OBI EE Plus. This product developer is Oracle Cooperation. Stable release is 10.1.3.4 and it release in 1st September 2009. Written language is C++ and Java. Oracle Business Intelligence Suite Enterprise Edition used operating system are Windows, Linux, Solaris, HPUX, AIX and MACOSX. This Oracle’s set of business intelligence tools consisting two business intelligence. There are:

Former Siebel business intelligence

Hyperion business intelligence.

The former Siebel products were initially marketed by Oracle as Oracle Business Intelligence Enterprise Edition. The Oracle Business Intelligence Suite Enterprise Edition is used inter changeably with Oracle Business Intelligence Applications. Industry counter-part and main competitors of Oracle Business Intelligence Suite Enterprise Edition are Microsoft BI, IBM Cognos, SAP Business Objects and SAS.

The full deployment of OBIEE contains the following components:

Oracle Business Intelligence Publisher

Oracle Business Intelligence Scheduler

Oracle Business Intelligence Systems Management

Oracle Business Intelligence Cluster Controller

Oracle Business Intelligence Presentation Services

Oracle Business Intelligence Server

Oracle Business Intelligence Presentation Services

Oracle Business Intelligence ODBC Driver

Oracle Business Intelligence Scheduler

Oracle Business Intelligence Administration Tool

Oracle Business Intelligence Client

Oracle Business Intelligence JDBC Driver

Oracle Business Intelligence Catalog Manager

Oracle Business Intelligence Job Manager

There are lots of components in this product. Some of major components are;

Oracle Business Intelligence Admin Tool

Oracle Business Intelligence Answers

Oracle Business Intelligence Server

Oracle Business Intelligence Marketing

Oracle Business Intelligence Interactive Dashboards

Hyperion Web Analysis

In this project these major components describe in detail. According to that Oracle BI Admin Tool is an administrator tool used to construct repositories consisting of a Physical Layer, Business Model and Mapping Layer, and an abstracted end-user Presentation Layer subsequently visible in BI Answers. Oracle BI Answers are an ad-hoc query and analysis tool it processes the data from multiple data sources in a pure Web environment. Users can remote from data structure complexity and they view and work with a logical view of the information. An analysis server providing a calculation and aggregation engine that integrates data from multiple relational, unstructured, OLAP, and other sources are called as Oracle BI Server. Oracle BI Marketing is which marketing needs, formerly known as Segmentation Server. And the last major component is Hyperion Web Analysis. This mean is delivers out-of-the-box presentation and reporting for Oracle multi-dimensional sources and Essbase.