Transfering data into Cube with less dimensions

[vasek1192]

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.

[Elessar]

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?

[vasek1192]

Hi,

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

[Elessar]

There are 2 ways:

1. Create a subset in Month dimension with desired months and loop through it (using While, SubsetGetSize and SubsetGetElementName) in Data tab
2. (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');
   

[vasek1192]

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:

[Elessar]

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

[vasek1192]

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;

[Wim Gielis]

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

[vasek1192]

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.

[Wim Gielis]

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.

[vasek1192]

Hi, the goal is to learn new way of doing this But I will settle for more elegant solution. Thanks.

[Wim Gielis]

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.

[vasek1192]

I did, it works great when using unequal distribution into the target cube, thanks for the tip Any suggestions on that subprocess?

[Elessar]

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?

[Wim Gielis]

-TI
- Send formulas in Excel

[vasek1192]

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.