MDX across summary/detail dimensions

[TM1Dunk]

Hello all

Been a *looong* while since I've been on here, and I some seeking your advice on MDX I've done the usual forum searches and reread the MDX Primer half a dozen times, but can't crack this one . All help/ideas/comments gratefully received.

Q: How do you pass the resulting set from a Summary dimension's MDX statement as the starting point for a Detail dimension's MDX statement?

Consider this example:

• 1) Dimension "Summary" contains a hierarchy of Country > City
  2) Dimension "Detail" contains a hierarchy City > Office
  3) Principal element names for 'Country' in both dimensions are identical

The puzzle is thus:

• 1) Perform an MDX query on the "Summary" dimension
  • a) Filter by wildcard country e.g. "UK"
    b) Expand out to find all cities within "UK"
    c) This is done with the MDX query below:

Code: Select all

{TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [Summary] )}, "UK")}, ALL, RECURSIVE )}

• 2) Perform an MDX query on the "Detail" dimension
  • a) Start with the cities resulting from query above
    b) Expand out to find all offices within these cities

Code: Select all

HOW CAN THIS BE DONE...?

I've experimented with a couple of things, using FILTER and EXISTS (the second of which I think is unsupported, either that or my syntax was bad as it wouldn't accept it) but can't get it to do what I expect.

The concept of using a subset of one dimension to drive a subset in another feels sound - indeed it is entirely possible to copy-paste the results of the first query into subset editor on the second dimension manually.

I am trying to avoid the use of TI to generate 2 separate subsets, having to loop through the first to ascertain the second if possible, as I want the second subset to be the source for a TI itself. Similarly, I want to avoid creating a technical dimension which munges together these to shiny clean ones.

TIA

TM1 Dunk

PS: If/when I get this to work, I might have some snazzy code to share with the good folk of the TM1Forum...

[rmackenzie]

Sorry to nit-pick, but your question is ambiguous due to:

1) Dimension "Summary" contains a hierarchy of All > Continent > Country

and

The puzzle is thus:

• 1) Perform an MDX query on the "Summary" dimension
  • a) Filter by wildcard country e.g. "UK"
    b) Expand out to find all cities within "UK"
    c) This is done with the MDX query below:

[TM1Dunk]

Good spot Mac - now corrected to remove ambuguity in my example.

Summary hierarchy is now Country > City
Detail hierarchy is now City > Office

[iansdigby]

How about creating a cube called 'Office' or something, with your two dims plus a measures one with a string element in.
Then put 'Y' in the cube against each matching Summary+Detail combination.

Then you could use MDX to read 'Y' values in the cube that match your selection.
(see page 11 of the MDX primer)

Hope this makes sense - no time to elucidate as off for Christmas lunch!

[TM1Dunk]

Ian

That is true; I could create a 2D cube and populate with strings or 1/0 flags to indicate intersections, then write the MDX to use this as a filter.

