Can't find what you need?

Enterprise Data Warehouse

Architecture, Types, Features, Benefits

Enterprise Data Warehouse (EDW)

ScienceSoft has been rendering a full range of data warehousing services since 2005.

Data Warehousing Services
Enterprise Data Warehouse (EDW)

What Is an Enterprise Data Warehouse: Core Concepts

An enterprise data warehouse (EDW) is a system for structuring and storing all company’s business data for analytics querying and reporting. The enterprise data warehouse integrates with a data lake, ML and BI software and its implementation costs start from $200,000 for a midsize business.

To enable answering both enterprise-level and department-specific questions, the enterprise data warehouse ingests data from all corporate business-critical software and external data sources, including:

  • Enterprise resource planning (ERP) system.
  • Customer relationship management (CRM) system.
  • Accounting software.
  • Talent management system.
  • Business process management (BPM) system.
  • Intranet.
  • The company’s website.
  • IoT device management system.
  • Publicly available datasets for machine learning, etc.

Data Warehouse vs Enterpise Data Warehouse

DWH

  • Stores data for particular business units
  • Answers department-specific questions

Enterprise DWH

  • Consolidates and stores data for all business units
  • Answers enterprise-level and department-specific questions

Enterprise Data Warehouse Solution Architecture

An enterprise data warehouse is a core element of a BI solution, which is structured in accordance with the particular data management and analytics needs a company pursues. Here, you may see common architectural components of the solutions that ScienceSoft delivers:

Sample Enterprise DWH architecture - ScienceSoft

A data source layer

Data from internal and external data sources.

A staging area

An intermediate storage area of temporary nature for data processing under the extract, transform and load (ETL) process. ETL consolidates data from multiple sources and transforms it into a modeled format suitable for storing in the enterprise DWH. Cloud-based enterprise data warehouses, due to their scalability, use ELT (extract, load, transform), which means that the transformation step is performed after data loading into an enterprise data warehouse.

Data storage layer

Centralized storage where data is made accessible for analytics (querying, reporting) and sharing.

Analytics and BI

Data in the enterprise data warehouse can be queried via OLAP tools, data mining tools, reporting and visualization tools.

Enterprise Data Warehouse Types

There are three deployment environment types for data warehousing solutions:

  • On-premises – a company purchases all required hardware and software to build and deploy an enterprise data warehouse and maintains it further on.
  • Cloud-hosted – a company deploys an enterprise data warehouse in the cloud, eliminating the need to purchase and maintain hardware and software.
  • Hybrid – a company augments an on-premises enterprise data warehouse with a cloud-hosted repository.

On-premises

Pros:

  • Full control over the enterprise data warehouse. In case of a failure, an in-house IT team has direct access to the DWH’s problem area for hardware and software tuning. Moreover, data security remains strictly under the in-house IT team’s control.
  • Full compliance with the required data standards. Data security compliance is easier to achieve with on-premises enterprise DWHs.
  • Availability. Business users from a facility where the enterprise data warehouse is located can effectively access all the data stored in the data warehouse without the dependence on the internet connection.

See the points of caution

Caution:

  • Full responsibility. Together with the control of the on-premises enterprise data warehouse, a company is fully responsible for its implementation and maintenance.
  • Complexity of agile scaling. To comply with the increased storage or compute requirements, you need to purchase new hardware, which may result in the need to tune or replace current software.

Hide

Cloud

Pros:

  • Scalability. The inherent agility of cloud data warehouses allows upscaling and downscaling with no impact on the enterprise data warehouse performance.
  • Reduced costs. There’re no hardware-related costs (hardware acquisition, deployment, maintenance, administration, etc.). And if you opt for Enterprise Data Warehouse as a Service, all software acquisition and maintenance costs are eliminated either.

See the points of caution

Caution:

  • Data compliance. Although most cloud providers have security features difficult for attackers to penetrate, some industry standards and regulations (FDA, HIPAA, etc.) still require sensitive data to be stored on-premises.
  • The risk of budget overruns. Unexpectedly increased query volumes, which require additional compute/storage resources, lead to overspending if no controlling or limiting the cloud resources is set up.

Hide

Hybrid

Pros:

  • Cloud flexibility. Meeting storage and compute requirements with the near-unlimited cloud resources.
  • Data compliance. Ensuring sensitive data is stored within the environment, which fully meets data compliance standards.

See the points of caution

Caution:

  • DWH costs. The company has to cover the maintenance costs and operating expenses of the on-premises DWH system while still paying the subscription fee for cloud DWH services.

Hide

Enterprise Data Warehouse Key Features

ScienceSoft creates enterprise data warehouse solutions with unique functionality closely bound to our clients’ objectives. Here, we share a set of features commonly requested by our customers:

Data integration and management

  • Data integration with ETL/ELT.
  • Full and incremental data extraction/load.
  • Structured, semi-structured, unstructured data ingestion.
  • Big data ingestion.
  • Streaming data ingestion.
  • Data loading and querying using SQL.

