Alphabet help

[Bucjeff]

We have a lot of employee movement between departments and the budget team needed a quick employee lookup to see where an employee currently is in our org structure. I built a process so that they can type a first letter of a last name to find someone's current information. Problem is There are a smattering of records that don't show up under the correct alpha grouping. I'm still new to TM1 and very inexperienced at TI scripting. Can anyone please point out where or what circumstance could cause a record to be in the wrong alpha grouping? This is my Metadata tab


#####################################
# BUILD EMP NUMBER INTO DIMENSION, IF NEEDED
#####################################

IF ( DIMIX ( strEmpIDDim, vValue ) = 0 ) ;

#DEFINE FIRST CHAR OF LAST NAME
strName = CELLGETS ( strCubeSource, vCategory, vCostCenter, vIndex, 'Name');
IF ( strName @= '');
strName = 'NoName' | vValue;
ENDIF;
strLastNameCharacter = SUBST ( strName, 1, 1 );

# BUILD A CONSOL ELEMENT WITH FIRST CHAR OF LAST NAME TO CREATE A HIERARCHY
IF ( DIMIX ( strEmpIDDim, strLastNameCharacter) = 0 );
DIMENSIONELEMENTINSERT ( strEmpIDDim, '', strLastNameCharacter, 'C' );
DIMENSIONELEMENTCOMPONENTADD ( strEmpIDDim , strEmpIDRoot , strLastNameCharacter, 1 ) ;
ENDIF;

# BUILD NEW ELEMENT AND PUT IT INTO THE CONSOL ELEMENT WITH FIRST CHAR OF THE LAST NAME
DIMENSIONELEMENTINSERT ( strEmpIDDim, '', vValue, 'N' );
DIMENSIONELEMENTCOMPONENTADD ( strEmpIDDim, strLastNameCharacter, vValue, 1 ) ;

ENDIF;

Any help is really appreciated.

Thanks

Jeff F

[Steve Rowe]

So your code creates a consolidation based on the first character of the persons last name.

I'd have a trim here to get rid of leading spaces.
strLastNameCharacter = SUBST ( Trim(strName), 1, 1 );

You've not told us anything about how it is going wrong, this is important. Do they appear under the wrong character, not appear at all, do they appear in the right place and the wrong place, generate some kind of TI error?

Don't forget that the code you have adds to the structure that already exists rather than rebuilds it from scratch, so if you made errors when developing the code they will persist in your current structure. You should have flatten hierarchy code in your prolog.

HTH

[Bakkone]

Hi,

Your first statement: IF ( DIMIX ( strEmpIDDim, vValue ) = 0 ) ;

Is probably causing problems. It means that only the new employees would be added to the "first character"-thing.

[Bucjeff]

Steve

As far as whats actually going wrong. In the lookup cube I have last names under an alphabetical consolidation that do not begin with the consolidation level. Last name of Bearce under the C and so forth. A name is only under one consolidation level so in this example Bearce is not under the B consolidation where it should be. Here is my data tab if it helps any.


#****Begin: Generated Statements***
#****End: Generated Statements****


#####################################
# BUILD EMP NUMBER INTO DIMENSION, IF NEEDED
#####################################

IF ( DIMIX ( strEmpIDDim, vValue ) = 0 ) ;

#DEFINE FIRST CHAR OF LAST NAME
strName = CELLGETS ( strCubeSource, vCategory, vCostCenter, vIndex, 'Name');
IF ( strName @= '');
strName = 'NoName' | vValue;
ENDIF;
strLastNameCharacter = SUBST ( strName, 1, 1 );

# BUILD A CONSOL ELEMENT WITH FIRST CHAR OF LAST NAME TO CREATE A HIERARCHY
IF ( DIMIX ( strEmpIDDim, strLastNameCharacter) = 0 );
DIMENSIONELEMENTINSERT ( strEmpIDDim, '', strLastNameCharacter, 'C' );
DIMENSIONELEMENTCOMPONENTADD ( strEmpIDDim , strEmpIDRoot , strLastNameCharacter, 1 ) ;
ENDIF;

