Finding out which Views a Subset belongs to?

[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

[Alan Kirk]

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.

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

cool, thanks guys.. had a feeling it might come to this

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

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

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

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

[lotsaram]

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.

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

[gtonkin]

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

Thanks Wim - that certainly removed most of the false positives from my searches!

[lotsaram]

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?

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

Thanks for the additions and for testing George.

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

[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

[gtonkin]

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.

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]

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.

[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

[Andy Key]

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.