9.5.1 How to Automate Maintenance of Hierarchies

[rfielden]

I am trying to create an automated TI process to maintain our store hierarchy from base Oracle tables. Levels are: All: Division: Territory: Region: District: Store (n level).

As I have been out of the Applix loop for awhile, I am once again starting at the beginning stage I left at.

Creating an ODBC process, I created the statement pulling in all the elements noted above. When process ran, not all the consolidate levels were created, including top level. My goal is to update the hierarchy on an on-going basis once created.

The only advanced tab that was populated was metadata with:

DIMENSIONELEMENTINSERT('CostCenters','',CONCEPT,'c');
DIMENSIONELEMENTINSERT('CostCenters','',AREA_NAME,'c');
DIMENSIONELEMENTINSERT('CostCenters','',REGION,'c');
DIMENSIONELEMENTINSERT('CostCenters','',DISTRICT,'c');
DIMENSIONELEMENTCOMPONENTADD('CostCenters',CONCEPT,AREA_NAME,1.000000);
DIMENSIONELEMENTCOMPONENTADD('CostCenters',AREA_NAME,REGION,1.000000);
DIMENSIONELEMENTCOMPONENTADD('CostCenters',REGION,DISTRICT,1.000000);
DIMENSIONELEMENTCOMPONENTADD('CostCenters',DISTRICT,COST_CTR,1.000000);

I have attempted some google searches and searched the IBM manuals with no luck for what I am exactly looking for. I am not necessarily looking for a solution but some guidance on how a 'rookie' should approach. Thanks.

[stex2727]

Were there errors during the process?

Are you sure they aren't being created? Depending on the order of the datasource the elements may look a little funny when you open the subset editor. To get a better view of the hierarchy hit the "all" button then the "hierarchy sort" button.

Asciioutput statement is a good way to debug a TI processes, eg

asciioutput('d:\test.txt', 'CostCenters',CONCEPT,AREA_NAME);

Steve

[rfielden]

Steve, thanks for the tip and that will provide a great learning tool for me!

I am getting close as I had a circular error.

I added the following line in the prolog tab: DimensionDeleteAllElements('RF_CstCtrs'); but this is failing to delete the elements, thus, the hierarchy continues to 'build'. Any suggestions? Thanks, Rick.

[rfielden]

I created a TI process with just the Dimension Element Delete code and worked fine. Is there some key how to format in main TI process to have it successfully complete?

[Wim Gielis]

Hello

Here are the conventions:

- Prolog tab: DimensionDeleteAllElements, but better would be an initial loop to delete the consolidated elements, or at least destroy the relationships between n-level elements and c-level elements. Tidying up could be done afterwards.
- using the TI wizard or not, but the Metadata tab should contain the DIMENSIONELEMENTINSERT and DIMENSIONELEMENTCOMPONENTADD functions. In the correct order, I mean you cannot add a child to a parent if the parent does not exist in the dimenion
- throw in some DIMIX functions to check whether an element already exists or not in that dimension. If existing,DIMIX returns a value exceeding 0.
- the code excerpt in post number 1 of the topic does not show all inserts in the dimension (based on your description of the levels in the dimension). Is the wizard used correctly and entirely?
- if you have circular errors, check the contents of the table in the data source: it could occur when you add child A to parent B, and then afterwards B again to A.

Wim

[rfielden]

Thanks all, everything now working. Circular error was sql code error.