COMPUTER TRAINING COURSE- DATA WAREHOUSING

Source:- Freshersworld

What is Data Warehouse?

A data warehouse or enterprise data warehouse (DW, DWH, or EDW) is a database used for reporting and data analysis. It is a central repository of data which is created by integrating data from one or more disparate sources. Data warehouses store current as well as historical data and are used for creating trending reports for senior management reporting such as annual and quarterly comparisons.

The data stored in the warehouse are uploaded from the operational systems (such as marketing, sales etc., shown in the figure to the right). The data may pass through an operational data store for additional operations before they are used in the DW for reporting.

A data warehouse constructed from an integrated data source systems does not require ETL, staging databases, or operational data store databases. The integrated data source systems may be considered to be a part of a distributed operational data store layer. Data federation methods or data virtualization methods may be used to access the distributed integrated source data systems to consolidate and aggregate data directly into the data warehouse database tables. Unlike the ETL-based data warehouse, the integrated source data systems and the data warehouse are all integrated since there is no transformation of dimensional or reference data. This integrated data warehouse architecture supports the drill down from the aggregate data of the data warehouse to the transactional data of the integrated source data systems.

Why Data Warehousing?

The term "Business Intelligence" describes the process a business uses to gather all its raw data from multiple sources and process it into practical information they will apply to determine effectiveness of business processes, create policy, forecast trends, analyze the market and much more. Data warehousing is an integral part of any effective business intelligence endeavour. Data warehousing is more than just a database-like method of storing information. While a database simply holds data, a well-designed data warehousing system is actually comprised of three segments:

Staging: raw data is stored and manipulated by developers. The goal of developers in this stage is to take raw information from widely disparate sources, standardize and organize it, readying it for integration.

Integration: raw data is further categorized and stored logically according to the needs of the end user, allowing easier access.

Access: data is presented to users in a coherent way that is easy to understand and use. Clients employ computer desktop applications to both access and analyze the information the data warehousing system provides.

While many companies are on board with data warehousing and storage and use business intelligence systems daily, others find the concept of a data warehouse and its benefits hard to grasp. Here is a look at some of the pros of employing a data warehousing solution:

Improved user access: a standard database can be read and manipulated by programs like SQL Query Studio or the Oracle client, but there is considerable ramp up time for end users to effectively use these apps to get what they need. Business intelligence and data warehouse end-user access tools are built specifically for the purposes data warehouses are used: analysis, benchmarking, prediction and more.

Better consistency of data: developers work with data warehousing systems after data has been received so that all the information contained in the data warehouse is standardized. Only uniform data can be used efficiently for successful comparisons. Other solutions simply cannot match a data warehouse's level of consistency.

All-in-one: a data warehouse has the ability to receive data from many different sources, meaning any system in a business can contribute its data. Let's face it: different business segments use different applications. Only a proper data warehouse solution can receive data from all of them and give a business the "big picture" view that is needed to analyze the business, make plans, track competitors and more.

Future-proof: a data warehouse doesn't care where it gets its data from. It can work with any raw information and developers can "massage" any data it may have trouble with. Considering this, you can see that a data warehouse will outlast other changes in the business' technology. For example, a business can overhaul its accounting system, choose a whole new CRM solution or change the applications it uses to gather statistics on the market and it won't matter at all to the data warehouse. Upgrading or overhauling apps anywhere in the enterprise will not require subsequent expenditures to change the data warehouse side.

Advanced query processing: in most businesses, even the best database systems are bound to either a single server or a handful of servers in a cluster. A data warehouse is a purpose-built hardware solution far more advanced than standard database servers. What this means is a data warehouse will process queries much faster and more effectively, leading to efficiency and increased productivity.

Retention of data history: end-user applications typically don't have the ability, not to mention the space, to maintain much transaction history and keep track of multiple changes to data. Data warehousing solutions have the ability to track all alterations to data, providing a reliable history of all changes, additions and deletions. With a data warehouse, the integrity of data is ensured.

Disaster recovery implications: a data warehouse system offers a great deal of security when it comes to disaster recovery. Since data from disparate systems is all sent to a data warehouse, that data warehouse essentially acts as another information backup source. Considering the data warehouse will also be backed up, that's now four places where the same information will be stored: the original source, its backup, the data warehouse and its subsequent backup. This is unparalleled information security.

These are just a few of the many benefits of a data warehouse system. As part of a company's business intelligence solution, a data warehouse is integral to the gathering, processing and use of all the information a business receives daily. A strong business intelligence plan, coupled with a robust data warehouse, will guarantee a business has all the tools needed to make the right decisions for today and for the future.

Difference between Database and Data warehouse :

Database

• Used for Online Transactional Processing (OLTP) but can be used for other purposes such as Data Warehousing. This records the data from the user for history.
• The tables and joins are complex since they are normalized (for RDMS). This is done to reduce redundant data and to save storage space.
• Entity – Relational modeling techniques are used for RDMS database design.
• Optimized for write operation.
• Performance is low for analysis queries.

Data Warehouse

• Used for Online Analytical Processing (OLAP). This reads the historical data for the Users for business decisions.
• The Tables and joins are simple since they are de-normalized. This is done to reduce the response time for analytical queries.
• Data – Modeling techniques are used for the Data Warehouse design.
• Optimized for read operations.
• High performance for analytical queries.
• It's important to note as well that Data Warehouses could be sourced zero to many databases.