TM1 Forum

Does anyone know if there is a TI function that will export a cube in the same manner as right clicking the cube and choosing 'export to text file'. Is the only option to loop through every dimension and then call a textoutput command?

atxchillin wrote:Does anyone know if there is a TI function that will export a cube in the same manner as right clicking the cube and choosing 'export to text file'. Is the only option to loop through every dimension and then call a textoutput command?

You don't loop through every dimension. Data is stored at the intersection of one element from every dimension, not in the individual elements.

You simply create a data source using ViewCreate, ensure that zeroes are suppressed using ViewExtractSkipZeroesSet, probably skip consolidations as well using ViewExtractSkipCalcsSet, then assign that view as the data source for the process that will do the export. (Or you could do it on the Prolog of the same process if you really wanted to.) Don't bother assigning any subsets to the view, which will mean that all dimensions will use the implied "All" subset.

After that you just put a TextOutput or AsciiOutput statement on the Data tab to export the data.

Thank you for the response - I get it now. It looks as though if we're happy with the format of dim1, dim2, Measure1, value then we get to use a view. If we want to change that format to Dim1, dim2, measure1 value, measure 2 value... then we are still stuck looping through dimensions.

Thanks again.

atxchillin wrote:Thank you for the response - I get it now. It looks as though if we're happy with the format of dim1, dim2, Measure1, value then we get to use a view. If we want to change that format to Dim1, dim2, measure1 value, measure 2 value... then we are still stuck looping through dimensions.

Not necessarily, though you may have to do a little more work.

If you have a consolidation which adds measure 1, measure 2 etc together (or if you don't, you can create one) what you could do is this:
- Add N level subsets for Dim1 and Dim 2 to the source view.
- Add a subset consisting of the Measures consolidation alone to the source view.
- Do NOT set ViewExtractSkipCalcsSet to on, as you need the consolidation in the Measures dimension to be part of your source.

In your data tab, you can then use ElComp to loop through the components of the Measures consolidation.

In this way the main loop is still provided by the source view, the only loop you need to create is one to extract the individual measures values so that you can write them "in line" the way you've described. More to the point, you won't have to worry about iterating through stacks of zero values to generate your output.

The only thing you have to watch out for is the possibility that the various measures values don't add back to zero, thus preventing that row from appearing in your data source... though I doubt that would be a common problem.