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:
Get Help With Your Essay
If you need assistance with writing your essay, our professional essay writing service is here to help!
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.
Cite This Work
To export a reference to this article please select a referencing style below: