Pivot Excel Output From Cube Using TI process
-
- 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
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
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
-
- 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
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
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
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
-
- 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
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
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
-
- 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
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.
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
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
-
- 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
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
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
-
- 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
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 monthEdward Stuart wrote:Data runs for each record in the datasource
-
- 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
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
Thanks,
Arvind
-
- 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
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:
Data:
The rest of data tab code follows above statement
At bottom of data tab code:
Prolog:
Code: Select all
vLine = 0;
Code: Select all
IF(vLine = 0);
ASCIIOutPut('YourFileName', 'Dim1Name', 'Dim2Name',...'Measure1Name', 'Measure2Name'...)
ENDIF;
At bottom of data tab code:
Code: Select all
vLine = vLine + 1;
-
- 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
Thanks a lot Tom,
I have exported my cube data successfully to my target file with headers
Thanks,
Arvind
I have exported my cube data successfully to my target file with headers
Thanks,
Arvind