Transfering data into Cube with less dimensions
-
- Posts: 47
- 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
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.
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
- Community Contributor
- Posts: 338
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: Transfering data into Cube with less dimensions
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?
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
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
-
- Posts: 47
- 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
Hi,
Thanks for the reply. I am assuming equal split between months.
Thanks for the reply. I am assuming equal split between months.
- Elessar
- Community Contributor
- Posts: 338
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: Transfering data into Cube with less dimensions
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');
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
-
- Posts: 47
- 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
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:
Data:
Result:
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';
Code: Select all
WHILE(LoopCounter <=12);
ElName = NumberToString(LoopCounter);
CellPutN(Value, sTargetCube, G_Rada, ElName, 'Pocet');
LoopCounter = LoopCounter + 1;
end;
- Attachments
-
- result.png (117.66 KiB) Viewed 4452 times
- Elessar
- Community Contributor
- Posts: 338
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: Transfering data into Cube with less dimensions
You need to reset the counter (LoopCounter = 1) before "While" in Data tab.
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
-
- Posts: 47
- 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
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;
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Transfering data into Cube with less dimensions
Seems indeed a classic textbook case of a loop nested within another loop.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 47
- 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
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.
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Transfering data into Cube with less dimensions
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, ... );
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 47
- 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
Hi, the goal is to learn new way of doing this But I will settle for more elegant solution. Thanks.
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Transfering data into Cube with less dimensions
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
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 47
- 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
I did, it works great when using unequal distribution into the target cube, thanks for the tip Any suggestions on that subprocess?
- Elessar
- Community Contributor
- Posts: 338
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: Transfering data into Cube with less dimensions
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:
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 =)
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Transfering data into Cube with less dimensions
-TI
- Send formulas in Excel
- Send formulas in Excel
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 47
- 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
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.
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.