Code for creating Dyamic Subsets with Process

[jimicron]

Hi all,

I sure hope I can relay this appropriately via writing

I am utlizing code that someone else wrote and don't understand it. In looking through the documentation, etc. and even online, I have struggled with understanding this. I reached out to a developer where i work and she said it was TM1 language and wasn't really able to help me either.

My main question is: is there a good place to go to in order to learn more about this type of code? Any suggestions? I know when I first started with SQL I was lost and now am very comfortable with it. I am thinking this is the same.

I'll post the code below. It has some comments in it. Where I am getting stuck is in the #3 section... I need the MDX to be dynamic and the "%Brand_Name%" isn't working

Currently, I only know how to really edit this code and dont' understand it. Being able to understand it would be helpful, thus my main question above

Here is the code:

DimName = 'SSD MPN';

##################### Subset: Brand #####################

# (1): Unwind the dimension being updated

Index = 1;
ElmCount = DIMSIZ(DimName);
While(Index <= ElmCount);
sElm = DIMNM(DimName,Index);
ChildCount = ELCOMPN(DimName,sElm);
WHILE(ChildCount > 0);
sChildElm = ELCOMP(DimName,sElm,ChildCount);
DIMENSIONELEMENTCOMPONENTDELETE(DimName, sElm, sChildElm );
ChildCount = ChildCount - 1;
END;
Index = Index + 1;
END;

# (2): Add all Level 0 elements to 'Total xxxxx' consolidation

Index = 1;
ElmCount = dimsiz(DimName);
While (index <= ElmCount );
sElm = DIMNM(DimName,Index);
IF(DTYPE(DimName,sElm) @<>'c');
DimensionElementComponentAdd(DimName, 'Total MPN',sElm,1);
ENDIF;
Index = Index + 1;
END;

#

DimName = 'SSD MPN';

# (3): Dynamically add subsets to DimName based on attribute of DimName

Index = 1;
ElmCount = dimsiz(DimName);
While (index < ElmCount );
Brand_Name = attrs(DimName,DIMNM(DimName,Index),'Brand');
SubsetPre = INSRT('Brand', '_', 1);
If(Brand_Name @<>'' );
SubsetName = SubsetPre | Brand_Name;
If( SubsetExists(DimName,SubsetName) = 0);
SubsetCreatebyMDX(SubsetName,Expand('{FILTER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[SSD MPN].[Brand] = "%Brand_Name%"} ) }, [SSD MPN].[Inclusion Flag] = "Yes")},
[SSD MPN].[Brand] = "%Brand_Name%")}'));
ENDIF;
ENDIF;
Index = Index + 1;
END;



# (4): Dynamically add consolidated elements based on attribute of DimName

Index = 1;
ElmCount = dimsiz(DimName);
While (index <= ElmCount );
sElm = DIMNM(DimName,Index);
Brand_Name = attrs(DimName,sElm,'Brand');
SubsetPre = INSRT('Brand', '_', 1);
If( Brand_Name @<>'' );
ConsolName = SubsetPre | Brand_Name;
DimensionElementInsert(DimName,'',ConsolName,'c');
DimensionElementComponentAdd(DimName, ConsolName,sElm,1);
ENDIF;
Index = Index + 1;
END;


Thanks a lot (as always). Appreciate any sort of guidence you can provide.

[jameswebber]

Jim,
TM1 is pretty tricky for newbies... espcially since usually a general IT department of developers doesn't want to touch it with a barge pole

Looks like you have a bit of unpicking to work out all the turbo intergrator code here.
This reference guide might help
http://pic.dhe.ibm.com/infocenter/ctm1/v10r1m0/nav/4_3

There is a link in it to the MDX functions.
But my first guess would be to remove the reference to {TM1SubsetBasis()}
Chances are this is a reference that doesn't exist anymore instead I would put the total Brand or just {[SSD MPN].[Brand]}

Code: Select all

SubsetCreatebyMDX(SubsetName,Expand('{FILTER( { DRILLDOWNMEMBER( {[SSD MPN].[Brand]}, {[SSD MPN].[Brand] = "%Brand_Name%"} ) }, [SSD MPN].[Inclusion Flag] = "Yes")},[SSD MPN].[Brand] = "%Brand_Name%")}'));

I would check this works by creating a new subset on one of the brand names in question on the brand dimension and check the expression works first
e.g. http://pic.dhe.ibm.com/infocenter/ctm1/ ... f%77%22%20

I hope that helps

[jimicron]

