Data Integration Best Practices
By Kepion Solution - Apr 02, 2013
Each Kepion application comes with its own relational database. All ETL can be performed on the application database and may include the following tables:
- Dimension Tables: Include any table that begins with D_ and DO_. The D_ tables store the main records for the dimension while the DO_ store the ordering information of the dimension members.
- Hierarchy Tables: Include any table that begins with H_ and HO_. The H_ tables store the parent child (PC) hierarchy member relationships while the HO_ store the ordering information of the PC hierarchy members.
- Model Tables: Include any table that begins with F_. The F_ table stores all the fact records found within a model sliced by the dimension members from the hierarchy table.
Fact records are stored within the application database in a star schema. Looking at the example above, the fact table for the model is represented by the Model Table and contains logical hierarchy keys to the H_ tables as represented by the Hierarchy Table which in turn have logical dimension keys to the D_ tables as represented by the Dimension Table.
Overview of Data Integration Process
When loading the initial set of data to the Kepion application, perform the following in order:
- Load data to staging tables (optional)
- Load all dimensions
- Load all hierarchies
- Load all models
Illustrated below are two methods that can be performed by a data integrator for ETL. Note that other methods are also possible.
1. Load from source system directly to application tables.
2. Load from source system to staging tables and from staging tables to application tables.
If staging tables are desired for use in the ETL process, you can use the application’s staging tables that are created along with each D_, H_, and F_ table. Note that these tables are provided solely to assist in an ETL process and are not required to be used.
Data Integration Tools
It is recommended to use Microsoft SQL Server Integration Services (SSIS) packages to perform ETL. To create and manage a data integration process, open the Microsoft Business Intelligence Development Studio (BIDS).