Tag Archives: dimension

TM1: List all subsets per dimension and their assigned aliases

powershell_2

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

Advertisements

Pimp my Data Warehouse: MDS-parametrized dimension tables

Every once and a while we need a “classification dimension” table in Data Warehouse. For example when we have a basic sales fact table and column like “(Sold)Quantity” that tells how many items have been sold within the sales transaction. The data in the column varies between 1 and 10 000 (actually there is no absolute maximum value but just to be clear here). Users often want to have different classifications depending on the Quantity column for example:

  • 1-10: Tiny sales
  • 11-50: Small sales
  • 51-100: Medium
  • 101 – 500: Huge
  • 501 – nnn: Enormous

Respectively our dimension table D_SALES_SIZE_CLASS would look like this:

CLASS_ID CLASS_NAME
1 Tiny
2 Small
3 Medium
4 Huge
5 Enormous

The problem often is that if we go by the book we need to create reference id column eg. “SALES_SIZE_CLASS_ID” in the fact table and then load the whole sales fact table from scratch. In big environments this could be a bit of a problem because the reloading could take hours and you might need to arrange a downtime window for the production DW.An alternative is use computed columns (virtual columns) and a CASE – statement to get the correct value for SALES_SIZE_CLASS_ID then we avoid the reloading of the fact table.

But, a more comprehensive way of doing this is not to alter the sales fact table at all but doing all the magic in the dimension table by generating as much rows in the dimension table as there are distinct values in the Quantity-column of the sales fact table. Like this:

QuantityAmount CLASS_ID CLASS_NAME
1 1 Tiny
2 1 Tiny
3 1 Tiny
4 1 Tiny
5 1 Tiny
6 1 Tiny
7 1 Tiny
8 1 Tiny
9 1 Tiny
10 1 Tiny
11 2 Small
12 2 Small
13 2 Small
14 2 Small
48 2 Small
49 2 Small
50 2 Small
51 3 Medium
52 3 Medium
53 3 Medium
9999 5 Enormous
10000 5 Enormous

Whenever the sales classification requirements are changing we only need to modify the dimension table rather than loading the possibly enormous sales fact table once again from the scratch.

Making it customizable and accessible by end-users with MDS

After it is implemented in DW why not make it customizable by end-users so they can change the classification whenever there is need. For that MDS comes once again handy. Traditionally one would dig and hide this deep into the logic of ETL and whenever there is a need for change you need an IT specialist to modify the ETL scripts. Now we just need to set up a special parameter entity in MDS and add some useful attributes like this:

Sales classification parameter entity in MDS

Create new entity PARAM_SALES_SIZE_CLASS with attributes MIN_VALUE and MAX_VALUE to define the limit values for each class. Very easily editable by end-users. Just make sure that the values don’t overlap each other.

SQL script to generate the sales classification dimension table rows

Then it is time for some SQL magic. We generate 10 000 rows (or any other value that represents the biggest possible sales quantity size) and assign the class levels by using the minimum and maximum values defined.

SQL query results ready for loading to dimension table

This will generate 10 000 rows that are ready to insert to our dimension table (e.g. D_SALES_SIZE_CLASS). We now have fully customizable dimension table that can be edited whenever the business users want to and when they need a different point of view for their analysis.