Transfering data into Cube with less dimensions

Post Reply
vasek1192
Posts: 28
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Transfering data into Cube with less dimensions

Post by vasek1192 » 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.

User avatar
Elessar
Community Contributor
Posts: 236
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia

Re: Transfering data into Cube with less dimensions

Post by Elessar » 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?
Best regards, Alexander Dvoynev

vasek1192
Posts: 28
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Re: Transfering data into Cube with less dimensions

Post by vasek1192 » Tue Feb 02, 2021 12:49 pm

Hi,

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

User avatar
Elessar
Community Contributor
Posts: 236
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia

Re: Transfering data into Cube with less dimensions

Post by Elessar » Tue Feb 02, 2021 3:14 pm

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');
    
Best regards, Alexander Dvoynev

vasek1192
Posts: 28
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Re: Transfering data into Cube with less dimensions

Post by vasek1192 » 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:
Attachments
result.png
result.png (117.66 KiB) Viewed 643 times

User avatar
Elessar
Community Contributor
Posts: 236
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia

Re: Transfering data into Cube with less dimensions

Post by Elessar » Fri Feb 05, 2021 10:42 am

You need to reset the counter (LoopCounter = 1) before "While" in Data tab.
Best regards, Alexander Dvoynev

vasek1192
Posts: 28
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Re: Transfering data into Cube with less dimensions

Post by vasek1192 » 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;

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Transfering data into Cube with less dimensions

Post by Wim Gielis » Fri Feb 05, 2021 10:34 pm

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

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

vasek1192
Posts: 28
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Re: Transfering data into Cube with less dimensions

Post by vasek1192 » 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.

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Transfering data into Cube with less dimensions

Post by Wim Gielis » 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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

vasek1192
Posts: 28
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Re: Transfering data into Cube with less dimensions

Post by vasek1192 » Sun Feb 07, 2021 3:15 pm

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

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Transfering data into Cube with less dimensions

Post by Wim Gielis » 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 :D 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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

vasek1192
Posts: 28
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Re: Transfering data into Cube with less dimensions

Post by vasek1192 » 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?

User avatar
Elessar
Community Contributor
Posts: 236
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia

Re: Transfering data into Cube with less dimensions

Post by Elessar » 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?
Best regards, Alexander Dvoynev

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Transfering data into Cube with less dimensions

Post by Wim Gielis » Thu Feb 11, 2021 4:03 pm

-TI
- Send formulas in Excel 😄
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

vasek1192
Posts: 28
Joined: Sun Jan 24, 2021 5:55 pm
OLAP Product: IBM Planning analytics
Version: 2.0.9.3
Excel Version: 2019

Re: Transfering data into Cube with less dimensions

Post by vasek1192 » 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.

Post Reply