0

I'm trying to write an olap4j (Mondrian) query that will group the rows by ranges. Assume we have counts of cards per child and the children ages. i want to sum the cards amount by age ranges, so i will have counts for ages 0-5,5-10,10-15 and so on. Is this can be done with olap4j?

Yogev Caspi
  • 151
  • 12

1 Answers1

1

You need to define calculated members for that:

With member [Age].[0-4] as [Age].[0]:[Age].[4]
member [Age].[5-9] as [Age].[5]:[Age].[9]

etc.

Alternatively, you may want to re-design your dimension table. I'm guessing you have age as a degenerate dimension in the fact table. I suggest creating a separate dimension dim_age with a structure like this:

age_id, age, age_group
0, null, null
1, 0, 0-4
2, 1, 0-4
(...)

Then it's easy to define a first level on the dimension based on the age_group.

nsousa
  • 4,448
  • 1
  • 10
  • 15
  • Yes, It's a nice Idea, But my range size is dynamic. So pre-defining the ranges in the table won't do the trick. – Yogev Caspi Oct 22 '14 at 07:20
  • In that case you will need to create them on query time. If they're dynamic "but not so much", you can have multiple groupings defined in your dimension. – nsousa Oct 22 '14 at 07:37
  • Is this possible with olap4j api without MDX? – Yogev Caspi Oct 22 '14 at 11:19
  • You either have the complexity of the query at the MDX level or the SQL level. I guess you may be able to query directly in SQL, I'm not familiar with Olap4j's API, but why would you? The resulting SQL is most likely much harder to write, maintain and debug. – nsousa Oct 23 '14 at 07:36