Copying Data from 1 Element to Another via TI

Ideas and tips for enhancing your TM1 application
Hector2005
Posts: 38
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Re: Copying Data from 1 Element to Another via TI

Post by Hector2005 » Tue May 01, 2018 9:28 am

Hello there,
After doing this process, It raised an error in the Data Tab that "NVALUE" isn't defined.

Anything I missed?

Thanks a lot,
Mohamed Al-Bana.

User avatar
Alan Kirk
Site Admin
Posts: 5838
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Copying Data from 1 Element to Another via TI

Post by Alan Kirk » Tue May 01, 2018 10:24 am

Hector2005 wrote:
Tue May 01, 2018 9:28 am
Hello there,
After doing this process, It raised an error in the Data Tab that "NVALUE" isn't defined.

Anything I missed?

Thanks a lot,
Mohamed Al-Bana.
I think we would need to see the code before we could answer that.

However at a guess, you have tried to use NValue with a data source that is not a cube view. NValue only exists when the data source is that type.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

Hector2005
Posts: 38
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Re: Copying Data from 1 Element to Another via TI

Post by Hector2005 » Tue May 01, 2018 11:03 am

Alan Kirk wrote:
Tue May 01, 2018 10:24 am
Hector2005 wrote:
Tue May 01, 2018 9:28 am
Hello there,
After doing this process, It raised an error in the Data Tab that "NVALUE" isn't defined.

Anything I missed?

Thanks a lot,
Mohamed Al-Bana.
I think we would need to see the code before we could answer that.

However at a guess, you have tried to use NValue with a data source that is not a cube view. NValue only exists when the data source is that type.
Should I define a data source manually first?
But I think I defined it in the Prolog code.

#Prolog

Code: Select all

# ********************** Source Dimension Values **************************************#
# Type the member name if other than all


SrcPeriod = 'Total 2018';
SrcManzalaCapex = 'All'; 
SrcVersion = 'Budget';

SrcCube = 'Manzala Capex';
# ********************** Target Dimension Values **************************************#
# Make sure only the target dimension that is being updated is filled
# Only required if it is changing

# THE VALUE CAN ONLY BE A LEVEL 0 Value.

SrcPeriod = 'Total 2018';
SrcManzalaCapex = 'All'; 
TgtVersion = 'Actual';

TgtCube = 'Manzala Capex';


# Declare View name
sView = 'Manzala Test';

# Declare Dimensions
sDim1 = 'Period';
sDim2 = 'Manzala Capex';
sDim3 = 'Version';

# Declare Dimension Subsets Name
sSub1 = 'Manzala - '| SrcPeriod |'';
sSub2 = 'Manzala - '| SrcManzalaCapex |'';
sSub3 = 'Manzala - '| SrcVersion |'';



# Declare Subsets Properties
# Only used if user choses something other than All in the parameters
sSubT1 = '['| sDim1 |'].['| SrcPeriod |']';
sSubT2 = '['| sDim2 |'].['| SrcManzalaCapex |']';
sSubT3 = '['| sDim3 |'].['| SrcVersion |']';

# Destroy previous view (if exists)
if (ViewExists(SrcCube, sView) = 1);
ViewDestroy(SrcCube, sView);
endif;

#Error Check
# Destroy existing subsets
if (SubsetExists(sDim1,sSub1) = 1);
SubsetDestroy(sDim1, sSub1);
endif;
if (SubsetExists(sDim2,sSub2) = 1);
SubsetDestroy(sDim2, sSub2);
endif;
if (SubsetExists(sDim3,sSub3) = 1);
SubsetDestroy(sDim3, sSub3);
endif;

# Create subsets
if (SrcPeriod@='All');
SubsetCreatebyMDX(sSub1, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim1|'] )}, 3)}, ASC)} ' );
else;
#SubsetCreatebyMDX(sSub1, '{TM1DRILLDOWNMEMBER( {'|sSubT1|'},ALL,RECURSIVE )},3)};
SUBSETCREATE('Period',sSub1);
SUBSETELEMENTINSERT('Period',sSub1,SrcPeriod,1);
endif;

