A data warehouse is what?
For the purposes of supporting data analysis, data mining, artificial intelligence (AI), and machine learning, a data warehouse, also known as an enterprise data warehouse (EDW), is a system that collects data from several sources into a single, central, consistent data storage. In ways that a traditional database cannot, a data warehouse system enables an organisation to do complex analytics on enormous amounts (petabytes and petabytes) of historical data. Over the past three decades, data warehousing systems have been a component of business intelligence (BI) solutions; but, in recent years, new data types and hosting techniques have caused them to change. A data warehouse's capabilities traditionally concentrated on obtaining data from various sources, cleaning and preparing the data, loading and maintaining the data in a relational database, and hosting the data on-premises, frequently on a mainframe computer. A data warehouse may now be housed on a dedicated appliance or in the cloud, and the most of them now include analytical capabilities as well as tools for data visualisation and presentation.
Source: Safalta.com
Architecture of a data warehouse
- Bottom tier: The bottom tier is made up of a data warehouse server, often a relational database system, which gathers, purifies, and transforms data from various data sources using an extraction, transformation, and loading (ETL) or extract, load, and transform (ELT) procedure (ELT).
- Online analytical processing (OLAP) server at the middle layer, which offers quick query response times. This tier allows for the usage of the ROLAP, MOLAP, and HOLAP types of OLAP models. The kind of database system that is utilised determines the kind of OLAP model that is employed.
- Top tier: The front-end user interface or reporting tool, which enables end users to do ad-hoc data analysis on their company data, is representative of the top tier.
Data warehouse schemas
- One fact table that can be connected to a number of denormalized dimension tables makes up the star schema. It is regarded as the simplest and most prevalent kind of schema, and its users gain from its quicker querying times.
- Another organisational style used in data warehouses is the snowflake schema, albeit not being as popular. In this instance, a number of normalised dimension tables with child tables are linked to the fact table. While a snowflake schema's low levels of data redundancy are advantageous to users, query speed suffers as a result.
OLTP and OLAP in data warehouses: An understanding
Software known as OLAP (for online analytical processing) allows users to quickly do multidimensional analysis on enormous amounts of data coming from a single, centralised data storage, such as a data warehouse. Online transactional processing, or OLTP, allows a lot of people to execute a lot of database transactions in real time, usually over the internet. The name of each technology distinguishes its primary function: OLAP is analytical in nature, whereas OLTP is transactional.In a data warehouse that houses both historical and transactional data, OLAP tools are made for multidimensional examination of the data. OLAP is frequently used for company reporting tasks including financial analysis, budgeting, and forecast planning as well as data mining and other business intelligence applications, intricate analytical computations, and predictive scenarios.
OLTP is made to facilitate transaction-oriented applications by properly and swiftly processing recent transactions. ATMs, e-commerce software, credit card payment processing, online bookings, reservation systems, and record-keeping tools are a few examples of common OLTP applications.
Data Warehouse Types
Cloud data storageCustomers can purchase a cloud data warehouse as a managed service. A cloud data warehouse is a data warehouse designed expressly to operate in the cloud. Over the last five to seven years, cloud-based data warehouses have become more widespread as more businesses adopt cloud services and try to decrease the footprint of their on-premises data centres. With a cloud data warehouse, the physical infrastructure is controlled by the cloud provider, thus the client is spared from the upfront costs of hardware and software as well as the burden of managing and maintaining the data warehouse solution.
Data warehouse software (licenced and installed)
A company can buy a licence for a data warehouse and then set up that warehouse on its own on-premises infrastructure. Government agencies, financial institutions, and other companies who need to adhere to stringent security or data privacy rules or regulations may find that this is a better option, even if it is often more expensive than a cloud data warehouse service.
Data warehouse appliance
A firm may connect a data warehouse appliance to its network and begin utilising it right away. A data warehouse appliance is a pre-integrated set of hardware and software, including CPUs, storage, an operating system, and data warehouse applications. In terms of initial cost, speed of deployment, simplicity of scaling, and administrative control, a data warehouse appliance falls halfway between cloud and on-premises systems.
