Create Subset using MDX based on another dimension

[prameson]

Hi All

There are a few posts on this topic but none of them has helped me solve this issue.

I have 2 dimensions used in different cubes:
Account
MIDI_Accounts

MIDI_Accounts contains a subset of Accounts (which have a source attribute of 'U') as well as other Accounts not in the Account dimension (with a different source attribute value)

I am trying to import data from one cube to another, and for this purpose want to create a subset on the Account dimension with all the Accounts that also exist in the MIDI_Accounts dimension (all the ones with the source attribute of 'U').

I have tried multiple MDX expressions using the source attribute (all with syntax errors), this is what I have at the moment:
{FILTER(
{TM1SUBSETALL( [Account] )},
[Account] = {FILTER( {TM1SUBSETALL( [MIDI_Accounts] )}, [MIDI_Accounts].[Source] = "U")}
)}

I've also tried to do just a simple Filter based on the other dimension - this doesn't return an error, but it doesn't return any elements either:
{FILTER(
{TM1SUBSETALL( [Account] )},
[MIDI_Accounts].[Source] = "U"
)}

Many thanks

[jim wood]

Why don't you just use the biggest subset as a data source for a process. Spool through it and those that have a dimix greater than 0 to the the subset of the smaller. Wouldn't that be easier than trying to play around with MDX?

[prameson]

Thanks Jim,

That is an option but this is part of what will be quite a large chore that will run quite frequently so if there is a cleaner (quicker?) way of creating this subset using MDX I would like to find it.

I'm really not sure if this is fairly straight forward and I'm just messing up the syntax or if it is not possible at all (in which case your option is a good one).

[lotsaram]

If you want an easy way to create a subset of Accounts elements that also exist in MIDI_Accounts with MDX then just create an attribute in the Accounts dimension "MIDI_Account" and Populate it with a flag value if the account is also in MIDI_Account. Seems a bit topsy turvy to me if this logic is already known (via the source attribute in the MIDI_Account dimension) but there isn't an attribute in the source dimension itself. I guess MIDI_Account was created manually? (If so better design would be to create MIDI_Account automatically by referencing the attribute in the Account dimension and do the same for the other source dimension. Better to maintain attributes manually if anything has to be manual and automate the dimension maintenance as opposed to vice versa.)

If you do this then the MDX will be pretty simple

Code: Select all

{FILTER( 
  {TM1SUBSETALL( [Account] )},
  [Account].[MIDI_Account] = "True")}
)}

But really you don't need MDX here and would be just as well off without it (better even if you are on a version prior to 10.2). If the reason for needing this subset is for use in a view to move data between cubes then this subset can be maintained with a few simple lines of code (either checking Source attribute = "U" in MIDI_Account or checking if DimIx to MIDI_Account > 0 from Account) which could be 1/ simpler and 2/ more performant if you want to use the subset in a TI process as prior to 10.2 there can be performance and locking issues with dynamic subsets as part of TI data sources.

[prameson]

Thanks for the replies guys

I will go with using TI code to create the subset.