Finding out which Views a Subset belongs to?

Post Reply
fleaster
Regular Participant
Posts: 167
Joined: Wed Mar 30, 2011 11:57 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: XL2010

Finding out which Views a Subset belongs to?

Post by fleaster »

Hi all,
Hopefully this is a quick one: am trying to delete a subset, but of course the system will not allow it because it is attached to a view...

Besides looking through all the probably cube views, is there a quick way of finding out all the cube views a subset is attached to?

thanks! :)

Matt
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: Finding out which Views a Subset belongs to?

Post by Alan Kirk »

fleaster wrote: Mon Jul 31, 2017 1:43 am Hi all,
Hopefully this is a quick one: am trying to delete a subset, but of course the system will not allow it because it is attached to a view...

Besides looking through all the probably cube views, is there a quick way of finding out all the cube views a subset is attached to?

thanks! :)

Matt
Short of using a custom system reporting tool, the easiest way is to use the Find In Files search feature of Notepad++. Search .vue files for the subset name in the server's database folder. Remember to check the box for searching sub-folders as well in case it's used in a private view.
"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.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Finding out which Views a Subset belongs to?

Post by gtonkin »

If you are old school and like the command prompt, you could try the following from your data folder:

Code: Select all

findstr /i /s "<subset>" *.vue
fleaster
Regular Participant
Posts: 167
Joined: Wed Mar 30, 2011 11:57 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: XL2010

Re: Finding out which Views a Subset belongs to?

Post by fleaster »

cool, thanks guys.. had a feeling it might come to this :)
Andy Key
MVP
Posts: 351
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: Finding out which Views a Subset belongs to?

Post by Andy Key »

If using a built-in feature of Notepad++ or a simple one line command is too much like hard work, and you are feeling new school and fancy a REST:

You can use javascript to loop over a list of cubes and return a list of those that use the dimension you are checking (which is only really necessary if you want to tell the user that no cubes use the dimension), then loop over that smaller list of cubes and for each of those loop over a list of views on that cube. For each view you then loop through the Columns, Rows and Titles to see if the subset is there and build up a list that you then return to the user.

Which would take around 80-100 lines of code. And a web page. And some more lines for constants. And assumes you already have code to connect to the server. And code to handle the HttpGet.

And this is the new 'improved' way...

I'm with Alan and would use Notepad++
Andy Key
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Finding out which Views a Subset belongs to?

Post by David Usherwood »

