TM1 Forum

Posted: **Tue Feb 02, 2021 11:45 am**

Hi all,

I have two cubes: Amount_All and Target. I need to transfer data from Amount_All into the Target using process. These cubes basically look at the same thing just with different granularity.

Dimensions in Amount_All: Product, Numbers_Total
Dimensions in Target: Product, Months, Numbers

For the sake of argument lets asume that Numbers_Total and Numbers have just one element.

I need to get the Amount/Numbers data from Amount_All into the Target. I heard that it involves two processes or some while cycle, but that is it. Could someone please describe how to transfer data from cube with larger number of dimensions into more granual cube? Or point me towards some tutorial/guide online?

Thanks.

Posted: **Tue Feb 02, 2021 12:24 pm**

Hi

If I understand you right, you need to transfer data to the cube with More dimensions? Amount_All cube (2 dims) -> Target cube (3 dims).
If so, how will you split data between months? Equally split it, or repeat in each month?

Posted: **Tue Feb 02, 2021 12:49 pm**

Hi,

Thanks for the reply. I am assuming equal split between months.

Posted: **Tue Feb 02, 2021 3:14 pm**

There are 2 ways:

Create a subset in Month dimension with desired months and loop through it (using While, SubsetGetSize and SubsetGetElementName) in Data tab

(easier) You can just write 12 CellPutNs in the Data tab like this:

Code: Select all

nNumber = nValue/12
CellPutN(nNumber, 'Target', vProduct, 'Jan');
CellPutN(nNumber, 'Target', vProduct, 'Feb');
...
CellPutN(nNumber, 'Target', vProduct, 'Dec');

Posted: **Fri Feb 05, 2021 10:32 am**

Thanks for the advice. I used the while version. It worked - sadly only for the first row (viz. img 1). Any advice on what went wrong and how to fix it? Thanks

Prolog:

Code: Select all

# Definice proměnných
sSourceCube = 'Pocet_Celkem';
sTargetCube = 'Zdroj';
sSourceView = 'TMP_Source';
sTargetView = 'TMP_Target';
nTempFlag = 1;
LoopCounter = 1;
sSourceDimName1 = 'G_Rada';			#G_Rada = product line
sSourceDimName2 = 'M_Pocet_Celkem';	#M_Pocet_Celkem = Total amount
sTargetDimName1 = 'G_Rada';			
sTargetDimName2 = 'T_Cas_M';			#T_Cas_M = Time dimension - consists of 12 monthy named 1, 2, 3...12
sTargetDimName3 = 'M_Zdroj';			#M_Zdroj = metrics dim. In this case it most importantly carries the Pocet (Amount) 



#___________________Source view_________________;
# Creates filters for subsets - leaf el only
sRadaFilter ='{TM1FILTERBYLEVEL( {TM1SUBSETALL( [G_Rada]   )}, 0)}';
sPocet_CelkemFilter ='{TM1FILTERBYLEVEL( {TM1SUBSETALL( [M_Pocet_Celkem]   )}, 0)}';
sCasFilter ='{TM1FILTERBYLEVEL( {TM1SUBSETALL( [T_Cas_M]   )}, 0)}';
sZdrojFilter ='{TM1FILTERBYPATTERN( {TM1SUBSETALL( [M_Zdroj]   )}, "Pocet")}';

# Deletes Source View and Subsets if they exist
IF(ViewExists( sSourceCube,sSourceView )=1); ViewDestroy(  sSourceCube,sSourceView ); ENDIF;
IF(SubsetExists( sSourceDimName1, sSourceView )=1); SubsetDestroy( sSourceDimName1, sSourceView );  ENDIF;
IF(SubsetExists( sSourceDimName2, sSourceView )=1); SubsetDestroy( sSourceDimName2, sSourceView );  ENDIF;

# Creates Subsets using created Filters
SubsetCreateByMDX(sSourceView, sRadaFilter, nTempFlag);
SubsetCreateByMDX(sSourceView, sPocet_CelkemFilter, nTempFlag);

# Creates Source View
ViewCreate(sSourceCube, sSourceView, nTempFlag);
ViewSubsetAssign(sSourceCube, sSourceView, sSourceDimName1, sSourceView);
ViewSubsetAssign(sSourceCube, sSourceView, sSourceDimName2, sSourceView);

# Rules for the Source View
ViewExtractSkipZeroesSet(sSourceCube, sSourceView, 1);
ViewExtractSkipCalcsSet(sSourceCube, sSourceView, 1);
ViewExtractSkipRuleValuesSet( sSourceCube, sSourceView, 1 );


#___________________Target View_________________;

# Deletes Target View and Subsets if they exist
IF(ViewExists( sTargetCube,sTargetView )=1); ViewDestroy(  sTargetCube,sTargetView ); ENDIF;
IF(SubsetExists( sTargetDimName1, sTargetView )=1); SubsetDestroy( sTargetDimName1, sTargetView );  ENDIF;
IF(SubsetExists( sTargetDimName2, sTargetView )=1); SubsetDestroy( sTargetDimName2, sTargetView );  ENDIF;
IF(SubsetExists( sTargetDimName3, sTargetView )=1); SubsetDestroy( sTargetDimName3, sTargetView );  ENDIF;

