Copy between cubes

[BariAbdul]

Hi Gurus, I have inherited a model where eight string and two numeric elements have been defined as measures within a time dimension. My apologies I can’t provide details of the dimensions due to data sensitivity issues.
There are total 7 dimensions in the cube and the time dimension is a row dimension whereas sequence no is column dimension.
There are pick lists (pick list cube) defined at intersection of string measures which displays according to elements being chosen from title dimensions.
Now I need to create another cube where all the pick lists defined against the row measures as separate dimensions.
So, there would be extra 7 dimensions in the new cube for which the data source will be the existing cube, which will be used by business on adhoc purpose by running data copy process.
What I am thinking about is there would be whole lot of data intersections would be created for new dimensions permutations and combinations, how the data would be copied over to these new intersections and how the data mapping would occur.
I am at loss now and would really appreciate your help.My apologies if I am not clear enough.Thanks

[Wim Gielis]

This isn't too difficult if you understand the concept of data copy processes, source view and target view with its zero out.

- Zero out the relevant cells in the target cube.
- Create (programmatically) the source view, containing the 2 numeric measures. In the Data tab, do CellGetS statements to retrieve the values for the picklist cells.
Do an Itemskip if values are empty or not part of the respective dimension in the target cube onto which you will load the numeric values.
Or, in the Metadata tab, add the pickist values to the dimensions such that you are sure that numeric values will all be written in the target cube (so in that case, do not do an itemskip).

That's it, all relatively easy I would say and not different from many other standard copy processes.

[Harvey]

You could try using rules to achieve what you want also. Performance is a major concern when doing so, but it can work very well if the data size will accomodate it.

I have a blog post about the approach you are describing where I refer to it as the "Item-based Approach". Here's the link, if you are interested:

http://blog.flowolap.com/post/2012/08/0 ... esign.aspx

[BariAbdul]

This isn't too difficult if you understand the concept of data copy processes, source view and target view with its zero out.

- Zero out the relevant cells in the target cube.
- Create (programmatically) the source view, containing the 2 numeric measures. In the Data tab, do CellGetS statements to retrieve the values for the picklist cells.
Do an Itemskip if values are empty or not part of the respective dimension in the target cube onto which you will load the numeric values.
Or, in the Metadata tab, add the pickist values to the dimensions such that you are sure that numeric values will all be written in the target cube (so in that case, do not do an itemskip).

That's it, all relatively easy I would say and not different from many other standard copy processes.

Thanks Wim,I did copied data between one element to another where the no of dimensions were same,This is something new to me where I was copying data between the 9 dimensional cube to 15 dimensional cube.I will give it go to the approach you described.Thanks again.

@Harvey,Thanks ,I can't use rule due to performance considerations.

[Wim Gielis]

This is something new to me where I was copying data between the 9 dimensional cube to 15 dimensional cube.

There is a first time for everything The concept is hardly any different from other data-integration processes.

[BariAbdul]

Thanks Wim, Sorry for the delayed response, got caught up with other issues. Finally this is test scenario I have tried for copying the data from 7 dimensional source cube data to 9 dimensional test target cube. If it comes off correctly, I could finally implement the same with 14 dimensional target cube.
Since most of the dimensions defined in the cube as picklist exist in the model, I am using these existing dimensions, I have wrote the below code and process is copying the values fine when I define individual elements of the two new dimension using CellPutN statements in Data tab. The data is getting copied over correctly.
But when I try to substitute element name with dimension name in order to copy the data for all the elements of these two new dimensions it renders an error “Data Procedure line 33, Invalid key name and element name”.
As I have defined them (two new dimensions) as new variable for example V9=’Projection’; and V10=’Supplier Details’;
But taking it off and also didn’t work.
I have also checked the dimension order of both the cube, it is in same order.
Also, Created Zero level element subsets for the both dimensions and assigned it to the target view but to no avail.
I know the error means the dimension doesn’t consist the element in question doesn’t exist in the Projection Dimension.
I can't destroy and create these dimensions in metadata as they are used in other cubes.Moreso due to the fact pick list values being rendered upon the title dimension element selection and sometime it could be empty.
Apparently by defining this new dimension as variable I am not able to call it in the process and ending up with “Invalid key name and element name” Key
Three to four dimensions have quite a few elements and writing hundreds of CellGetS is practically not feasible.
Appreciate if gurus could please help me to resolve the issue, would be very much thankful for your help. Thanks in advance.