I've just done this using Marius Wirtz' TM1PY libraries for Python (https://github.com/MariusWirtz/TM1py) in 24 lines, 6 of which are near duplicates to cover private and public subsets which I should have put in a function.
Wim Gielis
MVP
Posts: 3105
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: Finding out which Views a Subset belongs to?

Post by Wim Gielis »

Andy Key wrote: Wed Aug 02, 2017 7:35 amI'm with Alan and would use Notepad++
Me too, for ad hoc searches.
For an overview of all views/subsets, I would prefer a TI process and a couple of WildcardFileSearch'es in there, reading out the *.vue files.
Or TI that executes a PowerShell / Dos command.
With that information a (technical cube) could be set up to show which subsets are used in what views.
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
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Finding out which Views a Subset belongs to?

Post by lotsaram »

This post got me to thinking about private views. Since as far as I know private objects are ignored at startup and only read into memory when the user logs on then if deleting a public subset will the server throw an error if the subset is only referenced in a private view belonging to a user who doesn't have an active session? Anyone have a definitive answer?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Finding out which Views a Subset belongs to?

Post by lotsaram »

gtonkin wrote: Mon Jul 31, 2017 5:24 am If you are old school and like the command prompt, you could try the following from your data folder:

Code: Select all

findstr /i /s "<subset>" *.vue
The main issue I have with this when I've used it is all the false positives if the subset has a common name. Is there a way to search over a line break? ... As the vue files always contain property 7 dimension name followed on the next line by property 6 subset name (if dimension has subset defined else property 270 the unregistered member set definition)
e.g.
7,DimensionName
6,SubsetName

Anyone solved this elegantly with cmd script? I don't think findstr can search over a break.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Wim Gielis
MVP
Posts: 3105
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: Finding out which Views a Subset belongs to?

Post by Wim Gielis »

Something like this (pieced together based on the internet):

Code: Select all

          vTempFile_bat = 'SearchSubset.bat';

          AsciiOutput( vTempfile_bat, '@echo off' );
          AsciiOutput( vTempfile_bat, 'setlocal' );
          AsciiOutput( vTempfile_bat, '::Define LF variable containing a linefeed (0x0A)' );
          AsciiOutput( vTempfile_bat, 'set LF=^' );
          AsciiOutput( vTempfile_bat, '' );
          AsciiOutput( vTempfile_bat, '' );
          AsciiOutput( vTempfile_bat, '::Above 2 blank lines are critical - do not remove' );
          AsciiOutput( vTempfile_bat, '' );
          AsciiOutput( vTempfile_bat, '::Define CR variable containing a carriage return (0x0D)' );
          AsciiOutput( vTempfile_bat, 'for /f %%a in (''copy /Z "%~dpf0" nul'') do set "CR=%%a"' );
          AsciiOutput( vTempfile_bat, '' );
          AsciiOutput( vTempfile_bat, 'setlocal enableDelayedExpansion' );
          AsciiOutput( vTempfile_bat, '::regex "!CR!*!LF!" will match both Unix and Windows style End-Of-Line' );
          AsciiOutput( vTempfile_bat, 'findstr /n /r /s /i /m /c:%1 *.vue > ' | '"' | vRandomFilename | '"' );
          AsciiOutput( vTempfile_bat, 'If %ERRORLEVEL% EQU 1 del ' | '"' | vRandomFilename | '"' );


      # a DOS command
      vCommand = vTempFile_bat | ' "7,' | vDim | '!CR!*!LF!6,' | SubsetName | '"';
and execute it with the necessary parameters.
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
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Finding out which Views a Subset belongs to?

Post by gtonkin »

Wim Gielis wrote: Wed Aug 02, 2017 4:56 pm Something like this (pieced together based on the internet):...
Thanks Wim - that certainly removed most of the false positives from my searches!
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Finding out which Views a Subset belongs to?

Post by lotsaram »

Wim Gielis wrote: Wed Aug 02, 2017 4:56 pm Something like this (pieced together based on the internet)
...
Oh me likey +1
+2 if it all works OOTB when I test it.

Do you really need the 2 blank lines or is that gospel of the interwebs?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Finding out which Views a Subset belongs to?

Post by gtonkin »

Hope you don't mind Wim, but copied your work to the following batch file (staying old school command prompt) - worked OOTB for me! Only minor gotcha may be that you will get matched where susbset being searched for is part of a longer named one I.e. Contained in. Will test by adding the CRLF after subset name another day.

Code: Select all

@echo off
setlocal enableDelayedExpansion

:: Define LF to contain a lineFeed
set ^"LF=^

^" The empty line above is critical - DO NOT REMOVE

::Define CR variable containing a carriage return (0x0D)
for /f %%a in ('copy /Z "%~dpf0" nul') do set "CR=%%a"

::regex "!CR!*!LF!" will match both Unix and Windows style End-Of-Line
findstr /n /r /s /i /m /c:"7,%1!CR!*!LF!6,%2" *.vue 
You can execute using SearchSubset.bat "DimensionName" "SubsetName"

@Lotsaram-This may answer your question-seems like only one line with the <enter>
Wim Gielis
MVP
Posts: 3105
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: Finding out which Views a Subset belongs to?

Post by Wim Gielis »

Thanks for the additions and for testing George.
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
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Finding out which Views a Subset belongs to?

Post by lotsaram »

Works for me if and only if the dimension and subset names contain no spaces. Once the names contain spaces FINDSTR treats the space as an OR operator within the context of the regex regardless whether the /L argument is given. So end up with more false positives then ever since most subsets and most dimensions for that matter contain spaces in the name. Solve this problem and I'd be happy but otherwise no better off than before.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
jcr55
Posts: 54
Joined: Tue May 08, 2012 3:58 pm
OLAP Product: TM1
Version: 9.5.2 FP2
Excel Version: Excel 2007

Re: Finding out which Views a Subset belongs to?

Post by jcr55 »

Our TM1 servers are Windows servers.
I use freeware called Windows Grep (grep32.exe), which is a GUI Windows for Unix Grep-like search functions.
I search using the Subset name in all *.vue files.
Works great, no coding needed
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Finding out which Views a Subset belongs to?

Post by gtonkin »

lotsaram wrote: Wed Aug 02, 2017 10:50 pm Works for me if and only if the dimension and subset names contain no spaces. Once the names contain spaces FINDSTR treats the space as an OR operator within the context of the regex regardless whether the /L argument is given. So end up with more false positives then ever since most subsets and most dimensions for that matter contain spaces in the name. Solve this problem and I'd be happy but otherwise no better off than before.
HI Lotsaram, possibly a silly question but did you enclose the dimension and subset names in double quotes?
I seem to get the following - albeit using Wim's code in a standalone batch file:

Code: Select all

C:\TM1 Data\XYZ HR PM>..\SubsetUsage.bat "XYZ-Performance Management Reporting Source" Default

Admin\XYZ-Performance Management}vues\Admin-Sharepoint.vue
XYZ-Performance Management Reporting}vues\Default.vue
XYZ-Performance Management Reporting}vues\View-Ave per Gender.vue
XYZ-Performance Management Reporting}vues\View-Ave per Race and Gender.vue
XYZ-Performance Management Reporting}vues\View-Ave per Race.vue
XYZ-Performance Management Reporting}vues\View-Ave per Talent Grid.vue
XYZ-Performance Management Reporting}vues\View-Exco Summary.vue
XYZ-Performance Management Reporting}vues\View-Invalid per Exco.vue
XYZ-Performance Management Reporting}vues\View-Special Ratings.vue
XYZ-Performance Management Reporting}vues\View-Unassigned Talent Grid.vue
XYZ-Performance Management}vues\Default.vue
XYZ-Performance Management}vues\View-Active.vue
XYZ-Performance Management}vues\View-Invalid Summary.vue
XYZ-Performance Management}vues\_S-Extract.vue
XYZ-Performance Scenario Assumptions}vues\Default.vue
XYZ-Performance Scenario Control}vues\Default.vue
ZZ302957\XYZ-Performance Management}vues\Ratings.vue
ZZ302957\XYZ-Performance Management}vues\Talent Grid.vue
}CellSecurity_XYZ-Performance Management}vues\Default.vue
For Wim's TI code you would need to enclose vDim and Subset if you have not already tried this.
jcr55 wrote:Our TM1 servers are Windows servers.
I use freeware called Windows Grep (grep32.exe), which is a GUI Windows for Unix Grep-like search functions.
I search using the Subset name in all *.vue files.
Works great, no coding needed
The issue with Grep / Findstr by themselves will be apparent if you search for a view using Default - you really want to be able to filter by both dimension and subset to avoid subset named the same across dimensions coming up - grep is my tool of choice for most quick searches too! Not always easy to get IT to install, hence FINDSTR
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Finding out which Views a Subset belongs to?

