How to set up dynamic hierarchies in OLAP

Question

I'm currently setting up a bi-solution, and having difficulties by defining the cube and its hierarchies.

A part of the schema definition looks like this:

<Dimension foreignKey="user" highCardinality="false" name="user">
  <Hierarchy name="user" hasAll="true" allMemberName="all" primaryKey="ID">
    <Table name="user" />
    <Level name="timezone" column="timezone"/>
    <Level name="locale" column="locale"/>
    <Level name="gender" column="gender"/>
    </Level>
  </Hierarchy>
</Dimension>

Now I want to access the gender level directly.

SELECT
{[user].[gender].Members} ON COLUMNS,
{[Measures].[Fact Count]} ON ROWS
FROM [cube]

Results in something like [user].[zone3].[de_DE].[male] = 10, [user].[zone1].[en_US].[male] = 30 and so on. I want a total for each sex: [user].[male] = 20 and [user].[female] = 30.

In summary, I need a flexible ordering of the levels. Is this possible? I know there are parallel hierarchies but i cannot create one for every possible order...

I also tried to put all attributes in properties instead of levels but I could not figure out to get a simple total amount of male/female users.

And it has to be quite simple because the whole thing will be part of a dynamic bi-tool...

Answer 1

You are using the wrong function for what you want.

The Member MDX function get all members containing the constraint on the MDX, and not all values ​​of a Level. You want all the values ​​of the level.

For this you have to use the Children MDX function. It takes all the values ​​(or children) of the associated level. Try this:

SELECT {[user].[gender].children} ON COLUMNS,
  {[Measures].[Fact Count]} ON ROWS
FROM [cube]

In summary, I need a flexible ordering of the levels. Is this possible? I know there are parallel hierarchies but i cannot create one for every possible order...

Create a hierarchy containing only one level. I see that really has nothing to their hierarchy so strong, that would be recommended, and not lose flexibility. This prevents erroneous OLAP operation of the data cube, like drill-down and roll-up (that use hierarchies). Getting something like this:

<Dimension foreignKey="user" highCardinality="false" name="user">
   <Hierarchy name="TIMEZONE" hasAll="true" allMemberName="all" primaryKey="ID">
     <Table name="user" />
     <Level name="timezone" column="timezone"/>
   </Hierarchy>
   <Hierarchy name="LOCALE" hasAll="true" allMemberName="all" primaryKey="ID">
     <Table name="user" />
     <Level name="locale" column="locale"/>
   </Hierarchy>
   <Hierarchy name="GENDER" hasAll="true" allMemberName="all" primaryKey="ID">
     <Table name="user" />
     <Level name="gender" column="gender"/>
   </Hierarchy>
</Dimension>

Hope this help.