Data Integration Overview

Author: Kepion Product Team

Data Integration Process

The data integration process (a.k.a. Extract, Transform, Load (ETL) process) refers to pulling data into and pushing data out of an application. Each Kepion Application has a SQL database that can be used for data integration. Data integration will involve at least one of the following tables:

• Dimension Tables: Includes any table that begins with D_, DO_, or DS_. The D_ tables store the master list of Dimension Members; the DO_ tables store the ordering information of the Dimension Members; the DS_ tables store the Dimension Members' staging data. 
• Hierarchy Tables (a.k.a. Member Lists): Includes any table that begins with H_ or HO_. The H_ tables store a subset of a D_ table's Dimension Members and their parent-child relationships. The HO_ tables store the hierarchy members' ordering information.
• Model Tables (a.k.a. Fact Tables): Includes any table that begins with F_. The F_ table stores all of a Model's fact records.

Note: A Model is defined by a set of Member Lists. Member Lists constrain the range of valid data for a Model. 

Model fact records are structured in a star schema. Looking at the example above, the F_ table is represented by the Model Table. It contains logical hierarchy keys to the H_ tables (represented by the Hierarchy Tables), which in turn have logical dimension keys to the D_ tables (represented by the Dimension Tables). 

Tip: Click here for our Data Integration Guide.

Data Integration Tools

For data integration tools, we have two recommendations: Azure Data Factory (ADF) and SQL Server Integration Services (SSIS).

Both ADF and SSIS have the necessary features for a data integration tool. Let's look at how they differ and their pros and cons. 

Azure Data Factory (ADF)

ADF, released in 2015, is a serverless data integration service based on Microsoft Azure. ADF has a collection of 'pipelines,' the equivalent to SSIS's project and package structures. 

ADF will suit your business if the following applies: 

• You have Kepion hosted on the cloud
• Your Kepion cloud is not integrated with your on-premise infrastructure

Integration Services (SSIS)

Launched in 2005, SSIS has long been the top data integration tool for businesses using Microsoft SQL Server. SSIS combines its functional units of data integration work into 'packages,' which can be run individually or in groups.

SSIS will suit your business if you have Kepion installed on-premises.

Tools Wrap Up

While we've compared ADF and SSIS, it's actually not an either/or choice. The best data integration solution for you could be SSIS, ADF, or some combination of the two. Take the necessary time to consider your business's current and future needs and what tool(s) best aligns with them.