Transposing an ASCIIOutput within TI

Post Reply
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Transposing an ASCIIOutput within TI

Post by 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
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: Transposing an ASCIIOutput within TI

Post by 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!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Transposing an ASCIIOutput within TI

Post by RJ! »

Genius!

Will give that a go!
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Transposing an ASCIIOutput within TI

Post by Wim Gielis »

Marcus Scherer wrote: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.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Transposing an ASCIIOutput within TI

Post by 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:
Marcus Scherer wrote:... use the parent of your 12 month elements in your source view.
Consider a sequence of balances like this:
FYJanFebMarAprMayJunJulAugSepOctNovDec
000100-10000000000
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.
Robin Mackenzie
Duncan P
MVP
Posts: 600
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: Transposing an ASCIIOutput within TI

Post by Duncan P »

The feeding aspect of this is covered here.
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Transposing an ASCIIOutput within TI

Post by rmackenzie »

Duncan P wrote: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?
Robin Mackenzie
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Transposing an ASCIIOutput within TI

Post by 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... :lol:

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!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Transposing an ASCIIOutput within TI

Post by 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
MVP
Posts: 600
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: Transposing an ASCIIOutput within TI

Post by Duncan P »

rmackenzie wrote: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!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Transposing an ASCIIOutput within TI

Post by 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".
Post Reply