TI ASCII Output - Duplicate Rows issue

[CoSte]

Hi Everyone,

I have set up a TI Process to export data from a cube using a cube view. The cube consists of 8 dimensions and I want the results in the text file I am creating to be zero suppressed. I have run into an issue where the results in the file are producing multiple rows per Profit Center. The file should consist of 2 columns with constants, the next column has Profit Center, and the last 7 columns are data columns. The data is correct and zero suppression seems to be working fine. But if a Profit Center has three data columns with values and the other four data columns are all zeroes, I get 3 rows of the exact same data written for the Profit Center! I copied most of the Prolog below starting with my Profit Center definition because I guess it has something to do with how I am getting the Profit Center defined in the Subset. I also added what is in the Data tab:

Code: Select all

##PROLOG TAB##

###ProfitCenter Dim
strDim = 'tdeProfitCenter' ;

IF ( SUBSETEXISTS ( strDim, strView ) = 1 ) ;
   SUBSETDELETEALLELEMENTS ( strDim, strSub ) ;
ELSE ;
   SUBSETCREATE ( strDim, strSub ) ;
ENDIF ;

numDimSiz = DIMSIZ ( strDim ) ;
numCounter = 1 ;

WHILE ( numCounter <= numDimSiz);
     strElement = DIMNM ( strDim, numCounter ) ;
     IF ( ELLEV ( strDim, strElement ) = 0 ) ;
          SUBSETELEMENTINSERT ( strDim, strSub, strElement, 1 ) ;
     ENDIF ;
     numCounter = numCounter + 1 ;
END ;

VIEWSUBSETASSIGN ( strCube, strView, strDim, strSub ) ;

###############################

###Vendor Dim
strDim = 'tdeVendor' ;
strElement = 'Total Vendor' ;

IF (SUBSETEXISTS (strDim, strSub) = 0);
     SUBSETCREATE (strDim, strSub);
ENDIF;

SUBSETDELETEALLELEMENTS (strDim, strSub);
SUBSETELEMENTINSERT ( strDim, strSub, strElement, 1 );
VIEWSUBSETASSIGN ( strCube, strView, strDim, strSub );

################################

###PLAccount
strDim = 'tdePLAccount' ;
strElement = 'NSP' ;
strElement2 = 'FE' ;
strElement3 = 'FEC' ;
strElement4 = 'BE' ;
strElement5 = 'Discount' ;
strElement6 = 'Revenue' ;
strElement7 = 'Profit' ;

IF ( SUBSETEXISTS ( strDim, strSub ) = 0 ) ;
     SUBSETCREATE ( strDim, strSub ) ;
ENDIF ;

SUBSETDELETEALLELEMENTS ( strDim, strSub ) ;
SUBSETELEMENTINSERT ( strDim, strSub, strElement, 1 ) ;
SUBSETELEMENTINSERT ( strDim, strSub, strElement2, 2 ) ;
SUBSETELEMENTINSERT ( strDim, strSub, strElement3, 1 ) ;
SUBSETELEMENTINSERT ( strDim, strSub, strElement4, 1 ) ;
SUBSETELEMENTINSERT ( strDim, strSub, strElement5, 1 ) ;
SUBSETELEMENTINSERT ( strDim, strSub, strElement6, 1 ) ;
SUBSETELEMENTINSERT ( strDim, strSub, strElement7, 1 ) ;
VIEWSUBSETASSIGN ( strCube, strView, strDim, strSub ) ;

################################

###Measure
strDim = 'tdeMeasure' ;
strElement = 'Forecast' ;

IF ( SUBSETEXISTS ( strDim, strSub ) = 0 ) ;
     SUBSETCREATE ( strDim, strSub ) ;
ENDIF ;

SUBSETDELETEALLELEMENTS ( strDim, strSub ) ;
SUBSETELEMENTINSERT ( strDim, strSub, strElement, 1 ) ;
VIEWSUBSETASSIGN ( strCube, strView, strDim, strSub ) ;

