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...

are you writing a BI-tool ? OR are you configuring/setting up the data to be used/imported into a BI-tool ? — Yahia, Oct 22 '11 at 12:14
what do you consider as BI-tool? I use mondrian for the olap cube. — gapvision, Oct 22 '11 at 12:22
my err - I didn't see the `mondrian` first time... sorry, there I can't help much... hopefully someone else can... — Yahia, Oct 22 '11 at 12:25
well i guess there's no big difference to SASS or other tools... — gapvision, Oct 22 '11 at 12:27

score 1 · Accepted Answer · answered Dec 14 '11 at 23:03

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.

How to set up dynamic hierarchies in OLAP

1 Answers1