Using datasource as parameter for ZeroOut

[telula]

Hello,
I have the code snippet below in Prolog.
I have a text file as datasource with Period_Name, Account, Project, Amount in the Variables tab.

I have tried to use the Period_Name from the Variables tab but it doesnt seem to work.I get a "Dimension Element not found" at SubsetElementInsert(vDimName2, vSubName, CurYr, 1); line.

How can use solve this problem?


CurYr=’20’|SUBST(Period_Name,5,2);
CurMth= SUBST(Period_Name,1,3);


vCube = 'Reporting';
vDimName1 = 'Month';
vDimName2 = 'Year';
vTemp = 'SystemUseOnly_Load' | STR(RAND, 10, 8);
vViewName = vTemp;
vSubName = vTemp;

SubsetCreate(vDimName1, vSubName);
SubsetCreate(vDimName2, vSubName);
SubsetElementInsert(vDimName1, vSubName, CurMth, 1);
SubsetElementInsert(vDimName2, vSubName, CurYr, 1);

ViewCreate(vCube, vViewName);
ViewSubsetAssign(vCube, vViewName, vDimName1, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName2, vSubName);
ViewZeroOut(vCube, vViewName);
ViewDestroy(vCube, vViewName);
SubsetDestroy(vDimName1, vSubName);
SubsetDestroy(vDimName2, vSubName);

[bskalli]

you can not read the Datasource in prolog.

[ajain86]

I would just split out your code into the prolog, data, and the epilog tabs.

Prolog:

Code: Select all

CurYr=’20’|SUBST(Period_Name,5,2);
CurMth= SUBST(Period_Name,1,3);

vCube = 'Reporting';
vDimName1 = 'Month';
vDimName2 = 'Year';
vTemp = 'SystemUseOnly_Load' | STR(RAND, 10, 8);
vViewName = vTemp;
vSubName = vTemp;

SubsetCreate(vDimName1, vSubName);
SubsetCreate(vDimName2, vSubName);

ViewCreate(vCube, vViewName);
ViewSubsetAssign(vCube, vViewName, vDimName1, vSubName);
ViewSubsetAssign(vCube, vViewName, vDimName2, vSubName);

Data:

Code: Select all

SubsetElementInsert(vDimName1, vSubName, CurMth, 1);
SubsetElementInsert(vDimName2, vSubName, CurYr, 1);

Epilog:

Code: Select all

ViewZeroOut(vCube, vViewName);
ViewDestroy(vCube, vViewName);
SubsetDestroy(vDimName1, vSubName);
SubsetDestroy(vDimName2, vSubName);

[tomok]

I would just split out your code into the prolog, data, and the epilog tabs.

You're almost there. The problem is being caused by the following statements being in the Prolog tab:

CurYr=’20’|SUBST(Period_Name,5,2);
CurMth= SUBST(Period_Name,1,3);

because they reference a variable contained in the data and that is not available until the prolog has finished processing. If you modify the suggested split to also put these two statements in the Data tab the error will stop. I don't know the structure of your text file so doing this may not make sense. YMMV.

[ajain86]

tomok, you are correct. thanks for catching my mistake.

I guess the morning coffee's effect have not kicked in yet.

[telula]

Thanks.
So with the split, it means that the zero out will be in 1 TI and the data loading in a separate TI right?

[Steve Rowe]

I've only skimmed the thread whilst listening to the end of the first day of the second test match. WooT!

Anyway you can do both the VZO and the dataload in the one TI.

In the prolog put a variable
ixFirstRow=1;

as well as all your view building stuff already listed

In the metadata do the subset element inserts as well as all the metadata stuff you are already doing as part of your data load.

Then in the data tab have at the beginning.
If (ixFirstRow=1);
ViewZeroOut(vCube, vViewName);
ixFirstRow=2;
EndIf;
Your data loading code;

HTH
Cheers,

Steve

[ajain86]

As a suggestion, I would split the 2. This way you can run either without having to run both.