Data Integration Guide – Kepion Support Center

This guide explains how to perform data integration (a.k.a. extract-transform-load (ETL)) from a data source to a Kepion SQL database.

Before you Start

Skill Set Requirements

SQL knowledge
Familiarity with Microsoft SQL Server Management Studio

Tool Requirements

Microsoft SQL Server 2016+
Microsoft SQL Server Management Studio

Note: Refer to our Data Integration Overview to learn about data integration basics and tools.

Data Integration Concepts

Each Kepion Application has a SQL database that can be used for data integration. There are three concepts you need to know:

Data Sources
Staging Tables
Target Tables

Data Sources

Data sources are where you extract the data you wish to transfer and load it to Kepion databases. Some potential data sources are:

Excel Workbooks
SQL databases

Staging Tables

Staging tables are an intermediate workspace used for data processing before loading your data to your target tables. Typically, when the data integration process runs, staging tables are truncated (i.e., data is cleared) before they are populated with the loading data.

Dimension Staging Tables

Includes tables starting with DS_ (Dimension Staging) and DSE_ (Dimension Staging Error). These tables are used in conjunction with the Dimension data loading Stored Procedure (SP):

[dbo].[sp_Load_Dimension_From_Staging]

Attention: You must go through the Dimension loading process to update your Dimension data. This process ensures Dimension MemberIds remain consistent with your Application's assets (e.g., Forms, Rules, etc.).

Model Staging Tables

Includes tables starting with Staging_F. These tables are pre-created for convenience but are optional in the data integration process. You can also use the target F_ (Model/Fact) tables directly. Be aware that changes made to an F_ table will impact the Application.

Target Tables

The target tables hold the Application's data. There are three types of target tables: Dimension, Hierarchy, and Model.

Note: Model data are structured in a star schema. The F_ table contains hierarchy keys referencing H_ tables, which in turn have dimension keys referencing D_ tables.

Dimension Tables

Includes any table that begins with D_ or DO_. The D_ tables store the list of Dimension Members; the DO_ tables store the ordering information of the Dimension Members.

Attention: Avoid truncating records in the D_ table as you may lose MemberIds. However, if there are no fact records or assets (e.g., Forms, Rules, etc.) tied to the MemberIds, there is no concern.

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 any Parent-Child (PC) relationships. The HO_ tables store the hierarchy members' ordering information.

Note: Each PC hierarchy (i.e., Member List in Hierarchy Mode) has a V_H_ view and SysCache_V_H table to optimize application performance. When updating H_ tables, truncate SysCache_V_H_ and re-insert the records from V_H_.

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. There are two types of F_ tables that can belong to a Model:

Writeback – Stores data that will change based on user input. For example, user-entered Forecast records are stored in the Writeback table.
Non-Writeback – Stores all non-user-entered data. For example, Actuals are stored in the Actuals table, not in the Writeback table.

Tip: The FH_ table is a Model Table that logs all user-entered data when the Audit option is enabled. Refer to our audit trail article for more information.

Data Integration Process

The data integration process occurs in two steps:

Extracting data from a source system and loading it to staging tables.
Loading data from the staging tables to target tables.

Tip: Before starting data integration, ensure you have a backup of your application database in case of accidental data updates.

As a rule of thumb, execute data integration in the following order:

1. Update Dimension

2. Update Hierarchy

3. Update Model/Fact

Click the links above to go to how-to articles dedicated to each of the three steps of the data integration process.

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

Pros	Cons
Cloud-integrated and works with most cloud data sources	Pricing scheme can be complex depending on the services your company needs
No installation, machine, or SQL server required

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.

Pros	Cons
Easily connects with Microsoft tools	Limited integration with cloud data sources and non-Microsoft tools
Long-established product	May require additional components and/or licensing
One-time payment. No subscription or service charges.

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.