Dimension utilisation - finding redundant dimensions

Ideas and tips for enhancing your TM1 application
User avatar
Martin Ryan
Site Admin
Posts: 1929
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Dimension utilisation - finding redundant dimensions

Post by Martin Ryan » Wed May 04, 2011 2:25 am

The attached TI process will cycle through the list of dimensions and find out which cubes they are used in. If a dimension is not used then it puts out "-" in the cube column. This can help you identify which dimensions are no longer being used.
Attachments
Info_DimensionUtilisation.pro
(2.56 KiB) Downloaded 1257 times
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer

User avatar
jim wood
Site Admin
Posts: 3453
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Dimension utilisation - finding redundant dimensions

Post by jim wood » Wed May 04, 2011 12:30 pm

Very handy indeed.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 7 64-bit. TM1 Version: 10.1.1

Kyro
Community Contributor
Posts: 123
Joined: Tue Nov 03, 2009 7:46 pm
OLAP Product: IBM Cognos TM1 all the way!
Version: You name it.
Excel Version: 2003 -2007 -2010
Location: Sydney, Australia
Contact:

Re: Dimension utilisation - finding redundant dimensions

Post by Kyro » Mon Jul 18, 2011 7:26 am

Brilliant! thanks for this.

LoganNSE
Posts: 22
Joined: Fri Nov 18, 2011 10:19 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2003 2007

Re: Dimension utilisation - finding redundant dimensions

Post by LoganNSE » Tue Nov 22, 2011 10:55 am

Excellent!! Thanks for the code. :idea:

Logan

tosca1978
Posts: 101
Joined: Thu Oct 20, 2011 6:53 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007
Location: London, UK

Re: Dimension utilisation - finding redundant dimensions

Post by tosca1978 » Thu Apr 12, 2012 6:42 pm

Hi Martyn,

I can't seem to open the attachment once it is downloaded? It asks me to search the net for an appropriate program to open the file. Can you tell me how to open using TM1?

Cheers

User avatar
Alan Kirk
Site Admin
Posts: 5657
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Dimension utilisation - finding redundant dimensions

Post by Alan Kirk » Thu Apr 12, 2012 7:42 pm

tosca1978 wrote:Hi Martyn,

I can't seem to open the attachment once it is downloaded? It asks me to search the net for an appropriate program to open the file. Can you tell me how to open using TM1?

Cheers
I'll save "Martyn" (sic) some typing.