if (SrcManzalaCapex@='All');
SubsetCreatebyMDX(sSub2, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim2|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub2, '{TM1DRILLDOWNMEMBER( {'|sSubT2|'},ALL,RECURSIVE )}');
endif;

if (SrcVersion@='All');
SubsetCreatebyMDX(sSub3, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim3|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub3, '{TM1DRILLDOWNMEMBER( {'|sSubT3|'},ALL,RECURSIVE )}');
endif;




# Create View
ViewCreate(SrcCube, sView);

# Assign new view as datasource
DatasourceCubeview=sView;

# Assign Subsets
ViewSubsetAssign(SrcCube, sView, sDim1, sSub1);
ViewSubsetAssign(SrcCube, sView, sDim2, sSub2);
ViewSubsetAssign(SrcCube, sView, sDim3, sSub3);


#Arrange dimensions is view
ViewRowDimensionSet('Manzala Capex','Manzala Test','Manzala Capex',1);
ViewRowDimensionSet('Manzala Capex','Manzala Test','Version',2);


ViewColumnDimensionSet('ActualResults','Manzala Test','Period',1);

#Suppress Zeroes
ViewSuppressZeroesSet ('ActualResults','Manzala Test', 1);

#Epilog

Code: Select all

CellPutN(NValue, TgtCube, TgtPeriod,TgtManzalaCapex, TgtVersion);
Thanks in advance.

lotsaram
MVP
Posts: 3146
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Copying Data from 1 Element to Another via TI

Post by lotsaram » Tue May 01, 2018 1:03 pm

You are building a view sure.
But I see no DatasourceType=‘VIEW’ or DatasourceNameForServer=sCube in your Prolog code. If you have set up the variables using a text data source then as Alan already suggested the NValue variable will be undefined. Code only does what you tell it.

Also when using a view as a data source for TI setting row and column dimensions has absolutely zero effect since the source is read one cell at a time. Likewise ViewSupressZerosSet is valid only for user consumed views in the cube viewer. For TI datasources you need to use the ViewExtractSkip... functions.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Hector2005
Posts: 38
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Re: Copying Data from 1 Element to Another via TI

Post by Hector2005 » Tue May 01, 2018 2:30 pm

lotsaram wrote:
Tue May 01, 2018 1:03 pm
You are building a view sure.
But I see no DatasourceType=‘VIEW’ or DatasourceNameForServer=sCube in your Prolog code. If you have set up the variables using a text data source then as Alan already suggested the NValue variable will be undefined. Code only does what you tell it.

Also when using a view as a data source for TI setting row and column dimensions has absolutely zero effect since the source is read one cell at a time. Likewise ViewSupressZerosSet is valid only for user consumed views in the cube viewer. For TI datasources you need to use the ViewExtractSkip... functions.
Thanks for your info, According to your answer I wrote DatasourceType=‘VIEW’ and DatasourceNameForServer= SrcCube in my Prolog.

Exactly, I want to get the value from my cube view using code. I don't want to select the view through clicking manually. What should I do?

If I want to do this "Don't want to select cube view to fetch my data" So, I will select Script Only then I will write the code I attached in Prolog tab then the Insertion of values will be in Epilog tab because in Script Only case there is no Data tab. Is this correct?

#ProLog

Code: Select all

# ********************** Source Dimension Values **************************************#
# Type the member name if other than all


SrcPeriod = 'Total 2018';
SrcManzalaCapex = 'All'; 
SrcVersion = 'Budget';

SrcCube = 'Manzala Capex';
# ********************** Target Dimension Values **************************************#
# Make sure only the target dimension that is being updated is filled
# Only required if it is changing

# THE VALUE CAN ONLY BE A LEVEL 0 Value.

SrcPeriod = 'Total 2018';
SrcManzalaCapex = 'All'; 
TgtVersion = 'Actual';

TgtCube = 'Manzala Capex';