# BUILD NEW ELEMENT AND PUT IT INTO THE CONSOL ELEMENT WITH FIRST CHAR OF THE LAST NAME
DIMENSIONELEMENTINSERT ( strEmpIDDim, '', vValue, 'N' );
DIMENSIONELEMENTCOMPONENTADD ( strEmpIDDim, strLastNameCharacter, vValue, 1 ) ;

ENDIF;



The part about blank spaces may be a big help. If it found this would it probably assign it to the last consolidated alpha character it found? No TI error is created when the process is run.

Bakkone

Not sure what you mean as all employees are listed in the lookup cube.


Thanks in advance guys for your help.

Jeff F

[Steve Rowe]

What Bakkone is getting at is that the
IF ( DIMIX ( strEmpIDDim, strLastNameCharacter) = 0 );
line means that your code is only creating the relationship once the first time the employee is encountered by the code.

Now if the code was wrong or CELLGETS ( strCubeSource, vCategory, vCostCenter, vIndex, 'Name'); was wrong on the first pass then you would never correct the relationship on later passes since the name would already be in the dimension.

If you comment out the Dimix test then you should find that your elements are under the correct consolidation. Note that they may still be under the incorrect consolidation too depending on what you are doing to clear down the structure in the prolog.
Cheers,

[Bucjeff]

Polog tab



###DEFINE VARIABLES###
strCubeSource = 'bpmComp_EmpInfo' ;
strCubeTarget = 'bpmEmpID_Info' ;
strView = 'zzPopulateEmpIDcube' ;
strSub = strView ;
strEmpIDDim = 'bpmEmpID';
strEmpIDRoot = 'Total EmpID';
strEmpMsrDim = 'bpmEmpID_Msr';


#####################################
# CREATE Target VIEW
#####################################

IF(VIEWEXISTS ( strCubeTarget, strView) = 1);
VIEWDESTROY ( strCubeTarget, strView);
ENDIF;
VIEWCREATE ( strCubeTarget, strView);



#####################################
# DEFINE SUBSETS IN TARGET VIEW
#####################################

###bpmCategory
strDim = 'bpmCategory';
strElement = pCategory;

IF (SUBSETEXISTS (strDim, strSub) = 0);
SUBSETCREATE (strDim, strSub);
ENDIF;

SUBSETDELETEALLELEMENTS (strDim, strSub);
SUBSETELEMENTINSERT ( strDim, strSub, strElement, 1 );
VIEWSUBSETASSIGN ( strCubeTarget, strView, strDim, strSub );


###bpmEmpID Dim
strDim = 'bpmEmpID';

IF(SUBSETEXISTS (strDim, strView) = 1);
SUBSETDELETEALLELEMENTS(strDim, strSub);
ELSE;
SUBSETCREATE(strDim, strSub);
ENDIF;

numDimSiz = DIMSIZ (strDim);
numCounter = 1;
WHILE ( numCounter <= numDimSiz);
strElement = DIMNM ( strDim, numCounter);
IF ( ELLEV ( strDim, strElement) = 0 );
SUBSETELEMENTINSERT ( strDim, strSub, strElement, 1);
ENDIF;
numCounter = numCounter + 1;
END;

VIEWSUBSETASSIGN ( strCubeTarget, strView, strDim, strSub);


###bpmEmpID_Msr Dim
strDim = 'bpmEmpID_Msr';

IF(SUBSETEXISTS (strDim, strView) = 1);
SUBSETDELETEALLELEMENTS(strDim, strSub);
ELSE;
SUBSETCREATE(strDim, strSub);
ENDIF;

numDimSiz = DIMSIZ (strDim);
numCounter = 1;
WHILE ( numCounter <= numDimSiz);
strElement = DIMNM ( strDim, numCounter);
IF ( ELLEV ( strDim, strElement) = 0 );
SUBSETELEMENTINSERT ( strDim, strSub, strElement, 1);
ENDIF;
numCounter = numCounter + 1;
END;

VIEWSUBSETASSIGN ( strCubeTarget, strView, strDim, strSub);


