Enterprise data warehousing: architecture, types, best tools, and selection
June 20, 2023
An enterprise data warehouse (EDW) is a repository that centralizes enterprise-wide data coming from heterogeneous sources to prepare it for further analysis. An enterprise DWH breaks down data silos and makes corporate information accessible for companies to make timely data-driven decisions.
Enterprise data warehousing market overview
the projected enterprise data warehouse market size by 2030
Market Research Future
Scheme title: The most important data warehousing-related trends
Data source: yellowbrick.com — Key Trends in Hybrid, Multicloud and Distributed Cloud for 2021
What are the most important data warehousing-related trends for your company this year? Select all that apply.
Scheme title: Companies’ investments in analytics infrastructure
Data source: yellowbrick.com — Key Trends in Hybrid, Multicloud and Distributed Cloud for 2021
In the current climate of uncertainty, our investment in analytics infrastructure such as data platforms, data warehouses, etc. is:
6 components of an enterprise data warehouse
An enterprise data warehouse is more than a repository connected to your data sources (CRM, IoT devices, SaaS apps, etc.) on one end and to BI and analytics software on the other. In truth, an enterprise data warehousing solution is a comprehensive data processing and storage environment that consists of the following components:
1 ETL/ELT
Extract, transform, load (ETL) or extract, load, and transform (ELT) tools ingest information from the source systems and process it until it’s suitable for permanent storage. It’s one of the core elements for enterprise-grade analytics because companies have to rely on numerous data sources with different data types, models, and information generation speeds.
2 Staging area
A staging area is a temporary raw data repository between data sources and its permanent storage that hosts the data during the transformation stage. This element is typical for solutions built with the ETL approach but can be omitted if the transformations are performed in the data warehouse database.
3 Data warehouse database
Traditionally, an enterprise data warehouse database is a relational database where the integrated and subject-oriented business information is loaded into data models for analytical querying. This component also includes a metadata repository where an enterprise stores a map of its data for easy access and handling.
4 Data marts
Dimensional data marts are built to satisfy the analytics needs of specific user groups and departments, for example, sales and marketing, production, supply chain management, and finance. Data marts facilitate easier and quicker data access and analysis as they handle smaller, more applicable datasets.
5 OLAP cubes
Deploying multidimensional OLAP cubes that store data in the pre-aggregated form helps overcome the limitation of relational databases and streamline data analysis. The data in OLAP cubes can be sliced and diced, drilled down, rolled up, and pivoted to handle various analytics requests of business users.
6 Data governance
The data governance component defines processes and policies for managing data quality and security, data modeling, metadata, data retention and backup, data usage, and user activity.
Looking for a trustworthy DWH consultant?
Enterprise data warehouse architecture
Traditional enterprise data warehouse solutions are built according to the three-tier architecture, which includes:
- Data warehouse server (bottom tier)
- OLAP server (middle tier)
- Data access layer (top tier)
However, the architectural approach should still be dictated by the company’s needs, so other design methods (e.g. a one-tier or a two-tier architecture) can prove more suitable in some cases.
Enterprise data warehousing functionality
An enterprise data warehouse is not a specific software type but an environment combining multiple technologies. Together, they enable the following functionality:
Connectivity
- Pre-built connectors to various cloud and on-premises data sources, including databases, business applications, flat files, feeds, web URLs, IoT devices, and ecommerce platforms
- API libraries for custom connectors creation
- Integration with business intelligence and analytics software, including big data analytics and ML tools
- Integration with an operational data store and a data lake
Enterprise data warehouse integrations
To serve the needs of various users across the company, the enterprise data warehouse should integrate data from all sources defined by the established analytics objectives at the required granularity level. Among the most commonly integrated data sources are:
CRM system
External data sources
CSV and flat files
Project management
software
Corporate website and intranet
Enterprise data warehouse
Supply chain management software
Ecommerce platform
Accounting and finance software
Marketing software
ERP system
Enterprise data warehouse types
When setting up an enterprise data warehouse, businesses have to choose between a cloud, on-premises, or hybrid environment.
On-premises
Cloud
Hybrid
Description
On-premises
Cloud
Hybrid
An in-house or outsourced IT team on-premises deploys DWH on the local server
A cloud data warehouse is hosted and managed in the cloud. All hardware-related costs, software setup, infrastructure audits, and maintenance are the provider’s responsibility (if a DWH is delivered as a managed server).
A hybrid data warehouse is distributed across both cloud and on-premises environments
Major pros
On-premises
Cloud
Hybrid
Comprehensive control over the data warehouse hardware and software infrastructure High availability and security Compliance with data regulations, which require keeping data onsite
Quick deployment and fast and cost-effective storage and computational resources scaling up and out Minimized upfront costs due to a pay-as-you-go model High fault tolerance and disaster recovery due to the distributed nature of the cloud data warehouses
Efficient operation in the cloud while meeting the strictest regulatory requirements and addressing data latency issues
Limitations
On-premises
Cloud
Hybrid
Heavy upfront investments for hardware acquisition, software licenses, IT resources, etc. Requires comprehensive capacity planning due to complicated scaling Requires an experienced IT team to keep the system running efficiently
Failure to meet compliance requirements prohibiting cloud data storage Lack of pricing transparency and complicated pricing structures (e.g., egress fees, extra pay for hot data storage, excess compute, geo-redundancy)
High price due to purchasing hardware and software and paying for the cloud resources Requires solid expertise in development and maintenance
Top tools for enterprise data warehouse solutions
We recommend starting the data warehouse selection process by reviewing the solutions that are recognized leaders in the recent Forrester Wave and Gartner Magic Quadrant reports.
Features
- Direct querying of structured, semi-structured, and unstructured data from Amazon S3 for analysis without loading and transformation Seamless integration with the AWS analytics services and select AWS partners to ingest data from Salesforce, Google Analytics, Facebook Ads, Slack, Jira, Splunk, etc. Querying live data across Amazon Relational Database Service (RDS), Aurora PostgreSQL, RDS MySQL, and Aurora MySQL databases with the federated query capability Native support of semi-structured data Native support of advanced analytics
- Flexible separate payment for compute and storage resources with RA3-type nodes Dynamic concurrency scaling for extra compute power Continuous cluster health monitoring Manual and automated snapshots for disaster recovery Data access permissions applied to tables, multi-factor authentication, manually-enabled data encryption, dynamic data masking Compliance with HIPAA, ISO 27001, PCI DSS, SOC 1 Type II, and SOC 2 Type II
Software category
Cloud data warehousing
Pricing
On-demand pricing
from $0.25/hour
Amazon Redshift Serverless
$0.36/RPU hour
Managed storage pricing
$0.024/GB/month
Free trial
Need help choosing an optimal tech stack for your DWH project?
A guide to choosing an enterprise data warehouse platform
As the field of data analytics matures, the diversity of data warehouse software technologies can become overwhelming. So when assessing enterprise data warehouse technologies, a company should consider multiple factors to select the right tech stack.
Data volume
To avoid overwhelming storage costs and enterprise data warehouse scaling inflexibility, consider your current data volumes as well as target data volume when choosing the platform. To make estimates, take into account annual information growth rates and your strategic data management and analytics objectives, like plans to support decision-making with new data sources, including big data.
Enterprise data warehouse cost and timeline
Enterprise data warehouse costs include various categories of implementation and maintenance costs, such as:
Key factors
Data volume, its type, complexity, and quality
The number of data sources and their disparity
The number of data flows and data modeling complexity
Type of workload to support
Data cleansing and transformation complexity
Data security and compliance requirements
Platform scalability, fault tolerance, and velocity
Real-time reporting requirements
Software license fees
Solution management
Enterprise data warehouse benefits
Corporate information consolidation
Integrating enterprise-wide information typically scattered across multiple systems, companies can perform analysis of cross-functional historical data and carry out business performance assessment, risk analytics, or strategic planning.
Separation of operational and analytics workloads
Adopting an enterprise data warehouse, companies eliminate running analytics queries against OLTP databases, which are extremely slow and can result in system failures, and improving analytics speed and accuracy.
Centralized data governance and management
Setting up a unified approach to data governance and management alongside the enterprise data warehouse implementation, companies prevent data inconsistencies and redundancy, varying data quality, data access constraints, and compromised analytics results.
Automated data management
An enterprise data warehouse helps companies eliminate manual resource-consuming and error-prone data extraction, cleansing, and transformation, all while streamlining data management workflows and cutting operational costs.
Facilitated self-service BI
An enterprise data warehouse allows users to set up self-guiding data management and analytics and free up data teams from routine analytics and reporting activities as well as help business users easily obtain the insights they need.
Advanced analytics facilitation
An enterprise data warehouse serves as a solid and well-governed foundation for new analytics initiatives – big data analytics, predictive analytics, self-service BI, ML, and AI.
Do not delay your EDW transformation
As companies across industries try hard to embed data-driven insights into every business decision, customer interaction, and business process, data warehousing is gaining traction as a key enabler. There has never been a better time to get more value out of data, with the growing information volume, increasing computing powers, and more advanced and affordable technology. Since implementing an enterprise data warehousing solution requires solid expertise, consider bringing a trustworthy DWH consultant to your project. At Itransition, we offer a full range of DWH services to help companies design and introduce a scalable and future-proof data warehouse and use it to accelerate decision-making and gain a competitive advantage.
