Dimension utilisation - finding redundant dimensions

Ideas and tips for enhancing your TM1 application
User avatar
Martin Ryan
Site Admin
Posts: 1988
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 »

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 2663 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: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Dimension utilisation - finding redundant dimensions

Post by jim wood »

Very handy indeed.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Kyro
Community Contributor
Posts: 126
Joined: Tue Nov 03, 2009 7:46 pm
OLAP Product: MODLR - The CPM Cloud
Version: Always the latest.
Excel Version: 365
Location: Sydney, Australia
Contact:

Re: Dimension utilisation - finding redundant dimensions

Post by Kyro »

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 »

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 »

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: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Dimension utilisation - finding redundant dimensions

Post by Alan Kirk »

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 »

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 »

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: 1988
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 »

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: 3098
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Dimension utilisation - finding redundant dimensions

Post by Wim Gielis »

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

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
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 »

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

Egor.
User avatar
Martin Ryan
Site Admin
Posts: 1988
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 »

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 »

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: 1988
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 »

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 »

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 »

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: 3647
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Dimension utilisation - finding redundant dimensions

Post by lotsaram »

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 »

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 »

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: 113
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 »

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