Data Warehouse Implementation in 2022
ScienceSoft has been providing data warehousing services since 2005.
The Gist of Data Warehouse (DWH) Implementation
Data warehouse implementation implies developing and deploying a data warehouse to gather and structure company’s data for analytical querying and reporting.
Time: From 6-9 months
Data warehouse implementation steps: Feasibility study, discovery, data warehouse conceptualization and platform selection, business planning, data warehouse system analysis and architecture design, development and launch, support and evolution.
Cost: Starts from $70,000
Team: A PM, a BA, a DWH system analyst, DWH solution architect, data engineer, QA and DevOps engineers.
For 17 years, ScienceSoft has been designing and implementing efficient data warehouses that serve a solid foundation for robust BI solutions.
Trend 1. Moving to the cloud
- Scalability and flexibility of a cloud data warehouse.
The inherent scalability of a cloud data warehouse allows for easy adaptation to the changing amount of data and the required processing capacity. Thus, scaling the data volume up and down does not affect the performance of the data warehouse.
- Flexible pricing options.
Cloud providers offer flexible pricing models (e.g., pay-as-you-go) and discount opportunities for provisioned resources to meet their clients’ technical needs and budgets.
- Data availability.
Nearly all cloud data warehouses perform consistent backups automatically, which results in 99.99% data availability and fault tolerance.
Trend 2. Turning to Data Warehouse as a Service (DWaaS)
Minimizing data administration efforts.
When opting for DWaaS, you eliminate hardware and software acquisition, configuration and maintenance costs. As a DWaaS provider performs data warehouse administration and management, there is no need to hire an in-house team for managing the data storage infrastructure.
Trend 3. Big data integration into data warehouse
Combining historical business data with less structured data from big data sources (machine data, transactional data, public data, etc.) provides for uncovering hidden data patterns and correlations and getting insights that can drive business-improving actions, which is a huge step towards accurate forecasting and boosted profit.
The process of implementing a data warehouse is closely bound to particular business needs and objectives, so the data warehouse implementation steps may differ or merge depending on the project specificity and scale. Based on 17-year experience in delivering data warehousing solutions, ScienceSoft outlines some general steps that are typical of most data warehouse implementation projects:
Data warehouse feasibility study
Determining data warehouse implementation project viability considering:
- Business objectives (strategic and tactical) to achieve with data warehouse implementation.
- Company’s, departments’ and business users’ needs and expectations from the data warehouse implementation project.
- A high-level project management scenario with data warehouse implementation project deliverables, skills required, time and costs involved, potential problems.
We usually deliver a PoC to assess the data warehouse implementation viability. The estimated PoC delivery time is 2-3 weeks.
Business needs analysis and elicitation of high-level data warehouse requirements, including, but not limited to:
- Number of data source systems to be integrated.
- Source data volume and complexity, etc.
During the discovery step, our consultants analyze relevant documentation, interview and hold brainstorming sessions with all stakeholders to collect their needs, goals, and vision of the successful data warehousing project implementation. It helps understand their priorities, plan the development process accordingly and as a result - provide a satisfactory end product.
Data warehouse conceptualization and platform selection
Identification of the desired data warehouse feature set and optimal data warehouse software (DWH database, ETL/ELT tool) selection.
Factors to be taken into account:
- Data sources and data analytics infrastructure in use (if any).
- Number of data flows to be implemented.
- Data security requirements, etc.
- Defining data warehouse implementation project deliverables and timeframes.
- Designing project risk management and mitigation plans.
- Estimating data warehouse implementation costs and DWH TCO.
After evaluating data warehouse TCO, you are ready to estimate the return on investment (ROI) and choose the optimal deployment option ((multi-)cloud, on-premises or hybrid).
Our practice has shown that effective data warehouse implementation project planning can help reduce project time and budget by up to 30%. To achieve that, we carefully elaborate on the findings of the preceding stages.
Data warehouse system analysis and architecture design
Among key activities at this stage are:
- Detailed analysis of each data source with its data types, volume, structure, formats, and more.
- Identification and analysis of existing data quality issues.
- Building a valid data model.
- Identifying entities’ types (e.g., products, vendors, customers) and attributes (identifiers), relations between entities.
- Mapping objects, etc.
- Designing ETL/ELT processes for data integration and data flow control.
Development and stabilization
- Data warehouse platform customization.
- Data sources integration (including adaptation of source systems).
- Developing ETL/ELT pipelines (including data validation) and ETL/ELT testing.
- Data warehouse performance testing.
To deliver a data warehouse in the shortest time possible, in our projects we opt for DevOps-driven iterative development – it assures the quickness and frequency of releases without sacrificing the solution’s quality.
Data warehouse launch
Introducing the data warehouse solution to end users: depending on data warehouse size and complexity, it can be done on a step-by-step basis.
Data warehouse support and evolution
- Iterative data warehouse evolution (adopting changes in the source systems, expanding data flows, etc.)
- Supporting evolving storage and analytics needs by adding new data sources while preserving high data quality.
- ETL/ELT performance tuning.
- Monitoring/adjusting data warehouse performance and availability.
Consider Professional Services for Data Warehouse Implementation
ScienceSoft has been providing data warehouse services since 2005 and can help you build a data warehouse solution fully aligned with your business objectives with optimized investments involved.
Data warehouse implementation consulting
- DWH implementation feasibility study.
- DWH solution conceptualization and platform selection.
- DWH system analysis and architecture design.
- DWH solution implementation strategy.
- Optimal DWH implementation sourcing model.
Data warehouse implementation outsourcing
- DWH implementation feasibility study.
- DWH solution conceptualization and platform selection.
- DWH system analysis and architecture design.
- DWH solution development.
- DWH quality assurance and launch.
- DWH support and evolution.
- Data warehousing services since 2005.
- Data analytics expertise since 1989.
- Designing and implementing business intelligence solutions since 2005.
- A dedicated team of DWH solution architects, data engineers, DevOps specialists, database administrators, QA specialists.
- Expertise in delivering complex and large-scale solutions for 30+ industries.
ScienceSoft as a Trusted BI and Data Warehousing Tech 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.
The system created by ScienceSoft automates data integration from different sources, invoice generation, and provides visibility into the invoicing process. We have already engaged ScienceSoft in supporting the solution and would definitely consider ScienceSoft as an IT vendor in the future.
Heather Owen Nigl, Chief Financial Officer, Alta Resources
Cloud DWH and BI Solution for the Producer of Phytotherapy Products
- Consolidation of company-wide data from 5+ data sources into a cloud DWH.
- 20+ report templates to cater to different departmental needs.
- A set of user guides with the detailed information.
Cloud Data Warehouse for Big Data Analysis
- A big data warehousing solution to process 1,000+ raw data types.
- A 5-module analytics system for advertising channel analysis in 10+ countries.
- Up to 100 times faster analytical query processing.
DWH and Analytics for a Multibusiness Corporation
- 100 ETL processes to ingest structured and unstructured data from 15 sources in a data warehouse.
- An analytical server with 5 OLAP cubes and about 60 dimensions to enable retail analytics, stock management, etc.
- 90+ reports for different business directions and user roles.
DWH and an Analytics Solution for Advanced Sales Analysis
- A solution for a multinational FMCG corporation with more than 200 markets, 1 bn consumers, and 60,000 employees.
- A three-module BI solution for data unification and processing.
DWH and Analytics Solution for 500+ Nursing Homes
- An on-premises data warehouse solution to enable comprehensive standardized reporting.
- A universal 4-dimensional analytical cube.
- Role-based data access.
DWH and an Analytics Solution for a Regulatory Authority
- A centralized DWH solution and a BI module with an analytics sandbox to support experimental/development analytics activities.
- Support for 200+ concurrent business users handling over 500 reports simultaneously.
DWH and BI Consulting for an Agency with 1000+ Staff
- A concept of the solution to get data-driven insights for tactical and strategic decision-making, planning, and performance management.
- A set of detailed recommendations to consolidate disparate data sources in a data warehouse and save up to 90% of the report preparation time.
Data Warehouse Solution for Airline Market Data Analysis
- A data warehouse deployed in ScienceSoft’s data center.
- A 10-dimension OLAP cube to analyze the 10-year history of external data.
- Web-based reporting with self-service capabilities.
Data Warehouse and Analytics Solution for the Automotive Industry
- An ETL-based data warehouse with a staging area, DWH database and data marts.
- Multidimensional analytical cubes.
- 40+ customizable reports and dashboards.
Defines and communicates data warehouse implementation project objectives, manages project scope, costs, timing and quality.
Elicits and documents data warehouse solution’s functional and non-functional requirements (including data warehouse solution’s building blocks, integrations with data source systems, etc.), technical limitations (if any).
Data warehouse system analyst
Analyses data sources (and their dependencies) and data analytics software (if any) to be integrated with the data warehouse solution. Reviews data loaded into the data warehouse for accuracy.
Data warehouse solution architect
Draws up data warehouse architecture requirements. Designs data warehouse architecture that supports high availability, performance, scalability, and security of the data warehouse solution.
Develops a data model and its structures, draws up data flows (based on the system analyst’s input). Develops, tests and maintains a data pipeline routing source data to the data warehouse. Builds the ETL/ELT process.
Quality assurance engineer
Conducts data warehouse solution’s requirements analysis, defines a test strategy, and designs an optimal test environment to simulate real-time data warehouse scenarios. Executes test cases to evaluate functional and non-functional aspects of the data warehouse system.
Sets up the data warehouse software development infrastructure, automates and streamlines development and release processes by introducing CI/CD pipelines, monitors data warehouse performance, availability, and security.
In-house data warehouse implementation
The company has full control over a data warehouse implementation project.
Caution: Not to delay or compromise the project, there should be the sufficient amount of resources and expertise.
Technical resources are partially outsourced
Augmenting the in-house tech team with a vendor’s resources to perform such activities as data warehouse design, implementation or support. The company has substantial control over the implementation project.
Caution: High requirements to in-house competencies. Additionally, there should be effective communication between all stakeholders to avoid project delays.
Technical resources are fully outsourced
Minimized risk of the resource overprovisioning after the project completion.
Caution: High requirements to in-house PM and BA competencies.
In-house project sponsor, everything else is outsourced
Minimized risk of data warehouse implementation project delays or failures due to resource unavailability. A vendor takes on full responsibility for the data warehouse implementation project and all related risks.
Caution: Increased vendor-related risks due to high vendor dependency.
Why Build Data Warehouse Solutions with ScienceSoft
project time and budget costs due to thorough project management
up to 60%
less time for DWH solution maintenance due to optimal platform choice
up to 80%
reduction in cloud computing costs due to proper cloud configurations
Best for: petabyte-scale analytics
- Integration of structured, semi-structured, unstructured data types.
- SQL data querying (including big data).
- Integrations with the AWS ecosystem (including S3, AWS Glue, Amazon EMR) and third-party tools (Power BI, Tableau, Informatica, Qlik, Talend Cloud).
- Automated infrastructure provisioning, backups and cluster health monitoring.
- Federated query support and result caching.
- ML-optimized performance under varying workloads.
- Data encryption in transit and at rest and fine-grained access control.
- Separate scaling of compute and storage.
- On-demand pricing: $0.25/hour (dc2.large) - $13.04/hour (ra3.16xlarge).
- Reserved instance pricing can save up to 75% over the on-demand option (in a 3-year term).
- Data storage (RA3 node types): $0.024/GB/month.
Azure Synapse Analytics
Best for: advanced data management
- SQL querying of structured, semi-structured, unstructured data types.
- Multilanguage support (T-SQL, Python, Scala, Spark SQL, .Net).
- Native integrations with Apache Spark, Power BI, Azure ML, Azure Stream Analytics, Azure Cosmos DB, etc.
- Integration with third-party BI tools, including Tableau, SAS, Qlik, etc.
- Result-set caching.
- Automatic restore points and backups.
- End-to-end data encryption, dynamic data masking, granular access control.
- Compute on-demand pricing: $1.20/hour (DW100c) - $360/hour (DW30000c).
- Compute reserved instance pricing can save up to 65% over the on-demand option (in a 3-year term).
- Data storage: $122.88/TB/month.
Oracle Autonomous Data Warehouse
Best for: high-speed query processing
- Querying across structured, semi-structured, unstructured data types.
- Connection with custom applications and third-party products via SQL*Net, JDBC, ODBC.
- Connectivity to Oracle Cloud Infrastructure Object Storage, Azure Blob Storage, Amazon S3.
- Native integration with Oracle Analytics Desktop.
- Deployment flexibility (Oracle public cloud (shared/dedicated infrastructure) or a customer’s data center).
- Automated scaling, performance tuning, patching and upgrades, backups and recovery.
- Independent storage and compute scaling.
- Data encryption at rest and in transit.
- Multifactor authentication.
- Compute: $1.3441/CPU/hour.
- Data storage: $118.40/TB/month (in the public cloud).
Get all the information you need to choose an optimal data warehouse technology for your project in our free guide.
A data warehouse implementation project, which involves developing a 10GB data warehouse with data integration and data cleansing processes, may vary from $225,000 to $485,000 (excluding software licensing and other regular fees).
The major factors that influence data warehouse implementation costs are:
- Number of data sources (ERP, CRM, SCM, etc.) and their complexity (whether a data model description exists or not; if the data model differs from the new data warehouse data model).
- Data volume.
- Complexity of data cleansing.
- Number of data tables and columns used for analysis.
- Required security level and policies.
- Data warehouse velocity, scalability, and fault tolerance.
ScienceSoft is a global IT consulting and software development company headquartered in McKinney, TX, US. Since 2005, we’ve been helping companies handle and benefit from data with a full range of data warehousing services, including data warehouse consulting, data warehouse implementation, data warehouse migration and support, and Data Warehouse as a Service (DWaaS). Being ISO 9001 and ISO 27001-certified, we rely on a mature quality management system and guarantee cooperation with us does not pose any risks to our customers’ data security.