[SOLVED] How to create a new dimension inside Cube with MDX. I would like it to be as a concatenation of existing two dimensions

How to create a new dimension inside Cube with MDX. I would like it to be as a concatenation of existing two dimensions

I have two dimensions in a Cube: PositionType and GeographyCluster. I would like to create a new dimension that will only be concatenation of all possible members of those two dimensions. For example for PositionType I have Retail and for GeographyCluster - London. I would like Cube to show this as a Retail - London.

Is it possible to create this easily with MDX formula. Thanks!

I created a new member inside one of dimensions but that is not what I intended, I need a completely new dimension consisting all possible combinations of the PositionType and Cluster

Solution

I strongly believe that MDX itself is designed for querying and manipulating data that already exists within the structure of a cube, rather than altering the cube structure on the fly.

My workaround is : I assume you have a measure (or you can create a dummy measure)where you do the concat the names of the current members of PositionType and GeographyCluster for each cell.

WITH 
MEMBER [Measures].[PositionTypeGeographyCluster] AS 
  [PositionType].CurrentMember.Name || ' - ' || [GeographyCluster].CurrentMember.Name
SELECT 
  [Measures].[PositionTypeGeographyCluster] ON COLUMNS,
  [PositionType].[PositionType].MEMBERS *
  [GeographyCluster].[GeographyCluster].MEMBERS ON ROWS
FROM [YourCube]