Page 1 of 1
ViewSubsetAssign - what am I doing wrong?
Posted: Wed Mar 01, 2023 3:27 pm
by lcnbs
Hi,
I am trying to write a TI process that creates an alternate hierarchy within a dimension using attributes as parents. (dimension name = 'FOR_Nominal')
I was taught that when you are transferring data between cubes, you should always create and refresh a "dummy view" of the source data cube to ensure that your values are up to date and to maximize process efficiency. In this case, the source data cube is the control cube }ElementAttributes_FOR_Nominal - this has been selected within the Source Data tab, specifying to use the dummy view zUpdate. I know this view exists because I originally created it manually and can see it in the structure map on the left hand side!
My Prolog code is then, (parameter pDir)
Code: Select all
IF(ViewExists( '}ElementAttributes_FOR_Nominal', 'zUpdate' )=0);
ViewCreate( '}ElementAttributes_FOR_Nominal', 'zUpdate' );
ENDIF;
IF(SubsetExists( '}ElementAttributes_FOR_Nominal', 'zUpdate')=1);
SubsetDeleteAllElements( '}ElementAttributes_FOR_Nominal', 'zUpdate');
ELSE;
SubsetCreate( '}ElementAttributes_FOR_Nominal', 'zUpdate');
ENDIF;
SubsetElementInsert( '}ElementAttributes_FOR_Nominal', 'zUpdate', pDir, 1 );
ViewExtractSkipCalcsSet( '}ElementAttributes_FOR_Nominal', 'zUpdate', 1);
ViewExtractSkipRuleValuesSet( '}ElementAttributes_FOR_Nominal', 'zUpdate',1 );
ViewExtractSkipZeroesSet( '}ElementAttributes_FOR_Nominal', 'zUpdate',1);
ViewSubsetAssign( '}ElementAttributes_FOR_Nominal', 'zUpdate','FOR_Nominal','@L0');
ViewSubsetAssign( '}ElementAttributes_FOR_Nominal', 'zUpdate','}ElementAttributes_FOR_Nominal','zUpdate');
However when it gets down to ViewSubsetAssign it throws up an error message: "View "zUpdate" in cube "}ElementAttributes_FOR_Nominal" not found"
Any ideas please? The online reference points out that you can't assign a temporary subset to a permanent view, but from the system's point of view these subsets should be permanent.
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Wed Mar 01, 2023 5:39 pm
by ascheevel
I don't see any immediate issues in your code and was able to execute it successfully on my end using the setup your described.
There are a number of easier ways to achieve what you're trying to accomplish, the main one being
bedrock. There is already a
bedrock process for creating a hierarchy based on attributes. There is also a
bedrock process for creating a view in prolog to be used as a datsource for the TI. If that's too easy for you and you're feeling clever and stubborn, you could create a subset by MDX in the prolog and using a WHILE loop spin through the subset updating the hierarchy along the way with fewer lines of code than you've posted here.
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Thu Mar 02, 2023 9:51 am
by lcnbs
Ah, thanks @ascheeval. Good to know that it worked for you. I wonder whether it's something in the configuration of our specific system. I'll have a look at bedrock. Theoretically there's even a dedicated TI function - CreateHierarchyByAttribute() . But a bit of Googling suggested it was too buggy to be worth it, and I'd like the ability to add an extra consolidation layer anyway.
you're feeling clever and stubborn
Who are you and what are you doing in my house??
create a subset by MDX in the prolog and using a WHILE loop spin through the subset updating the hierarchy along the way
Oooh. I'll have to think about that. (I am not a software developer - merely an enthusiastic systems accountant)
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Thu Mar 02, 2023 10:16 am
by MarenC
Whats this line for:
Code: Select all
ViewSubsetAssign( '}ElementAttributes_FOR_Nominal', 'zUpdate','FOR_Nominal','@L0');
I would say if you insist on having special characters in object names then expect the unexpected. Especially if the special character is the first and last character.
I would also avoid making the subset name the same as the view name.
Maren
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Thu Mar 02, 2023 10:48 am
by lcnbs
Good points, @Maren - but I was following the conventions set out by our software developer consultant (who we are no longer getting help from) and they haven't thrown up issues before.
I'm now working on the Metadata and plan to go back to the Prolog later.
Parameter 'pDir' defines the name of the new hierarchy.
Source cube elements 'vNom' need to be leaves.
Source cube attributes 'Value' have already successfully been constructed as parents in the new hierarchy.
Code: Select all
HierarchyElementInsert('FOR_Nominal', pDir ,'', vNom ,'N');
HierarchyElementComponentAdd('FOR_Nominal', pDir , Value, vNom, 1.0);
But when I run this code, the new child elements constructed are all called 'pDir' and not 'vNom'.
Honestly, I feel like I'm slowly going mad.
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Thu Mar 02, 2023 11:10 am
by gtonkin
Probably not going to solve your problem but one of my pet hates is random variable names.
Why pDir for the new of the new hierarchy? Is this a poor convention picked up from the consultant?
Why not call it what it is, pHierarchy?
Same would go for all variables used. Be descriptive for the next person who has to read and debug, and further, use the same variable name when referring to the same object of variable across TIs, not pHierarchy in one, pHier in another etc.
Also, not a standard but wish it was, try be consistent with logically separating variables e.g. p prefix for parameters, v for datasource variables, s for strings in the script and n for numerics. Keeps it simple.
Anyway, just my two cents...
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Thu Mar 02, 2023 12:23 pm
by lcnbs
@gtonkin they're not as random as they might seem
"pDir" is the name of the Directorate entered as a parameter. Multiple alternate hierarchies will be created, one for each Directorate.
"vNom" are the nominal codes which are leaves of the original dimension in the source cube, and will form the leaves of each of the new hierarchies.
"Value" are the data points in the source cube - appreciate this one could be a bit more descriptive but it gives consistency across processes.
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Thu Mar 02, 2023 2:33 pm
by MarenC
"pDir" is the name of the Directorate entered as a parameter. Multiple alternate hierarchies will be created, one for each Directorate.
Presume you mean Multiple alternate hierarchies have been created (rather than will be), given you are inserting nominals into them.
Be interesting to see the HierarchyCreate Code for these directorates (pDir).
By the way, if you are scratching your head at While loops, being a merely enthusiastic systems accountant, then well done and good luck with jumping in with both feet re hierarchies!
Maren
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Thu Mar 02, 2023 2:46 pm
by lcnbs
Oh I understand while loops in principle, just working out which exact task you meant I should loop.
Be interesting to see the HierarchyCreate Code for these directorates (pDir)
Prolog:
Code: Select all
vCount=1;
IF(HierarchyExists('FOR_Nominal', pDir)=1);
HierarchyDeleteAllElements('FOR_Nominal', pDir );
ELSE;
HierarchyCreate( 'FOR_Nominal', pDir );
Endif;
MetaData:
Code: Select all
IF(vCount=1);
HierarchyElementInsert('FOR_Nominal', pDir ,'','Total '| pDir ,'C');
vCount=2;
EndIf;
If(Value@<>'');
HierarchyElementInsert('FOR_Nominal', pDir ,'', Value ,'C');
HierarchyElementComponentAdd('FOR_Nominal', pDir , 'Total '| pDir, Value, 1.0);
HierarchyElementInsert('FOR_Nominal', pDir ,'', vNom ,'N');
HierarchyElementComponentAdd('FOR_Nominal', pDir , Value, vNom, 1.0);
Endif;
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Thu Mar 02, 2023 4:14 pm
by lotsaram
lcnbs wrote: ↑Thu Mar 02, 2023 10:48 am
Good points, @Maren - but I was following the conventions set out by our software developer consultant (who we are no longer getting help from) and they haven't thrown up issues before.
With all due respect to your former developer. If their advice and "best practice" was to create
permanent views and subsets called "zUpdate" and use these as data sources for TI processes, well, then they were not a very good TM1 consultant.
If you are taking over maintaining this sytem my advice would be to learn how to use bedrock. It will make you life easier and you will need to write a lot less code.
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Wed Mar 08, 2023 3:32 pm
by lcnbs
my advice would be to learn how to use bedrock
Thanks. Unfortunately I have exactly zero chance of getting it installed (large public sector org with mountains of red tape). I'm also only on this project for the next four months before my secondment ends, which is really annoying but it is what it is.
I did take a look at the scripts that @ascheeval linked... clearly they're much more sophisticated in terms of error handling etc, but in essence the main code each time is the same:
Code: Select all
# Create the subset
If( SubsetExists( sDimension, sSubset ) = 1 );
SubsetDeleteAllElements( sDimension, sSubset );
Else;
SubsetCreate( sDimension, sSubset, pTemp );
EndIf;
# Attach to the view
ViewSubsetAssign( pCube, pView, sDimension, sSubset );
Code: Select all
#If parent does not exist AND allow insertion of new parents is TRUE then insert new consol
## Add the attribute value to the top node.
sElPar = sPrefix | sParent | sSuffix;
HierarchyElementinsert(pDim, sTargetHierarchy, '',sElPar, 'C');
HierarchyElementComponentAdd(pDim, sTargetHierarchy, sTopNode, sElPar, 1);
HierarchyElementinsert(pDim, sTargetHierarchy, '',vEle, 'N' );
HierarchyElementComponentAdd(pDim, sTargetHierarchy, sElPar, vEle, 1);
Having come back to it this week the compiler is now refusing to handle it in a whole new way, so I've submitted a support call to the company we're using and moving on. Wish me luck
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Wed Mar 15, 2023 7:14 pm
by WilliamSmith
ascheevel wrote: ↑Wed Mar 01, 2023 5:39 pm
I don't see any immediate issues in your code and was able to execute it successfully on my end using the setup your described.
There are a number of easier ways to achieve what you're trying to accomplish, the main one being
bedrock. There is already a
bedrock process for creating a hierarchy based on attributes. There is also a
bedrock process for creating a view in prolog to be used as a datsource for the TI. If that's too easy for you and you're feeling clever and stubborn, you could create a subset by MDX in the prolog and using a WHILE loop spin through the subset updating the hierarchy along the way with fewer lines of code than you've posted here.
Doesn't Bedrock require Cubewise ARC for TM1 paid software?
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Wed Mar 15, 2023 7:23 pm
by ascheevel
WilliamSmith wrote: ↑Wed Mar 15, 2023 7:14 pm
Doesn't Bedrock require Cubewise ARC for TM1 paid software?
Absolutely
not and for a few good reasons: Bedrock predating Arc and Cubewises's committment to Bedrock being an open source collaboration like TM1py.
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Wed Mar 15, 2023 8:58 pm
by WilliamSmith
Ok, thank you for the info, I will check it out
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Wed Mar 29, 2023 8:26 am
by lcnbs
Thought I'd update this just in case anyone is looking for similar guidance in the future.
I never did get my code to work and our consultants weren't able to offer any insight. So I gave up on that approach and instead just used the dimension (leaves) as my data source, grabbing the new parents using ATTRS.
Metadata:
Code: Select all
vGroup=ATTRS('FOR_Nominal', vNom, pDir );
IF(~vGroup@='');
HierarchyElementInsert('FOR_Nominal', pDir ,'', vGroup ,'C');
HierarchyElementComponentAdd('FOR_Nominal', pDir , 'Total '| pDir, vGroup, 1.0);
etc
I still don't know why the other didn't work, it feels like working off the control cube was throwing up a bug somewhere, but it doesn't really matter because I've got where I needed to.
Re: ViewSubsetAssign - what am I doing wrong?
Posted: Wed Mar 29, 2023 9:19 am
by MarenC
Hi,
I still don't know why the other didn't work, it feels like working off the control cube was throwing up a bug somewhere, but it doesn't really matter because I've got where I needed to.
May I remind you of this:
I would say if you insist on having special characters in object names then expect the unexpected. Especially if the special character is the first and last character.
Not saying this was your particular issue but worth reiterating again.
Maren