Code: Select all

Prolog
#Global Variables #
  StringGlobalVariable('gErrMsg');
 # Local Variables #
   vNow = TimSt(Now(), '\Y\m\d\h\i\s');

#Defining Target Cube ,View and  Target Dimension#

 TCub='DU_SERVICES_REPORT_MASTER';

 TDim='sa_period_master';
  TVw='Target View';
#Switch off the logging for target cube#

CubeSetLogChanges(TCub, 0);

## Creating Target View##

  IF (VIEWEXISTS(TCub,TVw)=1);

      VIEWDESTROY(TCub,TVw);

 ENDIF;

    VIEWCREATE(TCub,TVw);

##Creating Subset for the Target cube##

     IF (SUBSETEXISTS(TDim,'Tsub')=1);
   
          SUBSETDELETEALLELEMENTS(TDim,'Tsub');

      ELSE;
  
          SUBSETCREATE(TDim,'Tsub');
    
       ENDIF;

##Inserting Measure Elements in to the Subset#
    SUBSETELEMENTINSERT(TDim,'TSub','Charged Current Year',1);
    SUBSETELEMENTINSERT(TDim,'TSub','Charged Previous Year',2);

##Assigning Subset  to the Target View#
      VIEWSUBSETASSIGN(TCub,'TargetView','sa_period_master','TSub');

## ZeroOut Target View#

     VIEWZEROOUT(TCub,TVw);
       

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

Code: Select all

Data
****Begin: Generated Statements***
V9='Projection';
V10='Supplier Details';
#****End: Generated Statements****



## 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('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut1.CSV','Scenerio','Services', 'Rate_Ta', 'Business Unit', 'Product Sequence','Part', 'Charged Current Year','Medium','Rego');


    ASCIIOUTPUT('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut1.CSV','Scenerio','Services', 'Rate_Ta', 'Business Unit', 'Product Sequence', 'Part','Charged Previous Year','Medium','Rego');


  ##Copy source cube data to Target cube###
  
   IF (CellIsUpdateable('DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year','Medium','Rego')=1);
  
       CELLPUTN(NVal,'DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year','Medium','Rego');

  ENDIF;

   ASCIIOUTPUT('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut3.CSV',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year','Medium','Rego');

   IF (CellIsUpdateable('DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year','Medium','Rego')=1);
       CELLPUTN(NVal1,'DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year','Medium','Rego');

  ENDIF;
     

   ASCIIOUTPUT('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut4.CSV',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year','Medium','Rego');

Code: Select all

EpilogEpilog
#****Begin: Generated Statements***
#****End: Generated Statements****

#Switch on the logging for target cube#

CubeSetLogChanges(TCub, 1);

#Clean up

VIEWDESTROY(TCub,TVw);

SUBSETDESTROY(TDim,'Tsub');

[Carl Lonsdale]

Some clarification questions:

1.) Does the new 9 dim cube: DU_SERVICES_REPORT_MASTER have the 7 original dimensions + Projection + Suppliers?
(Your CellPut seem to imply this is the case)
2.) Is your intention to copy all data from the original cube (7dim) to the new cube or just some specific data?
(in this case, all data for: Charged Current Year & Charged Previous Year and NONE of the other Period Data)

Currently you are creating a Source View as follows:
1. ScenarioDim -> ALL ELEMENTS
2. ServicesDim -> ALL ELEMENTS
3. Rate_TaDim -> ALL ELEMENTS
4. Buisness UnitDim -> ALL ELEMENTS
5. Product SequenceDim -> ALL ELEMENTS
6. PartDim -> ALL ELEMENTS
7. Sa_PeriodDim -> Charged Current Year & Charged Previous Year Only
* Assuming some of these are rollups this is fairly inefficient on larger cubes, but yours is a small cube

