In TM1 (aka Performance Management), you can use a Turbo Integrator (TI) process to not only load data, but to export data as well, using the ASCIIOutput or TextOutput functions. So for example, you finalized your budget and now you need to load it into your Accounting system.  Your TM1 data export needs to be in a particular format.  Keep in mind that when you export data, TM1 naturally wants to export the data in the style of a flat file.  This means that all your dimension elements and data are in singular columns, something along the lines of this:

However, you need it so that each month’s data is in its own column:

Not to worry, because there is a way to make this happen. The method I use is to have a “counter” that flags the data records and then use CellGetN (works with CellGetS too if your data is text) to get your values to export.  I use a counter to flag the data that is not zero and simply populate each cell at my counter element with the number 1 if my data point does not equal zero.  You can’t simply take “Total Year” as a counter, because you could very well have a situation where you have $10,000 in June and then a minus $10,000 in July, netting “Total Year” to zero.  So to flag our data, we need to add the element “Counter” (or whatever you want to name it) to your measure dimension in your cube.

I always make sure I have a measure dimension that is specific to each cube.  In my Budget_PL cube, my measure dimension originally contained only one element called “Data”, so now I can just add “Counter” to it.  For a P&L cube, you may think of your Accounts dimension as the measure dimension and not have added my extra dimension that had just the element of “Data” that I used as a holding place in case I need to add functionality to the cube.  In that case, you could create a duplicate cube that has the missing dimension and populate it via TI process or keep things a bit tidier and write the TI process to build a temporary cube that houses your flag and data, exports the data from your temporary cube and then destroys the temporary cube at the end of your process.  Also, if your data is numeric, you will need to use the NumberToString function, because TM1 needs to export the data as string.

TM1 data export in a nutshell:

Write a TI process that populates the number one on your “Counter” element coordinate if data point is not equal to zero. Make sure that you have a zero-out process scripted before you populate your target cells.

Write a second TI process that executes your first process on the Prolog tab so that your flags are current. Define a cube view on the Prolog tab in your process that is based off of your counter element and Total Year.  You don’t need the individual months in your view, because you will get that from your CellGet(s) on defined on your Data tab.  Use CellGetN (and/or CellGetS) on your Data tab and have the export code on your Data tab.

For more detailed information and scripting of these TI processes, please contact us at Sales@lodestarsolutions.com and one of our TM1 coaches will be happy to assist you.