Recreate a dimension

[grantm15]

I think I am confused about what actually is happening when I recreate a dimension (Action: Recreate under the Dimensions tab in TI). I assumed that the entire dimension is being recreated from the ground up. In other words, the elements (and consolidations) are deleted and the entire hierarchy is rebuilt with the newly inserted elements and consolidations. That is obviously not happening for me. I have a product dimension that I want to "recreate" every night with the most current product element consolidation hierarchy. But what is happening is that even though the TI process "Recreates" the dimension every night, the old and new hierarchy that the element rolls up through still exists. So anytime a product hierarchy changes, I have 2 different aggregations of the same product for every period because the base element is in the hierarchy twice with different hierarchy consolidations. So my impression of what recreating does is obviously wrong. So how do I recreate the entire dimension with the current product hierarchy without losing cube data to build the dimension from scratch?

Thanks in advance.

Grant

[Alan Kirk]

I think I am confused about what actually is happening when I recreate a dimension (Action: Recreate under the Dimensions tab in TI). I assumed that the entire dimension is being recreated from the ground up. In other words, the elements (and consolidations) are deleted and the entire hierarchy is rebuilt with the newly inserted elements and consolidations. That is obviously not happening for me. I have a product dimension that I want to "recreate" every night with the most current product element consolidation hierarchy. But what is happening is that even though the TI process "Recreates" the dimension every night, the old and new hierarchy that the element rolls up through still exists. So anytime a product hierarchy changes, I have 2 different aggregations of the same product for every period because the base element is in the hierarchy twice with different hierarchy consolidations. So my impression of what recreating does is obviously wrong.

It shouldn't be.

I despise the Maps tab as I've mentioned many times in the past but in this case it seems to function as advertised. In a test with a two column file, one an element name and the other a consolidation name, it generated the following code in the Advanced tabs:

Prolog

Code: Select all

#****GENERATED STATEMENTS START****
DIMENSIONDELETEALLELEMENTS('zDim1');
DIMENSIONSORTORDER('zDim1','ByName','ASCENDING','ByHierarchy','ASCENDING');
#****GENERATED STATEMENTS FINISH****

That deletes all of the elements in the temporary copy, including consolidations.

Metadata

Code: Select all

#****GENERATED STATEMENTS START****
DIMENSIONELEMENTINSERT('zDim1','',Product,'n');
DIMENSIONELEMENTINSERT('zDim1','',Parent,'c');
DIMENSIONELEMENTCOMPONENTADD('zDim1',Parent,Product,1.000000);
#****GENERATED STATEMENTS FINISH****

I did have to play around with the sort order options to get the hierarchy to show correctly but that had no effect on whether the old hierarchies were completely destroyed.

So that leaves two possibilities. One is that your code is doing something that you aren't telling us about, which is why the Request for assistance guidelines (PLEASE READ) states that:

For Rules and TurboIntegrator processes, remember that including the actual code in your post will be a thousand times more useful than an attempted description of it.

And the second, which I consider to be the more likely of the two, is that your data source is actually returning both the new and old hierarchies, and the TI Process is therefore recreating them each time you run it.

The way to determine that is to do an ASCIIOutput in the Metadata tab and looking to see exactly what it is that's coming into your process.

[grantm15]

Sorry for not following the rules. I will attempt to comply from now on.

And the second, which I consider to be the more likely of the two, is that your data source is actually returning both the new and old hierarchies, and the TI Process is therefore recreating them each time you run it.

I have already checked what the data source is returning and it only returned the one element and only one associated hierarchy.

Prolog tab Code:

Code: Select all

#****Begin: Generated Statements***
DIMENSIONDELETEALLELEMENTS('Product');
DIMENSIONSORTORDER('Product','BYNAME','ASCENDING','BYHIERARCHY','ASCENDING');
#****End: Generated Statements****

Metadata Code:

Code: Select all

#****Begin: Generated Statements***
DIMENSIONELEMENTINSERT('Product','',ItemDescr,'n');
DIMENSIONELEMENTINSERT('Product','',AcctSubType,'c');
DIMENSIONELEMENTINSERT('Product','',AcctGroup,'c');
DIMENSIONELEMENTINSERT('Product','',AcctSubGroup,'c');
DIMENSIONELEMENTINSERT('Product','',SubDeptDescr,'c');
DIMENSIONELEMENTINSERT('Product','',CategoryDescr,'c');
DIMENSIONELEMENTINSERT('Product','',SubCategoryDescr,'c');
DIMENSIONELEMENTCOMPONENTADD('Product',AcctSubType,AcctGroup,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Product',AcctGroup,AcctSubGroup,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Product',AcctSubGroup,SubDeptDescr,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Product',SubDeptDescr,CategoryDescr,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Product',CategoryDescr,SubCategoryDescr,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Product',SubCategoryDescr,ItemDescr,1.000000);
#****End: Generated Statements****

It is strange because it seems as if another hierarchy I have that is recreating is properly clearing out the old element hierarchy.


Thanks.

[Alan Kirk]

Sorry for not following the rules. I will attempt to comply from now on.

They're not iron-clad rules, they're guidelines, and people don't get thrown from the Tarpeian rock for violating them... it's just that they're guidelines which are intended to try to get from question to answer as quickly and effectively (and accurately) as possible. A lot of people have a tendency to describe what they think is happening, rather than what really is. Seeing the actual code makes a huge difference.

Prolog tab Code:

Code: Select all

Metadata Code:
[code]
#****Begin: Generated Statements***
DIMENSIONELEMENTINSERT('Product','',ItemDescr,'n');
DIMENSIONELEMENTINSERT('Product','',AcctSubType,'c');
DIMENSIONELEMENTINSERT('Product','',AcctGroup,'c');
DIMENSIONELEMENTINSERT('Product','',AcctSubGroup,'c');
DIMENSIONELEMENTINSERT('Product','',SubDeptDescr,'c');
DIMENSIONELEMENTINSERT('Product','',CategoryDescr,'c');
DIMENSIONELEMENTINSERT('Product','',SubCategoryDescr,'c');
DIMENSIONELEMENTCOMPONENTADD('Product',AcctSubType,AcctGroup,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Product',AcctGroup,AcctSubGroup,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Product',AcctSubGroup,SubDeptDescr,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Product',SubDeptDescr,CategoryDescr,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Product',CategoryDescr,SubCategoryDescr,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Product',SubCategoryDescr,ItemDescr,1.000000);
#****End: Generated Statements****

Are you getting any errors in the error log? Because something about that code smells wrong to me. Specifically names like "SubDeptDescr" and "CategoryDescr" suggest attributes rather than elements. It's not impossible that they are intended to be elements, but if they're actually supposed to be attributes (and possibly not unique ones) then it's possible that the process of creating the hierarchy is spitting errors as a result of which you're not having the tree built correctly.