TM1 Data Export in Multiple Columns

2/2/2016

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:

TM1 data export 1
TM1 data export 1

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

TM1 data export 2
TM1 data export 2

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.

Execute Multiple TM1 TI Processes with Parallel Loading

​Execute Multiple TM1 TI Processes with Parallel Loading

As a TM1 power user, you are aware that a TM1 TI process sets a lock to the cube, which is loaded in a dimension maintenance function. This lock can cause end users to wait until the TI process is complete and the lock has been released. If you have a large amount of data to load, this can be very annoying to the end users, seeing that they have to wait a long time. So, it is recommended that you separate the Metadata maintenance from the data maintenance in separate TI processes and run them as a chore.   Are you aware that you can Execute Multiple TM1 TI Processes?

If long loading times are still an issue, try Parallel Loading, which is running multiple TI processes at the same time. This will allow the system to take advantage of multi-reading. You can even run the same TI process multiple times concurrently. When you define the parallel loading process make sure that all concurrent processes are loading a different segment of the data. Examples of segments could be time, products, regions, or business units.

Running Concurrent TM1 TurboIntegrator Processes –

  1. One way to run concurrent TI processes is to schedule a chore for each segment process at the same time. In the chore you will specify the segment parameters.
  2. You can also use a command-line tool to run a TI process, which will allow you to start the process from outside the Cognos TM1 server. This would allow you to also leverage non-Cognos schedulers, like Windows Scheduler, to run TM1 TI processes. The tool is called tm1runti.exe. This magically appeared in hot fix 1 for 9.5.2 and is incorporated in TM1 10. The tm1unti.exe tool will allow you to create Action buttons for executing TM1 process which can be embedded in other code, so you may be able to remove manual steps.
    For a guide on this, try this link. (You might have to cut and paste it into your browser).
    http://www-01.ibm.com/support/docview.wss?uid=swg21566543&aid=1

Execute Multiple TM1 TI Processes

If you would like help on your TM1 models, Execute Multiple TM1 TI Processes, TM1 customized training or other IBM Cognos assistance, please contact Bethanie at bnonami@lodestarsolutions. Lodestar is here to help.

Incorporate TM1 Naming Conventions in Your Model Build

TM1 is great for organizing your planning models and   Make the most of this and incorporate  TM1 Naming Conventions for your dimensions, views and TI processes.  This makes building, maintenance and training much easier in the long run, especially when you have more than one Admin.  Design a simple blueprint of your naming convention at the beginning of your implementation and keep it in your application folder for quick reference.  Another tip is to use the underscore in lieu of a space in titles, whether it be your cubes, dimensions or the title row of your data import.  For example, when creating a dimension using TI, I prefer that the variable name to show up as “Element_Name”, instead of “v1” if the underscore is not used, as would be the case if the column title was “Element Name” vs “Element_Name”.

Some other naming convention ideas:

M_Calculating_Dimension_Name: using the M_  prefix keeps the calculated dimensions in order and reminds you that this should be the last dimension used in cube building.
C_View_Name: C_  indicates this view is being used in your Contributor application
B_View_Name: B_  indicates this is used for board reports
123_Subset_Name: indicates specific to department 123
M_TI_Process_Name: indicates this would be run Monthly
D_TI_Process_Name: indicates this would be run Daily

Then organize these in your Application folders to increase efficiency and accuracy with regards to maintenance.

Have you joined the Lodestar community yet?  If not, what are you waiting for?  It take less than 1 minute and will ensure that you are kept up to date on our new blog and education as well as any new unique programs we offer.  Just log onto lodestarsolutions.com and sign up today!

X