There are three type of loading data into TM1:
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
#dimensions
ExecuteProcess('ODBC - Load Sales Manager (Monthly)','pYear',pYear,'pMonth',pMonth);
ExecuteProcess('ODBC - Load Dim Sales Manager - Sales Budget (Monthly)','pYear',pYear,'pMonth',pMonth);
ExecuteProcess('ODBC - Load Dim Sales Manager - PL (Monthly)','pYear',pYear,'pMonth',pMonth);
ExecuteProcess('ODBC - Load Dim Customer (Monthly)','pYear',pYear,'pMonth',pMonth);
ExecuteProcess('ODBC - Load Dim Category - Sales Budget (Monthly)','pYear',pYear,'pMonth',pMonth);
ExecuteProcess('ODBC - Load Dim Category (Monthly)','pYear',pYear,'pMonth',pMonth);
ExecuteProcess('ODBC - Load Dim QSO (Monthly)','pYear',pYear,'pMonth',pMonth);
ExecuteProcess('ODBC - Load Dim Brand (Monthly)','pYear',pYear,'pMonth',pMonth);
ExecuteProcess('ODBC - Update Attribute SubBrand in Category (Monthly)','pYear',pYear,'pMonth',pMonth);
#Fact
ExecuteProcess('ODBC - Load Sales (Monthly)','pYear',pYear,'pMonth',pMonth);
#exception
ExecuteProcess('Exceptions generated','pYear',pYear,'pMonth',pMonth);
Make dimension loading with intelligence
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
Under metadata tab
#****Begin: Generated Statements***
vAllCategory='All Category';
vItemName=ITEMNMBR |' ' | ITEMDESC;
#****End: Generated Statements****
IF (DIMIX('Category', ITEMNMBR) = 0);
DIMENSIONELEMENTINSERT('Category','',SubCategory,'c');
DIMENSIONELEMENTINSERT('Category','',vAllCategory,'c');
DIMENSIONELEMENTCOMPONENTADD('Category',SubCategory,ITEMNMBR,1.000000);
p=1;
While(p<=ElparN('Category - Sales Budget',SubCategory));
vParent=Elpar('Category - Sales Budget',SubCategory,p);
DIMENSIONELEMENTINSERT('Category','',vParent,'c');
DIMENSIONELEMENTCOMPONENTADD('Category',vParent,SubCategory,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Category',vAllCategory,vParent,1.000000);
p=p+1;
End;
ENDIF;
Make fact loading with intelligence
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
#****Begin: Generated Statements***
vMeasure='Amount';
#****End: Generated Statements****
CellPutN(CellGetN('Sales',vYear,vMonth,vSM,vQSO,ATTRS('Category', SKU, 'SubBrand') ,vCustomer,SKU,vMeasure)+vValue,
'Sales',vYear,vMonth,vSM,vQSO,ATTRS('Category', SKU, 'SubBrand') ,vCustomer,SKU,vMeasure);
Create a powerful exception mechanism
Exception handling is extremely important, as some are really errors, and some are warning. All these message are saved as exception cube, please some of checks below
#1) Missing dimension
d=1;
While(d<=Dimsiz('}Dimensions'));
vDim=Dimnm('}Dimensions',d);
If(Scan('#' | vDim | '#',pCheckDimensions)<>0 & (Subst(vDim,1,1) @<> '}'));
e=1;
While(e<=Dimsiz(vDim) );
vElem=Dimnm(vDim,e);
If(vElem@='Unknown' );
CellPutS('Error', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Error Warning');
CellPutS('Missing dimension', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Kind');
CellPutS( vDim, 'Admin Exception', NumberToString(vExceptionNo), 'Exception Dimension');
CellPutS( vElem, 'Admin Exception', NumberToString(vExceptionNo), 'Exception Element');
vExceptionNo = vExceptionNo+1;
EndIf;
e=e+1;
End;
EndIf;
d=d+1;
End;
#2)Multiple parent hierachy
………………………..
#3) No parent
………………………….
#4) Data inconsistency
………………….
IF ( ABS( vTotsalSales-vAmountInSalesCube ) >1.0 % ABS(vTotsalSales-vPriorYearActualInBudgetCube ) >1.0 % ABS(vTotsalSales - vPriorYearActualTotalNetSalesInPLCube) >1.0);
CellPutS('Error', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Error Warning');
CellPutS('Sales Data inconsistent', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Kind');
CellPutS( 'Sales from Data Mart =' | NumberToString( vTotsalSales) , 'Admin Exception', NumberToString(vExceptionNo), 'Exception Detail');
vExceptionNo = vExceptionNo+1;
CellPutS('Error', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Error Warning');
CellPutS('Sales Data inconsistent', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Kind');
CellPutS( 'Sales in Sales Cube =' | NumberToString(vAmountInSalesCube) , 'Admin Exception', NumberToString(vExceptionNo), 'Exception Detail');
vExceptionNo = vExceptionNo+1;
CellPutS('Error', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Error Warning');
CellPutS('Sales Data inconsistent', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Kind');
CellPutS( 'Sales in Budget Cube ='|NumberToString(vPriorYearActualInBudgetCube) , 'Admin Exception', NumberToString(vExceptionNo), 'Exception Detail');
vExceptionNo = vExceptionNo+1;
CellPutS('Error', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Error Warning');
CellPutS('Sales Data inconsistent', 'Admin Exception', NumberToString(vExceptionNo), 'Exception Kind');
CellPutS( 'Sales in PL Cube ='|NumberToString(vPriorYearActualTotalNetSalesInPLCube) , 'Admin Exception', NumberToString(vExceptionNo), 'Exception Detail');
vExceptionNo = vExceptionNo+1;
ENDIF;
#5) Missing hierachy
…………………..
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
## 0) Check valid customer and managers
………………….
##1) Add new manager into Sales manager – Sales budget
vOldElement = pOldSalesManager | '-' | pCustomer;
vOldName =ATTRS('Sales Manager - Sales Budget', vOldElement, 'Name');
vNewElement = pNewSalesManager | '-' | pCustomer;
vNewName = pNewSalesManager | Subst(vOldName,LONG(pOldSalesManager)+1, LONG(vOldName)-LONG(pOldSalesManager)) ;
If( DimIx( 'Sales Manager - Sales Budget', vNewElement ) = 0 );
DimensionElementInsertDirect('Sales Manager - Sales Budget', pNewSalesManager, vNewElement ,'N');
AttrPutS(pCustomer, 'Sales Manager - Sales Budget', vNewElement, 'Customer');
AttrPutS(pNewSalesManager, 'Sales Manager - Sales Budget', vNewElement, 'Sales Manager');
AttrPutS(vNewName, 'Sales Manager - Sales Budget', vNewElement, 'Name');
DimensionElementComponentAdd('Sales Manager - Sales Budget',pNewSalesManager,vNewElement , 1);
Endif;
##2) Update Sales data for Sales
ExecuteProcess('Lib.Cube.Data.Update2Elements',
'pCube','Sales',
'pViewSource','',
'pViewTarget','',
'pDimension','Sales Manager',
'pSourceElement',pOldSalesManager,
'pTargetElement',pNewSalesManager,
'pSkipRules',1,
'pZeroTarget',0,
'pZeroSource',1,
'pDestroyTempObj',1,
'pDebug',0,
'pDimension2','Customer',
'pSourceElement2',pCustomer,
'pTargetElement2',pCustomer
);
##3) Update budget for Sales budget Input
ExecuteProcess('Lib.Cube.Data.Update1Element',
'pCube','Sales Budget Input ',
'pViewSource','',
'pViewTarget','',
'pDimension','Sales Manager - Sales Budget',
'pSourceElement',pOldSalesManager | '-' | pCustomer,
'pTargetElement',pNewSalesManager | '-' | pCustomer,
'pSkipRules',1,
'pZeroTarget',0,
'pZeroSource',1,
'pDestroyTempObj',1,
'pDebug',0
);
##4) Remove manager for Sales manager – Sales budget
DimensionElementDeleteDirect('Sales Manager - Sales Budget', pOldSalesManager | '-' | pCustomer);
##5) Remove Sales manager for Sales manager – PL
vOldName =ATTRS('Sales Manager - PL', pCustomer, 'Name');
vNewName = pNewSalesManager | Subst(vOldName,LONG(pOldSalesManager)+1, LONG(vOldName)-LONG(pOldSalesManager)) ;
AttrPutS(pCustomer, 'Sales Manager - PL', pCustomer, 'Customer');
AttrPutS(pNewSalesManager, 'Sales Manager - PL', pCustomer, 'Sales Manager');
AttrPutS(vNewName, 'Sales Manager - PL', pCustomer, 'Name');
DimensionElementComponentAdd('Sales Manager - PL',pNewSalesManager, pCustomer , 1);
DimensionElementComponentDelete('Sales Manager - PL',pOldSalesManager, pCustomer);
##6) Run exception
No comments:
Post a Comment