Export Cube data in CSV file, with all Months as columns
-
- Posts: 45
- Joined: Fri Apr 17, 2015 5:55 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2013
Re: Export Cube data in CSV file, with all Months as columns
Recently I encountered a similar scenario ........
say a dimension name MEASURE
A (parent)
----- B (child1)
----- C (child2)
A = B -C
while exporting the data to csv file for all PRODUCT dimension elements.... I selected the top level of dim MEASURE (ie element A)
but the records for the PRODUCT elements having value for MEASURE B & C got excluded from the source view as I used the following code -
ViewExtractSkipCalcsSet (CubeName, ViewName, 0);
ViewExtractSkipZeroesSet (CubeName, ViewName, 1);
ViewExtractSkipRuleValuesSet(CubeName, ViewName, 0);
can't ignore ZERO SUPPRESS as cube/view size is already very heavy.....
Output file doesnt have records for those PRODUCT elements where B = C (MEASURE DImension)
I want all those PRODUCT elements to be in output csv file, which have data for MEASURE B and C.
===== PROLOG TAB =====
Creating source view
suppose Cube has 5 dimensions - COUNTRY, PRODUCT, YEAR, MONTH, MEASURE
while creating source view I selected the following (using SubsetCreate & SubsetElementInserrt functions ) -
COUNTRY - Country (top level)
PRODUCT - all leaf level elements using MDX Query
YEAR - pYear ( from parameter)
MONTH - pMonth (from parameter)
MEASURE - A (top level)
what could be the alternative approach, to get the desired result. (ie. All PRODUCT elements having non Zero values)
say a dimension name MEASURE
A (parent)
----- B (child1)
----- C (child2)
A = B -C
while exporting the data to csv file for all PRODUCT dimension elements.... I selected the top level of dim MEASURE (ie element A)
but the records for the PRODUCT elements having value for MEASURE B & C got excluded from the source view as I used the following code -
ViewExtractSkipCalcsSet (CubeName, ViewName, 0);
ViewExtractSkipZeroesSet (CubeName, ViewName, 1);
ViewExtractSkipRuleValuesSet(CubeName, ViewName, 0);
can't ignore ZERO SUPPRESS as cube/view size is already very heavy.....
Output file doesnt have records for those PRODUCT elements where B = C (MEASURE DImension)
I want all those PRODUCT elements to be in output csv file, which have data for MEASURE B and C.
===== PROLOG TAB =====
Creating source view
suppose Cube has 5 dimensions - COUNTRY, PRODUCT, YEAR, MONTH, MEASURE
while creating source view I selected the following (using SubsetCreate & SubsetElementInserrt functions ) -
COUNTRY - Country (top level)
PRODUCT - all leaf level elements using MDX Query
YEAR - pYear ( from parameter)
MONTH - pMonth (from parameter)
MEASURE - A (top level)
what could be the alternative approach, to get the desired result. (ie. All PRODUCT elements having non Zero values)
-
- MVP
- Posts: 3126
- 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: Export Cube data in CSV file, with all Months as columns
Did you really read the other contributions in this topic ?
Did you try them ?
Did you try them ?
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: 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: Export Cube data in CSV file, with all Months as columns
Let feeders be your friends. http://www.tm1forum.com/viewtopic.php?f=3&t=7439
Feeders will not suffer from the cancelling out of normal accumulation.
Feeders will not suffer from the cancelling out of normal accumulation.
-
- Posts: 113
- Joined: Fri Jul 22, 2016 8:33 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: Export Cube data in CSV file, with all Months as columns
Hey guys!
I am facing exactly this same problem right now, but I did not understand the flag point.
I am using ASCIIOutPut and I want months on columns.
I am using the cellgetn to get each month from january from december, but when I use the asciioutput the value of a data point of january for example, cames 12x on rows instead of one.
I didn't get what you have done about using flag and the total on view data source.
Can anyone explain that for me?
Thanks a lot.
JR.
I am facing exactly this same problem right now, but I did not understand the flag point.
I am using ASCIIOutPut and I want months on columns.
I am using the cellgetn to get each month from january from december, but when I use the asciioutput the value of a data point of january for example, cames 12x on rows instead of one.
I didn't get what you have done about using flag and the total on view data source.
Can anyone explain that for me?
Thanks a lot.
JR.
-
- MVP
- Posts: 3663
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Export Cube data in CSV file, with all Months as columns
I could just go with Wim's earlier suggestion for someone else.Jorge Rachid wrote: ↑Tue Jul 16, 2019 8:43 pm I am using ASCIIOutPut and I want months on columns.
I am using the cellgetn to get each month from january from december, but when I use the asciioutput the value of a data point of january for example, cames 12x on rows instead of one.
I didn't get what you have done about using flag and the total on view data source.
Can anyone explain that for me?
But assuming you did actually read the other posts I will try and keep it very simple.Wim Gielis wrote: ↑Mon Mar 27, 2017 6:15 am Did you really read the other contributions in this topic ?
Did you try them ?
You have a TI process which you control. You can edit it!
- You need to change the data source to read only "Total Year". DON'T have all the months in the source view.
- Then have 12 x CellGetN on your data tab to read in the month values. Assign each to a separate variable
- Then in the AsciiOutput write out the 12 values. Ignore the Total Year value (or include it as a 13th column)
Code: Select all
sValJan = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Jan', 'Value' ) );
sValFeb = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Feb', 'Value' ) =;
...
sValDec = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Dec', 'Value' ) );
AsciiOutput( myFile, sValJan, sValFeb, ... sValDec );
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: Export Cube data in CSV file, with all Months as columns
Hi Jorge
If by flag you are referring to the cFlag in the 6th post, that is actually nothing to do with getting the 12 months. Read the posts for that.
The cFlag is only there to output a heading row. Personally I wouldn't use a c prefix since that implies that it is a constant to be but it is being modified so it is a variable, however, we all have our own naming conventions.
The logic could be simpler. There is no need for a -1, eg
Prolog
iRowCount = 0 ;
Data Tab
iRowCount = iRowCount + 1 ;
IF( iRowCount = 1 ) ;
AsciiOutput( sPathFile, 'Account' , 'Cost Centre' , whatever headings are applicable to your file ) ;
ENDIF ;
Rest of logic deals with outputting the 12 values across the columns.
You could just use a variable as a flag eg bFirstRow = 1 on Prolog, then on Data Tab IF( bFirstRow = 1) ; output headings, followed by bFirstRow = 0 ENDIF. However, the advantage of using a RowCount is that this also gives you a count of the records that you processed which you can output in the Epilog to a logging cube.
Regards
Paul Simon
If by flag you are referring to the cFlag in the 6th post, that is actually nothing to do with getting the 12 months. Read the posts for that.
The cFlag is only there to output a heading row. Personally I wouldn't use a c prefix since that implies that it is a constant to be but it is being modified so it is a variable, however, we all have our own naming conventions.
The logic could be simpler. There is no need for a -1, eg
Prolog
iRowCount = 0 ;
Data Tab
iRowCount = iRowCount + 1 ;
IF( iRowCount = 1 ) ;
AsciiOutput( sPathFile, 'Account' , 'Cost Centre' , whatever headings are applicable to your file ) ;
ENDIF ;
Rest of logic deals with outputting the 12 values across the columns.
You could just use a variable as a flag eg bFirstRow = 1 on Prolog, then on Data Tab IF( bFirstRow = 1) ; output headings, followed by bFirstRow = 0 ENDIF. However, the advantage of using a RowCount is that this also gives you a count of the records that you processed which you can output in the Epilog to a logging cube.
Regards
Paul Simon
-
- Posts: 113
- Joined: Fri Jul 22, 2016 8:33 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: Export Cube data in CSV file, with all Months as columns
I did that, but now it is coming 16 rows with repeated values.
Here my code:
My data source view has only "Total of Months" on period dimension. No leaf elements.
Here the result of the flat file for one data point to give the example about what is happening:
[img1][/img1]
I was expecting to bring me only the green row, where I have the months on columns. But all the yellow rows are duplicated and repeat the values for the account each month.
This is the problem. I have read the post and still don't know what to do.
Here my code:
Code: Select all
vJan = NumberToString(CellGetN('base_despesas_rateio', '01/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vFev = NumberToString(CellGetN('base_despesas_rateio', '02/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vMar = NumberToString(CellGetN('base_despesas_rateio', '03/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vAbr = NumberToString(CellGetN('base_despesas_rateio', '04/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vMai = NumberToString(CellGetN('base_despesas_rateio', '05/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vJun = NumberToString(CellGetN('base_despesas_rateio', '06/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vJul = NumberToString(CellGetN('base_despesas_rateio', '07/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vAgo = NumberToString(CellGetN('base_despesas_rateio', '08/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vSet = NumberToString(CellGetN('base_despesas_rateio', '09/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vOut = NumberToString(CellGetN('base_despesas_rateio', '10/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vNov = NumberToString(CellGetN('base_despesas_rateio', '11/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
vDez = NumberToString(CellGetN('base_despesas_rateio', '12/2019', vEmpresa, vPacote, vArea, vContaOrcamentaria, vGestor, vTipoOrcamento, vTipoDespesa, vMetricas));
ASCIIOutPut ('\\bbs2tm1qa01\tm1$\BBS_Rateio_Despesas_ORC\sourcefiles\export.csv', vArea, vTipoDespesa, vMetricas, vContaOrcamentaria, vJan, vFev, vMar, vAbr, vMai, vJun, vJul, vAgo, vSet, vOut, vNov, vDez);
Here the result of the flat file for one data point to give the example about what is happening:
[img1][/img1]
I was expecting to bring me only the green row, where I have the months on columns. But all the yellow rows are duplicated and repeat the values for the account each month.
This is the problem. I have read the post and still don't know what to do.
lotsaram wrote: ↑Tue Jul 16, 2019 9:28 pmI could just go with Wim's earlier suggestion for someone else.Jorge Rachid wrote: ↑Tue Jul 16, 2019 8:43 pm I am using ASCIIOutPut and I want months on columns.
I am using the cellgetn to get each month from january from december, but when I use the asciioutput the value of a data point of january for example, cames 12x on rows instead of one.
I didn't get what you have done about using flag and the total on view data source.
Can anyone explain that for me?But assuming you did actually read the other posts I will try and keep it very simple.Wim Gielis wrote: ↑Mon Mar 27, 2017 6:15 am Did you really read the other contributions in this topic ?
Did you try them ?
You have a TI process which you control. You can edit it!
- You need to change the data source to read only "Total Year". DON'T have all the months in the source view.
- Then have 12 x CellGetN on your data tab to read in the month values. Assign each to a separate variable
- Then in the AsciiOutput write out the 12 values. Ignore the Total Year value (or include it as a 13th column)
Although now I actually did quickly scan the previous posts and I don't think I offered anything new or and simpler than what was said already (years ago).Code: Select all
sValJan = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Jan', 'Value' ) ); sValFeb = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Feb', 'Value' ) =; ... sValDec = NumberToString( CellGetN( myCube, dim1, dim2, dim3, 'Dec', 'Value' ) ); AsciiOutput( myFile, sValJan, sValFeb, ... sValDec );
- Attachments
-
- img1.JPG (86.01 KiB) Viewed 5768 times
-
- MVP
- Posts: 3126
- 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: Export Cube data in CSV file, with all Months as columns
Look at the Prolog tab and the view as the data source of the process.
You have a view defined on a cube with 9 dimensions.
Where in the csv output file do we see the dimensions that are not month, type, BU ?
Your AsciiOutput contains too little information (columns) and therefore if you have multiple records that appear to be the same, they in fact relate to other elements in the dimensions that you do not put in the file.
You have a view defined on a cube with 9 dimensions.
Where in the csv output file do we see the dimensions that are not month, type, BU ?
Your AsciiOutput contains too little information (columns) and therefore if you have multiple records that appear to be the same, they in fact relate to other elements in the dimensions that you do not put in the file.
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: 2832
- 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: Export Cube data in CSV file, with all Months as columns
If you employ the method in this thread you will have to explicitly define subsets for every dimension in the cube you are using as the data source. Since you have to turn off "Skip Consolidated Values", if you don't specify a subset for a dimension you will get every element in the dimension in your view, including consolidated nodes, which can result in duplicate records (one record for the leaf and then x number of records for each parent it rolls to. Go back to your view definition and make sure you have appropriately defined subsets for each dimension. There is about a 99% chance this is your problem.
-
- Posts: 113
- Joined: Fri Jul 22, 2016 8:33 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: Export Cube data in CSV file, with all Months as columns
Hi Wim!
The cube in fact has 9 dimension, but on my flat file I only want only some of them.
But the other dimensions has only one element for each row of the flat file, so I do not think that is the problem, but I will a export with all dimension to see what happens.
Tks.
The cube in fact has 9 dimension, but on my flat file I only want only some of them.
But the other dimensions has only one element for each row of the flat file, so I do not think that is the problem, but I will a export with all dimension to see what happens.
Tks.
Wim Gielis wrote: ↑Tue Jul 16, 2019 10:41 pm Look at the Prolog tab and the view as the data source of the process.
You have a view defined on a cube with 9 dimensions.
Where in the csv output file do we see the dimensions that are not month, type, BU ?
Your AsciiOutput contains too little information (columns) and therefore if you have multiple records that appear to be the same, they in fact relate to other elements in the dimensions that you do not put in the file.
-
- Posts: 113
- Joined: Fri Jul 22, 2016 8:33 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: Export Cube data in CSV file, with all Months as columns
Hi tomok.
So to solve that I have to put only totals on my subsets on source view?
Thanks.
So to solve that I have to put only totals on my subsets on source view?
Thanks.
tomok wrote: ↑Wed Jul 17, 2019 12:09 pm If you employ the method in this thread you will have to explicitly define subsets for every dimension in the cube you are using as the data source. Since you have to turn off "Skip Consolidated Values", if you don't specify a subset for a dimension you will get every element in the dimension in your view, including consolidated nodes, which can result in duplicate records (one record for the leaf and then x number of records for each parent it rolls to. Go back to your view definition and make sure you have appropriately defined subsets for each dimension. There is about a 99% chance this is your problem.
-
- Posts: 113
- Joined: Fri Jul 22, 2016 8:33 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: Export Cube data in CSV file, with all Months as columns
Hi guys.
I have changed the source view and put all subsets that I don't want on flat file on total.
The other dimensions that I want I put all leaf elements.
On the source view am not skipping consolidated values.
Doing this it worked fine!
Thanks a lot for everyone that helped me.
Best regards.
JR
I have changed the source view and put all subsets that I don't want on flat file on total.
The other dimensions that I want I put all leaf elements.
On the source view am not skipping consolidated values.
Doing this it worked fine!
Thanks a lot for everyone that helped me.
Best regards.
JR