Sorting TM1 text output
- Oct. 11, 2011
TM1Tutorials
This article was also posted as a Guest Article at the TM1 tutorials blog. Thanks Ben.
Introduction
The case is as follows. Recently, the customer wanted an export from a cube view showing monthly revenue values per project. The user should open the exported file in Excel, modify and import back into TM1. Exporting data to a text file format like .CSV or .TXT is not an issue; an ASCIIOUTPUT() or TEXTOUTPUT() function in a TI process with a custom view as the Data source, will do the trick. A snapshot to Excel was not possible in this case, since we have more business logic in the TI process and also, a push on the button is easier than creating a custom view and snapshots/slice it :-)
Next requirements
Next, we have the requirement of 12 months in the columns. That is less obvious, since an export from a cube view will create 12 distinct rows one below the other. A workaround is to include the consolidated “Total year ” (The sum of all months) value in the export view, and not skipping the consolidations in the view. However, this is not ideal, because it could be that monthly n elements with a value of -5 and 5 result in 0 at the consolidated level (and 0‘s are best suppressed to exclude thousands or millions of cells from processing).
The last problem is the least obvious, so we will spend more time on it. I am not sure I fully understand the order in which TM1 exports cube values to text file records. It is certainly (maybe partly) based on the index number of elements, but no matter what view layout we have, the export file is identical. Also, reordering the dimensions expressly will not change the export file. I take it that the original dimension order and the element indexes define the order in the output.
The aim is to export a list of projects that generated revenues in a chosen year, but sorted by project name in column A. The project name is an alias on the project number element names. It is possible to create a subset containing projects at n level, showing the alias, and sorted on that alias. It is possible to assign this custom subset to a view. But when TM1 processes the view, he pays no attention to ordered subsets whasoever.
Workaround
The workaround I came up with is to use a simple DOS command to sort the exported file. First, create the records in the file with code in the Data tab of the TI process. Then, in the Epilog tab, sort the file with the sort command in DOS. This can be done from the same TI process and does not require trickery or heavy coding. You do however need some extra code, obviously.
Here is sample code:
# Wim Gielis # https://www.wimgielis.com##### # Advanced > Prolog tab # 11/10/11 ###### here comes the usual code to create a custom view # then: DataSourceAsciiQuoteCharacter=''; # ADJUST HERE path='D:\MY PATH\'; batfile=path | 'code.bat'; mytempfile=path | 'data.csv'; myfile=path | 'data_sorted.csv'; AsciiOutput(batfile,'sort "' | mytempfile | '" > "' | myfile | '"');
In the Advanced > Data tab, the custom view is exported to the filename contained in the variable mytempfile. In the Epilog tab we need to execute the batch script and clean up our temp objects:
This strategy appears to work very fine. The real customer case, however, was a bit more involved since a custom header was part of the text file. We do not want to sort the file when the header is part of it, since it would screw up the order of the records. Normally, you count the exported lines in the Data tab, and ONLY create the header when the line count is 1.# Wim Gielis # https://www.wimgielis.com##### # Advanced > Epilog tab # 11/10/11 #####ExecuteCommand(batfile,1); AsciiDelete(mytempfile); AsciiDelete(batfile);
When sorting, you can write the header in a separate file (data_sorted.csv in the code above). Then, the sort command in the batch file would need to append the sorted data file to these headers:
AsciiOutput(batfile,'sort "' | mytempfile | '" >> "' | myfile | '"');
Please mind the >> instead of > to append data to an existing file.
Please drop me an email if you know of a more elegant solution. I do not think that processing the data source twice (metadata tab and data tab) will yield us anything than an increased processing time, which is not good. I would like to hear input from you if can improve on the current way of working - preferably only TM1 coding.