# Declare View name
sView = 'Manzala Test';

# Declare Dimensions
sDim1 = 'Period';
sDim2 = 'Manzala Capex';
sDim3 = 'Version';

# Declare Dimension Subsets Name
sSub1 = 'Manzala - '| SrcPeriod |'';
sSub2 = 'Manzala - '| SrcManzalaCapex |'';
sSub3 = 'Manzala - '| SrcVersion |'';



# Declare Subsets Properties
# Only used if user choses something other than All in the parameters
sSubT1 = '['| sDim1 |'].['| SrcPeriod |']';
sSubT2 = '['| sDim2 |'].['| SrcManzalaCapex |']';
sSubT3 = '['| sDim3 |'].['| SrcVersion |']';

# Destroy previous view (if exists)
if (ViewExists(SrcCube, sView) = 1);
ViewDestroy(SrcCube, sView);
endif;

#Error Check
# Destroy existing subsets
if (SubsetExists(sDim1,sSub1) = 1);
SubsetDestroy(sDim1, sSub1);
endif;
if (SubsetExists(sDim2,sSub2) = 1);
SubsetDestroy(sDim2, sSub2);
endif;
if (SubsetExists(sDim3,sSub3) = 1);
SubsetDestroy(sDim3, sSub3);
endif;

# Create subsets
if (SrcPeriod@='All');
SubsetCreatebyMDX(sSub1, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim1|'] )}, 3)}, ASC)} ' );
else;
#SubsetCreatebyMDX(sSub1, '{TM1DRILLDOWNMEMBER( {'|sSubT1|'},ALL,RECURSIVE )},3)};
SUBSETCREATE('Period',sSub1);
SUBSETELEMENTINSERT('Period',sSub1,SrcPeriod,1);
endif;

if (SrcManzalaCapex@='All');
SubsetCreatebyMDX(sSub2, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim2|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub2, '{TM1DRILLDOWNMEMBER( {'|sSubT2|'},ALL,RECURSIVE )}');
endif;

if (SrcVersion@='All');
SubsetCreatebyMDX(sSub3, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( ['|sDim3|'] )}, 0)}, ASC)} ' );
else;
SubsetCreatebyMDX(sSub3, '{TM1DRILLDOWNMEMBER( {'|sSubT3|'},ALL,RECURSIVE )}');
endif;




# Create View
ViewCreate(SrcCube, sView);

DATASOURCENAMEFORSERVER = SrcCube;
Datasourcetype = 'view';

# Assign new view as datasource
DatasourceCubeview=sView;

# Assign Subsets
ViewSubsetAssign(SrcCube, sView, sDim1, sSub1);
ViewSubsetAssign(SrcCube, sView, sDim2, sSub2);
ViewSubsetAssign(SrcCube, sView, sDim3, sSub3);


#Arrange dimensions is view
ViewRowDimensionSet('Manzala Capex','Manzala Test','Manzala Capex',1);
ViewRowDimensionSet('Manzala Capex','Manzala Test','Version',2);


ViewColumnDimensionSet('ActualResults','Manzala Test','Period',1);

#Suppress Zeroes
ViewSuppressZeroesSet ('ActualResults','Manzala Test', 1);

#Epilog

Code: Select all

CellPutN(NValue, TgtCube, TgtPeriod,TgtManzalaCapex, TgtVersion);

lotsaram
MVP
Posts: 3146
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Copying Data from 1 Element to Another via TI

Post by lotsaram » Tue May 01, 2018 8:31 pm

Go back and RTM.

Epilog code is executed once and once only after the data source connection is closed. Any CellPutN here would update only one cell.

For the data source you of course want to select a view of the source cube that you will be processing. The manually selected view serves one purpose only: to declare the variables that will be needed per the dimensionality of the cube (and if the type is a cube view then the 3 extra implicit variables nValue, sValue & value_is_string). If you use DatasourceType, DatasourceNameForServer and DatasourceCubeView on the prolog then the manually selected view will never be used during runtime.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Hector2005
Posts: 38
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Re: Copying Data from 1 Element to Another via TI

