MDX to get Surrogate Element from Primary
- gtonkin
- MVP
- Posts: 1212
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
MDX to get Surrogate Element from Primary
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
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
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: MDX to get Surrogate Element from Primary
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
- MVP
- Posts: 1212
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX to get Surrogate Element from Primary
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.
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.
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: MDX to get Surrogate Element from Primary
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 wrote: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.
- gtonkin
- MVP
- Posts: 1212
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX to get Surrogate Element from Primary
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.
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.
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: MDX to get Surrogate Element from Primary
Try this:
Does that work?
Code: Select all
{ Filter(
{ TM1FilterByLevel( { TM1SubsetAll( [Company] )}, 0 )},
[Company].CurrentMember.Parent.Properties("Surrogate") = [Company].CurrentMember.Name
)}
Robin Mackenzie
- gtonkin
- MVP
- Posts: 1212
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX to get Surrogate Element from Primary
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.
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.
-
- MVP
- Posts: 3667
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: MDX to get Surrogate Element from Primary
What happens in a non-leveled dimension or a dimension with alternate hierarchies, does it still work?gtonkin wrote:Works perfectly - My dimension has an extra level added but using ...CurrentMember.Parent.Parent.Properties... I get the much required result.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
- gtonkin
- MVP
- Posts: 1212
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX to get Surrogate Element from Primary
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.lotsaram wrote:What happens in a non-leveled dimension or a dimension with alternate hierarchies, does it still work?
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.
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: MDX to get Surrogate Element from Primary
No worries - but note the issue raised below in case your dimension structure changes.gtonkin wrote:Works perfectly - My dimension has an extra level added but using ...CurrentMember.Parent.Parent.Properties... I get the much required result.
Alternate hierarchies will almost certainly cause problems. There must be a problem with MDX analogous to the classic issue with ELPAR and alternate hierarchies.lotsaram wrote:What happens ... with alternate hierarchies, does it still work?
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") ) }
)}
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?lotsaram wrote:What happens in a non-leveled dimension ...?
Robin Mackenzie
- gtonkin
- MVP
- Posts: 1212
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX to get Surrogate Element from Primary
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:
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?
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:
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?
-
- MVP
- Posts: 3667
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: MDX to get Surrogate Element from Primary
"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 wrote: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?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: MDX to get Surrogate Element from Primary
Yes, the culprit would any spaces in the element name - so you need to add [ and ] in the expression like so:gtonkin wrote: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:
Code: Select all
{Generate(
{Filter(
{TM1SubsetAll([Company])},
[Company].CurrentMember.Properties("Surrogate") <> ""
)},
{StrToMember ( "[" + [Company].CurrentMember.Properties("Surrogate") + "]" ) }
)}
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:lotsaram wrote:"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 wrote: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?
Code: Select all
{Generate(
{Filter(
{TM1DrillDownMember({[Company].[All Companies]}, ALL, RECURSIVE)},
[Company].CurrentMember.Properties("Surrogate") <> ""
)},
{StrToMember ( "[" + [Company].CurrentMember.Properties("Surrogate") + "]" ) }
)}
Robin Mackenzie
- gtonkin
- MVP
- Posts: 1212
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX to get Surrogate Element from Primary
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.
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
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: MDX to get Surrogate Element from Primary
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
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
- MVP
- Posts: 1212
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: MDX to get Surrogate Element from Primary
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.
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.