I was bit amazed to find out that TM1 doesn’t seem to have any built-in functions for fetching all the subsets inside a dimension and it sure does not have a way to tell which alias is currently applied to the subset. Subsets are saved in separate files inside the datafiles directory so I needed a little PowerShell magik to fetch the subsets and their corresponding alias names by myself.

Inspired by the script “Listing all Subsets per Dimension in an IBM Cognos TM1 Server” in IBM developerWorks I wanted to push it little more further. I also switched TI scripting to PowerShell script because I needed to go inside the files contents instead of just iterating through the filenames.

The thing is, inside the .sub-files there is a special line starting with “274,” following after that comes the name of the alias that is applied to the subset. The real reason why I wanted to have a list the aliases applied to each subset is described in the next post but for now here comes the PowerShell script (it must be executed inside your datafiles-folder of your TM1 server):

Get-ChildItem -Recurse -Include *.sub
| Where-Object {$_.Directory -match "\w*}subs\b"}
| Select-String -pattern "274,"
| Select-Object Path, Filename, Line
| Where-Object {$_.Line -notmatch ",$" -AND $_.Path -notmatch "datafiles\\}" 
  -AND $_.Path -notmatch "datafiles\\tunit"}
| ForEach-Object { $_.Path.Split('\')[-2].Split('}')[0] + ";" 
  + $_.Filename.Split('.')[0] + ";" + $_.Line.Split(',')[1] + ";" + $_.Path }
| Set-Content "D:\temp\TM1_Dimension_Subset_Alias.csv"

What it does is basically the following:

• go through recursively all the files in the datafiles folder that are located in subfolders containing “}subs” in their name
• from the files, fetch all the lines that match the pattern “274,” (alias name)
• filter out unnecessary system folders
• finally form a nice csv-formatted content containing dimension name, subset name, alias name and the full path to the subset file
• write the results to output csv file

Example of the contents of the output file:

dimName1;subsetNameA;aliasname;c:\tm1server\datafiles\dimName}subs\subsetName.sub

There you go! Have fun powershelling your TM1 subsets!

-Greeny