TM1 ZeroOut View TI Process Code Help

Post Reply
slu
Posts: 21
Joined: Sun Jan 22, 2012 8:53 pm
OLAP Product: TM1
Version: 9.4.1
Excel Version: 2007

TM1 ZeroOut View TI Process Code Help

Post by slu » Tue Feb 06, 2018 2:20 am

Hi there, I need to create a view to zero out. For some reason, the second condition did not work, could anybody help to pinpoint the problem in my TI process? I want h_accounts dim's subset to include all accounts with first digit as '1", but not include accounts last four digit as '1000' such as 1090-1000 and 1190-1000 should be excluded. It appears that all accounts not starting with 1 got excluded correctly, but second condition did not work. Any suggestion would be highly appreciated.

Code: Select all

[ELSEIF(vTgtDim @= 'h_accounts');
  IF(SUBSETEXISTS(vTgtDim, vTgtSub) = 0);
    SUBSETCREATE(vTgtDim, vTgtSub);
  ENDIF;
 
  vTgtStartIndex = 1;
    While(vTgtStartIndex<=DIMSIZ(vTgtDim));
      vTgtElement = DIMNM(vTgtDim, vTgtStartIndex);
        IF((ELLEV(vTgtDim, vTgtElement) = 0));
			IF(SUBST(vTgtElement,1,1)@='1'&SUBST(vTgtElement,6,4)@<>'1000');
				SubsetElementInsert(vTgtDim, vTgtSub, vTgtElement, 1);
			ENDIF;
        ENDIF;
      vTgtStartIndex = vTgtStartIndex + 1;
    END;[code]

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

Re: TM1 ZeroOut View TI Process Code Help

Post by lotsaram » Tue Feb 06, 2018 6:48 am

2 questions
1/ why not do it with MDX rather than the loop?
2/ is "1090-1000" the principal name or an alias? DimNm will always return the string of the principal name so if the principal name is actually 10901000 then that would explain it ...
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Mark RMBC
Regular Participant
Posts: 160
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1 ZeroOut View TI Process Code Help

Post by Mark RMBC » Tue Feb 06, 2018 9:01 am

Not sure how anyone else deals with 'on the fly' view creation but I tend to have one create view process, which has a number of parameters, including pDim1, pDim2, pDim3 etc etc to the expected maximum number of dimensions, so maybe pDim12 and pCube for the cube the view is being built against.

I would then use TabDim and Expand to create a view for any given cube.

I would include logic which said if for example pDim1 was 0 then return level 0, i.e. mdx that returned leafs only, otherwise return the string passed into the parameter, which would be mdx.

I would also have another parameter called pZero which would then zero out the view if pZero = 1.

cheers, Mark

slu
Posts: 21
Joined: Sun Jan 22, 2012 8:53 pm
OLAP Product: TM1
Version: 9.4.1
Excel Version: 2007

Re: TM1 ZeroOut View TI Process Code Help

Post by slu » Tue Feb 06, 2018 7:28 pm

Hi Lotsaram and Mark, thanks for taking time to read and answer my post, I am aware that MDX is another way to create a temp subset for a temp view (removed the view in epilog in TI), the code was developed by a developer which I just admin the TM1 model. Mark's point is to create a zero-out view process that fit all cubes, do I understand him correctly?

1) The code does have a loop using tabdim to obtain every vTgtdim for the view, I extracted only one dimension of the view which I do not need all 0 children, I need to put a filter to exclude certain children to insert into the subset. 1090-1000 is the principal name of the element, not an alias, I used subst(vTgtelement, 5,4), not working either.

2) The weird thing is that I removed the view and subset destroy code in Epilog, so the view got generated, I can see what elements are in the account dim subset, I saw 1090-1000 did not get excluded, but when I used the zero-out view as a data source, played below code, I did not text output 1090-1000.

Code: Select all


vFile='D:\temp\acctoutput.csv';
## starting position 6, length 4 retrieved the last four digit subacct. correctly
#vstring=SUBST(h_accounts,6,4);
IF (SUBST(h_accounts,1,1)@='1');
  # IF(vstring@<>'1000');
#ASCIIOUTPUT(vFile, vstring, h_accounts);
ASCIIOUTPUT(vFile,  h_accounts);
ENDIF;
ENDIF;
3) I recently ran into an issue that I used a MDX to create a temp subset, then included it in a temp view, but I can not delete the temp view because it is not a static subset, even I put 1 in the end of SubsetCreatebyMDX(SubName, MDX_Expression,1) per some suggested, I ended up not getting the dynamic subset I intended to build, I will create another thread to discuss it when I have a chance.

4) I will use planning sample to create a test cube to see whether I can reproduce the same issue there today. We recently did TM1 server p2v, hope the issue is not related to that.

Mark RMBC
Regular Participant
Posts: 160
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1 ZeroOut View TI Process Code Help

Post by Mark RMBC » Wed Feb 07, 2018 10:01 am

I have one create view process that fits all cubes, which gives the option to zero out the view if that is the purpose of creating the view. So if the purpose of creating the view is to zero it out then pZero is set to 1, if the purpose is not to zero out the view then pZero is set to 0.

But I guess this is a distraction to your immediate problem.

I don’t see anything wrong with your original code, at least the snippet you gave us.

Have you tried to create a test process that just creates a test subset?
Do you still get the issue of element with last four digits = ‘1000’ being shown?

cheers, Mark

Post Reply