Thanks James for taking a stab at this! You are very right... it IS tricky for newbies... and when it comes to this sort of stuff, it's REALLY tricky. I can visualize what I want (or need) but can't quite get there. I am a huge fan of automating as much as humanly possible to mimimize the room for human error... and it makes things easier for end users... but sometimes to get to that end result means pulling ones hair out

What i am doing (or attempting to do) is create subsets for a dimension automatically. We have a LOT of parts... and they have one of two brands. I am using this concept for many but Brand is the easiest b/c there are only two. But, if a part changes brands... I want the new subset to reflect that. Thus, the whole "unwind" concept.

I tried both suggestions and the process aborted each time I could save it without error, but when running, it aborts. I replaced that one line but kept the rest of the code the same. I replaced it first with what you gave:

SubsetCreatebyMDX(SubsetName,Expand('{FILTER( { DRILLDOWNMEMBER( {[SSD MPN].[Brand]}, {[SSD MPN].[Brand] = "%Brand_Name%"} ) }, [SSD MPN].[Inclusion Flag] = "Yes")},[SSD MPN].[Brand] = "%Brand_Name%")}'));

And then tried this instead:

SubsetCreatebyMDX(SubsetName,Expand('{FILTER( { DRILLDOWNMEMBER( {[SSD MPN].[Brand]}, {[SSD MPN].[Brand]} ) }, [SSD MPN].[Inclusion Flag] = "Yes")},[SSD MPN].[Brand] = "%Brand_Name%")}'));

It stinks to not really know what any of this code is doing... I'd love to be able to truly understand it... I think then i would be able to be more successful. I can see us using this code a ton in our models...and then just tweaking it for each situation.

BTW... the second "%Brand_Name%" at the end works just great... it's just when I try to do that same concept of a wildcard above in the code is where it doesn't work. And, I need to have this MDX in the subset in order for the subset to work correctly - it IS a tangled web

Anyway, thanks again for anything you can provide. Appreciate it!

[jameswebber]

Jim,
So I think you still need to prove that you can create the MDX properly without any errors in a Expression Window in the Subset Editor. (see http://www.bihints.com/book/export/html/68)

Does this work with a hard coded value for brand_name?
I would make sure that works outside the TI process first.

Was this working before or has it never worked?

Next I would add a debug function by running ASCIIOutput with the values of %Brand_Name%, as my guess is this is a problem with a particaulr line of data if it was working and stopped working.
http://publib.boulder.ibm.com/infocente ... utput.html

Does that help?

[jimicron]

thank you. I am going to read through your links but wanted to reply quickly...

Yes, it was actually working (meaning, the process runs without error and creates the two subsets) when I was using this:

SubsetCreatebyMDX(SubsetName,Expand('{FILTER( {TM1SUBSETALL( [SSD MPN] )}, [SSD MPN].[Inclusion Flag] = "Yes")}+{FILTER( {TM1FILTERBYLEVEL( { HIERARCHIZE( {TM1SUBSETALL( [SSD MPN] )} ) }, 0)},
[SSD MPN].[Brand] = "%Brand_Name%")}'));

In replace of:

SubsetCreatebyMDX(SubsetName,Expand('{FILTER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[SSD MPN].[Brand] = "%Brand_Name%"} ) }, [SSD MPN].[Inclusion Flag] = "Yes")},
[SSD MPN].[Brand] = "%Brand_Name%")}'));


It would create two subsets; Brand_Micron and Brand_Crucial

The problem with that MDX though is that the second part of it wasn't working by filtering on the "inclusion flag" to capture only those that have "Yes"

When I go in and literally just record expression - it works... so, now that I have these subsets created (from using the "bad" code shown above in green) and then just grab the one I want and expand it, I get this:

{FILTER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[SSD MPN].[Brand_MICRON]} ) }, [SSD MPN].[Inclusion Flag] = "Yes")}

This DOES work... so, I wanted to use this same sort of code in my others by making the Brand_MICRON a wildcard... versus hard coding it.

Whew... I hope this is making sense... very sorry if not.

[jameswebber]

So then if I usnderstand you correctly it seems that the issue is you manualy can create the subset in the expression window okay but when you take the same code and use it in TI it doesnt work.

