Using parameter to specify CellPutN/S

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

Using parameter to specify CellPutN/S

Post by vasek1192 » Mon Feb 15, 2021 10:35 pm

Hi Everyone,

I am attempting to load data from CSV file into the cube Sales in the following way:
-cube has 3 dimensions: Version (1-12), Months (1-12) and metric dimension Sales (Total_Sales, A_Sales, B_Sales, Comment)
-the goal is to load the data only into the version specified by the parameter and corresponding month. January (1) corresponds to Version 1, Ferbuary to version 2 and so on.

Here is what I attempted:
insert attributes into Version dimension (viz img):
ControlCube.png
ControlCube.png (51.92 KiB) Viewed 103 times
Then in a process with CSV file as datasource I would specify the appropriate month with CellGetN command, which I would afterwards use in CellPutN/S commands. The problem is, taht no matter what parameter I use, I always insert data corresponding to the December. I have no idea why.

Variables:
Input-variables.png
Input-variables.png (57.36 KiB) Viewed 103 times
Prolog:

Code: Select all

sSourceCube = '}ElementAttributes_V_Version2';
sTargetCube = 'Sales';
sElA_Sales = 'A_Sales';
aElB_Sales = 'B_Sales';
aElComment = 'Comment';
aMonth = 'aMonth';
Data:

Code: Select all

nElVersion = NumberToString(CellGetN(sSourceCube, pVersion, aMonth));

CellPutN(vnA_Sales, sTargetCube, pVersion, nElVersion, sElA_Sales);
CellPutN(vnB_Sales, sTargetCube, pVersion, nElVersion, aElB_Sales);
CellPutS(vsComment, sTargetCube, pVersion, nElVersion, aElComment);
Wrong Result after spicifying the parameter pVersion to be 9:
WrongResult.png
WrongResult.png (71.96 KiB) Viewed 103 times
I know I dont have defined Source and Target Views which is a mistake, but in this case i presume it should not be the cause of the problem. I would appreciate any help or pointers to what might be wrong.

Adam
Posts: 41
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.4
Excel Version: Office 365 x32

Re: Using parameter to specify CellPutN/S

Post by Adam » Tue Feb 16, 2021 3:29 am

vasek1192 wrote:
Mon Feb 15, 2021 10:35 pm
The problem is, taht no matter what parameter I use, I always insert data corresponding to the December. I have no idea why.
vasek1192 wrote:
Mon Feb 15, 2021 10:35 pm
Wrong Result after spicifying the parameter pVersion to be 9.
Hi Vasek,

I’m a little confused.

You mention the data always shows in December.

Then you specify 9 as the pVersion parameter, and the screenshot shows data in (row) T_Cas.. 9 when V_Version2 (context) is filtered to 9.

What exactly is the issue?
What do you expect to see in the cube view?
Does your CSV have more lines than your example screenshot shows?
Take care.
Adam

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: Using parameter to specify CellPutN/S

Post by vasek1192 » Tue Feb 16, 2021 6:26 am

Hi,

Thanks for the reply. Yes the CSV file has 12 rows + title. The problem is not the location to which the process allocated the data. The problem is the data itself. He always puts data from December into the cells specifies by parameter. If I set pVersion as 9 he should take data from September and insert them into Version 9, September. Instead he Always takes data from December.
Input.png
Input.png (20.86 KiB) Viewed 86 times
WrongResult.png
WrongResult.png (71.96 KiB) Viewed 86 times

declanr
MVP
Posts: 1696
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Using parameter to specify CellPutN/S

Post by declanr » Tue Feb 16, 2021 8:42 am

You are using a parameter in your cellputn - this is controlling which cells you write into.

However, you have made no filter on your datasource. So the CellPutN runs for every line of your source data, so it has loaded each row into the same target cells of "Version 9". The last row of data is "December" so that is the one that ends up in the system.

You need to filter the source data in some way e.g.

Code: Select all

If ( vsTIme @= pVersion );
   # Do Stuff
Else;
   # Don't do stuff
EndIf;
I also recommend turning on cube logging, rerunning the TI process as you currently have it and looking at the transaction logs that it creates. It may help you understand what is happening a little better.
Declan Rodger

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: Using parameter to specify CellPutN/S

Post by vasek1192 » Tue Feb 16, 2021 9:00 am

You sir, saved my butt. Thanks a lot. :D

Post Reply