Data storage

  • Subject-oriented data repository.
  • Time-variant (data from the historical point of view) data repository.
  • Nonvolatile (read-only) data repository.
  • Granular data storage.
  • Metadata storage.
  • Storage in multiple environments (cloud, on-premises, hybrid).

Database performance

  • Scalability.
  • Automated DWH maintenance tasks – backups, replication, patching, etc.
  • Advanced data searching (materialized view support, data indexes, result-caching, etc.).

Security and compliance

  • Data encryption.
  • Securing data access with user authentication and authorization.
  • Granular access control (row- and column-level).
  • Compliance with national, regional, and industry-specific regulations (for example, GDPR, HIPAA, PCI DSS).

Need to Consolidate Your Corporate Data?

ScienceSoft is ready to establish a highly effective enterprise data warehousing solution for you to integrate disparate data sources under one roof and enhance your decision-making with company-wide analytics.

Alex Bekker

Alex Bekker, Head of Data Analytics Department at ScienceSoft, describes key integrations with an enterprise data warehouse:

“To enable cost-effective storage of enterprise data, advanced self-service analytics and reporting capabilities, ScienceSoft recommends integrating the enterprise data warehouse with a data lake, self-service analytics software and ML software.”

Data lake

To keep huge sets of structured, semi-structured, and unstructured data in a data lake and export processed data into a data lake to analyze it with ML, big data analytics, etc. services.

Self-service analytics software

To enable business users to make decisions based on timely and relevant reports, queries and analysis customized and conducted according to their own needs.

Machine learning software

To enable data scientists to build machine learning models with processed and cleaned data from the enterprise data warehouse to predict a company’s revenue, assess financial risks, forecast market trends and the company’s performance, etc.

Consider Professional EDW Services

Having 17+ years of hands-on experience in delivering DWH solutions, partnerships with global technology leaders (including Microsoft, Amazon and Oracle), we know how to deliver tailored EDW solutions that help our clients meet their tactical and strategic business objectives.

EDW consulting and implementation

To help you establish an EDW solution, we cover:

  • Business needs analysis and requirements elicitation.
  • EDW implementation strategy design.
  • EDW configuration and development.
  • EDW integration.
  • Data management procedures.
  • User training.
  • EDW software maintenance and adaptation. 
  • EDW support and administration (if required).
Go for EDW consulting and implementation

EDW as a Service

If you want to skip the complexities of EDW development, implementation and management and just store data for your self-service analytical solution, we are ready to customize an enterprise data warehouse and rent it out to you on a subscription fee basis.

Explore EDWaaS

ScienceSoft as a Reliable EDW Implementation Partner

When we first contacted ScienceSoft, we needed expert advice on the creation of the centralized analytical solution to achieve company-wide transparent analytics and reporting. After a series of interviews, ScienceSoft’s consultants analyzed our workloads, documentation, and the existing infrastructure and provided us with a clear project roadmap.

They stayed in daily contact with us, which allowed us to adjust the scope of works promptly and implement new requirements on the fly. Additionally, the team delivered demos every other week so that we could be sure that the system aligned with our business needs.

Heather Owen Nigl, Chief Financial Officer, Alta Resources

Factors Leading to the Enterprise Data Warehouse Success

To maximize enterprise data warehouse ROI for the customers, ScienceSoft focuses on the following factors:

Out-of-the-box integrations with data sources; SDKs in most common programming languages for reduced development costs.

Automation of enterprise data warehouse maintenance and administration tasks (ETL monitoring, managing data quality and data security, etc.) to decrease operational costs.

Enterprise data warehouse stability and availability to quickly access business-critical data in a centralized location.

High security and data protection standards of the enterprise data warehouse.

Secure Your Enterprise DWH Success with Testing

ScienceSoft offers optimal data warehouse testing coverage to ensure the reliability and high performance of your DWH, enterprise data resilience, and proper functioning of the integrated infrastructure.

EDW Implementation: Success Stories by ScienceSoft

Development of an EDW and a BI Solution for the Producer of Phytotherapy Products

ScienceSoft integrated disparate data sources in a cloud EDW solution for the Customer to achieve improved visibility into internal business processes and conduct company-wide reporting and analysis.

Development of an EDW solution for an FMCG corporation

ScienceSoft developed a data management and analytics platform for an FMCG corporation with more than 200 markets, 1 billion consumers and 60,000 employees, which helped the Customer distribute 100 SKUs through 10 large retail chains and 10,500 stores.

Development of an EDW and a BI Solution for 200 Healthcare Centers

ScienceSoft developed an EDW and an analytics solution to help 200 US healthcare centers and retirement homes process patient and medication data and optimize their management processes due to prompt analytics reports.

Implementation of an EDW and BI Solution for an International Real Estate Developer

ScienceSoft developed an analytics platform allowing the Customer to integrate the data from disparate sources and better understand their business with a comprehensive financial analysis.

Development of an EDW and an Analytics Solution for a Multibusiness Corporation