It's a TI Process (.pro) file. You don't open it (though you can using Notepad if you want to see what's in it first), you put it into your data directory so that the server can register the process the next time it restarts.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

tosca1978
Posts: 101
Joined: Thu Oct 20, 2011 6:53 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007
Location: London, UK

Re: Dimension utilisation - finding redundant dimensions

Post by tosca1978 » Fri Apr 13, 2012 5:28 am

Martin - apologies for the typo and many thanks for the TI - it's exactly what I wanted.

Alan - many thanks for pointing out what I needed to do - much appreciated.

Cheers

tosca1978
Posts: 101
Joined: Thu Oct 20, 2011 6:53 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007
Location: London, UK

Re: Dimension utilisation - finding redundant dimensions

Post by tosca1978 » Fri Apr 13, 2012 5:48 am

Hi Martin - sorry another question!

Is there anyway of establishing if a dimension (and cube for that matter) is referenced in any rules or TI's? I have a few dimensions in my model which are not used in any cubes but are referenced in some rules. As I inherited this model from another developer (with no technical documentation) I am pretty nervous about deleting dimensions and cubes although the model definitely needs tidying up!

Cheers

User avatar
Martin Ryan
Site Admin
Posts: 1929
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Dimension utilisation - finding redundant dimensions

Post by Martin Ryan » Mon Apr 16, 2012 8:05 am

There are some clever documentation tools out there somewhere but you could also use the TM1 tools add-in which probably give you want you need more quickly. The specific tool you're after is described...
- The ability to load text files (including .pro and .rux files) into an excel workbook and then search them for a specified text expression. This can help determine the TI processes or rules which affect a specified cube.
As well as searching for cube references using this functionality you can search for dimension names. Or any text at all.

Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer

Wim Gielis
MVP
Posts: 1425
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Dimension utilisation - finding redundant dimensions

Post by Wim Gielis » Mon Apr 16, 2012 2:50 pm

tosca1978 wrote:Hi Martin - sorry another question!

Is there anyway of establishing if a dimension (and cube for that matter) is referenced in any rules or TI's? I have a few dimensions in my model which are not used in any cubes but are referenced in some rules. As I inherited this model from another developer (with no technical documentation) I am pretty nervous about deleting dimensions and cubes although the model definitely needs tidying up!

Cheers
Alternatively, you could use tools like Notepad++ to search in several files and folders. Also, regex and other features.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

rogovem
Posts: 3
Joined: Fri Nov 16, 2012 12:33 pm
OLAP Product: TM1, BI
Version: 10.1
Excel Version: 2007
Location: Moscow, Russia

Re: Dimension utilisation - finding redundant dimensions

Post by rogovem » Thu Feb 14, 2013 1:49 pm

Hi Martin,
is it possible to make finding the cubes for two dimensions?

Egor.

User avatar
Martin Ryan
Site Admin
Posts: 1929
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Dimension utilisation - finding redundant dimensions

Post by Martin Ryan » Fri Feb 15, 2013 1:46 am

Hi Egor,

I'm not entirely sure I follow your question but if you're asking whether you can check on just two specific dimensions then there are two approaches. The easiest one is to look at the extract file and just hide/delete all the dimensions you're not interested in.

The other option is to add a line like the one below, which will skip any dimension except 'Dimension1' and 'Dimension2';

if(sDim@='Dimension1' % sDim@='DImension2', 0, itemskip);

If that's not what you're asking could you try rephrasing your question?

Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer

rogovem
Posts: 3
Joined: Fri Nov 16, 2012 12:33 pm
OLAP Product: TM1, BI
Version: 10.1
Excel Version: 2007
Location: Moscow, Russia

Re: Dimension utilisation - finding redundant dimensions

Post by rogovem » Fri Feb 15, 2013 6:53 am

Hi Martin!
sorry for the wrong question.

I had in mind is to figure out a full set of cubes that uses two particular dimensions at the same time.
Perhaps these examples will help to explain what I mean:
asciioutput(exportFile, sDim1, sDim2, sCubeName);

Egor.

User avatar
Martin Ryan
Site Admin
Posts: 1929
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Dimension utilisation - finding redundant dimensions

Post by Martin Ryan » Mon Feb 18, 2013 9:54 pm

Ok, I see what you're trying to achieve. This particular process wouldn't do it. Here's how I'd probably do that

- Create a new process
- Use the All subset of the }Cubes dimension as the TI source
- Set the variable to be called sCubeName;
- In the Prolog tab have something like

Code: Select all

keyDim1='MyDim1';
keyDim2='MyDim2';
sFile='C:\myfile.csv';
- in the Data tab have something like

Code: Select all

nKeyDimCount=0;
# cycle through the dimensions in the current cube
j=0;
while(j<40);
  j=j+1;
  tDim=tabDim(sCubeName, j);
  if(tDim@=keyDim1 % tDim@=keyDim2);
    nKeyDimCount=nKeyDimCount+1;
  endif;
end;

if(nKeyDimCount=2);
  asciioutput(sFile, sCubeName, 'Has both key dimensions');
endif;
Hopefully that'll point you in the right direction. You can do whatever tweaks you need within that if statement.

Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer

rogovem
Posts: 3
Joined: Fri Nov 16, 2012 12:33 pm
OLAP Product: TM1, BI
Version: 10.1
Excel Version: 2007
Location: Moscow, Russia

Re: Dimension utilisation - finding redundant dimensions

Post by rogovem » Tue Feb 19, 2013 9:50 am

Hi Matin!

This is exactly what I had in mind.

it works great!
I think that I could not do it myself.

You have helped me incredibly.
Thank you a lot!

Egor.