#-------------------------------------------------------
# IGNORE CONSOL ELEMENTS, RULE BASED ELEMENTS AND ZEROS IN TARGET VIEW
#-------------------------------------------------------
VIEWEXTRACTSKIPRULEVALUESSET ( strCubeTarget, strView, 1) ;
VIEWEXTRACTSKIPCALCSSET ( strCubeTarget, strView, 1) ;
VIEWEXTRACTSKIPZEROESSET ( strCubeTarget, strView, 1) ;




#####################################
# ZERO OUT DATA IN THE TARGET VIEW
#####################################
VIEWZEROOUT ( strCubeTarget, strView );





#####################################
# CREATE Source VIEW
#####################################

IF(VIEWEXISTS ( strCubeSource, strView) = 1);
VIEWDESTROY ( strCubeSource, strView);
ENDIF;
VIEWCREATE ( strCubeSource, strView);



#####################################
# DEFINE SUBSETS IN SOURCE VIEW
#####################################

###bpmCategory
strDim = 'bpmCategory';
strElement = pCategory;

IF (SUBSETEXISTS (strDim, strSub) = 0);
SUBSETCREATE (strDim, strSub);
ENDIF;

SUBSETDELETEALLELEMENTS (strDim, strSub);
SUBSETELEMENTINSERT ( strDim, strSub, strElement, 1 );
VIEWSUBSETASSIGN ( strCubeSource, strView, strDim, strSub );


###bpmCostCenter Dim
strDim = 'bpmCostCenter';

IF(SUBSETEXISTS (strDim, strView) = 1);
SUBSETDELETEALLELEMENTS(strDim, strSub);
ELSE;
SUBSETCREATE(strDim, strSub);
ENDIF;

numDimSiz = DIMSIZ (strDim);
numCounter = 1;
WHILE ( numCounter <= numDimSiz);
strElement = DIMNM ( strDim, numCounter);
IF ( ELLEV ( strDim, strElement) = 0 );
SUBSETELEMENTINSERT ( strDim, strSub, strElement, 1);
ENDIF;
numCounter = numCounter + 1;
END;

VIEWSUBSETASSIGN ( strCubeSource, strView, strDim, strSub);


###bpmComp_Index Dim
strDim = 'bpmComp_Index';

IF(SUBSETEXISTS (strDim, strView) = 1);
SUBSETDELETEALLELEMENTS(strDim, strSub);
ELSE;
SUBSETCREATE(strDim, strSub);
ENDIF;

numDimSiz = DIMSIZ (strDim);
numCounter = 1;
WHILE ( numCounter <= numDimSiz);
strElement = DIMNM ( strDim, numCounter);
IF ( ELLEV ( strDim, strElement) = 0 );
SUBSETELEMENTINSERT ( strDim, strSub, strElement, 1);
ENDIF;
numCounter = numCounter + 1;
END;

VIEWSUBSETASSIGN ( strCubeSource, strView, strDim, strSub);


###bpmComp_Info_Msr
strDim = 'bpmComp_Info_Msr';
strElement = 'LoadEmpID';

IF (SUBSETEXISTS (strDim, strSub) = 0);
SUBSETCREATE (strDim, strSub);
ENDIF;

SUBSETDELETEALLELEMENTS (strDim, strSub);
SUBSETELEMENTINSERT ( strDim, strSub, strElement, 1 );
VIEWSUBSETASSIGN ( strCubeSource, strView, strDim, strSub );


#-------------------------------------------------------
# IGNORE CONSOL ELEMENTS, RULE BASED ELEMENTS AND ZEROS IN SOURCE VIEW
#-------------------------------------------------------
VIEWEXTRACTSKIPRULEVALUESSET ( strCubeSource, strView, 1) ;
VIEWEXTRACTSKIPCALCSSET ( strCubeSource, strView, 1) ;
VIEWEXTRACTSKIPZEROESSET ( strCubeSource, strView, 1) ;


#####################################
# USE THE NEW SOURCE VIEW IN THE PROCESS
#####################################
DATASOURCECUBEVIEW = strView;

To a novice like me it looks right?

[lotsaram]