If this is the case I would assume it is because you are using a reference to {TM1SubsetBasis()} which is just a pointer to what was selected in the subset window at the time (see http://www.tm1forum.com/viewtopic.php?p=2079)
You need to come up with a selection to replace this.

Will this not work?

Code: Select all

SubsetCreatebyMDX(SubsetName,Expand('{FILTER( { DRILLDOWNMEMBER( {TM1SUBSETALL( [SSD MPN] )}, {[SSD MPN].[Brand] = "%Brand_Name%"} ) }, [SSD MPN].[Inclusion Flag] = "Yes")},
[SSD MPN].[Brand] = "%Brand_Name%")}'));

[jimicron]

I took the MDX you provided and then stripped out the stuff that is in there just for the TI process... left just what's left for the actual MDX in the expression window:

{FILTER( { DRILLDOWNMEMBER( {TM1SUBSETALL( [SSD MPN] )}, {[SSD MPN].[Brand] = "%Brand_Name%"} ) }, [SSD MPN].[Inclusion Flag] = "Yes")}

That didn't work... so I thought, okay, let me try just putting the real brand name in there... so tried this:

{FILTER( { DRILLDOWNMEMBER( {TM1SUBSETALL( [SSD MPN] )}, {[SSD MPN].[Brand] = "MICRON"} ) }, [SSD MPN].[Inclusion Flag] = "Yes")}

That didn't work either...

But if I use the following, it does work:

{FILTER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[SSD MPN].[Brand_MICRON]} ) }, [SSD MPN].[Inclusion Flag] = "Yes")}

So, keeping in line with what you stated, I then took out:

{TM1SubsetBasis()},

So was left with this:

{FILTER( { DRILLDOWNMEMBER( {[SSD MPN].[Brand_MICRON]} ) }, [SSD MPN].[Inclusion Flag] = "Yes")}

But then it doesn't work. It's like it's needing that {TM1SubsetBasis()},

Very frustrating. I appreciate your help!! I'm a lone ranger on this and with this being new it doesn't make it particularly easy at the moment.

[jameswebber]

The reason you need Brand_MICRON rather than just MICRON is because of these lines of code

Code: Select all

Brand_Name = attrs(DimName,DIMNM(DimName,Index),'Brand');

Get the brand name e.g. MICRON

Code: Select all

SubsetPre = INSRT('Brand', '_', 1);

Put Brand_ infront

Code: Select all

If(Brand_Name @<>'' );

If you have a value

Code: Select all

SubsetName = SubsetPre | Brand_Name;

The name of the subset is Brand_ + the brand_name



Doesn't the expression you are trying to write "{FILTER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[SSD MPN].[Brand_MICRON]} ) }, [SSD MPN].[Inclusion Flag] = "Yes")}" use the subset that you would have created using TI?

The TI create subset is different it's actually making this subset called in this case {[SSD MPN].[Brand_MICRON]}
SubsetCreatebyMDX(SubsetName,Expand('{FILTER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[SSD MPN].[Brand] = "%Brand_Name%"} ) }, [SSD MPN].[Inclusion Flag] = "Yes")}, [SSD MPN].[Brand] = "%Brand_Name%")}'));

You say there are only two brands Brand_Micron and Brand_Crucial
So at the moment when you open these two subsets in the server explorer (nothing to do with TI) are they presenting correctly?

Because right now the code checks to see if they exist and does nothign if they are already there

Code: Select all

If( SubsetExists(DimName,SubsetName) = 0);

So any code change you have been making is not being run.

I expect when you open one of these subsets you will find it is empty and I think the reason for this will be it is referencing {TM1SubsetBasis()} which need to be changed in the subset editor.
Alternatively you could delete the subset and let the TI process create it again.

[jimicron]

Thanks for your patience James. I am sure it's frustrating. Also, thanks for helping explain a bit what the code was doing!! Appreciate learning that!

