Export Cube data in CSV file, with all Months as columns

[SBK88]

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)

[Wim Gielis]

Did you really read the other contributions in this topic ?
Did you try them ?

[Duncan P]

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.

[Jorge Rachid]

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.

[lotsaram]

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?

I could just go with Wim's earlier suggestion for someone else.

Did you really read the other contributions in this topic ?
Did you try them ?

But assuming you did actually read the other posts I will try and keep it very simple.

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 );

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).

[paulsimon]

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

[Jorge Rachid]

I did that, but now it is coming 16 rows with repeated values.

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);

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.

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?

I could just go with Wim's earlier suggestion for someone else.

Did you really read the other contributions in this topic ?
Did you try them ?

But assuming you did actually read the other posts I will try and keep it very simple.

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 );

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).

[Wim Gielis]

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.

[tomok]

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.

[Jorge Rachid]

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.

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.

[Jorge Rachid]

Hi tomok.
So to solve that I have to put only totals on my subsets on source view?
Thanks.

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.

[Jorge Rachid]

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