ldwnt
Posts: 5
Joined: Fri May 03, 2013 5:51 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2003

Re: Dimension utilisation - finding redundant dimensions

Post by ldwnt » Tue May 07, 2013 5:33 am

Martin Ryan wrote:The attached TI process will cycle through the list of dimensions and find out which cubes they are used in. If a dimension is not used then it puts out "-" in the cube column. This can help you identify which dimensions are no longer being used.
Thanks for the process example. But I notice that a variable 'j' is used to go through all dimensions in a cube & it's assumed to be less than 20. I wonder if there's any way to find out the exact number of dimensions associated with a cube in TI?

lotsaram
MVP
Posts: 2954
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: 10.1 10.2
Excel Version: 2010 2013 365
Location: Switzerland

Re: Dimension utilisation - finding redundant dimensions

Post by lotsaram » Tue May 07, 2013 6:50 am

ldwnt wrote:I wonder if there's any way to find out the exact number of dimensions associated with a cube in TI?
You just make the while condition check for the result of the tabdim function (returns a blank string if the dimension index argument is a number greater than dimensions in the cube).

Code: Select all

i=1;
While( TabDim(sCubeName, i) @<> '' );
  sDimName = TabDim(sCubeName, i);
  i = i + 1;
End;
.. at the end of the loop number of dimensions in the cube = i - 1

ldwnt
Posts: 5
Joined: Fri May 03, 2013 5:51 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2003

Re: Dimension utilisation - finding redundant dimensions

Post by ldwnt » Wed May 08, 2013 2:45 am

lotsaram wrote:
ldwnt wrote:I wonder if there's any way to find out the exact number of dimensions associated with a cube in TI?
You just make the while condition check for the result of the tabdim function (returns a blank string if the dimension index argument is a number greater than dimensions in the cube).

Code: Select all

i=1;
While( TabDim(sCubeName, i) @<> '' );
  sDimName = TabDim(sCubeName, i);
  i = i + 1;
End;
.. at the end of the loop number of dimensions in the cube = i - 1
It works. Thank you. It would be more convenient if TI provided a single function~

rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Dimension utilisation - finding redundant dimensions

Post by rmackenzie » Wed May 08, 2013 5:04 am

Sometimes it is useful to know the number of dimensions in the largest cube in the server (by dimension size). This snippet extends on Lotsa's code to give that answer. I also amended it to only make the call to TABDIM once, and use a continue flag instead, as IMHO this is slightly more efficient:

Code: Select all

# minimum 2 dimensions in a cube
nMaxNumberOfDimensions = 2;

# iterate cubes
nCubeCounter = 1;
nMaxCubes = DIMSIZ ( '}Cubes' );
WHILE ( nCubeCounter <= nMaxCubes );
  sCubeName = DIMNM ( '}Cubes', nCubeCounter );

  # iterate cube dimensions
  nDimCounter = 1;
  nContinue = 1;
  WHILE ( nContinue = 1 );
    sDimName = TABDIM ( sCubeName, nDimCounter );
    IF ( sDimName @= '' );
      nContinue = 0;
    ELSE;
      nDimCounter = nDimCounter + 1;
    ENDIF;
  END;

  # subtract 1 from nDimCounter
  nDimCounter = nDimCounter - 1; 

  # update max dims if less than dimcounter
  IF ( nMaxNumberOfDimensions < nDimCounter );
    nMaxNumberOfDimensions = nDimCounter;
  ENDIF;

  # do next cube
  nCubeCounter = nCubeCounter + 1;

END;

# pass to log
ItemReject ( 'Largest cube has ' | NumberToString ( nMaxNumberOfDimensions ) | ' dimensions' );
Robin Mackenzie

Jorge Rachid
Posts: 30
Joined: Fri Jul 22, 2016 8:33 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: Dimension utilisation - finding redundant dimensions

Post by Jorge Rachid » Wed Mar 29, 2017 3:08 pm

Hello,

I am trying to use this process but I am facing an error on .cma file.

What I have to do in this case about the exportfile folder on prolog?

Tks,

JR

Post Reply