MDX to get Surrogate Element from Primary

[gtonkin]

I have a Company dimension with multiple level - 3 to be exact - N=Branch, 1=Division, 2=Total
Some items that are stored in the cube need to be stored against the Division only.
To cater for this, each Division has a surrogate/proxy element e.g. Division A-Input which is an N level and a child of Division A in this example.
The surrogate element is not always the first or last child under each Division as the hierarchy is ordered by hierarchy and by name to keep everything alphabetical.
I have added a TEXT attribute so that I can store the name of the surrogate element against each division only.

What I need to do is create a subset via MDX yielding a list of the surrogates, not the Divisions as these are the elements with data that I may need to zero out rather than using an all N.

I would like to try and avoid using TI and looping through all C level elements where the attribute is empty and building a static subset by inserting the surrogates one by one. I do not want to just take the principal name and suffix '-Input' to it as I am guessing elements and mappings will change over time and no doubt something will break. The dynamic route would be preferable.

I have been fiddling with StrToMember and TM1Member but keep running into the fundamental problem of the reverse look-up or somehow doing a select.
I have also trawled many posts to see where someone was trying to do something similar but have not hit on anything yet.

Dimension=COMPANY
Attribute=SURROGATE

[Division A] would have a surrogate of [Division A-Input] for example - the MDX needs to return [Division A-Input]

Any ideas?

Thanks in advance

[tomok]

Why make it so difficult? Just add a text attribute to the Company dimension called "Surrogate" and put a "Y" in there if it is. Then you can create an MDX statement to filter for all elements that have a "Y" in the surrogate attribute.

[gtonkin]

Thanks for the quick response Tom!
The flag has been my traditional approach but was trying to go for some kind of "link" that may be more flexible in the future.
I guess having both the name of the surrogate element in one attribute and the indicator in another, you would get around most selections/updates/references that I can think of.

Would still be interested to see if there is essentially a way to take values in attributes and convert these to elements for a subset.

[tomok]

Would still be interested to see if there is essentially a way to take values in attributes and convert these to elements for a subset.

Can't see how that is possible unless the attribute is also an alias. MDX returns elements, not attribute values. Yes, you can filter elements by their attribute values but you are still returning elements. In your case you want to return an attribute from MDX. doesn't matter that the value in the attribute might also represent the name of another element. You can look up an attribute from a given element but you can't do the opposite. Sure would be a nice feature if you could. I suppose you could create a separate dimension of just the surrogate elements and make the primary element an attribute of that. Then your MDX could be based on the surrogate dimension. The only problem here is you would only be able to use that NDX in an active form report, no cube views.

[gtonkin]

Tom, you are right, I am magically looking to produce a list and transform that list into Elements.
It is looking doubtful that this can be done so may have to reluctantly concede and go with an attribute for filtering and another for referencing in TI/Rules.
Thanks again for your effort and insight-much appreciated.

[rmackenzie]

Try this:

Code: Select all

{ Filter(
  { TM1FilterByLevel( { TM1SubsetAll( [Company] )}, 0 )},
  [Company].CurrentMember.Parent.Properties("Surrogate") = [Company].CurrentMember.Name
)}

Does that work?

[gtonkin]

Robin you are a star!

Works perfectly - My dimension has an extra level added but using ...CurrentMember.Parent.Parent.Properties... I get the much required result.

I think the gain with being able to select the elements like this is not having to put the flag on the surrogates. This always becomes a bit of a design issue i.e. Do you let the custodian update the Surrogate attribute and then via TI set the flag or have them do both - having this MDX solves this extra step. This method personally just seems more elegant.

Thanks again - I hate to concede when I know there must be a better way.

[lotsaram]

Works perfectly - My dimension has an extra level added but using ...CurrentMember.Parent.Parent.Properties... I get the much required result.

What happens in a non-leveled dimension or a dimension with alternate hierarchies, does it still work?

[gtonkin]

What happens in a non-leveled dimension or a dimension with alternate hierarchies, does it still work?

Thanks for raising this lotsaram.The Parent or Parent.Parent solution will definitely not work on a staggered/non-leveled dimension - That is why I started down the road of TM1Member and StrToMember, hoping for a nice generic solution to fit all cases.
For my immediate requirements, luckily I am working with a fairly simple non-staggered hierarchy.

If anyone has another way of doing a value to element switch, I am all eyes.

[rmackenzie]

Works perfectly - My dimension has an extra level added but using ...CurrentMember.Parent.Parent.Properties... I get the much required result.

No worries - but note the issue raised below in case your dimension structure changes.

What happens ... with alternate hierarchies, does it still work?

Alternate hierarchies will almost certainly cause problems. There must be a problem with MDX analogous to the classic issue with ELPAR and alternate hierarchies.

I think this MDX should work with alternate hierarchies - it doesn't rely on member relationships and instead tries to cast the string value of the property into a member for the output set:

Code: Select all

{Generate(
  {Filter(
    {TM1SubsetAll([Company])},
    [Company].CurrentMember.Properties("Surrogate") <> ""
  )},
  {StrToMember ( [Company].CurrentMember.Properties("Surrogate") ) }
)}

