Transposing an ASCIIOutput within TI
-
- 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
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!
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!
-
- 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
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.
-
- 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
Genius!
Will give that a go!
Will give that a go!
-
- 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
Or browse the other recent topics. This question comes up every so often and has been discussed a number of times before.Marcus Scherer wrote:If you need an example just tell.
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
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
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Transposing an ASCIIOutput within TI
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:
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.
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:
Consider a sequence of balances like this:Marcus Scherer wrote:... use the parent of your 12 month elements in your source view.
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 |
Robin Mackenzie
-
- 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
The feeding aspect of this is covered here.
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Transposing an ASCIIOutput within TI
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.Duncan P wrote:The feeding aspect of this is covered here.
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
-
- 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
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.
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.
-
- 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
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)?
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)?
-
- 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
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.rmackenzie wrote:Duncan, out of curiosity, what do you think about the SQL method?
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.
-
- 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
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".
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".