Page 2 of 2

Re: Copying Data from 1 Element to Another via TI

Posted: Tue May 01, 2018 9:28 am
by Hector2005
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.

Re: Copying Data from 1 Element to Another via TI

Posted: Tue May 01, 2018 10:24 am
by Alan Kirk
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.

Re: Copying Data from 1 Element to Another via TI

Posted: Tue May 01, 2018 11:03 am
by Hector2005
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.

Re: Copying Data from 1 Element to Another via TI

Posted: Tue May 01, 2018 1:03 pm
by lotsaram
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.

Re: Copying Data from 1 Element to Another via TI

Posted: Tue May 01, 2018 2:30 pm
by Hector2005
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);

Re: Copying Data from 1 Element to Another via TI

Posted: Tue May 01, 2018 8:31 pm
by lotsaram
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.

Re: Copying Data from 1 Element to Another via TI

Posted: Thu May 03, 2018 12:55 am
by Hector2005
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.

Re: Copying Data from 1 Element to Another via TI

Posted: Thu May 03, 2018 6:17 am
by lotsaram
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.

Re: Copying Data from 1 Element to Another via TI

Posted: Fri May 04, 2018 1:10 pm
by Hector2005
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.

Re: Copying Data from 1 Element to Another via TI

Posted: Sat May 05, 2018 8:24 pm
by Wim Gielis
Hector2005 wrote: Thu May 03, 2018 12:55 amIf 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.