Dimension utilisation - finding redundant dimensions
- 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
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 2842 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
Jodi Ryan Family Lawyer
- jim wood
- Site Admin
- Posts: 3953
- 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
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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
Brilliant! thanks for this.
-
- 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
Excellent!! Thanks for the code.
Logan
Logan
-
- 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
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 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
- Alan Kirk
- Site Admin
- Posts: 6610
- 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
I'll save "Martyn" (sic) some typing.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
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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- 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
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
Alan - many thanks for pointing out what I needed to do - much appreciated.
Cheers
-
- 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
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
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
- 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
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...
Martin
As well as searching for cube references using this functionality you can search for dimension names. Or any text at all.- 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.
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
Jodi Ryan Family Lawyer
-
- MVP
- Posts: 3128
- 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
Alternatively, you could use tools like Notepad++ to search in several files and folders. Also, regex and other features.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
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
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
-
- 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
Hi Martin,
is it possible to make finding the cubes for two dimensions?
Egor.
is it possible to make finding the cubes for two dimensions?
Egor.
- 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
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
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
Jodi Ryan Family Lawyer
-
- 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
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.
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.
- 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
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
- in the Data tab have something like
Hopefully that'll point you in the right direction. You can do whatever tweaks you need within that if statement.
Martin
- 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';
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;
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
Jodi Ryan Family Lawyer
-
- 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
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.
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.
-
- 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
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?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.
-
- MVP
- Posts: 3667
- 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
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).ldwnt wrote:I wonder if there's any way to find out the exact number of dimensions associated with a cube in TI?
Code: Select all
i=1;
While( TabDim(sCubeName, i) @<> '' );
sDimName = TabDim(sCubeName, i);
i = i + 1;
End;
-
- 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
It works. Thank you. It would be more convenient if TI provided a single function~lotsaram wrote: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).ldwnt wrote:I wonder if there's any way to find out the exact number of dimensions associated with a cube in TI?.. at the end of the loop number of dimensions in the cube = i - 1Code: Select all
i=1; While( TabDim(sCubeName, i) @<> '' ); sDimName = TabDim(sCubeName, i); i = i + 1; End;
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Dimension utilisation - finding redundant dimensions
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
-
- 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
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
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