### INCLUDE CONSOL ELEMENTS AND RULE BASED ELEMENTS, IGNORE ZEROS ###
VIEWEXTRACTSKIPRULEVALUESSET ( strCube, strView, 0 ) ; 
VIEWEXTRACTSKIPCALCSSET ( strCube, strView, 0 ) ; 
VIEWEXTRACTSKIPZEROESSET ( strCube, strView, 1 ) ; 

### REDEFINE SOURCE VIEW ###
DATASOURCECUBEVIEW = strView ;

### ELIMINATE QUOTES IN DELIMITER ####
DatasourceASCIIQuoteCharacter = '' ;

### MAKE OUTPUT FILE PIPE DELIMITED ###
DatasourceASCIIDelimiter =  ( CHAR (9) ) ;


##DATA TAB ##

### SET VARIABLES FOR DATA ###

vNSP = CELLGETN ( strCube , vCompany , vCurrency , vBook , vProfitCenter , vVendor , vOutlook , 'NSP' , vMeasure ) ;
vFE = CELLGETN ( strCube , vCompany , vCurrency , vBook , vProfitCenter , vVendor , vOutlook , 'FE' , vMeasure ) ;
vFEC = CELLGETN ( strCube , vCompany , vCurrency , vBook , vProfitCenter , vVendor , vOutlook , 'FEC' , vMeasure ) ;
vBE =  CELLGETN ( strCube , vCompany , vCurrency , vBook , vProfitCenter , vVendor , vOutlook , 'BE' , vMeasure ) ;
vDiscount = CELLGETN ( strCube , vCompany , vCurrency , vBook , vProfitCenter , vVendor , vOutlook , 'Discount' , vMeasure ) ;
vRev =  CELLGETN ( strCube , vCompany , vCurrency , vBook , vProfitCenter , vVendor , vOutlook , 'Revenue' , vMeasure ) ;
vProf =  CELLGETN ( strCube , vCompany , vCurrency , vBook , vProfitCenter , vVendor , vOutlook , 'Profit' , vMeasure ) ;

### EXPORT TO FILE ###

ASCIIOUTPUT ( strDataFileOutput , strForecastDate_g , strFiscalQtr_g , vProfitCenter , NUMBERTOSTRING ( vNSP ) , NUMBERTOSTRING ( vFE ) , NUMBERTOSTRING ( vFEC ) , NUMBERTOSTRING ( vBE ) , NUMBERTOSTRING ( vDiscount ) , NUMBERTOSTRING ( vRev ) , NUMBERTOSTRING ( vProf ) ) ;

Here's a sample what the resulting file looks like:

20161113 4/2017 A012345678 0 0 0 -1111.1 0 0 0
20161113 4/2017 A987654321 222222 3333.33 44444 0 0 0 0
20161113 4/2017 A987654321 222222 3333.33 44444 0 0 0 0
20161113 4/2017 A987654321 222222 3333.33 44444 0 0 0 0

Please note that most of the code seen above was copied / pasted from other TI processes we have that were written for us by external consultants, I just configured it to meet my needs for this specific TI process so my level of expertise in TI coding should be considered low (although I am trying to improve it ) Any assistance would be much appreciated.

[gtonkin]

At a very quick glance, looks like the issue may be caused by your view returning multiple "records" due to the ###PLAccount subset.
Each PL Account is added into the subset which means that when the view is returned, you will get cells in your view for populated combinations of company, profit centre etc.

The TI then reads the values of the PL Account using CellGetN for the various items so that a record can be written with each.

The problem now is that a record that looks the same is written as you view contains "records" for each PL Account giving you what is duplicated.
You would need to change your view to limit the PL Account to one item (i.e. one you know is always populated) or use a consolidation of the measures and change your
### INCLUDE CONSOL ELEMENTS AND RULE BASED ELEMENTS, IGNORE ZEROS ### section to allow consolidated items to be included.

[CoSte]

I limited the PLAccount Subset to one item as you suggested and it worked.

I used the one parent element we have in the PLAccount dim since that was the only element I could be sure would always have data. I made that change to the PLAccount Subset build in the Prolog and changed no other coding.

Not sure I completely understand the concept and why this worked But it solved my issue so thanks for that!