Data Warehouse

Applications of a Data Warehouse

A data warehouse is used in many ways by businesses. Let's look at a few applications of a data warehouse. Data Mining: Using data mining, organizations analyze warehouse data to identify patterns and correlations between data. Such patterns and correlations help organizations make business predictions. Web Mining: Organizations use web mining to gather information from the web. For example, an online shopping portal analyzes data to understand customer behavior. This data includes the time customers spend browsing a page while buying a product, checking reviews and recommendations, and so on. Using this analysis, the portal can present customized views for each user. Metadata Creation: Metadata is information about data. It is created using existing data from the database. Metadata describes the type of data in the data warehouse. It also points to the locations of important data. Metadata speeds up the search process in a large data warehouse. Additionally, it stores data mappings from the database to the data warehouse. These mappings involve information about data conversion, changes in naming conventions, and so on.

Benefits of Using a Data Warehouse

A data warehouse offers multiple benefits to an organization. Let's look at some of these benefits. A data warehouse forms the backbone of an organization's reporting needs. Organizations use business intelligence (BI) tools to generate reports using data stored in data warehouses. These tools present reports in a friendly user interface called dashboards. A data warehouse generates information from multiple sources. This capability provides users with access to more data as compared to data from a single source. A data warehouse stores historical data that accumulates over certain periods, such as the last quarter or last 12 months. Companies require such data for auditing and compliance purposes. A data warehouse provides faster retrieval of reports as compared to a traditional transactional system. Generating reports on a transactional system reduces the speed and efficiency of the system. Organizations are able to ensure consistency of historical data within a data warehouse. This assurance is possible because organizations check source data for accuracy when they import it from a transactional database. A data warehouse allows renaming tables and their field names, which is not possible in operational databases. This feature of the data warehouse helps create meaningful names in reports. For example, a field named Monthly_Sales is more meaningful than M02. A data warehouse generates information from multiple sources. It stores historical and current data and creates meaningful table names before storing them. To retrieve data, a user can use the tools available with a data warehouse

Data Warehouse

A typical business organization generates a large amount of data every day. For example, a manufacturing firm needs to maintain data about raw material purchases. After these purchases, firms generate data for various processes. These processes include the conversion of raw material to the finished product, sale of the product, and so on. Typically, this data is stored on transactional databases, which businesses use for daily operations. Over time, the transactional databases become insufficient for storing increasing data. Also, most of the older data is no longer used in daily operations. However, this data is important for future references at many stages of business processes, such as analysis and compliance. So, it is better to move this data from transactional databases into a separate storage system. This storage system is called a data warehouse. A data warehouse contains data from several different transactional databases. Organizations use data warehouses to generate reports for making informed decisions. Some data warehouses even provide the visualization tools for making presentations. A data warehouse does not get updated as often as a transactional database does.

Data Warehouse Considerations

An organization may create a data warehouse or purchase one. In either case, it is an investment for the organization. Before making the investment, the organization should keep the following points in mind: The business objectives of the organization should align with the need for a data warehouse. The organization must decide on the size of the data warehouse, the frequency of its use, and its location. All the stakeholders must participate in the requirements-gathering process of the data warehouse. There should also be proper communication between business analysts and the data warehouse developers. The organization must ensure that automated archiving procedures in diverse applications are able to move data into the data warehouse. For this procedure, it is important to identify the exact data tables in the database. The organization also needs to decide whether it needs an internal maintenance team. Alternatively, the organization can outsource the maintenance to external parties. A stationary data warehouse provides users with direct access to data on the source systems using an advanced interface. A multistage data warehouse is essential for users who need summarized data as well as detailed historical data.

Types of Data Warehouses

Different types of data warehouses are available to suit the business requirements of organizations. Let's look at a few of these data warehouses. LAN-based Data Warehouse: The design of a data warehouse of this type keeps a few users connected by a local network. Business groups can use this type of warehouse to meet their own data needs, without relying on IT personnel. LAN-based data warehouses require organized maintenance and performance monitoring. These data warehouses may have hardware limitations. Multistage Data Warehouse: A multistage data warehouse is essential for users who need summarized data for short-term decisions. This type of warehouse also provides detailed historical data for long-term strategic data. The summarized data is derived from operational databases. Distributed Data Warehouse: This type of data warehouse is used by organizations that have several businesses requiring their own data. Distributed data warehouses are often located at different geographical locations. Each data warehouse is called a local data warehouse and has its own structure. A local data warehouse is unique and significant to the locality in which it resides, and all activities and transactions pertaining to the data are completed at the local site. Common data in the local data warehouses is maintained at a single global data warehouse of the organization. Stationary Data Warehouse: A stationary data warehouse gives users direct access to data on the source systems. These data warehouses use an advanced interface. Organizations that don't access data frequently use such warehouses. In order to use a stationary data warehouse, it is important to have access to a metadata store. Virtual Data Warehouse: A virtual data warehouse is unlike a physical storage system. This type of data warehouse uses system software, called middleware, to connect diverse applications to access data from databases, text files, and so on. To use a virtual data warehouse, users need access to information on different databases. They also need to know how the data elements in the tables relate to each other.

Moving Data to a Data Warehouse

When an organization creates or purchases a data warehouse, it needs to move data from the transactional databases into the data warehouse. As a data warehouse professional, you need to consider several points before you move the data. Let's look at some of these considerations. Determine what kind of analysis is expected from the business users of the data warehouse. This understanding helps you to determine if you have the required data suitable for the analysis. Ensure that the quality of data is good. This implies that you remove incorrect or duplicate data. Warehouse data generates reports that have a business impact. So, data needs to be accurate. Estimate the storage space or capacity of the data warehouse. Along with space, you also need to estimate the time it takes to retrieve data from the data warehouse. Evaluate the number of existing data warehousing experts in your organization. These personnel will help in setting up and maintaining your data warehouse. Ensure that you create a test plan for testing data in the data warehouse. Proper testing helps to ensure data accuracy, resulting in users making better business decisions. Before moving data from a transactional database into a data warehouse, you need to stop users from updating the data. You may provide a read-only access so that users can view the data. This process helps prevent data corruption when moving data from the database. Moving data into a data warehouse involves three steps: extract, transform, and load. Let's look at each of these steps in detail. Extract: During this step, data is collected from source systems, which are the transaction processing systems of the organization. Examples include the sales management or product information system for a shopping portal. The data collected from these systems is placed in temporary storage called a staging area for further processing. The transaction processing systems (source systems) continue running after the placement of data in the staging area. Transform: In this step, the data in the staging area transforms into a uniform format. During the transformation, the data undergoes several processes. For example, data cleansing removes inaccurate or duplicate data. Filtering helps select only the necessary columns from a table for saving storage space. Splitting involves dividing a single column into multiple columns. Validation involves checking the data for correctness. Load: After extracting and transforming data in the staging area, it is loaded into the data warehouse for use. Before moving data into a data warehouse, users receive read-only access to view the data. This helps prevent data corruption when moving the data from a transactional database to a data warehouse.