And you are trying to Map it to:
'DU_SERVICES_REPORT_MASTER',
SCENERIO,
SERVICES,
RATE_TA,
BUSINESSUNIT,
PRODUCTSEQUENCE,
PART,'
Charged Current Year or Charged Previous Year','
Medium',' (New Dim)
Rego' (New Dim)

If as I understand your previous post you would instead like to map the Values from the Source Cube to ALL NEW intersections in the Target Cube you would need to write a loop or write out the total number of CellPuts: in this case 5x8 = 40.
Because the new dims are:

Projection Dim
All Projections
Medium
High
Low
Above Expectation

AND

SuppliersDim
All Suppliers
Atlantic
Rego
Sega
Pecu
Tunm
Ruty
Yui

Correct?

If this is the case and you would like to map to all element intersections of the 2 new dimensions: Projection and Suppliers, try something like this in your data:

Code: Select all

  ##Copy source cube data to Target cube###
  sDim1 = 'Projection Dim';
  sDim2 = 'SuppliersDim';
  iLoop1=1;
  iDim1Siz = DIMSIZ ( sDim1 );
  iLoop2=1;
  iDim1Siz = DIMSIZ ( sDim2 );
  While ( iLoop1 <= iDim1Siz );
	sDim1CurEl = DIMNM ( sDim1 );
	WHILE ( iLoop2 <= iDim2Siz );
		sDim2CurEl = DIMNM ( sDim2 );
		IF (CellIsUpdateable('DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',sDim1CurEl, sDim2CurEl)=1);
		   CELLPUTN(NVal,'DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',sDim1CurEl, sDim2CurEl);
		ENDIF;
		ASCIIOUTPUT('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut3.CSV',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',sDim1CurEl, sDim2CurEl);

		IF (CellIsUpdateable('DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year',sDim1CurEl,sDim2CurEl)=1);
			CELLPUTN(NVal1,'DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year',sDim1CurEl,sDim2CurEl);
		ENDIF;
		ASCIIOUTPUT('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut4.CSV',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year',sDim1CurEl,sDim2CurEl);
		iLoop2 = iLoop2 + 1;
	END;
	iLoop1 = iLoop1 + 1;
  END;

Note this should only replace the code Starting at:
##Copy source cube data to Target cube###
And down to the end:

This will for reach record:
Find the Value in the source cube
Copy that value to EVERY corresponding intersection in the new cube, for each of the 2 NEW dimensions.
Repeat
Alternatively you could also change sDim1CurEl & sDim2CurEl to your unused Variables -> V9 & v10 respectively. This will effectively make v9 and v10 equal to ALL elements in their respective dimensions.

IF on the other hand you want to map each to a specific corresponding element in the 2 new Dimensions, you will need to write out some sort of mapping procedure to identify what goes where.

[BariAbdul]

Thanks Carl,I am really thankful for your time and effort.Here goes the clarification you have raised:

1.) Does the new 9 dim cube: DU_SERVICES_REPORT_MASTER have the 7 original dimensions + Projection + Suppliers?
(Your CellPut seem to imply this is the case)

Yes ,it does.Basically orginal cube which has picklist define somewhat cumbersome for analysis for business,this is the reason business want these picklists as title dimensions2.)
Is your intention to copy all data from the original cube (7dim) to the new cube or just some specific data?
(in this case, all data for: Charged Current Year & Charged Previous Year and NONE of the other Period Data)

The requirement is whenever business needed they want to see same data in new cube.So,I want to copy all data from (7dim)to new cube.Actually I have data define against time dimesnion elements such 201002 etc which I will add later in to the process.But first I want to done be done with new dimensions.

