Pivot Excel Output From Cube Using TI process

Post Reply
aravind.cgns
Posts: 51
Joined: Sun Sep 11, 2016 1:55 pm
OLAP Product: Cognos Tm1
Version: 10.2,11
Excel Version: 2007

Pivot Excel Output From Cube Using TI process

Post by aravind.cgns »

Hi Guys,

I am trying to export data from cube as "CSV" target file using TI process, I was sucessfully able to generate the target "CSV" file. I have two issues in this task.

1. I am unable to copy the target "CSV" file in to specified location, It is displaying an error stating unable to find the specified path. I am doing this task in TM1(10.3) cloud environment.

2. The second issue is i am copying measures dimension from cube to target file, it is generating target file with 2 columns one holding the measure dimension items and the other column holding the measure dimension element values. I want to pivot the columns into rows such that each of the measure dimension element should be displayed as column holding its value in target "csv" file.

Thanks,
Arvind
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: Pivot Excel Output From Cube Using TI process

Post by Wim Gielis »

Hello

1. I don't know what you mean with that. Can you explain in depth please ?
2. It is not a very bad idea to browse the other topics here or use the search engine. For example: http://www.tm1forum.com/viewtopic.php?f=3&t=13079
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
aravind.cgns
Posts: 51
Joined: Sun Sep 11, 2016 1:55 pm
OLAP Product: Cognos Tm1
Version: 10.2,11
Excel Version: 2007

Re: Pivot Excel Output From Cube Using TI process

Post by aravind.cgns »

Wim,

Thanks for the reply, i have gone through the other topic about ASCIIOUTPUT, but that wont fit my requirement. The issue is in my case my measure dimension has elements of both String and numeric one's.

Current Ouput in CSV:

Column A Column B
Measure Element1 RUSSPRO
Measure Element2 6/4/2012
Measure Element3 Monthly
Measure Element4 300


Expected Output :
Column A Column B Column C Column D
Measure Element1 Measure Element2 Measure Element3 Measure Element4
RUSSPRO 6/4/2012 Monthly 300

Thanks,
Arvind
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: Pivot Excel Output From Cube Using TI process

Post by Wim Gielis »

Then you select 1 measure for the data source, for example, Measure Element4.
In the Data tab, do CellGetN and CellGetS to retrieve the other 3 data fields.
Then concatenate the 4 values and do you AsciiOutput.
This is what is meant with "d Pull the values from each measure via Cellgetn" in the post of David Usherwood in the other topic.
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
Edward Stuart
Community Contributor
Posts: 247
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: Pivot Excel Output From Cube Using TI process

Post by Edward Stuart »

The key point to note is that TI processes work in a logical and prescribed manner,

Prolog runs once
Metadata runs for each record in the datasource
Data runs for each record in the datasource
Epilog runs once

Therefore if you have an ASCIIOUTPUT statement on your Data tab then you will output the details of the ASCIIOUTPUT statement for each record of the datasource

For further reading, check out the Order of Operations within a Turbo Integrator section in the Turbo Integrator Guide
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Pivot Excel Output From Cube Using TI process

Post by tomok »

Edward Stuart wrote:Data runs for each record in the datasource
To further clarify, a record, when sourced from a TM1 cube view, consists of a single intersection of one element from each dimension. That's why if you pick two measures then that's gonna mean two records. What they're saying is that you're going to have to go off the reservation and go beyond the wizard functionality and write your own code to pull more than one measure. What you do is create a consolidated measure, if you don't already have one, that adds the two or more measures you want and use that consolidated node as your source. This is done because you want your source to include all the intersections that might have a value from either measure 1 or measure 2. Then in the data tab you write CELLGETN formulas to pull the values from the individual measures and then you can craft your ASCIIOUTPUT as you see fit. This strategy is very common when you want to export a full year's worth of data and you want each month as a column in the export. You use the consolidated year in the view and then have a CELLGETN to pull each individual month
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
aravind.cgns
Posts: 51
Joined: Sun Sep 11, 2016 1:55 pm
OLAP Product: Cognos Tm1
Version: 10.2,11
Excel Version: 2007

Re: Pivot Excel Output From Cube Using TI process

Post by aravind.cgns »

Thanks a lot Tom, Got to know like how the intersection of nodes at element level works. If i get the values by using CellGetS/CellGetN and export using ASCIIOUTPUT i will be exporting the values, Will i be able to export the Measure Element as column header holding its values?? correct me if i am wrong!

Thanks,
Arvind
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Pivot Excel Output From Cube Using TI process

Post by tomok »

If you want to create a "header" record for your flat file then you set a line counter variable and when the line counter is zero, you write the header as the first statement in the data tab. The reason you have to do this all in the data tab is because TM1 will blow that record away if the do it in the prolog. Here's how:

Prolog:

Code: Select all

vLine = 0;
Data:

Code: Select all

IF(vLine = 0);
  ASCIIOutPut('YourFileName', 'Dim1Name', 'Dim2Name',...'Measure1Name', 'Measure2Name'...)
ENDIF;
The rest of data tab code follows above statement

At bottom of data tab code:

Code: Select all

vLine = vLine + 1;
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
aravind.cgns
Posts: 51
Joined: Sun Sep 11, 2016 1:55 pm
OLAP Product: Cognos Tm1
Version: 10.2,11
Excel Version: 2007

Re: Pivot Excel Output From Cube Using TI process

Post by aravind.cgns »

Thanks a lot Tom,

I have exported my cube data successfully to my target file with headers :D

Thanks,
Arvind
Post Reply