Create custom hierarchy MDX for dimension

Question

In a cube there's an item dimension. An item - other than an item number - can also have other (numerical) attributes.

D_Item

• Attribute [attrib1]
  • Element1 [70]
  • Element2 [40]
  • Element3 [1]
  • Element4 [2]
• Other hierarchies

My goal is to use a iif or case ... when ... to create a new hierarchy (or set?) that I could use for slicing. Why not persist it into the cube? Because it is a very single use case and it is for use in a report...

My desired structure looks like this:

    WITH MyNewHierarchy AS 
CASE 
WHEN [D_Item].[attrib].Value = 70 then 'Company 1'
WHEN [D_Item].[attrib].Value = 40 then 'Company 2'
WHEN [D_Item].[attrib].Value = 1 OR [D_Item].[attrib].Value = 2 then 'Company 3'
END

SELECT Measure ON COLUMNS,
[D_Date].[Month].Chilren on ROWS

FROM CUBE

WHERE

[MyNewHierarchy].[Company 3]

That's probably quite a relational approach but I hope to reach this result one day.

Answer 1

I don't know if you can make a whole new hierarchy on the fly.

I know you can create custom members and "host" them in an unused existing hierarchy. You can do this with tuples but you must include the [All] member of the host in each tuple.

So your three new members would be like so:

MEMBER [SomeExistingDim].[SomeExistingHier].[All].[Company 1] AS
   (
      [SomeExistingDim].[SomeExistingHier].[All]
     ,[D_Item].[attrib].[Element1]
   )
MEMBER [SomeExistingDim].[SomeExistingHier].[All].[Company 2] AS
   (
      [SomeExistingDim].[SomeExistingHier].[All]
     ,[D_Item].[attrib].[Element2]
   )
MEMBER [SomeExistingDim].[SomeExistingHier].[All].[Company 3] AS
   (
      [SomeExistingDim].[SomeExistingHier].[All]
     ,[D_Item].[attrib].[Element3]
   )
+
   (
      [SomeExistingDim].[SomeExistingHier].[All]
     ,[D_Item].[attrib].[Element4]
   ) 

Then you can create a custom set from just the above:

SET [newMembers] AS
  {
     [SomeExistingDim].[SomeExistingHier].[All].[Company 1],
     [SomeExistingDim].[SomeExistingHier].[All].[Company 2], 
     [SomeExistingDim].[SomeExistingHier].[All].[Company 3],
  }