A data warehouse, or enterprise data warehouse (EDW), is a system that aggregates data from different sources into a single, central, consistent data store to support data analysis, data mining, artificial intelligence (AI), and machine learning. A data warehouse system enables an organization to run powerful analytics on huge volumes (petabytes and petabytes) of historical data in ways that a standard database cannot.
Data warehousing systems have been a part of business intelligence (BI) solutions for over three decades, but they have evolved recently with the emergence of new data types and data hosting methods. Traditionally, a data warehouse was hosted on-premises—often on a mainframe computer—and its functionality was focused on extracting data from other sources, cleansing and preparing the data, and loading and maintaining the data in a relational database. More recently, a data warehouse might be hosted on a dedicated appliance or in the cloud, and most data warehouses have added analytics capabilities and data visualization and presentation tools.
IBM data warehouse solutions
Generally speaking, data warehouses have a three-tier architecture, which consists of a:
OLAP (for online analytical processing) is software for performing multidimensional analysis at high speeds on large volumes of data from unified, centralized data store, like a data warehouse. OLTP, or online transactional processing, enables the real-time execution of large numbers of database transactions by large numbers of people, typically over the internet. The main difference between OLAP and OLTP is in the name: OLAP is analytical in nature, and OLTP is transactional.
OLAP tools are designed for multidimensional analysis of data in a data warehouse, which contains both historical and transactional data. Common uses of OLAP include data mining and other business intelligence applications, complex analytical calculations, and predictive scenarios, as well as business reporting functions like financial analysis, budgeting, and forecast planning.
OLTP is designed to support transaction-oriented applications by processing recent transactions as quickly and accurately as possible. Common uses of OLTP include ATMs, e-commerce software, credit card payment processing, online bookings, reservation systems, and record-keeping tools.
For a deep dive into the differences between these approaches, check out "OLAP vs. OLTP: What's the Difference?"
Schemas are ways in which data is organized within a database or data warehouse. There are two main types of schema structures, the star schema and the snowflake schema, which will impact the design of your data model.
Star schema: This schema consists of one fact table which can be joined to a number of denormalized dimension tables. It is considered the simplest and most common type of schema, and its users benefit from its faster speeds while querying.
Snowflake schema: While not as widely adopted, the snowflake schema is another organization structure in data warehouses. In this case, the fact table is connected to a number of normalized dimension tables, and these dimension tables have child tables. Users of a snowflake schema benefit from its low levels of data redundancy, but it comes at a cost to query performance.
Data warehouse, database, data lake, and data mart are all terms that tend to be used interchangeably. While the terms are similar, important differences exist:
A data warehouse gathers raw data from multiple sources into a central repository, structured using predefined schemas designed for data analytics. A data lake is a data warehouse without the predefined schemas. As a result, it enables more types of analytics than a data warehouse. Data lakes are commonly built on big data platforms such as Apache Hadoop.
See the following video for more information on data lakes:
A data mart is a subset of a data warehouse that contains data specific to a particular business line or department. Because they contain a smaller subset of data, data marts enable a department or business line to discover more-focused insights more quickly than possible when working with the broader data warehouse data set.
A database is built primarily for fast queries and transaction processing, not analytics. A database typically serves as the focused data store for a specific application, whereas a data warehouse stores data from any number (or even all) of the applications in your organization.
A database focuses on updating real-time data while a data warehouse has a broader scope, capturing current and historical data for predictive analytics, machine learning, and other advanced types of analysis.
A cloud data warehouse is a data warehouse specifically built to run in the cloud, and it is offered to customers as a managed service. Cloud-based data warehouses have grown more popular over the last five to seven years as more companies use cloud services and seek to reduce their on-premises data center footprint.
With a cloud data warehouse, the physical data warehouse infrastructure is managed by the cloud company, meaning that the customer doesn’t have to make an upfront investment in hardware or software and doesn’t have to manage or maintain the data warehouse solution.
A business can purchase a data warehouse license and then deploy a data warehouse on their own on-premises infrastructure. Although this is typically more expensive than a cloud data warehouse service, it might be a better choice for government entities, financial institutions, or other organizations that want more control over their data or need to comply with strict security or data privacy standards or regulations.
A data warehouse appliance is a pre-integrated bundle of hardware and software—CPUs, storage, operating system, and data warehouse software—that a business can connect to its network and start using as-is. A data warehouse appliance sits somewhere between cloud and on-premises implementations in terms of upfront cost, speed of deployment, ease of scalability, and management control.
A data warehouse provides a foundation for the following:
IBM offers on-premises, cloud, and integrated appliance data warehouse solutions—all built on a data analytics and artificial intelligence foundation optimized for predictive insight and data-driven decision making. All three are part of the IBM Db2 family of products, offering a common SQL engine to streamline queries and machine learning capabilities that enhance data management performance.
IBM Db2 Warehouse on Cloud is a fully managed, elastic cloud data warehouse that delivers independent scaling of storage and compute, featuring a highly optimized columnar data store, actionable compression, and in-memory processing to supercharge your analytics and machine learning workloads. Smaller data marts and spin ups can add Flex One, an elastic data warehouse built for high-performance analytics, deployable on multiple cloud providers, starting at 40 GB of storage.
Netezza Performance Server, the next evolution of the IBM Netezza appliance, builds on the hyper-converged architecture of the IBM Cloud Pak for Data System to provide a cloud native decision support system for your enterprise’s most complex analytics.
And IBM Watson Studio, a data science and machine-learning offering, empowers organizations to tap into data assets and inject predictions into business processes and modern applications.
For more information on data warehouses, sign up for an IBMid and create your IBM Cloud account.
IBM data warehouse solutions offer performance and flexibility to support structured and unstructured data for analytics workloads including machine learning.
Explore the capabilities of a fully managed, elastic cloud data warehouse built for high-performance analytics and AI.
Explore an advanced data warehouse and analytics platform with powerful in-database analytics, available both on premises and on cloud.
IBM Cloud Pak for Data System is an all-in-one hybrid cloud platform that delivers a preconfigured, governed and security-rich environment on premises.
Build, run and manage AI models. Prepare data and build models on any cloud using open source code or visual modeling. Predict and optimize your outcomes.
Explore IBM DataStage, a powerful, scalable ETL platform that delivers near real-time integration of all data types across on-premises and cloud environments.
AI can present a number of challenges that enterprise data warehouses and data marts can help overcome. Discover how to assess the total value such a solution can provide.