This appears to have the bonus functionality of failing gracefully where the attribute value isn't actually an element in the dimension. To be honest I thought the first expression was more concise so posted that even though this second expression covers a wider range of dimension configurations.

What happens in a non-leveled dimension ...?

What do you mean exactly by 'non-levelled' dimension? I assume you mean a dimension with no hierarchy - all n-level elements - but then why would you want 'surrogate' elements? I must be missing your point?

[gtonkin]

Thanks Robin, this looks similar to what I was trying to achieve but could not.
I am however getting the following error which I am unable to correct:

Code: Select all

TM1 Error: GENERATE: Error in value expression: STRTOMEMBER: Error in value expression: 

The first part of the MDX works i.e. the filtering, as soon as I add the GENERATE and the STRTOMEMBER back, I cannot compile/update
I tried too with [COMPANY].CurrentMember.Name just to see if there was an issue accessing the attribute, same issue.
Replacing the StrToMember with an element e.g. [102] returns 102 as expected.
Trying StrToMember("102") does not work. I have tried variations of CurrentMember, CurrentMember.Name etc. to see if something works, no luck.

Any suggestions?

[lotsaram]

What do you mean exactly by 'non-levelled' dimension? I assume you mean a dimension with no hierarchy - all n-level elements - but then why would you want 'surrogate' elements? I must be missing your point?

"non-levelled" as in ragged hierarchy, or alternate paths down a hierarchy having different number of levels. Doesn't exist in Analysis Services but pretty common in most TM1 dimensions.

[rmackenzie]

Thanks Robin, this looks similar to what I was trying to achieve but could not.
I am however getting the following error which I am unable to correct:

Code: Select all

TM1 Error: GENERATE: Error in value expression: STRTOMEMBER: Error in value expression: 

Yes, the culprit would any spaces in the element name - so you need to add [ and ] in the expression like so:

Code: Select all

{Generate(
  {Filter(
    {TM1SubsetAll([Company])},
    [Company].CurrentMember.Properties("Surrogate") <> ""
  )},
  {StrToMember ( "[" + [Company].CurrentMember.Properties("Surrogate") + "]" ) }
)}

What do you mean exactly by 'non-levelled' dimension? I assume you mean a dimension with no hierarchy - all n-level elements - but then why would you want 'surrogate' elements? I must be missing your point?

"non-levelled" as in ragged hierarchy, or alternate paths down a hierarchy having different number of levels. Doesn't exist in Analysis Services but pretty common in most TM1 dimensions.

Right - so the expression above should still work as the method is agnostic to levels or element relationships. Perhaps it would be better not to use TM1SubsetAll though in a dimension with multiple hierarchies and just concentrate on the hierarchy of interest, i.e. use TM1DrillDownMember:

Code: Select all

{Generate(
  {Filter(
    {TM1DrillDownMember({[Company].[All Companies]}, ALL, RECURSIVE)},
    [Company].CurrentMember.Properties("Surrogate") <> ""
  )},
  {StrToMember ( "[" + [Company].CurrentMember.Properties("Surrogate") + "]" ) }
)}

Lotsa, I'm sure you have a dimension tucked away somewhere that is so weird it will break it though

[gtonkin]

And voila! The brackets did the trick!

Thanks for your persistence Robin. This has been an interesting quest to understand MDX a little better, especially the concept of enumerating through a list of elements to test them each based on some condition. Your help is hugely appreciated. Thank too Lotsa and Tom for your comments and guidance.

[paulsimon]

Hi

Lots of MDX but this seems a little complex for what you are trying to achieve.

If the Input element below a consolidation always ends in _Input then you can create an MDX subset by recording yourself doing a wildcard match with *_Input.

The area in which I use flag is more when the dimension is being built. Some of the standard routines I use will add an _Input element below every consolidation that has a Y in an Attribute. Others will add _Input elements below all consolidations at a range of levels, unless they have an attribute saying that we don't want an _Input element. This is because businesses don't always want input at the very top of the hierarchy, but only on the intermediate range, and there may be some areas of the hierarchy where they want people to budget at the detailed level.

Regards

Paul

[gtonkin]

Thanks Paul - TM1FILTERBYPATTERN would definitely be the easiest way if I knew that the surrogates would end in _input or similar. They do in this case but I still wanted to see if there was a way of converting an attribute value to an element. The other gotcha with my example currently and this is where having the all the facts and the vision before the design would help, is that an extra level (Level 1) has been added and depending on what happens, this level may have surrogates in future for a different purpose. Al things being equal, my Level 2 now could have multiple descendents with the _input suffix which TM1FILTERBYPATTERN would obviously pick up.
Using the Level 2's attribute, I can link the desired surrogate and ensure only one surrogate per Level 2 element.

The right thing of course is probably to move the data into a cube where the Level 2's are N levels as looking at the data at any other level than Level 2 in my cube can not only be confusing but is probably just poor design (at least in this case only one dimension with surrogates-seen worse). No doubt as new data and requirements come may way, design will change and futher development should rationalise the situation.