How to Set Up Data Consolidation

• Gathering business objectives that the data consolidation solution needs to meet, for example consolidate data in a central storage for analytics and reporting, enhance data quality and security, set up master data management, etc. These goals are further prioritized and classified into two groups – core and optional.
• Outlining a high-level project management scenario, including deliverables, skills required, time and budget, potential risks, etc.

Outlining business requirements and determining the high-level scope of a data consolidation solution by:

• Reviewing data sources to be integrated.
• Mapping out the current data flow.
• Discovering and describing connectivity between systems.
• Discovering and describing established data security practices.
• Conducting preliminary analysis of data in the source systems (defining data type and structure, volume, etc.) and data quality across them.

• Defining the optimal approach (ETL vs ELT) for replicating data from source systems to the final destination.

  ScienceSoft's best practice: In our projects, we use the ETL approach for consolidating smaller datasets of structured data, and the ELT approach to consolidate high data volumes of various structure.

• Forming solution architecture vision and selecting the preliminary tech stack, taking into account:
  • Source systems (how many systems, what data types they contain, how often they are updated, etc.).
  • Data volume to be consolidated.
  • Data flows to be implemented.
  • Data security requirements, etc.

ScienceSoft's best practice: Not to overcomplicate the architecture, we set up close cooperation of ScienceSoft's business analyst and solution architect with business users for the accurate definition of the solution’s requirements.

• Defining data consolidation project scope and timeline, estimating efforts for the data consolidation project, TCO and ROI.
• Outlining project risks and developing a risk management plan.
• Drawing up data consolidation project documentation (scope statement, deployment strategy, testing strategy, project implementation roadmap, etc.).

• Detailed data profiling and master data management:
  • Tagging data with keywords, descriptions, or categories.
  • Assessing data quality against the metrics set by business users.
  • Discovering metadata and assessing its accuracy.
  • Assessing risks of data consolidation, etc.

    ScienceSoft's best practice: In projects where the source data is locked within custom-built legacy systems or is entered manually, we put much effort into data profiling activities.

• Designing each data layer, its internal architecture (landing, staging, processing, storing, analytical) and ETL/ELT processes for data consolidation and data flow control.

• Designing a data quality management framework (data cleansing, enrichment, deduplication, etc.).
• Designing a data security framework (data access policies and policies for data access monitoring, data encryption policies, etc.).

ScienceSoft's best practice: Together with data consolidation architecture design, we design data stores (data warehouses, data marts, etc.) and map data objects from source systems to destination stores.

ScienceSoft's best practice: When the architecture is outlined, we define the final tech stack for each component of the data consolidation solution.

• Data consolidation architecture development and testing.
• Developing ETL/ELT pipelines and ETL/ELT testing.
• Implementing data quality management, data quality validation.
• Implementing data security policies.
• Developing data models and structures.
• Data consolidation solution testing and stabilization.

• Deploying the data consolidation solution.
• ETL/ELT performance tuning.
• Adjusting data consolidation solution performance and availability, etc.
• After-launch support of the solution and end users.