Something in what you wrote made me look at the "Dimension Editor." When I went into that, I saw my subsets there - was not expecting that (I don't want that actually). If I go to Server Explorer and expand my dimension there, and then expand Subsets, I also see my subsets there. That is fine - that is what I want. I suspect the reason they also show up in the "Dimension Editor" is b/c of how my code is? Or, maybe that is just how it's designed.

So, with all of that, I just went in and deleted the subsets from both Dimension Editor (dimension structure) and under Subsets in Server Explorer.

I then tried this again (as you had me try this before):

Code: Select all

SubsetCreatebyMDX(SubsetName,Expand('{FILTER( { DRILLDOWNMEMBER( {TM1SUBSETALL( [SSD MPN] )}, {[SSD MPN].[Brand] = "%Brand_Name%"} ) }, [SSD MPN].[Inclusion Flag] = "Yes")},
[SSD MPN].[Brand] = "%Brand_Name%")}'));

Still aborted

You asked:

Doesn't the expression you are trying to write "{FILTER( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[SSD MPN].[Brand_MICRON]} ) }, [SSD MPN].[Inclusion Flag] = "Yes")}" use the subset that you would have created using TI?

I apologize, I am not quite following your question on that

When I look at these subsets... no, they are not presenting correctly

I think I'll come back tomorrow with a fresh head and look at this again. I'm spinning a lot on this and I think I need to walk away.

Thanks again for your help!

[tomok]

I need the MDX to be dynamic and the "%Brand_Name%" isn't working

And it's never going to work because the wildcard concept does not exist in MDX.

[lotsaram]

You need to be more careful about your terminology for people to be able to understand what you mean and be able to help you. One thing which you most certainly can't do in dimension editor is to edit or delete SUBSETS! Otherwise we are second guessing what the actual situation is and always needing additional clarification.

[rozef]

And it's never going to work because the wildcard concept does not exist in MDX.

Actually, it does.
You just need to had what we can call, an hidden MDX function.

Code: Select all

{FILTER( {TM1SUBSETALL( [SSD MPN] )}, INSTR( [SSD MPN].[Brand] , "'| Brand_Name |'" ) > 0 ) }

http://pic.dhe.ibm.com/infocenter/db2lu ... instr.html

[tomok]

Actually, it does.
You just need to had what we can call, an hidden MDX function.

Code: Select all

{FILTER( {TM1SUBSETALL( [SSD MPN] )}, INSTR( [SSD MPN].[Brand] , "'| Brand_Name |'" ) > 0 ) }

http://pic.dhe.ibm.com/infocenter/db2lu ... instr.html

No, it doesn't. That is not a wildcard function. You are building a string inside a TI process. If you took that and pasted it into the subset editor it would not work because the MDX interface cannot substitute the variable. The best you can do inside MDX would be to put the string value inside a cube and reference that cell.

[rozef]

I don't know what is your definition of wildcard but anyway, the issue is not about subset editor but syntax.
A simple FILTER can not do a caracters research (let's call it like that to avoid any confusion) and a FILTERBYPATERN don't work on attribute.
As you said replacing the variable '| Brand_Name |' by a string value would work in subset editor but the original issue was in TI.

[tomok]

I don't know what is your definition of wildcard but anyway, the issue is not about subset editor but syntax.
A simple FILTER can not do a caracters research (let's call it like that to avoid any confusion) and a FILTERBYPATERN don't work on attribute.
As you said replacing the variable '| Brand_Name |' by a string value would work in subset editor but the original issue was in TI.

I know we are splitting hairs but the issue isn't the difference between TI and subset editor. The issue is the concept of a wildcard, like the original user was trying to do, does not exist in MDX. Yes, you can do a FILTERBYPATTERN but there is no way inside MDX to subsitute a variable, so that the MDX query itself is dynamic, except to pull the variable from a cube. In the end you are going to end up with as many subsets as there are brands, not a dynamic subset that can evaluate to whatever brand is in the context you are looking at in the cube view. The example given is not MDX, it is a string built to be used by the SubsetCreateByMDX function in TM1. SubsetCreateByMDX is not an MDX query, it is a TM1 function for creating an MDX query.

[rozef]

I didn't realize that concept indeed.
There is still a way to replace the variable by a cube value (and why not add a picklist on it).
I don't know if that would provide the desire need.

[lotsaram]

.. but there is no way inside MDX to subsitute a variable, so that the MDX query itself is dynamic, except to pull the variable from a cube. In the end you are going to end up with as many subsets as there are brands, not a dynamic subset that can evaluate to whatever brand is in the context you are looking at in the cube view.

We are getting way of the OP's topic here and this might also be splitting hairs but in the specific case of the context of a cube view yes you can make an MDX set query dynamic based on the identity of the currently selected member by using .CurrentMember. This can work to have one dynamic subset used in either rows or columns return a "dynamic" set of members based on the selected title element from another dimension in the cube. This has been discussed a couple of times previously.

[jimicron]

Thank you everyone for the input in this thread! I greatly appreciate the conversation, input, suggestions, etc. (although I do think some were a bit harsh for a newbie ) I'm doing my best with where I am at in the learning curve - which I think we would all agree is rather sharp

Thanks also to Carl for taking the time to go line by line and explain what the code was doing! (He sent me an email) Very cool to help out a stranger like that and was very helpful! I've actually been working nearly solid on this the past couple of days. What we ended up doing is utilizing most of the code... but got rid of section 3 (the problematic part).

Instead of using Subsets, we are using Alternate Hierarchies in the dimension. Basically, it goes like this:

1) Clear the Parent/Child relationships
2) Delete existing hierarchies
3) Recreate hierarchies based on attributes of a dimension
4) Recreate Parent/Child relationship for Total MPN

