This guide explains how to perform data integration (a.k.a. extract-transform-load (ETL)) from a data source to a Kepion SQL database.
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 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 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):
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.
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.
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.
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:
Click the links above to view how-to articles on the three steps of data integration.
For data integration tools, we have three recommendations:
SSMS is the essential tool for data integration with Kepion databases. You must have it in order to conduct advanced data integration processes.
Azure Data Factory
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
|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|
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.
|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.|