Saturday, March 15, 2014

How to automatically load data into TM1 database without data warehouse

Type 1: Data is loaded from well defined data warehouse. All data in data warehousing are well developed based on dimension modelling concept, where SCD 1, SCD 2 and missing dimension and hierarchy handled. The TI process in TM1 side is very easy to implement.

Type 2: data is loaded from TM1 worksheet, all logic of data integration is handled in worksheet. It should not have any problems with missing dimensions and hierarchy.

Type 3: data is directly loaded from transactional database. This type is most complicated process, as loading process must not only move data into TM1, but also handle all logic with missing dimensions and hierarchy. Furthermore, a well define cube structure and a transformation of data into cube are needed.

This document will focus on type 3 and intended to share some experience of implementation. Type 3 occurs frequently in TM1 implementation in many sales budgeting and forecasting, while there is no central data warehousing in house.

The basic approach

Similar as implement data warehouse, some of dimensions such as SKU and Customer are not in Cognos yet. There are three ways to handle this problem:

1. Hold the load until we resolve missed SKU and Customer. This is manual process, in addition, we need to build interface somewhere to allow user access, which will result in a big effort.

2. Let the load go with warning. This is NOT an option, as the loaded data is NOT consistent with your source.

3. Insert an element into dimension, let loading process go through. For example one customer is not available in dimension, and then the customer will be added into dimension. However, the hierarchy is still missed.

The third approach is a practical approach. We can improve this approach with some intelligence.

Solution overview

The solution is to leverage TI process as follows:

Make loading process with intelligence

Create a powerful exception mechanism

Implement TI procedure to handle complicated exception

Provide a user interface to allow user to fill in missing data (will be described in separated blog )

Implementation

Traditionally, TI process to load data from database is very easy to build by simply applying map. However, it can only work when the process is simple. Below are a few solutions to share:

Make the TI process as similar process in data warehouse, loading dimension first and then load fact

This solution will make sure that all dimensions are ready before you load fact. This approach will provide a clear logic. Please see sample below

The dimension information is not consistent between Cognos database and source. The idea is to current Cognos dimension information, not data from data source. There are two reasons: 1) Change sales information will have big impact on current budget, as Budget and Sales are at different sub category level. 2) It is very difficult to change on data mart. Sample code below resolve two issues: 1) in case when Item number (or SKU) is database, then do not change dimension at all; 2) for the second level hierarchy, it will follow another hierarchy for budget and make sure both dimensions for different levels are consistent

Sometimes, we need to make attribute as separated dimension for sales budgeting and forecasting. For example, brand is an attribute of SKU dimension, however, we need to make brand as separated dimension for budget and Profit and Loss. So behind the scene, we still need to make sure that Brand is from attribute for sales. There will be two processes to set attributes right 1) Update Attribute Brand in SKU dimension from Sales cube, and 2) Update Attribute Brand in SKU dimension from Sales source data. When loading sales, we use attribute as element. See sample data below

Implement TI procedure to handle complicated exception

For each exception, there will be TI process to handle it. Some TI process can be very complicated. For example, manager realignment is complicated process, as we need to convert all historical sales and budget data. The appreciated approach is to use some common modular procedure to handle it, such as update elements. Please see sample below