How to Eliminate specific elements of a dimension

[Loki419]

Hi

I am pretty new to TI so I apologize in advance for what is probably a simple task

I have a dimension with over 10 levels and literally dozens of alternate hierarchies. What I need to accomplish is eliminate everything that is not a part of three specific subsets. I have every things i need in Subset 1, Subset 2 and Subset 3. I want to keep all elements of these subsets but delete everything else. I assume there is a way to accomplish this with ELISANC in some manner but am not sure how to go about this.

Hope this makes sense and I appreciate any assistance
Loki

[gtonkin]

The simplest option may be to create a fourth subset by using MDX and a SubsetAll and Except e.g.

Code: Select all

EXCEPT(
{TM1SUBSETALL( [DIM] )},
{[DIM].[Subset1],[DIM].[Subset2],[DIM].[Subset3]})

You could then enumerate(loop) through this subset and delete elements from the dimension.
Not sure of the exact version of 10.2 you are using but you may want to make the subset static by creating it with SubsetMDXSet
Alternatively you could try looping through an ALL subset and testing if the element exists in any of the three subsets using SubsetElementGetIndex but sure that this will be longer and more complicated.

[Loki419]

Thanks so much for responding.

What I did was create the three subsets I want to keep (Parent levels). My thought is that I can basically say something to the effect of Delete everything from the dimension with the exception of all parents and children associated with Subset1, Subset2 and Subset3.

I'll play around with your suggestion and let ya'll know how it works out.

Thanks from a beginner !!

Loki

[Loki419]

BTW, I am running 10.2.2

Thanks
Loki

[tomok]

The simplest option may be to create a fourth subset by using MDX and a SubsetAll and Except e.g.

Code: Select all

EXCEPT(
{TM1SUBSETALL( [DIM] )},
{[DIM].[Subset1],[DIM].[Subset2],[DIM].[Subset3]})

You could then enumerate(loop) through this subset and delete elements from the dimension.
Not sure of the exact version of 10.2 you are using but you may want to make the subset static by creating it with SubsetMDXSet
Alternatively you could try looping through an ALL subset and testing if the element exists in any of the three subsets using SubsetElementGetIndex but sure that this will be longer and more complicated.

The problem with this MDX-based solution is it's only going to cover the elements in the subsets, not all the ancestors of the elements in the subsets. I don't know of any MDX-based solution that is going to work for you. I would merge the three subsets into one, so you can use that as data source in a TI, then step through all the elements in the data source and for each record do the following:

1. Get the size of the dimension.
2. Use the size as a counter
3. Starting with the last element in the dimension step backwards through the dimension and
4. Check to see if the element is either equal to the subset record OR an ancestor of the subset record.
5. If both conditions in 4 are false then delete the element from the dimension.

The reason you have to step backwards is because once you delete the element from the dimension then your counter no longer works.

[BrianL]

I would merge the three subsets into one, so you can use that as data source in a TI, then step through all the elements in the data source and for each record do the following:

1. Get the size of the dimension.
2. Use the size as a counter
3. Starting with the last element in the dimension step backwards through the dimension and
4. Check to see if the element is either equal to the subset record OR an ancestor of the subset record.
5. If both conditions in 4 are false then delete the element from the dimension.

I believe this still has issues and will end up deleting every element from the dimension.

I would still create a merged subset, but I'd follow this algorithm instead.

1. Get the size of the dimension
2. Use the size as a counter
3. Starting with the last element in the dimension step backwards through the dimension and
4. Get the size of the subset
5. Use the subset size as a counter
6. Initialize a found variable to zero (false)
7. Step through the subset and
8. check to see if the dimension element is either equal to the subset record OR an ancestor of the subset record
9. If either condition is true, set the found variable to one (true) and BREAK out of the inner loop
10. End the inner loop
11. Now outside the inner loop, check if found equals zero
12. if so, delete the element from the dimension


Note: it probably would've been easier and less confusing to just write the code, but I've already typed this out so...

[Moh]

Good morning sir,this is tough and confusing will be helpful tm1 starters like me if anybody show how this code to write.

[Loki419]

Good morning,

With the need to keep it simple due to my limited coding abilities, here is what I have done so far.

In the subset editor I grabbed all of the parent levels I need to maintain and hit the Keep button. Then I expanded all elements of those I kept. Saved a public subset KEEP. Opened the CostCenter dimension and added a text attribute called Keep.

Created a process using CostCenter->KEEP as the source
On the Variables tab I set CostCenter as an Element.
Created a variable and left it as V2 (I know, not exactly best practice but this is a one time throw away process). This variable has a formula V2='Y';
Variable type is set to string and Contents set to Attribute
On the Maps tab for cube I have nothing set. For Dimension I point to the Cost Center dimension andn the set the attribute tab to point to the Keep attribute I set up previously.
Running this process entered Y in the Keep attribute for every element in the KEEP subset.

Now I need to delete every element where the Keep attribute = “” and this is where I need a little more guidance. My thought is I can write a process something like
Prolog:
sDimName = 'CostCenter';
nCounter = 1;
sElName = DimNm(sDimName,nCounter);

My understanding is that the Metadata tab will run through each record in the source so I am thinking I can write something like the following

IF(attrs(sDimName,sElName,'Keep') @= ' ');
DimensionElementDelete(sDimName,sElName);
EndIf;
Any faults in my simple logic you guys can point out is appreciated. Hope these details help out those with an experience level similar to my own.

Thanks
Loki

[jim wood]

Good morning sir,this is tough and confusing will be helpful tm1 starters like me if anybody show how this code to write.

The point of this forum is to help people not do work for them. Loki (as you can see above) has tried to complete the task and attempted the code. Others will help Loki develop that code and the solution, helping Loki understand the reasoning behind the code not just the answer.

[tomok]

In the subset editor I grabbed all of the parent levels I need to maintain and hit the Keep button. Then I expanded all elements of those I kept. Saved a public subset KEEP. Opened the CostCenter dimension and added a text attribute called Keep.

Did you populate this attribute with anything? If not then I would so immediately. Copy and paste each of the members of this subset into an Excel sheet and write a DBS formula to send a "Y" to attribute for each of these elements. Now you have a way to flag which ones to keep and conversely, which ones to get rid of.

Now create a subset called "Delete" and use the following MDX code to base it on:

Code: Select all

{FILTER( {TM1SUBSETALL( [CostCenter] )}, [CostCenter].[Keep] = "")}

Create a process using this subset as the Data Source. In the Variable tab assign the name sCostCenter to the incoming field. In the Metadata tab put the following line:

Code: Select all

DimensionElementDelete('CostCenter', sCostCenter);

This is all you need.

[Loki419]

First off, Thanks for all of the help folks.

Here is what I got to work for me

Created Attribute 'CTEST' on the Center dimension

Created subset with all hierarchies I wanted to keep.
Created a process using ALL subset as a source and populated CTEST with N
Used the subset I created with the keepers as a source and populated CTEST with Y

Created another process with all code on PROLOG

sDimName = 'Center';
nCounter = 1;
nMaximum = DimSiz(sDimName);
WHILE(nCounter <= nMaximum);
sElName = DimNm(sDimName, nCounter);
IF(ATTRS(sDimName,sElName, 'CTEST') @= 'N' );
DimensionElementDelete(sDimName, sElName);
nMaximum = nMaximum - 1;
Else;
nCounter = nCounter + 1;
EndIF;
END;

This seems to have had the desired results.

Thanks again folks
Loki