TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

Copy between cubes

https://www.tm1forum.com/viewtopic.php?t=10874

Page 2 of 2

Re: Copy between cubes

Posted: Wed Nov 19, 2014 6:24 am
by BariAbdul
Thanks a lot Michel and Declar.The variables are declared as strings only,Strangely when I changed the variable"value" from Numeric to String the process ran successfully ,I know the variable 'Value' should be numeric only.Could someone please let me know why the process is running successfully when value is changed to string type.Did anybody come across this before?Thanks

Code: Select all

Data tab
      NVal= CellGetN('DU_SERVICES_REPORT',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year');

      NVal1= CellGetN('DU_SERVICES_REPORT',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged  previous Year');

CellPutN(NVal,'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl);

CellPutN(NVal1,'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl);

Re: Copy between cubes

Posted: Wed Nov 19, 2014 6:33 am
by rmackenzie
What is the data source of the process? Is it a view of the DU_SERVICES_REPORT cube?

Re: Copy between cubes

Posted: Wed Nov 19, 2014 6:48 am
by BariAbdul
Thanks Robin,Yes it is view of 'DU_SERVICES_REPORT'.

Re: Copy between cubes

Posted: Wed Nov 19, 2014 7:22 am
by rmackenzie
Right - so does the sa_period dimension have any string measures?

See here

Re: Copy between cubes

Posted: Wed Nov 19, 2014 7:38 am
by BariAbdul
Thanks again Robin,Yes it does have few string elements defined in them.Meanwhile I will also go through the link you provided.

Re: Copy between cubes

Posted: Wed Nov 19, 2014 7:46 am
by rmackenzie
BariAbdul wrote:Yes it does have few string elements defined in them
OK, so that's why this error occurs:
BariAbdul wrote: Error: Meta Data procedure line (0): Cannot convert field number 8, value "SLT" to a real number
If you only want to process the numbers in the cube, then you have to build a subset of just the measures that hold numbers and then assign that subset to the view that is named as the cube view being used by the process. If you want a mixed subset of measures, both numeric and string, then the link discusses it in some detail and has code snippets.

Re: Copy between cubes

Posted: Wed Nov 19, 2014 3:07 pm
by BariAbdul
Thanks a million,Robin.Appreciate your help.I will give it a go.

Re: Copy between cubes

Posted: Sat Nov 22, 2014 12:55 pm
by BariAbdul
If you only want to process the numbers in the cube, then you have to build a subset of just the measures that hold numbers and then assign that subset to the view that is named as the cube view being used by the process. If you want a mixed subset of measures, both numeric and string, then the link discusses it in some detail and has code snippets.
Hi Robin and Gurus, This is what I have tried so far unsucessfully:
Firstly,I do have subset of numeric elements and assigned to source view:

Code: Select all

Prolog:
#Defining Source Cube,View and Dimension#
  SCub='DU_SERVICES_REPORT';

 SDim='sa_period';
 SVw='Source View';

#Making sure Source View exists for the Process##

 IF(VIEWEXISTS(SCub,SVw)=0);

      VIEWCREATE(SCub,SVw);
ENDIF;
##Creating ubset for source view##

   IF
      (SUBSETEXISTS(SDim,'Ssub')=1);

       SUBSETDELETEALLELEMENTS(SDim,'Ssub');

  ELSE;

      SUBSETCREATE(SDim,'Ssub');

  ENDIF;

##Inserting measure elements in to the source view#

    SUBSETELEMENTINSERT(SDim,'Ssub','Charged Current Year',1);
    SUBSETELEMENTINSERT(SDim,'Ssub','Charged Previous Year',2);

#Defining the view setup#
#Since Source cube consists rule derived values I want to include it#

      # VIEWROWSUPPRESSZEROESSET(SCub,'SVw',0);

       VIEWEXTRACTSKIPCALCSSET(SCub,SVw,1);
       VIEWEXTRACTSKIPRULEVALUESSET(SCub,SVw,0);

 #-------Assign Data Source---#

DataSourceType='View';
DataNameForServer='DU_SERVICES_REPORT';
DataSourceNameForClient='DU_SERVICES_REPORT';
DataSourceCubeView=SVw;
1. Below code runs fine and shows process ran successfully but no data gets copied and works only when I make "Value variable" numeric to string.

Code: Select all

Data tab
      NVal= CellGetN('DU_SERVICES_REPORT',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year');

      NVal1= CellGetN('DU_SERVICES_REPORT',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged  previous Year');


CellPutN(NVal,'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl);

CellPutN(NVal1,'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl);
But as soon as I change "Value variable "to numeric data type,It gives
",Data Source line (1) Error: MetaData procedure line (0): Cannot convert field number 8, value "002587 - Turn _Dep" to a real number"

2. I have also tried (Value_is_String=1) as below
This returns me same type of error as number one.I have tried with Value variable as Numeric first ,later as as String but no success.

Code: Select all

IF( VALUE_IS_STRING = 1 );
  CellPutS(SVALUE,'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl); 
ELSE;
 CellPutN(NVALUE, 'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl); 
ENDIF;
3. I have also tried:
Before this I did changed Value variable to String

Code: Select all

vElType = DTYPE( sa_period, 'Charge Rate Year0' );
IF( vElType @= 'N' );
  CellPutN( NUMBR(Value), 'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl );
ELSE;
  CellPutS(Value, 'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl);
ENDIF;
 similarly I did the same with 'Charge Rate Year 1'
When I put NUMBR Function before NVal, the process giving me invalid string expression error.After process being run the following error
" Data Source Line (1) Error Data procedure line 33, Cell Type is real"
I might be doing something terribly wrong Is it something to do with two string elements in me period_sa dimension,Would it help if I create subset for these and assign to source view , Appreciate our help.Thanks

Re: Copy between cubes

Posted: Sun Nov 23, 2014 3:40 am
by rmackenzie
BariAbdul wrote:Firstly,I do have subset of numeric elements and assigned to source view:

Code: Select all

Prolog:
#Defining Source Cube,View and Dimension#
  SCub='DU_SERVICES_REPORT';

 SDim='sa_period';
 SVw='Source View';

#Making sure Source View exists for the Process##

 IF(VIEWEXISTS(SCub,SVw)=0);

      VIEWCREATE(SCub,SVw);
ENDIF;
##Creating ubset for source view##

   IF
      (SUBSETEXISTS(SDim,'Ssub')=1);

       SUBSETDELETEALLELEMENTS(SDim,'Ssub');

  ELSE;

      SUBSETCREATE(SDim,'Ssub');

  ENDIF;

##Inserting measure elements in to the source view#

    SUBSETELEMENTINSERT(SDim,'Ssub','Charged Current Year',1);
    SUBSETELEMENTINSERT(SDim,'Ssub','Charged Previous Year',2);

#Defining the view setup#
#Since Source cube consists rule derived values I want to include it#

      # VIEWROWSUPPRESSZEROESSET(SCub,'SVw',0);

       VIEWEXTRACTSKIPCALCSSET(SCub,SVw,1);
       VIEWEXTRACTSKIPRULEVALUESSET(SCub,SVw,0);

 #-------Assign Data Source---#

DataSourceType='View';
DataNameForServer='DU_SERVICES_REPORT';
DataSourceNameForClient='DU_SERVICES_REPORT';
DataSourceCubeView=SVw;
You need to assign the subset to the view. I.e. your missing this line after the last SUBSETELEMENTINSERT:

Code: Select all

ViewSubsetAssign ( SCub, SVw, SDim, 'SSub' );
Also, it appears you're struggling a bit with naming conventions. You've used SCub, SVw and SDim as variable names. However, your subset name is SSub rather than SSub being a variable name which would evaluate to 'my subset' or something. Anyway, as long as you get it working the way you want, that's a step in the right direction.

Re: Copy between cubes

Posted: Sun Nov 23, 2014 2:23 pm
by BariAbdul
Thanks a lot,Robin.I couldn't appreciate enough your help.
I have assigned the subset to the source view in my script which mistakenly didn't copy paste in to the code tag.
I want to try below code like

Code: Select all

DataTab
SDim='sa_period';
# Vsa_period from variable tab#
vElType = DTYPE( sa_period,Vsa_period);
IF( vElType @= 'N' );
  CellPutN( NUMBR(Value), 'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl );
ELSE;
  CellPutS(Value, 'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl);
ENDIF;
 similarly forthe same with 'Charge Rate Year 1'
Is this the right approach.All I want to do is to pass retrieved values NVal and NVal1 in to 'SSM_EXPENSES_REPORTING Cube. The error on the NUMBR(Value) part is understandable in a sense Value is of numeric data type ,although being changed to string in variable table,but I also tried STR and NumberToString without success.Thanks again.

Re: Copy between cubes

Posted: Sun Nov 23, 2014 8:17 pm
by Wim Gielis
Hello

You can use the implicit variables NValue, SValue and Value_is_string.
This is better in my opinion.

Re: Copy between cubes

Posted: Mon Nov 24, 2014 5:21 am
by BariAbdul
Thanks Wim,I have used it before,but it gave me

",Data Source line (1) Error: MetaData procedure line (0): Cannot convert field number 8, value "002587 - Turn _Dep" to a real number"

Code: Select all

IF( VALUE_IS_STRING = 1 );
  CellPutS(SVALUE,'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl); 
ELSE;
 CellPutN(NVALUE, 'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl); 
ENDIF;

Re: Copy between cubes

Posted: Mon Nov 24, 2014 6:34 am
by rmackenzie
It smells like there's something else wrong with your process beyond just some difficulty your having with the coding.

What is "002587 - Turn _Dep" - is it an element in one of the dimensions in the cube used as a data source? If so, it seems that there's a mismatch between your data source and your variable mappings.

Re: Copy between cubes

Posted: Mon Nov 24, 2014 12:32 pm
by BariAbdul
Thanks again,Robin.You are correct ,evidently I am struggling here and it is becoming bit embarrassing for me to come back again to Gurus so many times. :oops:
What is "002587 - Turn _Dep" - is it an element in one of the dimensions in the cube used as a data source? If so, it seems that there's a mismatch between your data source and your variable mappings.
Yes you are correct 002587 - Turn _Dep" is an element of one of the dimension,I have checked the mapping it is in correct order.
Meanwhile,I have tried below code ,It ran successfully but SSM_EXPENSES_REPORTING is desperately empty.I don't know where I am going wrong here.

[codevElType = DTYPE( sa_period, 'vsa_period );
IF( vElType @= 'N' );
CellPutN( NUMBR(Value), 'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl );
#ELSE;
#CellPutS(Value, 'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge #Rate Year 0',sDim1CurEl,sDim2CurEl);
ENDIF;
similarly I did the same with 'Charge Rate Year 1][/code]

Re: Copy between cubes

Posted: Mon Nov 24, 2014 12:42 pm
by declanr
if you have pulled through an element name in what should be your cube values variable then you should stop looking at the code on the data tab until you resolve what is coming through in each variable; unless you have already sorted this?
If not, get rid of all the code currently on your metadata and data tabs and do an asciioutput of all of your variables for each source row. This will then allow you to map it all back and see what your process is actually processing.

Re: Copy between cubes

Posted: Mon Nov 24, 2014 1:41 pm
by rmackenzie
Earlier in the thread you stated that the cube used as a data source is DU_SERVICES_REPORT. But you're also posting code that is writing to SSM_EXPENSES_REPORTING...

Make sure that the variables map to DU_SERVICES_REPORT and not to SSM_EXPENSES_REPORTING!

Re: Copy between cubes

Posted: Wed Nov 26, 2014 6:30 am
by BariAbdul
Thanks a lot Declan ,Robin and other Gurus, I am really thankful for your help and appreciate it a lot. I have no success yet, but will keep trying! Thanks heaps again and Happy Thanksgiving's to folks in US.

Re: Copy between cubes

Posted: Tue Dec 09, 2014 7:00 am
by BariAbdul
Hi ,Gurus, Honestly I am really embrassed :oops: Finally what I did is I have split the process to two processes one for Numeric and other for String elements ,They are running successfully but the data is not populating in the reporting cube.

When I did assciioutput on the Data tab, What I found is my variable are not incrementing at all,The output coming along something like this

ASCIIOUTPUT('\test2,Forecast, Computer, CurrentSale,88887, 11, G8978, Charge current year,225.22,
,'Projection'
,'supplier Detail' These not incrementing simply coming off as declared.
,'general no'
,'pa_urgency'
,'pa_gross'
,'inception'
,product_status);

Could some one please help me out here.Thanks

Code: Select all

  #### ASCIIOUTPUT the data before the load

 #ASCIIOUTPUT('\\test_csv',





## Retrieve Data from the source cube##


      NVal= CellGetN('DU_SERVICES_REPORT',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year');

      NVal1= CellGetN('DU_SERVICES_REPORT',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged  previous Year');

  #### ASCIIOUTPUT the data before the load


#ASCIIOUTPUT('\test2


##Copy source cube data to Target cube###

sDim1='Projection';
sDim2='supplier Detail';
sDim3='general  no';
sDim4='pa_urgency';
sDim5='pa_gross';
sDim6='inception';
sDim7='product_status';



iLoop1=1;
iDim1Siz = DIMSIZ ( sDim1 );
iLoop2=1;
iDim2Siz = DIMSIZ ( sDim2 );

iLoop3=1;
iDim3Siz = DIMSIZ ( sDim3 );

iLoop4=1;
iDim4Siz = DIMSIZ ( sDim4 );


iLoop5=1;
iDim5Siz = DIMSIZ ( sDim5 );


iLoop6=1;
iDim6Siz = DIMSIZ ( sDim6 );


iLoop7=1;
iDim7Siz = DIMSIZ ( sDim7 );


While ( iLoop1 <= iDim1Siz );
    sDim1CurEl = DIMNM ( sDim1,iLoop1 );

WHILE ( iLoop2 <= iDim2Siz );
     sDim2CurEl = DIMNM ( sDim2,iLoop2 );

WHILE ( iLoop3 <= iDim3Siz );
     sDim3CurEl = DIMNM ( sDim3,iLoop3 );


WHILE ( iLoop4 <= iDim4Siz );

     sDim4CurEl = DIMNM ( sDim4,iLoop4 );

WHILE ( iLoop5 <= iDim5Siz );
        sDim5CurEl = DIMNM ( sDim5,iLoop5 );

WHILE ( iLoop6 <= iDim6Siz );
         sDim6CurEl = DIMNM ( sDim6,iLoop6 );

WHILE ( iLoop7 <= iDim7Siz );
         sDim7CurEl = DIMNM ( sDim7,iLoop7 );





IF (CellIsUpdateable('SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl,
sDim3CurEl,sDim4CurEl,sDim5CurEl,sDim6CurEl,sDim7CurEl)=1);

   CellPutN(NVal,'SSM_EXPENSES_REPORTING',SCENERIO,SPECIALITY,PA_EXPENSE,'BUSINESS UNITS',SEQ_DET,PART,'Charge Rate Year 0',sDim1CurEl,sDim2CurEl,sDim3CurEl,sDim4CurEl,sDim5CurEl,sDim6CurEl,sDim7CurEl);

     ASCIIOUTPUT('\\test_csv', DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',sDim1CurEl,sDim2CurEl,sDim3
CurEl,sDim4CurEl,sDim5CurEl,sDim6CurEl,sDim7CurEl);

ENDIF;

IF (CellIsUpdateable(‘DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'ChargedCurrentYear’,sDim1CurEl,sDim2CurEl,sDim3CurEl,sDim4CurEl,sDim5CurEl,sDim6CurEl,sDim7CurEl)=1);

CellPutN(NVal1, ‘DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year',sDim1CurEl,sDim2CurEl,sDim3
CurEl,sDim4CurEl,sDim5CurEl,sDim6CurEl,sDim7CurEl);
ENDIF;


   

   iLoop7 = iLoop7 + 1;
END;
   iLoop6 = iLoop6 + 1;
END;
   iLoop5 = iLoop5 + 1;
END;

   iLoop4 = iLoop4 + 1;
END;
   iLoop3 = iLoop3 + 1;
END;
  iLoop2 = iLoop2 + 1;
END;

    iLoop1= iLoop1 + 1;
END;

Re: Copy between cubes

Posted: Tue Dec 09, 2014 10:41 am
by rmackenzie
BariAbdul, I think you are over-reaching. You should just try and write one TI where you can copy cube data from one slice to another. Once you've conquered that step, you can continue to the more complex operations e.g. mixing numeric and string elements in the copy process.

The code you presented in the last post makes it look like you simply have no definite idea about the dimensional relationship between the source and target cubes. Looping through multiple dimensions in the target cube and repeatedly posting the source cube value looks, well, indiscriminate. I'm sure one of our more direct colleagues can think of something more direct to say about this.

Until you're comfortable with the basic read-from-source-cube-write-to-target-cube stuff then perhaps you are biting off more than you can chew?
All times are UTC
Page 2 of 2

Powered by phpBB® Forum Software © phpBB Limited