However, I was hoping to be more elegant than this (partly as the resulting MDX subset is only *part* of the overall goal, and that both of these dimensions are transient and volatile, and quite large, so creating a temporary cube populated by TI or a text-to-rule command aren't my favoured approaches).

The concept of using one subset as a starting set for another subset feels doable, where principal element names are common across dims (its not like I'm referencing GUIDs or MUNs or CAMIDs here!).

I'm hoping a) it is doable and b) it turns out to be one of those "oh, of course" moments when I figure out the nested MDX to use.

Thanks for the response though

Duncan

[tomok]

Here's your problem. You're asking for a recursive MDX query, where the first statement produces a set of elements from Dim_1 and the second statement wants to filter Dim_2 for all the members pulled from Dim_1. I'm pretty sure this isn't doable with MDX alone because the FILTER command will not accept a set to filter on, only a single value (including wild cards). Sorry, I don't have a solution for you. Just wanted to tell you not to waste your time trying MDX alone.

[rmackenzie]

You need to use string comparison in the FILTER function to get around the 'incorrect dimension for subset' error.

This will work to get the parents in Office matching a defined subset of n-levels in City:

Code: Select all

Filter ( 
	TM1FilterByLevel ( 
		TM1SubsetAll ( [Office] ), 
		1
		),
	Instr ( 
		SetToStr ( 
			TM1SubsetToSet ( [City], "Sample Cities" )
		),  
		[Office].CurrentMember.Name ) > 0
)

And you can use different functions in the INSTR as well, e.g.:

Code: Select all

Filter ( 
	TM1FilterByLevel ( 
		TM1SubsetAll ( [Office] ), 
		1
		),
	Instr ( 
		SetToStr ( 
			TM1SubsetAll ( [City]  )
		),  
		[Office].CurrentMember.Name ) > 0
)

And to return the children (offices) of the matching cities, you use the GENERATE function (it's iterative, not recursive btw; but the one tomok is looking for) to iterate over the matches and get the children:

Code: Select all

Generate (
	Filter ( 
		TM1FilterByLevel ( 
			TM1SubsetAll ( [Office] ), 
			1
			),
		Instr ( 
			SetToStr ( 
				TM1SubsetToSet ( [City], "Sample Cities"  )
			),  
			[Office].CurrentMember.Name ) > 0
	),
	[Office].CurrentMember.Children
)

MDX tends to incur problematic performance over large dimensions so YMMV with the usage of Generate(). Note I've dropped the {}s for clarity as it is already getting a bit unwieldly.

I'm interested in how you intend to use this - in an Active Form, TI, or something else?

[paulsimon]

Hi

I think that the key question is about how you want to use this?

If you are planning to display this using Active Forms then one possibility is to have two Active Forms, one for master and the other for detail.

The Master Active Form will just use a Std MDX statement to get all below the hierarchy, with City as the base.

For the Detail Active Form, you can use the parameter of the TM1RPTROW that allows an MDX statement, and just make up the MDX by splicing in the selected City from the Master Active Form.

To allow the user to select a City from the Master Active Form, you can use an Active Form, with the option to select details from the row in the Master Active Form that the user clicked on. This is then the parameter to the Detail Active Form MDX Statement.

You can get that fairly simply without the need for very complex MDX.

Regards

Paul Simon

[lotsaram]

If the query UI is an active form report then I think Paul's suggestion has a lot of merit. It seems by far the simplest way to solve the problem.

[rmackenzie]

You can get that fairly simply without the need for very complex MDX.

To be fair, it's not just the complexity, but the workaround involved. The sample MDX I wrote has a bit of a problem with e.g. Acton and Clacton-on-sea, or Eltham and Feltham (London place names for those unfamiliar) as the INSTR test will give false positives in such cases.

[iansdigby]

As with all complex problems, breaking this into non-complex chunks may be the best solution, especially if you are the only one in a department with advanced MDX skills that couldn't be understood or maintained by others. Or by yourself 2 years down the line.

I try to apply a sort of 'Occam's Razor' approach to complex problems nowadays: the simplest method is always the one to be preferred.

[TM1Dunk]

Thanks to all for the responses. In order to give due credit and answer some very prudent questions asked:

Tomok - not recursive to my mind, just 2-stage, whereby the results from one dimension's query become the SubsetToSet starting point of the second dimension's query
Mack - thanks for the detailed code sampler, I'll give it a try. Thankfully, though the dimensions may be reasonable sizes (around 3k-6k elements) the routine to generate the MDX subset is to be run very infrequently, and I'll immediately be converting it to a static subset (or nuking it)
Paul & Scotty - this is not intended for end-user visibility, and the physical subset is what I need to create, so Active Forms aren't an option
Ian - I agree, simple = best*

To shed a little light on what I'm toying with (definitely earned after the detailed responses), imagine a set of TIs which create one technical dimension listing all physical objects within a TM1 instance (cub, dim, pro, cho etc.) and another one which hunts for logical objects (vue, sub, blb etc.). These are individually useful but
likely more useful if you could hit them with a query like "show me cube XYZ, all of its dimensions, views, subsets, rules etc.".

Duncan

* most of the time...

[iansdigby]

It's just possible TM1Dunk, that this may be of interest - it is a VBA tool for doing apparently just what you are. Please use with caution - it's only something I knocked up for personal/in house use.

http://www.tm1forum.com/viewtopic.php?f=3&t=11174

Best, Ian

[TM1Dunk]

Ian

Thanks for the link - I did spot that and had a quick look at the VBA (charmingly parsed btw). However, I'm looking to create the "big brother" to your macro sheet, covering all file types (especially the pesky .blb and .blob) and 'shadow' objects created by TM1 Contributor apps. Also, I was hoping to keep the whole thing inside TI so it could be deployed on servers, which in some cases don't have Excel installed.

Regards,

TM1Dunk