Steve Vincent wrote:Not sure if this is specific to my version or not. Would be interesting if someone could test on a later version before i submit another bug report to Cognos
TM1 9.0.3 182 U7 running on Win Server 2003 Standard
I have a TI which is based on a dynamic subset of the employees dimension. The subset is basic enough, just grabs all level 0 elements on A to Z order
Code: Select all
{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employees] )}, 0)}, ASC)}
The TI i am writing is designed to locate possible duplicate clock numbers. We have a fantastic system that allows leading zeros for clock numbers, sometimes users will open a CSV with Excel and it'll loose those leading zeroes. Yes, i have told them not to do that but people make mistakes and so i have a mess of an employees dimension i'd like to fix, as i have loads of duplicate clocks like "0001234" and "1234".
The TI code is below. Its a WIP so i might not being doing this the most efficient way, if anyone has any improvements feel free to suggest them!
(all in the data tab, nowt else)
{Code snipped for brevity}
My problem is that the dimension has 26,000 elements and its trying to check each one 26,000 times. I know it needs to do that to find the duplicates, but the effect of looking at 26000 x 26000 data points (676 Million!) is a very long wait. Anyway, after a few mins of running i cancelled it to check how it was progressing and doing so killed the TI. When i went back to the datasource tab, the dim subset was blank, but if you check the same subset from the subset editior its fine
Closing the TI and re-opening it doesn't work. Logging off and on also doesn't. Even creating a new TI from scratch fails to give me any data, the only solution has been to restart the service.
Can anyone else reproduce this on a later version, or know if its fixed in a later release?
OK, first things first. I can't reproduce this in 9.4 (original release). Unfortunately Tuesday's my busy day and I wasn't able to test it on 9.1 SP3 (haven't installed SP4 yet) nor 8.2.12, though I doubt you'll be interested in the last one anyway. Will let you know what I encounter in 9.1, though you probably won't want to migrate over just this bug even if it works.
But in 9.4, the Preview button will bring up all of the elements of the subset in the window, happy as little clams.
But now to second things.
If I understand you correctly:
- The proper, bona fide clock numbers will all have 7 characters with leading 0's;
- The User-Induced potential duplicates will therefore have fewer characters; but
- They'll only be duplicates if the corresponding 7-character-with-leading-zero element is also there.
If so, I wouldn't bother looping through the elements more than once, since you'll be able to find the dups on the first pass. The following code doesn't use any data source, and goes in the Prolog. I've got it looping through the whole dim; my belief is that it would work well enough if you changed it to loop through an N level dynamic subset, but if you don't trust that in your version this probably won't take a significantly greater amount of time unless you have a consolidation for almost every element or something.
I used it with a 4600 element dimension with about a dozen induced duplicates, and it was "blink of an eye" stuff.
Code: Select all
SC_DIM_NAME = 'Employees';
# No particular reason why you couldn't iterate through an N level
# dynamic subset instead, but if you don't trust it we can
# do the whole dim.
l_DimLen = DimSiz( SC_DIM_NAME );
l_DimIdx = 1;
While ( l_DimIdx <= l_DimLen);
s_Element = DimNm( SC_DIM_NAME, l_DimIdx );
l_ElLev = ElLev ( SC_DIM_NAME, s_Element);
l_ElNameLen = Long ( s_Element);
If ( l_ElLev = 0 & l_ElNameLen < 7);
# Get the name of the element with padded 0's
s_LongName = Fill ( '0', 7 - l_ElNameLen) | s_Element;
If ( DimIx ( SC_DIM_NAME, s_LongName) > 0);
AttrPutS ( s_LongName , SC_DIM_NAME , s_Element , 'Duplicate' );
AttrPutS ( s_Element , SC_DIM_NAME , s_LongName , 'Duplicate' );
EndIf;
EndIf;
l_DimIdx = l_DimIdx + 1;
End;
Now drifting off topic slightly, and I'll post a proper thread when I figure out what's going on here, there's a big ol' gotcha with the new, improved 9.4.
I got a bit sloppy in writing the code above, and forgot to increment the index. Hey, I wrote it on the train on the way home and was distracted by the sight of a group of wallopers (that's "cops", Eric... "fuzz"? "Da man?") hog-tying a fat bald guy with glasses on the platform of one of the stations as I went past. Certainly something that I don't NORMALLY see from my office window. Anyway, I forgot to put the increment line in... which put the thing into an infinite loop.
So I used the standard back-out procedure when you don't have TM1 Top available. (Cursing under breath, then killing Excel and the local server along with it.) When I restarted Excel and the server, the server went to 50% processor usage and just hung there. It had started as an Application and when I went to the window I could see why; it had RE-DONE everything that happened before the crash, INCLUDING triggering the process.
I'm guessing that this has something to do with the new metadata logging feature. I killed Excel again, yanked every .log file I could find out of the system, and restarted with no problem.
Just something to watch for if you're in 9.4. (Sorry for slightly threadjacking there Steve.) When I confirm exactly where this cute little issue stems from, I'll post more details. But for now... it's DINNER TIME!