ScienceSoft developed a centralized data management platform for the Customer to get a 360-degree customer view, optimize stock management, and assess employees’ performance.

Development of an EDW and an Analytics Solution for a Regulatory Authority

ScienceSoft developed a robust data management and analytics solution to automate data flow management and obtain company-wide reporting. The Customer’s employees can now conduct comprehensive financial analysis and perform proactive capital markets regulation.

Enterprise Data Warehouse Key Benefits

Reduced time to insight due to consolidated corporate data ready for analysis.

Increased productivity of business users and the elimination of the communication bottlenecks across departments due to quick and easy role-based access to structured and high-quality data.

Saved time of IT staff and data analysts due to automated data management procedures (data collection, transformation, cleansing, structuring, modeling, etc.).

Enhanced data accuracy, consistency and security due to the centralized data governance approach.

A solid foundation for advanced analytics initiatives.

Expansion of data literacy across the enterprise.

Enterprise Data Warehouse Implementation Costs

The major factors that influence the cost of the enterprise data warehouse implementation project are:

  • Number of data sources (ERP, CRM, SCM, etc.)
  • Data disparity across different sources (for example, difference in data structure, format, and use of values).
  • Data source complexity (for example, big data, streaming data).
  • Data volume to be processed and stored.
  • Data sensitivity and data security requirements.
  • Number of data flows and the number of entities (“clients”, “salary”, “transactions”, etc.) to be integrated into the data warehouse.
  • Data warehouse performance requirements (velocity, scalability, etc.).

Based on ScienceSoft’s experience in EDW software implementation, the approximate timeframes for the EDW implementation project are from 3 to 12 months and the cost of an enterprise data warehouse implementation project may vary as follows:

  • 200 – 500 employees: $70,000 – $200,000*.
  • 500 – 1000 employees: $200,000 – $400,000*.
  • 1000+ employees: $400,000 – $1,000,000*.

*Monthly software license fee and other regular fees are NOT included.

Ballpark timelines for each stage of EDW implementation

A typical ScienceSoft's project on EDW software implementation covers the following stages and timelines:

  • EDW goals elicitation: 3-20 days.
  • EDW solution conceptualization and tech stack selection: 2-15 days.
  • Business case and project roadmap creation: 2-15 days.
  • System analysis and EDW architecture design: from 15 days.
  • EDW solution development and stabilization: from 2 months.
  • EDW solution launch: from 2 days.
  • After-launch support, maintenance, and evolution: as requested.

HIDE

EDW Solutions ScienceSoft Recommends

The selected platforms are recognized leaders in enterprise data warehousing solutions (The Forrester Wave, Gartner Magic Quadrant), which are fully compliant with the key criteria for an enterprise-scale DWH: almost instant scalability of compute and storage resources (due to the cloud-based nature), high performance and availability (up to 99.99% uptime), advanced security, etc.

Azure Synapse Analytics

Description

A scalable data warehousing solution with a node-based architecture, which employs parallel query processing to achieve fast query response time and high query throughput. Azure Synapse unifies the Azure Data Lake storage and the SQL data warehouse to allow direct querying of raw data and combining relational and non-relational data for deeper analytics insight.

Data security

Dynamic data masking, built-in authentication, authorization, data encryption, etc.

Pricing

  • Data storage $122.88 per TB/month ($ 0.17/TB/hour). The data storage size includes your DWH data and 7 days of incremental snapshot storage.
  • Query performance pricing depends on the service level and region.

Amazon Redshift

Description

A scalable data warehousing service, which achieves great performance due to such features as massively parallel processing, columnar data storage, query optimizer, result caching, etc. With the Redshift Spectrum feature it is possible to query data directly from Amazon to enable data lake analytics.

Data security

End-to-end encryption, granular access controls, network isolation, etc.

Pricing

The price is charged according to the amount of stored data and the number of nodes. The on-demand pricing option starts from $0.25/hour (hourly rate based on the type and number of nodes in the cluster).

Google BigQuery

Description

A scalable data warehousing solution backed up with the Dremel technology designed to instantly run queries on massive structured datasets.

Data security

Data encryption, Google’s virtual private cloud policy controls, etc.

Pricing

Storage costs$0.02/GB/mo ($0.01/GB/month for long-term storage).

Streaming inserts: $0.01/200 MB.

For query performance, 2 subscription options are available:

  • Pay-as-you-go ($5/TB, 1st TB/month is free).
  • Flat-rate pricing (from $10,000/ month for a dedicated reservation of 500 processing units).

Expertly Choose Enterprise DWH Software

Study ScienceSoft’s practical guide to choose the right technology for your enterprise DWH project, minimize data warehouse total cost of ownership and maximize its performance.

About ScienceSoft

ScienceSoft is a global IT consulting and IT service company headquartered in McKinney, TX, US. Since 2005, we render data warehouse consulting services to support our clients’ agile and data-based decision-making. Being ISO 27001-certified, ScienceSoft guarantees cooperation with us does not pose any risks to our customers' data security.