Dimension Worksheet Function

[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.

[Alan Kirk]

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?

[Paul Segal]

=DIMNM(Dimension, Index, Alias) will return the company name.

[Alan Kirk]

I thought there was a function where I could reference an dimension index numbers and I would be able to retrieve the dimension name.

=DIMNM(Dimension, Index, Alias) will return the company name.

Paul, I think you're thinking of an element name rather than a dimension name.

[Paul Segal]

Ah. I'll get my coat...

[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.

[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.

[Alan Kirk]

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.

[Alan Kirk]

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...

[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;

[Martin Ryan]

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

[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';

[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

[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