Post by lotsaram »

gtonkin wrote: Thu Aug 03, 2017 6:02 pm HI Lotsaram, possibly a silly question but did you enclose the dimension and subset names in double quotes?
I seem to get the following - albeit using Wim's code in a standalone batch file
I got it working but interestingly it was the opposite.

If I had a batch file
findstr /n /r /s /i /m /c:"7,%1!CR!*!LF!6,%2" *.vue

... and called this via cmd
FindSubset.bat "my dimension" "my subset"

Then this was interpreted as find my or dimension or subset, etc.

Which was the same as this in the command line
findstr /n /r /s /i /m /c:"7,"my dimension"!CR!*!LF!6,"my subset"" *.vue

BUT, removing the quotes around the dimension and subset names containing the blank spaces then treats the whole as one uninterrupted string. Hence this works.
findstr /n /r /s /i /m /c:"7,my dimension!CR!*!LF!6,my subset" *.vue

I can't make it work with passing arguments to the batch file. But it doesn't matter as can just generate the batch file from scratch for each use. Happy that this is now working and its a pretty simple solution, but none the wiser as to HOW it is actually working.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Wim Gielis
MVP
Posts: 3105
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: Finding out which Views a Subset belongs to?

Post by Wim Gielis »

Glad it all works now :-)
Off the top of my head I can't provide more details about de DOS command.
I have the same variant with PowerShell too if you like.

I use this code in a TI process to create / update / delete typical subsets and/or views.
As in, "create an MDX-driven default subset in all the application dimensions of a cube or entire TM1 model",
knowing that subsets can be used in views (or not), can already exist (or not), apply an alias, etc.
Creating a Default subset for any kind of dimension can be tough though.
I use DNLEV, DIMSIZ, DimensionTopElementName to determine what would be a good fit for a Default subset on that dimension.
Of course the subset can be changed manually just afterwards.

As you know (certainly if you work in Switzerland ;-) ) with different language settings in the tm1p.ini, "Default" as a name is not sufficient.
The names are language-dependent and "Default" will not be recognized in say a French user interface ("Par défaut").

References:
http://www.wimgielis.com/tm1_userinterf ... ges_EN.htm
http://www.wimgielis.com/tm1_tm1pini_EN.htm
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
Andy Key
MVP
Posts: 351
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: Finding out which Views a Subset belongs to?

Post by Andy Key »

lotsaram wrote: Wed Aug 02, 2017 2:25 pm This post got me to thinking about private views. Since as far as I know private objects are ignored at startup and only read into memory when the user logs on then if deleting a public subset will the server throw an error if the subset is only referenced in a private view belonging to a user who doesn't have an active session? Anyone have a definitive answer?
I don't have an answer to that, but it sounds like a config setting that would Load Private Subsets On Startup would make the question moot.

Would you be surprised if IBM decided to call such a config setting LoadPrivateSubsetsOnStartup.

The doco only seems to exist for PA, but there are rumours that it is in FP7 as well - waiting for confirmation on that though.
Andy Key
Post Reply