We have a lot of employee movement between departments and the budget team needed a quick employee lookup to see where an employee currently is in our org structure. I built a process so that they can type a first letter of a last name to find someone's current information. Problem is There are a smattering of records that don't show up under the correct alpha grouping. I'm still new to TM1 and very inexperienced at TI scripting.

I think there really are 2 separate issues here.
1/ what is the actual requirement & possible ways to meet it
2/ help with TI scripting
Hence I'm going to post 2 responses to address each in turn.

I'm surprised no one else seems to have jumped on the 1st point but I would question why at all you are building consolidations (on the fly it seems?) by some letters of an employee's name. I don't see why to meet the requirement of doing even a fuzzyish character match on a name you need to do any dimension manipulation at all. I think all you need for this is an active form report with employees on rows and columns with attribute data showing department, etc. For the rows all that's needed is some MDX using the INSTR function pulling from a text entry cell where the user enters a partial string match for a part of the employee's name.

e.g.

Code: Select all

{FILTER( TM1FILTERBYLEVEL(TM1SUBSETALL([Employee]), 0), INSTR( 1, [Employee].CurrentMember.Properties("LastName"), "Z", 0 ) = 1 )}

In the above example assuming there is a dimension called "Employee" with an attribute "Last Name" this will return a list of all employees with last name starting with Z. Change the operator from = to >= then this would return a list of all employees with last names containing Z. Obviously the "Z" wouldn't be a string literal but in an active form can be passed from a cell value. (Just don't forget the encasing quotes and be aware that INSTR is case sensitive so best to convert both the search string and searched string to either UCase or LCase.

[lotsaram]

Prt 2: TI scripting

###DEFINE VARIABLES###
strCubeSource = 'bpmComp_EmpInfo' ;
strCubeTarget = 'bpmEmpID_Info' ;
strView = 'zzPopulateEmpIDcube' ;
strSub = strView ;
strEmpIDDim = 'bpmEmpID';
strEmpIDRoot = 'Total EmpID';
strEmpMsrDim = 'bpmEmpID_Msr';

...
Many lines of code later
...

DATASOURCECUBEVIEW = strView;

To a novice like me it looks right?

To me that looks like
197 lines of code
taking out the blank spaces 143 lines of code & comments
taking out 32 comment lines that leaves 111 actual lines of code.
Still a lot of code.

(Aside: if posting heaps of code best to use the code markup tags)

You could achieve the same result with this

Code: Select all

###DEFINE VARIABLES###
strCubeSource = 'bpmComp_EmpInfo' ;
strCubeTarget = 'bpmEmpID_Info' ;
strView = 'zzPopulateEmpIDcube' ;
strSub = strView ;

###Clear target cube
ExecuteProcess('Bedrock.Cube.Data.Clear', 'pCube', strCubeTarget, 'pView', strView, 'pFilter', 'bpmCategory:'|pCategory);

###Create source view
ExecuteProcess('Bedrock.Cube.View.Create', 'pCube', strCubeSource, 'pView', strView, 'pFilter', 'bpmCategory:'|pCategory|'&bpmComp_Info_Msr:LoadEmpID', 'pSuppressZero', 1, 'pSuppressRules', 1, 'pSuppressConsol', 1);

###Assign source view
DATASOURCECUBEVIEW = strView;

That's seven (7) lines of code to get the same result. Much less code to review, much less chances to make an error, much less that can go wrong. If you haven't come across bedrock then you should check it out. If you are starting out it can help you out a lot. Hell it helps me a lot and I've been doing this for 15 years.

Note this code will do the same as what yours was meant to do. (also there is no need to set subsets for the dimensions that where you are looping to set all leaf elements, this is wasted effort since for both the zero out and data source views the skip calcs flag is set to true)

[Bakkone]

Yes, my point was that all elements that currently exist in your "alphabet dimension" will get skipped since they will return an index above 0. So only new employees added to the source after you last ran the TI will get updated into the correct structure.


Lotsarams solution is very good. It can however be worth it to add hierarchies to big dimensions cause for some reasons TM1 can loose performance if its just one big flat dimension.