Transposing an ASCIIOutput within TI

[RJ!]

Hi All,

I've been asked to generate a Text file for our budget numbers with the Months transposed along the top of the Text file like below:
| Cost Centre | Account | Currency | Jan 2015 | Feb 2015 | ...

Has anyone been able to get TI to be able to do this?
I'm working on creating a Cube that can collect a Concatenation of the Cost Centre + Account + Currency and then a set of generic Dimensions y1,y2,y3,... to allow me "transpose" with those Dimensions.
Still trying to work out how to get it in the Cube

If any one has a smarter idea, I'm all ears!

[Marcus Scherer]

You could use the parent of your 12 month elements in your source view. Then don't skip calcs. Then in your data tab write 12 CellGetN statements for your months on the same slice as the year value. Thus you're "generating" 12 values out of one source value. Below put your ASCIIOUTPUT statement with your 12 values converted to string. If you need an example just tell.

[RJ!]

Genius!

Will give that a go!

[Wim Gielis]

If you need an example just tell.

Or browse the other recent topics. This question comes up every so often and has been discussed a number of times before.

[rmackenzie]

If you're doing this to try and load up a table in a relational database system then it is worth looking at using SQL to do the 'transposing'. In relational-database-land they call this denormalisation and the technique in SQL looks like a lighter coding task than it does for TI. MSDN has some information:
Denormalized view for normalized tables
And this link shows a simple and more comprehensible worked example:
How to Denormalize a Normalized Table Using SQL

For our purposes, the way TM1 arranges a view for processing in a TI (a 'view extract') is effectively normalising a view you look at in the cube.

Also note that in TI you can run into problems with things like general ledger data. For example, if following Marcus' (very good) advice to:

... use the parent of your 12 month elements in your source view.

Consider a sequence of balances like this:

FY	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
0	0	0	100	-100	0	0	0	0	0	0	0	0

If the view is zero suppressed then the Full year element will be suppressed in this case and therefore won't get picked up in the datasource view. To code around this, you need an attribute on the account to use as the output flag rather than the Full Year result. As you can see, the is now getting more elaborate and there could be further exceptions required.So, for some cases, the SQL method may be a simpler way of getting data in the database, if that is what you are trying to do.

[Duncan P]

The feeding aspect of this is covered here.

[rmackenzie]

The feeding aspect of this is covered here.

Duncan, out of curiosity, what do you think about the SQL method? It is still kind of difficult, right? But still, what with the need for dummy months and additional rules/ feeders to control the dummy month to get the denormalised output, I feel this is somewhat onerous to do in TM1. Some particular script that works for one cube view can't easily be 'genericised' for another.

What would be great is some sort of TI functionality to do a ViewOutput. It's sort of possible using the ODBO data-source methods but the way it works with the member levels still doesn't suit what the OP is after - a straight dump of the view to CSV/ CMA. This question comes up so much it is interesting to speculate on the types of TI functionality that could be implemented... in a perfect world... don't you think?

[RJ!]

Thanks all for the suggestions!

It's actually for a text file output that our Regional Finance team wants, thought I'd do it all in TM1 instead of via a pivot table.
For the amount of time spent on it vs. the number of times it'll get used...

I looked at the link that Duncan posted, i will try that and see how it goes.
I've managed to extract the data & reconcile it already & lucky for me there are no "years" totalling $0.

[RJ!]

Just a thought...

Why not Select both Year + All Months in the Year Consolidation in your Period Subset.
Then in the TI, have an ItemSkip for any Period Element that is not 4 digits?

I'm aware that it'll be slightly more overhead, but I'm sure it'd consume less resources than a rule (in the long run anyway)?

[Duncan P]

Duncan, out of curiosity, what do you think about the SQL method?

I had a look at the links and it seems that the automatic generation of the query that you would have to use is at least as complicated as any combination of structuring and specialist TI that you would have to do to achieve this purely in TI.

Additionally this is OK if you have the normalised tables already defined in your RDBMS but a lot of work otherwise. Finally I would be very wary of introducing another layer of technology if it were not absolutely necessary. I've never really been a fan of debugging ODBC connections from TM1.

[RJ!]

Resolved it by TI Code.

Set up 2 TI Processes:
1st TI: With View that has all Months: In the Metadata tab, trawl through & concatenate all the non-vlaue Dimensions to be used in the output file, add this "Key" as an Element in a temp Dimension.
In the Data Tab, push the value from the View in to an attribute (in my case, used generic p1,p2,p3 etc), ensuring that you are rolling any existing balances over. Just note you'll need logic to stop your Values being overstated by the number of periods your loading! Note I used a "SubsetGetSize" function to be able to create the required number of generic "periods" when creating the temp Dimesion (it gets deleted at the end of the process!)
2nd TI: Export from the Dimension View, feed the Period Count so loop can be used to determine number of Periods to be "transposed".