7. Sa_PeriodDim -> Charged Current Year & Charged Previous Year Only
Assuming some of these are rollups this is fairly inefficient on larger cubes, but yours is a small cube.

Actually most of the new dimension baring three are fairly small.

I will try your solution and let you know.Thanks heaps again for your help.

[BariAbdul]

Hi Carl,I have tried your code at work ,the process ran for 17 Min for about 240321 records successfully still the data didn't get copied over,I checked the source cube and the data was available for selection title dimensions and combination of picklist elements.
I need to do data mapping also in order to make sure same data available for combination of elements selection as is in source cube ,Any help on this please!

I am wondering what would be the performance hit if I copy the data finally to 14 dimensional cube.Now the code in my data tab looks likes this,I have also tried little variant of this (Thanks to Tomok).Appreciate your help.Thanks.

Code: Select all

Data
****Begin: Generated Statements***
V9='Projection';
V10='Supplier Details';
#****End: Generated Statements****



## 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('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut1.CSV','Scenerio','Services', 'Rate_Ta', 'Business Unit', 'Product Sequence','Part', 'Charged Current Year','Medium','Rego');


    ASCIIOUTPUT('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut1.CSV','Scenerio','Services', 'Rate_Ta', 'Business Unit', 'Product Sequence', 'Part','Charged Previous Year','Medium','Rego');
  
  ##Copy source cube data to Target cube###
  sDim1 = 'Projection Dim';
  sDim2 = 'SuppliersDim';
  iLoop1=1;
  iDim1Siz = DIMSIZ ( sDim1 );
  iLoop2=1;
  iDim1Siz = DIMSIZ ( sDim2 );
  While ( iLoop1 <= iDim1Siz );
   sDim1CurEl = DIMNM ( sDim1,Iloop1 );
   WHILE ( iLoop2 <= iDim2Siz );
      sDim2CurEl = DIMNM ( sDim2,Iloop2 );
      IF (CellIsUpdateable('DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',sDim1CurEl, sDim2CurEl)=1);
         CELLPUTN(NVal,'DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',sDim1CurEl, sDim2CurEl);
      ENDIF;
      ASCIIOUTPUT('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut3.CSV',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',sDim1CurEl, sDim2CurEl);

      IF (CellIsUpdateable('DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year',sDim1CurEl,sDim2CurEl)=1);
         CELLPUTN(NVal1,'DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year',sDim1CurEl,sDim2CurEl);
      ENDIF;
      ASCIIOUTPUT('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut4.CSV',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year',sDim1CurEl,sDim2CurEl);
      iLoop2 = iLoop2 + 1;
   END;
   iLoop1 = iLoop1 + 1;
  END;

with little bit variation:

Code: Select all

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

     x=1;

     y=1;

 While(x<=DIMSIZ('PrjectionDim'));
        
      gele=DIMNM('ProjectionDim',x);
While(y<=DIMSIZ('SuppliersDim'));
       ile=DIMNM('SuppliersDim',y);
IF (CellIsUpdateable('DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',gele, ile)=1);
         CELLPUTN(NVal,'DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',gele, ile);
      ENDIF;
      ASCIIOUTPUT('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut3.CSV',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',gele, ile);

      IF (CellIsUpdateable('DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year',gele,ile)=1);
         CELLPUTN(NVal1,'DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year',gele,ile);
      ENDIF;
      ASCIIOUTPUT('C:\Documents and Settings\Support\Desktop\TM1 Server Logs\OutPut4.CSV',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Previous Year',gele,ile);

   y=y+1;


    END;

      x=x+1;

    END;

[Carl Lonsdale]

Hey BariAbdul,
The issue I think is your view:
As I mentioned earlier currently you are ONLY copying data for:
Sa_PeriodDim -> Charged Current Year & Charged Previous Year Only

This is the ONLY data that is (Or rather should be) being copied over.

Alternatively if that IS the data you are looking for and it is not erroring out you might check to ensure the cells are writable. The code does this for you, however, since it does this for you, you will not get an error message if it doesn't write any data (It might correctly process all the records and simply not be able to write any of them). Make sure these cells are not locked via rules and or are of the correct type (Numeric vs String CellPutN vs CellPutS). Alternatively you could enable logging for the process and disable the data check for if the cells is writable.

To disable the Checks for update, just comment out the if and endif's:
Example:

#IF (CellIsUpdateable('DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',gele, ile)=1);
CELLPUTN(NVal,'DU_SERVICES_REPORT_MASTER',SCENERIO,SERVICES, RATE_TA, BUSINESSUNIT, PRODUCTSEQUENCE, PART,'Charged Current Year',gele, ile);
#ENDIF;

And rerun the process. If it throws errors you have your answer.
If it doesn't then hopefully now you have logs and you can go looking for it, perhaps it didn't get placed where you think it did?

Lastly: in so far as your question about converting it to run to 14 dimensions instead of 9. The downside to this is the run-time will increase exponentially with the total number of elements and this method will become unmanageable since you would need to go 7 While Loops deep (Bad idea) or run a rotation using Expand, which is a little complicated; also your run-time will very likely balloon to hours. You can mitigate this a little bit with IF statements to immediately place or skip records, but honestly at that point I think you will basically be have to establish a mapping procedure. Or decide on placing all the records into what are commonly referred to as buckets, a set element in each of the new dimensions that it will ALWAYS map to for ALL records.

[BariAbdul]

As I mentioned earlier currently you are ONLY copying data for:
Sa_PeriodDim -> Charged Current Year & Charged Previous Year Only

This is the ONLY data that is (Or rather should be) being copied over.

Thanks for the help,Carl.I really appreciate it.Yes ,Presently I am copying the data for these two elements of Sa_PeriodDim now.

Lastly: in so far as your question about converting it to run to 14 dimensions instead of 9. The downside to this is the run-time will increase exponentially with the total number of elements and this method will become unmanageable since you would need to go 7 While Loops deep (Bad idea) or run a rotation using Expand, which is a little complicated; also your run-time will very likely balloon to hours.

I know it is bad practice in terms of performance and run time,but when you are in support sometimes your opinion matters a little.

You can mitigate this a little bit with IF statements to immediately place or skip records, but honestly at that point I think you will basically be have to establish a mapping procedure. Or decide on placing all the records into what are commonly referred to as buckets, a set element in each of the new dimensions that it will ALWAYS map to for ALL records

Could you please elaborate it with an example ,I am sorry ,I am not getting it completely.
Thanks a million again.

[BariAbdul]

Lastly: in so far as your question about converting it to run to 14 dimensions instead of 9. The downside to this is the run-time will increase exponentially with the total number of elements and this method will become unmanageable since you would need to go 7 While Loops deep (Bad idea) or run a rotation using Expand, which is a little complicated; also your run-time will very likely balloon to hours. You can mitigate this a little bit with IF statements to immediately place or skip records, but honestly at that point I think you will basically be have to establish a mapping procedure. Or decide on placing all the records into what are commonly referred to as buckets, a set element in each of the new dimensions that it will ALWAYS map to for ALL records.

Finally I have implemented with 14 dimensions and you are quite right.The process ran for 1 hour 45 mins for 8 million records.This is terrible time for process to run.I would be thankful if you could expand little on your alternative suggestions.Thanks a lot.

[BariAbdul]

Hi Gurus,My process now runs for 25 mins for over 10 million records but it also throws an error of can't convert field to real number,I understand this error occurs for one of these :

*When trying to load the string data in to numeric cell,but all the elements of each dimension are numeric only.
*Dimension order is wrong but the order of dimension is same in both the cubes.
So I culdn't able to figure out why this particular error is happening.

Secondly How could I reduce the run time from 25 min to couple of mins at least.Please help me out,I am really holed up 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;

[Wim Gielis]

BariAbdul

I didn't re-read the whole topic again, but a couple of observations:

- 10 million is not a small number and don't be too ambitious in that it the process must run in x or y minutes. There's more to it in the equation than only this.
- Is the 25 minutes with AsciiOutput statements, or without? If with, then you can seriously cut down run time if you take them out.
- The most important thing is why the heck do you need to loop over the dimensions in the target cube? Can't the picklist values have a direct link with elements in the dimensions of the target cube?
For example, as an alias, you would be able to directly do the CellPutN or CellIncrementN. Obviously, all this looping is the reason that the process runs for half an hour. Which is even very fast if you ask me.

[BariAbdul]

10 million is not a small number and don't be too ambitious in that it the process must run in x or y minutes. There's more to it in the equation than only this.
- Is the 25 minutes with AsciiOutput statements, or without? If with, then you can seriously cut down run time if you take them out.
- The most important thing is why the heck do you need to loop over the dimensions in the target cube? Can't the picklist values have a direct link with elements in the dimensions of the target cube?
For example, as an alias, you would be able to directly do the CellPutN or CellIncrementN. Obviously, all this looping is the reason that the process runs for half an hour. Which is even very fast if you ask me.

Thanks a lot Wim,The 25 Mins is without AsciiOutput statements which I commented out,The looping is done in order to check and pass each element and pass its respective value to target cube.

The Link is here in the form of for example Projection dimension leaf level elements are defined at Projection picklist interaction. ProJection
element1
element2
and so on....

Prjection is just an element in the source cube on which Cell interaction Projection dimension (which exists in the model)leaf level elements are defined and it is dependent on little dimension elements selection.

For example, as an alias, you would be able to directly do the CellPutN or CellIncrementN. Obviously, all this looping is the reason that the process runs for half an hour. Which is even very fast if you ask me.

Sorry for slow on uptake but I really didnn't get it how could I directly do CellPutN or CellIncrementN ,Could you please expand it with an example.
Appreciate your help.

[BariAbdul]

Hi TM1 experts, Rather than starting a new thread ,I thought to continue with this one as issue is pertaining to this requirement.
Now I have other issue at hand and couldn't able to get it around.
I have facing strange issue, all dimensions have numeric elements but when I run the process
I am getting an error,
"20.20.20","Regional","Hours","PITS_CHFMI_TEWP","002","R78085","Expansion","SLT",Data Source line (23) Error: Meta Data procedure line (0): Cannot convert field number 8, value "SLT" to a real number
Here,SLT is of numeric datatype,I have deleted the destination cube and created again with different dimensional ordering ,even tried StringToNumber etc but no success.
I have done assciioutput on the original cube strangely same error popped up when i ran the process.
Any thoughts please.Thanks a lot.

[Michel Zijlema]

Cannot convert field number 8, value "SLT" to a real number
Here,SLT is of numeric datatype,I have deleted the destination cube and created again with different dimensional ordering ,even tried StringToNumber etc but no success.

Hi Abdul,

The error states that it cannot convert the value "SLT" to a number, which is logical as "SLT" is not a number...
I'm a bit puzzled by your remark underneath the error message.

Michel

[BariAbdul]

Thanks Michel,I am puzzled too! Since all the dimensions in this cube has numeric datatype elements ,still "Can't convert to real number " error I am unable to understand.

[Michel Zijlema]

Thanks Michel,I am puzzled too! Since all the dimensions in this cube has numeric datatype elements ,still "Can't convert to real number " error I am unable to understand.

Hi Abdul,

The error message is about your data source, not your cube. You receive a value "SLT" in field (variable) 8, which you declared as numeric, but there is no way "SLT" can be converted to a number.

Michel

[declanr]

Just to add a little more to Michel's point; I see a lot of new users go down this route. Just because an element is "n" type that does not mean that the element name is a number.

"SLT" is a numeric element but it is obviously actually text; as are all element names (even if the name is "100" just to add a little confusion) therefore you need to select string type on your datasource. The only things likely to be "numeric" type in a datasource are the numeric values you intend to put into a cube cell via a CellPutN... there are a few exceptions to this but that is the general concept.