Post by Hector2005 » Thu May 03, 2018 12:55 am

lotsaram wrote:
Tue May 01, 2018 8:31 pm
Go back and RTM.

Epilog code is executed once and once only after the data source connection is closed. Any CellPutN here would update only one cell.

For the data source you of course want to select a view of the source cube that you will be processing. The manually selected view serves one purpose only: to declare the variables that will be needed per the dimensionality of the cube (and if the type is a cube view then the 3 extra implicit variables nValue, sValue & value_is_string). If you use DatasourceType, DatasourceNameForServer and DatasourceCubeView on the prolog then the manually selected view will never be used during runtime.

Dear lotsaram, Sorry for the latency but I spent much time trying to get a solution but unfortunately I cannot.
after that I know that I have a misconception ... I tried before to make a process for each cube separately but this not my target I want to create just one. If I can get a blog or an article from A - Z or if you can help to understand and Implement this type of process because all posts I saw written from the end not from the beginning.

I have a question, If I select a data source manually from a cube then I want to fetch data from another cube Is this okay?

I understood "we want to create a view to fetch data from" then make it as a source. How can I get the intersected values? How can I get each account? What are the variables I will use to manage that?


Thanks a lot,
Best Regards.

lotsaram
MVP
Posts: 3146
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Copying Data from 1 Element to Another via TI

Post by lotsaram » Thu May 03, 2018 6:17 am

Hector2005 wrote:
Thu May 03, 2018 12:55 am
I have a question, If I select a data source manually from a cube then I want to fetch data from another cube Is this okay?
Yes you can and this is no problem as long as the other cubes being set dynamically at runtinme have the same or less dimensions than the cube which was used for the data source definition.
Hector2005 wrote:
Thu May 03, 2018 12:55 am
I tried before to make a process for each cube separately but this not my target I want to create just one. If I can get a blog or an article from A - Z or if you can help to understand and Implement this type of process because all posts I saw written from the end not from the beginning.
The best open source and free to use example you are going to get for this is the bedrock library. I suggest you yo there and look at the Bedrock.Cube.Data.Copy process
Hector2005 wrote:
Thu May 03, 2018 12:55 am
I understood "we want to create a view to fetch data from" then make it as a source. How can I get the intersected values? How can I get each account? What are the variables I will use to manage that?
No one is going to write your code for you. You need to understand the fundamentals of what a TI process does and how the data is processed one-cell-at-a-time. That's all from me untill you can prove that you have read and understood the request for assistance guidelines.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Hector2005
Posts: 38
Joined: Wed Jan 31, 2018 2:01 pm
OLAP Product: MOLAP
Version: 10.2
Excel Version: 2013

Re: Copying Data from 1 Element to Another via TI

Post by Hector2005 » Fri May 04, 2018 1:10 pm

No one is going to write your code for you. You need to understand the fundamentals of what a TI process does and how the data is processed one-cell-at-a-time. That's all from me untill you can prove that you have read and understood the request for assistance guidelines.
Hello there,

Firstly, thanks for your help I appreciate that.
Secondly, This solution "TM1" doesn't have any advanced tutorials like cookbook. This my first time I faced this problem "Process that you can change your data source in runtime".
Thirdly, If you can help fine if you cannot don't disturb yourself and write words disturb the others.

Best Regards,
Hector.

Wim Gielis
MVP
Posts: 1839
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Copying Data from 1 Element to Another via TI

Post by Wim Gielis » Sat May 05, 2018 8:24 pm

Hector2005 wrote:
Thu May 03, 2018 12:55 am
If I can get a blog or an article from A - Z or if you can help to understand and Implement this type of process because all posts I saw written from the end not from the beginning.
Lotsaram pointed you to a generic TI process (Bedrock.Cube.Data.Copy in Bedrock).
Please go there and dissect the code. It's the best thing you can do now.
Any to-the-point questions that you still have on the code, can be asked and effort will be made to answer them.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Post Reply