# Creates target Subsets using filters
SubsetCreateByMDX(sTargetView, sRadaFilter, nTempFlag);
SubsetCreateByMDX(sTargetView, sCasFilter, nTempFlag);
SubsetCreateByMDX(sTargetView, sZdrojFilter, nTempFlag);

# Creates Target View
ViewCreate(sTargetCube, sTargetView, nTempFlag);
ViewSubsetAssign(sTargetCube, sTargetView, sTargetDimName1, sTargetView);
ViewSubsetAssign(sTargetCube, sTargetView, sTargetDimName2, sTargetView);
ViewSubsetAssign(sTargetCube, sTargetView, sTargetDimName3, sTargetView);

# Creates rulůes for target View
ViewExtractSkipZeroesSet(sTargetCube, sTargetView, 1);
ViewExtractSkipCalcsSet(sTargetCube, sTargetView, 1);
ViewExtractSkipRuleValuesSet( sTargetCube, sTargetView, 1 );

# Clears Target View
ViewZeroOut(sTargetCube, sTargetView);

DataSourceNameForServer = sSourceCube;
DataSourceNameForClient = sSourceCube;
DatasourceCubeview = sSourceView;
DataSourceType = 'VIEW';

Data:

Code: Select all

WHILE(LoopCounter <=12);
	ElName = NumberToString(LoopCounter);
	CellPutN(Value, sTargetCube, G_Rada, ElName, 'Pocet');
 	LoopCounter = LoopCounter + 1;
end;

Result:

Posted: **Fri Feb 05, 2021 10:42 am**

You need to reset the counter (LoopCounter = 1) before "While" in Data tab.

Posted: **Fri Feb 05, 2021 11:51 am**

Thanks a lot

, fixed it:

Code: Select all

While(LoopCounter2 <= G_Rada_Size);
	LoopCounter = 1;

	WHILE(LoopCounter <=12);
		ElName = NumberToString(LoopCounter);
		CellPutN(Value, sTargetCube, G_Rada, ElName, 'Pocet');
 		LoopCounter = LoopCounter + 1;
	END;
    
	LoopCounter2 = LoopCounter2 + 1;

end;

Posted: **Fri Feb 05, 2021 10:34 pm**

Seems indeed a classic textbook case of a loop nested within another loop.

Posted: **Sat Feb 06, 2021 4:55 pm**

Is there another way to solve this? I mean inserting data into the cube with more dimensions. I was told it can be done more efficiently with subprocess but I used that I dont know how...Any suggestions? Thanks.

Posted: **Sat Feb 06, 2021 6:46 pm**

vasek1192 wrote: ↑Sat Feb 06, 2021 4:55 pm Is there another way to solve this? I mean inserting data into the cube with more dimensions. I was told it can be done more efficiently with subprocess but I used that I dont know how...Any suggestions? Thanks.

What problem do you try to solve ?

- is the data incorrect ?
- is the process running slowly ?
- do you prefer a more elegant solution than looping over cells ?

You could have a look at the following function:

Code: Select all

CellPutProportionalSpread( nValue, sCube, sEl1, sEl2, sEl3, ... );

But I'm not sure if that will help in this case.

Posted: **Sun Feb 07, 2021 3:15 pm**

Hi, the goal is to learn new way of doing this

But I will settle for more elegant solution. Thanks.

Posted: **Mon Feb 08, 2021 12:54 am**

vasek1192 wrote: ↑Sun Feb 07, 2021 3:15 pm Hi, the goal is to learn new way of doing this But I will settle for more elegant solution. Thanks.

Then have a look at CellPutProportionalSpread, to spread a value over existing values.
However, it's a useful approach but I'm not sure it will bring you advantages in this situation, where every child has to receive a value.

Posted: **Tue Feb 09, 2021 9:46 am**

I did, it works great when using unequal distribution into the target cube, thanks for the tip

Any suggestions on that subprocess?

Posted: **Thu Feb 11, 2021 3:50 pm**

Everybody,

This task with "Find all the ways to copy data from 2dim cube to 3dim cube" has really interested me

I can come up with 4 another ways to do ths:

Using tm1py
Using pure REST API (for the most persistent)
Using "relative proportional spread"
Just_copy_and_paste =)

Any other ideas?

Posted: **Thu Feb 11, 2021 4:03 pm**

-TI
- Send formulas in Excel

Posted: **Mon Feb 15, 2021 4:14 pm**

Found one more by mildly modifying Ellesars method using sub-process

Use static subset from the missing dimension as data source in sub-process in which you use CellGetN and CellPutN with parameter replacing missing elements. Use execute process in the main-process with the target cube as data source and parameter values drawn from the variables of the target cube. Runs pretty fast.

TM1 Forum

Transfering data into Cube with less dimensions

Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions

Re: Transfering data into Cube with less dimensions