Page 1 of 1
Dimension Worksheet Function
Posted: Mon Nov 03, 2008 6:00 pm
by Eric
I thought there was a function where I could reference an dimension index numbers and I would be able to retrieve the dimension name. Maybe because it is monday, but I can't seem to find it.
Re: Dimension Worksheet Function
Posted: Mon Nov 03, 2008 6:44 pm
by Alan Kirk
Eric wrote:I thought there was a function where I could reference an dimension index numbers and I would be able to retrieve the dimension name. Maybe because it is monday, but I can't seem to find it.
Are you thinking of TabDim, perhaps?
Re: Dimension Worksheet Function
Posted: Mon Nov 03, 2008 6:59 pm
by Paul Segal
=DIMNM(Dimension, Index, Alias) will return the company name.
Re: Dimension Worksheet Function
Posted: Mon Nov 03, 2008 8:52 pm
by Alan Kirk
Eric wrote:I thought there was a function where I could reference an dimension index numbers and I would be able to retrieve the dimension name.
Paul Segal wrote:=DIMNM(Dimension, Index, Alias) will return the company name.
Paul, I think you're thinking of an element name rather than a dimension name.
Re: Dimension Worksheet Function
Posted: Mon Nov 03, 2008 10:56 pm
by Paul Segal
Ah. I'll get my coat...
Re: Dimension Worksheet Function
Posted: Wed Nov 05, 2008 5:51 pm
by Eric
Maybe I should ask a different question.
What is the best way to audit your DIM so you can clean out the garbage DIMs you are no longer using?
I was going to exract a list and then bounce each one off every cube.
Re: Dimension Worksheet Function
Posted: Wed Nov 05, 2008 11:57 pm
by ScottW
Hi Eric,
In the old TM1 recommended practices look up "Documenting_Dimension_Usage_by_Cube" - both TI and rules approaches are explained.
To improve on things a little I would recommend rather than using the control dims in the audit cube creating a replica of }Cubes and }Dimensions with a top level consolidation "All Cubes" and "All Dims" as this will make analysis easier.
Re: Dimension Worksheet Function
Posted: Thu Nov 06, 2008 12:14 am
by Alan Kirk
ScottW wrote:Hi Eric,
In the old TM1 recommended practices look up "Documenting_Dimension_Usage_by_Cube" - both TI and rules approaches are explained.
To improve on things a little I would recommend rather than using the control dims in the audit cube creating a replica of }Cubes and }Dimensions with a top level consolidation "All Cubes" and "All Dims" as this will make analysis easier.
Would I be correct in guessing that this is an archival copy that you have?
A search of the "Proven Practices" site that Eric located in this thread:
http://forums.olapforums.com/viewtopic.php?f=3&t=354
discloses only 8 documents[1] when you search for "TM1", none of which are that one.
[1] When it finally comes up. Honest to glub, I can't believe how slow Cognos Web sites are compared to... pretty much any other web site I visit.
Re: Dimension Worksheet Function
Posted: Thu Nov 06, 2008 12:24 am
by Alan Kirk
Eric wrote:Maybe I should ask a different question.
What is the best way to audit your DIM so you can clean out the garbage DIMs you are no longer using?
I was going to exract a list and then bounce each one off every cube.
I don't know if this is the BEST way (indeed it probably isn't), but this is a little quick & dirty app that I knocked together in between doing other stuff this morning. (Which means that it's not fully tested (though I tested it on my own server), and if it falls over and dribbles on you don't blame me.)
It generates three sheets; a list of cubes, a list of dimensions, and a list of the dimensions used in each cube. Rather than getting the cube or dim list from the server, it does it from the data directory. The list of dims in each cube, it gets from the server. That means that you need to have access to the data folder and be logged on.
Also it uses the file system object (which typically I don't, since it's a slug compared to the Windows API), but for something cheap and dirty it works well enough. However it does mean that you need to have the MS scripting library available. (Some more paranoid IT departments apparently block it, but that's not common.)
I discovered one flaw when I ran it the first time; if a dim is used in its own element attributes cube, it wouldn't be flagged as being an orphan. By default the app no longer looks at attribute cube usage, but you can change that behaviour on the control sheet.
You can also re-sort the dims in cube sheet to see the cubes that each dim is used in.
Enjoy if you want it, ignore it if you don't...
Re: Dimension Worksheet Function
Posted: Thu Nov 06, 2008 10:10 am
by Steve Vincent
I take no credit for this, its something i borrowed from Garry when i moved jobs
It checks all the non-control cubes and dims and reports info against them including how many cubes they are used in.
TI with no data source, all code in the prolog;
Code: Select all
FileOut='\\path\to\output\file\Dim Analysis.csv';
count=1;
DimTot=DIMSIZ('}Dimensions');
ASCIIOutput(FileOut, 'Dimension', 'No of Data Points','Number of elements','Consolidated Data Points', 'No of attached cubes');
ASCIIOutput(FileOut, ' ');
While(count<=DimTot);
dimension=dimnm('}Dimensions',Count);
IF(SUBST(dimension,1,1)@='}');
Itemskip;
Endif;
Cubecount=1;
Cubatt=0;
While(Cubecount <= dimsiz('}Cubes'));
cube=dimnm('}Cubes', cubecount);
cubeno=1;
If(SUBST(cube,1,1)@<>'}');
While(cubeno<=16);
If(dimension@=tabdim(cube,cubeno));
Cubatt=Cubatt+1;
Endif;
Cubeno=Cubeno+1;
End;
Endif;
Cubecount=cubecount+1;
End;
NoofCubes=TRIM(STR(Cubatt,2,0));
NoofElem=TRIM(STR(DIMSIZ(dimension),5,0));
Elemcount=0;
Count2=1;
While(Count2<=DIMSIZ(dimension));
Elem=DIMNM(dimension,count2);
If(ELLEV(dimension,Elem)=0);
elemcount=elemcount+1;
Endif;
Count2=Count2+1;
End;
highlvlcount=TRIM(STR((DIMSIZ(dimension)-elemcount),5,0));
elemstr=TRIM(STR(elemcount,5,0));
ASCIIOutput(FileOut, dimension, Noofelem, elemstr, highlvlcount, NoofCubes);
count=count+1;
End;
Re: Dimension Worksheet Function
Posted: Thu Nov 06, 2008 12:45 pm
by Martin Ryan
Steve Vincent wrote:I take no credit for this, its something i borrowed from Garry when i moved jobs
Speaking of Garry, what's happened to him? Haven't heard a peep out of him since his post about not being able to find TM1 people to work in the North West. Did he find you, Steve, then give you the baby and move on to other things?
Martin
Re: Dimension Worksheet Function
Posted: Thu Nov 06, 2008 12:48 pm
by Eric
Everyone. Thanks! I expected this to be a little simpler, but you have provided enough for me to do what I need in the short term. I will explore a long term goal when I have time.
OLAPFForumsMemeber = 'Rock';
Re: Dimension Worksheet Function
Posted: Thu Nov 06, 2008 2:48 pm
by Steve Rowe
A simple wks that dose this.
Change the server name
Copy the row and column headers across according to how many cubes and dims you have.
Change the Or statment in the formula for the cube with the maximum number of dimensions.
Will take a while to calculate, with big pause at the beginnig.
Values >0 in column B indicate dimensions used in cubes.
HTH
Re: Dimension Worksheet Function
Posted: Thu Nov 06, 2008 3:21 pm
by Steve Vincent
Other way around, he joined us, took over when my previous boss left then i left leaving him with the baby
He's still around tho, just a very busy man