It's looking really nice and makes it all automated and the end user should be very happy and it will require no input from me going forward

Thanks again for all of your help and suggestions!! A couple of us here have gone over and it was very helpful.

[jameswebber]

Glad you got it sorted Jim.
Sorry got sidetracked with some urgent work and saw you were getting some replies to this thread.

Very sorry if you found any of my comments harsh. It can be difficult sometimes to make sure we are talking about the same thing.

I would recommend reading that MDX primer if you are going to use MDX in the future. I found it really helpful.

Also believe it or not the but official IBM book is very good (I'm not on royalties or anything )
http://www.amazon.com/IBM-Cognos-TM1-Of ... 0071765697 you can even get a kindle version now so that might be worth checking out.

[jimicron]

Oh no James - I have really appreciated your help as well as others!

FYI

Here is how the code ended up.

In summary, it:

1) Clears the child/parent relationship
2) Deletes all alternate hierarchies (except for my "Total MPN")
3) Recreates the hierarchies
4) Puts my leaf level elements back under "Total MPN"

It's working great! I deleted each new hierarchy that is created and just left the one that we have been talking about, which is Brand. I also create quite a few others as you can see.

Thought maybe this would be helpful to others

Thanks again!

Code: Select all

DimName = 'SSD MPN';

############################################################################
#####################    CLEAR PARENT/CHILD RELATIONSHIPS   ######################
############################################################################

# Deletes all the Parent/Child relationships in the dimension for every element (removes from subtotal)

Index = 1;
ElmCount = DIMSIZ(DimName);
While(Index <= ElmCount);
sElm = DIMNM(DimName,Index);
ChildCount = ELCOMPN(DimName,sElm);
     WHILE(ChildCount > 0);
     sChildElm = ELCOMP(DimName,sElm,ChildCount);
     DIMENSIONELEMENTCOMPONENTDELETE(DimName, sElm, sChildElm );
     ChildCount = ChildCount - 1;
     END;
Index = Index + 1;
END;

############################################################################
############################   DELETE HIERARCHIES    ############################
############################################################################

# Deletes dynamcally created consolidated elements prior to recreating in the next step

DimName = 'SSD MPN';
ElmCount = dimsiz(DimName);
x = ElmCount;
WHILE( x >= 1 );
   ElName = DIMNM( DimName,x);
   IF( DTYPE( DimName,ElName) @= 'C');
           IF(subst(ElName, 1,9) @<> 'Total MPN');   
      DimensionElementDelete( DimName, ElName);
   ENDIF;
   ENDIF;
   x = x - 1;
END;


############################################################################
############################   CREATE HIERARCHIES    ############################
############################################################################

# Dynamically add consolidated elements based on attribute of DimName

##################################   Brand   ###################################

Index = 1;
ElmCount = dimsiz(DimName);
While (index <= ElmCount );
sElm = DIMNM(DimName,Index);
Brand_Name = attrs(DimName,sElm,'Brand');
SubsetPre = INSRT('Brand', '_', 1);
     If( Brand_Name @<>'' );
          If( attrs(DimName,sElm,'Inclusion Flag') @= 'Yes');
     ConsolName = SubsetPre | Brand_Name;
     DimensionElementInsert(DimName,'',ConsolName,'c');
     DimensionElementComponentAdd(DimName, ConsolName,sElm,1);
     DIMENSIONELEMENTINSERT(DimName,'','Brand_Total','c');
     DimensionElementComponentAdd(DimName,'Brand_Total', ConsolName,1);
     ENDIF;
     ENDIF;
Index = Index + 1;
END;

##################################   Series   ###################################

################################   SSD Description   #################################

##################################   SSD Model   ###################################

#################################   Classification   ##################################

##################################   Package   ###################################

##################################   Density   ###################################

##################################   DID   ###################################

##################################   MFF   ###################################


############################################################################
##########################    REPOPULATE MPN TOTAL   ###########################
############################################################################

# Adds all level zero elements back to subtotal:  Total MPN

Index = 1;
ElmCount = dimsiz(DimName);
While (index <= ElmCount );
sElm = DIMNM(DimName,Index);
     IF(DTYPE(DimName,sElm) @<>'c'); 
     DimensionElementComponentAdd(DimName, 'Total MPN',sElm,1);
     ENDIF;
Index = Index + 1;
END;