Introduction to Data Warehousing

Data Warehouse

• A Data Warehouse (DW or DWH), also recognized as an Enterprise Data Warehouse (EDW), is a system for reporting and data analysis that is a key component of business intelligence.
• We can also consider it as a central repository of integrated data from one or more disparate sources.
• It consolidates current and historical data and use it to produce analytical reports for employees across the enterprise.

Type of Systems

In any organisation, there are two essentially distinct types of systems.

1. Operational systems
2. Informational systems.

• “Operational systems” are exactly what their name says; they are the systems that assist us in managing day-to-day business operations.
• Our “order entry”, “inventory”, “manufacturing”, “payroll”, and “accounting systems” are the foundation of any business. Operational systems were nearly often the first components of a firm to be computerized because of their significance to the organisation.
• In fact, without their operating systems and the data they manage, the majority of large enterprises today would be unable to function.
• Transaction processing is supported by operational systems.
• In general, operational systems are process-driven or -oriented, which means that they are concentrated on particular business activities or tasks.  billing and registration are examples of tasks.
• Operational systems’ data are typically updated frequently as needed.
• Operational systems are often designed to quickly insert new data and update existing data in relatively small volumes.
• Informational system is a set of integrated parts used to convey information, knowledge, and digital products as well as gather, store, and process data.
• Information systems are essential for businesses and other organisations to run and manage operations, communicate with clients and suppliers, and engage in market competition.
• For instance, companies employ information systems to process financial accounts, manage their personnel resources, and send targeted communications to potential clients online.
• Governments use information systems to offer residents services at a reasonable cost.
• Information systems are used to deliver digital goods like software and books and online services like auctions and social networking.
• For much of their personal lives, people use information systems, usually found on the Internet: for fun, study, socializing, shopping, banking, and other activities.

What is OLTP?

• Online transaction processing (OLTP), also known as transaction-oriented application management, is a class of Operational System that facilitates and manages transaction-oriented applications, primarily for data entry and retrieval transaction processing.
• A significant number of quick online transactions make up OLTP (On-line Transaction Processing) (INSERT, UPDATE, DELETE).
• The word “transaction” is a little contradictory; some people interpret it in terms of business or commercial transactions, while others define it in terms of computer or database interactions.
• The term “OLTP” has also been used to describe processes where the system reacts instantly to user queries.
• A commercial transaction processing application is an automated teller machine (ATM) for a bank.
• There are hundreds of users using these applications at once.
• The main objectives of OLTP applications are concurrency, performance, availability, and recoverability.
• The primary focus for OLTP systems is on very quick query processing, preserving data integrity in environments with multiple users, and an efficiency measured by the number of transactions per second.

What is OLAP?

• On-line analytical processing, or OLAP, is able to analyse and quickly summarise a huge number of historical transactions or data records, ranging in size from MBs to TBs.
• The unique quality for those in the corporate world should be “Multidimensional.”
• In other words, it is the capacity to assess measurements across a range of variables, including those related to time, place, gender, and product.
• For instance, the business’s sales are rising. Which area is primarily to blame for this rise? Which local retailer is primarily to blame for the rise? What specific product category or categories were the main drivers of the growth?
• Answering these types of questions in order means that you are performing an OLAP analysis.
• The main component of OLAP technology, which is essential to data warehousing, is its multidimensionality.
• Business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting, and related sectors are typical OLAP applications. New applications, such agricultural, are emerging.